D-Net

--

-- sqoopQL database dump

--

SET statement_timeout = 0;

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,

    name text ,

    category text

);

--

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

--

CREATE TABLE shadow.datasource (

    id text NOT NULL,

    name text ,

    type text,

    compatibility text,

    latitude text,

    longitude text,

    dateofvalidation text,

    yearofvalidation integer,

    url text,

    entityType text,

);

--

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

--

CREATE TABLE shadow.datasource_languages (

    id text NOT NULL,

    language text NOT NULL

);

--

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

--

--

CREATE TABLE shadow.datasource_organizations (

    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

--

CREATE VIEW shadow.datasource_results AS

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

--

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

--

--

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

--

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,

    name text,

    context text

);

--

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

--

CREATE TABLE shadow.claim (

    id text NOT NULL,

    date text ,

    userid text

);

--

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

--

CREATE TABLE shadow.context (

    id text NOT NULL,

    name text

);

--

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

--

CREATE TABLE shadow.organization (

    id text NOT NULL,

    name text,

    country text,

    entityType text

);

--

-- 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: 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,

    acronym text ,

    title text ,

    funding_lvl0 text,

    funding_lvl1 text,

    funding_lvl2 text,

    sc39 text,

    url text,

    start_year integer,

    end_year integer,

    duration integer,

    haspubs text DEFAULT 'no'::text,

    numpubs integer,

    enddate text,

    startdate text,

    daysforlastpub integer,

    delayedpubs integer,

     entityType text

);

--

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

--

--ALTER TABLE shadow.result_projects OWNER TO sqoop;

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,

   cleanedId text,

    title text,

    alterntitles text,

    publisher text,

    journal text,

    year integer,

    date text ,

    access_mode text,

    bestlicense text,

    type text ,

    embargo_end_date text,

    delayed text,

    authors integer,

    authorList text ,

    extReferences text,

    originalIds text

);

--

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

--

CREATE TABLE shadow.result_claims (

    id text NOT NULL,

    claim text NOT NULL

);

--

-- 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: 

--

CREATE TABLE shadow.result_languages (

    id text NOT NULL,

    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,

    topic text NOT NULL

);

--

-- DATASOURCE Extra Inserts

--

CREATE TABLE shadow.person (

    id text NOT NULL,

    firstname text ,

secondname text,

    fullname text ,

    nationality text ,

   coauthorsCount text,

    entityType text

);

CREATE TABLE shadow.person_results (

    id text NOT NULL,

    result text NOT NULL

);

CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource()

 RETURNS void AS

$BODY$

BEGIN

INSERT INTO "shadow".datasource (id,datasource_results,datasource_organizations,datasource_languages,datasource_topics,name,type,compatibility,number) (SELECT 'other','other','other','other','other','Other','Repository','unknown',1 WHERE NOT EXISTS (SELECT 1 FROM "shadow".datasource WHERE name='Unknown Repository'));

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);

TRUNCATE TABLE "shadow".defaults;

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".datasource SET yearofvalidation=null WHERE yearofvalidation='-1';

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.extra_defaults_datasource()

  OWNER TO sqoop;

--

-- Project Results Extra Inserts

--

CREATE OR REPLACE FUNCTION shadow.update_project_results()

  RETURNS void AS

$BODY$

BEGIN 

CREATE OR REPLACE VIEW shadow.project_results_publication AS

SELECT  "shadow".result_projects.id AS result, 

"shadow".result_projects.project AS project_results,

"shadow"."result"."date" as  resultdate,

"shadow"."project"."enddate" as projectenddate,

"shadow".result_projects.daysfromend   

as daysfromend FROM  "shadow".result_projects, 

"shadow".result, 

"shadow".project 

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

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

 and  "shadow".project.project_results= "shadow".result_projects.project;

END

$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$

