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,

    latitude text,

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

  title 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

);

--

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

);

CREATE TABLE shadow.result_pids (

  id text NOT NULL,

  type text ,

  pid text

);

CREATE TABLE shadow.country (

  code text NOT NULL,

  name text,

  continent_code text,

  continent_name text

);

-----------

--functions

-----------

CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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.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$;

ALTER FUNCTION shadow.extra_defaults_datasource() OWNER TO sqoop;

--

-- Project Results Extra Inserts

--

CREATE OR REPLACE FUNCTION shadow.update_project_results() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.update_project_results() OWNER TO sqoop;

--

-- Project Has Publications Extra Inserts

--

CREATE OR REPLACE FUNCTION shadow.project_has_pubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.project_has_pubs() OWNER TO sqoop;

--

-- Project Publications Count Updates

--

CREATE OR REPLACE FUNCTION shadow.project_pubs_count() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.project_pubs_count() OWNER TO sqoop;

--

-- Project  Delayed Publications

--

CREATE OR REPLACE FUNCTION shadow.project_delayedpubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.project_delayedpubs() OWNER TO sqoop;

--

-- Project    daysforlastpub

--

CREATE OR REPLACE FUNCTION shadow.project_daysforlastpub() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.project_daysforlastpub() OWNER TO sqoop;

--

-- Project    delayed

--

CREATE OR REPLACE FUNCTION shadow.project_delayed () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.project_delayed() OWNER TO sqoop;

--

-- Cleaning Up Temps

-- 

CREATE OR REPLACE FUNCTION shadow.cleanTemps () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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$;

ALTER FUNCTION shadow.cleanTemps() OWNER TO sqoop;

--

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

--

CREATE OR REPLACE FUNCTION shadow.create_arrays() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 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;

    -- todo fix this query

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

  END $BODY$;

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 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 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 dtsrc_org_org ON shadow.datasource_organizations USING btree (organization COLLATE pg_catalog."default");

    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 res_bestlicense ON shadow.result USING btree (bestlicense 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);

    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 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_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 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 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_name ON shadow.organization USING btree (name 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");

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;

--     CREATE TABLE "shadow".chart_country_datasources AS SELECT org.country AS country, d.name AS datasource, 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, d.name ORDER BY org.country, count(distinct r.id) DESC;

--     CREATE TABLE "shadow".chart_country_type AS SELECT org.country AS country, rc.type AS type, count(distinct r.id) AS publications FROM "shadow".result r, "shadow".result_datasources rd, "shadow".datasource d, "shadow".result_classifications rc, "shadow".datasource_organizations dor, "shadow".organization org WHERE r.id=rd.id AND r.id=rc.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' GROUP BY org.country, rc.type;

--     CREATE TABLE "shadow".chart_country_fp7 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, "shadow".project_results pr, "shadow".project p WHERE r.id=rd.id AND rd.datasource=d.id AND rd.datasource=dor.id AND dor.organization=org.id AND r.id=pr.result AND pr.id=p.id AND p.funding_lvl0='FP7' AND r.type='publication' AND r.year>='1990' AND r.year<=extract(YEAR from now()) GROUP BY org.country, r.year ORDER BY org.country, r.year;

-- --

-- --     -- datasource charts

--     CREATE TABLE "shadow".chart_datasource_type AS SELECT rd.datasource, rc.type, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;

--     CREATE TABLE "shadow".chart_datasource_year AS SELECT rd.datasource, r.year, count(distinct rd.id) FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;

--     CREATE TABLE "shadow".chart_datasource_funders AS SELECT rd.datasource, p.funder, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;

--     CREATE TABLE "shadow".chart_datasource_projects_pubs AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id AND r.type='publication' GROUP BY rd.datasource, p.title;

--     CREATE TABLE "shadow".chart_datasource_projects_data AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id and r.type='dataset' GROUP BY rd.datasource, p.title;

-- --

--     -- project charts

--     CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND p.id=pr.id AND r.year>=p.start_year GROUP BY p.id, r.year;

--     CREATE TABLE "shadow".chart_project_license AS SELECT pr.id, r.bestlicense, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;

--     CREATE TABLE "shadow".chart_project_repos AS SELECT pr.id, d.name, count (distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".datasource d, "shadow".datasource_results dr WHERE r.id=dr.result AND d.id=dr.id AND r.id=pr.result AND r.type='publication' GROUP BY pr.id, d.name;

  END;$$;

--

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

--

CREATE or replace FUNCTION "shadow".create_chart_indexes() RETURNS void LANGUAGE plpgsql AS $$

  BEGIN

    -- country nums

--     CREATE INDEX numbers_country_country ON "shadow".numbers_country USING btree(country);

    -- country charts

--     CREATE INDEX chart_country_year_country ON "shadow".chart_country_year USING btree(country);

--     CREATE INDEX chart_country_datasources_country ON "shadow".chart_country_datasources USING btree(country);

--     CREATE INDEX chart_country_type_country ON "shadow".chart_country_type USING btree(country);

--     CREATE INDEX chart_country_fp7_country ON "shadow".chart_country_fp7 USING btree(country);

    -- datasource

--     CREATE INDEX chart_datasource_type_datasource ON "shadow".chart_datasource_type USING btree(datasource);

--     CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource);

--     CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource);

