Revision 55644
Added by Antonis Lempesis almost 5 years ago
stats_db_schema.sql | ||
---|---|---|
46 | 46 |
name text , |
47 | 47 |
type text, |
48 | 48 |
compatibility text, |
49 |
latitude text, |
|
50 |
longitude text, |
|
49 | 51 |
dateofvalidation text, |
50 | 52 |
yearofvalidation integer, |
51 | 53 |
harvested TEXT DEFAULT 'false', |
... | ... | |
284 | 286 |
journal text, |
285 | 287 |
year integer, |
286 | 288 |
date text , |
289 |
-- access_mode text, |
|
287 | 290 |
bestlicense text, |
288 | 291 |
type text , |
289 | 292 |
embargo_end_date text, |
... | ... | |
373 | 376 |
description text |
374 | 377 |
); |
375 | 378 |
|
376 |
-- |
|
377 |
-- DATASOURCE Extra Inserts |
|
378 |
-- |
|
379 |
|
|
380 |
|
|
381 |
-- CREATE TABLE shadow.person ( |
|
382 |
-- id text NOT NULL, |
|
383 |
-- person_results text NOT NULL, |
|
384 |
-- firstname text, |
|
385 |
-- lastname text, |
|
386 |
-- fullname text , |
|
387 |
-- nationality text , |
|
388 |
-- email text, |
|
389 |
-- phone text, |
|
390 |
-- deletedbyinference TEXT, |
|
391 |
-- number text |
|
392 |
-- ); |
|
393 |
|
|
394 |
|
|
395 |
-- CREATE TABLE shadow.person_results ( |
|
396 |
-- id text NOT NULL, |
|
397 |
-- result text NOT NULL |
|
398 |
-- ); |
|
399 |
|
|
400 |
-- CREATE TABLE shadow.project_persons ( |
|
401 |
-- id text NOT NULL, |
|
402 |
-- person text NOT NULL |
|
403 |
-- ); |
|
404 |
|
|
405 | 379 |
CREATE TABLE shadow.result_pids ( |
406 | 380 |
id text NOT NULL, |
407 | 381 |
type text , |
408 | 382 |
pid text |
409 | 383 |
); |
410 | 384 |
|
385 |
CREATE TABLE shadow.country ( |
|
386 |
code text NOT NULL, |
|
387 |
name text, |
|
388 |
continent_code text, |
|
389 |
continent_name text |
|
411 | 390 |
|
391 |
); |
|
412 | 392 |
|
393 |
----------- |
|
394 |
--functions |
|
395 |
----------- |
|
396 |
CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
397 |
BEGIN |
|
413 | 398 |
|
399 |
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')); |
|
400 |
UPDATE "shadow".datasource SET name='Other' where name='Unknown Repository'; |
|
401 |
UPDATE "shadow".result_datasources SET datasource=(SELECT id FROM "shadow".datasource WHERE name='Other') WHERE datasource NOT IN (SELECT id FROM "shadow".datasource); |
|
414 | 402 |
|
403 |
TRUNCATE TABLE "shadow".defaults; |
|
404 |
INSERT INTO "shadow".defaults VALUES ('result', 'year', 'number', 'count', '', 'column', 1); |
|
405 |
INSERT INTO "shadow".defaults VALUES ('project', 'funding_lvl1', 'number', 'count', '', 'column', 2); |
|
406 |
INSERT INTO "shadow".defaults VALUES ('organization', 'country', 'number', 'count', '', 'column', 3); |
|
407 |
INSERT INTO "shadow".defaults VALUES ('datasource', 'oa_compatible', 'number', 'count', '', 'column', 4); |
|
415 | 408 |
|
409 |
UPDATE "shadow".datasource SET yearofvalidation=null WHERE yearofvalidation='-1'; |
|
410 |
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); |
|
411 |
END $BODY$; |
|
416 | 412 |
|
417 |
CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource() |
|
418 |
RETURNS void AS |
|
419 |
$BODY$ |
|
420 |
BEGIN |
|
413 |
ALTER FUNCTION shadow.extra_defaults_datasource() OWNER TO sqoop; |
|
421 | 414 |
|
422 |
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')); |
|
423 |
UPDATE "shadow".datasource SET name='Other' where name='Unknown Repository'; |
|
424 |
UPDATE "shadow".result_datasources SET datasource=(SELECT id FROM "shadow".datasource WHERE name='Other') WHERE datasource NOT IN (SELECT id FROM "shadow".datasource); |
|
425 | 415 |
|
426 |
TRUNCATE TABLE "shadow".defaults; |
|
427 |
INSERT INTO "shadow".defaults VALUES ('result', 'year', 'number', 'count', '', 'column', 1); |
|
428 |
INSERT INTO "shadow".defaults VALUES ('project', 'funding_lvl1', 'number', 'count', '', 'column', 2); |
|
429 |
INSERT INTO "shadow".defaults VALUES ('organization', 'country', 'number', 'count', '', 'column', 3); |
|
430 |
INSERT INTO "shadow".defaults VALUES ('datasource', 'oa_compatible', 'number', 'count', '', 'column', 4); |
|
431 |
|
|
432 |
UPDATE "shadow".datasource SET yearofvalidation=null WHERE yearofvalidation='-1'; |
|
433 |
|
|
434 |
|
|
435 |
-- UPDATE shadow.project SET funder='FCT' WHERE funder='Fundação para a Ciência e a Tecnologia, I.P.'; |
|
436 |
|
|
437 |
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); |
|
438 |
|
|
439 |
END |
|
440 |
$BODY$ |
|
441 |
LANGUAGE plpgsql VOLATILE |
|
442 |
COST 100; |
|
443 |
ALTER FUNCTION shadow.extra_defaults_datasource() |
|
444 |
OWNER TO sqoop; |
|
445 |
|
|
446 |
|
|
447 | 416 |
-- |
448 | 417 |
-- Project Results Extra Inserts |
449 | 418 |
-- |
419 |
CREATE OR REPLACE FUNCTION shadow.update_project_results() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
420 |
BEGIN |
|
421 |
CREATE OR REPLACE VIEW shadow.project_results_publication AS |
|
422 |
SELECT "shadow".result_projects.id AS result, "shadow".result_projects.project AS project_results, "shadow"."result"."date" as resultdate, |
|
423 |
"shadow"."project"."enddate" as projectenddate, "shadow".result_projects.daysfromend as daysfromend |
|
424 |
FROM "shadow".result_projects, "shadow".result, "shadow".project |
|
425 |
where "shadow".result_projects.id="shadow".result.id and "shadow".result.type='publication' and "shadow".project.id= "shadow".result_projects.project; |
|
426 |
END $BODY$; |
|
450 | 427 |
|
451 |
CREATE OR REPLACE FUNCTION shadow.update_project_results() |
|
452 |
RETURNS void AS |
|
453 |
$BODY$ |
|
454 |
BEGIN |
|
428 |
ALTER FUNCTION shadow.update_project_results() OWNER TO sqoop; |
|
455 | 429 |
|
456 | 430 |
|
457 |
CREATE OR REPLACE VIEW shadow.project_results_publication AS |
|
458 |
SELECT "shadow".result_projects.id AS result, |
|
459 |
"shadow".result_projects.project AS project_results, |
|
460 |
"shadow"."result"."date" as resultdate, |
|
461 |
"shadow"."project"."enddate" as projectenddate, |
|
462 |
"shadow".result_projects.daysfromend |
|
463 |
as daysfromend FROM "shadow".result_projects, |
|
464 |
"shadow".result, |
|
465 |
"shadow".project |
|
466 |
where "shadow".result_projects.id="shadow".result.id |
|
467 |
and "shadow".result.type='publication' |
|
468 |
and "shadow".project.id= "shadow".result_projects.project; |
|
469 | 431 |
|
470 |
END |
|
471 |
$BODY$ |
|
472 |
LANGUAGE plpgsql VOLATILE |
|
473 |
COST 100; |
|
474 |
ALTER FUNCTION shadow.update_project_results() |
|
475 |
OWNER TO sqoop; |
|
476 |
|
|
477 |
|
|
478 |
|
|
479 | 432 |
-- |
480 | 433 |
-- Project Has Publications Extra Inserts |
481 | 434 |
-- |
482 |
CREATE OR REPLACE FUNCTION shadow.project_has_pubs() |
|
483 |
RETURNS void AS
|
|
484 |
$BODY$
|
|
485 |
BEGIN
|
|
435 |
CREATE OR REPLACE FUNCTION shadow.project_has_pubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$
|
|
436 |
BEGIN
|
|
437 |
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');
|
|
438 |
END $BODY$;
|
|
486 | 439 |
|
487 |
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');
|
|
440 |
ALTER FUNCTION shadow.project_has_pubs() OWNER TO sqoop;
|
|
488 | 441 |
|
489 |
END |
|
490 |
$BODY$ |
|
491 |
LANGUAGE plpgsql VOLATILE |
|
492 |
COST 100; |
|
493 |
ALTER FUNCTION shadow.project_has_pubs() |
|
494 |
OWNER TO sqoop; |
|
495 | 442 |
|
496 |
|
|
497 | 443 |
-- |
498 | 444 |
-- Project Publications Count Updates |
499 | 445 |
-- |
446 |
CREATE OR REPLACE FUNCTION shadow.project_pubs_count() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
447 |
BEGIN |
|
448 |
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; |
|
449 |
END $BODY$; |
|
500 | 450 |
|
451 |
ALTER FUNCTION shadow.project_pubs_count() OWNER TO sqoop; |
|
501 | 452 |
|
502 |
CREATE OR REPLACE FUNCTION shadow.project_pubs_count() |
|
503 |
RETURNS void AS |
|
504 |
$BODY$ |
|
505 |
BEGIN |
|
506 | 453 |
|
507 |
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; |
|
508 | 454 |
|
509 |
END |
|
510 |
$BODY$ |
|
511 |
LANGUAGE plpgsql VOLATILE |
|
512 |
COST 100; |
|
513 |
ALTER FUNCTION shadow.project_pubs_count() |
|
514 |
OWNER TO sqoop; |
|
515 |
|
|
516 |
|
|
517 |
|
|
518 | 455 |
-- |
519 | 456 |
-- Project Delayed Publications |
520 | 457 |
-- |
458 |
CREATE OR REPLACE FUNCTION shadow.project_delayedpubs() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
459 |
BEGIN |
|
460 |
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; |
|
461 |
END $BODY$; |
|
521 | 462 |
|
522 |
CREATE OR REPLACE FUNCTION shadow.project_delayedpubs() |
|
523 |
RETURNS void AS |
|
524 |
$BODY$ |
|
525 |
BEGIN |
|
463 |
ALTER FUNCTION shadow.project_delayedpubs() OWNER TO sqoop; |
|
526 | 464 |
|
527 |
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; |
|
528 | 465 |
|
529 |
END |
|
530 |
$BODY$ |
|
531 |
LANGUAGE plpgsql VOLATILE |
|
532 |
COST 100; |
|
533 |
ALTER FUNCTION shadow.project_delayedpubs() |
|
534 |
OWNER TO sqoop; |
|
535 |
|
|
536 |
|
|
537 | 466 |
-- |
538 | 467 |
-- Project daysforlastpub |
539 | 468 |
-- |
469 |
CREATE OR REPLACE FUNCTION shadow.project_daysforlastpub() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
470 |
BEGIN |
|
471 |
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; |
|
472 |
END $BODY$; |
|
540 | 473 |
|
541 |
CREATE OR REPLACE FUNCTION shadow.project_daysforlastpub() |
|
542 |
RETURNS void AS |
|
543 |
$BODY$ |
|
544 |
BEGIN |
|
474 |
ALTER FUNCTION shadow.project_daysforlastpub() OWNER TO sqoop; |
|
545 | 475 |
|
546 |
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; |
|
547 |
|
|
548 |
END |
|
549 |
$BODY$ |
|
550 |
LANGUAGE plpgsql VOLATILE |
|
551 |
COST 100; |
|
552 |
ALTER FUNCTION shadow.project_daysforlastpub() |
|
553 |
OWNER TO sqoop; |
|
554 |
|
|
555 | 476 |
-- |
556 | 477 |
-- Project delayed |
557 | 478 |
-- |
479 |
CREATE OR REPLACE FUNCTION shadow.project_delayed () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
480 |
BEGIN |
|
481 |
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); |
|
482 |
END $BODY$; |
|
558 | 483 |
|
484 |
ALTER FUNCTION shadow.project_delayed() OWNER TO sqoop; |
|
559 | 485 |
|
560 |
CREATE OR REPLACE FUNCTION shadow.project_delayed () |
|
561 |
RETURNS void AS |
|
562 |
$BODY$ |
|
563 |
BEGIN |
|
564 |
|
|
565 |
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); |
|
566 |
|
|
567 |
END |
|
568 |
$BODY$ |
|
569 |
LANGUAGE plpgsql VOLATILE |
|
570 |
COST 100; |
|
571 |
ALTER FUNCTION shadow.project_delayed() |
|
572 |
OWNER TO sqoop; |
|
573 |
|
|
574 |
|
|
575 | 486 |
-- |
576 | 487 |
-- Cleaning Up Temps |
577 | 488 |
-- |
578 |
CREATE OR REPLACE FUNCTION shadow.cleanTemps () |
|
579 |
RETURNS void AS |
|
580 |
$BODY$ |
|
581 |
BEGIN |
|
489 |
CREATE OR REPLACE FUNCTION shadow.cleanTemps () RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
490 |
BEGIN |
|
491 |
drop view if exists shadow.delayedpubs ; |
|
492 |
drop view if exists shadow.project_pub_count ; |
|
493 |
drop view if exists shadow.delayedpubs ; |
|
494 |
drop view if exists shadow.project_results_publication ; |
|
495 |
END $BODY$; |
|
582 | 496 |
|
583 |
drop view if exists shadow.delayedpubs ; |
|
584 |
drop view if exists shadow.project_pub_count ; |
|
585 |
drop view if exists shadow.delayedpubs ; |
|
497 |
ALTER FUNCTION shadow.cleanTemps() OWNER TO sqoop; |
|
586 | 498 |
|
587 |
drop view if exists shadow.project_results_publication ; |
|
588 |
END |
|
589 |
$BODY$ |
|
590 |
LANGUAGE plpgsql VOLATILE |
|
591 |
COST 100; |
|
592 |
ALTER FUNCTION shadow.cleanTemps() |
|
593 |
OWNER TO sqoop; |
|
594 |
|
|
595 |
|
|
596 |
---functions |
|
597 |
|
|
598 |
|
|
599 | 499 |
-- |
600 | 500 |
-- Name: create_arrays(); Type FUNCTION; Schema: shadow; Owner: sqoop |
601 | 501 |
-- |
602 |
CREATE OR REPLACE FUNCTION shadow.create_arrays() |
|
603 |
RETURNS void AS |
|
604 |
$BODY$ |
|
605 |
BEGIN |
|
502 |
CREATE OR REPLACE FUNCTION shadow.create_arrays() RETURNS void LANGUAGE plpgsql VOLATILE COST 100 AS $BODY$ |
|
503 |
BEGIN |
|
606 | 504 |
|
607 |
ALTER TABLE shadow.result ADD COLUMN funders text[], ADD COLUMN funding_lvl0 text[], ADD COLUMN projects text[], ADD COLUMN datasources text[]; |
|
608 |
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; |
|
609 |
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; |
|
610 |
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; |
|
611 |
-- 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; |
|
505 |
ALTER TABLE shadow.result ADD COLUMN funders text[], ADD COLUMN funding_lvl0 text[], ADD COLUMN projects text[], ADD COLUMN datasources text[];
|
|
506 |
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 |
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 |
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;
|
|
612 | 510 |
|
613 |
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; |
|
614 |
DROP TABLE shadow.result CASCADE; |
|
615 |
ALTER TABLE shadow.result_temp RENAME TO result; |
|
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; |
|
514 |
END $BODY$; |
|
616 | 515 |
|
617 |
END |
|
618 |
$BODY$ |
|
619 |
LANGUAGE plpgsql VOLATILE |
|
620 |
COST 100; |
|
621 |
ALTER FUNCTION shadow.create_arrays() |
|
622 |
OWNER TO sqoop; |
|
516 |
ALTER FUNCTION shadow.create_arrays() OWNER TO sqoop; |
|
623 | 517 |
|
624 | 518 |
-- |
625 | 519 |
-- Name: create_indexes(); Type: FUNCTION; Schema: shadow; Owner: sqoop |
626 | 520 |
-- |
627 | 521 |
|
628 |
CREATE FUNCTION shadow.create_indexes() RETURNS void |
|
629 |
LANGUAGE plpgsql |
|
630 |
AS $$ |
|
522 |
CREATE FUNCTION shadow.create_indexes() RETURNS void LANGUAGE plpgsql AS $$ |
|
523 |
BEGIN |
|
524 |
CREATE INDEX datasource_id ON "shadow".datasource USING btree (id); |
|
525 |
CREATE INDEX datasource_type ON "shadow".datasource USING btree (type); |
|
526 |
CREATE INDEX datasource_name ON "shadow".datasource USING btree (name); |
|
527 |
CREATE INDEX datasource_piwik_id ON "shadow".datasource USING btree (piwik_id); |
|
528 |
CREATE INDEX dtsrc_type ON shadow.datasource USING btree (type COLLATE pg_catalog."default"); |
|
529 |
CREATE INDEX dtsrc_name ON shadow.datasource USING btree (name COLLATE pg_catalog."default"); |
|
530 |
CREATE INDEX dtsrc_compatibility ON shadow.datasource USING btree (compatibility COLLATE pg_catalog."default"); |
|
631 | 531 |
|
632 |
BEGIN |
|
532 |
CREATE INDEX datasource_oids_id ON "shadow".datasource_oids USING btree (id COLLATE pg_catalog."default"); |
|
533 |
CREATE INDEX datasource_oids_orid ON "shadow".datasource_oids USING btree(orid COLLATE pg_catalog."default"); |
|
534 |
CREATE INDEX datasource_websites_id ON "shadow".datasource_websites USING btree(id COLLATE pg_catalog."default"); |
|
535 |
CREATE INDEX datasource_lang_id ON shadow.datasource_languages (id ASC NULLS LAST); |
|
536 |
CREATE INDEX dtsrc_org_id ON shadow.datasource_organizations USING btree (id COLLATE pg_catalog."default"); |
|
537 |
CREATE INDEX dtsrc_org_org ON shadow.datasource_organizations USING btree (organization COLLATE pg_catalog."default"); |
|
633 | 538 |
|
634 |
CREATE INDEX datasource_id ON "shadow".datasource USING btree (id); |
|
635 |
CREATE INDEX datasource_type ON "shadow".datasource USING btree (type); |
|
636 |
CREATE INDEX datasource_name ON "shadow".datasource USING btree (name); |
|
637 |
CREATE INDEX datasource_piwik_id ON "shadow".datasource USING btree (piwik_id); |
|
539 |
CREATE INDEX result_authors ON "shadow".result USING btree (authors); |
|
540 |
CREATE INDEX result_id ON "shadow".result USING btree (id); |
|
541 |
CREATE INDEX result_year ON "shadow".result USING btree (year); |
|
542 |
CREATE INDEX result_date ON "shadow"."result" USING btree ("date"); |
|
543 |
CREATE INDEX result_type ON "shadow"."result" USING btree ("type"); |
|
544 |
CREATE INDEX res_bestlicense ON shadow.result USING btree (bestlicense COLLATE pg_catalog."default"); |
|
545 |
CREATE INDEX result_funders_idx ON shadow.result USING gin (funders); |
|
546 |
CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0); |
|
547 |
CREATE INDEX result_projects_idx ON shadow.result USING gin (projects); |
|
548 |
CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources); |
|
638 | 549 |
|
639 |
CREATE INDEX result_authors ON "shadow".result USING btree (authors); |
|
640 |
CREATE INDEX result_id ON "shadow".result USING btree (id); |
|
641 |
CREATE INDEX result_year ON "shadow".result USING btree (year); |
|
642 |
CREATE INDEX result_date ON "shadow"."result" USING btree ("date"); |
|
643 |
CREATE INDEX result_type ON "shadow"."result" USING btree ("type"); |
|
644 | 550 |
|
551 |
CREATE INDEX project_acronym ON "shadow"."project" USING btree (acronym); |
|
552 |
CREATE INDEX project_enddate ON "shadow"."project" USING btree (enddate); |
|
553 |
CREATE INDEX project_id ON "shadow"."project" USING btree (id); |
|
554 |
CREATE INDEX proj_funder ON shadow.project USING btree (funder COLLATE pg_catalog."default"); |
|
555 |
CREATE INDEX proj_title ON shadow.project USING btree (title COLLATE pg_catalog."default"); |
|
556 |
CREATE INDEX proj_fndlvl0 ON shadow.project USING btree (funding_lvl0 COLLATE pg_catalog."default"); |
|
557 |
CREATE INDEX proj_fndlvl1 ON shadow.project USING btree (funding_lvl1 COLLATE pg_catalog."default"); |
|
558 |
CREATE INDEX proj_fndlvl2 ON shadow.project USING btree (funding_lvl2 COLLATE pg_catalog."default"); |
|
559 |
CREATE INDEX proj_sc39 ON shadow.project USING btree (sc39 COLLATE pg_catalog."default"); |
|
560 |
CREATE INDEX proj_fndlvl3 ON shadow.project USING btree (funding_lvl3 COLLATE pg_catalog."default"); |
|
645 | 561 |
|
646 |
CREATE INDEX project_acronym ON "shadow"."project" USING btree (acronym); |
|
647 |
CREATE INDEX project_enddate ON "shadow"."project" USING btree (enddate); |
|
648 |
CREATE INDEX project_id ON "shadow"."project" USING btree (id); |
|
649 |
CREATE INDEX project_results_result ON "shadow"."project_results" USING btree (result); |
|
650 |
CREATE INDEX project_results_project ON "shadow"."project_results" USING btree (id); |
|
562 |
CREATE INDEX project_results_result ON "shadow"."project_results" USING btree (result); |
|
563 |
CREATE INDEX project_results_project ON "shadow"."project_results" USING btree (id); |
|
651 | 564 |
|
652 |
CREATE INDEX result_oids_id ON "shadow".result_oids USING btree (id COLLATE pg_catalog."default"); |
|
653 |
CREATE INDEX result_oids_oid ON "shadow".result_oids USING btree(orid COLLATE pg_catalog."default"); |
|
565 |
CREATE INDEX result_oids_id ON "shadow".result_oids USING btree (id COLLATE pg_catalog."default");
|
|
566 |
CREATE INDEX result_oids_oid ON "shadow".result_oids USING btree(orid COLLATE pg_catalog."default");
|
|
654 | 567 |
|
655 |
CREATE INDEX result_pids_id ON "shadow".result_pids USING btree (id COLLATE pg_catalog."default"); |
|
656 |
CREATE INDEX result_pids_type ON "shadow".result_pids USING btree (type COLLATE pg_catalog."default"); |
|
657 |
CREATE INDEX result_pids_pid ON "shadow".result_pids USING btree(pid COLLATE pg_catalog."default"); |
|
568 |
CREATE INDEX result_pids_id ON "shadow".result_pids USING btree (id COLLATE pg_catalog."default");
|
|
569 |
CREATE INDEX result_pids_type ON "shadow".result_pids USING btree (type COLLATE pg_catalog."default");
|
|
570 |
CREATE INDEX result_pids_pid ON "shadow".result_pids USING btree(pid COLLATE pg_catalog."default");
|
|
658 | 571 |
|
659 |
CREATE INDEX result_extra_id ON "shadow".result_extra USING btree(id COLLATE pg_catalog."default"); |
|
572 |
CREATE INDEX result_extra_id ON "shadow".result_extra USING btree(id COLLATE pg_catalog."default");
|
|
660 | 573 |
|
661 |
CREATE INDEX datasource_oids_id ON "shadow".datasource_oids USING btree (id COLLATE pg_catalog."default"); |
|
662 |
CREATE INDEX datasource_oids_orid ON "shadow".datasource_oids USING btree(orid COLLATE pg_catalog."default"); |
|
663 |
CREATE INDEX datasource_websites_id ON "shadow".datasource_websites USING btree(id COLLATE pg_catalog."default"); |
|
574 |
CREATE INDEX res_class_type ON shadow.result_classifications USING btree (type COLLATE pg_catalog."default"); |
|
575 |
CREATE INDEX res_class_id ON shadow.result_classifications USING btree (id COLLATE pg_catalog."default"); |
|
664 | 576 |
|
577 |
CREATE INDEX res_dtsrc_dtsrc ON shadow.result_datasources USING btree (datasource COLLATE pg_catalog."default"); |
|
578 |
CREATE INDEX res_dtsrc_id ON shadow.result_datasources USING btree (id COLLATE pg_catalog."default"); |
|
665 | 579 |
|
580 |
CREATE INDEX res_lang_lang ON shadow.result_languages USING btree (language COLLATE pg_catalog."default"); |
|
666 | 581 |
|
667 |
CREATE INDEX datasource_lang_id |
|
668 |
ON shadow.datasource_languages (id ASC NULLS LAST); |
|
582 |
CREATE INDEX org_id ON shadow.organization USING btree (id COLLATE pg_catalog."default"); |
|
583 |
CREATE INDEX org_country ON shadow.organization USING btree (country COLLATE pg_catalog."default"); |
|
584 |
CREATE INDEX org_name ON shadow.organization USING btree (name COLLATE pg_catalog."default"); |
|
669 | 585 |
|
670 |
CREATE INDEX dtsrc_org_id |
|
671 |
ON shadow.datasource_organizations |
|
672 |
USING btree |
|
673 |
(id COLLATE pg_catalog."default"); |
|
586 |
CREATE INDEX proj_org_org ON shadow.project_organizations USING btree (organization COLLATE pg_catalog."default"); |
|
587 |
CREATE INDEX proj_org_id ON shadow.project_organizations USING btree (id COLLATE pg_catalog."default"); |
|
674 | 588 |
|
675 |
CREATE INDEX res_class_type |
|
676 |
ON shadow.result_classifications |
|
677 |
USING btree |
|
678 |
(type COLLATE pg_catalog."default"); |
|
589 |
CREATE INDEX res_res_id ON shadow.result_results USING btree (id COLLATE pg_catalog."default"); |
|
590 |
CREATE INDEX res_res_res ON shadow.result_results USING btree (result COLLATE pg_catalog."default"); |
|
679 | 591 |
|
680 |
CREATE INDEX res_class_id |
|
681 |
ON shadow.result_classifications |
|
682 |
USING btree |
|
683 |
(id COLLATE pg_catalog."default"); |
|
592 |
CREATE INDEX res_lang_id ON shadow.result_languages USING btree (id COLLATE pg_catalog."default"); |
|
684 | 593 |
|
685 |
CREATE INDEX res_dtsrc_dtsrc |
|
686 |
ON shadow.result_datasources |
|
687 |
USING btree |
|
688 |
(datasource COLLATE pg_catalog."default"); |
|
594 |
CREATE INDEX res_conc_id ON shadow.result_concepts USING btree (id COLLATE pg_catalog."default"); |
|
595 |
CREATE INDEX res_conc_conc ON shadow.result_concepts USING btree (concept COLLATE pg_catalog."default"); |
|
596 |
END;$$; |
|
689 | 597 |
|
690 |
CREATE INDEX res_dtsrc_id |
|
691 |
ON shadow.result_datasources |
|
692 |
USING btree |
|
693 |
(id COLLATE pg_catalog."default"); |
|
694 |
|
|
695 |
CREATE INDEX res_lang_lang |
|
696 |
ON shadow.result_languages |
|
697 |
USING btree |
|
698 |
(language COLLATE pg_catalog."default"); |
|
699 |
|
|
700 |
CREATE INDEX org_id |
|
701 |
ON shadow.organization |
|
702 |
USING btree |
|
703 |
(id COLLATE pg_catalog."default"); |
|
704 |
|
|
705 |
CREATE INDEX org_country |
|
706 |
ON shadow.organization |
|
707 |
USING btree |
|
708 |
(country COLLATE pg_catalog."default"); |
|
709 |
|
|
710 |
CREATE INDEX proj_funder |
|
711 |
ON shadow.project |
|
712 |
USING btree |
|
713 |
(funder COLLATE pg_catalog."default"); |
|
714 |
|
|
715 |
CREATE INDEX proj_title |
|
716 |
ON shadow.project |
|
717 |
USING btree |
|
718 |
(title COLLATE pg_catalog."default"); |
|
719 |
|
|
720 |
CREATE INDEX proj_fndlvl0 |
|
721 |
ON shadow.project |
|
722 |
USING btree |
|
723 |
(funding_lvl0 COLLATE pg_catalog."default"); |
|
724 |
|
|
725 |
CREATE INDEX proj_fndlvl1 |
|
726 |
ON shadow.project |
|
727 |
USING btree |
|
728 |
(funding_lvl1 COLLATE pg_catalog."default"); |
|
729 |
|
|
730 |
CREATE INDEX proj_fndlvl2 |
|
731 |
ON shadow.project |
|
732 |
USING btree |
|
733 |
(funding_lvl2 COLLATE pg_catalog."default"); |
|
734 |
|
|
735 |
CREATE INDEX proj_org_org |
|
736 |
ON shadow.project_organizations |
|
737 |
USING btree |
|
738 |
(organization COLLATE pg_catalog."default"); |
|
739 |
|
|
740 |
CREATE INDEX proj_org_id |
|
741 |
ON shadow.project_organizations |
|
742 |
USING btree |
|
743 |
(id COLLATE pg_catalog."default"); |
|
744 |
|
|
745 |
CREATE INDEX res_res_id |
|
746 |
ON shadow.result_results |
|
747 |
USING btree |
|
748 |
(id COLLATE pg_catalog."default"); |
|
749 |
|
|
750 |
CREATE INDEX res_res_res |
|
751 |
ON shadow.result_results |
|
752 |
USING btree |
|
753 |
(result COLLATE pg_catalog."default"); |
|
754 |
|
|
755 |
CREATE INDEX res_lang_id |
|
756 |
ON shadow.result_languages |
|
757 |
USING btree |
|
758 |
(id COLLATE pg_catalog."default"); |
|
759 |
|
|
760 |
CREATE INDEX res_conc_id |
|
761 |
ON shadow.result_concepts |
|
762 |
USING btree |
|
763 |
(id COLLATE pg_catalog."default"); |
|
764 |
|
|
765 |
CREATE INDEX res_conc_conc |
|
766 |
ON shadow.result_concepts |
|
767 |
USING btree |
|
768 |
(concept COLLATE pg_catalog."default"); |
|
769 |
|
|
770 |
CREATE INDEX res_bestlicense |
|
771 |
ON shadow.result |
|
772 |
USING btree |
|
773 |
(bestlicense COLLATE pg_catalog."default"); |
|
774 |
|
|
775 |
CREATE INDEX proj_sc39 |
|
776 |
ON shadow.project |
|
777 |
USING btree |
|
778 |
(sc39 COLLATE pg_catalog."default"); |
|
779 |
|
|
780 |
CREATE INDEX proj_fndlvl3 |
|
781 |
ON shadow.project |
|
782 |
USING btree |
|
783 |
(funding_lvl3 COLLATE pg_catalog."default"); |
|
784 |
|
|
785 |
CREATE INDEX dtsrc_type |
|
786 |
ON shadow.datasource |
|
787 |
USING btree |
|
788 |
(type COLLATE pg_catalog."default"); |
|
789 |
|
|
790 |
CREATE INDEX dtsrc_name |
|
791 |
ON shadow.datasource |
|
792 |
USING btree |
|
793 |
(name COLLATE pg_catalog."default"); |
|
794 |
|
|
795 |
CREATE INDEX dtsrc_compatibility |
|
796 |
ON shadow.datasource |
|
797 |
USING btree |
|
798 |
(compatibility COLLATE pg_catalog."default"); |
|
799 |
|
|
800 |
CREATE INDEX org_name |
|
801 |
ON shadow.organization |
|
802 |
USING btree |
|
803 |
(name COLLATE pg_catalog."default"); |
|
804 |
|
|
805 |
CREATE INDEX dtsrc_org_org |
|
806 |
ON shadow.datasource_organizations |
|
807 |
USING btree |
|
808 |
(organization COLLATE pg_catalog."default"); |
|
809 |
|
|
810 |
CREATE INDEX result_funders_idx ON shadow.result USING gin (funders); |
|
811 |
CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0); |
|
812 |
CREATE INDEX result_projects_idx ON shadow.result USING gin (projects); |
|
813 |
CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources); |
|
814 |
|
|
815 |
|
|
816 |
END;$$; |
|
817 |
|
|
818 |
|
|
819 | 598 |
ALTER FUNCTION shadow.create_indexes() OWNER TO sqoop; |
820 | 599 |
|
821 | 600 |
-- |
822 | 601 |
-- Name: create_views(); Type: FUNCTION; Schema: shadow; Owner: sqoop |
823 | 602 |
-- |
824 | 603 |
|
825 |
CREATE or replace FUNCTION "shadow".create_views() RETURNS void |
|
826 |
LANGUAGE plpgsql |
|
827 |
AS $$ |
|
604 |
CREATE or replace FUNCTION "shadow".create_views() RETURNS void LANGUAGE plpgsql AS $$ |
|
605 |
BEGIN |
|
606 |
CREATE OR REPLACE VIEW shadow.datasource_results AS SELECT datasource AS id, id AS result FROM shadow.result_datasources; |
|
607 |
CREATE OR REPLACE VIEW shadow.organization_datasources AS SELECT organization AS id, id AS datasource FROM shadow.datasource_organizations; |
|
608 |
CREATE OR REPLACE VIEW shadow.organization_projects AS SELECT id AS project, organization as id FROM shadow.project_organizations; |
|
828 | 609 |
|
829 |
BEGIN |
|
610 |
CREATE OR REPLACE VIEW shadow.result_projects AS SELECT shadow.project_results.result AS id, |
|
611 |
shadow.project_results.id AS project, ( select to_date("shadow"."result"."date", 'YYYY-MM-DD')- to_date("shadow"."project"."enddate", 'YYYY-MM-DD') |
|
612 |
from shadow.result, shadow.project where shadow.result.id = shadow.project_results.result |
|
613 |
and shadow.project_results.id= shadow.project.id and shadow.result.type='publication' ) |
|
614 |
as daysfromend FROM shadow.project_results; |
|
830 | 615 |
|
831 |
CREATE OR REPLACE VIEW shadow.datasource_results AS SELECT datasource AS id, id AS result FROM shadow.result_datasources; |
|
832 |
|
|
833 |
CREATE OR REPLACE VIEW shadow.organization_datasources AS SELECT organization AS id, id AS datasource FROM shadow.datasource_organizations; |
|
834 |
|
|
835 |
CREATE OR REPLACE VIEW shadow.organization_projects AS SELECT id AS project, organization as id FROM shadow.project_organizations; |
|
836 |
|
|
837 |
|
|
838 |
CREATE OR REPLACE VIEW shadow.result_projects AS SELECT shadow.project_results.result AS id, |
|
839 |
shadow.project_results.id AS project, ( select to_date("shadow"."result"."date", 'YYYY-MM-DD')- to_date("shadow"."project"."enddate", 'YYYY-MM-DD') |
|
840 |
from shadow.result, shadow.project where shadow.result.id = shadow.project_results.result |
|
841 |
and shadow.project_results.id= shadow.project.id and shadow.result.type='publication' ) |
|
842 |
as daysfromend FROM shadow.project_results; |
|
843 |
|
|
844 |
CREATE OR REPLACE VIEW "shadow".datasource_topics AS |
|
845 |
SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic |
|
846 |
FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics |
|
847 |
WHERE "shadow".datasource.id = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND |
|
848 |
"shadow".result_topics.id = "shadow".result.id; |
|
849 |
|
|
850 |
|
|
616 |
CREATE OR REPLACE VIEW "shadow".datasource_topics AS |
|
617 |
SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic |
|
618 |
FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics |
|
619 |
WHERE "shadow".datasource.id = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND |
|
620 |
"shadow".result_topics.id = "shadow".result.id; |
|
851 | 621 |
END;$$; |
852 | 622 |
-- |
853 | 623 |
-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop |
854 | 624 |
-- |
855 | 625 |
|
856 |
CREATE or replace FUNCTION "shadow".create_charts() RETURNS void |
|
857 |
LANGUAGE plpgsql |
|
858 |
AS $$ |
|
859 |
|
|
860 |
BEGIN |
|
861 |
|
|
862 |
-- country nums |
|
863 |
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; |
|
864 |
|
|
865 |
-- country charts |
|
866 |
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; |
|
867 |
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; |
|
868 |
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; |
|
869 |
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; |
|
870 |
|
|
871 |
-- datasource charts |
|
872 |
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; |
|
873 |
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; |
|
874 |
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; |
|
875 |
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; |
|
876 |
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; |
|
877 |
|
|
878 |
-- project charts |
|
879 |
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; |
|
880 |
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; |
|
881 |
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; |
|
882 |
|
|
883 |
-- funder nums |
|
884 |
-- CREATE TABLE "shadow".numbers_funder AS SELECT funder, count(distinct r.id) AS pubstotal, count(distinct CASE WHEN r.bestlicense='Open Access' THEN r.id END) AS oapubs, count(distinct CASE WHEN r.bestlicense='Restricted' THEN r.id END) AS respubs, count(distinct CASE WHEN r.bestlicense='Embargo' THEN r.id END) AS embpubs, count(distinct p.id) AS projpubs, (SELECT count(distinct p2.id) FROM "shadow".project p2 WHERE p2.funder=p.funder GROUP BY p2.funder) AS projtotal FROM "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' GROUP BY funder ORDER BY funder; |
|
885 |
|
|
886 |
-- funder charts |
|
887 |
-- CREATE TABLE "shadow".chart_funder_year AS SELECT p.funder, r.year, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND pr.id=p.id AND r.type='publication' AND r.year >= 2007 AND r.year <= extract(YEAR from now()) GROUP BY p.funder, r.year ORDER BY p.funder, r.year; |
|
888 |
-- CREATE TABLE "shadow".chart_funder_license AS SELECT p.funder, r.bestlicense, count(distinct r.id) from "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND pr.id=p.id AND r.type='publication' GROUP BY p.funder, r.bestlicense ORDER BY p.funder, r.bestlicense; |
|
889 |
-- CREATE TABLE "shadow".chart_funder_fp7 AS SELECT p.funder, r.year, count(distinct r.id) from "shadow".result r, "shadow".project_results pr, "shadow".project p, "shadow".project_results pr2, "shadow".project p2 WHERE r.id=pr.result AND pr.id=p.id AND pr2.result=r.id AND pr2.id=p2.id AND p2.funding_lvl0='FP7' AND r.year >= 2007 AND r.year <= extract(YEAR from now()) GROUP BY p.funder, r.year ORDER BY p.funder, r.year; |
|
890 |
|
|
891 |
|
|
626 |
CREATE or replace FUNCTION "shadow".create_charts() RETURNS void LANGUAGE plpgsql AS $$ |
|
627 |
BEGIN |
|
628 |
-- 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; |
|
892 | 648 |
END;$$; |
893 | 649 |
|
894 | 650 |
-- |
895 | 651 |
-- Name: create_charts(); Type: FUNCTION; Schema: shadow; Owner: sqoop |
896 | 652 |
-- |
653 |
CREATE or replace FUNCTION "shadow".create_chart_indexes() RETURNS void LANGUAGE plpgsql AS $$ |
|
654 |
BEGIN |
|
655 |
-- country nums |
|
656 |
-- CREATE INDEX numbers_country_country ON "shadow".numbers_country USING btree(country); |
|
897 | 657 |
|
898 |
CREATE or replace FUNCTION "shadow".create_chart_indexes() RETURNS void |
|
899 |
LANGUAGE plpgsql |
|
900 |
AS $$ |
|
658 |
-- country charts |
|
659 |
-- CREATE INDEX chart_country_year_country ON "shadow".chart_country_year USING btree(country); |
|
660 |
-- CREATE INDEX chart_country_datasources_country ON "shadow".chart_country_datasources USING btree(country); |
|
661 |
-- CREATE INDEX chart_country_type_country ON "shadow".chart_country_type USING btree(country); |
|
662 |
-- CREATE INDEX chart_country_fp7_country ON "shadow".chart_country_fp7 USING btree(country); |
|
901 | 663 |
|
902 |
BEGIN |
|
664 |
-- datasource |
|
665 |
-- CREATE INDEX chart_datasource_type_datasource ON "shadow".chart_datasource_type USING btree(datasource); |
|
666 |
-- CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource); |
|
667 |
-- CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource); |
|
668 |
-- CREATE INDEX chart_datasource_projects_pubs_datasource ON "shadow".chart_datasource_projects_pubs USING btree(datasource); |
|
669 |
-- CREATE INDEX chart_datasource_projects_data_datasource ON "shadow".chart_datasource_projects_data USING btree(datasource); |
|
903 | 670 |
|
904 |
-- country nums |
|
905 |
CREATE INDEX numbers_country_country ON "shadow".numbers_country USING btree(country); |
|
906 |
|
|
907 |
-- country charts |
|
908 |
CREATE INDEX chart_country_year_country ON "shadow".chart_country_year USING btree(country); |
|
909 |
CREATE INDEX chart_country_datasources_country ON "shadow".chart_country_datasources USING btree(country); |
|
910 |
CREATE INDEX chart_country_type_country ON "shadow".chart_country_type USING btree(country); |
|
911 |
CREATE INDEX chart_country_fp7_country ON "shadow".chart_country_fp7 USING btree(country); |
|
912 |
|
|
913 |
-- datasource |
|
914 |
CREATE INDEX chart_datasource_type_datasource ON "shadow".chart_datasource_type USING btree(datasource); |
|
915 |
CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource); |
|
916 |
CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource); |
|
917 |
CREATE INDEX chart_datasource_projects_pubs_datasource ON "shadow".chart_datasource_projects_pubs USING btree(datasource); |
|
918 |
CREATE INDEX chart_datasource_projects_data_datasource ON "shadow".chart_datasource_projects_data USING btree(datasource); |
|
919 |
|
|
920 |
-- project |
|
921 |
CREATE INDEX chart_project_year_id ON "shadow".chart_project_year USING btree(id); |
|
922 |
CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id); |
|
923 |
CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id); |
|
924 |
|
|
925 |
-- funder nums |
|
926 |
-- CREATE INDEX numbers_funder_funder ON "shadow".numbers_funder USING btree(funder); |
|
927 |
|
|
928 |
-- funder charts |
|
929 |
-- CREATE INDEX chart_funder_year_funder ON "shadow".chart_funder_year USING btree(funder); |
|
930 |
-- CREATE INDEX chart_funder_license_funder ON "shadow".chart_funder_license USING btree(funder); |
|
931 |
|
|
932 |
|
|
671 |
-- project |
|
672 |
-- CREATE INDEX chart_project_year_id ON "shadow".chart_project_year USING btree(id); |
|
673 |
-- CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id); |
|
674 |
-- CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id); |
|
933 | 675 |
END;$$; |
934 | 676 |
|
935 | 677 |
-- |
936 | 678 |
-- Name: clean_tables(); Type: FUNCTION; Schema: shadow; Owner: sqoop |
937 | 679 |
-- |
680 |
CREATE or replace FUNCTION "shadow".clean_tables() RETURNS void LANGUAGE plpgsql AS $$ |
|
681 |
BEGIN |
|
682 |
CREATE TABLE "shadow".rd_distinct AS SELECT DISTINCT * FROM "shadow".result_datasources; |
|
938 | 683 |
|
939 |
CREATE or replace FUNCTION "shadow".clean_tables() RETURNS void |
|
940 |
LANGUAGE plpgsql |
|
941 |
AS $$ |
|
942 |
BEGIN |
|
684 |
DROP TABLE "shadow".result_datasources; |
|
685 |
ALTER TABLE "shadow".rd_distinct RENAME TO result_datasources; |
|
943 | 686 |
|
944 |
CREATE TABLE "shadow".rd_distinct AS SELECT DISTINCT * FROM "shadow".result_datasources;
|
|
945 |
TRUNCATE "shadow".result_datasources;
|
|
946 |
INSERT INTO "shadow".result_datasources SELECT * FROM "shadow".rd_distinct ORDER BY datasource;
|
|
947 |
DROP TABLE "shadow".rd_distinct;
|
|
687 |
-- TRUNCATE "shadow".result_datasources;
|
|
688 |
-- INSERT INTO "shadow".result_datasources SELECT * FROM "shadow".rd_distinct ORDER BY datasource;
|
|
689 |
-- DROP TABLE "shadow".rd_distinct;
|
|
690 |
END;$$;
|
|
948 | 691 |
|
949 | 692 |
|
693 |
CREATE OR REPLACE FUNCTION "shadow".insert_countries() RETURNS void LANGUAGE plpgsql AS $$ |
|
694 |
BEGIN |
|
695 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AD', 'Andorra', 'EU', 'Europe'); |
|
696 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AE', 'United Arab Emirates', 'AS', 'Asia'); |
|
697 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AF', 'Afghanistan', 'AS', 'Asia'); |
|
698 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AG', 'Antigua and Barbuda', 'NA', 'North America'); |
|
699 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AI', 'Anguilla', 'NA', 'North America'); |
|
700 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AL', 'Albania', 'EU', 'Europe'); |
|
701 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AM', 'Armenia', 'AS', 'Asia'); |
|
702 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AO', 'Angola', 'AF', 'Africa'); |
|
703 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AQ', 'Antarctica', 'AN', 'Antarctica'); |
|
704 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AR', 'Argentina', 'SA', 'South America'); |
|
705 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AS', 'American Samoa', 'OC', 'Oceania'); |
|
706 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AT', 'Austria', 'EU', 'Europe'); |
|
707 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AU', 'Australia', 'OC', 'Oceania'); |
|
708 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AW', 'Aruba', 'NA', 'North America'); |
|
709 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AX', 'Åland Islands', 'EU', 'Europe'); |
|
710 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('AZ', 'Azerbaijan', 'AS', 'Asia'); |
|
711 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BA', 'Bosnia and Herzegovina', 'EU', 'Europe'); |
|
712 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BB', 'Barbados', 'NA', 'North America'); |
|
713 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BD', 'Bangladesh', 'AS', 'Asia'); |
|
714 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BE', 'Belgium', 'EU', 'Europe'); |
|
715 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BF', 'Burkina Faso', 'AF', 'Africa'); |
|
716 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BG', 'Bulgaria', 'EU', 'Europe'); |
|
717 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BH', 'Bahrain', 'AS', 'Asia'); |
|
718 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BI', 'Burundi', 'AF', 'Africa'); |
|
719 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BJ', 'Benin', 'AF', 'Africa'); |
|
720 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BL', 'Saint Barthélemy', 'NA', 'North America'); |
|
721 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BM', 'Bermuda', 'NA', 'North America'); |
|
722 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BN', 'Brunei Darussalam', 'AS', 'Asia'); |
|
723 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BO', 'Bolivia, Plurinational State of', 'SA', 'South America'); |
|
724 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BR', 'Brazil', 'SA', 'South America'); |
|
725 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BS', 'Bahamas', 'NA', 'North America'); |
|
726 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BT', 'Bhutan', 'AS', 'Asia'); |
|
727 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BV', 'Bouvet Island', 'AN', 'Antarctica'); |
|
728 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BW', 'Botswana', 'AF', 'Africa'); |
|
729 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BY', 'Belarus', 'EU', 'Europe'); |
|
730 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('BZ', 'Belize', 'NA', 'North America'); |
|
731 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CA', 'Canada', 'NA', 'North America'); |
|
732 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CC', 'Cocos (Keeling) Islands', 'AS', 'Asia'); |
|
733 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CD', 'Congo, the Democratic Republic of the', 'AF', 'Africa'); |
|
734 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CF', 'Central African Republic', 'AF', 'Africa'); |
|
735 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CG', 'Congo', 'AF', 'Africa'); |
|
736 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CH', 'Switzerland', 'EU', 'Europe'); |
|
737 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CI', 'Côte d''Ivoire', 'AF', 'Africa'); |
|
738 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CK', 'Cook Islands', 'OC', 'Oceania'); |
|
739 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CL', 'Chile', 'SA', 'South America'); |
|
740 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CM', 'Cameroon', 'AF', 'Africa'); |
|
741 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CN', 'China', 'AS', 'Asia'); |
|
742 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CO', 'Colombia', 'SA', 'South America'); |
|
743 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CR', 'Costa Rica', 'NA', 'North America'); |
|
744 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CU', 'Cuba', 'NA', 'North America'); |
|
745 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CV', 'Cape Verde', 'AF', 'Africa'); |
|
746 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CX', 'Christmas Island', 'AS', 'Asia'); |
|
747 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CY', 'Cyprus', 'AS', 'Asia'); |
|
748 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('CZ', 'Czech Republic', 'EU', 'Europe'); |
|
749 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DE', 'Germany', 'EU', 'Europe'); |
|
750 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DJ', 'Djibouti', 'AF', 'Africa'); |
|
751 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DK', 'Denmark', 'EU', 'Europe'); |
|
752 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DM', 'Dominica', 'NA', 'North America'); |
|
753 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DO', 'Dominican Republic', 'NA', 'North America'); |
|
754 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('DZ', 'Algeria', 'AF', 'Africa'); |
|
755 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EC', 'Ecuador', 'SA', 'South America'); |
|
756 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EE', 'Estonia', 'EU', 'Europe'); |
|
757 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EG', 'Egypt', 'AF', 'Africa'); |
|
758 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('EH', 'Western Sahara', 'AF', 'Africa'); |
|
759 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ER', 'Eritrea', 'AF', 'Africa'); |
|
760 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ES', 'Spain', 'EU', 'Europe'); |
|
761 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ET', 'Ethiopia', 'AF', 'Africa'); |
|
762 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FI', 'Finland', 'EU', 'Europe'); |
|
763 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FJ', 'Fiji', 'OC', 'Oceania'); |
|
764 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FK', 'Falkland Islands (Malvinas)', 'SA', 'South America'); |
|
765 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FM', 'Micronesia, Federated States of', 'OC', 'Oceania'); |
|
766 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FO', 'Faroe Islands', 'EU', 'Europe'); |
|
767 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('FR', 'France', 'EU', 'Europe'); |
|
768 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GA', 'Gabon', 'AF', 'Africa'); |
|
769 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GB', 'United Kingdom', 'EU', 'Europe'); |
|
770 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GD', 'Grenada', 'NA', 'North America'); |
|
771 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GE', 'Georgia', 'AS', 'Asia'); |
|
772 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GF', 'French Guiana', 'SA', 'South America'); |
|
773 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GG', 'Guernsey', 'EU', 'Europe'); |
|
774 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GH', 'Ghana', 'AF', 'Africa'); |
|
775 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GI', 'Gibraltar', 'EU', 'Europe'); |
|
776 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GL', 'Greenland', 'NA', 'North America'); |
|
777 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GM', 'Gambia', 'AF', 'Africa'); |
|
778 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GN', 'Guinea', 'AF', 'Africa'); |
|
779 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GP', 'Guadeloupe', 'NA', 'North America'); |
|
780 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GQ', 'Equatorial Guinea', 'AF', 'Africa'); |
|
781 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GR', 'Greece', 'EU', 'Europe'); |
|
782 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GS', 'South Georgia and the South Sandwich Islands', 'AN', 'Antarctica'); |
|
783 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GT', 'Guatemala', 'NA', 'North America'); |
|
784 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GU', 'Guam', 'OC', 'Oceania'); |
|
785 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GW', 'Guinea-Bissau', 'AF', 'Africa'); |
|
786 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('GY', 'Guyana', 'SA', 'South America'); |
|
787 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HK', 'Hong Kong', 'AS', 'Asia'); |
|
788 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HM', 'Heard Island and McDonald Islands', 'AN', 'Antarctica'); |
|
789 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HN', 'Honduras', 'NA', 'North America'); |
|
790 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HR', 'Croatia', 'EU', 'Europe'); |
|
791 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HT', 'Haiti', 'NA', 'North America'); |
|
792 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('HU', 'Hungary', 'EU', 'Europe'); |
|
793 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ID', 'Indonesia', 'AS', 'Asia'); |
|
794 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IE', 'Ireland', 'EU', 'Europe'); |
|
795 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IL', 'Israel', 'AS', 'Asia'); |
|
796 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IM', 'Isle of Man', 'EU', 'Europe'); |
|
797 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IN', 'India', 'AS', 'Asia'); |
|
798 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IO', 'British Indian Ocean Territory', 'AS', 'Asia'); |
|
799 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IQ', 'Iraq', 'AS', 'Asia'); |
|
800 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IR', 'Iran, Islamic Republic of', 'AS', 'Asia'); |
|
801 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IS', 'Iceland', 'EU', 'Europe'); |
|
802 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('IT', 'Italy', 'EU', 'Europe'); |
|
803 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JE', 'Jersey', 'EU', 'Europe'); |
|
804 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JM', 'Jamaica', 'NA', 'North America'); |
|
805 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JO', 'Jordan', 'AS', 'Asia'); |
|
806 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('JP', 'Japan', 'AS', 'Asia'); |
|
807 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KE', 'Kenya', 'AF', 'Africa'); |
|
808 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KG', 'Kyrgyzstan', 'AS', 'Asia'); |
|
809 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KH', 'Cambodia', 'AS', 'Asia'); |
|
810 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KI', 'Kiribati', 'OC', 'Oceania'); |
|
811 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KM', 'Comoros', 'AF', 'Africa'); |
|
812 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KN', 'Saint Kitts and Nevis', 'NA', 'North America'); |
|
813 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KP', 'Korea, Democratic People''s Republic of', 'AS', 'Asia'); |
|
814 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KR', 'Korea, Republic of', 'AS', 'Asia'); |
|
815 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KW', 'Kuwait', 'AS', 'Asia'); |
|
816 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KY', 'Cayman Islands', 'NA', 'North America'); |
|
817 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('KZ', 'Kazakhstan', 'AS', 'Asia'); |
|
818 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LA', 'Lao People''s Democratic Republic', 'AS', 'Asia'); |
|
819 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LB', 'Lebanon', 'AS', 'Asia'); |
|
820 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LC', 'Saint Lucia', 'NA', 'North America'); |
|
821 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LI', 'Liechtenstein', 'EU', 'Europe'); |
|
822 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LK', 'Sri Lanka', 'AS', 'Asia'); |
|
823 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LR', 'Liberia', 'AF', 'Africa'); |
|
824 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LS', 'Lesotho', 'AF', 'Africa'); |
|
825 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LT', 'Lithuania', 'EU', 'Europe'); |
|
826 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LU', 'Luxembourg', 'EU', 'Europe'); |
|
827 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LV', 'Latvia', 'EU', 'Europe'); |
|
828 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('LY', 'Libya', 'AF', 'Africa'); |
|
829 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MA', 'Morocco', 'AF', 'Africa'); |
|
830 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MC', 'Monaco', 'EU', 'Europe'); |
|
831 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MD', 'Moldova, Republic of', 'EU', 'Europe'); |
|
832 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ME', 'Montenegro', 'EU', 'Europe'); |
|
833 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MF', 'Saint Martin (French part)', 'NA', 'North America'); |
|
834 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MG', 'Madagascar', 'AF', 'Africa'); |
|
835 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MH', 'Marshall Islands', 'OC', 'Oceania'); |
|
836 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MK', 'Macedonia, the Former Yugoslav Republic of', 'EU', 'Europe'); |
|
837 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ML', 'Mali', 'AF', 'Africa'); |
|
838 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MM', 'Myanmar', 'AS', 'Asia'); |
|
839 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MN', 'Mongolia', 'AS', 'Asia'); |
|
840 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MO', 'Macao', 'AS', 'Asia'); |
|
841 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MP', 'Northern Mariana Islands', 'OC', 'Oceania'); |
|
842 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MQ', 'Martinique', 'NA', 'North America'); |
|
843 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MR', 'Mauritania', 'AF', 'Africa'); |
|
844 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MS', 'Montserrat', 'NA', 'North America'); |
|
845 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MT', 'Malta', 'EU', 'Europe'); |
|
846 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MU', 'Mauritius', 'AF', 'Africa'); |
|
847 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MV', 'Maldives', 'AS', 'Asia'); |
|
848 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MW', 'Malawi', 'AF', 'Africa'); |
|
849 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MX', 'Mexico', 'NA', 'North America'); |
|
850 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MY', 'Malaysia', 'AS', 'Asia'); |
|
851 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('MZ', 'Mozambique', 'AF', 'Africa'); |
|
852 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NA', 'Namibia', 'AF', 'Africa'); |
|
853 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NC', 'New Caledonia', 'OC', 'Oceania'); |
|
854 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NE', 'Niger', 'AF', 'Africa'); |
|
855 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NF', 'Norfolk Island', 'OC', 'Oceania'); |
|
856 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NG', 'Nigeria', 'AF', 'Africa'); |
|
857 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NI', 'Nicaragua', 'NA', 'North America'); |
|
858 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NL', 'Netherlands', 'EU', 'Europe'); |
|
859 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NO', 'Norway', 'EU', 'Europe'); |
|
860 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NP', 'Nepal', 'AS', 'Asia'); |
|
861 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NR', 'Nauru', 'OC', 'Oceania'); |
|
862 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NU', 'Niue', 'OC', 'Oceania'); |
|
863 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('NZ', 'New Zealand', 'OC', 'Oceania'); |
|
864 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('OM', 'Oman', 'AS', 'Asia'); |
|
865 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PA', 'Panama', 'NA', 'North America'); |
|
866 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PE', 'Peru', 'SA', 'South America'); |
|
867 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PF', 'French Polynesia', 'OC', 'Oceania'); |
|
868 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PG', 'Papua New Guinea', 'OC', 'Oceania'); |
|
869 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PH', 'Philippines', 'AS', 'Asia'); |
|
870 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PK', 'Pakistan', 'AS', 'Asia'); |
|
871 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PL', 'Poland', 'EU', 'Europe'); |
|
872 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PM', 'Saint Pierre and Miquelon', 'NA', 'North America'); |
|
873 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PN', 'Pitcairn', 'OC', 'Oceania'); |
|
874 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PR', 'Puerto Rico', 'NA', 'North America'); |
|
875 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PS', 'Palestine, State of', 'AS', 'Asia'); |
|
876 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PT', 'Portugal', 'EU', 'Europe'); |
|
877 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PW', 'Palau', 'OC', 'Oceania'); |
|
878 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('PY', 'Paraguay', 'SA', 'South America'); |
|
879 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('QA', 'Qatar', 'AS', 'Asia'); |
|
880 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RE', 'Réunion', 'AF', 'Africa'); |
|
881 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RO', 'Romania', 'EU', 'Europe'); |
|
882 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RS', 'Serbia', 'EU', 'Europe'); |
|
883 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RU', 'Russian Federation', 'EU', 'Europe'); |
|
884 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('RW', 'Rwanda', 'AF', 'Africa'); |
|
885 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SA', 'Saudi Arabia', 'AS', 'Asia'); |
|
886 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SB', 'Solomon Islands', 'OC', 'Oceania'); |
|
887 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SC', 'Seychelles', 'AF', 'Africa'); |
|
888 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SD', 'Sudan', 'AF', 'Africa'); |
|
889 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SE', 'Sweden', 'EU', 'Europe'); |
|
890 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SG', 'Singapore', 'AS', 'Asia'); |
|
891 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SH', 'Saint Helena, Ascension and Tristan da Cunha', 'AF', 'Africa'); |
|
892 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SI', 'Slovenia', 'EU', 'Europe'); |
|
893 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SJ', 'Svalbard and Jan Mayen', 'EU', 'Europe'); |
|
894 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SK', 'Slovakia', 'EU', 'Europe'); |
|
895 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SL', 'Sierra Leone', 'AF', 'Africa'); |
|
896 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SM', 'San Marino', 'EU', 'Europe'); |
|
897 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SN', 'Senegal', 'AF', 'Africa'); |
|
898 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SO', 'Somalia', 'AF', 'Africa'); |
|
899 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SR', 'Suriname', 'SA', 'South America'); |
|
900 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ST', 'Sao Tome and Principe', 'AF', 'Africa'); |
|
901 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SV', 'El Salvador', 'NA', 'North America'); |
|
902 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SY', 'Syrian Arab Republic', 'AS', 'Asia'); |
|
903 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('SZ', 'Swaziland', 'AF', 'Africa'); |
|
904 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TC', 'Turks and Caicos Islands', 'NA', 'North America'); |
|
905 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TD', 'Chad', 'AF', 'Africa'); |
|
906 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TF', 'French Southern Territories', 'AN', 'Antarctica'); |
|
907 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TG', 'Togo', 'AF', 'Africa'); |
|
908 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TH', 'Thailand', 'AS', 'Asia'); |
|
909 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TJ', 'Tajikistan', 'AS', 'Asia'); |
|
910 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TK', 'Tokelau', 'OC', 'Oceania'); |
|
911 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TL', 'Timor-Leste', 'AS', 'Asia'); |
|
912 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TM', 'Turkmenistan', 'AS', 'Asia'); |
|
913 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TN', 'Tunisia', 'AF', 'Africa'); |
|
914 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TO', 'Tonga', 'OC', 'Oceania'); |
|
915 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TR', 'Turkey', 'EU', 'Europe'); |
|
916 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TT', 'Trinidad and Tobago', 'NA', 'North America'); |
|
917 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TV', 'Tuvalu', 'OC', 'Oceania'); |
|
918 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TW', 'Taiwan, Province of China', 'AS', 'Asia'); |
|
919 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('TZ', 'Tanzania, United Republic of', 'AF', 'Africa'); |
|
920 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UA', 'Ukraine', 'EU', 'Europe'); |
|
921 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UG', 'Uganda', 'AF', 'Africa'); |
|
922 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UM', 'United States Minor Outlying Islands', 'OC', 'Oceania'); |
|
923 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('US', 'United States', 'NA', 'North America'); |
|
924 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UY', 'Uruguay', 'SA', 'South America'); |
|
925 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('UZ', 'Uzbekistan', 'AS', 'Asia'); |
|
926 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VA', 'Holy See (Vatican City State)', 'EU', 'Europe'); |
|
927 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VC', 'Saint Vincent and the Grenadines', 'NA', 'North America'); |
|
928 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VE', 'Venezuela, Bolivarian Republic of', 'SA', 'South America'); |
|
929 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VG', 'Virgin Islands, British', 'NA', 'North America'); |
|
930 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VI', 'Virgin Islands, U.S.', 'NA', 'North America'); |
|
931 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VN', 'Viet Nam', 'AS', 'Asia'); |
|
932 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('VU', 'Vanuatu', 'OC', 'Oceania'); |
|
933 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WF', 'Wallis and Futuna', 'OC', 'Oceania'); |
|
934 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('WS', 'Samoa', 'OC', 'Oceania'); |
|
935 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YE', 'Yemen', 'AS', 'Asia'); |
|
936 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('YT', 'Mayotte', 'AF', 'Africa'); |
|
937 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZA', 'South Africa', 'AF', 'Africa'); |
|
938 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZM', 'Zambia', 'AF', 'Africa'); |
|
939 |
INSERT INTO shadow.country (code, name, continent_code, continent_name) VALUES ('ZW', 'Zimbabwe', 'AF', 'Africa'); |
|
950 | 940 |
END;$$; |
951 | 941 |
|
942 |
CREATE OR REPLACE FUNCTION "shadow".backwards_compatibility() RETURNS void LANGUAGE plpgsql AS $$ |
|
943 |
BEGIN |
|
944 |
ALTER TABLE "shadow".result RENAME TO tresult; |
|
945 |
ALTER TABLE "shadow".datasource RENAME TO tdatasource; |
|
946 |
ALTER TABLE "shadow".project RENAME TO tproject; |
|
947 |
ALTER TABLE "shadow".organization RENAME TO torganization; |
|
948 |
|
|
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".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 |
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 |
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; |
|
953 |
END;$$; |
|
954 |
|
|
955 |
|
|
952 | 956 |
-- sqoopQL database dump complete |
953 |
-- |
|
957 |
-- |
Also available in: Unified diff
numerous bug fixes and changes to schema