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