Project

General

Profile

1

    
2
CREATE TABLE dsm_api (
3
    id character varying(255) NOT NULL,
4
    protocol character varying(255),
5
    datasource character varying(255),
6
    contentdescription character varying(255) DEFAULT 'metadata'::character varying,
7
    active boolean DEFAULT false,
8
    removable boolean DEFAULT false,
9
    typology character varying(255) DEFAULT 'UNKNOWN'::character varying,
10
    compatibility character varying(255) DEFAULT 'UNKNOWN'::character varying,
11
    metadata_identifier_path character varying(512) DEFAULT NULL::character varying,
12
    last_collection_total integer,
13
    last_collection_date timestamp without time zone,
14
    last_collection_mdid character varying(255) DEFAULT NULL::character varying,
15
    last_aggregation_total integer,
16
    last_aggregation_date timestamp without time zone,
17
    last_aggregation_mdid character varying(255) DEFAULT NULL::character varying,
18
    last_download_total integer,
19
    last_download_date timestamp without time zone,
20
    last_download_objid character varying(255) DEFAULT NULL::character varying,
21
    last_validation_job character varying(255) DEFAULT NULL::character varying,
22
    baseurl text,
23
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
24
    compatibility_override character varying(255) DEFAULT NULL::character varying
25
);
26

    
27

    
28
ALTER TABLE public.dsm_api OWNER TO dnetapi;
29

    
30

    
31
CREATE TABLE dsm_datasource_organization (
32
    datasource character varying(255) NOT NULL,
33
    organization character varying(255) NOT NULL,
34
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
35
);
36

    
37

    
38
ALTER TABLE public.dsm_datasource_organization OWNER TO dnetapi;
39

    
40
--
41
-- Name: dsm_datasources; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
42
--
43

    
44
CREATE TABLE dsm_datasources (
45
    id character varying(255) NOT NULL,
46
    officialname character varying(512) NOT NULL,
47
    englishname character varying(512),
48
    websiteurl character varying(255),
49
    logourl character varying(255),
50
    contactemail character varying(255),
51
    latitude double precision DEFAULT 0.0,
52
    longitude double precision DEFAULT 0.0,
53
    timezone character varying(10) DEFAULT '0.0'::character varying,
54
    namespaceprefix character(12) NOT NULL,
55
    languages text,
56
    od_contenttypes text,
57
    collectedfrom character varying(255),
58
    dateofvalidation date,
59
    optional1 character varying(255),
60
    optional2 character varying(255),
61
    typology character varying(255) NOT NULL,
62
    provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying,
63
    dateofcollection date DEFAULT ('now'::text)::date NOT NULL,
64
    platform character varying(255),
65
    activationid character varying(255),
66
    description text,
67
    releasestartdate date,
68
    releaseenddate date,
69
    missionstatementurl character varying(512),
70
    dataprovider boolean,
71
    serviceprovider boolean,
72
    databaseaccesstype character varying(64),
73
    datauploadtype character varying(64),
74
    databaseaccessrestriction character varying(64),
75
    datauploadrestriction character varying(64),
76
    versioning boolean,
77
    citationguidelineurl character varying(512),
78
    qualitymanagementkind character varying(64),
79
    pidsystems text,
80
    certificates text,
81
    aggregator character varying(64) DEFAULT 'OPENAIRE'::character varying NOT NULL,
82
    issn character varying(20),
83
    eissn character varying(20),
84
    lissn character varying(20),
85
    registeredby character varying(255),
86
    subjects text,
87
    managed boolean DEFAULT false,
88
    registrationdate date,
89
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
90
);
91

    
92

    
93
ALTER TABLE public.dsm_datasources OWNER TO dnetapi;
94

    
95
--
96
-- Name: dsm_organizations; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
97
--
98

    
99
CREATE TABLE dsm_organizations (
100
    id character varying(255) NOT NULL,
101
    legalshortname character varying(255),
102
    legalname character varying(255),
103
    websiteurl text,
104
    logourl character varying(255),
105
    ec_legalbody boolean DEFAULT false,
106
    ec_legalperson boolean DEFAULT false,
107
    ec_nonprofit boolean DEFAULT false,
108
    ec_researchorganization boolean DEFAULT false,
109
    ec_highereducation boolean DEFAULT false,
110
    ec_internationalorganizationeurinterests boolean DEFAULT false,
111
    ec_internationalorganization boolean DEFAULT false,
112
    ec_enterprise boolean DEFAULT false,
113
    ec_smevalidated boolean DEFAULT false,
114
    ec_nutscode boolean DEFAULT false,
115
    country character varying(255),
116
    collectedfrom character varying(255),
117
    optional1 character varying(255),
118
    optional2 character varying(255),
119
    dateofcollection date DEFAULT ('now'::text)::date NOT NULL,
120
    provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying,
121
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
122
    lastupdate date DEFAULT ('now'::text)::date NOT NULL,
123
    trust double precision DEFAULT 0.9
124
);
125

    
126

    
127
ALTER TABLE public.dsm_organizations OWNER TO dnetapi;
128

    
129
--
130
-- Name: browse_countries; Type: VIEW; Schema: public; Owner: dnet
131
--
132

    
133
CREATE VIEW browse_countries AS
134
 SELECT o.country AS term,
