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

--

DROP SCHEMA IF EXISTS backup CASCADE;

--

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

    dateofvalidation text,

    yearofvalidation integer,

    harvested TEXT DEFAULT 'false',

    piwik_id integer

);

--

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

--

CREATE TABLE shadow.datasource_languages (

    id text NOT NULL,

    language text

);

--

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

--

CREATE TABLE shadow.datasource_websites (

    id text NOT NULL,

    website text

);

--

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

);

--

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

--

CREATE TABLE shadow.result_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 ,

    funder text,

    funding_lvl0 text,

    funding_lvl1 text,

    funding_lvl2 text,

    funding_lvl3 text,

    sc39 text,

    type text,

    start_year integer,

    end_year integer,

    duration integer,

    haspubs text DEFAULT 'no'::text,

    numpubs integer,

    enddate text,

    startdate text,

    daysforlastpub integer,

    delayedpubs integer,

    callidentifier  text,

    code text

);

--

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

--

 CREATE TABLE shadow.project_oids (

    id text NOT NULL,

    orid text

);

 CREATE TABLE shadow.result_oids (

    id text NOT NULL,

    orid text

);

 CREATE TABLE shadow.organization_oids (

    id text NOT NULL,

   orid text

);

 CREATE TABLE shadow.datasource_oids (

    id text NOT NULL,

   orid text

);

-- CREATE TABLE shadow.person_oids (

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

);

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

  publisher text,

  journal text,

  year integer,

  date text ,

  bestlicense text,

  type text ,

  embargo_end_date text,

  delayed text,

  authors integer

);

--

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

--

CREATE TABLE shadow.result_extra (

  id text NOT NULL,

  title text,

  source text

);

--

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

--

CREATE TABLE shadow.result_claims (

    id text NOT NULL,

    claim text NOT NULL

);

CREATE TABLE shadow.result_citations (

  id text NOT NULL,

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

--

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

);

CREATE TABLE shadow.result_descriptions (

    id text NOT NULL,

    description  text

);

--

-- DATASOURCE Extra Inserts

--

-- CREATE TABLE shadow.person (

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

-- );

-- CREATE TABLE shadow.person_results (

--     id text NOT NULL,

--    result text NOT NULL

-- );

-- CREATE TABLE shadow.project_persons (

--  id text NOT NULL,

--  person text NOT NULL

-- );

CREATE TABLE shadow.result_pids (

  id text NOT NULL,

  type text ,

  pid text

);

CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource()

 RETURNS void AS

$BODY$

BEGIN

