D-Net

DROP VIEW organizations_view;

DROP VIEW organizations_info_view;

DROP VIEW organizations_simple_view;

DROP VIEW users_view;

DROP VIEW conflict_groups_view;

DROP VIEW suggestions_info_by_country_view;

DROP VIEW duplicate_groups_view

DROP TABLE IF EXISTS other_ids;

DROP TABLE IF EXISTS other_names;

DROP TABLE IF EXISTS acronyms;

DROP TABLE IF EXISTS relationships;

DROP TABLE IF EXISTS urls;

DROP TABLE IF EXISTS oa_duplicates;

DROP TABLE IF EXISTS oa_conflicts;

DROP TABLE IF EXISTS organizations;

DROP TABLE IF EXISTS org_types;

DROP TABLE IF EXISTS user_countries;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS user_roles;

DROP TABLE IF EXISTS countries;

DROP TABLE IF EXISTS id_types;

DROP TABLE IF EXISTS languages;

DROP SEQUENCE IF EXISTS organizations_id_seq;

CREATE TABLE org_types (val text PRIMARY KEY);

INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other'), ('UNKNOWN');

CREATE TABLE id_types (val text PRIMARY KEY);

INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');

CREATE TABLE languages (val text PRIMARY KEY);

INSERT INTO languages VALUES ('UNKNOWN'), ('aa'), ('af'), ('am'), ('ar'), ('as'), ('az'), ('ba'), ('be'), ('bg'), ('bn'), ('br'), ('bs'), ('ca'), ('ch'), ('co'), ('cs'), ('cy'), ('da'),

			('de'), ('dv'), ('dz'), ('el'), ('en'), ('eo'), ('es'), ('et'), ('eu'), ('fa'), ('fi'), ('fr'), ('fy'), ('ga'), ('gd'), ('gl'), ('gu'), ('he'), ('hi'), ('hr'), ('hu'), ('hy'), ('id'), ('is'),

			('it'), ('iu'), ('ja'), ('jv'), ('ka'), ('kk'), ('kl'), ('km'), ('kn'), ('ko'), ('ku'), ('ky'), ('la'), ('lb'), ('lo'), ('lt'), ('lv'), ('mg'), ('mi'), ('mk'), ('ml'), ('mn'), ('mr'), ('ms'),

			('mt'), ('my'), ('nb'), ('ne'), ('nl'), ('nn'), ('no'), ('oc'), ('or'), ('pa'), ('pl'), ('ps'), ('pt'), ('rm'), ('ro'), ('ru'), ('rw'), ('sa'), ('sd'), ('si'), ('sk'), ('sl'), ('so'), ('sq'),

			('sr'), ('sv'), ('sw'), ('ta'), ('te'), ('tg'), ('th'), ('tk'), ('tl'), ('tr'), ('tt'), ('ug'), ('uk'), ('ur'), ('uz'), ('vi'), ('xh'), ('yo'), ('zh'), ('zu');

CREATE TABLE countries (val text PRIMARY KEY);

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'),

			('BJ'), ('BM'), ('BN'), ('BO'), ('BQ'), ('BR'), ('BS'), ('BT'), ('BW'), ('BY'), ('BZ'), ('CA'), ('CD'), ('CF'), ('CG'), ('CH'), ('CI'), ('CL'), ('CM'), ('CN'), ('CO'), ('CR'),

			('CU'), ('CV'), ('CW'), ('CY'), ('CZ'), ('DE'), ('DJ'), ('DK'), ('DM'), ('DO'), ('DZ'), ('EC'), ('EE'), ('EG'), ('EH'), ('ER'), ('ES'), ('ET'), ('FI'), ('FJ'), ('FM'), ('FO'),

			('FR'), ('GA'), ('GB'), ('GD'), ('GE'), ('GF'), ('GH'), ('GI'), ('GL'), ('GM'), ('GN'), ('GP'), ('GQ'), ('GR'), ('GT'), ('GW'), ('GY'), ('HN'), ('HR'), ('HT'), ('HU'), ('ID'),

			('IE'), ('IL'), ('IM'), ('IN'), ('IQ'), ('IR'), ('IS'), ('IT'), ('JE'), ('JM'), ('JO'), ('JP'), ('KE'), ('KG'), ('KH'), ('KN'), ('KP'), ('KR'), ('KW'), ('KY'), ('KZ'), ('LA'),

			('LB'), ('LC'), ('LI'), ('LK'), ('LR'), ('LS'), ('LT'), ('LU'), ('LV'), ('LY'), ('MA'), ('MC'), ('MD'), ('ME'), ('MG'), ('MK'), ('ML'), ('MM'), ('MN'), ('MO'), ('MQ'), ('MR'),

			('MS'), ('MT'), ('MU'), ('MV'), ('MW'), ('MX'), ('MY'), ('MZ'), ('NA'), ('NC'), ('NE'), ('NG'), ('NI'), ('NL'), ('NO'), ('NP'), ('NU'), ('NZ'), ('OM'), ('PA'), ('PE'), ('PF'),

			('PG'), ('PH'), ('PK'), ('PL'), ('PS'), ('PT'), ('PW'), ('PY'), ('QA'), ('RE'), ('RO'), ('RS'), ('RU'), ('RW'), ('SA'), ('SB'), ('SC'), ('SD'), ('SE'), ('SG'), ('SI'), ('SJ'),

			('SK'), ('SL'), ('SM'), ('SN'), ('SO'), ('SR'), ('SS'), ('ST'), ('SV'), ('SX'), ('SY'), ('SZ'), ('TC'), ('TD'), ('TG'), ('TH'), ('TJ'), ('TL'), ('TM'), ('TN'), ('TO'), ('TR'),

			('TT'), ('TV'), ('TW'), ('TZ'), ('UA'), ('UG'), ('US'), ('UY'), ('UZ'), ('VA'), ('VC'), ('VE'), ('VG'), ('VN'), ('WS'), ('XK'), ('YE'), ('ZA'), ('ZM'), ('ZW');

