D-Net

--

-- sqoopQL database dump

--

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

-- Name: shadow; Type: SCHEMA; Schema: -; Owner: sqoop

--

-- Name: shadow; Type: SCHEMA; Schema: -; Owner: sqoop

--

DROP SCHEMA  IF EXISTS shadow CASCADE;

CREATE SCHEMA shadow;

--ALTER SCHEMA shadow OWNER TO sqoop;

 SET search_path = shadow, pg_catalog;

--

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

--

CREATE TABLE shadow.concept (

    id text NOT NULL,

    category text

-- Name: datasource; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.datasource (

    id text NOT NULL,

    datasource_results text NOT NULL,

    datasource_organizations text NOT NULL,

    datasource_languages text NOT NULL,

    datasource_topics text NOT NULL,

    compatibility text,

    latitude text,

    longitude text,

    dateofvalidation text,

    yearofvalidation integer,

    deletedbyinference TEXT,

);

--

-- Name: datasource_languages; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

-- Name: datasource_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

    id text NOT NULL,

    organization text NOT NULL

);

-- Name: result_datasources; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_datasources (

    id text NOT NULL,

    datasource text NOT NULL

);

-- Name: datasource_results; Type: VIEW; Schema: shadow; Owner: sqoop

--

--    SELECT result_datasources.datasource AS id, result_datasources.id AS result FROM result_datasources;

--

--

CREATE TABLE shadow.defaults (

    tablename text NOT NULL,

    xaxis text,

    yaxis text,

    agg text,

    zaxis text,

    type text,

    tableorder integer NOT NULL

);

--

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

--