135
    count(*) AS total
136
   FROM (((dsm_api a
137
     LEFT JOIN dsm_datasources d ON (((a.datasource)::text = (d.id)::text)))
138
     LEFT JOIN dsm_datasource_organization dao ON (((d.id)::text = (dao.datasource)::text)))
139
     LEFT JOIN dsm_organizations o ON (((dao.organization)::text = (o.id)::text)))
140
  GROUP BY o.country
141
  ORDER BY count(*) DESC;
142

    
143

    
144
ALTER TABLE public.browse_countries OWNER TO dnet;
145

    
146

    
147

    
148
CREATE TABLE dsm_apiparams (
149
    param character varying(255) NOT NULL,
150
    value text DEFAULT ''::character varying NOT NULL,
151
    api character varying(255) NOT NULL,
152
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
153
);
154

    
155

    
156
ALTER TABLE public.dsm_apiparams OWNER TO dnetapi;
157

    
158
--
159
-- Name: dsm_datasource_api; Type: VIEW; Schema: public; Owner: dnet
160
--
161

    
162
CREATE VIEW dsm_datasource_api AS
163
 SELECT row_number() OVER (ORDER BY a.id) AS rowid,
164
    d.id,
165
    d.officialname,
166
    d.englishname,
167
    d.websiteurl,
168
    d.logourl,
169
    d.contactemail,
170
    d.latitude,
171
    d.longitude,
172
    d.timezone,
173
    d.namespaceprefix,
174
    d.languages,
175
    d.od_contenttypes,
176
    d.collectedfrom,
177
    d.dateofvalidation,
178
    d.optional1,
179
    d.optional2,
180
    d.typology,
181
    d.provenanceaction,
182
    d.dateofcollection,
183
    d.platform,
184
    d.activationid,
185
    d.description,
186
    d.releasestartdate,
187
    d.releaseenddate,
188
    d.missionstatementurl,
189
    d.dataprovider,
190
    d.serviceprovider,
191
    d.databaseaccesstype,
192
    d.datauploadtype,
193
    d.databaseaccessrestriction,
194
    d.datauploadrestriction,
195
    d.versioning,
196
    d.citationguidelineurl,
197
    d.qualitymanagementkind,
198
    d.pidsystems,
199
    d.certificates,
200
    d.aggregator,
201
    d.issn,
202
    d.eissn,
203
    d.lissn,
204
    d.registeredby,
205
    d.subjects,
206
    d.managed,
207
    a.protocol,
208
    a.contentdescription,
209
    a.active,
210
    a.removable,
211
    a.typology AS apitypology,
212
    a.compatibility,
213
    a.metadata_identifier_path,
214
    a.baseurl
215
   FROM (dsm_datasources d
216
     LEFT JOIN dsm_api a ON (((d.id)::text = (a.datasource)::text)));
