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