Revision 57304
Added by Michele Artini over 4 years ago
| schema.sql | ||
|---|---|---|
| 18 | 18 |
DROP TABLE IF EXISTS id_types; |
| 19 | 19 |
DROP TABLE IF EXISTS languages; |
| 20 | 20 |
|
| 21 |
DROP SEQUENCE IF EXISTS organizations_id_seq; |
|
| 21 | 22 |
|
| 22 |
|
|
| 23 | 23 |
CREATE TABLE org_types (val text PRIMARY KEY); |
| 24 |
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other');
|
|
| 24 |
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other'), ('UNKNOWN');
|
|
| 25 | 25 |
|
| 26 | 26 |
CREATE TABLE id_types (val text PRIMARY KEY); |
| 27 | 27 |
INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');
|
| ... | ... | |
| 51 | 51 |
CREATE TABLE users ( |
| 52 | 52 |
email text PRIMARY KEY, |
| 53 | 53 |
password text NOT NULL, |
| 54 |
valid boolean default true,
|
|
| 54 |
valid boolean DEFAULT true,
|
|
| 55 | 55 |
role text NOT NULL default 'USER' REFERENCES user_roles(role) |
| 56 | 56 |
); |
| 57 | 57 |
|
| ... | ... | |
| 61 | 61 |
PRIMARY KEY(email, country) |
| 62 | 62 |
); |
| 63 | 63 |
|
| 64 |
|
|
| 65 |
CREATE SEQUENCE organizations_id_seq; |
|
| 66 |
|
|
| 64 | 67 |
CREATE TABLE organizations ( |
| 65 |
id text PRIMARY KEY, |
|
| 68 |
id text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),
|
|
| 66 | 69 |
name text, |
| 67 |
type text NOT NULL default 'Other' REFERENCES org_types(val),
|
|
| 70 |
type text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),
|
|
| 68 | 71 |
lat double precision, |
| 69 | 72 |
lng double precision, |
| 70 | 73 |
city text, |
| ... | ... | |
| 74 | 77 |
modified_by text, |
| 75 | 78 |
modification_date timestamp with time zone DEFAULT now() |
| 76 | 79 |
); |
| 80 |
CREATE INDEX organizations_type_idx ON organizations(type); |
|
| 81 |
CREATE INDEX organizations_country_idx ON organizations(country); |
|
| 77 | 82 |
|
| 78 | 83 |
CREATE TABLE other_ids ( |
| 79 |
id text REFERENCES organizations(id), |
|
| 84 |
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 80 | 85 |
otherid text, |
| 81 | 86 |
type text REFERENCES id_types(val), |
| 82 | 87 |
PRIMARY KEY (id, otherid, type) |
| 83 | 88 |
); |
| 89 |
CREATE INDEX other_ids_id_idx ON other_ids(id); |
|
| 84 | 90 |
|
| 85 | 91 |
CREATE TABLE other_names ( |
| 86 |
id text REFERENCES organizations(id), |
|
| 92 |
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 87 | 93 |
name text, |
| 88 | 94 |
lang text REFERENCES languages(val), |
| 89 | 95 |
PRIMARY KEY (id, name, lang) |
| 90 | 96 |
); |
| 97 |
CREATE INDEX other_names_id_idx ON other_names(id); |
|
| 91 | 98 |
|
| 92 | 99 |
CREATE TABLE acronyms ( |
| 93 |
id text, |
|
| 100 |
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 94 | 101 |
acronym text, |
| 95 | 102 |
PRIMARY KEY (id, acronym) |
| 96 | 103 |
); |
| 104 |
CREATE INDEX acronyms_id_idx ON acronyms(id); |
|
| 97 | 105 |
|
| 98 | 106 |
CREATE TABLE relationships ( |
| 99 |
id1 text REFERENCES organizations(id), |
|
| 107 |
id1 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 100 | 108 |
reltype text, |
| 101 |
id2 text REFERENCES organizations(id), |
|
| 109 |
id2 text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 102 | 110 |
PRIMARY KEY (id1, reltype, id2) |
| 103 | 111 |
); |
| 112 |
CREATE INDEX relationships_id1_idx ON relationships(id1); |
|
| 113 |
CREATE INDEX relationships_id2_idx ON relationships(id2); |
|
| 104 | 114 |
|
| 105 | 115 |
CREATE TABLE urls ( |
| 106 |
id text REFERENCES organizations(id), |
|
| 116 |
id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 107 | 117 |
url text, |
| 108 | 118 |
PRIMARY KEY (id, url) |
| 109 | 119 |
); |
| 120 |
CREATE INDEX urls_id_idx ON urls(id); |
|
| 110 | 121 |
|
| 111 | 122 |
CREATE TABLE openaire_simrels ( |
| 112 |
local_id text REFERENCES organizations(id), |
|
| 123 |
local_id text REFERENCES organizations(id) ON UPDATE CASCADE,
|
|
| 113 | 124 |
oa_id text, |
| 114 | 125 |
oa_original_id text, |
| 115 | 126 |
oa_name text, |
| ... | ... | |
| 120 | 131 |
reltype text NOT NULL DEFAULT 'suggested', |
| 121 | 132 |
PRIMARY KEY (local_id, oa_id) |
| 122 | 133 |
); |
| 134 |
CREATE INDEX openaire_simrels_local_id_idx ON openaire_simrels(local_id); |
|
| 123 | 135 |
|
| 124 | 136 |
CREATE VIEW organizations_view AS SELECT |
| 125 | 137 |
org.id, |
Also available in: Unified diff
sequential identifiers