217

    
218

    
219
ALTER TABLE public.dsm_datasource_api OWNER TO dnet;
220

    
221
--
222
-- Name: dsm_datasourcepids; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
223
--
224

    
225
CREATE TABLE dsm_datasourcepids (
226
    datasource character varying(255) NOT NULL,
227
    pid character varying(255) NOT NULL,
228
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
229
);
230

    
231

    
232
ALTER TABLE public.dsm_datasourcepids OWNER TO dnetapi;
233

    
234
--
235
-- Name: dsm_identities; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
236
--
237

    
238
CREATE TABLE dsm_identities (
239
    pid character varying(255) NOT NULL,
240
    issuertype character varying(255),
241
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
242
);
243

    
244

    
245
ALTER TABLE public.dsm_identities OWNER TO dnetapi;
246

    
247
--
248
-- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
249
--
250

    
251
CREATE TABLE dsm_organizationpids (
252
    organization character varying(255) NOT NULL,
253
    pid character varying(255) NOT NULL,
254
    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
255
);
256

    
257

    
258
ALTER TABLE public.dsm_organizationpids OWNER TO dnetapi;
259

    
260
--
261
-- Name: funder_identity; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
262
--
263

    
264
CREATE TABLE funder_identity (
265
    funder character varying(255),
266
    pid character varying(255)
267
);
268

    
269

    
270
ALTER TABLE public.funder_identity OWNER TO dnet;
271

    
272
--
273
-- Name: funders; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
274
--
275

    
276
CREATE TABLE funders (
277
    id character varying(255) NOT NULL,
278
    name character varying(255),
279
    shortname character varying(255),
280
    jurisdiction character varying(255),
281
    websiteurl text,
282
    policy character varying(255),
283
    registrationdate date DEFAULT ('now'::text)::date NOT NULL,
284
    lastupdatedate date
285
);
286

    
287

    
288
ALTER TABLE public.funders OWNER TO dnet;
289

    
290
--
291
-- Name: fundingpaths; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
292
--
293

    
294

    
295

    
296

    
297

    
298

    
299

    
300

    
301

    
302

    
303

    
304

    
305

    
306

    
307
--
308
-- Name: project_organization; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
309
--
310

    
311
--CREATE TABLE project_organization (
312
--    participantnumber integer,
313
--    project character varying(255) NOT NULL,
314
--    resporganization character varying(255) NOT NULL,
315
--    semanticclass character varying(255) DEFAULT 'UNKNOWN'::character varying,
316
--    trust double precision DEFAULT 0.9,
317
--    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
318
--);
319

    
320

    
321
--ALTER TABLE public.project_organization OWNER TO dnet;
322

    
323

    
324

    
325

    
326
--
327
-- Name: dsm_api_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
328
--
329

    
330
ALTER TABLE ONLY dsm_api
331
    ADD CONSTRAINT dsm_api_pkey PRIMARY KEY (id);
332

    
333

    
334
--
335
-- Name: dsm_apicollection_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
336
--
337

    
338
ALTER TABLE ONLY dsm_apiparams
339
    ADD CONSTRAINT dsm_apicollection_pkey PRIMARY KEY (api, param);
340

    
341

    
342
--
343
-- Name: dsm_datasourcepids_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
344
--
345

    
346
ALTER TABLE ONLY dsm_datasourcepids
347
    ADD CONSTRAINT dsm_datasourcepids_pkey PRIMARY KEY (datasource, pid);
348

    
349

    
350
--
351
-- Name: dsm_datasources_namespaceprefix_key; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
352
--
353

    
354
ALTER TABLE ONLY dsm_datasources
355
    ADD CONSTRAINT dsm_datasources_namespaceprefix_key UNIQUE (namespaceprefix);
356

    
357

    
358
--
359
-- Name: dsm_datasources_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
360
--
361

    
362
ALTER TABLE ONLY dsm_datasources
363
    ADD CONSTRAINT dsm_datasources_pkey PRIMARY KEY (id);
364

    
365

    
366
--
367
-- Name: dsm_identities_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
368
--
369

    
370
ALTER TABLE ONLY dsm_identities
371
    ADD CONSTRAINT dsm_identities_pkey PRIMARY KEY (pid);
372

    
373

    
374
--
375
-- Name: dsm_organization_datasource_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
376
--
377

    
378
ALTER TABLE ONLY dsm_datasource_organization
379
    ADD CONSTRAINT dsm_organization_datasource_pkey PRIMARY KEY (datasource, organization);
380

    
381

    
382
--
383
-- Name: dsm_organizationpids_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
384
--
385

    
386
ALTER TABLE ONLY dsm_organizationpids
387
    ADD CONSTRAINT dsm_organizationpids_pkey PRIMARY KEY (organization, pid);
388

    
389

    
390
--
391
-- Name: dsm_organizations_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
392
--
393

    
394
ALTER TABLE ONLY dsm_organizations
395
    ADD CONSTRAINT dsm_organizations_pkey PRIMARY KEY (id);