INSERT INTO "shadow".datasource (id,name,type,compatibility) (SELECT 'other','Other','Repository','unknown' 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';

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

UPDATE shadow.datasource set harvested ='true' WHERE datasource.id IN (SELECT DISTINCT d.id FROM datasource d, result_datasources rd where d.id=rd.datasource);

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.id

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

 and  "shadow".project.id= "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

UPDATE shadow.project SET haspubs='yes' WHERE id IN (SELECT pr.id FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='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

UPDATE shadow.project p SET numpubs=prr.np FROM (SELECT pr.id, count(distinct pr.result) AS np FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='publication' GROUP BY pr.id) AS prr WHERE prr.id=p.id;

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

UPDATE shadow.project pp SET delayedpubs = prr.dp FROM (SELECT p.id, count(distinct r.id) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;

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

UPDATE shadow.project pp SET daysforlastpub = prr.dp FROM (SELECT p.id, max(to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD')) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;

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

UPDATE shadow.result SET delayed = 'yes' WHERE id IN (SELECT distinct r.id FROM result r, project_results pr, project p WHERE r.id=pr.result AND pr.id=p.id AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 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_arrays(); Type FUNCTION; Schema: shadow; Owner: sqoop

--

CREATE OR REPLACE FUNCTION shadow.create_arrays()

  RETURNS void AS

$BODY$

BEGIN

ALTER TABLE shadow.result ADD COLUMN funders text[], ADD COLUMN funding_lvl0 text[], ADD COLUMN projects text[], ADD COLUMN datasources text[];

UPDATE shadow.result r SET funders = prr.funders FROM (SELECT pr.result AS rid, array_agg(distinct funder) AS funders FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;

UPDATE shadow.result r SET funding_lvl0 = prr.funding_lvl0 FROM (SELECT pr.result AS rid, array_agg(distinct funding_lvl0) AS funding_lvl0 FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;

UPDATE shadow.result r SET projects = prr.ids FROM (SELECT pr.result AS rid, array_agg(distinct pr.id) AS ids FROM shadow.project_results pr GROUP BY pr.result) AS prr WHERE r.id = prr.rid;

-- UPDATE shadow.result r SET datasources = drr.ids FROM (SELECT rd.id AS rid, array_agg(distinct rd.datasource) AS ids FROM shadow.result_datasources rd GROUP BY rd.id) AS drr WHERE r.id = drr.rid;

CREATE TABLE shadow.result_temp AS SELECT r.id, publisher, journal, year, date, bestlicense, type, embargo_end_date, delayed, authors, funders, funding_lvl0, projects, array_agg(distinct rd.datasource) as datasources FROM shadow.result r, shadow.result_datasources rd WHERE r.id=rd.id GROUP BY r.id, r.publisher, r.journal, r.year, r.date, r.bestlicense, r.type, r.embargo_end_date, r.delayed, r.authors, r.funders, r.funding_lvl0, r.projects;

DROP TABLE shadow.result CASCADE;

ALTER TABLE shadow.result_temp RENAME TO result;

END

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION shadow.create_arrays()

  OWNER TO sqoop;

--

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

--

CREATE FUNCTION shadow.create_indexes() RETURNS void

    LANGUAGE plpgsql

    AS $$

BEGIN

CREATE INDEX datasource_id ON "shadow".datasource USING btree (id);

CREATE INDEX datasource_type ON   "shadow".datasource USING btree (type);

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

CREATE INDEX datasource_piwik_id ON "shadow".datasource USING btree (piwik_id);

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

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

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_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_id ON "shadow".result_oids USING btree (id COLLATE pg_catalog."default");

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

CREATE INDEX result_pids_id ON "shadow".result_pids USING btree (id 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 result_extra_id ON "shadow".result_extra USING btree(id COLLATE pg_catalog."default");

CREATE INDEX datasource_oids_id ON "shadow".datasource_oids USING btree (id COLLATE pg_catalog."default");

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

CREATE INDEX datasource_websites_id ON "shadow".datasource_websites USING btree(id COLLATE pg_catalog."default");

CREATE INDEX datasource_lang_id

   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 proj_funder

  ON shadow.project

  USING btree

  (funder COLLATE pg_catalog."default");

CREATE INDEX proj_title

  ON shadow.project

  USING btree

  (title COLLATE pg_catalog."default");

CREATE INDEX proj_fndlvl0

  ON shadow.project

  USING btree

  (funding_lvl0 COLLATE pg_catalog."default");

CREATE INDEX proj_fndlvl1

  ON shadow.project

  USING btree

  (funding_lvl1 COLLATE pg_catalog."default");

CREATE INDEX proj_fndlvl2

  ON shadow.project

  USING btree

  (funding_lvl2 COLLATE pg_catalog."default");

CREATE INDEX proj_org_org

  ON shadow.project_organizations

  USING btree

  (organization COLLATE pg_catalog."default");

CREATE INDEX proj_org_id

  ON shadow.project_organizations

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_res_id

  ON shadow.result_results

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_res_res

  ON shadow.result_results

  USING btree

  (result COLLATE pg_catalog."default");

CREATE INDEX res_lang_id

  ON shadow.result_languages

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_conc_id

  ON shadow.result_concepts

  USING btree

  (id COLLATE pg_catalog."default");

CREATE INDEX res_conc_conc

  ON shadow.result_concepts

  USING btree

  (concept COLLATE pg_catalog."default");

CREATE INDEX res_bestlicense

  ON shadow.result

  USING btree

  (bestlicense COLLATE pg_catalog."default");

CREATE INDEX proj_sc39

  ON shadow.project

  USING btree

  (sc39 COLLATE pg_catalog."default");

CREATE INDEX proj_fndlvl3

  ON shadow.project

  USING btree

  (funding_lvl3 COLLATE pg_catalog."default");

CREATE INDEX dtsrc_type

  ON shadow.datasource

  USING btree

  (type COLLATE pg_catalog."default");

CREATE INDEX dtsrc_name

  ON shadow.datasource

  USING btree

  (name COLLATE pg_catalog."default");

CREATE INDEX dtsrc_compatibility

  ON shadow.datasource

  USING btree

  (compatibility COLLATE pg_catalog."default");

CREATE INDEX org_name

  ON shadow.organization

  USING btree

  (name COLLATE pg_catalog."default");

CREATE INDEX dtsrc_org_org

  ON shadow.datasource_organizations

  USING btree

  (organization COLLATE pg_catalog."default");

  CREATE INDEX result_funders_idx ON shadow.result USING gin (funders);

  CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0);

  CREATE INDEX result_projects_idx ON shadow.result USING gin (projects);

  CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources);

  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.id = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND

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

  END;$$;

--

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

--

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

    LANGUAGE plpgsql

    AS $$

BEGIN

-- country nums

CREATE TABLE "shadow".numbers_country AS SELECT org.country AS country, count(distinct rd.datasource) AS datasources, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND rd.datasource=d.id AND d.id=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' GROUP BY org.country;

-- country charts

CREATE TABLE "shadow".chart_country_year AS SELECT org.country AS country, r.year AS year, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND rd.datasource=d.id AND rd.datasource=dor.id AND dor.organization=org.id AND r.type='publication' AND r.bestlicense='Open Access' and r.year>='1990' AND r.year<=extract(YEAR from now()) group by org.country, r.year ORDER BY org.country, r.year;