D-Net

CREATE TABLE dsm_api (

    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,

    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,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

    compatibility_override character varying(255) DEFAULT NULL::character varying

);

ALTER TABLE public.dsm_api OWNER TO dnetapi;

CREATE TABLE dsm_datasource_organization (

    datasource character varying(255) NOT NULL,

    organization character varying(255) NOT NULL,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_datasource_organization OWNER TO dnetapi;

--

-- Name: dsm_datasources; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE TABLE dsm_datasources (

    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,

    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 NOT NULL,

    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(64),

    datauploadtype character varying(64),

    databaseaccessrestriction character varying(64),

    datauploadrestriction character varying(64),

    versioning boolean,

    citationguidelineurl character varying(512),

    qualitymanagementkind character varying(64),

    pidsystems text,

    certificates text,

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

    issn character varying(20),

    eissn character varying(20),

    lissn character varying(20),

    registeredby character varying(255),

    subjects text,

    managed boolean DEFAULT false,

    registrationdate date,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_datasources OWNER TO dnetapi;

--

-- Name: dsm_organizations; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE TABLE dsm_organizations (

    id character varying(255) NOT NULL,

    legalshortname character varying(255),

    legalname character varying(255),

    websiteurl text,

    logourl character varying(255),

    ec_legalbody boolean DEFAULT false,

    ec_legalperson boolean DEFAULT false,

    ec_nonprofit boolean DEFAULT false,

    ec_researchorganization boolean DEFAULT false,

    ec_highereducation boolean DEFAULT false,

    ec_internationalorganizationeurinterests boolean DEFAULT false,

    ec_internationalorganization boolean DEFAULT false,

    ec_enterprise boolean DEFAULT false,

    ec_smevalidated boolean DEFAULT false,

    ec_nutscode boolean DEFAULT false,

    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,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),

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

    trust double precision DEFAULT 0.9

);

ALTER TABLE public.dsm_organizations OWNER TO dnetapi;

--

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

--

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;

ALTER TABLE public.browse_countries OWNER TO dnet;

CREATE TABLE dsm_apiparams (

    param character varying(255) NOT NULL,

    value text DEFAULT ''::character varying NOT NULL,

    api character varying(255) NOT NULL,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_apiparams OWNER TO dnetapi;

--

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

--

CREATE OR REPLACE VIEW dsm_datasource_api AS

    SELECT

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

    d.id,

    d.officialname,

    d.englishname,

    d.websiteurl,

    d.contactemail,

    d.collectedfrom,

    d.typology,

    d.platform,

    d.registeredby,

    d.managed,

    a.protocol,

    a.contentdescription,

    a.active,

    a.removable,

    a.typology AS apitypology,

    a.compatibility,

    a.baseurl

    FROM (dsm_datasources d

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

--

-- Name: dsm_datasourcepids; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE TABLE dsm_datasourcepids (

    datasource character varying(255) NOT NULL,

    pid character varying(255) NOT NULL,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_datasourcepids OWNER TO dnetapi;

--

-- Name: dsm_identities; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE TABLE dsm_identities (

    pid character varying(255) NOT NULL,

    issuertype character varying(255),

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_identities OWNER TO dnetapi;

--

-- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE TABLE dsm_organizationpids (

    organization character varying(255) NOT NULL,

    pid character varying(255) NOT NULL,

    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

);

ALTER TABLE public.dsm_organizationpids OWNER TO dnetapi;

--

-- Name: funder_identity; Type: TABLE; Schema: public; Owner: dnet; Tablespace:

--

CREATE TABLE funder_identity (

    funder character varying(255),

    pid character varying(255)

);

ALTER TABLE public.funder_identity OWNER TO dnet;

--

-- Name: funders; Type: TABLE; Schema: public; Owner: dnet; Tablespace:

--

CREATE TABLE funders (

    id character varying(255) NOT NULL,

    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

);

ALTER TABLE public.funders OWNER TO dnet;

--

-- Name: fundingpaths; Type: TABLE; Schema: public; Owner: dnet; Tablespace:

--

--

-- Name: project_organization; Type: TABLE; Schema: public; Owner: dnet; Tablespace:

--

--CREATE TABLE project_organization (

--    participantnumber integer,

--    project character varying(255) NOT NULL,

--    resporganization character varying(255) NOT NULL,

--    semanticclass character varying(255) DEFAULT 'UNKNOWN'::character varying,

--    trust double precision DEFAULT 0.9,

--    _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))

--);

--ALTER TABLE public.project_organization OWNER TO dnet;

--

-- Name: dsm_api_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_api

    ADD CONSTRAINT dsm_api_pkey PRIMARY KEY (id);

--

-- Name: dsm_apicollection_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_apiparams

    ADD CONSTRAINT dsm_apicollection_pkey PRIMARY KEY (api, param);

--

-- Name: dsm_datasourcepids_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_datasourcepids

    ADD CONSTRAINT dsm_datasourcepids_pkey PRIMARY KEY (datasource, pid);

--

-- Name: dsm_datasources_namespaceprefix_key; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_datasources

    ADD CONSTRAINT dsm_datasources_namespaceprefix_key UNIQUE (namespaceprefix);

--

-- Name: dsm_datasources_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_datasources

    ADD CONSTRAINT dsm_datasources_pkey PRIMARY KEY (id);

--

-- Name: dsm_identities_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_identities

    ADD CONSTRAINT dsm_identities_pkey PRIMARY KEY (pid);

--

-- Name: dsm_organization_datasource_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_datasource_organization

    ADD CONSTRAINT dsm_organization_datasource_pkey PRIMARY KEY (datasource, organization);

--

-- Name: dsm_organizationpids_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_organizationpids

    ADD CONSTRAINT dsm_organizationpids_pkey PRIMARY KEY (organization, pid);

--

-- Name: dsm_organizations_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:

--

ALTER TABLE ONLY dsm_organizations

    ADD CONSTRAINT dsm_organizations_pkey PRIMARY KEY (id);

--

-- Name: dsm_datasources_contactemail_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_datasources_contactemail_idx ON dsm_datasources USING btree (contactemail);

--

-- Name: dsm_datasources_englishname_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_datasources_englishname_idx ON dsm_datasources USING btree (englishname);

--

-- Name: dsm_datasources_managed_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_datasources_managed_idx ON dsm_datasources USING btree (managed);

--

-- Name: dsm_datasources_officialname_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_datasources_officialname_idx ON dsm_datasources USING btree (officialname);

--

-- Name: dsm_datasources_registeredby_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_datasources_registeredby_idx ON dsm_datasources USING btree (registeredby);

--

-- Name: dsm_organizations_country_idx; Type: INDEX; Schema: public; Owner: dnetapi; Tablespace:

--

CREATE INDEX dsm_organizations_country_idx ON dsm_organizations USING btree (country);

ALTER TABLE ONLY dsm_api

    ADD CONSTRAINT dsm_api_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id) ON DELETE CASCADE;

--

-- Name: dsm_apicollections_api_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_apiparams

    ADD CONSTRAINT dsm_apicollections_api_fkey FOREIGN KEY (api) REFERENCES dsm_api(id);

--

-- Name: dsm_datasource_organization_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_datasource_organization

    ADD CONSTRAINT dsm_datasource_organization_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);

--

-- Name: dsm_datasource_organization_organization_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

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_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_datasourcepids

    ADD CONSTRAINT dsm_datasourcepids_datasource_fkey FOREIGN KEY (datasource) REFERENCES dsm_datasources(id);

--

-- Name: dsm_datasourcepids_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_datasourcepids

    ADD CONSTRAINT dsm_datasourcepids_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);