CREATE TABLE user_roles(role text PRIMARY KEY);

INSERT INTO user_roles VALUES ('ADMIN'), ('NATIONAL_ADMIN'), ('USER'), ('PENDING'), ('NOT_AUTHORIZED');

CREATE TABLE users (

	email     text PRIMARY KEY,

	valid     boolean DEFAULT true,

	role      text NOT NULL default 'USER' REFERENCES user_roles(role)

);

CREATE TABLE user_countries (

	email     text REFERENCES users(email),

	country   text  REFERENCES countries(val),

	PRIMARY KEY(email, country)	

);

CREATE SEQUENCE organizations_id_seq;

CREATE TABLE organizations (

    id                text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),

    name              text,

   	type              text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),

    lat               double precision,

	lng               double precision,

	city              text,

	country           text REFERENCES countries(val),

	created_by        text,

	creation_date     timestamp with time zone DEFAULT now(),

	modified_by       text,

	modification_date timestamp with time zone DEFAULT now()

);

CREATE INDEX organizations_type_idx ON organizations(type);

CREATE INDEX organizations_country_idx ON organizations(country);

CREATE TABLE other_ids (

	id      text REFERENCES organizations(id) ON UPDATE CASCADE,

	otherid text,

	type    text REFERENCES id_types(val),

	PRIMARY KEY (id, otherid, type)

);

CREATE INDEX other_ids_id_idx ON other_ids(id);

CREATE TABLE other_names (

	id    text REFERENCES organizations(id) ON UPDATE CASCADE,

	name  text,

	lang  text REFERENCES languages(val),

	PRIMARY KEY (id, name, lang)

);

CREATE INDEX other_names_id_idx ON other_names(id);

CREATE TABLE acronyms (

	id text  REFERENCES organizations(id) ON UPDATE CASCADE,

	acronym text,

	PRIMARY KEY (id, acronym)

);

CREATE INDEX acronyms_id_idx ON acronyms(id);

CREATE TABLE relationships (

	id1     text REFERENCES organizations(id) ON UPDATE CASCADE,

	reltype text,

	id2     text REFERENCES organizations(id) ON UPDATE CASCADE,

    PRIMARY KEY (id1, reltype, id2)

);

CREATE INDEX relationships_id1_idx ON relationships(id1);

CREATE INDEX relationships_id2_idx ON relationships(id2);

CREATE TABLE urls (

	id  text REFERENCES organizations(id) ON UPDATE CASCADE,

	url text,

	PRIMARY KEY (id, url)

);

CREATE INDEX urls_id_idx ON urls(id);

CREATE TABLE oa_duplicates (

	local_id         text REFERENCES organizations(id) ON UPDATE CASCADE,

	oa_original_id   text NOT NULL,

	oa_name          text NOT NULL,

	oa_acronym       text,

	oa_country       text,

	oa_url           text,

	oa_collectedfrom text,

	reltype          text NOT NULL DEFAULT 'suggested',

	PRIMARY KEY (local_id, oa_original_id)

);

CREATE INDEX oa_duplicates_local_id_idx ON oa_duplicates(local_id);

