D-Net

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

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

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

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

    CREATE VIEW "shadow".datasource AS SELECT tdatasource.id, tdatasource.name, tdatasource.type, tdatasource.compatibility, tdatasource.dateofvalidation, tdatasource.yearofvalidation, tdatasource.harvested, tdatasource.piwik_id, tdatasource.latitude, tdatasource.longitude, tdatasource.id AS datasource_results, tdatasource.id AS datasource_organizations, 1 AS number FROM tdatasource;