CREATE TABLE shadow.category (

    id text NOT NULL,

    context text

--

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

--

CREATE TABLE shadow.claim (

    id text NOT NULL,

    userid text

--

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

--

CREATE TABLE shadow.context (

    id text NOT NULL,

-- Name: organization; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.organization (

    id text NOT NULL,

    organization_datasources text NOT NULL,

    organization_projects text NOT NULL,

  deletedbyinference TEXT,

  number integer DEFAULT 1 NOT NULL

--

-- Name: organization_datasources; Type: VIEW; Schema: shadow; Owner: sqoop

--

CREATE VIEW shadow.organization_datasources AS

    SELECT datasource_organizations.organization AS id, datasource_organizations.id AS datasource FROM datasource_organizations;

--

-- Name: project_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.project_organizations (

    id text NOT NULL,

    organization text NOT NULL

);

-- Name: project_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

    id text NOT NULL,

    organization text NOT NULL

);

-- Name: organization_projects; Type: VIEW; Schema: shadow; Owner: sqoop

--

CREATE VIEW shadow.organization_projects AS

    SELECT project_organizations.id AS project, project_organizations.organization AS id FROM project_organizations;

--

-- Name: project; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.project (

    id text NOT NULL,

    project_organizations text NOT NULL,

    project_results text NOT NULL,

    funding_lvl1 text,

    funding_lvl2 text,

    start_year integer,

    end_year integer,

    duration integer,

    haspubs text DEFAULT 'no'::text,

    numpubs integer,

    enddate text,

    startdate text,

    daysforlastpub integer,

    delayedpubs integer,

    code text,

    ecarticle293 text,

);

--

-- Name: result_projects; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

    id text NOT NULL,

 CREATE TABLE shadow.result_oids (

    id text NOT NULL,

 CREATE TABLE shadow.organization_oids (

    id text NOT NULL,

 CREATE TABLE shadow.datasource_oids (

    id text NOT NULL,

--   id text NOT NULL,

--  orid text

-- );

 CREATE TABLE shadow.project_keywords (

    id text NOT NULL,

    keyword text

);

 CREATE TABLE shadow.project_subjects (

    id text NOT NULL,

    subject text

);

CREATE TABLE shadow.project_results (

    id text NOT NULL,

    result text NOT NULL,

    daysfromend integer

);

--

-- Name: result; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result (

    id text NOT NULL,

    result_topics text NOT NULL,

    result_languages text NOT NULL,

    result_projects text NOT NULL,

    result_datasources text NOT NULL,

    result_classifications text NOT NULL,

    result_infrastructures text NOT NULL,

    result_claims text NOT NULL,

    result_results text NOT NULL,

  format text,

  publisher text,

  journal text,

  year integer,

  date text ,

  access_mode text,

  bestlicense text,

  type text ,

  embargo_end_date text,

  delayed text,

  authors integer,

  source text,

  deletedbyinference TEXT,

  number integer DEFAULT 1 NOT NULL

--

-- Name: result_claims; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_claims (

    id text NOT NULL,

    claim text NOT NULL

);

--  trust text,

--  provenance text,

--  citation text

--

-- Name: result_classifications; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_classifications (

    id text NOT NULL,

    type text NOT NULL

);

--

-- Name: result_concepts; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_concepts (

    id text NOT NULL,

    concept text NOT NULL

);

--

-- Name: result_languages; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

    language text NOT NULL

);

--

-- Name: result_results; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_results (

    id text NOT NULL,

    result text NOT NULL

);

--

-- Name: result_topics; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:

--

CREATE TABLE shadow.result_topics (

    id text NOT NULL,

    id text NOT NULL,

    description  text

);

-- DATASOURCE Extra Inserts

--

--     id text NOT NULL,

--     person_results text NOT NULL,

--   firstname text,

--   lastname text,

--   fullname text ,

--   nationality text ,

--   email text,

--   phone text,

--   deletedbyinference TEXT,

--   number text

-- );

--     id text NOT NULL,

--    result text NOT NULL

-- );

--  id text NOT NULL,

--  person text NOT NULL

-- );

CREATE TABLE shadow.result_pids (

  id text NOT NULL,

  type text ,

  pid text

);

 RETURNS void AS

BEGIN

UPDATE "shadow".datasource SET name='Other' where name='Unknown Repository';

UPDATE "shadow".result_datasources SET datasource=(SELECT id FROM "shadow".datasource WHERE name='Other') WHERE datasource NOT IN (SELECT id FROM "shadow".datasource);

INSERT INTO "shadow".defaults VALUES ('result', 'year', 'number', 'count', '', 'column', 1);

INSERT INTO "shadow".defaults VALUES ('project', 'funding_lvl1', 'number', 'count', '', 'column', 2);

INSERT INTO "shadow".defaults VALUES ('organization', 'country', 'number', 'count', '', 'column',  3);

INSERT INTO "shadow".defaults VALUES ('datasource', 'oa_compatible', 'number', 'count', '', 'column', 4);

UPDATE shadow.project SET funder='FCT' WHERE funder='Fundação para a Ciência e a Tecnologia, I.P.';

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.extra_defaults_datasource()

  OWNER TO sqoop;

--

CREATE OR REPLACE FUNCTION shadow.update_project_results()

  RETURNS void AS

$BODY$

as daysfromend FROM  "shadow".result_projects,

"shadow".result,

"shadow".project

 where  "shadow".result_projects.id="shadow".result.result_projects

 and  "shadow".result.type='publication'

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.update_project_results()

  OWNER TO sqoop;

--

-- Project Has Publications Extra Inserts

--

CREATE OR REPLACE FUNCTION shadow.project_has_pubs()

  RETURNS void AS

$BODY$

 CREATE OR REPLACE VIEW shadow.project_pub_count AS

 SELECT count(*) AS count, project_results_publication.project_results

 GROUP BY project_results_publication.project_results;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_has_pubs()

  OWNER TO sqoop;

--

-- Project Publications Count Updates

--

CREATE OR REPLACE FUNCTION shadow.project_pubs_count()

  RETURNS void AS

$BODY$

 --COUNT PUBS

where "shadow"."project".project_results in (  SELECT "shadow".project_results_publication.project_results   FROM  shadow.project_results_publication  );

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_pubs_count()

  OWNER TO sqoop;

--

-- Project  Delayed Publications

--

CREATE OR REPLACE FUNCTION shadow.project_delayedpubs()

  RETURNS void AS

$BODY$

BEGIN

--delayedpubs PUBS

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_delayedpubs()

  OWNER TO sqoop;

--

-- Project    daysforlastpub

--

CREATE OR REPLACE FUNCTION shadow.project_daysforlastpub()

  RETURNS void AS

$BODY$

BEGIN

where    "shadow".project.project_results in ( select project_results from  "shadow"."delayedpubs" );

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_daysforlastpub()

  OWNER TO sqoop;

--

-- Project    delayed

--

CREATE OR REPLACE FUNCTION shadow.project_delayed ()

  RETURNS void AS

$BODY$

BEGIN

UPDATE "shadow"."result" SET delayed = 'yes' WHERE result.id IN

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_delayed()

  OWNER TO sqoop;

-- Cleaning Up Temps

  RETURNS void AS

$BODY$

BEGIN

drop view  if exists  shadow.project_pub_count   ;

drop view if exists  shadow.delayedpubs    ;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.cleanTemps()

  OWNER TO sqoop;

-- Name: create_indexes(); Type: FUNCTION; Schema: shadow; Owner: sqoop

--

CREATE FUNCTION shadow.create_indexes() RETURNS void

    LANGUAGE plpgsql

    AS $$

CREATE INDEX datasource_datasource_organizations ON   "shadow".datasource USING btree (datasource_organizations);

CREATE INDEX datasource_datasource_results ON   "shadow".datasource USING btree (datasource_results);

CREATE INDEX datasource_datasource_topics ON   "shadow".datasource USING btree (datasource_topics);

CREATE INDEX datasource_name ON   "shadow".datasource USING btree (name);

CREATE INDEX result_authors ON   "shadow".result USING btree (authors);

CREATE INDEX result_id ON   "shadow".result USING btree (id);

CREATE INDEX result_result_datasources ON   "shadow".result USING btree (result_datasources);

CREATE INDEX result_result_languages ON   "shadow".result USING btree (result_languages);

CREATE INDEX result_result_projects ON   "shadow".result USING btree (result_projects);

CREATE INDEX result_result_topics ON   "shadow".result USING btree (result_topics);

CREATE INDEX result_year ON   "shadow".result USING btree (year);

CREATE INDEX result_date ON   "shadow"."result" USING btree ("date");

CREATE INDEX project_enddate ON   "shadow"."project" USING btree (enddate);

CREATE INDEX project_id ON   "shadow"."project" USING btree (id);

CREATE INDEX project_project_results ON   "shadow"."project" USING btree (project_results);

CREATE INDEX project_results_result ON   "shadow"."project_results" USING btree (result);

CREATE INDEX project_results_project ON   "shadow"."project_results" USING btree (id);

CREATE INDEX result_oids_oid ON "shadow".result_oids USING btree(orid COLLATE pg_catalog."default");

CREATE INDEX result_pids_type ON "shadow".result_pids USING btree (type COLLATE pg_catalog."default");

CREATE INDEX result_pids_pid ON "shadow".result_pids USING btree(pid COLLATE pg_catalog."default");

CREATE INDEX datasource_oids_orid ON "shadow".datasource_oids USING btree(orid COLLATE pg_catalog."default");

   ON shadow.datasource_languages (id ASC NULLS LAST);

CREATE INDEX dtsrc_org_id

  ON shadow.datasource_organizations

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_class_type

  ON shadow.result_classifications

  USING btree

  (type COLLATE pg_catalog."default");

CREATE INDEX res_class_id

  ON shadow.result_classifications

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_dtsrc_dtsrc

  ON shadow.result_datasources

  USING btree

  (datasource COLLATE pg_catalog."default");

CREATE INDEX res_dtsrc_id

  ON shadow.result_datasources

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_lang_lang

  ON shadow.result_languages

  USING btree

  (language COLLATE pg_catalog."default");

CREATE INDEX org_id

  ON shadow.organization

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX org_country

  ON shadow.organization

  USING btree

  (country COLLATE pg_catalog."default");

CREATE INDEX org_dtsrc

  ON shadow.organization

  USING btree

  (organization_datasources COLLATE pg_catalog."default");

CREATE INDEX org_proj

  ON shadow.organization

  USING btree

  (organization_projects COLLATE pg_catalog."default");