BEGIN 

 CREATE OR REPLACE VIEW shadow.project_pub_count AS 

 SELECT count(*) AS count, project_results_publication.project_results 

 FROM shadow.project_results_publication

 GROUP BY project_results_publication.project_results;

--HAS PUBS

UPDATE "shadow"."project" SET haspubs='yes' WHERE project_results in (select project_results from shadow.project_results_publication ); 

END

$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$

BEGIN        

 --COUNT PUBS

UPDATE "shadow"."project" SET numpubs=( SELECT  count from  shadow.project_pub_count

WHERE shadow.project_pub_count.project_results = shadow.project.project_results )

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

END

$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

create or replace view shadow.delayedpubs as SELECT count(*) , project_results from "shadow"."project_results_publication" 

 WHERE  "shadow"."project_results_publication".daysfromend > 0 group by project_results;

UPDATE "shadow"."project" SET delayedpubs = (SELECT "shadow"."delayedpubs".count from "shadow"."delayedpubs" 

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

END

$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

--daysforlastpub

UPDATE "shadow"."project" SET daysforlastpub = (

SELECT max(daysfromend) 

FROM "shadow"."project_results_publication" WHERE shadow.project.project_results = shadow.project_results_publication.project_results AND "shadow"."project_results_publication".daysfromend > 0  )

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

END

$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

--delayed

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

(SELECT  result  from shadow.project_results_publication where daysfromend >0);

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.project_delayed()

  OWNER TO sqoop;

--

-- Cleaning Up Temps

-- 

CREATE OR REPLACE FUNCTION shadow.cleanTemps ()

  RETURNS void AS

$BODY$

BEGIN

drop view if exists  shadow.delayedpubs   ;

drop view  if exists  shadow.project_pub_count   ;

drop view if exists  shadow.delayedpubs    ;

drop view  if exists  shadow.project_results_publication   ;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.cleanTemps()

  OWNER TO sqoop;

  ---functions

--

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

--

CREATE FUNCTION shadow.create_indexes() RETURNS void

    LANGUAGE plpgsql

    AS $$

BEGIN 

CREATE INDEX datasource_datasource_languages ON  "shadow".datasource USING btree (datasource_languages);

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_type ON   "shadow".datasource USING btree (type);

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

CREATE INDEX result_access_mode ON   "shadow".result USING btree (access_mode);

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 result_type ON   "shadow"."result" USING btree ("type");

CREATE INDEX project_acronym ON   "shadow"."project" USING btree (acronym);

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);

  END;$$;

ALTER FUNCTION shadow.create_indexes() OWNER TO sqoop;

--

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

--

CREATE or replace FUNCTION "shadow".create_views() RETURNS void

    LANGUAGE plpgsql

    AS $$

BEGIN

CREATE OR REPLACE    VIEW  "shadow".datasource_results   as  SELECT datasource as id  , id  as  result FROM "shadow".result_datasources ;

CREATE OR REPLACE   VIEW "shadow".organization_datasources as SELECT   organization  as id , id as datasource  FROM "shadow".datasource_organizations ;

CREATE OR REPLACE   VIEW "shadow".organization_projects as SELECT  id  as project, organization as id FROM "shadow".project_organizations ;

CREATE OR REPLACE VIEW shadow.result_projects AS SELECT  shadow.project_results.result AS id,

  shadow.project_results.id AS project, ( select to_date("shadow"."result"."date", 'YYYY-MM-DD')- to_date("shadow"."project"."enddate", 'YYYY-MM-DD')  

   from shadow.result, shadow.project where shadow.result.id =  shadow.project_results.result 

   and shadow.project_results.id= shadow.project.id and shadow.result.type='publication' )

 as daysfromend FROM shadow.project_results;

 CREATE OR REPLACE   VIEW "shadow".datasource_topics AS 

 SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic

   FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics

  WHERE "shadow".datasource.datasource_results = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND 

  "shadow".result_topics.id = "shadow".result.result_topics;

  END;$$;