D-Net

DROP VIEW organizations_view;

DROP VIEW organizations_simple_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 types;

DROP TABLE IF EXISTS urls;

DROP TABLE IF EXISTS openaire_simrels;

DROP TABLE IF EXISTS organizations;

DROP TABLE IF EXISTS org_types;

DROP TABLE IF EXISTS id_types;

DROP TABLE IF EXISTS rel_types;

DROP TABLE IF EXISTS simrel_types;

CREATE TABLE org_types (val text PRIMARY KEY);

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

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 rel_types (val text PRIMARY KEY);

INSERT INTO rel_types VALUES ('Child'), ('Parent'), ('Related'), ('Other');

CREATE TABLE simrel_types (val text PRIMARY KEY);

INSERT INTO simrel_types VALUES ('suggested'), ('is_similar'), ('is_different');

CREATE TABLE organizations (

    id                text PRIMARY KEY,

    name              text,

    lat               double precision,

	lng               double precision,

	city              text,

	country           text,

	created_by        text,

	creation_date     timestamp with time zone DEFAULT now(),

	modified_by       text,

	modification_date timestamp with time zone DEFAULT now()

);

CREATE TABLE other_ids (

	id      text REFERENCES organizations(id),

	otherid text,

	type    text REFERENCES id_types(val),

	PRIMARY KEY (id, otherid, type)

);

CREATE TABLE other_names (

	id    text REFERENCES organizations(id),

	name  text,

	lang  text DEFAULT '',

	PRIMARY KEY (id, name, lang)

);

CREATE TABLE acronyms (

	id text,

	acronym text,

	PRIMARY KEY (id, acronym)

);

CREATE TABLE relationships (

	id1     text REFERENCES organizations(id),

	reltype text REFERENCES rel_types(val),

	id2     text REFERENCES organizations(id),

    PRIMARY KEY (id1, reltype, id2)

);

CREATE TABLE types (

	id   text REFERENCES organizations(id),

	type text REFERENCES org_types(val),

	PRIMARY KEY (id, type)

);

CREATE TABLE urls (

	id  text REFERENCES organizations(id),

	url text,

	PRIMARY KEY (id, url)

);

CREATE TABLE openaire_simrels (

	local_id         text REFERENCES organizations(id),

	oa_id            text,

	oa_original_id   text,

	oa_name          text,

	oa_acronym       text,

	oa_country       text,

	oa_url           text,

	oa_collectedfrom text,

	reltype          text  NOT NULL DEFAULT 'suggested' REFERENCES simrel_types(val),

	PRIMARY KEY (local_id, oa_id)

);

CREATE VIEW organizations_view AS SELECT

    org.id,

    org.name,

    org.lat,

	org.lng,

	org.city,

	org.country,

	org.created_by,

	org.creation_date,

	org.modified_by,

	org.modification_date,

	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 t.type::text)                                            FILTER (WHERE t.type      IS NOT NULL), '[]') AS types,

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

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 types t          ON (org.id = t.id)

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

GROUP BY

    org.id,

    org.name,

    org.lat,

	org.lng,

	org.city,

	org.country,

	org.created_by,

	org.creation_date,

	org.modified_by,

	org.modification_date;

CREATE VIEW organizations_simple_view AS SELECT

    org.id,

    org.name,

	org.city,

	org.country,

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

	array_agg(DISTINCT t.type::text) FILTER (WHERE t.type    IS NOT NULL) AS types

FROM

    organizations org

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

    LEFT OUTER JOIN types t          ON (org.id = t.id)

GROUP BY

    org.id,

    org.name,

	org.city,

	org.country;