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,

    datasource_results text NOT NULL,

    datasource_organizations text NOT NULL,

    datasource_languages text NOT NULL,

    datasource_topics text NOT NULL,

    name text ,

    type text,

    compatibility text,

    latitude text,

    longitude text,

    dateofvalidation text,

    yearofvalidation integer,

    websiteurl text,

    piwik_id integer,

    harvested TEXT DEFAULT 'false',

    deletedbyinference TEXT,

    number integer DEFAULT 1 NOT NULL

);

--

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

--

CREATE TABLE shadow.datasource_languages (

    id text NOT NULL,

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

    organization_datasources text NOT NULL,

    organization_projects text NOT NULL,

    name text,

    country text,

    websiteurl  text,

  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:

--

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,

    project_organizations text NOT NULL,

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

    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,

    callidentifier  text,

    code text,

    ecarticle293 text,

    sources text,

  deletedbyinference TEXT,

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

    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,

  title text,

  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

);

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

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, datasource_results dr where d.id=dr.id);

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

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

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_class

  ON shadow.result

  USING btree

  (result_classifications 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");

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

--

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