1 |
56326
|
michele.ar
|
DROP VIEW organizations_view;
|
2 |
57674
|
michele.ar
|
DROP VIEW organizations_info_view;
|
3 |
56360
|
michele.ar
|
DROP VIEW organizations_simple_view;
|
4 |
57542
|
michele.ar
|
DROP VIEW users_view;
|
5 |
57833
|
michele.ar
|
DROP VIEW conflict_groups_view;
|
6 |
57819
|
michele.ar
|
DROP VIEW suggestions_info_by_country_view;
|
7 |
57854
|
michele.ar
|
DROP VIEW duplicate_groups_view
|
8 |
56326
|
michele.ar
|
|
9 |
|
|
DROP TABLE IF EXISTS other_ids;
|
10 |
56332
|
michele.ar
|
DROP TABLE IF EXISTS other_names;
|
11 |
|
|
DROP TABLE IF EXISTS acronyms;
|
12 |
56326
|
michele.ar
|
DROP TABLE IF EXISTS relationships;
|
13 |
|
|
DROP TABLE IF EXISTS urls;
|
14 |
57829
|
michele.ar
|
DROP TABLE IF EXISTS oa_duplicates;
|
15 |
|
|
DROP TABLE IF EXISTS oa_conflicts;
|
16 |
56326
|
michele.ar
|
DROP TABLE IF EXISTS organizations;
|
17 |
57097
|
michele.ar
|
DROP TABLE IF EXISTS org_types;
|
18 |
57289
|
michele.ar
|
|
19 |
|
|
DROP TABLE IF EXISTS user_countries;
|
20 |
|
|
DROP TABLE IF EXISTS users;
|
21 |
|
|
|
22 |
|
|
DROP TABLE IF EXISTS user_roles;
|
23 |
|
|
DROP TABLE IF EXISTS countries;
|
24 |
57097
|
michele.ar
|
DROP TABLE IF EXISTS id_types;
|
25 |
57099
|
michele.ar
|
DROP TABLE IF EXISTS languages;
|
26 |
56326
|
michele.ar
|
|
27 |
57304
|
michele.ar
|
DROP SEQUENCE IF EXISTS organizations_id_seq;
|
28 |
57212
|
michele.ar
|
|
29 |
57097
|
michele.ar
|
CREATE TABLE org_types (val text PRIMARY KEY);
|
30 |
57304
|
michele.ar
|
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other'), ('UNKNOWN');
|
31 |
56503
|
michele.ar
|
|
32 |
57097
|
michele.ar
|
CREATE TABLE id_types (val text PRIMARY KEY);
|
33 |
|
|
INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');
|
34 |
56503
|
michele.ar
|
|
35 |
57099
|
michele.ar
|
CREATE TABLE languages (val text PRIMARY KEY);
|
36 |
|
|
INSERT INTO languages VALUES ('UNKNOWN'), ('aa'), ('af'), ('am'), ('ar'), ('as'), ('az'), ('ba'), ('be'), ('bg'), ('bn'), ('br'), ('bs'), ('ca'), ('ch'), ('co'), ('cs'), ('cy'), ('da'),
|
37 |
|
|
('de'), ('dv'), ('dz'), ('el'), ('en'), ('eo'), ('es'), ('et'), ('eu'), ('fa'), ('fi'), ('fr'), ('fy'), ('ga'), ('gd'), ('gl'), ('gu'), ('he'), ('hi'), ('hr'), ('hu'), ('hy'), ('id'), ('is'),
|
38 |
|
|
('it'), ('iu'), ('ja'), ('jv'), ('ka'), ('kk'), ('kl'), ('km'), ('kn'), ('ko'), ('ku'), ('ky'), ('la'), ('lb'), ('lo'), ('lt'), ('lv'), ('mg'), ('mi'), ('mk'), ('ml'), ('mn'), ('mr'), ('ms'),
|
39 |
|
|
('mt'), ('my'), ('nb'), ('ne'), ('nl'), ('nn'), ('no'), ('oc'), ('or'), ('pa'), ('pl'), ('ps'), ('pt'), ('rm'), ('ro'), ('ru'), ('rw'), ('sa'), ('sd'), ('si'), ('sk'), ('sl'), ('so'), ('sq'),
|
40 |
|
|
('sr'), ('sv'), ('sw'), ('ta'), ('te'), ('tg'), ('th'), ('tk'), ('tl'), ('tr'), ('tt'), ('ug'), ('uk'), ('ur'), ('uz'), ('vi'), ('xh'), ('yo'), ('zh'), ('zu');
|
41 |
57097
|
michele.ar
|
|
42 |
57099
|
michele.ar
|
CREATE TABLE countries (val text PRIMARY KEY);
|
43 |
|
|
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'),
|
44 |
|
|
('BJ'), ('BM'), ('BN'), ('BO'), ('BQ'), ('BR'), ('BS'), ('BT'), ('BW'), ('BY'), ('BZ'), ('CA'), ('CD'), ('CF'), ('CG'), ('CH'), ('CI'), ('CL'), ('CM'), ('CN'), ('CO'), ('CR'),
|
45 |
|
|
('CU'), ('CV'), ('CW'), ('CY'), ('CZ'), ('DE'), ('DJ'), ('DK'), ('DM'), ('DO'), ('DZ'), ('EC'), ('EE'), ('EG'), ('EH'), ('ER'), ('ES'), ('ET'), ('FI'), ('FJ'), ('FM'), ('FO'),
|
46 |
|
|
('FR'), ('GA'), ('GB'), ('GD'), ('GE'), ('GF'), ('GH'), ('GI'), ('GL'), ('GM'), ('GN'), ('GP'), ('GQ'), ('GR'), ('GT'), ('GW'), ('GY'), ('HN'), ('HR'), ('HT'), ('HU'), ('ID'),
|
47 |
|
|
('IE'), ('IL'), ('IM'), ('IN'), ('IQ'), ('IR'), ('IS'), ('IT'), ('JE'), ('JM'), ('JO'), ('JP'), ('KE'), ('KG'), ('KH'), ('KN'), ('KP'), ('KR'), ('KW'), ('KY'), ('KZ'), ('LA'),
|
48 |
|
|
('LB'), ('LC'), ('LI'), ('LK'), ('LR'), ('LS'), ('LT'), ('LU'), ('LV'), ('LY'), ('MA'), ('MC'), ('MD'), ('ME'), ('MG'), ('MK'), ('ML'), ('MM'), ('MN'), ('MO'), ('MQ'), ('MR'),
|
49 |
|
|
('MS'), ('MT'), ('MU'), ('MV'), ('MW'), ('MX'), ('MY'), ('MZ'), ('NA'), ('NC'), ('NE'), ('NG'), ('NI'), ('NL'), ('NO'), ('NP'), ('NU'), ('NZ'), ('OM'), ('PA'), ('PE'), ('PF'),
|
50 |
|
|
('PG'), ('PH'), ('PK'), ('PL'), ('PS'), ('PT'), ('PW'), ('PY'), ('QA'), ('RE'), ('RO'), ('RS'), ('RU'), ('RW'), ('SA'), ('SB'), ('SC'), ('SD'), ('SE'), ('SG'), ('SI'), ('SJ'),
|
51 |
|
|
('SK'), ('SL'), ('SM'), ('SN'), ('SO'), ('SR'), ('SS'), ('ST'), ('SV'), ('SX'), ('SY'), ('SZ'), ('TC'), ('TD'), ('TG'), ('TH'), ('TJ'), ('TL'), ('TM'), ('TN'), ('TO'), ('TR'),
|
52 |
|
|
('TT'), ('TV'), ('TW'), ('TZ'), ('UA'), ('UG'), ('US'), ('UY'), ('UZ'), ('VA'), ('VC'), ('VE'), ('VG'), ('VN'), ('WS'), ('XK'), ('YE'), ('ZA'), ('ZM'), ('ZW');
|
53 |
|
|
|
54 |
57253
|
michele.ar
|
CREATE TABLE user_roles(role text PRIMARY KEY);
|
55 |
57661
|
michele.ar
|
INSERT INTO user_roles VALUES ('ADMIN'), ('NATIONAL_ADMIN'), ('USER'), ('PENDING'), ('NOT_AUTHORIZED');
|
56 |
57253
|
michele.ar
|
|
57 |
57212
|
michele.ar
|
CREATE TABLE users (
|
58 |
|
|
email text PRIMARY KEY,
|
59 |
57304
|
michele.ar
|
valid boolean DEFAULT true,
|
60 |
57253
|
michele.ar
|
role text NOT NULL default 'USER' REFERENCES user_roles(role)
|
61 |
57212
|
michele.ar
|
);
|
62 |
57099
|
michele.ar
|
|
63 |
57212
|
michele.ar
|
CREATE TABLE user_countries (
|
64 |
|
|
email text REFERENCES users(email),
|
65 |
|
|
country text REFERENCES countries(val),
|
66 |
|
|
PRIMARY KEY(email, country)
|
67 |
|
|
);
|
68 |
|
|
|
69 |
57304
|
michele.ar
|
CREATE SEQUENCE organizations_id_seq;
|
70 |
|
|
|
71 |
56326
|
michele.ar
|
CREATE TABLE organizations (
|
72 |
57304
|
michele.ar
|
id text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),
|
73 |
56642
|
michele.ar
|
name text,
|
74 |
57304
|
michele.ar
|
type text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),
|
75 |
56642
|
michele.ar
|
lat double precision,
|
76 |
|
|
lng double precision,
|
77 |
|
|
city text,
|
78 |
57099
|
michele.ar
|
country text REFERENCES countries(val),
|
79 |
56642
|
michele.ar
|
created_by text,
|
80 |
|
|
creation_date timestamp with time zone DEFAULT now(),
|
81 |
|
|
modified_by text,
|
82 |
|
|
modification_date timestamp with time zone DEFAULT now()
|
83 |
56326
|
michele.ar
|
);
|
84 |
57304
|
michele.ar
|
CREATE INDEX organizations_type_idx ON organizations(type);
|
85 |
|
|
CREATE INDEX organizations_country_idx ON organizations(country);
|
86 |
56326
|
michele.ar
|
|
87 |
|
|
CREATE TABLE other_ids (
|
88 |
57304
|
michele.ar
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
89 |
56326
|
michele.ar
|
otherid text,
|
90 |
57097
|
michele.ar
|
type text REFERENCES id_types(val),
|
91 |
56326
|
michele.ar
|
PRIMARY KEY (id, otherid, type)
|
92 |
|
|
);
|
93 |
57304
|
michele.ar
|
CREATE INDEX other_ids_id_idx ON other_ids(id);
|
94 |
56326
|
michele.ar
|
|
95 |
56332
|
michele.ar
|
CREATE TABLE other_names (
|
96 |
57304
|
michele.ar
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
97 |
56332
|
michele.ar
|
name text,
|
98 |
57099
|
michele.ar
|
lang text REFERENCES languages(val),
|
99 |
56332
|
michele.ar
|
PRIMARY KEY (id, name, lang)
|
100 |
56326
|
michele.ar
|
);
|
101 |
57304
|
michele.ar
|
CREATE INDEX other_names_id_idx ON other_names(id);
|
102 |
56326
|
michele.ar
|
|
103 |
56332
|
michele.ar
|
CREATE TABLE acronyms (
|
104 |
57304
|
michele.ar
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
105 |
56332
|
michele.ar
|
acronym text,
|
106 |
|
|
PRIMARY KEY (id, acronym)
|
107 |
|
|
);
|
108 |
57304
|
michele.ar
|
CREATE INDEX acronyms_id_idx ON acronyms(id);
|
109 |
56332
|
michele.ar
|
|
110 |
56326
|
michele.ar
|
CREATE TABLE relationships (
|
111 |
57304
|
michele.ar
|
id1 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
112 |
57130
|
michele.ar
|
reltype text,
|
113 |
57304
|
michele.ar
|
id2 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
114 |
56326
|
michele.ar
|
PRIMARY KEY (id1, reltype, id2)
|
115 |
|
|
);
|
116 |
57304
|
michele.ar
|
CREATE INDEX relationships_id1_idx ON relationships(id1);
|
117 |
|
|
CREATE INDEX relationships_id2_idx ON relationships(id2);
|
118 |
56326
|
michele.ar
|
|
119 |
|
|
CREATE TABLE urls (
|
120 |
57304
|
michele.ar
|
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
121 |
56326
|
michele.ar
|
url text,
|
122 |
|
|
PRIMARY KEY (id, url)
|
123 |
|
|
);
|
124 |
57304
|
michele.ar
|
CREATE INDEX urls_id_idx ON urls(id);
|
125 |
56326
|
michele.ar
|
|
126 |
57829
|
michele.ar
|
CREATE TABLE oa_duplicates (
|
127 |
57304
|
michele.ar
|
local_id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
128 |
57435
|
michele.ar
|
oa_original_id text NOT NULL,
|
129 |
|
|
oa_name text NOT NULL,
|
130 |
56503
|
michele.ar
|
oa_acronym text,
|
131 |
|
|
oa_country text,
|
132 |
|
|
oa_url text,
|
133 |
|
|
oa_collectedfrom text,
|
134 |
57435
|
michele.ar
|
reltype text NOT NULL DEFAULT 'suggested',
|
135 |
57518
|
michele.ar
|
PRIMARY KEY (local_id, oa_original_id)
|
136 |
56503
|
michele.ar
|
);
|
137 |
57829
|
michele.ar
|
CREATE INDEX oa_duplicates_local_id_idx ON oa_duplicates(local_id);
|
138 |
56503
|
michele.ar
|
|
139 |
57829
|
michele.ar
|
|
140 |
|
|
CREATE TABLE oa_conflicts (
|
141 |
|
|
id1 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
142 |
|
|
id2 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
143 |
|
|
reltype text NOT NULL DEFAULT 'suggested',
|
144 |
57833
|
michele.ar
|
idgroup text,
|
145 |
57829
|
michele.ar
|
PRIMARY KEY (id1, id2)
|
146 |
|
|
);
|
147 |
|
|
CREATE INDEX oa_conflicts_id1_idx ON oa_conflicts(id1);
|
148 |
|
|
CREATE INDEX oa_conflicts_id2_idx ON oa_conflicts(id2);
|
149 |
57833
|
michele.ar
|
CREATE INDEX oa_conflicts_idgroup_idx ON oa_conflicts(idgroup);
|
150 |
57829
|
michele.ar
|
|
151 |
56326
|
michele.ar
|
CREATE VIEW organizations_view AS SELECT
|
152 |
|
|
org.id,
|
153 |
|
|
org.name,
|
154 |
57289
|
michele.ar
|
org.type,
|
155 |
56326
|
michele.ar
|
org.lat,
|
156 |
|
|
org.lng,
|
157 |
|
|
org.city,
|
158 |
|
|
org.country,
|
159 |
57801
|
michele.ar
|
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
|
160 |
|
|
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang)) FILTER (WHERE n.name IS NOT NULL), '[]') AS other_names,
|
161 |
|
|
COALESCE(jsonb_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL), '[]') AS acronyms,
|
162 |
|
|
COALESCE(jsonb_agg(DISTINCT u.url) FILTER (WHERE u.url IS NOT NULL), '[]') AS urls,
|
163 |
|
|
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('relatedOrgId', relorg.id, 'relatedOrgName', relorg.name, 'type', r.reltype)) FILTER (WHERE relorg.id IS NOT NULL), '[]') AS relations
|
164 |
56326
|
michele.ar
|
FROM
|
165 |
|
|
organizations org
|
166 |
57801
|
michele.ar
|
LEFT OUTER JOIN other_ids oid ON (org.id = oid.id)
|
167 |
|
|
LEFT OUTER JOIN other_names n ON (org.id = n.id)
|
168 |
|
|
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
169 |
|
|
LEFT OUTER JOIN urls u ON (org.id = u.id)
|
170 |
|
|
LEFT OUTER JOIN relationships r ON (org.id = r.id1)
|
171 |
|
|
LEFT OUTER JOIN organizations relorg ON (relorg.id = r.id2)
|
172 |
56326
|
michele.ar
|
GROUP BY
|
173 |
|
|
org.id,
|
174 |
|
|
org.name,
|
175 |
57289
|
michele.ar
|
org.type,
|
176 |
56326
|
michele.ar
|
org.lat,
|
177 |
|
|
org.lng,
|
178 |
|
|
org.city,
|
179 |
57674
|
michele.ar
|
org.country;
|
180 |
|
|
|
181 |
|
|
CREATE VIEW organizations_info_view AS SELECT
|
182 |
|
|
org.id,
|
183 |
57801
|
michele.ar
|
org.name,
|
184 |
56642
|
michele.ar
|
org.created_by,
|
185 |
|
|
org.creation_date,
|
186 |
|
|
org.modified_by,
|
187 |
57674
|
michele.ar
|
org.modification_date,
|
188 |
57829
|
michele.ar
|
count(DISTINCT d.oa_original_id) as n_duplicates,
|
189 |
|
|
count(DISTINCT c.id2) as n_conflicts
|
190 |
57674
|
michele.ar
|
FROM organizations org
|
191 |
57829
|
michele.ar
|
LEFT OUTER JOIN oa_duplicates d ON (org.id = d.local_id AND d.reltype = 'suggested')
|
192 |
|
|
LEFT OUTER JOIN oa_conflicts c ON (org.id = c.id1 AND c.reltype = 'suggested')
|
193 |
57674
|
michele.ar
|
GROUP BY org.id;
|
194 |
56326
|
michele.ar
|
|
195 |
56360
|
michele.ar
|
CREATE VIEW organizations_simple_view AS SELECT
|
196 |
|
|
org.id,
|
197 |
|
|
org.name,
|
198 |
57289
|
michele.ar
|
org.type,
|
199 |
56360
|
michele.ar
|
org.city,
|
200 |
|
|
org.country,
|
201 |
57289
|
michele.ar
|
array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms
|
202 |
56360
|
michele.ar
|
FROM
|
203 |
|
|
organizations org
|
204 |
57269
|
michele.ar
|
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
205 |
56360
|
michele.ar
|
GROUP BY
|
206 |
|
|
org.id,
|
207 |
|
|
org.name,
|
208 |
57289
|
michele.ar
|
org.type,
|
209 |
56360
|
michele.ar
|
org.city,
|
210 |
|
|
org.country;
|
211 |
57542
|
michele.ar
|
|
212 |
|
|
CREATE VIEW users_view AS SELECT
|
213 |
|
|
u.email,
|
214 |
|
|
u.valid,
|
215 |
|
|
u.role,
|
216 |
57596
|
michele.ar
|
array_agg(uc.country) FILTER (WHERE uc.country IS NOT NULL) AS countries
|
217 |
57542
|
michele.ar
|
FROM
|
218 |
|
|
users u
|
219 |
|
|
LEFT OUTER JOIN user_countries uc ON (u.email = uc.email)
|
220 |
|
|
GROUP BY u.email, u.valid, u.role
|
221 |
|
|
ORDER BY u.email;
|
222 |
|
|
|
223 |
57845
|
michele.ar
|
CREATE VIEW suggestions_info_by_country_view AS SELECT c.val AS country,
|
224 |
57854
|
michele.ar
|
coalesce(t1.n_duplicates, 0) AS n_duplicates,
|
225 |
|
|
coalesce(t2.n_conflicts, 0) AS n_conflicts
|
226 |
57845
|
michele.ar
|
FROM countries c
|
227 |
57854
|
michele.ar
|
LEFT OUTER JOIN (SELECT o.country AS country, count(DISTINCT d.*) AS n_duplicates FROM oa_duplicates d LEFT OUTER JOIN organizations o ON (d.local_id = o.id) GROUP BY o.country) AS t1 ON (t1.country = c.val)
|
228 |
|
|
LEFT OUTER JOIN (SELECT o.country AS country, count(DISTINCT c.idgroup) AS n_conflicts FROM oa_conflicts c LEFT OUTER JOIN organizations o ON (c.id1 = o.id) GROUP BY o.country) AS t2 ON (t2.country = c.val);
|
229 |
57819
|
michele.ar
|
|
230 |
57833
|
michele.ar
|
CREATE VIEW conflict_groups_view AS SELECT
|
231 |
|
|
c.idgroup AS idgroup,
|
232 |
|
|
o1.id AS id_1,
|
233 |
57607
|
michele.ar
|
o1.name AS name_1,
|
234 |
|
|
o1.type AS type_1,
|
235 |
|
|
o1.city AS city_1,
|
236 |
|
|
o1.country AS country_1,
|
237 |
|
|
o2.id AS id_2,
|
238 |
|
|
o2.name AS name_2,
|
239 |
|
|
o2.type AS type_2,
|
240 |
|
|
o2.city AS city_2,
|
241 |
|
|
o2.country AS country_2
|
242 |
|
|
FROM
|
243 |
57829
|
michele.ar
|
oa_conflicts c
|
244 |
|
|
LEFT OUTER JOIN organizations o1 ON (c.id1 = o1.id)
|
245 |
|
|
LEFT OUTER JOIN organizations o2 ON (c.id2 = o2.id)
|
246 |
57607
|
michele.ar
|
WHERE
|
247 |
57845
|
michele.ar
|
o1.id IS NOT NULL AND O2.id IS NOT NULL AND c.idgroup IS NOT NULL;
|
248 |
57607
|
michele.ar
|
|
249 |
57854
|
michele.ar
|
CREATE VIEW duplicate_groups_view AS SELECT
|
250 |
|
|
o.id,
|
251 |
|
|
o.name,
|
252 |
57894
|
michele.ar
|
o.city,
|
253 |
57854
|
michele.ar
|
o.country,
|
254 |
|
|
count(d.*) as n_duplicates
|
255 |
|
|
FROM
|
256 |
|
|
oa_duplicates d
|
257 |
|
|
LEFT OUTER JOIN organizations o ON (o.id = d.local_id)
|
258 |
|
|
WHERE
|
259 |
|
|
d.reltype = 'suggested'
|
260 |
57894
|
michele.ar
|
GROUP BY o.id, o.name, o.city, o.country
|
261 |
57854
|
michele.ar
|
ORDER BY o.name;
|