1
|
--
|
2
|
-- PostgreSQL database dump
|
3
|
--
|
4
|
|
5
|
-- Dumped from database version 9.4.1
|
6
|
-- Dumped by pg_dump version 9.6.2
|
7
|
|
8
|
SET statement_timeout = 0;
|
9
|
SET lock_timeout = 0;
|
10
|
SET client_encoding = 'UTF8';
|
11
|
SET standard_conforming_strings = on;
|
12
|
SET check_function_bodies = false;
|
13
|
SET client_min_messages = warning;
|
14
|
SET row_security = off;
|
15
|
|
16
|
--
|
17
|
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
|
18
|
--
|
19
|
|
20
|
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
|
21
|
|
22
|
|
23
|
SET search_path = public, pg_catalog;
|
24
|
|
25
|
SET default_tablespace = '';
|
26
|
|
27
|
SET default_with_oids = false;
|
28
|
|
29
|
--
|
30
|
-- Name: dsm_api; Type: TABLE; Schema: public; Owner: dnet
|
31
|
--
|
32
|
|
33
|
CREATE TABLE dsm_api (
|
34
|
id character varying(255) NOT NULL,
|
35
|
protocol character varying(255),
|
36
|
datasource character varying(255),
|
37
|
contentdescription character varying(255) DEFAULT 'metadata'::character varying,
|
38
|
active boolean DEFAULT false,
|
39
|
removable boolean DEFAULT false,
|
40
|
typology character varying(255) DEFAULT 'UNKNOWN'::character varying,
|
41
|
compatibility character varying(255) DEFAULT 'UNKNOWN'::character varying,
|
42
|
compatibility_override character varying(255) DEFAULT null,
|
43
|
metadata_identifier_path character varying(512) DEFAULT NULL::character varying,
|
44
|
last_collection_total integer,
|
45
|
last_collection_date date,
|
46
|
last_collection_mdid character varying(255) DEFAULT NULL::character varying,
|
47
|
last_aggregation_total integer,
|
48
|
last_aggregation_date date,
|
49
|
last_aggregation_mdid character varying(255) DEFAULT NULL::character varying,
|
50
|
last_download_total integer,
|
51
|
last_download_date date,
|
52
|
last_download_objid character varying(255) DEFAULT NULL::character varying,
|
53
|
last_validation_job character varying(255) DEFAULT NULL::character varying,
|
54
|
baseurl text
|
55
|
);
|
56
|
|
57
|
|
58
|
ALTER TABLE dsm_api OWNER TO dnetapi;
|
59
|
ALTER TABLE ONLY dsm_api ADD CONSTRAINT dsm_api_pkey PRIMARY KEY (id);
|
60
|
ALTER TABLE ONLY dsm_api ADD CONSTRAINT dsm_api_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);
|
61
|
|
62
|
--
|
63
|
-- Name: dsm_apiparams; Type: TABLE; Schema: public; Owner: dnet
|
64
|
--
|
65
|
|
66
|
CREATE TABLE dsm_apiparams (
|
67
|
param character varying(255) NOT NULL,
|
68
|
value character varying(255) DEFAULT ''::character varying NOT NULL,
|
69
|
api character varying(255) NOT NULL
|
70
|
);
|
71
|
|
72
|
|
73
|
ALTER TABLE dsm_apiparams OWNER TO dnetapi;
|
74
|
ALTER TABLE ONLY dsm_apiparams ADD CONSTRAINT dsm_apicollection_pkey PRIMARY KEY (api, param);
|
75
|
ALTER TABLE ONLY dsm_apiparams ADD CONSTRAINT dsm_apicollections_api_fkey FOREIGN KEY (api) REFERENCES dsm_api(id);
|
76
|
|
77
|
|
78
|
|
79
|
--
|
80
|
-- Name: dsm_datasource_organization; Type: TABLE; Schema: public; Owner: dnet
|
81
|
--
|
82
|
|
83
|
CREATE TABLE dsm_datasource_organization (
|
84
|
datasource character varying(255) NOT NULL,
|
85
|
organization character varying(255) NOT NULL
|
86
|
);
|
87
|
|
88
|
|
89
|
ALTER TABLE dsm_datasource_organization OWNER TO dnet;
|
90
|
ALTER TABLE ONLY dsm_datasource_organization ADD CONSTRAINT dsm_organization_datasource_pkey PRIMARY KEY (datasource, organization);
|
91
|
ALTER TABLE ONLY dsm_datasource_organization ADD CONSTRAINT dsm_datasource_organization_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);
|
92
|
ALTER TABLE ONLY dsm_datasource_organization ADD CONSTRAINT dsm_datasource_organization_organization_fkey FOREIGN KEY (organization) REFERENCES dsm_organizations(id) ON DELETE CASCADE;
|
93
|
|
94
|
--
|
95
|
-- Name: dsm_datasourcepids; Type: TABLE; Schema: public; Owner: dnet
|
96
|
--
|
97
|
|
98
|
CREATE TABLE dsm_datasourcepids (
|
99
|
datasource character varying(255) NOT NULL,
|
100
|
pid character varying(255) NOT NULL
|
101
|
);
|
102
|
|
103
|
|
104
|
ALTER TABLE dsm_datasourcepids OWNER TO dnet;
|
105
|
ALTER TABLE ONLY dsm_datasourcepids ADD CONSTRAINT dsm_datasourcepids_pkey PRIMARY KEY (datasource, pid);
|
106
|
ALTER TABLE ONLY dsm_datasourcepids ADD CONSTRAINT dsm_datasourcepids_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);
|
107
|
ALTER TABLE ONLY dsm_datasourcepids ADD CONSTRAINT dsm_datasourcepids_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);
|
108
|
|
109
|
|
110
|
--
|
111
|
-- Name: dsm_datasources; Type: TABLE; Schema: public; Owner: dnet
|
112
|
--
|
113
|
|
114
|
CREATE TABLE dsm_datasources (
|
115
|
id character varying(255) NOT NULL,
|
116
|
officialname character varying(512) NOT NULL,
|
117
|
englishname character varying(512),
|
118
|
websiteurl character varying(255),
|
119
|
logourl character varying(255),
|
120
|
contactemail character varying(255),
|
121
|
latitude double precision DEFAULT 0.0,
|
122
|
longitude double precision DEFAULT 0.0,
|
123
|
timezone character varying(10) DEFAULT '0.0'::character varying,
|
124
|
namespaceprefix character(12) NOT NULL,
|
125
|
languages text DEFAULT ARRAY[]::character varying[],
|
126
|
od_contenttypes text,
|
127
|
collectedfrom character varying(255),
|
128
|
dateofvalidation date,
|
129
|
optional1 character varying(255),
|
130
|
optional2 character varying(255),
|
131
|
typology character varying(255) NOT NULL,
|
132
|
provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying,
|
133
|
dateofcollection date DEFAULT ('now'::text)::date NOT NULL,
|
134
|
platform character varying(255),
|
135
|
activationid character varying(255),
|
136
|
description text,
|
137
|
releasestartdate date,
|
138
|
releaseenddate date,
|
139
|
missionstatementurl character varying(512),
|
140
|
dataprovider boolean,
|
141
|
serviceprovider boolean,
|
142
|
databaseaccesstype character varying(32),
|
143
|
datauploadtype character varying(32),
|
144
|
databaseaccessrestriction character varying(32),
|
145
|
datauploadrestriction character varying(32),
|
146
|
versioning boolean,
|
147
|
citationguidelineurl character varying(512),
|
148
|
qualitymanagementkind character varying(32),
|
149
|
pidsystems text,
|
150
|
certificates text,
|
151
|
aggregator character varying(64) DEFAULT 'OPENAIRE'::character varying NOT NULL,
|
152
|
issn character varying(20),
|
153
|
eissn character varying(20),
|
154
|
lissn character varying(20),
|
155
|
registeredby character varying(255),
|
156
|
subjects text DEFAULT ARRAY[]::character varying[],
|
157
|
managed boolean DEFAULT false
|
158
|
);
|
159
|
|
160
|
ALTER TABLE ONLY dsm_datasources ADD CONSTRAINT dsm_datasources_namespaceprefix_key UNIQUE (namespaceprefix);
|
161
|
ALTER TABLE ONLY dsm_datasources ADD CONSTRAINT dsm_datasources_pkey PRIMARY KEY (id);
|
162
|
ALTER TABLE ONLY dsm_datasources ADD CONSTRAINT dsm_datasources_collectedfrom_fkey FOREIGN KEY (collectedfrom) REFERENCES dsm_datasources(id);
|
163
|
|
164
|
CREATE INDEX dsm_datasources_id_idx ON dsm_datasources (id);
|
165
|
CREATE INDEX dsm_datasources_contactemail_idx ON dsm_datasources (contactemail);
|
166
|
CREATE INDEX dsm_datasources_englishname_idx ON dsm_datasources (englishname);
|
167
|
CREATE INDEX dsm_datasources_officialname_idx ON dsm_datasources (officialname);
|
168
|
|
169
|
ALTER TABLE dsm_datasources OWNER TO dnetapi;
|
170
|
|
171
|
--
|
172
|
-- Name: dsm_identities; Type: TABLE; Schema: public; Owner: dnet
|
173
|
--
|
174
|
|
175
|
CREATE TABLE dsm_identities (
|
176
|
pid character varying(255) NOT NULL,
|
177
|
issuertype character varying(255)
|
178
|
);
|
179
|
|
180
|
|
181
|
ALTER TABLE dsm_identities OWNER TO dnetapi;
|
182
|
ALTER TABLE ONLY dsm_identities ADD CONSTRAINT dsm_identities_pkey PRIMARY KEY (pid);
|
183
|
|
184
|
|
185
|
--
|
186
|
-- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnet
|
187
|
--
|
188
|
|
189
|
CREATE TABLE dsm_organizationpids (
|
190
|
organization character varying(255) NOT NULL,
|
191
|
pid character varying(255) NOT NULL
|
192
|
);
|
193
|
|
194
|
ALTER TABLE dsm_organizationpids OWNER TO dnet;
|
195
|
ALTER TABLE ONLY dsm_organizationpids ADD CONSTRAINT dsm_organizationpids_pkey PRIMARY KEY (organization, pid);
|
196
|
ALTER TABLE ONLY dsm_organizationpids ADD CONSTRAINT dsm_organizationpids_organization_fkey FOREIGN KEY (organization) REFERENCES dsm_organizations(id);
|
197
|
|
198
|
|
199
|
--
|
200
|
-- Name: dsm_organizations; Type: TABLE; Schema: public; Owner: dnet
|
201
|
--
|
202
|
|
203
|
CREATE TABLE dsm_organizations (
|
204
|
id character varying(255) NOT NULL,
|
205
|
legalshortname character varying(255),
|
206
|
legalname character varying(255),
|
207
|
websiteurl text,
|
208
|
logourl character varying(255),
|
209
|
ec_legalbody boolean DEFAULT false,
|
210
|
ec_legalperson boolean DEFAULT false,
|
211
|
ec_nonprofit boolean DEFAULT false,
|
212
|
ec_researchorganization boolean DEFAULT false,
|
213
|
ec_highereducation boolean DEFAULT false,
|
214
|
ec_internationalorganizationeurinterests boolean DEFAULT false,
|
215
|
ec_internationalorganization boolean DEFAULT false,
|
216
|
ec_enterprise boolean DEFAULT false,
|
217
|
ec_smevalidated boolean DEFAULT false,
|
218
|
ec_nutscode boolean DEFAULT false,
|
219
|
country character varying(255),
|
220
|
collectedfrom character varying(255),
|
221
|
optional1 character varying(255),
|
222
|
optional2 character varying(255),
|
223
|
dateofcollection date DEFAULT ('now'::text)::date NOT NULL,
|
224
|
provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying
|
225
|
);
|
226
|
|
227
|
|
228
|
ALTER TABLE dsm_organizations OWNER TO dnetapi;
|
229
|
ALTER TABLE ONLY dsm_organizations ADD CONSTRAINT dsm_organizations_pkey PRIMARY KEY (id);
|
230
|
ALTER TABLE ONLY dsm_organizations ADD CONSTRAINT dsm_organizations_collectedfrom_fkey FOREIGN KEY (collectedfrom) REFERENCES dsm_datasources(id);
|
231
|
|
232
|
CREATE INDEX dsm_organizations_country_idx ON dsm_organizations (country);
|
233
|
|
234
|
--
|
235
|
-- Name: search_api; Type: VIEW; Schema: public; Owner: dnet
|
236
|
--
|
237
|
|
238
|
CREATE VIEW search_api AS
|
239
|
SELECT a.id,
|
240
|
a.compatibility,
|
241
|
a.protocol,
|
242
|
a.active,
|
243
|
a.last_aggregation_date,
|
244
|
a.last_aggregation_total,
|
245
|
d.id AS repoid,
|
246
|
d.officialname,
|
247
|
d.englishname,
|
248
|
d.namespaceprefix,
|
249
|
(array_agg(DISTINCT o.country))[1] AS country,
|
250
|
(array_agg(DISTINCT o.legalname))[1] AS organization
|
251
|
FROM (((dsm_api a
|
252
|
LEFT JOIN dsm_datasources d ON (((a.datasource)::text = (d.id)::text)))
|
253
|
LEFT JOIN dsm_datasource_organization dor ON (((d.id)::text = (dor.datasource)::text)))
|
254
|
LEFT JOIN dsm_organizations o ON (((dor.organization)::text = (o.id)::text)))
|
255
|
GROUP BY a.id, a.compatibility, a.protocol, a.active, a.last_aggregation_date, a.last_aggregation_total, d.id, d.officialname, d.englishname, d.namespaceprefix;
|
256
|
|
257
|
|
258
|
ALTER TABLE search_api OWNER TO dnetapi;
|
259
|
|
260
|
CREATE VIEW browse_activation AS
|
261
|
SELECT dsm_api.active::text AS term,
|
262
|
count(*) AS total
|
263
|
FROM dsm_api
|
264
|
GROUP BY dsm_api.active
|
265
|
ORDER BY count(*) DESC;
|
266
|
|
267
|
CREATE VIEW browse_compatibility AS
|
268
|
SELECT a.compatibility AS term,
|
269
|
count(*) AS total
|
270
|
FROM dsm_api a
|
271
|
GROUP BY a.compatibility
|
272
|
ORDER BY count(*) DESC;
|
273
|
|
274
|
|
275
|
|
276
|
CREATE VIEW browse_countries AS
|
277
|
SELECT o.country AS term,
|
278
|
count(*) AS total
|
279
|
FROM dsm_api a
|
280
|
LEFT JOIN dsm_datasources d ON a.datasource::text = d.id::text
|
281
|
LEFT JOIN dsm_datasource_organization dao ON d.id::text = dao.datasource::text
|
282
|
LEFT JOIN dsm_organizations o ON dao.organization::text = o.id::text
|
283
|
GROUP BY o.country
|
284
|
ORDER BY count(*) DESC;
|
285
|
|
286
|
|
287
|
CREATE VIEW browse_protocols AS
|
288
|
SELECT a.protocol AS term,
|
289
|
count(*) AS total
|
290
|
FROM dsm_api a
|
291
|
GROUP BY a.protocol
|
292
|
ORDER BY count(*) DESC;
|
293
|
|
294
|
CREATE VIEW browse_typologies AS
|
295
|
SELECT a.typology AS term,
|
296
|
count(*) AS total
|
297
|
FROM dsm_api a
|
298
|
GROUP BY a.typology
|
299
|
ORDER BY count(*) DESC;
|
300
|
|