--

-- Name: dsm_datasources_collectedfrom_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_datasources

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

--

-- Name: dsm_organizationpids_organization_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_organizationpids

    ADD CONSTRAINT dsm_organizationpids_organization_fkey FOREIGN KEY (organization) REFERENCES dsm_organizations(id);

--

-- Name: dsm_organizationpids_pid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_organizationpids

    ADD CONSTRAINT dsm_organizationpids_pid_fkey FOREIGN KEY (pid) REFERENCES dsm_identities(pid);

--

-- Name: dsm_organizations_collectedfrom_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnetapi

--

ALTER TABLE ONLY dsm_organizations

    ADD CONSTRAINT dsm_organizations_collectedfrom_fkey FOREIGN KEY (collectedfrom) REFERENCES dsm_datasources(id);

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO PUBLIC;

REVOKE ALL ON TABLE browse_countries FROM PUBLIC;

REVOKE ALL ON TABLE browse_countries FROM dnet;

GRANT ALL ON TABLE browse_countries TO dnet;

GRANT ALL ON TABLE browse_countries TO dnetapi;

REVOKE ALL ON TABLE dsm_datasource_api FROM PUBLIC;

REVOKE ALL ON TABLE dsm_datasource_api FROM dnet;

GRANT ALL ON TABLE dsm_datasource_api TO dnet;

GRANT SELECT ON TABLE dsm_datasource_api TO dnetapi;