Project

General

Profile

1
DROP VIEW organizations_view;
2
DROP VIEW organizations_simple_view;
3

    
4
DROP TABLE IF EXISTS other_ids;
5
DROP TABLE IF EXISTS other_names;
6
DROP TABLE IF EXISTS acronyms;
7
DROP TABLE IF EXISTS relationships;
8
DROP TABLE IF EXISTS types;
9
DROP TABLE IF EXISTS urls;
10
DROP TABLE IF EXISTS openaire_simrels;
11
DROP TABLE IF EXISTS organizations;
12

    
13
DROP TYPE IF EXISTS org_type;
14
DROP TYPE IF EXISTS id_type;
15
DROP TYPE IF EXISTS rel_type;
16
DROP TYPE IF EXISTS simrel_type;
17

    
18
CREATE TYPE org_type AS ENUM ('Archive', 'Company', 'Education', 'Facility', 'Government', 'Healthcare', 'Nonprofit', 'Other');
19
CREATE TYPE id_type  AS ENUM ('CNRS', 'FundRef', 'HESA', 'ISNI', 'OrgRef', 'UCAS', 'UKPRN', 'Wikidata', 'grid.ac');
20
CREATE TYPE rel_type AS ENUM ('Child', 'Parent', 'Related', 'Other');
21
CREATE TYPE simrel_type AS ENUM ('suggested', 'is_similar', 'is_different');
22

    
23

    
24
CREATE TABLE organizations (
25
    id      text PRIMARY KEY,
26
    name    text,
27
    lat     double precision,
28
	lng     double precision,
29
	city    text,
30
	country text,
31
	source  text
32
);
33

    
34
CREATE TABLE other_ids (
35
	id      text REFERENCES organizations(id),
36
	otherid text,
37
	type    id_type,
38
	PRIMARY KEY (id, otherid, type)
39
);
40

    
41
CREATE TABLE other_names (
42
	id    text REFERENCES organizations(id),
43
	name  text,
44
	lang  text DEFAULT '',
45
	PRIMARY KEY (id, name, lang)
46
);
47

    
48
CREATE TABLE acronyms (
49
	id text,
50
	acronym text,
51
	PRIMARY KEY (id, acronym)
52
);
53

    
54
CREATE TABLE relationships (
55
	id1     text REFERENCES organizations(id),
56
	reltype rel_type,
57
	id2     text REFERENCES organizations(id),
58
    PRIMARY KEY (id1, reltype, id2)
59
);
60

    
61
CREATE TABLE types (
62
	id   text REFERENCES organizations(id),
63
	type org_type,
64
	PRIMARY KEY (id, type)
65
);
66

    
67
CREATE TABLE urls (
68
	id  text REFERENCES organizations(id),
69
	url text,
70
	PRIMARY KEY (id, url)
71
);
72

    
73
CREATE TABLE openaire_simrels (
74
	local_id         text REFERENCES organizations(id),
75
	oa_id            text,
76
	oa_name          text,
77
	oa_acronym       text,
78
	oa_country       text,
79
	oa_url           text,
80
	oa_collectedfrom text,
81
	reltype          simrel_type,
82
	PRIMARY KEY (local_id, oa_id)
83
);
84

    
85
CREATE VIEW organizations_view AS SELECT
86
    org.id,
87
    org.name,
88
    org.lat,
89
	org.lng,
90
	org.city,
91
	org.country,
92
	org.source,
93
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type))                                   FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
94
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name',          n.name, 'lang', n.lang))                               FILTER (WHERE n.name      IS NOT NULL), '[]') AS other_names,
95
	COALESCE(jsonb_agg(DISTINCT a.acronym)                                                                                 FILTER (WHERE a.acronym   IS NOT NULL), '[]') AS acronyms,	
96
	COALESCE(jsonb_agg(DISTINCT t.type)                                                                                    FILTER (WHERE t.type      IS NOT NULL), '[]') AS types,
97
	COALESCE(jsonb_agg(DISTINCT u.url)                                                                                     FILTER (WHERE u.url       IS NOT NULL), '[]') AS urls
98
FROM
99
    organizations org
100
    LEFT OUTER JOIN other_ids oid    ON (org.id = oid.id)
101
    LEFT OUTER JOIN other_names n    ON (org.id = n.id)
102
    LEFT OUTER JOIN relationships r  ON (org.id = r.id1)
103
    LEFT OUTER JOIN organizations ro ON (ro.id  = r.id2)
104
    LEFT OUTER JOIN acronyms a       ON (org.id = a.id)
105
    LEFT OUTER JOIN types t          ON (org.id = t.id)
106
    LEFT OUTER JOIN urls u           ON (org.id = u.id)
107
GROUP BY
108
    org.id,
109
    org.name,
110
    org.lat,
111
	org.lng,
112
	org.city,
113
	org.country,
114
	org.source;
115
	
116
CREATE VIEW organizations_simple_view AS SELECT
117
    org.id,
118
    org.name,
119
	org.city,
120
	org.country,
121
	array_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL) AS acronyms,	
122
	array_agg(DISTINCT t.type)    FILTER (WHERE t.type    IS NOT NULL) AS types
123
FROM
124
    organizations org
125
    LEFT OUTER JOIN acronyms a       ON (org.id = a.id)
126
    LEFT OUTER JOIN types t          ON (org.id = t.id)
127
GROUP BY
128
    org.id,
129
    org.name,
130
	org.city,
131
	org.country;
132
	
133
	
(2-2/2)