Revision 13866
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 93 | 93 |
|
| 94 | 94 |
|
| 95 | 95 |
-- |
| 96 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 97 |
-- |
|
| 98 |
|
|
| 99 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger |
|
| 100 |
LANGUAGE plpgsql |
|
| 101 |
AS $$ |
|
| 102 |
BEGIN |
|
| 103 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 104 |
RETURN NULL; |
|
| 105 |
END; |
|
| 106 |
$$; |
|
| 107 |
|
|
| 108 |
|
|
| 109 |
-- |
|
| 96 | 110 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
| 97 | 111 |
-- |
| 98 | 112 |
|
| ... | ... | |
| 537 | 551 |
|
| 538 | 552 |
|
| 539 | 553 |
-- |
| 554 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
|
| 555 |
-- |
|
| 556 |
|
|
| 557 |
CREATE VIEW taxon_match_input AS |
|
| 558 |
SELECT taxon_match."Name_number", |
|
| 559 |
taxon_match."Name_submitted", |
|
| 560 |
taxon_match."Overall_score", |
|
| 561 |
taxon_match."Name_matched", |
|
| 562 |
taxon_match."Name_matched_rank", |
|
| 563 |
taxon_match."Name_score", |
|
| 564 |
taxon_match."Name_matched_author", |
|
| 565 |
taxon_match."Name_matched_url", |
|
| 566 |
taxon_match."Author_matched", |
|
| 567 |
taxon_match."Author_score", |
|
| 568 |
taxon_match."Family_matched", |
|
| 569 |
taxon_match."Family_score", |
|
| 570 |
taxon_match."Name_matched_accepted_family", |
|
| 571 |
taxon_match."Genus_matched", |
|
| 572 |
taxon_match."Genus_score", |
|
| 573 |
taxon_match."Specific_epithet_matched", |
|
| 574 |
taxon_match."Specific_epithet_score", |
|
| 575 |
taxon_match."Infraspecific_rank", |
|
| 576 |
taxon_match."Infraspecific_epithet_matched", |
|
| 577 |
taxon_match."Infraspecific_epithet_score", |
|
| 578 |
taxon_match."Infraspecific_rank_2", |
|
| 579 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 580 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 581 |
taxon_match."Annotations", |
|
| 582 |
taxon_match."Unmatched_terms", |
|
| 583 |
taxon_match."Taxonomic_status", |
|
| 584 |
taxon_match."Accepted_name", |
|
| 585 |
taxon_match."Accepted_name_author", |
|
| 586 |
taxon_match."Accepted_name_rank", |
|
| 587 |
taxon_match."Accepted_name_url", |
|
| 588 |
taxon_match."Accepted_name_species", |
|
| 589 |
taxon_match."Accepted_name_family", |
|
| 590 |
taxon_match."Selected", |
|
| 591 |
taxon_match."Source", |
|
| 592 |
taxon_match."Warnings", |
|
| 593 |
taxon_match."Accepted_name_lsid" |
|
| 594 |
FROM taxon_match; |
|
| 595 |
|
|
| 596 |
|
|
| 597 |
-- |
|
| 598 |
-- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 599 |
-- |
|
| 600 |
|
|
| 601 |
CREATE TABLE taxon_match_input__copy_to ( |
|
| 602 |
"Name_number" integer, |
|
| 603 |
"Name_submitted" text, |
|
| 604 |
"Overall_score" double precision, |
|
| 605 |
"Name_matched" text, |
|
| 606 |
"Name_matched_rank" text, |
|
| 607 |
"Name_score" double precision, |
|
| 608 |
"Name_matched_author" text, |
|
| 609 |
"Name_matched_url" text, |
|
| 610 |
"Author_matched" text, |
|
| 611 |
"Author_score" double precision, |
|
| 612 |
"Family_matched" text, |
|
| 613 |
"Family_score" double precision, |
|
| 614 |
"Name_matched_accepted_family" text, |
|
| 615 |
"Genus_matched" text, |
|
| 616 |
"Genus_score" double precision, |
|
| 617 |
"Specific_epithet_matched" text, |
|
| 618 |
"Specific_epithet_score" double precision, |
|
| 619 |
"Infraspecific_rank" text, |
|
| 620 |
"Infraspecific_epithet_matched" text, |
|
| 621 |
"Infraspecific_epithet_score" double precision, |
|
| 622 |
"Infraspecific_rank_2" text, |
|
| 623 |
"Infraspecific_epithet_2_matched" text, |
|
| 624 |
"Infraspecific_epithet_2_score" double precision, |
|
| 625 |
"Annotations" text, |
|
| 626 |
"Unmatched_terms" text, |
|
| 627 |
"Taxonomic_status" text, |
|
| 628 |
"Accepted_name" text, |
|
| 629 |
"Accepted_name_author" text, |
|
| 630 |
"Accepted_name_rank" text, |
|
| 631 |
"Accepted_name_url" text, |
|
| 632 |
"Accepted_name_species" text, |
|
| 633 |
"Accepted_name_family" text, |
|
| 634 |
"Selected" text, |
|
| 635 |
"Source" text, |
|
| 636 |
"Warnings" text, |
|
| 637 |
"Accepted_name_lsid" text |
|
| 638 |
); |
|
| 639 |
|
|
| 640 |
|
|
| 641 |
-- |
|
| 540 | 642 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: - |
| 541 | 643 |
-- |
| 542 | 644 |
|
| ... | ... | |
| 704 | 806 |
|
| 705 | 807 |
|
| 706 | 808 |
-- |
| 809 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: - |
|
| 810 |
-- |
|
| 811 |
|
|
| 812 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert(); |
|
| 813 |
|
|
| 814 |
|
|
| 815 |
-- |
|
| 707 | 816 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: - |
| 708 | 817 |
-- |
| 709 | 818 |
|
| 710 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin(); |
|
| 819 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();
|
|
| 711 | 820 |
|
| 712 | 821 |
|
| 713 | 822 |
-- |
| ... | ... | |
| 789 | 898 |
|
| 790 | 899 |
|
| 791 | 900 |
-- |
| 901 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
|
| 902 |
-- |
|
| 903 |
|
|
| 904 |
REVOKE ALL ON TABLE taxon_match_input FROM PUBLIC; |
|
| 905 |
REVOKE ALL ON TABLE taxon_match_input FROM bien; |
|
| 906 |
GRANT ALL ON TABLE taxon_match_input TO bien; |
|
| 907 |
GRANT SELECT ON TABLE taxon_match_input TO bien_read; |
|
| 908 |
|
|
| 909 |
|
|
| 910 |
-- |
|
| 792 | 911 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: - |
| 793 | 912 |
-- |
| 794 | 913 |
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15277 | 15277 |
|
| 15278 | 15278 |
|
| 15279 | 15279 |
-- |
| 15280 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 15281 |
-- |
|
| 15282 |
|
|
| 15283 |
|
|
| 15284 |
|
|
| 15285 |
|
|
| 15286 |
-- |
|
| 15280 | 15287 |
-- Name: taxon_name_is_safe(varchar(255)); Type: FUNCTION; Schema: TNRS; Owner: - |
| 15281 | 15288 |
-- |
| 15282 | 15289 |
|
| ... | ... | |
| 15512 | 15519 |
|
| 15513 | 15520 |
|
| 15514 | 15521 |
-- |
| 15522 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
|
| 15523 |
-- |
|
| 15524 |
|
|
| 15525 |
|
|
| 15526 |
|
|
| 15527 |
|
|
| 15528 |
-- |
|
| 15529 |
-- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 15530 |
-- |
|
| 15531 |
|
|
| 15532 |
CREATE TABLE taxon_match_input__copy_to ( |
|
| 15533 |
`Name_number` int(11), |
|
| 15534 |
`Name_submitted` varchar(255), |
|
| 15535 |
`Overall_score` double, |
|
| 15536 |
`Name_matched` varchar(255), |
|
| 15537 |
`Name_matched_rank` varchar(255), |
|
| 15538 |
`Name_score` double, |
|
| 15539 |
`Name_matched_author` varchar(255), |
|
| 15540 |
`Name_matched_url` varchar(255), |
|
| 15541 |
`Author_matched` varchar(255), |
|
| 15542 |
`Author_score` double, |
|
| 15543 |
`Family_matched` varchar(255), |
|
| 15544 |
`Family_score` double, |
|
| 15545 |
`Name_matched_accepted_family` varchar(255), |
|
| 15546 |
`Genus_matched` varchar(255), |
|
| 15547 |
`Genus_score` double, |
|
| 15548 |
`Specific_epithet_matched` varchar(255), |
|
| 15549 |
`Specific_epithet_score` double, |
|
| 15550 |
`Infraspecific_rank` varchar(255), |
|
| 15551 |
`Infraspecific_epithet_matched` varchar(255), |
|
| 15552 |
`Infraspecific_epithet_score` double, |
|
| 15553 |
`Infraspecific_rank_2` varchar(255), |
|
| 15554 |
`Infraspecific_epithet_2_matched` varchar(255), |
|
| 15555 |
`Infraspecific_epithet_2_score` double, |
|
| 15556 |
`Annotations` varchar(255), |
|
| 15557 |
`Unmatched_terms` varchar(255), |
|
| 15558 |
`Taxonomic_status` varchar(255), |
|
| 15559 |
`Accepted_name` varchar(255), |
|
| 15560 |
`Accepted_name_author` varchar(255), |
|
| 15561 |
`Accepted_name_rank` varchar(255), |
|
| 15562 |
`Accepted_name_url` varchar(255), |
|
| 15563 |
`Accepted_name_species` varchar(255), |
|
| 15564 |
`Accepted_name_family` varchar(255), |
|
| 15565 |
`Selected` varchar(255), |
|
| 15566 |
`Source` varchar(255), |
|
| 15567 |
`Warnings` varchar(255), |
|
| 15568 |
`Accepted_name_lsid` varchar(255) |
|
| 15569 |
); |
|
| 15570 |
|
|
| 15571 |
|
|
| 15572 |
-- |
|
| 15515 | 15573 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: - |
| 15516 | 15574 |
-- |
| 15517 | 15575 |
|
| ... | ... | |
| 15683 | 15741 |
|
| 15684 | 15742 |
|
| 15685 | 15743 |
-- |
| 15744 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: - |
|
| 15745 |
-- |
|
| 15746 |
|
|
| 15747 |
|
|
| 15748 |
|
|
| 15749 |
|
|
| 15750 |
-- |
|
| 15686 | 15751 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: - |
| 15687 | 15752 |
-- |
| 15688 | 15753 |
|
| ... | ... | |
| 15768 | 15833 |
|
| 15769 | 15834 |
|
| 15770 | 15835 |
-- |
| 15836 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
|
| 15837 |
-- |
|
| 15838 |
|
|
| 15839 |
|
|
| 15840 |
|
|
| 15841 |
|
|
| 15842 |
|
|
| 15843 |
|
|
| 15844 |
|
|
| 15845 |
-- |
|
| 15771 | 15846 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: - |
| 15772 | 15847 |
-- |
| 15773 | 15848 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19042 | 19042 |
|
| 19043 | 19043 |
|
| 19044 | 19044 |
-- |
| 19045 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19046 |
-- |
|
| 19047 |
|
|
| 19048 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger |
|
| 19049 |
LANGUAGE plpgsql |
|
| 19050 |
AS $$ |
|
| 19051 |
BEGIN |
|
| 19052 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 19053 |
RETURN NULL; |
|
| 19054 |
END; |
|
| 19055 |
$$; |
|
| 19056 |
|
|
| 19057 |
|
|
| 19058 |
-- |
|
| 19045 | 19059 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
| 19046 | 19060 |
-- |
| 19047 | 19061 |
|
| ... | ... | |
| 19528 | 19542 |
|
| 19529 | 19543 |
|
| 19530 | 19544 |
-- |
| 19545 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
|
| 19546 |
-- |
|
| 19547 |
|
|
| 19548 |
CREATE VIEW taxon_match_input AS |
|
| 19549 |
SELECT taxon_match."Name_number", |
|
| 19550 |
taxon_match."Name_submitted", |
|
| 19551 |
taxon_match."Overall_score", |
|
| 19552 |
taxon_match."Name_matched", |
|
| 19553 |
taxon_match."Name_matched_rank", |
|
| 19554 |
taxon_match."Name_score", |
|
| 19555 |
taxon_match."Name_matched_author", |
|
| 19556 |
taxon_match."Name_matched_url", |
|
| 19557 |
taxon_match."Author_matched", |
|
| 19558 |
taxon_match."Author_score", |
|
| 19559 |
taxon_match."Family_matched", |
|
| 19560 |
taxon_match."Family_score", |
|
| 19561 |
taxon_match."Name_matched_accepted_family", |
|
| 19562 |
taxon_match."Genus_matched", |
|
| 19563 |
taxon_match."Genus_score", |
|
| 19564 |
taxon_match."Specific_epithet_matched", |
|
| 19565 |
taxon_match."Specific_epithet_score", |
|
| 19566 |
taxon_match."Infraspecific_rank", |
|
| 19567 |
taxon_match."Infraspecific_epithet_matched", |
|
| 19568 |
taxon_match."Infraspecific_epithet_score", |
|
| 19569 |
taxon_match."Infraspecific_rank_2", |
|
| 19570 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 19571 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 19572 |
taxon_match."Annotations", |
|
| 19573 |
taxon_match."Unmatched_terms", |
|
| 19574 |
taxon_match."Taxonomic_status", |
|
| 19575 |
taxon_match."Accepted_name", |
|
| 19576 |
taxon_match."Accepted_name_author", |
|
| 19577 |
taxon_match."Accepted_name_rank", |
|
| 19578 |
taxon_match."Accepted_name_url", |
|
| 19579 |
taxon_match."Accepted_name_species", |
|
| 19580 |
taxon_match."Accepted_name_family", |
|
| 19581 |
taxon_match."Selected", |
|
| 19582 |
taxon_match."Source", |
|
| 19583 |
taxon_match."Warnings", |
|
| 19584 |
taxon_match."Accepted_name_lsid" |
|
| 19585 |
FROM taxon_match; |
|
| 19586 |
|
|
| 19587 |
|
|
| 19588 |
-- |
|
| 19589 |
-- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 19590 |
-- |
|
| 19591 |
|
|
| 19592 |
CREATE TABLE taxon_match_input__copy_to ( |
|
| 19593 |
"Name_number" integer, |
|
| 19594 |
"Name_submitted" text, |
|
| 19595 |
"Overall_score" double precision, |
|
| 19596 |
"Name_matched" text, |
|
| 19597 |
"Name_matched_rank" text, |
|
| 19598 |
"Name_score" double precision, |
|
| 19599 |
"Name_matched_author" text, |
|
| 19600 |
"Name_matched_url" text, |
|
| 19601 |
"Author_matched" text, |
|
| 19602 |
"Author_score" double precision, |
|
| 19603 |
"Family_matched" text, |
|
| 19604 |
"Family_score" double precision, |
|
| 19605 |
"Name_matched_accepted_family" text, |
|
| 19606 |
"Genus_matched" text, |
|
| 19607 |
"Genus_score" double precision, |
|
| 19608 |
"Specific_epithet_matched" text, |
|
| 19609 |
"Specific_epithet_score" double precision, |
|
| 19610 |
"Infraspecific_rank" text, |
|
| 19611 |
"Infraspecific_epithet_matched" text, |
|
| 19612 |
"Infraspecific_epithet_score" double precision, |
|
| 19613 |
"Infraspecific_rank_2" text, |
|
| 19614 |
"Infraspecific_epithet_2_matched" text, |
|
| 19615 |
"Infraspecific_epithet_2_score" double precision, |
|
| 19616 |
"Annotations" text, |
|
| 19617 |
"Unmatched_terms" text, |
|
| 19618 |
"Taxonomic_status" text, |
|
| 19619 |
"Accepted_name" text, |
|
| 19620 |
"Accepted_name_author" text, |
|
| 19621 |
"Accepted_name_rank" text, |
|
| 19622 |
"Accepted_name_url" text, |
|
| 19623 |
"Accepted_name_species" text, |
|
| 19624 |
"Accepted_name_family" text, |
|
| 19625 |
"Selected" text, |
|
| 19626 |
"Source" text, |
|
| 19627 |
"Warnings" text, |
|
| 19628 |
"Accepted_name_lsid" text |
|
| 19629 |
); |
|
| 19630 |
|
|
| 19631 |
|
|
| 19632 |
-- |
|
| 19531 | 19633 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: - |
| 19532 | 19634 |
-- |
| 19533 | 19635 |
|
| ... | ... | |
| 19772 | 19874 |
|
| 19773 | 19875 |
|
| 19774 | 19876 |
-- |
| 19877 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: - |
|
| 19878 |
-- |
|
| 19879 |
|
|
| 19880 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert(); |
|
| 19881 |
|
|
| 19882 |
|
|
| 19883 |
-- |
|
| 19775 | 19884 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: - |
| 19776 | 19885 |
-- |
| 19777 | 19886 |
|
| 19778 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin(); |
|
| 19887 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();
|
|
| 19779 | 19888 |
|
| 19780 | 19889 |
|
| 19781 | 19890 |
-- |
| ... | ... | |
| 19857 | 19966 |
|
| 19858 | 19967 |
|
| 19859 | 19968 |
-- |
| 19969 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
|
| 19970 |
-- |
|
| 19971 |
|
|
| 19972 |
REVOKE ALL ON TABLE taxon_match_input FROM PUBLIC; |
|
| 19973 |
REVOKE ALL ON TABLE taxon_match_input FROM bien; |
|
| 19974 |
GRANT ALL ON TABLE taxon_match_input TO bien; |
|
| 19975 |
GRANT SELECT ON TABLE taxon_match_input TO bien_read; |
|
| 19976 |
|
|
| 19977 |
|
|
| 19978 |
-- |
|
| 19860 | 19979 |
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: - |
| 19861 | 19980 |
-- |
| 19862 | 19981 |
|
| trunk/bin/tnrs_db | ||
|---|---|---|
| 32 | 32 |
|
| 33 | 33 |
tnrs_input = sql_gen.Table('tnrs_input_name')
|
| 34 | 34 |
tnrs_batch = sql_gen.Table('batch')
|
| 35 |
tnrs_data = sql_gen.Table('taxon_match')
|
|
| 35 |
tnrs_data = sql_gen.Table('taxon_match_input__copy_to')
|
|
| 36 | 36 |
|
| 37 | 37 |
def main(): |
| 38 | 38 |
# Input |
Also available in: Unified diff
inputs/.TNRS/schema.sql: taxon_match: insert names via taxon_match_input auto-updatable view instead of directly into taxon_match, to allow the taxon_match columns to be renamed while still supporting inserts using the TNRS column names