/ - Diff - D-Net - D-Net project tracking tool

« Previous | Next »

Revision 54115

Added by Claudio Atzori over 5 years ago

remoed deprecated methods to retrieve the first harvest date from the workflow logger db

     		return Instant.parse(startDate.replaceAll("\\+.*", "Z"));
+    	}
     	@Override
     	@Deprecated
     	@Cacheable("dsm-firstharvestdate-cache")
     	public String firstHarvestDate(final String dsId) throws DsmException {
     		log.warn(String.format("firstHarvestDate(dsId = %s): not using cache", dsId));
     		final Bson query = queryForAggregationHistory(dsId, "collect");
     		try {
     			final Optional<AggregationInfo> first = Utils.stream(getCollection().find(query)
     					.projection(fields)
     					.sort(dbo("system:startHumanDate", 1))
     					.limit(1)
     					.iterator())
     					.map(getMapper())
     					.findFirst();
     			return first.isPresent() ? first.get().getDate() : null;
     		} catch (Throwable e) {
     			throw new DsmException(HttpStatus.SC_INTERNAL_SERVER_ERROR, String.format("error reading first harvesting date", dsId), e);
+    		}
+    	}
     	private Instant asInstant(final AggregationInfo a) {
     		return Instant.parse(a.getDate() + "T00:00:00Z");
+    	}

     	List<AggregationInfo> getAggregationHistory(final String dsId) throws DsmException;
     	@Deprecated
     	String firstHarvestDate(final String dsId) throws DsmException;
     	void dropCache();
+    }

     		return dsInfo;
+    	}
     	@Deprecated
     	private void getFirstHarvestingDate(final String dsId,
     			final CountDownLatch outerLatch,
     			final Queue<Throwable> errors,
     			final DatasourceInfo dsInfo) {
     		Futures.addCallback(
     				executor.submit(() -> mongoLoggerClient.firstHarvestDate(dsId)),
     				new FutureCallback<String>() {
     					@Override
     					public void onSuccess(@Nullable final String firstHarvestDate) {
     						dsInfo.setFirstHarvestDate(firstHarvestDate);
     						outerLatch.countDown();
+    					}
     					@Override
     					public void onFailure(final Throwable e) {
     						log.error(ExceptionUtils.getStackTrace(e));
     						errors.offer(e);
     						outerLatch.countDown();
+    					}
     				}, executor);
+    	}
     	private void getAggregationHistory(final String dsId,
     			final CountDownLatch outerLatch,
     			final Queue<Throwable> errors,

     --
     -- 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),
-...
         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_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
         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 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);
     ALTER TABLE public.dsm_api OWNER TO dnetapi;
     --
     -- 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
         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 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;
     ALTER TABLE public.dsm_datasource_organization OWNER TO dnetapi;
     --
     -- Name: dsm_datasourcepids; Type: TABLE; Schema: public; Owner: dnet
     -- Name: dsm_datasources; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
     --
     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),
-...
         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[],
         languages text,
         od_contenttypes text,
         collectedfrom character varying(255),
         dateofvalidation date,
-...
         optional2 character varying(255),
         typology character varying(255) NOT NULL,
         provenanceaction character varying(255) DEFAULT 'UNKNOWN'::character varying,
         dateofcollection date DEFAULT ('now'::text)::date,
         dateofcollection date DEFAULT ('now'::text)::date NOT NULL,
         platform character varying(255),
         activationid character varying(255),
         description text,
