Revision 56483
Added by Antonis Lempesis almost 5 years ago
stats_db_schema.sql | ||
---|---|---|
282 | 282 |
|
283 | 283 |
CREATE TABLE shadow.result ( |
284 | 284 |
id text NOT NULL, |
285 |
title text, |
|
285 | 286 |
publisher text, |
286 | 287 |
journal text, |
287 | 288 |
year integer, |
... | ... | |
291 | 292 |
type text , |
292 | 293 |
embargo_end_date text, |
293 | 294 |
delayed text, |
294 |
authors integer |
|
295 |
authors integer, |
|
296 |
source text |
|
295 | 297 |
); |
296 | 298 |
|
297 | 299 |
-- |
... | ... | |
506 | 508 |
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; |
507 | 509 |
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; |
508 | 510 |
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; |
509 |
-- 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; |
|
510 | 511 |
|
511 |
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;
|
|
512 |
DROP TABLE shadow.result CASCADE;
|
|
513 |
ALTER TABLE shadow.result_temp RENAME TO result; |
|
512 |
-- todo fix this query
|
|
513 |
-- 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;
|
|
514 |
|
|
514 | 515 |
END $BODY$; |
515 | 516 |
|
516 | 517 |
ALTER FUNCTION shadow.create_arrays() OWNER TO sqoop; |
... | ... | |
545 | 546 |
CREATE INDEX result_funders_idx ON shadow.result USING gin (funders); |
546 | 547 |
CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0); |
547 | 548 |
CREATE INDEX result_projects_idx ON shadow.result USING gin (projects); |
548 |
CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources); |
|
549 |
-- CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources);
|
|
549 | 550 |
|
550 | 551 |
|
551 | 552 |
CREATE INDEX project_acronym ON "shadow"."project" USING btree (acronym); |
... | ... | |
626 | 627 |
CREATE or replace FUNCTION "shadow".create_charts() RETURNS void LANGUAGE plpgsql AS $$ |
627 | 628 |
BEGIN |
628 | 629 |
-- country nums |
629 |
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; |
|
630 |
-- |
|
631 |
-- -- country charts |
|
632 |
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; |
|
633 |
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; |
|
634 |
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; |
|
635 |
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; |
|
636 |
-- |
|
637 |
-- -- datasource charts |
|
638 |
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; |
|
639 |
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; |
|
640 |
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; |
|
641 |
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; |
|
642 |
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; |
|
643 |
-- |
|
644 |
-- project charts |
|
645 |
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; |
|
646 |
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; |
|
647 |
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; |
|
630 |
-- 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;
|
|
631 |
-- --
|
|
632 |
-- -- -- country charts
|
|
633 |
-- 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;
|
|
634 |
-- 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;
|
|
635 |
-- 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;
|
|
636 |
-- 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;
|
|
637 |
-- --
|
|
638 |
-- -- -- datasource charts
|
|
639 |
-- 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;
|
|
640 |
-- 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;
|
|
641 |
-- 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;
|
|
642 |
-- 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;
|
|
643 |
-- 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;
|
|
644 |
-- --
|
|
645 |
-- -- project charts
|
|
646 |
-- 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;
|
|
647 |
-- 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;
|
|
648 |
-- 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;
|
|
648 | 649 |
END;$$; |
649 | 650 |
|
650 | 651 |
-- |
... | ... | |
946 | 947 |
ALTER TABLE "shadow".project RENAME TO tproject; |
947 | 948 |
ALTER TABLE "shadow".organization RENAME TO torganization; |
948 | 949 |
|
949 |
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; |
|
950 |
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, tresult.title as title, tresult.source as source FROM tresult;
|
|
950 | 951 |
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; |
951 | 952 |
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; |
952 | 953 |
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; |
Also available in: Unified diff
Minor bug fixes