CREATE TABLE oa_conflicts (

	id1     text REFERENCES organizations(id) ON UPDATE CASCADE,

	id2     text REFERENCES organizations(id) ON UPDATE CASCADE,

	reltype text NOT NULL DEFAULT 'suggested',

	idgroup text,

	PRIMARY KEY (id1, id2)

);

CREATE INDEX oa_conflicts_id1_idx ON oa_conflicts(id1);

CREATE INDEX oa_conflicts_id2_idx ON oa_conflicts(id2);

CREATE INDEX oa_conflicts_idgroup_idx ON oa_conflicts(idgroup);

CREATE VIEW organizations_view AS SELECT

    org.id,

    org.name,

    org.type,

    org.lat,

	org.lng,

	org.city,

	org.country,

	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type))                                         FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,

	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang))                                              FILTER (WHERE n.name      IS NOT NULL), '[]') AS other_names,

	COALESCE(jsonb_agg(DISTINCT a.acronym)                                                                                       FILTER (WHERE a.acronym   IS NOT NULL), '[]') AS acronyms,	

	COALESCE(jsonb_agg(DISTINCT u.url)                                                                                           FILTER (WHERE u.url       IS NOT NULL), '[]') AS urls,

	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

FROM

    organizations org

    LEFT OUTER JOIN other_ids oid         ON (org.id = oid.id)

    LEFT OUTER JOIN other_names n         ON (org.id = n.id)

    LEFT OUTER JOIN acronyms a            ON (org.id = a.id)

    LEFT OUTER JOIN urls u                ON (org.id = u.id)

    LEFT OUTER JOIN relationships r       ON (org.id = r.id1)

    LEFT OUTER JOIN organizations relorg  ON (relorg.id = r.id2)

GROUP BY

    org.id,

    org.name,

    org.type,

    org.lat,

	org.lng,

	org.city,

	org.country;

CREATE VIEW organizations_info_view AS SELECT

	org.id,

	org.name,

	org.created_by,

	org.creation_date,

	org.modified_by,

	org.modification_date,

	count(DISTINCT d.oa_original_id) as n_duplicates,

	count(DISTINCT c.id2) as n_conflicts

FROM organizations org

	LEFT OUTER JOIN oa_duplicates d ON (org.id = d.local_id AND d.reltype = 'suggested')

	LEFT OUTER JOIN oa_conflicts  c ON (org.id = c.id1 AND c.reltype = 'suggested')

GROUP BY org.id;

CREATE VIEW organizations_simple_view AS SELECT

    org.id,

    org.name,

    org.type,

	org.city,

	org.country,

	array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms

FROM

    organizations org

    LEFT OUTER JOIN acronyms a ON (org.id = a.id)

GROUP BY

    org.id,

    org.name,

    org.type,

	org.city,

	org.country;

CREATE VIEW	users_view AS SELECT

	u.email,

	u.valid,

	u.role,

	array_agg(uc.country) FILTER (WHERE uc.country IS NOT NULL) AS countries

FROM

	users u

	LEFT OUTER JOIN user_countries uc ON (u.email = uc.email)

GROUP BY u.email, u.valid, u.role

ORDER BY u.email;

CREATE VIEW suggestions_info_by_country_view AS SELECT c.val AS country,

	coalesce(t1.n_duplicates, 0) AS n_duplicates,

	coalesce(t2.n_conflicts,  0) AS n_conflicts

FROM countries c

	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)

	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);

CREATE VIEW conflict_groups_view AS SELECT

    c.idgroup  AS idgroup,

	o1.id      AS id_1,

    o1.name    AS name_1,

    o1.type    AS type_1,

	o1.city    AS city_1,

	o1.country AS country_1,

    o2.id      AS id_2,

    o2.name    AS name_2,

    o2.type    AS type_2,

	o2.city    AS city_2,

	o2.country AS country_2

FROM

	oa_conflicts c

	LEFT OUTER JOIN organizations o1 ON (c.id1 = o1.id)

    LEFT OUTER JOIN organizations o2 ON (c.id2 = o2.id)

WHERE

	o1.id IS NOT NULL AND O2.id IS NOT NULL AND c.idgroup IS NOT NULL;

CREATE VIEW duplicate_groups_view AS SELECT

	o.id,

	o.name,

	o.city,

	o.country,

	count(d.*) as n_duplicates

FROM

	oa_duplicates d

	LEFT OUTER JOIN organizations o ON (o.id = d.local_id)

WHERE

	d.reltype = 'suggested'

GROUP BY o.id, o.name, o.city, o.country

ORDER BY o.name;