-...
         qualitymanagementkind character varying(32),
         pidsystems text,
         certificates text,
         aggregator character varying(64) DEFAULT 'OPENAIRE'::character varying,
         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,
         subjects text DEFAULT ARRAY[]::character varying[],
         managed boolean DEFAULT false
         _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
     );
     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 public.dsm_datasources OWNER TO dnetapi;
     ALTER TABLE 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: dsm_identities; Type: TABLE; Schema: public; Owner: dnet
     -- 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 VIEW dsm_datasource_api AS
      SELECT row_number() OVER (ORDER BY a.id) AS rowid,
         d.id,
         d.officialname,
         d.englishname,
         d.websiteurl,
         d.logourl,
         d.contactemail,
         d.latitude,
         d.longitude,
         d.timezone,
         d.namespaceprefix,
         d.languages,
         d.od_contenttypes,
         d.collectedfrom,
         d.dateofvalidation,
         d.optional1,
         d.optional2,
         d.typology,
         d.provenanceaction,
         d.dateofcollection,
         d.platform,
         d.activationid,
         d.description,
         d.releasestartdate,
         d.releaseenddate,
         d.missionstatementurl,
         d.dataprovider,
         d.serviceprovider,
         d.databaseaccesstype,
         d.datauploadtype,
         d.databaseaccessrestriction,
         d.datauploadrestriction,
         d.versioning,
         d.citationguidelineurl,
         d.qualitymanagementkind,
         d.pidsystems,
         d.certificates,
         d.aggregator,
         d.issn,
         d.eissn,
         d.lissn,
         d.registeredby,
         d.subjects,
         d.managed,
         a.protocol,
         a.contentdescription,
         a.active,
         a.removable,
         a.typology AS apitypology,
         a.compatibility,
         a.metadata_identifier_path,
         a.baseurl
        FROM (dsm_datasources d
          LEFT JOIN dsm_api a ON (((d.id)::text = (a.datasource)::text)));
     ALTER TABLE public.dsm_datasource_api OWNER TO dnet;
     --
     -- 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 (
         _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)
         issuertype character varying(255),
         _dnet_resource_identifier_ character varying(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text))
     );
     ALTER TABLE dsm_identities OWNER TO dnetapi;
     ALTER TABLE ONLY dsm_identities ADD CONSTRAINT dsm_identities_pkey PRIMARY KEY (pid);
     ALTER TABLE public.dsm_identities OWNER TO dnetapi;
     --
     -- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnet
     -- Name: dsm_organizationpids; Type: TABLE; Schema: public; Owner: dnetapi; Tablespace:
     --
     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
         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 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);
     ALTER TABLE public.dsm_organizationpids OWNER TO dnetapi;
     --
     -- Name: dsm_organizations; Type: TABLE; Schema: public; Owner: dnet
     -- Name: funder_identity; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
     --
     CREATE TABLE dsm_organizations (
         _dnet_resource_identifier_ CHARACTER VARYING(2048) DEFAULT ((('temp_'::text || md5((clock_timestamp())::text)) || '_'::text) || md5((random())::text)),
     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,
         legalshortname character varying(255),
         legalname character varying(255),
         name character varying(255),
         shortname character varying(255),
         jurisdiction 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
         policy character varying(255),
         registrationdate date DEFAULT ('now'::text)::date NOT NULL,
         lastupdatedate date
     );
     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);
     ALTER TABLE public.funders OWNER TO dnet;
     CREATE INDEX dsm_organizations_country_idx ON dsm_organizations (country);
     --
     -- Name: fundingpaths; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
     --
     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
     -- Name: project_organization; Type: TABLE; Schema: public; Owner: dnet; Tablespace:
     --
     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;
     --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 search_api OWNER TO dnetapi;
     --ALTER TABLE public.project_organization OWNER TO dnet;
     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;
     --
     -- Name: dsm_api_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
     --
     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 ONLY dsm_api
         ADD CONSTRAINT dsm_api_pkey PRIMARY KEY (id);
     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;
     --
     -- Name: dsm_apicollection_pkey; Type: CONSTRAINT; Schema: public; Owner: dnetapi; Tablespace:
     --
     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;
     ALTER TABLE ONLY dsm_apiparams
         ADD CONSTRAINT dsm_apicollection_pkey PRIMARY KEY (api, param);
     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 ;
     --
     -- 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;

Also available in: Unified diff

Project

General

Profile

D-Net

Revision 54115

Added by Claudio Atzori over 5 years ago