Revision 14097
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 5742 | 5742 |
|
| 5743 | 5743 |
|
| 5744 | 5744 |
-- |
| 5745 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 5746 |
-- |
|
| 5747 |
|
|
| 5748 |
CREATE TABLE threatened_taxonlabel ( |
|
| 5749 |
taxonlabel_id int(11) NOT NULL |
|
| 5750 |
); |
|
| 5751 |
|
|
| 5752 |
|
|
| 5753 |
-- |
|
| 5754 | 5745 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public; Owner: - |
| 5755 | 5746 |
-- |
| 5756 | 5747 |
|
| ... | ... | |
| 6164 | 6155 |
|
| 6165 | 6156 |
|
| 6166 | 6157 |
-- |
| 6167 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
|
| 6168 |
-- |
|
| 6169 |
|
|
| 6170 |
|
|
| 6171 |
|
|
| 6172 |
|
|
| 6173 |
-- |
|
| 6174 | 6158 |
-- Name: COLUMN analytical_stem_view.cultivated_bien; Type: COMMENT; Schema: public; Owner: - |
| 6175 | 6159 |
-- |
| 6176 | 6160 |
|
| ... | ... | |
| 7812 | 7796 |
|
| 7813 | 7797 |
|
| 7814 | 7798 |
-- |
| 7799 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 7800 |
-- |
|
| 7801 |
|
|
| 7802 |
CREATE TABLE threatened_taxonlabel ( |
|
| 7803 |
taxonlabel_id int(11) NOT NULL |
|
| 7804 |
); |
|
| 7805 |
|
|
| 7806 |
|
|
| 7807 |
-- |
|
| 7815 | 7808 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
| 7816 | 7809 |
-- |
| 7817 | 7810 |
|
| ... | ... | |
| 14713 | 14706 |
|
| 14714 | 14707 |
|
| 14715 | 14708 |
-- |
| 14716 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 14717 |
-- |
|
| 14718 |
|
|
| 14719 |
|
|
| 14720 |
|
|
| 14721 |
|
|
| 14722 |
|
|
| 14723 |
|
|
| 14724 |
|
|
| 14725 |
-- |
|
| 14726 | 14709 |
-- Name: analytical_stem_view; Type: ACL; Schema: public; Owner: - |
| 14727 | 14710 |
-- |
| 14728 | 14711 |
|
| ... | ... | |
| 15268 | 15251 |
|
| 15269 | 15252 |
|
| 15270 | 15253 |
-- |
| 15254 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 15255 |
-- |
|
| 15256 |
|
|
| 15257 |
|
|
| 15258 |
|
|
| 15259 |
|
|
| 15260 |
|
|
| 15261 |
|
|
| 15262 |
|
|
| 15263 |
-- |
|
| 15271 | 15264 |
-- Name: threatened_taxonlabel_view; Type: ACL; Schema: public; Owner: - |
| 15272 | 15265 |
-- |
| 15273 | 15266 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 8410 | 8410 |
|
| 8411 | 8411 |
|
| 8412 | 8412 |
-- |
| 8413 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 8414 |
-- |
|
| 8415 |
|
|
| 8416 |
CREATE TABLE threatened_taxonlabel ( |
|
| 8417 |
taxonlabel_id integer NOT NULL |
|
| 8418 |
); |
|
| 8419 |
|
|
| 8420 |
|
|
| 8421 |
-- |
|
| 8422 | 8413 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public; Owner: - |
| 8423 | 8414 |
-- |
| 8424 | 8415 |
|
| ... | ... | |
| 8480 | 8471 |
taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies__@VegCore__@vegpath.org", |
| 8481 | 8472 |
taxonoccurrence.growthform AS "[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org", |
| 8482 | 8473 |
plantobservation.reproductivecondition AS "reproductiveCondition__@DwC__@vegpath.org", |
| 8483 |
((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, |
|
| 8484 | 8474 |
(((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, |
| 8485 | 8475 |
CASE |
| 8486 | 8476 |
WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis |
| ... | ... | |
| 8501 | 8491 |
plantobservation.authorplantcode AS "individualID__@DwC__@vegpath.org", |
| 8502 | 8492 |
aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org", |
| 8503 | 8493 |
stemobservation.authorstemcode AS "stemCode__@VegBank__.stemLocation@vegpath.org" |
| 8504 |
FROM ((((((((((((((("plot.**"
|
|
| 8494 |
FROM (((((((((((((("plot.**"
|
|
| 8505 | 8495 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**"."locationevent.locationevent_id__@VegBIEN__.public@vegpath.org"))) |
| 8506 | 8496 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| 8507 | 8497 |
LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 8515 | 8505 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 8516 | 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname))) |
| 8517 | 8507 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 8518 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))) |
|
| 8519 |
LEFT JOIN threatened_taxonlabel USING (taxonlabel_id)); |
|
| 8508 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))); |
|
| 8520 | 8509 |
|
| 8521 | 8510 |
|
| 8522 | 8511 |
-- |
| ... | ... | |
| 8937 | 8926 |
|
| 8938 | 8927 |
|
| 8939 | 8928 |
-- |
| 8940 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
|
| 8941 |
-- |
|
| 8942 |
|
|
| 8943 |
COMMENT ON COLUMN analytical_stem_view.threatened_bien IS 'whether the taxon is on the IUCN Red List of Threatened Species'; |
|
| 8944 |
|
|
| 8945 |
|
|
| 8946 |
-- |
|
| 8947 | 8929 |
-- Name: COLUMN analytical_stem_view.cultivated_bien; Type: COMMENT; Schema: public; Owner: - |
| 8948 | 8930 |
-- |
| 8949 | 8931 |
|
| ... | ... | |
| 11099 | 11081 |
|
| 11100 | 11082 |
|
| 11101 | 11083 |
-- |
| 11084 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 11085 |
-- |
|
| 11086 |
|
|
| 11087 |
CREATE TABLE threatened_taxonlabel ( |
|
| 11088 |
taxonlabel_id integer NOT NULL |
|
| 11089 |
); |
|
| 11090 |
|
|
| 11091 |
|
|
| 11092 |
-- |
|
| 11102 | 11093 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
| 11103 | 11094 |
-- |
| 11104 | 11095 |
|
| ... | ... | |
| 18513 | 18504 |
|
| 18514 | 18505 |
|
| 18515 | 18506 |
-- |
| 18516 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 18517 |
-- |
|
| 18518 |
|
|
| 18519 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 18520 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 18521 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 18522 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 18523 |
|
|
| 18524 |
|
|
| 18525 |
-- |
|
| 18526 | 18507 |
-- Name: analytical_stem_view; Type: ACL; Schema: public; Owner: - |
| 18527 | 18508 |
-- |
| 18528 | 18509 |
|
| ... | ... | |
| 19068 | 19049 |
|
| 19069 | 19050 |
|
| 19070 | 19051 |
-- |
| 19052 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 19053 |
-- |
|
| 19054 |
|
|
| 19055 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 19056 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 19057 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 19058 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 19059 |
|
|
| 19060 |
|
|
| 19061 |
-- |
|
| 19071 | 19062 |
-- Name: threatened_taxonlabel_view; Type: ACL; Schema: public; Owner: - |
| 19072 | 19063 |
-- |
| 19073 | 19064 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 8410 | 8410 |
|
| 8411 | 8411 |
|
| 8412 | 8412 |
-- |
| 8413 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 8414 |
-- |
|
| 8415 |
|
|
| 8416 |
CREATE TABLE threatened_taxonlabel ( |
|
| 8417 |
taxonlabel_id integer NOT NULL |
|
| 8418 |
); |
|
| 8419 |
|
|
| 8420 |
|
|
| 8421 |
-- |
|
| 8422 | 8413 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public; Owner: - |
| 8423 | 8414 |
-- |
| 8424 | 8415 |
|
| ... | ... | |
| 8480 | 8471 |
taxon_scrub.scrubbed_morphospecies_binomial AS "speciesBinomialWithMorphospecies__@VegCore__@vegpath.org", |
| 8481 | 8472 |
taxonoccurrence.growthform AS "[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org", |
| 8482 | 8473 |
plantobservation.reproductivecondition AS "reproductiveCondition__@DwC__@vegpath.org", |
| 8483 |
((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, |
|
| 8484 | 8474 |
(((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, |
| 8485 | 8475 |
CASE |
| 8486 | 8476 |
WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis |
| ... | ... | |
| 8501 | 8491 |
plantobservation.authorplantcode AS "individualID__@DwC__@vegpath.org", |
| 8502 | 8492 |
aggregateoccurrence.count AS "individualCount__@DwC__@vegpath.org", |
| 8503 | 8493 |
stemobservation.authorstemcode AS "stemCode__@VegBank__.stemLocation@vegpath.org" |
| 8504 |
FROM ((((((((((((((("plot.**"
|
|
| 8494 |
FROM (((((((((((((("plot.**"
|
|
| 8505 | 8495 |
LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**"."locationevent.locationevent_id__@VegBIEN__.public@vegpath.org"))) |
| 8506 | 8496 |
LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) |
| 8507 | 8497 |
LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
| ... | ... | |
| 8515 | 8505 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 8516 | 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname))) |
| 8517 | 8507 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 8518 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))) |
|
| 8519 |
LEFT JOIN threatened_taxonlabel USING (taxonlabel_id)); |
|
| 8508 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))); |
|
| 8520 | 8509 |
|
| 8521 | 8510 |
|
| 8522 | 8511 |
-- |
| ... | ... | |
| 8937 | 8926 |
|
| 8938 | 8927 |
|
| 8939 | 8928 |
-- |
| 8940 |
-- Name: COLUMN analytical_stem_view.threatened_bien; Type: COMMENT; Schema: public; Owner: - |
|
| 8941 |
-- |
|
| 8942 |
|
|
| 8943 |
COMMENT ON COLUMN analytical_stem_view.threatened_bien IS 'whether the taxon is on the IUCN Red List of Threatened Species'; |
|
| 8944 |
|
|
| 8945 |
|
|
| 8946 |
-- |
|
| 8947 | 8929 |
-- Name: COLUMN analytical_stem_view.cultivated_bien; Type: COMMENT; Schema: public; Owner: - |
| 8948 | 8930 |
-- |
| 8949 | 8931 |
|
| ... | ... | |
| 11099 | 11081 |
|
| 11100 | 11082 |
|
| 11101 | 11083 |
-- |
| 11084 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 11085 |
-- |
|
| 11086 |
|
|
| 11087 |
CREATE TABLE threatened_taxonlabel ( |
|
| 11088 |
taxonlabel_id integer NOT NULL |
|
| 11089 |
); |
|
| 11090 |
|
|
| 11091 |
|
|
| 11092 |
-- |
|
| 11102 | 11093 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
| 11103 | 11094 |
-- |
| 11104 | 11095 |
|
| ... | ... | |
| 18513 | 18504 |
|
| 18514 | 18505 |
|
| 18515 | 18506 |
-- |
| 18516 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 18517 |
-- |
|
| 18518 |
|
|
| 18519 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 18520 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 18521 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 18522 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 18523 |
|
|
| 18524 |
|
|
| 18525 |
-- |
|
| 18526 | 18507 |
-- Name: analytical_stem_view; Type: ACL; Schema: public; Owner: - |
| 18527 | 18508 |
-- |
| 18528 | 18509 |
|
| ... | ... | |
| 19068 | 19049 |
|
| 19069 | 19050 |
|
| 19070 | 19051 |
-- |
| 19052 |
-- Name: threatened_taxonlabel; Type: ACL; Schema: public; Owner: - |
|
| 19053 |
-- |
|
| 19054 |
|
|
| 19055 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM PUBLIC; |
|
| 19056 |
REVOKE ALL ON TABLE threatened_taxonlabel FROM bien; |
|
| 19057 |
GRANT ALL ON TABLE threatened_taxonlabel TO bien; |
|
| 19058 |
GRANT SELECT ON TABLE threatened_taxonlabel TO bien_read; |
|
| 19059 |
|
|
| 19060 |
|
|
| 19061 |
-- |
|
| 19071 | 19062 |
-- Name: threatened_taxonlabel_view; Type: ACL; Schema: public; Owner: - |
| 19072 | 19063 |
-- |
| 19073 | 19064 |
|
Also available in: Unified diff
fix: schemas/public_.sql: analytical_stem_view: don't use threatened_taxonlabel, which was never populated correctly. instead, this can eventually be rewritten to use new iucn_red_list.