D-Net

--

-- PostgreSQL database dump

--

-- Dumped from database version 9.4.1

-- Dumped by pg_dump version 9.6.2

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET row_security = off;

--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: dsm_api; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_api (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    id character varying(255) NOT NULL,

    protocol character varying(255),

    datasource character varying(255),

    contentdescription character varying(255) DEFAULT 'metadata'::character varying,

    active boolean DEFAULT false,

    removable boolean DEFAULT false,

    typology character varying(255) DEFAULT 'UNKNOWN'::character varying,

    compatibility character varying(255) DEFAULT 'UNKNOWN'::character varying,

    compatibility_override character varying(255) DEFAULT null,

    metadata_identifier_path character varying(512) DEFAULT NULL::character varying,

    last_collection_total integer,

    last_collection_date timestamp without time zone,

    last_collection_mdid character varying(255) DEFAULT NULL::character varying,

    last_aggregation_total integer,

    last_aggregation_date timestamp without time zone,

    last_aggregation_mdid character varying(255) DEFAULT NULL::character varying,

    last_download_total integer,

    last_download_date timestamp without time zone,

    last_download_objid character varying(255) DEFAULT NULL::character varying,

    last_validation_job character varying(255) DEFAULT NULL::character varying,

    baseurl text

);

ALTER TABLE dsm_api OWNER TO dnetapi;

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

ALTER TABLE ONLY dsm_api ADD CONSTRAINT dsm_api_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);

--

-- Name: dsm_apiparams; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_apiparams (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    param character varying(255) NOT NULL,

    value character varying(255) DEFAULT ''::character varying NOT NULL,

    api character varying(255) NOT NULL

);

ALTER TABLE dsm_apiparams OWNER TO dnetapi;

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

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

--

-- Name: dsm_datasource_organization; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_datasource_organization (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    datasource character varying(255) NOT NULL,

    organization character varying(255) NOT NULL

);

ALTER TABLE dsm_datasource_organization OWNER TO dnet;

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

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

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

--

-- Name: dsm_datasourcepids; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_datasourcepids (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    datasource character varying(255) NOT NULL,

    pid character varying(255) NOT NULL

);

ALTER TABLE dsm_datasourcepids OWNER TO dnet;

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

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

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

--

-- Name: dsm_datasources; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_datasources (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    id character varying(255) NOT NULL,

    officialname character varying(512) NOT NULL,

    englishname character varying(512),

    websiteurl character varying(255),

    logourl character varying(255),

    contactemail character varying(255),

    latitude double precision DEFAULT 0.0,

    longitude double precision DEFAULT 0.0,

    timezone character varying(10) DEFAULT '0.0'::character varying,

    namespaceprefix character(12) NOT NULL,

    languages text DEFAULT ARRAY[]::character varying[],

    od_contenttypes text,

    collectedfrom character varying(255),

    dateofvalidation date,

    optional1 character varying(255),

    optional2 character varying(255),

    typology character varying(255) NOT NULL,

    provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying,

    dateofcollection date DEFAULT ('now'::text)::date,

    platform character varying(255),

    activationid character varying(255),

    description text,

    releasestartdate date,

    releaseenddate date,

    missionstatementurl character varying(512),

    dataprovider boolean,

    serviceprovider boolean,

    databaseaccesstype character varying(32),

    datauploadtype character varying(32),

    databaseaccessrestriction character varying(32),

    datauploadrestriction character varying(32),

    versioning boolean,

    citationguidelineurl character varying(512),

    qualitymanagementkind character varying(32),

    pidsystems text,

    certificates text,

    aggregator character varying(64) DEFAULT 'OPENAIRE'::character varying,

    issn character varying(20),

    eissn character varying(20),

    lissn character varying(20),

    registeredby character varying(255),

    registrationdate date,

    subjects text DEFAULT ARRAY[]::character varying[],

    managed boolean DEFAULT false

);

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

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

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

CREATE INDEX dsm_datasources_id_idx ON dsm_datasources (id);

CREATE INDEX dsm_datasources_contactemail_idx ON dsm_datasources (contactemail);

CREATE INDEX dsm_datasources_englishname_idx ON dsm_datasources (englishname);

CREATE INDEX dsm_datasources_officialname_idx ON dsm_datasources (officialname);

ALTER TABLE dsm_datasources OWNER TO dnetapi;

--

-- Name: dsm_identities; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_identities (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    pid character varying(255) NOT NULL,

    issuertype character varying(255)

);

ALTER TABLE dsm_identities OWNER TO dnetapi;

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

--

-- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_organizationpids (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    organization character varying(255) NOT NULL,

    pid character varying(255) NOT NULL

);