--     CREATE INDEX chart_datasource_projects_pubs_datasource ON "shadow".chart_datasource_projects_pubs USING btree(datasource);

--     CREATE INDEX chart_datasource_projects_data_datasource ON "shadow".chart_datasource_projects_data USING btree(datasource);

--     project

--     CREATE INDEX chart_project_year_id ON "shadow".chart_project_year USING btree(id);

--     CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id);

--     CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id);

  END;$$;

--

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

--

CREATE or replace FUNCTION "shadow".clean_tables() RETURNS void LANGUAGE plpgsql AS $$

  BEGIN

    CREATE TABLE "shadow".rd_distinct AS SELECT DISTINCT * FROM "shadow".result_datasources;

    DROP TABLE "shadow".result_datasources;

    ALTER TABLE "shadow".rd_distinct RENAME TO result_datasources;

--     TRUNCATE "shadow".result_datasources;

--     INSERT INTO "shadow".result_datasources SELECT * FROM "shadow".rd_distinct ORDER BY datasource;

--     DROP TABLE "shadow".rd_distinct;

  END;$$;

CREATE OR REPLACE FUNCTION "shadow".insert_countries() RETURNS void LANGUAGE plpgsql AS $$

  BEGIN

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AD', 'Andorra', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AE', 'United Arab Emirates', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AF', 'Afghanistan', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AG', 'Antigua and Barbuda', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AI', 'Anguilla', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AL', 'Albania', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AM', 'Armenia', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AO', 'Angola', 'AF', 'Africa');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AQ', 'Antarctica', 'AN', 'Antarctica');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AR', 'Argentina', 'SA', 'South America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AS', 'American Samoa', 'OC', 'Oceania');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AT', 'Austria', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AU', 'Australia', 'OC', 'Oceania');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AW', 'Aruba', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AX', 'Åland Islands', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AZ', 'Azerbaijan', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BA', 'Bosnia and Herzegovina', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BB', 'Barbados', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BD', 'Bangladesh', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BE', 'Belgium', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BF', 'Burkina Faso', 'AF', 'Africa');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BG', 'Bulgaria', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BH', 'Bahrain', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BI', 'Burundi', 'AF', 'Africa');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BJ', 'Benin', 'AF', 'Africa');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BL', 'Saint Barthélemy', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BM', 'Bermuda', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BN', 'Brunei Darussalam', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BO', 'Bolivia, Plurinational State of', 'SA', 'South America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BR', 'Brazil', 'SA', 'South America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BS', 'Bahamas', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BT', 'Bhutan', 'AS', 'Asia');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BV', 'Bouvet Island', 'AN', 'Antarctica');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BW', 'Botswana', 'AF', 'Africa');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BY', 'Belarus', 'EU', 'Europe');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BZ', 'Belize', 'NA', 'North America');

    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CA', 'Canada', 'NA', 'North America');