--
-- 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,
  publisher text,
  journal text,
  year integer,
  date text ,
--   access_mode 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
);

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

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');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CC', 'Cocos (Keeling) Islands', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CD', 'Congo, the Democratic Republic of the', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CF', 'Central African Republic', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CG', 'Congo', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CH', 'Switzerland', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CI', 'Côte d''Ivoire', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CK', 'Cook Islands', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CL', 'Chile', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CM', 'Cameroon', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CN', 'China', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CO', 'Colombia', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CR', 'Costa Rica', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CU', 'Cuba', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CV', 'Cape Verde', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CX', 'Christmas Island', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CY', 'Cyprus', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CZ', 'Czech Republic', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DE', 'Germany', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DJ', 'Djibouti', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DK', 'Denmark', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DM', 'Dominica', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DO', 'Dominican Republic', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DZ', 'Algeria', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EC', 'Ecuador', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EE', 'Estonia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EG', 'Egypt', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EH', 'Western Sahara', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ER', 'Eritrea', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ES', 'Spain', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ET', 'Ethiopia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FI', 'Finland', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FJ', 'Fiji', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FK', 'Falkland Islands (Malvinas)', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FM', 'Micronesia, Federated States of', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FO', 'Faroe Islands', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FR', 'France', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GA', 'Gabon', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GB', 'United Kingdom', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GD', 'Grenada', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GE', 'Georgia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GF', 'French Guiana', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GG', 'Guernsey', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GH', 'Ghana', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GI', 'Gibraltar', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GL', 'Greenland', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GM', 'Gambia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GN', 'Guinea', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GP', 'Guadeloupe', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GQ', 'Equatorial Guinea', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GR', 'Greece', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GS', 'South Georgia and the South Sandwich Islands', 'AN', 'Antarctica');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GT', 'Guatemala', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GU', 'Guam', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GW', 'Guinea-Bissau', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GY', 'Guyana', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HK', 'Hong Kong', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HM', 'Heard Island and McDonald Islands', 'AN', 'Antarctica');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HN', 'Honduras', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HR', 'Croatia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HT', 'Haiti', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HU', 'Hungary', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ID', 'Indonesia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IE', 'Ireland', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IL', 'Israel', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IM', 'Isle of Man', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IN', 'India', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IO', 'British Indian Ocean Territory', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IQ', 'Iraq', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IR', 'Iran, Islamic Republic of', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IS', 'Iceland', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IT', 'Italy', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JE', 'Jersey', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JM', 'Jamaica', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JO', 'Jordan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JP', 'Japan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KE', 'Kenya', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KG', 'Kyrgyzstan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KH', 'Cambodia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KI', 'Kiribati', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KM', 'Comoros', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KN', 'Saint Kitts and Nevis', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KP', 'Korea, Democratic People''s Republic of', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KR', 'Korea, Republic of', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KW', 'Kuwait', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KY', 'Cayman Islands', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KZ', 'Kazakhstan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LA', 'Lao People''s Democratic Republic', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LB', 'Lebanon', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LC', 'Saint Lucia', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LI', 'Liechtenstein', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LK', 'Sri Lanka', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LR', 'Liberia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LS', 'Lesotho', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LT', 'Lithuania', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LU', 'Luxembourg', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LV', 'Latvia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LY', 'Libya', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MA', 'Morocco', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MC', 'Monaco', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MD', 'Moldova, Republic of', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ME', 'Montenegro', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MF', 'Saint Martin (French part)', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MG', 'Madagascar', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MH', 'Marshall Islands', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MK', 'Macedonia, the Former Yugoslav Republic of', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ML', 'Mali', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MM', 'Myanmar', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MN', 'Mongolia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MO', 'Macao', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MP', 'Northern Mariana Islands', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MQ', 'Martinique', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MR', 'Mauritania', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MS', 'Montserrat', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MT', 'Malta', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MU', 'Mauritius', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MV', 'Maldives', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MW', 'Malawi', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MX', 'Mexico', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MY', 'Malaysia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MZ', 'Mozambique', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NA', 'Namibia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NC', 'New Caledonia', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NE', 'Niger', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NF', 'Norfolk Island', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NG', 'Nigeria', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NI', 'Nicaragua', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NL', 'Netherlands', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NO', 'Norway', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NP', 'Nepal', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NR', 'Nauru', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NU', 'Niue', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NZ', 'New Zealand', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('OM', 'Oman', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PA', 'Panama', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PE', 'Peru', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PF', 'French Polynesia', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PG', 'Papua New Guinea', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PH', 'Philippines', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PK', 'Pakistan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PL', 'Poland', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PM', 'Saint Pierre and Miquelon', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PN', 'Pitcairn', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PR', 'Puerto Rico', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PS', 'Palestine, State of', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PT', 'Portugal', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PW', 'Palau', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PY', 'Paraguay', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('QA', 'Qatar', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RE', 'Réunion', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RO', 'Romania', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RS', 'Serbia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RU', 'Russian Federation', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RW', 'Rwanda', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SA', 'Saudi Arabia', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SB', 'Solomon Islands', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SC', 'Seychelles', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SD', 'Sudan', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SE', 'Sweden', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SG', 'Singapore', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SH', 'Saint Helena, Ascension and Tristan da Cunha', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SI', 'Slovenia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SJ', 'Svalbard and Jan Mayen', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SK', 'Slovakia', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SL', 'Sierra Leone', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SM', 'San Marino', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SN', 'Senegal', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SO', 'Somalia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SR', 'Suriname', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ST', 'Sao Tome and Principe', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SV', 'El Salvador', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SY', 'Syrian Arab Republic', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SZ', 'Swaziland', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TC', 'Turks and Caicos Islands', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TD', 'Chad', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TF', 'French Southern Territories', 'AN', 'Antarctica');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TG', 'Togo', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TH', 'Thailand', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TJ', 'Tajikistan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TK', 'Tokelau', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TL', 'Timor-Leste', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TM', 'Turkmenistan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TN', 'Tunisia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TO', 'Tonga', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TR', 'Turkey', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TT', 'Trinidad and Tobago', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TV', 'Tuvalu', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TW', 'Taiwan, Province of China', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TZ', 'Tanzania, United Republic of', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UA', 'Ukraine', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UG', 'Uganda', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UM', 'United States Minor Outlying Islands', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('US', 'United States', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UY', 'Uruguay', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UZ', 'Uzbekistan', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VA', 'Holy See (Vatican City State)', 'EU', 'Europe');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VC', 'Saint Vincent and the Grenadines', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VE', 'Venezuela, Bolivarian Republic of', 'SA', 'South America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VG', 'Virgin Islands, British', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VI', 'Virgin Islands, U.S.', 'NA', 'North America');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VN', 'Viet Nam', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VU', 'Vanuatu', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WF', 'Wallis and Futuna', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WS', 'Samoa', 'OC', 'Oceania');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YE', 'Yemen', 'AS', 'Asia');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YT', 'Mayotte', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZA', 'South Africa', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZM', 'Zambia', 'AF', 'Africa');
    INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZW', 'Zimbabwe', 'AF', 'Africa');
  END;$$;