ALTER TABLE dsm_organizationpids OWNER TO dnet;

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

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

--

-- Name: dsm_organizations; Type: TABLE; Schema: public; Owner: dnet

--

CREATE TABLE dsm_organizations (

    _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    id character varying(255) NOT NULL,

    legalshortname character varying(255),

    legalname character varying(255),

    websiteurl text,

    logourl character varying(255),

    country character varying(255),

    collectedfrom character varying(255),

    optional1 character varying(255),

    optional2 character varying(255),

    dateofcollection date DEFAULT ('now'::text)::date NOT NULL,

    provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying

);

ALTER TABLE dsm_organizations OWNER TO dnetapi;

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

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

CREATE INDEX dsm_organizations_country_idx ON dsm_organizations (country);

CREATE TABLE funders (

  id character varying(255),

  name character varying(255),

  shortname character varying(255),

  jurisdiction character varying(255),

  websiteurl text,

  policy character varying(255),

  registrationdate date DEFAULT ('now'::text)::date NOT NULL,

  lastupdatedate date

);

GRANT ALL ON funders to dnetapi ;

ALTER TABLE ONLY funders ADD CONSTRAINT funders_pkey PRIMARY KEY (id);

CREATE TABLE funder_identity (

  funder character varying(255),

  pid character varying(255)

);

GRANT ALL ON funder_identity to dnetapi ;

ALTER TABLE ONLY funder_identity ADD CONSTRAINT funder_identity_funder_fkey FOREIGN KEY (funder) REFERENCES funders(id);

ALTER TABLE ONLY funder_identity ADD CONSTRAINT funder_identity_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);

ALTER TABLE fundingpaths ADD COLUMN funderid varchar(255) ;

ALTER TABLE fundingpaths ADD CONSTRAINT fundingpaths_funderid_fkey FOREIGN KEY (funderid) REFERENCES funders(id);

--

-- Name: search_api; Type: VIEW; Schema: public; Owner: dnet

--

CREATE VIEW search_api AS

 SELECT a.id,

    a.compatibility,

    a.protocol,

    a.active,

    a.last_aggregation_date,

    a.last_aggregation_total,

    d.id AS repoid,

    d.officialname,

    d.englishname,

    d.namespaceprefix,

    (array_agg(DISTINCT o.country))[1] AS country,

    (array_agg(DISTINCT o.legalname))[1] AS organization

   FROM (((dsm_api a

     LEFT JOIN dsm_datasources d ON (((a.datasource)::text = (d.id)::text)))

     LEFT JOIN dsm_datasource_organization dor ON (((d.id)::text = (dor.datasource)::text)))

     LEFT JOIN dsm_organizations o ON (((dor.organization)::text = (o.id)::text)))

  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;

ALTER TABLE search_api OWNER TO dnetapi;

CREATE VIEW browse_activation AS

 SELECT dsm_api.active::text AS term,

    count(*) AS total

   FROM dsm_api

  GROUP BY dsm_api.active

  ORDER BY count(*) DESC;

CREATE VIEW browse_compatibility AS

 SELECT a.compatibility AS term,

    count(*) AS total

   FROM dsm_api a

  GROUP BY a.compatibility

  ORDER BY count(*) DESC;

CREATE VIEW browse_countries AS

 SELECT o.country AS term,

    count(*) AS total

   FROM dsm_api a

     LEFT JOIN dsm_datasources d ON a.datasource::text = d.id::text

     LEFT JOIN dsm_datasource_organization dao ON d.id::text = dao.datasource::text

     LEFT JOIN dsm_organizations o ON dao.organization::text = o.id::text

  GROUP BY o.country

  ORDER BY count(*) DESC;

CREATE VIEW browse_protocols AS

 SELECT a.protocol AS term,

    count(*) AS total

   FROM dsm_api a

  GROUP BY a.protocol

  ORDER BY count(*) DESC;

CREATE VIEW browse_typologies AS

 SELECT a.typology AS term,

    count(*) AS total

   FROM dsm_api a

  GROUP BY a.typology

  ORDER BY count(*) DESC;

CREATE OR REPLACE VIEW dsm_datasource_api AS (

  SELECT

    row_number() OVER (ORDER BY a.id) AS rowid,

    d.officialname,

    d.englishname,

    d.websiteurl,

    d.contactemail,

    d.registeredby,

    d.typology,

    d.platform,

    d.managed,

    a.protocol,

    a.contentdescription,

    a.active,

    a.removable,

    a.typology AS apitypology,

    a.compatibility,

    a.baseurl

  FROM dsm_datasources d LEFT OUTER JOIN dsm_api a ON (d.id = a.datasource));

grant select on dsm_datasource_api to dnetapi ;