Revision 56332
Added by Michele Artini almost 5 years ago
| schema.sql | ||
|---|---|---|
| 1 | 1 |
DROP VIEW organizations_view; |
| 2 | 2 |
|
| 3 | 3 |
DROP TABLE IF EXISTS other_ids; |
| 4 |
DROP TABLE IF EXISTS labels; |
|
| 4 |
DROP TABLE IF EXISTS other_names; |
|
| 5 |
DROP TABLE IF EXISTS acronyms; |
|
| 5 | 6 |
DROP TABLE IF EXISTS relationships; |
| 6 | 7 |
DROP TABLE IF EXISTS types; |
| 7 | 8 |
DROP TABLE IF EXISTS urls; |
| 8 |
DROP TABLE IF EXISTS acronyms; |
|
| 9 |
DROP TABLE IF EXISTS aliases; |
|
| 10 | 9 |
DROP TABLE IF EXISTS organizations; |
| 11 | 10 |
|
| 12 | 11 |
CREATE TABLE organizations ( |
| ... | ... | |
| 26 | 25 |
PRIMARY KEY (id, otherid, type) |
| 27 | 26 |
); |
| 28 | 27 |
|
| 29 |
CREATE TABLE labels (
|
|
| 28 |
CREATE TABLE other_names (
|
|
| 30 | 29 |
id text REFERENCES organizations(id), |
| 31 |
lang text,
|
|
| 32 |
label text,
|
|
| 33 |
PRIMARY KEY (id, lang, label)
|
|
| 30 |
name text,
|
|
| 31 |
lang text DEFAULT '',
|
|
| 32 |
PRIMARY KEY (id, name, lang)
|
|
| 34 | 33 |
); |
| 35 | 34 |
|
| 35 |
CREATE TABLE acronyms ( |
|
| 36 |
id text, |
|
| 37 |
acronym text, |
|
| 38 |
PRIMARY KEY (id, acronym) |
|
| 39 |
); |
|
| 40 |
|
|
| 36 | 41 |
CREATE TABLE relationships ( |
| 37 | 42 |
id1 text REFERENCES organizations(id), |
| 38 | 43 |
reltype text, |
| ... | ... | |
| 52 | 57 |
PRIMARY KEY (id, url) |
| 53 | 58 |
); |
| 54 | 59 |
|
| 55 |
CREATE TABLE acronyms ( |
|
| 56 |
id text REFERENCES organizations(id), |
|
| 57 |
acronym text, |
|
| 58 |
PRIMARY KEY (id, acronym) |
|
| 59 |
); |
|
| 60 |
|
|
| 61 |
CREATE TABLE aliases ( |
|
| 62 |
id text REFERENCES organizations(id), |
|
| 63 |
alias text, |
|
| 64 |
PRIMARY KEY (id, alias) |
|
| 65 |
); |
|
| 66 |
|
|
| 67 | 60 |
CREATE VIEW organizations_view AS SELECT |
| 68 | 61 |
org.id, |
| 69 | 62 |
org.name, |
| ... | ... | |
| 72 | 65 |
org.city, |
| 73 | 66 |
org.country, |
| 74 | 67 |
org.source, |
| 75 |
COALESCE(json_agg(json_build_object('id',oid.otherid,'type',oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,--TODO AGGIUNGERE DISTINCT
|
|
| 76 |
COALESCE(json_agg(json_build_object('label',l.label,'lang',l.lang)) FILTER (WHERE l.label IS NOT NULL), '[]') AS labels,--TODO AGGIUNGERE DISTINCT
|
|
| 77 |
COALESCE(json_agg(json_build_object('related_to',r.id2,'type',r.reltype)) FILTER (WHERE r.id2 IS NOT NULL), '[]') AS relations,--TODO AGGIUNGERE DISTINCT
|
|
| 78 |
array_to_json(array_remove(array_agg(DISTINCT t.type ), NULL)) as types, |
|
| 79 |
array_to_json(array_remove(array_agg(DISTINCT u.url ), NULL)) as urls, |
|
| 80 |
array_to_json(array_remove(array_agg(DISTINCT ac.acronym), NULL)) as acronyms, |
|
| 81 |
array_to_json(array_remove(array_agg(DISTINCT al.alias ), NULL)) as aliases |
|
| 68 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type)) FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,
|
|
| 69 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name', n.name, 'lang', n.lang)) FILTER (WHERE n.name IS NOT NULL), '[]') AS other_names,
|
|
| 70 |
COALESCE(jsonb_agg(DISTINCT jsonb_build_object('related_to_id', r.id2, 'type', r.reltype, 'related_to_name', ro.name)) FILTER (WHERE r.id2 IS NOT NULL), '[]') AS relations,
|
|
| 71 |
COALESCE(jsonb_agg(DISTINCT a.acronym) FILTER (WHERE a.acronym IS NOT NULL), '[]') AS acronyms, |
|
| 72 |
COALESCE(jsonb_agg(DISTINCT t.type) FILTER (WHERE t.type IS NOT NULL), '[]') AS types, |
|
| 73 |
COALESCE(jsonb_agg(DISTINCT u.url) FILTER (WHERE u.url IS NOT NULL), '[]') AS urls |
|
| 82 | 74 |
FROM |
| 83 | 75 |
organizations org |
| 84 |
LEFT OUTER JOIN other_ids oid ON (org.id = oid.id) |
|
| 85 |
LEFT OUTER JOIN labels l ON (org.id = l.id)
|
|
| 86 |
LEFT OUTER JOIN relationships r ON (org.id = r.id1) |
|
| 87 |
LEFT OUTER JOIN types t ON (org.id = t.id)
|
|
| 88 |
LEFT OUTER JOIN urls u ON (org.id = u.id)
|
|
| 89 |
LEFT OUTER JOIN acronyms ac ON (org.id = ac.id)
|
|
| 90 |
LEFT OUTER JOIN aliases al ON (org.id = al.id)
|
|
| 76 |
LEFT OUTER JOIN other_ids oid ON (org.id = oid.id)
|
|
| 77 |
LEFT OUTER JOIN other_names n ON (org.id = n.id)
|
|
| 78 |
LEFT OUTER JOIN relationships r ON (org.id = r.id1)
|
|
| 79 |
LEFT OUTER JOIN organizations ro ON (ro.id = r.id2)
|
|
| 80 |
LEFT OUTER JOIN acronyms a ON (org.id = a.id)
|
|
| 81 |
LEFT OUTER JOIN types t ON (org.id = t.id)
|
|
| 82 |
LEFT OUTER JOIN urls u ON (org.id = u.id)
|
|
| 91 | 83 |
GROUP BY |
| 92 | 84 |
org.id, |
| 93 | 85 |
org.name, |
| ... | ... | |
| 97 | 89 |
org.country, |
| 98 | 90 |
org.source; |
| 99 | 91 |
|
| 100 |
|
|
| 101 |
|
|
| 102 |
|
|
| 103 |
|
|
| 104 |
|
|
| 105 |
|
|
| 106 |
|
|
| 107 |
|
|
| 108 |
|
|
| 109 |
|
|
| 110 |
|
|
| 111 |
|
|
| 112 |
|
|
| 113 |
|
|
| 114 |
|
|
| 115 |
|
|
| 116 | 92 |
|
Also available in: Unified diff