CREATE OR REPLACE FUNCTION "shadow".backwards_compatibility() RETURNS void LANGUAGE plpgsql AS $$
  BEGIN
    ALTER TABLE "shadow".result RENAME TO tresult;
    ALTER TABLE "shadow".datasource RENAME TO tdatasource;
    ALTER TABLE "shadow".project RENAME TO tproject;
    ALTER TABLE "shadow".organization RENAME TO torganization;

    CREATE VIEW "shadow".result AS SELECT tresult.id, tresult.id AS result_projects, tresult.id AS result_datasources, tresult.publisher, tresult.year, tresult.bestlicense, tresult.bestlicense AS access_mode, tresult.type, tresult.embargo_end_date, tresult.delayed, tresult.authors, tresult.funders, tresult.funding_lvl0 AS arfunding_lvl0, tresult.projects, tresult.datasources, 1 AS number FROM tresult;
    CREATE VIEW "shadow".datasource AS SELECT tdatasource.id, tdatasource.name, tdatasource.type, tdatasource.compatibility, tdatasource.dateofvalidation, tdatasource.yearofvalidation, tdatasource.harvested, tdatasource.piwik_id, tdatasource.id AS datasource_results, tdatasource.id AS datasource_organizations, 1 AS number FROM tdatasource;
    CREATE VIEW "shadow".project AS SELECT tproject.id, tproject.acronym, tproject.title, tproject.funder, tproject.funding_lvl0, tproject.funding_lvl1, tproject.funding_lvl2, tproject.funding_lvl3, tproject.sc39, tproject.type, tproject.start_year, tproject.end_year, tproject.duration, tproject.haspubs, tproject.numpubs, tproject.enddate, tproject.startdate, tproject.daysforlastpub, tproject.delayedpubs, tproject.callidentifier, tproject.code, tproject.id AS project_results, tproject.id AS project_organizations, 1 AS number FROM tproject;
    CREATE VIEW "shadow".organization AS SELECT torganization.id, torganization.name, torganization.country, torganization.id AS organization_datasources, torganization.id AS organization_projects, 1 AS number FROM torganization;
  END;$$;


-- sqoopQL database dump complete
--