396

    
397

    
398

    
399

    
400

    
401

    
402
--
403
-- Name: dsm_datasources_contactemail_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
404
--
405

    
406
CREATE INDEX dsm_datasources_contactemail_idx ON dsm_datasources USING btree (contactemail);
407

    
408

    
409
--
410
-- Name: dsm_datasources_englishname_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
411
--
412

    
413
CREATE INDEX dsm_datasources_englishname_idx ON dsm_datasources USING btree (englishname);
414

    
415

    
416
--
417
-- Name: dsm_datasources_managed_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
418
--
419

    
420
CREATE INDEX dsm_datasources_managed_idx ON dsm_datasources USING btree (managed);
421

    
422

    
423
--
424
-- Name: dsm_datasources_officialname_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
425
--
426

    
427
CREATE INDEX dsm_datasources_officialname_idx ON dsm_datasources USING btree (officialname);
428

    
429

    
430
--
431
-- Name: dsm_datasources_registeredby_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
432
--
433

    
434
CREATE INDEX dsm_datasources_registeredby_idx ON dsm_datasources USING btree (registeredby);
435

    
436

    
437
--
438
-- Name: dsm_organizations_country_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:
439
--
440

    
441
CREATE INDEX dsm_organizations_country_idx ON dsm_organizations USING btree (country);
442

    
443

    
444

    
445

    
446

    
447

    
448

    
449

    
450
ALTER TABLE ONLY dsm_api
451
    ADD CONSTRAINT dsm_api_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id) ON DELETE CASCADE;
452

    
453

    
454
--
455
-- Name: dsm_apicollections_api_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
456
--
457

    
458
ALTER TABLE ONLY dsm_apiparams
459
    ADD CONSTRAINT dsm_apicollections_api_fkey FOREIGN KEY (api) REFERENCES dsm_api(id);
460

    
461

    
462
--
463
-- Name: dsm_datasource_organization_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
464
--
465

    
466
ALTER TABLE ONLY dsm_datasource_organization
467
    ADD CONSTRAINT dsm_datasource_organization_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);
468

    
469

    
470
--
471
-- Name: dsm_datasource_organization_organization_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
472
--
473

    
474
ALTER TABLE ONLY dsm_datasource_organization
475
    ADD CONSTRAINT dsm_datasource_organization_organization_fkey FOREIGN KEY (organization) REFERENCES dsm_organizations(id) ON DELETE CASCADE;
476

    
477

    
478
--
479
-- Name: dsm_datasourcepids_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
480
--
481

    
482
ALTER TABLE ONLY dsm_datasourcepids
483
    ADD CONSTRAINT dsm_datasourcepids_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);
484

    
485

    
486
--
487
-- Name: dsm_datasourcepids_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
488
--
489

    
490
ALTER TABLE ONLY dsm_datasourcepids
491
    ADD CONSTRAINT dsm_datasourcepids_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);
492

    
493

    
494
--
495
-- Name: dsm_datasources_collectedfrom_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
496
--
497

    
498
ALTER TABLE ONLY dsm_datasources
499
    ADD CONSTRAINT dsm_datasources_collectedfrom_fkey FOREIGN KEY (collectedfrom) REFERENCES dsm_datasources(id);
500

    
501

    
502
--
503
-- Name: dsm_organizationpids_organization_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
504
--
505

    
506
ALTER TABLE ONLY dsm_organizationpids
507
    ADD CONSTRAINT dsm_organizationpids_organization_fkey FOREIGN KEY (organization) REFERENCES dsm_organizations(id);
508

    
509

    
510
--
511
-- Name: dsm_organizationpids_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
512
--
513

    
514
ALTER TABLE ONLY dsm_organizationpids
515
    ADD CONSTRAINT dsm_organizationpids_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);
516

    
517

    
518
--
519
-- Name: dsm_organizations_collectedfrom_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi
520
--
521

    
522
ALTER TABLE ONLY dsm_organizations
523
    ADD CONSTRAINT dsm_organizations_collectedfrom_fkey FOREIGN KEY (collectedfrom) REFERENCES dsm_datasources(id);
524

    
525

    
526

    
527
REVOKE ALL ON SCHEMA public FROM PUBLIC;
528
REVOKE ALL ON SCHEMA public FROM postgres;
529
GRANT ALL ON SCHEMA public TO postgres;
530
GRANT ALL ON SCHEMA public TO PUBLIC;
531

    
532

    
533
REVOKE ALL ON TABLE browse_countries FROM PUBLIC;
534
REVOKE ALL ON TABLE browse_countries FROM dnet;
535
GRANT ALL ON TABLE browse_countries TO dnet;
536
GRANT ALL ON TABLE browse_countries TO dnetapi;
537

    
538

    
539
REVOKE ALL ON TABLE dsm_datasource_api FROM PUBLIC;
540
REVOKE ALL ON TABLE dsm_datasource_api FROM dnet;
541
GRANT ALL ON TABLE dsm_datasource_api TO dnet;
542
GRANT SELECT ON TABLE dsm_datasource_api TO dnetapi;
543

    
544

    
(3-3/6)