Revision 14436
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 401 | 401 |
SET default_with_oids = false; |
| 402 | 402 |
|
| 403 | 403 |
-- |
| 404 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 405 |
-- |
|
| 406 |
|
|
| 407 |
CREATE TABLE batch ( |
|
| 408 |
id text NOT NULL, |
|
| 409 |
id_by_time text, |
|
| 410 |
time_submitted timestamp with time zone DEFAULT now(), |
|
| 411 |
client_version text |
|
| 412 |
); |
|
| 413 |
|
|
| 414 |
|
|
| 415 |
-- |
|
| 416 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 417 |
-- |
|
| 418 |
|
|
| 419 |
CREATE TABLE batch_download_settings ( |
|
| 420 |
id text NOT NULL, |
|
| 421 |
"E-mail" text, |
|
| 422 |
"Id" text, |
|
| 423 |
"Job type" text, |
|
| 424 |
"Contains Id" boolean, |
|
| 425 |
"Start time" text, |
|
| 426 |
"Finish time" text, |
|
| 427 |
"TNRS version" text, |
|
| 428 |
"Sources selected" text, |
|
| 429 |
"Match threshold" double precision, |
|
| 430 |
"Classification" text, |
|
| 431 |
"Allow partial matches?" boolean, |
|
| 432 |
"Sort by source" boolean, |
|
| 433 |
"Constrain by higher taxonomy" boolean |
|
| 434 |
); |
|
| 435 |
|
|
| 436 |
|
|
| 437 |
-- |
|
| 438 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 439 |
-- |
|
| 440 |
|
|
| 441 |
COMMENT ON TABLE batch_download_settings IS ' |
|
| 442 |
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt |
|
| 443 |
'; |
|
| 444 |
|
|
| 445 |
|
|
| 446 |
-- |
|
| 447 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 448 |
-- |
|
| 449 |
|
|
| 450 |
CREATE TABLE client_version ( |
|
| 451 |
id text NOT NULL, |
|
| 452 |
global_rev integer NOT NULL, |
|
| 453 |
"/lib/tnrs.py rev" integer, |
|
| 454 |
"/bin/tnrs_db rev" integer |
|
| 455 |
); |
|
| 456 |
|
|
| 457 |
|
|
| 458 |
-- |
|
| 459 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 460 |
-- |
|
| 461 |
|
|
| 462 |
COMMENT ON TABLE client_version IS ' |
|
| 463 |
contains svn revisions |
|
| 464 |
'; |
|
| 465 |
|
|
| 466 |
|
|
| 467 |
-- |
|
| 468 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 469 |
-- |
|
| 470 |
|
|
| 471 |
COMMENT ON COLUMN client_version.global_rev IS ' |
|
| 472 |
from `svn info .` > Last Changed Rev |
|
| 473 |
'; |
|
| 474 |
|
|
| 475 |
|
|
| 476 |
-- |
|
| 477 |
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 478 |
-- |
|
| 479 |
|
|
| 480 |
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS ' |
|
| 481 |
from `svn info lib/tnrs.py` > Last Changed Rev |
|
| 482 |
'; |
|
| 483 |
|
|
| 484 |
|
|
| 485 |
-- |
|
| 486 |
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 487 |
-- |
|
| 488 |
|
|
| 489 |
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS ' |
|
| 490 |
from `svn info bin/tnrs_db` > Last Changed Rev |
|
| 491 |
'; |
|
| 492 |
|
|
| 493 |
|
|
| 494 |
-- |
|
| 404 | 495 |
-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
| 405 | 496 |
-- |
| 406 | 497 |
|
| ... | ... | |
| 1419 | 1510 |
|
| 1420 | 1511 |
|
| 1421 | 1512 |
-- |
| 1422 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
|
| 1423 |
-- |
|
| 1424 |
|
|
| 1425 |
CREATE VIEW "ValidMatchedTaxon" AS |
|
| 1426 |
SELECT taxon_best_match.batch, |
|
| 1427 |
taxon_best_match.match_num, |
|
| 1428 |
taxon_best_match."*Name_number", |
|
| 1429 |
taxon_best_match."*Name_submitted", |
|
| 1430 |
taxon_best_match."*Overall_score", |
|
| 1431 |
taxon_best_match."*Name_matched", |
|
| 1432 |
taxon_best_match."*Name_matched_rank", |
|
| 1433 |
taxon_best_match."*Name_score", |
|
| 1434 |
taxon_best_match."*Name_matched_author", |
|
| 1435 |
taxon_best_match."*Name_matched_url", |
|
| 1436 |
taxon_best_match."*Author_matched", |
|
| 1437 |
taxon_best_match."*Author_score", |
|
| 1438 |
taxon_best_match."*Family_matched", |
|
| 1439 |
taxon_best_match."*Family_score", |
|
| 1440 |
taxon_best_match."*Name_matched_accepted_family", |
|
| 1441 |
taxon_best_match."*Genus_matched", |
|
| 1442 |
taxon_best_match."*Genus_score", |
|
| 1443 |
taxon_best_match."*Specific_epithet_matched", |
|
| 1444 |
taxon_best_match."*Specific_epithet_score", |
|
| 1445 |
taxon_best_match."*Infraspecific_rank", |
|
| 1446 |
taxon_best_match."*Infraspecific_epithet_matched", |
|
| 1447 |
taxon_best_match."*Infraspecific_epithet_score", |
|
| 1448 |
taxon_best_match."*Infraspecific_rank_2", |
|
| 1449 |
taxon_best_match."*Infraspecific_epithet_2_matched", |
|
| 1450 |
taxon_best_match."*Infraspecific_epithet_2_score", |
|
| 1451 |
taxon_best_match."*Annotations", |
|
| 1452 |
taxon_best_match."*Unmatched_terms", |
|
| 1453 |
taxon_best_match."*Taxonomic_status", |
|
| 1454 |
taxon_best_match."*Accepted_name", |
|
| 1455 |
taxon_best_match."*Accepted_name_author", |
|
| 1456 |
taxon_best_match."*Accepted_name_rank", |
|
| 1457 |
taxon_best_match."*Accepted_name_url", |
|
| 1458 |
taxon_best_match."*Accepted_name_species", |
|
| 1459 |
taxon_best_match."*Accepted_name_family", |
|
| 1460 |
taxon_best_match."*Selected", |
|
| 1461 |
taxon_best_match."*Source", |
|
| 1462 |
taxon_best_match."*Warnings", |
|
| 1463 |
taxon_best_match."*Accepted_name_lsid", |
|
| 1464 |
taxon_best_match.is_valid_match, |
|
| 1465 |
taxon_best_match.scrubbed_unique_taxon_name, |
|
| 1466 |
taxon_best_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", |
|
| 1467 |
taxon_best_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 1468 |
taxon_best_match."matched~Name[_no_author]___@TNRS__@vegpath.org", |
|
| 1469 |
taxon_best_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", |
|
| 1470 |
taxon_best_match.matched_has_accepted, |
|
| 1471 |
taxon_best_match."Accepted_family__@TNRS__@vegpath.org", |
|
| 1472 |
taxon_best_match."Accepted_species[_binomial]__@TNRS__@vegpath.org", |
|
| 1473 |
taxon_best_match."__accepted_{genus,specific_epithet,infra_{rank,epithet}}",
|
|
| 1474 |
taxon_best_match."[accepted_]genus__@DwC__@vegpath.org", |
|
| 1475 |
taxon_best_match."[accepted_]specificEpithet__@DwC__@vegpath.org", |
|
| 1476 |
taxon_best_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", |
|
| 1477 |
taxon_best_match."__accepted_infraspecific_{rank,epithet}",
|
|
| 1478 |
taxon_best_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", |
|
| 1479 |
taxon_best_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", |
|
| 1480 |
taxon_best_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", |
|
| 1481 |
taxon_best_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", |
|
| 1482 |
taxon_best_match."[scrubbed_]taxonRank__@DwC__@vegpath.org", |
|
| 1483 |
taxon_best_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 1484 |
taxon_best_match."[scrubbed_]genus__@DwC__@vegpath.org", |
|
| 1485 |
taxon_best_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org", |
|
| 1486 |
taxon_best_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 1487 |
taxon_best_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", |
|
| 1488 |
taxon_best_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", |
|
| 1489 |
taxon_best_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", |
|
| 1490 |
taxon_best_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 1491 |
taxon_best_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 1492 |
taxon_best_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" |
|
| 1493 |
FROM taxon_best_match |
|
| 1494 |
WHERE taxon_best_match.is_valid_match; |
|
| 1495 |
|
|
| 1496 |
|
|
| 1497 |
-- |
|
| 1498 |
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1499 |
-- |
|
| 1500 |
|
|
| 1501 |
COMMENT ON VIEW "ValidMatchedTaxon" IS ' |
|
| 1502 |
to update, use * as the column list |
|
| 1503 |
'; |
|
| 1504 |
|
|
| 1505 |
|
|
| 1506 |
-- |
|
| 1507 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 1508 |
-- |
|
| 1509 |
|
|
| 1510 |
CREATE TABLE batch ( |
|
| 1511 |
id text NOT NULL, |
|
| 1512 |
id_by_time text, |
|
| 1513 |
time_submitted timestamp with time zone DEFAULT now(), |
|
| 1514 |
client_version text |
|
| 1515 |
); |
|
| 1516 |
|
|
| 1517 |
|
|
| 1518 |
-- |
|
| 1519 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 1520 |
-- |
|
| 1521 |
|
|
| 1522 |
CREATE TABLE batch_download_settings ( |
|
| 1523 |
id text NOT NULL, |
|
| 1524 |
"E-mail" text, |
|
| 1525 |
"Id" text, |
|
| 1526 |
"Job type" text, |
|
| 1527 |
"Contains Id" boolean, |
|
| 1528 |
"Start time" text, |
|
| 1529 |
"Finish time" text, |
|
| 1530 |
"TNRS version" text, |
|
| 1531 |
"Sources selected" text, |
|
| 1532 |
"Match threshold" double precision, |
|
| 1533 |
"Classification" text, |
|
| 1534 |
"Allow partial matches?" boolean, |
|
| 1535 |
"Sort by source" boolean, |
|
| 1536 |
"Constrain by higher taxonomy" boolean |
|
| 1537 |
); |
|
| 1538 |
|
|
| 1539 |
|
|
| 1540 |
-- |
|
| 1541 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1542 |
-- |
|
| 1543 |
|
|
| 1544 |
COMMENT ON TABLE batch_download_settings IS ' |
|
| 1545 |
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt |
|
| 1546 |
'; |
|
| 1547 |
|
|
| 1548 |
|
|
| 1549 |
-- |
|
| 1550 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 1551 |
-- |
|
| 1552 |
|
|
| 1553 |
CREATE TABLE client_version ( |
|
| 1554 |
id text NOT NULL, |
|
| 1555 |
global_rev integer NOT NULL, |
|
| 1556 |
"/lib/tnrs.py rev" integer, |
|
| 1557 |
"/bin/tnrs_db rev" integer |
|
| 1558 |
); |
|
| 1559 |
|
|
| 1560 |
|
|
| 1561 |
-- |
|
| 1562 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1563 |
-- |
|
| 1564 |
|
|
| 1565 |
COMMENT ON TABLE client_version IS ' |
|
| 1566 |
contains svn revisions |
|
| 1567 |
'; |
|
| 1568 |
|
|
| 1569 |
|
|
| 1570 |
-- |
|
| 1571 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1572 |
-- |
|
| 1573 |
|
|
| 1574 |
COMMENT ON COLUMN client_version.global_rev IS ' |
|
| 1575 |
from `svn info .` > Last Changed Rev |
|
| 1576 |
'; |
|
| 1577 |
|
|
| 1578 |
|
|
| 1579 |
-- |
|
| 1580 |
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1581 |
-- |
|
| 1582 |
|
|
| 1583 |
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS ' |
|
| 1584 |
from `svn info lib/tnrs.py` > Last Changed Rev |
|
| 1585 |
'; |
|
| 1586 |
|
|
| 1587 |
|
|
| 1588 |
-- |
|
| 1589 |
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1590 |
-- |
|
| 1591 |
|
|
| 1592 |
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS ' |
|
| 1593 |
from `svn info bin/tnrs_db` > Last Changed Rev |
|
| 1594 |
'; |
|
| 1595 |
|
|
| 1596 |
|
|
| 1597 |
-- |
|
| 1598 | 1513 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 1599 | 1514 |
-- |
| 1600 | 1515 |
|
| ... | ... | |
| 1949 | 1864 |
|
| 1950 | 1865 |
|
| 1951 | 1866 |
-- |
| 1952 |
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
|
| 1953 |
-- |
|
| 1954 |
|
|
| 1955 |
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC; |
|
| 1956 |
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien; |
|
| 1957 |
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien; |
|
| 1958 |
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read; |
|
| 1959 |
|
|
| 1960 |
|
|
| 1961 |
-- |
|
| 1962 | 1867 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
| 1963 | 1868 |
-- |
| 1964 | 1869 |
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15963 | 15963 |
|
| 15964 | 15964 |
|
| 15965 | 15965 |
-- |
| 15966 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 15967 |
-- |
|
| 15968 |
|
|
| 15969 |
CREATE TABLE batch ( |
|
| 15970 |
id varchar(255) NOT NULL, |
|
| 15971 |
id_by_time varchar(255), |
|
| 15972 |
time_submitted timestamp NULL , |
|
| 15973 |
client_version varchar(255) |
|
| 15974 |
); |
|
| 15975 |
|
|
| 15976 |
|
|
| 15977 |
-- |
|
| 15978 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 15979 |
-- |
|
| 15980 |
|
|
| 15981 |
CREATE TABLE batch_download_settings ( |
|
| 15982 |
id varchar(255) NOT NULL, |
|
| 15983 |
`E-mail` varchar(255), |
|
| 15984 |
`Id` varchar(255), |
|
| 15985 |
`Job type` varchar(255), |
|
| 15986 |
`Contains Id` int(1), |
|
| 15987 |
`Start time` varchar(255), |
|
| 15988 |
`Finish time` varchar(255), |
|
| 15989 |
`TNRS version` varchar(255), |
|
| 15990 |
`Sources selected` varchar(255), |
|
| 15991 |
`Match threshold` double, |
|
| 15992 |
`Classification` varchar(255), |
|
| 15993 |
`Allow partial matches?` int(1), |
|
| 15994 |
`Sort by source` int(1), |
|
| 15995 |
`Constrain by higher taxonomy` int(1) |
|
| 15996 |
); |
|
| 15997 |
|
|
| 15998 |
|
|
| 15999 |
-- |
|
| 16000 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16001 |
-- |
|
| 16002 |
|
|
| 16003 |
|
|
| 16004 |
|
|
| 16005 |
|
|
| 16006 |
-- |
|
| 16007 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 16008 |
-- |
|
| 16009 |
|
|
| 16010 |
CREATE TABLE client_version ( |
|
| 16011 |
id varchar(255) NOT NULL, |
|
| 16012 |
global_rev int(11) NOT NULL, |
|
| 16013 |
`/lib/tnrs.py rev` int(11), |
|
| 16014 |
`/bin/tnrs_db rev` int(11) |
|
| 16015 |
); |
|
| 16016 |
|
|
| 16017 |
|
|
| 16018 |
-- |
|
| 16019 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16020 |
-- |
|
| 16021 |
|
|
| 16022 |
|
|
| 16023 |
|
|
| 16024 |
|
|
| 16025 |
-- |
|
| 16026 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16027 |
-- |
|
| 16028 |
|
|
| 16029 |
|
|
| 16030 |
|
|
| 16031 |
|
|
| 16032 |
-- |
|
| 16033 |
-- Name: COLUMN client_version.`/lib/tnrs.py rev`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16034 |
-- |
|
| 16035 |
|
|
| 16036 |
|
|
| 16037 |
|
|
| 16038 |
|
|
| 16039 |
-- |
|
| 16040 |
-- Name: COLUMN client_version.`/bin/tnrs_db rev`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16041 |
-- |
|
| 16042 |
|
|
| 16043 |
|
|
| 16044 |
|
|
| 16045 |
|
|
| 16046 |
-- |
|
| 15966 | 16047 |
-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
| 15967 | 16048 |
-- |
| 15968 | 16049 |
|
| ... | ... | |
| 16302 | 16383 |
|
| 16303 | 16384 |
|
| 16304 | 16385 |
-- |
| 16305 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
|
| 16306 |
-- |
|
| 16307 |
|
|
| 16308 |
|
|
| 16309 |
|
|
| 16310 |
|
|
| 16311 |
-- |
|
| 16312 |
-- Name: VIEW `ValidMatchedTaxon`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16313 |
-- |
|
| 16314 |
|
|
| 16315 |
|
|
| 16316 |
|
|
| 16317 |
|
|
| 16318 |
-- |
|
| 16319 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 16320 |
-- |
|
| 16321 |
|
|
| 16322 |
CREATE TABLE batch ( |
|
| 16323 |
id varchar(255) NOT NULL, |
|
| 16324 |
id_by_time varchar(255), |
|
| 16325 |
time_submitted timestamp NULL , |
|
| 16326 |
client_version varchar(255) |
|
| 16327 |
); |
|
| 16328 |
|
|
| 16329 |
|
|
| 16330 |
-- |
|
| 16331 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 16332 |
-- |
|
| 16333 |
|
|
| 16334 |
CREATE TABLE batch_download_settings ( |
|
| 16335 |
id varchar(255) NOT NULL, |
|
| 16336 |
`E-mail` varchar(255), |
|
| 16337 |
`Id` varchar(255), |
|
| 16338 |
`Job type` varchar(255), |
|
| 16339 |
`Contains Id` int(1), |
|
| 16340 |
`Start time` varchar(255), |
|
| 16341 |
`Finish time` varchar(255), |
|
| 16342 |
`TNRS version` varchar(255), |
|
| 16343 |
`Sources selected` varchar(255), |
|
| 16344 |
`Match threshold` double, |
|
| 16345 |
`Classification` varchar(255), |
|
| 16346 |
`Allow partial matches?` int(1), |
|
| 16347 |
`Sort by source` int(1), |
|
| 16348 |
`Constrain by higher taxonomy` int(1) |
|
| 16349 |
); |
|
| 16350 |
|
|
| 16351 |
|
|
| 16352 |
-- |
|
| 16353 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16354 |
-- |
|
| 16355 |
|
|
| 16356 |
|
|
| 16357 |
|
|
| 16358 |
|
|
| 16359 |
-- |
|
| 16360 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 16361 |
-- |
|
| 16362 |
|
|
| 16363 |
CREATE TABLE client_version ( |
|
| 16364 |
id varchar(255) NOT NULL, |
|
| 16365 |
global_rev int(11) NOT NULL, |
|
| 16366 |
`/lib/tnrs.py rev` int(11), |
|
| 16367 |
`/bin/tnrs_db rev` int(11) |
|
| 16368 |
); |
|
| 16369 |
|
|
| 16370 |
|
|
| 16371 |
-- |
|
| 16372 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16373 |
-- |
|
| 16374 |
|
|
| 16375 |
|
|
| 16376 |
|
|
| 16377 |
|
|
| 16378 |
-- |
|
| 16379 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16380 |
-- |
|
| 16381 |
|
|
| 16382 |
|
|
| 16383 |
|
|
| 16384 |
|
|
| 16385 |
-- |
|
| 16386 |
-- Name: COLUMN client_version.`/lib/tnrs.py rev`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16387 |
-- |
|
| 16388 |
|
|
| 16389 |
|
|
| 16390 |
|
|
| 16391 |
|
|
| 16392 |
-- |
|
| 16393 |
-- Name: COLUMN client_version.`/bin/tnrs_db rev`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 16394 |
-- |
|
| 16395 |
|
|
| 16396 |
|
|
| 16397 |
|
|
| 16398 |
|
|
| 16399 |
-- |
|
| 16400 | 16386 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 16401 | 16387 |
-- |
| 16402 | 16388 |
|
| ... | ... | |
| 16703 | 16689 |
|
| 16704 | 16690 |
|
| 16705 | 16691 |
-- |
| 16706 |
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
|
| 16707 |
-- |
|
| 16708 |
|
|
| 16709 |
|
|
| 16710 |
|
|
| 16711 |
|
|
| 16712 |
|
|
| 16713 |
|
|
| 16714 |
|
|
| 16715 |
-- |
|
| 16716 | 16692 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
| 16717 | 16693 |
-- |
| 16718 | 16694 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 20323 | 20323 |
SET default_with_oids = false; |
| 20324 | 20324 |
|
| 20325 | 20325 |
-- |
| 20326 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 20327 |
-- |
|
| 20328 |
|
|
| 20329 |
CREATE TABLE batch ( |
|
| 20330 |
id text NOT NULL, |
|
| 20331 |
id_by_time text, |
|
| 20332 |
time_submitted timestamp with time zone DEFAULT now(), |
|
| 20333 |
client_version text |
|
| 20334 |
); |
|
| 20335 |
|
|
| 20336 |
|
|
| 20337 |
-- |
|
| 20338 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 20339 |
-- |
|
| 20340 |
|
|
| 20341 |
CREATE TABLE batch_download_settings ( |
|
| 20342 |
id text NOT NULL, |
|
| 20343 |
"E-mail" text, |
|
| 20344 |
"Id" text, |
|
| 20345 |
"Job type" text, |
|
| 20346 |
"Contains Id" boolean, |
|
| 20347 |
"Start time" text, |
|
| 20348 |
"Finish time" text, |
|
| 20349 |
"TNRS version" text, |
|
| 20350 |
"Sources selected" text, |
|
| 20351 |
"Match threshold" double precision, |
|
| 20352 |
"Classification" text, |
|
| 20353 |
"Allow partial matches?" boolean, |
|
| 20354 |
"Sort by source" boolean, |
|
| 20355 |
"Constrain by higher taxonomy" boolean |
|
| 20356 |
); |
|
| 20357 |
|
|
| 20358 |
|
|
| 20359 |
-- |
|
| 20360 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20361 |
-- |
|
| 20362 |
|
|
| 20363 |
COMMENT ON TABLE batch_download_settings IS ' |
|
| 20364 |
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt |
|
| 20365 |
'; |
|
| 20366 |
|
|
| 20367 |
|
|
| 20368 |
-- |
|
| 20369 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 20370 |
-- |
|
| 20371 |
|
|
| 20372 |
CREATE TABLE client_version ( |
|
| 20373 |
id text NOT NULL, |
|
| 20374 |
global_rev integer NOT NULL, |
|
| 20375 |
"/lib/tnrs.py rev" integer, |
|
| 20376 |
"/bin/tnrs_db rev" integer |
|
| 20377 |
); |
|
| 20378 |
|
|
| 20379 |
|
|
| 20380 |
-- |
|
| 20381 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20382 |
-- |
|
| 20383 |
|
|
| 20384 |
COMMENT ON TABLE client_version IS ' |
|
| 20385 |
contains svn revisions |
|
| 20386 |
'; |
|
| 20387 |
|
|
| 20388 |
|
|
| 20389 |
-- |
|
| 20390 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20391 |
-- |
|
| 20392 |
|
|
| 20393 |
COMMENT ON COLUMN client_version.global_rev IS ' |
|
| 20394 |
from `svn info .` > Last Changed Rev |
|
| 20395 |
'; |
|
| 20396 |
|
|
| 20397 |
|
|
| 20398 |
-- |
|
| 20399 |
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20400 |
-- |
|
| 20401 |
|
|
| 20402 |
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS ' |
|
| 20403 |
from `svn info lib/tnrs.py` > Last Changed Rev |
|
| 20404 |
'; |
|
| 20405 |
|
|
| 20406 |
|
|
| 20407 |
-- |
|
| 20408 |
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20409 |
-- |
|
| 20410 |
|
|
| 20411 |
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS ' |
|
| 20412 |
from `svn info bin/tnrs_db` > Last Changed Rev |
|
| 20413 |
'; |
|
| 20414 |
|
|
| 20415 |
|
|
| 20416 |
-- |
|
| 20326 | 20417 |
-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
| 20327 | 20418 |
-- |
| 20328 | 20419 |
|
| ... | ... | |
| 21341 | 21432 |
|
| 21342 | 21433 |
|
| 21343 | 21434 |
-- |
| 21344 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
|
| 21345 |
-- |
|
| 21346 |
|
|
| 21347 |
CREATE VIEW "ValidMatchedTaxon" AS |
|
| 21348 |
SELECT taxon_best_match.batch, |
|
| 21349 |
taxon_best_match.match_num, |
|
| 21350 |
taxon_best_match."*Name_number", |
|
| 21351 |
taxon_best_match."*Name_submitted", |
|
| 21352 |
taxon_best_match."*Overall_score", |
|
| 21353 |
taxon_best_match."*Name_matched", |
|
| 21354 |
taxon_best_match."*Name_matched_rank", |
|
| 21355 |
taxon_best_match."*Name_score", |
|
| 21356 |
taxon_best_match."*Name_matched_author", |
|
| 21357 |
taxon_best_match."*Name_matched_url", |
|
| 21358 |
taxon_best_match."*Author_matched", |
|
| 21359 |
taxon_best_match."*Author_score", |
|
| 21360 |
taxon_best_match."*Family_matched", |
|
| 21361 |
taxon_best_match."*Family_score", |
|
| 21362 |
taxon_best_match."*Name_matched_accepted_family", |
|
| 21363 |
taxon_best_match."*Genus_matched", |
|
| 21364 |
taxon_best_match."*Genus_score", |
|
| 21365 |
taxon_best_match."*Specific_epithet_matched", |
|
| 21366 |
taxon_best_match."*Specific_epithet_score", |
|
| 21367 |
taxon_best_match."*Infraspecific_rank", |
|
| 21368 |
taxon_best_match."*Infraspecific_epithet_matched", |
|
| 21369 |
taxon_best_match."*Infraspecific_epithet_score", |
|
| 21370 |
taxon_best_match."*Infraspecific_rank_2", |
|
| 21371 |
taxon_best_match."*Infraspecific_epithet_2_matched", |
|
| 21372 |
taxon_best_match."*Infraspecific_epithet_2_score", |
|
| 21373 |
taxon_best_match."*Annotations", |
|
| 21374 |
taxon_best_match."*Unmatched_terms", |
|
| 21375 |
taxon_best_match."*Taxonomic_status", |
|
| 21376 |
taxon_best_match."*Accepted_name", |
|
| 21377 |
taxon_best_match."*Accepted_name_author", |
|
| 21378 |
taxon_best_match."*Accepted_name_rank", |
|
| 21379 |
taxon_best_match."*Accepted_name_url", |
|
| 21380 |
taxon_best_match."*Accepted_name_species", |
|
| 21381 |
taxon_best_match."*Accepted_name_family", |
|
| 21382 |
taxon_best_match."*Selected", |
|
| 21383 |
taxon_best_match."*Source", |
|
| 21384 |
taxon_best_match."*Warnings", |
|
| 21385 |
taxon_best_match."*Accepted_name_lsid", |
|
| 21386 |
taxon_best_match.is_valid_match, |
|
| 21387 |
taxon_best_match.scrubbed_unique_taxon_name, |
|
| 21388 |
taxon_best_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", |
|
| 21389 |
taxon_best_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 21390 |
taxon_best_match."matched~Name[_no_author]___@TNRS__@vegpath.org", |
|
| 21391 |
taxon_best_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", |
|
| 21392 |
taxon_best_match.matched_has_accepted, |
|
| 21393 |
taxon_best_match."Accepted_family__@TNRS__@vegpath.org", |
|
| 21394 |
taxon_best_match."Accepted_species[_binomial]__@TNRS__@vegpath.org", |
|
| 21395 |
taxon_best_match."__accepted_{genus,specific_epithet,infra_{rank,epithet}}",
|
|
| 21396 |
taxon_best_match."[accepted_]genus__@DwC__@vegpath.org", |
|
| 21397 |
taxon_best_match."[accepted_]specificEpithet__@DwC__@vegpath.org", |
|
| 21398 |
taxon_best_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", |
|
| 21399 |
taxon_best_match."__accepted_infraspecific_{rank,epithet}",
|
|
| 21400 |
taxon_best_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", |
|
| 21401 |
taxon_best_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", |
|
| 21402 |
taxon_best_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", |
|
| 21403 |
taxon_best_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", |
|
| 21404 |
taxon_best_match."[scrubbed_]taxonRank__@DwC__@vegpath.org", |
|
| 21405 |
taxon_best_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 21406 |
taxon_best_match."[scrubbed_]genus__@DwC__@vegpath.org", |
|
| 21407 |
taxon_best_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org", |
|
| 21408 |
taxon_best_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 21409 |
taxon_best_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", |
|
| 21410 |
taxon_best_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", |
|
| 21411 |
taxon_best_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", |
|
| 21412 |
taxon_best_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 21413 |
taxon_best_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", |
|
| 21414 |
taxon_best_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" |
|
| 21415 |
FROM taxon_best_match |
|
| 21416 |
WHERE taxon_best_match.is_valid_match; |
|
| 21417 |
|
|
| 21418 |
|
|
| 21419 |
-- |
|
| 21420 |
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21421 |
-- |
|
| 21422 |
|
|
| 21423 |
COMMENT ON VIEW "ValidMatchedTaxon" IS ' |
|
| 21424 |
to update, use * as the column list |
|
| 21425 |
'; |
|
| 21426 |
|
|
| 21427 |
|
|
| 21428 |
-- |
|
| 21429 |
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 21430 |
-- |
|
| 21431 |
|
|
| 21432 |
CREATE TABLE batch ( |
|
| 21433 |
id text NOT NULL, |
|
| 21434 |
id_by_time text, |
|
| 21435 |
time_submitted timestamp with time zone DEFAULT now(), |
|
| 21436 |
client_version text |
|
| 21437 |
); |
|
| 21438 |
|
|
| 21439 |
|
|
| 21440 |
-- |
|
| 21441 |
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 21442 |
-- |
|
| 21443 |
|
|
| 21444 |
CREATE TABLE batch_download_settings ( |
|
| 21445 |
id text NOT NULL, |
|
| 21446 |
"E-mail" text, |
|
| 21447 |
"Id" text, |
|
| 21448 |
"Job type" text, |
|
| 21449 |
"Contains Id" boolean, |
|
| 21450 |
"Start time" text, |
|
| 21451 |
"Finish time" text, |
|
| 21452 |
"TNRS version" text, |
|
| 21453 |
"Sources selected" text, |
|
| 21454 |
"Match threshold" double precision, |
|
| 21455 |
"Classification" text, |
|
| 21456 |
"Allow partial matches?" boolean, |
|
| 21457 |
"Sort by source" boolean, |
|
| 21458 |
"Constrain by higher taxonomy" boolean |
|
| 21459 |
); |
|
| 21460 |
|
|
| 21461 |
|
|
| 21462 |
-- |
|
| 21463 |
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21464 |
-- |
|
| 21465 |
|
|
| 21466 |
COMMENT ON TABLE batch_download_settings IS ' |
|
| 21467 |
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt |
|
| 21468 |
'; |
|
| 21469 |
|
|
| 21470 |
|
|
| 21471 |
-- |
|
| 21472 |
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: |
|
| 21473 |
-- |
|
| 21474 |
|
|
| 21475 |
CREATE TABLE client_version ( |
|
| 21476 |
id text NOT NULL, |
|
| 21477 |
global_rev integer NOT NULL, |
|
| 21478 |
"/lib/tnrs.py rev" integer, |
|
| 21479 |
"/bin/tnrs_db rev" integer |
|
| 21480 |
); |
|
| 21481 |
|
|
| 21482 |
|
|
| 21483 |
-- |
|
| 21484 |
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21485 |
-- |
|
| 21486 |
|
|
| 21487 |
COMMENT ON TABLE client_version IS ' |
|
| 21488 |
contains svn revisions |
|
| 21489 |
'; |
|
| 21490 |
|
|
| 21491 |
|
|
| 21492 |
-- |
|
| 21493 |
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21494 |
-- |
|
| 21495 |
|
|
| 21496 |
COMMENT ON COLUMN client_version.global_rev IS ' |
|
| 21497 |
from `svn info .` > Last Changed Rev |
|
| 21498 |
'; |
|
| 21499 |
|
|
| 21500 |
|
|
| 21501 |
-- |
|
| 21502 |
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21503 |
-- |
|
| 21504 |
|
|
| 21505 |
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS ' |
|
| 21506 |
from `svn info lib/tnrs.py` > Last Changed Rev |
|
| 21507 |
'; |
|
| 21508 |
|
|
| 21509 |
|
|
| 21510 |
-- |
|
| 21511 |
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 21512 |
-- |
|
| 21513 |
|
|
| 21514 |
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS ' |
|
| 21515 |
from `svn info bin/tnrs_db` > Last Changed Rev |
|
| 21516 |
'; |
|
| 21517 |
|
|
| 21518 |
|
|
| 21519 |
-- |
|
| 21520 | 21435 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 21521 | 21436 |
-- |
| 21522 | 21437 |
|
| ... | ... | |
| 21948 | 21863 |
|
| 21949 | 21864 |
|
| 21950 | 21865 |
-- |
| 21951 |
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
|
| 21952 |
-- |
|
| 21953 |
|
|
| 21954 |
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC; |
|
| 21955 |
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien; |
|
| 21956 |
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien; |
|
| 21957 |
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read; |
|
| 21958 |
|
|
| 21959 |
|
|
| 21960 |
-- |
|
| 21961 | 21866 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
| 21962 | 21867 |
-- |
| 21963 | 21868 |
|
Also available in: Unified diff
inputs/.TNRS/schema.sql: removed no longer used view ValidMatchedTaxon. use taxon_scrub instead.