Revision 14367
Added by Aaron Marcuse-Kubitza over 10 years ago
| schema.sql | ||
|---|---|---|
| 257 | 257 |
ELSE NULL::text |
| 258 | 258 |
END) FROM (SELECT new.*) new); |
| 259 | 259 |
new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
| 260 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
|
| 260 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text) FROM (SELECT new.*) new);
|
|
| 261 | 261 |
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet}"[1],
|
| 262 | 262 |
CASE |
| 263 | 263 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
|
| ... | ... | |
| 267 | 267 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| 268 | 268 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 269 | 269 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 270 |
ELSE "*Accepted_name_species"
|
|
| 270 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 271 | 271 |
END FROM (SELECT new.*) new); |
| 272 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 272 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 273 | 273 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 274 | 274 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 275 | 275 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| ... | ... | |
| 292 | 292 |
ELSE "*Specific_epithet_matched" |
| 293 | 293 |
END FROM (SELECT new.*) new); |
| 294 | 294 |
new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE |
| 295 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 295 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 296 | 296 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 297 | 297 |
END FROM (SELECT new.*) new); |
| 298 | 298 |
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| ... | ... | |
| 496 | 496 |
CASE |
| 497 | 497 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 498 | 498 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 499 |
ELSE "*Accepted_name_species"
|
|
| 499 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 500 | 500 |
END))), |
| 501 | 501 |
CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),
|
| 502 | 502 |
CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
|
| ... | ... | |
| 546 | 546 |
END))), |
| 547 | 547 |
CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
| 548 | 548 |
CASE |
| 549 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 549 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 550 | 550 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 551 | 551 |
END))), |
| 552 | 552 |
CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
|
| ... | ... | |
| 560 | 560 |
ELSE "*Name_matched_rank" |
| 561 | 561 |
END))), |
| 562 | 562 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 563 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 563 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 564 | 564 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
| 565 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
|
| 565 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text)))),
|
|
| 566 | 566 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| 567 | 567 |
CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))
|
| 568 | 568 |
); |
| ... | ... | |
| 759 | 759 |
-- |
| 760 | 760 |
|
| 761 | 761 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
| 762 |
= regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
|
| 762 |
= regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)
|
|
| 763 | 763 |
|
| 764 | 764 |
derived column |
| 765 | 765 |
|
| 766 | 766 |
to modify expr: |
| 767 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
|
| 767 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)$$)::util.derived_col_def);
|
|
| 768 | 768 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 769 | 769 |
|
| 770 | 770 |
to rename: |
| ... | ... | |
| 830 | 830 |
= CASE |
| 831 | 831 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 832 | 832 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 833 |
ELSE "*Accepted_name_species"
|
|
| 833 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 834 | 834 |
END |
| 835 | 835 |
|
| 836 | 836 |
derived column |
| ... | ... | |
| 839 | 839 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 840 | 840 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 841 | 841 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 842 |
ELSE "*Accepted_name_species"
|
|
| 842 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 843 | 843 |
END$$)::util.derived_col_def); |
| 844 | 844 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 845 | 845 |
|
| ... | ... | |
| 855 | 855 |
-- |
| 856 | 856 |
|
| 857 | 857 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 858 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 858 |
= ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 859 | 859 |
|
| 860 | 860 |
derived column |
| 861 | 861 |
|
| 862 | 862 |
to modify expr: |
| 863 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 863 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 864 | 864 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 865 | 865 |
|
| 866 | 866 |
to rename: |
| ... | ... | |
| 1080 | 1080 |
|
| 1081 | 1081 |
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
| 1082 | 1082 |
= CASE |
| 1083 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 1083 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 1084 | 1084 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 1085 | 1085 |
END |
| 1086 | 1086 |
|
| ... | ... | |
| 1088 | 1088 |
|
| 1089 | 1089 |
to modify expr: |
| 1090 | 1090 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
| 1091 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 1091 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 1092 | 1092 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 1093 | 1093 |
END$$)::util.derived_col_def); |
| 1094 | 1094 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
Also available in: Unified diff
bugfix: inputs/.TNRS/schema.sql: taxon_match: use "Accepted_species[_binomial]__@TNRS__@vegpath.org" instead of "*Accepted_name_species". this fixes a bug in __accepted_infraspecific_label where Accepted_name_species with trailing whitespace could not be prefix-removed from names that contained just a species binomial.