|
1
|
DROP VIEW organizations_view;
|
|
2
|
DROP VIEW organizations_simple_view;
|
|
3
|
DROP VIEW users_view;
|
|
4
|
|
|
5
|
DROP TABLE IF EXISTS other_ids;
|
|
6
|
DROP TABLE IF EXISTS other_names;
|
|
7
|
DROP TABLE IF EXISTS acronyms;
|
|
8
|
DROP TABLE IF EXISTS relationships;
|
|
9
|
DROP TABLE IF EXISTS urls;
|
|
10
|
DROP TABLE IF EXISTS openaire_simrels;
|
|
11
|
DROP TABLE IF EXISTS organizations;
|
|
12
|
DROP TABLE IF EXISTS org_types;
|
|
13
|
|
|
14
|
DROP TABLE IF EXISTS user_countries;
|
|
15
|
DROP TABLE IF EXISTS users;
|
|
16
|
|
|
17
|
DROP TABLE IF EXISTS user_roles;
|
|
18
|
DROP TABLE IF EXISTS countries;
|
|
19
|
DROP TABLE IF EXISTS id_types;
|
|
20
|
DROP TABLE IF EXISTS languages;
|
|
21
|
|
|
22
|
DROP SEQUENCE IF EXISTS organizations_id_seq;
|
|
23
|
|
|
24
|
CREATE TABLE org_types (val text PRIMARY KEY);
|
|
25
|
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other'), ('UNKNOWN');
|
|
26
|
|
|
27
|
CREATE TABLE id_types (val text PRIMARY KEY);
|
|
28
|
INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');
|
|
29
|
|
|
30
|
CREATE TABLE languages (val text PRIMARY KEY);
|
|
31
|
INSERT INTO languages VALUES ('UNKNOWN'), ('aa'), ('af'), ('am'), ('ar'), ('as'), ('az'), ('ba'), ('be'), ('bg'), ('bn'), ('br'), ('bs'), ('ca'), ('ch'), ('co'), ('cs'), ('cy'), ('da'),
|
|
32
|
('de'), ('dv'), ('dz'), ('el'), ('en'), ('eo'), ('es'), ('et'), ('eu'), ('fa'), ('fi'), ('fr'), ('fy'), ('ga'), ('gd'), ('gl'), ('gu'), ('he'), ('hi'), ('hr'), ('hu'), ('hy'), ('id'), ('is'),
|
|
33
|
('it'), ('iu'), ('ja'), ('jv'), ('ka'), ('kk'), ('kl'), ('km'), ('kn'), ('ko'), ('ku'), ('ky'), ('la'), ('lb'), ('lo'), ('lt'), ('lv'), ('mg'), ('mi'), ('mk'), ('ml'), ('mn'), ('mr'), ('ms'),
|
|
34
|
('mt'), ('my'), ('nb'), ('ne'), ('nl'), ('nn'), ('no'), ('oc'), ('or'), ('pa'), ('pl'), ('ps'), ('pt'), ('rm'), ('ro'), ('ru'), ('rw'), ('sa'), ('sd'), ('si'), ('sk'), ('sl'), ('so'), ('sq'),
|
|
35
|
('sr'), ('sv'), ('sw'), ('ta'), ('te'), ('tg'), ('th'), ('tk'), ('tl'), ('tr'), ('tt'), ('ug'), ('uk'), ('ur'), ('uz'), ('vi'), ('xh'), ('yo'), ('zh'), ('zu');
|
|
36
|
|
|
37
|
CREATE TABLE countries (val text PRIMARY KEY);
|
|
38
|
INSERT INTO countries VALUES ('UNKNOWN'), ('AD'), ('AE'), ('AF'), ('AG'), ('AL'), ('AM'), ('AO'), ('AR'), ('AT'), ('AU'), ('AW'), ('AX'), ('AZ'), ('BA'), ('BB'), ('BD'), ('BE'), ('BF'), ('BG'), ('BH'), ('BI'),
|
|
39
|
('BJ'), ('BM'), ('BN'), ('BO'), ('BQ'), ('BR'), ('BS'), ('BT'), ('BW'), ('BY'), ('BZ'), ('CA'), ('CD'), ('CF'), ('CG'), ('CH'), ('CI'), ('CL'), ('CM'), ('CN'), ('CO'), ('CR'),
|
|
40
|
('CU'), ('CV'), ('CW'), ('CY'), ('CZ'), ('DE'), ('DJ'), ('DK'), ('DM'), ('DO'), ('DZ'), ('EC'), ('EE'), ('EG'), ('EH'), ('ER'), ('ES'), ('ET'), ('FI'), ('FJ'), ('FM'), ('FO'),
|
|
41
|
('FR'), ('GA'), ('GB'), ('GD'), ('GE'), ('GF'), ('GH'), ('GI'), ('GL'), ('GM'), ('GN'), ('GP'), ('GQ'), ('GR'), ('GT'), ('GW'), ('GY'), ('HN'), ('HR'), ('HT'), ('HU'), ('ID'),
|
|
42
|
('IE'), ('IL'), ('IM'), ('IN'), ('IQ'), ('IR'), ('IS'), ('IT'), ('JE'), ('JM'), ('JO'), ('JP'), ('KE'), ('KG'), ('KH'), ('KN'), ('KP'), ('KR'), ('KW'), ('KY'), ('KZ'), ('LA'),
|
|
43
|
('LB'), ('LC'), ('LI'), ('LK'), ('LR'), ('LS'), ('LT'), ('LU'), ('LV'), ('LY'), ('MA'), ('MC'), ('MD'), ('ME'), ('MG'), ('MK'), ('ML'), ('MM'), ('MN'), ('MO'), ('MQ'), ('MR'),
|
|
44
|
('MS'), ('MT'), ('MU'), ('MV'), ('MW'), ('MX'), ('MY'), ('MZ'), ('NA'), ('NC'), ('NE'), ('NG'), ('NI'), ('NL'), ('NO'), ('NP'), ('NU'), ('NZ'), ('OM'), ('PA'), ('PE'), ('PF'),
|
|
45
|
('PG'), ('PH'), ('PK'), ('PL'), ('PS'), ('PT'), ('PW'), ('PY'), ('QA'), ('RE'), ('RO'), ('RS'), ('RU'), ('RW'), ('SA'), ('SB'), ('SC'), ('SD'), ('SE'), ('SG'), ('SI'), ('SJ'),
|
|
46
|
('SK'), ('SL'), ('SM'), ('SN'), ('SO'), ('SR'), ('SS'), ('ST'), ('SV'), ('SX'), ('SY'), ('SZ'), ('TC'), ('TD'), ('TG'), ('TH'), ('TJ'), ('TL'), ('TM'), ('TN'), ('TO'), ('TR'),
|
|
47
|
('TT'), ('TV'), ('TW'), ('TZ'), ('UA'), ('UG'), ('US'), ('UY'), ('UZ'), ('VA'), ('VC'), ('VE'), ('VG'), ('VN'), ('WS'), ('XK'), ('YE'), ('ZA'), ('ZM'), ('ZW');
|
|
48
|
|
|
49
|
CREATE TABLE user_roles(role text PRIMARY KEY);
|
|
50
|
INSERT INTO user_roles VALUES ('USER'), ('SUPERUSER'), ('PENDING');
|
|
51
|
|
|
52
|
CREATE TABLE users (
|
|
53
|
email text PRIMARY KEY,
|
|
54
|
password text NOT NULL,
|
|
55
|
valid boolean DEFAULT true,
|
|
56
|
role text NOT NULL default 'USER' REFERENCES user_roles(role)
|
|
57
|
);
|
|
58
|
|
|
59
|
CREATE TABLE user_countries (
|
|
60
|
email text REFERENCES users(email),
|
|
61
|
country text REFERENCES countries(val),
|
|
62
|
PRIMARY KEY(email, country)
|
|
63
|
);
|
|
64
|
|
|
65
|
CREATE SEQUENCE organizations_id_seq;
|
|
66
|
|
|
67
|
CREATE TABLE organizations (
|
|
68
|
id text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),
|
|
69
|
name text,
|
|
70
|
type text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),
|
|
71
|
lat double precision,
|
|
72
|
lng double precision,
|
|
73
|
city text,
|
|
74
|
country text REFERENCES countries(val),
|
|
75
|
created_by text,
|
|
76
|
creation_date timestamp with time zone DEFAULT now(),
|
|
77
|
modified_by text,
|
|
78
|
modification_date timestamp with time zone DEFAULT now()
|
|
79
|
);
|
|
80
|
CREATE INDEX organizations_type_idx ON organizations(type);
|
|
81
|
CREATE INDEX organizations_country_idx ON organizations(country);
|
|
82
|
|
|
83
|
CREATE TABLE other_ids (
|
|
84
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
85
|
otherid text,
|
|
86
|
type text REFERENCES id_types(val),
|
|
87
|
PRIMARY KEY (id, otherid, type)
|
|
88
|
);
|
|
89
|
CREATE INDEX other_ids_id_idx ON other_ids(id);
|
|
90
|
|
|
91
|
CREATE TABLE other_names (
|
|
92
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
93
|
name text,
|
|
94
|
lang text REFERENCES languages(val),
|
|
95
|
PRIMARY KEY (id, name, lang)
|
|
96
|
);
|
|
97
|
CREATE INDEX other_names_id_idx ON other_names(id);
|
|
98
|
|
|
99
|
CREATE TABLE acronyms (
|
|
100
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
101
|
acronym text,
|
|
102
|
PRIMARY KEY (id, acronym)
|
|
103
|
);
|
|
104
|
CREATE INDEX acronyms_id_idx ON acronyms(id);
|
|
105
|
|
|
106
|
CREATE TABLE relationships (
|
|
107
|
id1 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
108
|
reltype text,
|
|
109
|
id2 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
110
|
PRIMARY KEY (id1, reltype, id2)
|
|
111
|
);
|
|
112
|
CREATE INDEX relationships_id1_idx ON relationships(id1);
|
|
113
|
CREATE INDEX relationships_id2_idx ON relationships(id2);
|
|
114
|
|
|
115
|
CREATE TABLE urls (
|
|
116
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
117
|
url text,
|
|
118
|
PRIMARY KEY (id, url)
|
|
119
|
);
|
|
120
|
CREATE INDEX urls_id_idx ON urls(id);
|
|
121
|
|
|
122
|
CREATE TABLE openaire_simrels (
|
|
123
|
local_id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
124
|
oa_original_id text NOT NULL,
|
|
125
|
oa_name text NOT NULL,
|
|
126
|
oa_acronym text,
|
|
127
|
oa_country text,
|
|
128
|
oa_url text,
|
|
129
|
oa_collectedfrom text,
|
|
130
|
reltype text NOT NULL DEFAULT 'suggested',
|
|
131
|
PRIMARY KEY (local_id, oa_original_id)
|
|
132
|
);
|
|
133
|
CREATE INDEX openaire_simrels_local_id_idx ON openaire_simrels(local_id);
|
|
134
|
|
|
135
|
CREATE VIEW organizations_view AS SELECT
|
|
136
|
org.id,
|
|
137
|
org.name,
|
|
138
|
org.type,
|
|
139
|
org.lat,
|
|
140
|
org.lng,
|
|
141
|
org.city,
|
|
142
|
org.country,
|
|
143
|
org.created_by,
|
|
144
|
org.creation_date,
|
|
145
|
org.modified_by,
|
|
146
|
org.modification_date,
|
|
147
|
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
|
|
148
|
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang)) FILTER (WHERE n.name IS NOT NULL), '[]') AS other_names,
|
|
149
|
COALESCE(jsonb_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL), '[]') AS acronyms,
|
|
150
|
COALESCE(jsonb_agg(DISTINCT u.url) FILTER (WHERE u.url IS NOT NULL), '[]') AS urls
|
|
151
|
FROM
|
|
152
|
organizations org
|
|
153
|
LEFT OUTER JOIN other_ids oid ON (org.id = oid.id)
|
|
154
|
LEFT OUTER JOIN other_names n ON (org.id = n.id)
|
|
155
|
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
|
156
|
LEFT OUTER JOIN urls u ON (org.id = u.id)
|
|
157
|
GROUP BY
|
|
158
|
org.id,
|
|
159
|
org.name,
|
|
160
|
org.type,
|
|
161
|
org.lat,
|
|
162
|
org.lng,
|
|
163
|
org.city,
|
|
164
|
org.country,
|
|
165
|
org.created_by,
|
|
166
|
org.creation_date,
|
|
167
|
org.modified_by,
|
|
168
|
org.modification_date;
|
|
169
|
|
|
170
|
CREATE VIEW organizations_simple_view AS SELECT
|
|
171
|
org.id,
|
|
172
|
org.name,
|
|
173
|
org.type,
|
|
174
|
org.city,
|
|
175
|
org.country,
|
|
176
|
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms
|
|
177
|
FROM
|
|
178
|
organizations org
|
|
179
|
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
|
180
|
GROUP BY
|
|
181
|
org.id,
|
|
182
|
org.name,
|
|
183
|
org.type,
|
|
184
|
org.city,
|
|
185
|
org.country;
|
|
186
|
|
|
187
|
CREATE VIEW users_view AS SELECT
|
|
188
|
u.email,
|
|
189
|
u.valid,
|
|
190
|
u.role,
|
|
191
|
array_agg(uc.country) AS countries
|
|
192
|
FROM
|
|
193
|
users u
|
|
194
|
LEFT OUTER JOIN user_countries uc ON (u.email = uc.email)
|
|
195
|
GROUP BY u.email, u.valid, u.role
|
|
196
|
ORDER BY u.email;
|
|
197
|
|
|
198
|
|
|
199
|
|