-- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: TNRS; Type: SCHEMA; Schema: -; Owner: bien -- CREATE SCHEMA "TNRS"; ALTER SCHEMA "TNRS" OWNER TO bien; -- -- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: bien -- COMMENT ON SCHEMA "TNRS" IS ' IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev: on vegbiendev: # back up existing TNRS schema (in case of an accidental incorrect change): $ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s") $ svn up $ svn di # make the changes shown in the diff ## to change column types: SELECT util.set_col_types(''"TNRS".taxon_match'', ARRAY[ (''col'', ''new_type'') ]::util.col_cast[]); -- runtime: 9 min ("531282 ms") $ rm=1 inputs/.TNRS/schema.sql.run # repeat until `svn di` shows no diff # back up new TNRS schema: $ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s") '; SET search_path = "TNRS", pg_catalog; -- -- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION batch__fill() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN new.id_by_time = new.time_submitted; new.id = COALESCE(new.id, new.id_by_time); RETURN new; END; $$; ALTER FUNCTION "TNRS".batch__fill() OWNER TO bien; -- -- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION family_is_homonym(family text) RETURNS boolean LANGUAGE sql STABLE STRICT AS $_$ SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1) $_$; ALTER FUNCTION "TNRS".family_is_homonym(family text) OWNER TO bien; -- -- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean LANGUAGE sql STABLE STRICT AS $_$ SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1) $_$; ALTER FUNCTION "TNRS".genus_is_homonym(genus text) OWNER TO bien; -- -- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text LANGUAGE sql IMMUTABLE AS $_$ /* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields: "taxonomic_status should be accepted instead of synonym when an accepted name is available (this is not always the case when a name is marked as a synonym)" */ SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END $_$; ALTER FUNCTION "TNRS".map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) OWNER TO bien; -- -- Name: remove_prefix(text, text, boolean, boolean); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true, case_sensitive boolean DEFAULT true) RETURNS text LANGUAGE sql IMMUTABLE AS $$ SELECT util.remove_prefix(prefix, str, require, case_sensitive) $$; ALTER FUNCTION "TNRS".remove_prefix(prefix text, str text, require boolean, case_sensitive boolean) OWNER TO bien; -- -- Name: FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean); Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean) IS ' wrapper that prevents views from getting dropped when the util schema is reinstalled '; -- -- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN PERFORM util.seq__reset('pg_temp.taxon_match__match_num__seq', 0); RETURN NULL; END; $$; ALTER FUNCTION "TNRS".taxon_match__batch_begin() OWNER TO bien; -- -- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match__fill() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN DECLARE "Specific_epithet_is_plant" boolean := (CASE WHEN new."*Infraspecific_epithet_matched" IS NOT NULL OR new."*Infraspecific_epithet_2_matched" IS NOT NULL OR new."*Specific_epithet_score" >= 0.9 -- fuzzy match THEN true ELSE NULL -- ambiguous END); never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false); -- author disambiguates family_is_homonym boolean = NOT never_homonym AND "TNRS".family_is_homonym(new."*Family_matched"); genus_is_homonym boolean = NOT never_homonym AND "TNRS".genus_is_homonym(new."*Genus_matched"); BEGIN /* exclude homonyms because these are not valid matches (TNRS provides a name, but the name is not meaningful because it is not unambiguous) */ new.is_valid_match = new."*Taxonomic_status" != 'Invalid' AND COALESCE(CASE WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match THEN true ELSE -- consider genus (CASE WHEN new."*Genus_score" = 1 -- exact match THEN (CASE WHEN NOT genus_is_homonym THEN true ELSE "Specific_epithet_is_plant" END) WHEN new."*Genus_score" >= 0.85 -- fuzzy match THEN "Specific_epithet_is_plant" ELSE NULL -- ambiguous END) END, false); END; DECLARE matched_taxon_name_with_author text = NULLIF(concat_ws(' ' , NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'), new."*Name_matched") , NULLIF(new."*Name_matched", 'No suitable matches found.') , new."*Name_matched_author" ), ''); accepted_taxon_name_with_author text = NULLIF(concat_ws(' ' , NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'), new."*Accepted_name") , new."*Accepted_name" , new."*Accepted_name_author" ), ''); BEGIN new.scrubbed_unique_taxon_name = COALESCE( accepted_taxon_name_with_author, matched_taxon_name_with_author); END; RETURN new; END; $$; ALTER FUNCTION "TNRS".taxon_match__fill() OWNER TO bien; -- -- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON FUNCTION taxon_match__fill() IS ' IMPORTANT: when changing this function, you must regenerate the derived cols: UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted" runtime: 30 min ("5564082 rows affected, 1918900 ms execution time") VACUUM ANALYZE "TNRS".taxon_match -- to remove previous rows runtime: 1.5 min ("92633 ms") '; -- -- Name: taxon_match__fill_derived(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match__fill_derived() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN -- clear derived cols so old values won't be used in calculations new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL; new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL; new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; new.matched_has_accepted = NULL; new."Accepted_family__@TNRS__@vegpath.org" = NULL; new."__accepted_{genus,specific_epithet}" = NULL; new."[accepted_]genus__@DwC__@vegpath.org" = NULL; new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL; new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = NULL; new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; new.__accepted_infraspecific_label = NULL; new."__accepted_infraspecific_{rank,epithet}" = NULL; new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL; new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL; new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL; new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL; new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL; new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL; new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL; new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL; new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; -- populate derived cols new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new); new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new); new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new); new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new); new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family", CASE WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name" ELSE NULL::text END) FROM (SELECT new.*) new); new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new); new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet}"[1], CASE WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name" ELSE NULL::text END) FROM (SELECT new.*) new); new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new); new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new); new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "*Accepted_name_species" END FROM (SELECT new.*) new); 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); new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new); new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new); new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new); new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new); new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new); new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "*Accepted_name_rank" ELSE "*Name_matched_rank" END FROM (SELECT new.*) new); new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org" ELSE "*Name_matched_accepted_family" END FROM (SELECT new.*) new); new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" ELSE "*Genus_matched" END FROM (SELECT new.*) new); new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Specific_epithet_matched" END FROM (SELECT new.*) new); new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "*Accepted_name_species" ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" END FROM (SELECT new.*) new); new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" END FROM (SELECT new.*) new); new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" ELSE "*Infraspecific_rank" END FROM (SELECT new.*) new); new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Infraspecific_epithet_matched" END FROM (SELECT new.*) new); new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "*Accepted_name" ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org" END FROM (SELECT new.*) new); new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "*Accepted_name_author" ELSE "*Name_matched_author" END FROM (SELECT new.*) new); new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT CASE WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" END FROM (SELECT new.*) new); RETURN new; END; $$; ALTER FUNCTION "TNRS".taxon_match__fill_derived() OWNER TO bien; -- -- Name: FUNCTION taxon_match__fill_derived(); Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON FUNCTION taxon_match__fill_derived() IS ' autogenerated, do not edit to regenerate: SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match__match_num__fill() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF new.match_num IS NULL THEN new.match_num = "TNRS".taxon_match__match_num__next(); END IF; RETURN new; END; $$; ALTER FUNCTION "TNRS".taxon_match__match_num__fill() OWNER TO bien; -- -- Name: taxon_match__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match__match_num__next() RETURNS bigint LANGUAGE sql AS $$ SELECT nextval('pg_temp.taxon_match__match_num__seq'); $$; ALTER FUNCTION "TNRS".taxon_match__match_num__next() OWNER TO bien; -- -- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO "TNRS".taxon_match_input SELECT new.*; RETURN NULL; END; $$; ALTER FUNCTION "TNRS".taxon_match_input__copy_to__insert() OWNER TO bien; -- -- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean LANGUAGE sql IMMUTABLE AS $_$ SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names())) $_$; ALTER FUNCTION "TNRS".taxon_name_is_safe(taxon_name text) OWNER TO bien; -- -- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: bien -- CREATE FUNCTION unsafe_taxon_names() RETURNS text[] LANGUAGE sql IMMUTABLE AS $$ SELECT ARRAY[ ]::text[] $$; ALTER FUNCTION "TNRS".unsafe_taxon_names() OWNER TO bien; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE taxon_match ( batch text DEFAULT now() NOT NULL, match_num integer NOT NULL, "*Name_number" integer NOT NULL, "*Name_submitted" text NOT NULL, "*Overall_score" double precision, "*Name_matched" text, "*Name_matched_rank" text, "*Name_score" double precision, "*Name_matched_author" text, "*Name_matched_url" text, "*Author_matched" text, "*Author_score" double precision, "*Family_matched" text, "*Family_score" double precision, "*Name_matched_accepted_family" text, "*Genus_matched" text, "*Genus_score" double precision, "*Specific_epithet_matched" text, "*Specific_epithet_score" double precision, "*Infraspecific_rank" text, "*Infraspecific_epithet_matched" text, "*Infraspecific_epithet_score" double precision, "*Infraspecific_rank_2" text, "*Infraspecific_epithet_2_matched" text, "*Infraspecific_epithet_2_score" double precision, "*Annotations" text, "*Unmatched_terms" text, "*Taxonomic_status" text, "*Accepted_name" text, "*Accepted_name_author" text, "*Accepted_name_rank" text, "*Accepted_name_url" text, "*Accepted_name_species" text, "*Accepted_name_family" text, "*Selected" text, "*Source" text, "*Warnings" text, "*Accepted_name_lsid" text, is_valid_match boolean NOT NULL, scrubbed_unique_taxon_name text, "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text, "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text, "matched~Name[_no_author]___@TNRS__@vegpath.org" text, "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text, matched_has_accepted boolean, "Accepted_family__@TNRS__@vegpath.org" text, "Accepted_species[_binomial]__@TNRS__@vegpath.org" text, "__accepted_{genus,specific_epithet}" text[], "[accepted_]genus__@DwC__@vegpath.org" text, "[accepted_]specificEpithet__@DwC__@vegpath.org" text, "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text, __accepted_infraspecific_label text, "__accepted_infraspecific_{rank,epithet}" text[], "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text, "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" text, "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" text, "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text, "[scrubbed_]taxonRank__@DwC__@vegpath.org" text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" text, "[scrubbed_]genus__@DwC__@vegpath.org" text, "[scrubbed_]specificEpithet__@DwC__@vegpath.org" text, "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" text, "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text, "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text, "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" text, "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text, "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text, "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text, CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family", CASE WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name" ELSE NULL::text END)))), CONSTRAINT "Accepted_species[_binomial]__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_species[_binomial]__@TNRS__@vegpath.org" IS DISTINCT FROM rtrim("*Accepted_name_species", ' '::text)))), CONSTRAINT "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[1]))), CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM COALESCE("__accepted_{genus,specific_epithet}"[1], CASE WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name" ELSE NULL::text END)))), CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))), CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM CASE WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "*Accepted_name_species" END))), 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))))), CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))), CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))), CONSTRAINT "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM (("*Genus_matched" || ' '::text) || "*Specific_epithet_matched")))), CONSTRAINT "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" CHECK ((NOT ("[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS DISTINCT FROM "*Unmatched_terms"))), CONSTRAINT "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" ELSE "*Infraspecific_rank" END))), CONSTRAINT "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "*Accepted_name_author" ELSE "*Name_matched_author" END))), CONSTRAINT "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org" ELSE "*Name_matched_accepted_family" END))), CONSTRAINT "[scrubbed_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]genus__@DwC__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" ELSE "*Genus_matched" END))), CONSTRAINT "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Infraspecific_epithet_matched" END))), CONSTRAINT "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM CASE WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" END))), CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "*Accepted_name" ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org" END))), CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" END))), CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "*Accepted_name_species" ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" END))), CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Specific_epithet_matched" END))), CONSTRAINT "[scrubbed_]taxonRank__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonRank__@DwC__@vegpath.org" IS DISTINCT FROM CASE WHEN matched_has_accepted THEN "*Accepted_name_rank" ELSE "*Name_matched_rank" END))), CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))), 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)))), CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))), CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))), CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))), 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)))) ); ALTER TABLE "TNRS".taxon_match OWNER TO bien; -- -- Name: TABLE taxon_match; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON TABLE taxon_match IS ' whenever columns are renamed: SELECT util.derived_cols_update(''"TNRS".taxon_match''); to port derived column changes to vegbiendev: SELECT util.derived_cols_export(''"TNRS".taxon_match''); # run the returned SQL on vegbiendev -- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time") SELECT util.recreate_view(''"TNRS".taxon_best_match''); to add a new derived column: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''col''), $$ expr $$)); SELECT util.derived_cols_populate(''"TNRS".taxon_match''); SELECT util.recreate_view(''"TNRS".taxon_best_match''); $ make schemas/remake to remove a column: SELECT util.recreate_view(''"TNRS".taxon_best_match''); $ make schemas/remake to move a derived column to the middle or to add a non-derived column: make the changes in inputs/.TNRS/schema.sql $ inputs/.TNRS/data.sql.run refresh # re-run TNRS SELECT util.derived_cols_update(''"TNRS".taxon_match''); SELECT util.derived_cols_repopulate(''"TNRS".taxon_match''); SELECT util.recreate_view(''"TNRS".taxon_best_match''); $ make schemas/remake to add a constraint: runtime: 3 min ("173620 ms") '; -- -- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS ' = "*Unmatched_terms" derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' = ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched" derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org" IS ' = NULLIF("*Name_matched", ''No suitable matches found.''::text) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col, $$NULLIF("*Name_matched", ''No suitable matches found.''::text)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' = "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match.matched_has_accepted; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match.matched_has_accepted IS ' = "*Accepted_name" IS NOT NULL derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org" IS ' = COALESCE("*Accepted_name_family", CASE WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name" ELSE NULL::text END) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col, $$COALESCE("*Accepted_name_family", CASE WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name" ELSE NULL::text END)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org" IS ' = rtrim("*Accepted_name_species", '' ''::text) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_species[_binomial]__@TNRS__@vegpath.org'')::util.col, $$rtrim("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS ' = regexp_split_to_array("*Accepted_name_species", '' ''::text) derived column to modify expr: 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); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS ' = COALESCE("__accepted_{genus,specific_epithet}"[1], CASE WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name" ELSE NULL::text END) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$COALESCE("__accepted_{genus,specific_epithet}"[1], CASE WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name" ELSE NULL::text END)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS ' = "__accepted_{genus,specific_epithet}"[2] derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' = CASE WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "*Accepted_name_species" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "*Accepted_name_species" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' = ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text) derived column to modify expr: 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); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS ' = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' = "__accepted_infraspecific_{rank,epithet}"[1] derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS ' = "__accepted_infraspecific_{rank,epithet}"[2] derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text) derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS ' = "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonomicStatus__@DwC__@vegpath.org'')::util.col, $$"TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "*Accepted_name_rank" ELSE "*Name_matched_rank" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonRank__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_rank" ELSE "*Name_matched_rank" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org" ELSE "*Name_matched_accepted_family" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org" ELSE "*Name_matched_accepted_family" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" ELSE "*Genus_matched" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]genus__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" ELSE "*Genus_matched" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Specific_epithet_matched" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Specific_epithet_matched" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "*Accepted_name_species" ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_species" ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' = CASE WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org") ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" ELSE "*Infraspecific_rank" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" ELSE "*Infraspecific_rank" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Infraspecific_epithet_matched" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Infraspecific_epithet_matched" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "*Accepted_name" ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name" ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "*Accepted_name_author" ELSE "*Name_matched_author" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_author" ELSE "*Name_matched_author" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' = CASE WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" END derived column to modify expr: SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" END$$)::util.derived_col_def); SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); to rename: # rename column # rename CHECK constraint SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass); '; -- -- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW taxon_best_match AS SELECT taxon_match.batch, taxon_match.match_num, taxon_match."*Name_number", taxon_match."*Name_submitted", taxon_match."*Overall_score", taxon_match."*Name_matched", taxon_match."*Name_matched_rank", taxon_match."*Name_score", taxon_match."*Name_matched_author", taxon_match."*Name_matched_url", taxon_match."*Author_matched", taxon_match."*Author_score", taxon_match."*Family_matched", taxon_match."*Family_score", taxon_match."*Name_matched_accepted_family", taxon_match."*Genus_matched", taxon_match."*Genus_score", taxon_match."*Specific_epithet_matched", taxon_match."*Specific_epithet_score", taxon_match."*Infraspecific_rank", taxon_match."*Infraspecific_epithet_matched", taxon_match."*Infraspecific_epithet_score", taxon_match."*Infraspecific_rank_2", taxon_match."*Infraspecific_epithet_2_matched", taxon_match."*Infraspecific_epithet_2_score", taxon_match."*Annotations", taxon_match."*Unmatched_terms", taxon_match."*Taxonomic_status", taxon_match."*Accepted_name", taxon_match."*Accepted_name_author", taxon_match."*Accepted_name_rank", taxon_match."*Accepted_name_url", taxon_match."*Accepted_name_species", taxon_match."*Accepted_name_family", taxon_match."*Selected", taxon_match."*Source", taxon_match."*Warnings", taxon_match."*Accepted_name_lsid", taxon_match.is_valid_match, taxon_match.scrubbed_unique_taxon_name, taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org", taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", taxon_match.matched_has_accepted, taxon_match."Accepted_family__@TNRS__@vegpath.org", taxon_match."__accepted_{genus,specific_epithet}", taxon_match."[accepted_]genus__@DwC__@vegpath.org", taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org", taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org", taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", taxon_match.__accepted_infraspecific_label, taxon_match."__accepted_infraspecific_{rank,epithet}", taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org", taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", taxon_match."[scrubbed_]genus__@DwC__@vegpath.org", taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org", taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" FROM taxon_match WHERE (taxon_match."*Selected" = 'true'::text); ALTER TABLE "TNRS".taxon_best_match OWNER TO bien; -- -- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON VIEW taxon_best_match IS ' to modify: SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$ SELECT __ $$); '; -- -- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW "MatchedTaxon" AS SELECT taxon_best_match.batch, taxon_best_match.match_num, taxon_best_match."*Name_number", taxon_best_match."*Name_submitted", taxon_best_match."*Overall_score", taxon_best_match."*Name_matched", taxon_best_match."*Name_matched_rank", taxon_best_match."*Name_score", taxon_best_match."*Name_matched_author", taxon_best_match."*Name_matched_url", taxon_best_match."*Author_matched", taxon_best_match."*Author_score", taxon_best_match."*Family_matched", taxon_best_match."*Family_score", taxon_best_match."*Name_matched_accepted_family", taxon_best_match."*Genus_matched", taxon_best_match."*Genus_score", taxon_best_match."*Specific_epithet_matched", taxon_best_match."*Specific_epithet_score", taxon_best_match."*Infraspecific_rank", taxon_best_match."*Infraspecific_epithet_matched", taxon_best_match."*Infraspecific_epithet_score", taxon_best_match."*Infraspecific_rank_2", taxon_best_match."*Infraspecific_epithet_2_matched", taxon_best_match."*Infraspecific_epithet_2_score", taxon_best_match."*Annotations", taxon_best_match."*Unmatched_terms", taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name", taxon_best_match."*Accepted_name_author", taxon_best_match."*Accepted_name_rank", taxon_best_match."*Accepted_name_url", taxon_best_match."*Accepted_name_species", taxon_best_match."*Accepted_name_family", taxon_best_match."*Selected", taxon_best_match."*Source", taxon_best_match."*Warnings", taxon_best_match."*Accepted_name_lsid", taxon_best_match.is_valid_match, taxon_best_match.scrubbed_unique_taxon_name, taxon_best_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", taxon_best_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_best_match."matched~Name[_no_author]___@TNRS__@vegpath.org", taxon_best_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", taxon_best_match.matched_has_accepted, taxon_best_match."Accepted_family__@TNRS__@vegpath.org", taxon_best_match."__accepted_{genus,specific_epithet}", taxon_best_match."[accepted_]genus__@DwC__@vegpath.org", taxon_best_match."[accepted_]specificEpithet__@DwC__@vegpath.org", taxon_best_match."Accepted_species[_binomial]__@TNRS__@vegpath.org", taxon_best_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", taxon_best_match.__accepted_infraspecific_label, taxon_best_match."__accepted_infraspecific_{rank,epithet}", taxon_best_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", taxon_best_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", taxon_best_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]taxonRank__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", taxon_best_match."[scrubbed_]genus__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_best_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", taxon_best_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", taxon_best_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", taxon_best_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", taxon_best_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", taxon_best_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org", map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus", CASE WHEN (taxon_best_match."*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name_family", taxon_best_match."*Unmatched_terms") WHEN (taxon_best_match."*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name", taxon_best_match."*Unmatched_terms") ELSE taxon_best_match."*Accepted_name_species" END AS accepted_morphospecies_binomial FROM taxon_best_match; ALTER TABLE "TNRS"."MatchedTaxon" OWNER TO bien; -- -- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON VIEW "MatchedTaxon" IS ' to modify: SELECT util.recreate_view(''"TNRS"."MatchedTaxon"'', $$ SELECT __ $$); '; -- -- Name: Source; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE "Source" ( "*row_num" integer NOT NULL, "sourceType" text DEFAULT 'aggregator'::text NOT NULL, "datasetURL" text DEFAULT 'http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html'::text NOT NULL, CONSTRAINT nulls_mapped CHECK (true) ); ALTER TABLE "TNRS"."Source" OWNER TO bien; -- -- Name: COLUMN "Source"."sourceType"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN "Source"."sourceType" IS ' constant '; -- -- Name: COLUMN "Source"."datasetURL"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN "Source"."datasetURL" IS ' constant '; -- -- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW "ValidMatchedTaxon" AS SELECT "MatchedTaxon".batch, "MatchedTaxon".match_num, "MatchedTaxon"."*Name_number", "MatchedTaxon"."*Name_submitted", "MatchedTaxon"."*Overall_score", "MatchedTaxon"."*Name_matched", "MatchedTaxon"."*Name_matched_rank", "MatchedTaxon"."*Name_score", "MatchedTaxon"."*Name_matched_author", "MatchedTaxon"."*Name_matched_url", "MatchedTaxon"."*Author_matched", "MatchedTaxon"."*Author_score", "MatchedTaxon"."*Family_matched", "MatchedTaxon"."*Family_score", "MatchedTaxon"."*Name_matched_accepted_family", "MatchedTaxon"."*Genus_matched", "MatchedTaxon"."*Genus_score", "MatchedTaxon"."*Specific_epithet_matched", "MatchedTaxon"."*Specific_epithet_score", "MatchedTaxon"."*Infraspecific_rank", "MatchedTaxon"."*Infraspecific_epithet_matched", "MatchedTaxon"."*Infraspecific_epithet_score", "MatchedTaxon"."*Infraspecific_rank_2", "MatchedTaxon"."*Infraspecific_epithet_2_matched", "MatchedTaxon"."*Infraspecific_epithet_2_score", "MatchedTaxon"."*Annotations", "MatchedTaxon"."*Unmatched_terms", "MatchedTaxon"."*Taxonomic_status", "MatchedTaxon"."*Accepted_name", "MatchedTaxon"."*Accepted_name_author", "MatchedTaxon"."*Accepted_name_rank", "MatchedTaxon"."*Accepted_name_url", "MatchedTaxon"."*Accepted_name_species", "MatchedTaxon"."*Accepted_name_family", "MatchedTaxon"."*Selected", "MatchedTaxon"."*Source", "MatchedTaxon"."*Warnings", "MatchedTaxon"."*Accepted_name_lsid", "MatchedTaxon".is_valid_match, "MatchedTaxon".scrubbed_unique_taxon_name, "MatchedTaxon"."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", "MatchedTaxon"."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", "MatchedTaxon"."matched~Name[_no_author]___@TNRS__@vegpath.org", "MatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", "MatchedTaxon".matched_has_accepted, "MatchedTaxon"."Accepted_family__@TNRS__@vegpath.org", "MatchedTaxon"."__accepted_{genus,specific_epithet}", "MatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org", "MatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org", "MatchedTaxon"."Accepted_species[_binomial]__@TNRS__@vegpath.org", "MatchedTaxon"."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "MatchedTaxon".__accepted_infraspecific_label, "MatchedTaxon"."__accepted_infraspecific_{rank,epithet}", "MatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "MatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", "MatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "MatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", "MatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "MatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "MatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", "MatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", "MatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", "MatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org", "MatchedTaxon"."taxonomicStatus", "MatchedTaxon".accepted_morphospecies_binomial FROM "MatchedTaxon" WHERE "MatchedTaxon".is_valid_match; ALTER TABLE "TNRS"."ValidMatchedTaxon" OWNER TO bien; -- -- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON VIEW "ValidMatchedTaxon" IS ' to update, use * as the column list '; -- -- Name: batch; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE batch ( id text NOT NULL, id_by_time text, time_submitted timestamp with time zone DEFAULT now(), client_version text ); ALTER TABLE "TNRS".batch OWNER TO bien; -- -- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE batch_download_settings ( id text NOT NULL, "E-mail" text, "Id" text, "Job type" text, "Contains Id" boolean, "Start time" text, "Finish time" text, "TNRS version" text, "Sources selected" text, "Match threshold" double precision, "Classification" text, "Allow partial matches?" boolean, "Sort by source" boolean, "Constrain by higher taxonomy" boolean ); ALTER TABLE "TNRS".batch_download_settings OWNER TO bien; -- -- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON TABLE batch_download_settings IS ' stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt '; -- -- Name: client_version; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE client_version ( id text NOT NULL, global_rev integer NOT NULL, "/lib/tnrs.py rev" integer, "/bin/tnrs_db rev" integer ); ALTER TABLE "TNRS".client_version OWNER TO bien; -- -- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON TABLE client_version IS ' contains svn revisions '; -- -- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN client_version.global_rev IS ' from `svn info .` > Last Changed Rev '; -- -- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS ' from `svn info lib/tnrs.py` > Last Changed Rev '; -- -- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS ' from `svn info bin/tnrs_db` > Last Changed Rev '; -- -- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW taxon_match_input AS SELECT taxon_match."*Name_number" AS "Name_number", taxon_match."*Name_submitted" AS "Name_submitted", taxon_match."*Overall_score" AS "Overall_score", taxon_match."*Name_matched" AS "Name_matched", taxon_match."*Name_matched_rank" AS "Name_matched_rank", taxon_match."*Name_score" AS "Name_score", taxon_match."*Name_matched_author" AS "Name_matched_author", taxon_match."*Name_matched_url" AS "Name_matched_url", taxon_match."*Author_matched" AS "Author_matched", taxon_match."*Author_score" AS "Author_score", taxon_match."*Family_matched" AS "Family_matched", taxon_match."*Family_score" AS "Family_score", taxon_match."*Name_matched_accepted_family" AS "Name_matched_accepted_family", taxon_match."*Genus_matched" AS "Genus_matched", taxon_match."*Genus_score" AS "Genus_score", taxon_match."*Specific_epithet_matched" AS "Specific_epithet_matched", taxon_match."*Specific_epithet_score" AS "Specific_epithet_score", taxon_match."*Infraspecific_rank" AS "Infraspecific_rank", taxon_match."*Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", taxon_match."*Infraspecific_epithet_score" AS "Infraspecific_epithet_score", taxon_match."*Infraspecific_rank_2" AS "Infraspecific_rank_2", taxon_match."*Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", taxon_match."*Infraspecific_epithet_2_score" AS "Infraspecific_epithet_2_score", taxon_match."*Annotations" AS "Annotations", taxon_match."*Unmatched_terms" AS "Unmatched_terms", taxon_match."*Taxonomic_status" AS "Taxonomic_status", taxon_match."*Accepted_name" AS "Accepted_name", taxon_match."*Accepted_name_author" AS "Accepted_name_author", taxon_match."*Accepted_name_rank" AS "Accepted_name_rank", taxon_match."*Accepted_name_url" AS "Accepted_name_url", taxon_match."*Accepted_name_species" AS "Accepted_name_species", taxon_match."*Accepted_name_family" AS "Accepted_name_family", taxon_match."*Selected" AS "Selected", taxon_match."*Source" AS "Source", taxon_match."*Warnings" AS "Warnings", taxon_match."*Accepted_name_lsid" AS "Accepted_name_lsid" FROM taxon_match; ALTER TABLE "TNRS".taxon_match_input OWNER TO bien; -- -- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE taxon_match_input__copy_to ( "Name_number" integer, "Name_submitted" text, "Overall_score" double precision, "Name_matched" text, "Name_matched_rank" text, "Name_score" double precision, "Name_matched_author" text, "Name_matched_url" text, "Author_matched" text, "Author_score" double precision, "Family_matched" text, "Family_score" double precision, "Name_matched_accepted_family" text, "Genus_matched" text, "Genus_score" double precision, "Specific_epithet_matched" text, "Specific_epithet_score" double precision, "Infraspecific_rank" text, "Infraspecific_epithet_matched" text, "Infraspecific_epithet_score" double precision, "Infraspecific_rank_2" text, "Infraspecific_epithet_2_matched" text, "Infraspecific_epithet_2_score" double precision, "Annotations" text, "Unmatched_terms" text, "Taxonomic_status" text, "Accepted_name" text, "Accepted_name_author" text, "Accepted_name_rank" text, "Accepted_name_url" text, "Accepted_name_species" text, "Accepted_name_family" text, "Selected" text, "Source" text, "Warnings" text, "Accepted_name_lsid" text ); ALTER TABLE "TNRS".taxon_match_input__copy_to OWNER TO bien; -- -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name, taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank, COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family, taxon_match."*Genus_matched" AS scrubbed_genus, taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet, taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank, taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, taxon_match."*Name_matched_author" AS scrubbed_author, taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author, (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author FROM taxon_match; ALTER TABLE "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" OWNER TO bien; -- -- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS ' to modify: SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$ SELECT __ $$); scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse. '; -- -- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: bien -- CREATE VIEW taxon_scrub AS SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, "ValidMatchedTaxon".batch, "ValidMatchedTaxon".match_num, "ValidMatchedTaxon"."*Name_number", "ValidMatchedTaxon"."*Name_submitted", "ValidMatchedTaxon"."*Overall_score", "ValidMatchedTaxon"."*Name_matched", "ValidMatchedTaxon"."*Name_matched_rank", "ValidMatchedTaxon"."*Name_score", "ValidMatchedTaxon"."*Name_matched_author", "ValidMatchedTaxon"."*Name_matched_url", "ValidMatchedTaxon"."*Author_matched", "ValidMatchedTaxon"."*Author_score", "ValidMatchedTaxon"."*Family_matched", "ValidMatchedTaxon"."*Family_score", "ValidMatchedTaxon"."*Name_matched_accepted_family", "ValidMatchedTaxon"."*Genus_matched", "ValidMatchedTaxon"."*Genus_score", "ValidMatchedTaxon"."*Specific_epithet_matched", "ValidMatchedTaxon"."*Specific_epithet_score", "ValidMatchedTaxon"."*Infraspecific_rank", "ValidMatchedTaxon"."*Infraspecific_epithet_matched", "ValidMatchedTaxon"."*Infraspecific_epithet_score", "ValidMatchedTaxon"."*Infraspecific_rank_2", "ValidMatchedTaxon"."*Infraspecific_epithet_2_matched", "ValidMatchedTaxon"."*Infraspecific_epithet_2_score", "ValidMatchedTaxon"."*Annotations", "ValidMatchedTaxon"."*Unmatched_terms", "ValidMatchedTaxon"."*Taxonomic_status", "ValidMatchedTaxon"."*Accepted_name", "ValidMatchedTaxon"."*Accepted_name_author", "ValidMatchedTaxon"."*Accepted_name_rank", "ValidMatchedTaxon"."*Accepted_name_url", "ValidMatchedTaxon"."*Accepted_name_species", "ValidMatchedTaxon"."*Accepted_name_family", "ValidMatchedTaxon"."*Selected", "ValidMatchedTaxon"."*Source", "ValidMatchedTaxon"."*Warnings", "ValidMatchedTaxon"."*Accepted_name_lsid", "ValidMatchedTaxon".is_valid_match, "ValidMatchedTaxon"."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", "ValidMatchedTaxon"."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", "ValidMatchedTaxon"."matched~Name[_no_author]___@TNRS__@vegpath.org", "ValidMatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org", "ValidMatchedTaxon".matched_has_accepted, "ValidMatchedTaxon"."Accepted_family__@TNRS__@vegpath.org", "ValidMatchedTaxon"."__accepted_{genus,specific_epithet}", "ValidMatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org", "ValidMatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org", "ValidMatchedTaxon"."Accepted_species[_binomial]__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "ValidMatchedTaxon".__accepted_infraspecific_label, "ValidMatchedTaxon"."__accepted_infraspecific_{rank,epithet}", "ValidMatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org", "ValidMatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", "ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org", "ValidMatchedTaxon"."taxonomicStatus", "ValidMatchedTaxon".accepted_morphospecies_binomial, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, CASE WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."*Unmatched_terms") WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."*Unmatched_terms") ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet) END AS scrubbed_morphospecies_binomial FROM ("ValidMatchedTaxon" LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name)); ALTER TABLE "TNRS".taxon_scrub OWNER TO bien; -- -- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: bien -- COMMENT ON VIEW taxon_scrub IS ' to modify: SELECT util.recreate_view(''"TNRS".taxon_scrub'', $$ SELECT __ $$); '; -- -- Name: ~Source.map; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace: -- CREATE TABLE "~Source.map" ( "from" text NOT NULL, "to" text, filter text, notes text ); ALTER TABLE "TNRS"."~Source.map" OWNER TO bien; -- -- Data for Name: Source; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY "Source" ("*row_num", "sourceType", "datasetURL") FROM stdin; 1 aggregator http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html \. -- -- Data for Name: batch; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY batch (id, id_by_time, time_submitted, client_version) FROM stdin; 2014-07-26 21:26:21.824011-07 2014-07-26 21:26:21.824011-07 2014-07-26 21:26:21.824011-07 \N \. -- -- Data for Name: batch_download_settings; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY batch_download_settings (id, "E-mail", "Id", "Job type", "Contains Id", "Start time", "Finish time", "TNRS version", "Sources selected", "Match threshold", "Classification", "Allow partial matches?", "Sort by source", "Constrain by higher taxonomy") FROM stdin; \. -- -- Data for Name: client_version; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY client_version (id, global_rev, "/lib/tnrs.py rev", "/bin/tnrs_db rev") FROM stdin; \. -- -- Data for Name: taxon_match; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY taxon_match (batch, match_num, "*Name_number", "*Name_submitted", "*Overall_score", "*Name_matched", "*Name_matched_rank", "*Name_score", "*Name_matched_author", "*Name_matched_url", "*Author_matched", "*Author_score", "*Family_matched", "*Family_score", "*Name_matched_accepted_family", "*Genus_matched", "*Genus_score", "*Specific_epithet_matched", "*Specific_epithet_score", "*Infraspecific_rank", "*Infraspecific_epithet_matched", "*Infraspecific_epithet_score", "*Infraspecific_rank_2", "*Infraspecific_epithet_2_matched", "*Infraspecific_epithet_2_score", "*Annotations", "*Unmatched_terms", "*Taxonomic_status", "*Accepted_name", "*Accepted_name_author", "*Accepted_name_rank", "*Accepted_name_url", "*Accepted_name_species", "*Accepted_name_family", "*Selected", "*Source", "*Warnings", "*Accepted_name_lsid", is_valid_match, scrubbed_unique_taxon_name, "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", "matched~Name[_no_author]___@TNRS__@vegpath.org", "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", matched_has_accepted, "Accepted_family__@TNRS__@vegpath.org", "Accepted_species[_binomial]__@TNRS__@vegpath.org", "__accepted_{genus,specific_epithet}", "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org", "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", __accepted_infraspecific_label, "__accepted_infraspecific_{rank,epithet}", "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", "[scrubbed_]taxonRank__@DwC__@vegpath.org", "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]genus__@DwC__@vegpath.org", "[scrubbed_]specificEpithet__@DwC__@vegpath.org", "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org") FROM stdin; 2014-07-26 21:26:21.824011-07 0 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 \N http://www.theplantlist.org/1.1/browse/A/Compositae/ \N \N Compositae 1 \N \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Accepted Compositae \N family http://www.theplantlist.org/1.1/browse/A/Compositae/ \N \N true tpl [Ambiguous match] \N t Compositae indet. sp.1 \N Compositae Compositae t Compositae \N \N \N \N Compositae indet. sp.1 \N \N \N \N Compositae accepted family Compositae \N \N \N Compositae indet. sp.1 \N \N Compositae \N Compositae 2014-07-26 21:26:21.824011-07 1 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 \N \N \N \N Compositae 1 \N \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Accepted Compositae \N family \N \N \N false tpl [Ambiguous match] \N t Compositae indet. sp.1 \N Compositae Compositae t Compositae \N \N \N \N Compositae indet. sp.1 \N \N \N \N Compositae accepted family Compositae \N \N \N Compositae indet. sp.1 \N \N Compositae \N Compositae 2014-07-26 21:26:21.824011-07 2 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 Giseke http://www.tropicos.org/Name/50255940 \N \N Compositae 1 Compositae \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Synonym Asteraceae Bercht. & J. Presl family http://www.tropicos.org/Name/50307371 \N Asteraceae false tropicos \N t Asteraceae Bercht. & J. Presl indet. sp.1 \N Compositae Compositae Giseke t Asteraceae \N \N \N \N Asteraceae indet. sp.1 \N \N \N \N Asteraceae Bercht. & J. Presl accepted family Asteraceae \N \N \N Asteraceae indet. sp.1 \N \N Asteraceae Bercht. & J. Presl Asteraceae Bercht. & J. Presl 2014-07-26 21:26:21.824011-07 3 1 Fabaceae Boyle#6500 0.839999999999999969 Fagaceae family 0.939999999999999947 \N ;http://plants.usda.gov/java/nameSearch \N \N Fagaceae 0.880000000000000004 \N \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fagaceae \N family ;http://plants.usda.gov/java/nameSearch \N \N true tpl;usda \N f Fagaceae Boyle#6500 \N Fagaceae Fagaceae t Fagaceae \N \N \N \N Fagaceae Boyle#6500 \N \N \N \N Fagaceae accepted family Fagaceae \N \N \N Fagaceae Boyle#6500 \N \N Fagaceae \N Fagaceae 2014-07-26 21:26:21.824011-07 4 1 Fabaceae Boyle#6500 0.900000000000000022 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae false tropicos \N t Fabaceae Lindl. Boyle#6500 \N Fabaceae Fabaceae Lindl. t Fabaceae \N \N \N \N Fabaceae Boyle#6500 \N \N \N \N Fabaceae Lindl. accepted family Fabaceae \N \N \N Fabaceae Boyle#6500 \N \N Fabaceae Lindl. Fabaceae Lindl. 2014-07-26 21:26:21.824011-07 5 1 Fabaceae Boyle#6500 0.839999999999999969 Fagaceae family 0.939999999999999947 Dumort. http://www.tropicos.org/Name/42000062 \N \N Fagaceae 0.880000000000000004 Fagaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fagaceae Dumort. family http://www.tropicos.org/Name/42000062 \N Fagaceae false tropicos \N f Fagaceae Dumort. Boyle#6500 \N Fagaceae Fagaceae Dumort. t Fagaceae \N \N \N \N Fagaceae Boyle#6500 \N \N \N \N Fagaceae Dumort. accepted family Fagaceae \N \N \N Fagaceae Boyle#6500 \N \N Fagaceae Dumort. Fagaceae Dumort. 2014-07-26 21:26:21.824011-07 6 1 Fabaceae Boyle#6500 0.67000000000000004 Ficaceae family 0.770000000000000018 Bercht. & J. Presl http://www.tropicos.org/Name/100353631 \N \N Ficaceae 0.75 Ficaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 No opinion \N \N \N \N \N \N false tropicos \N f Ficaceae Bercht. & J. Presl Boyle#6500 \N Ficaceae Ficaceae Bercht. & J. Presl f \N \N \N \N \N \N \N \N \N \N \N no opinion family Ficaceae \N \N \N Ficaceae Boyle#6500 \N \N Ficaceae Bercht. & J. Presl Ficaceae Bercht. & J. Presl 2014-07-26 21:26:21.824011-07 7 1 Fabaceae Boyle#6500 0.67000000000000004 Fucaceae family 0.770000000000000018 \N http://www.tropicos.org/Name/100371040 \N \N Fucaceae 0.75 Fucaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 No opinion \N \N \N \N \N \N false tropicos \N f Fucaceae Boyle#6500 \N Fucaceae Fucaceae f \N \N \N \N \N \N \N \N \N \N \N no opinion family Fucaceae \N \N \N Fucaceae Boyle#6500 \N \N Fucaceae \N Fucaceae 2014-07-26 21:26:21.824011-07 8 1 Fabaceae Boyle#6500 0.900000000000000022 Fabaceae family 1 \N http://plants.usda.gov/java/nameSearch \N \N Fabaceae 1 \N \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fabaceae \N family http://plants.usda.gov/java/nameSearch \N \N false usda \N t Fabaceae Boyle#6500 \N Fabaceae Fabaceae t Fabaceae \N \N \N \N Fabaceae Boyle#6500 \N \N \N \N Fabaceae accepted family Fabaceae \N \N \N Fabaceae Boyle#6500 \N \N Fabaceae \N Fabaceae 2014-07-26 21:26:21.824011-07 9 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 \N http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Accepted Inga \N genus http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N Fabaceae true tpl;usda \N t Fabaceae Inga "fuzzy leaf" \N Inga Inga t Fabaceae \N \N Inga \N Inga "fuzzy leaf" \N \N \N \N Inga accepted genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga \N Inga 2014-07-26 21:26:21.824011-07 10 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae false tropicos \N t Fabaceae Inga Mill. "fuzzy leaf" \N Inga Inga Mill. t Fabaceae \N \N Inga \N Inga "fuzzy leaf" \N \N \N \N Inga Mill. accepted genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga Mill. Inga Mill. 2014-07-26 21:26:21.824011-07 11 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 Scop. http://www.tropicos.org/Name/50215121 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Illegitimate \N \N \N \N \N \N false tropicos \N t Fabaceae Inga Scop. "fuzzy leaf" \N Inga Inga Scop. f \N \N \N \N \N \N \N \N \N \N \N illegitimate genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga Scop. Inga Scop. 2014-07-26 21:26:21.824011-07 12 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 \N http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Accepted Inga \N genus http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N Fabaceae true tpl;usda \N t Fabaceae Inga sp.3 \N Inga Inga t Fabaceae \N \N Inga \N Inga sp.3 \N \N \N \N Inga accepted genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga \N Inga 2014-07-26 21:26:21.824011-07 13 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae false tropicos \N t Fabaceae Inga Mill. sp.3 \N Inga Inga Mill. t Fabaceae \N \N Inga \N Inga sp.3 \N \N \N \N Inga Mill. accepted genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga Mill. Inga Mill. 2014-07-26 21:26:21.824011-07 14 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 Scop. http://www.tropicos.org/Name/50215121 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Illegitimate \N \N \N \N \N \N false tropicos \N t Fabaceae Inga Scop. sp.3 \N Inga Inga Scop. f \N \N \N \N \N \N \N \N \N \N \N illegitimate genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga Scop. Inga Scop. 2014-07-26 21:26:21.824011-07 15 4 Fabaceae unknown #2 0.839999999999999969 Fagaceae family 0.939999999999999947 \N ;http://plants.usda.gov/java/nameSearch \N \N Fagaceae 0.880000000000000004 \N \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fagaceae \N family ;http://plants.usda.gov/java/nameSearch \N \N true tpl;usda \N f Fagaceae unknown #2 \N Fagaceae Fagaceae t Fagaceae \N \N \N \N Fagaceae unknown #2 \N \N \N \N Fagaceae accepted family Fagaceae \N \N \N Fagaceae unknown #2 \N \N Fagaceae \N Fagaceae 2014-07-26 21:26:21.824011-07 16 4 Fabaceae unknown #2 0.900000000000000022 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae false tropicos \N t Fabaceae Lindl. unknown #2 \N Fabaceae Fabaceae Lindl. t Fabaceae \N \N \N \N Fabaceae unknown #2 \N \N \N \N Fabaceae Lindl. accepted family Fabaceae \N \N \N Fabaceae unknown #2 \N \N Fabaceae Lindl. Fabaceae Lindl. 2014-07-26 21:26:21.824011-07 17 4 Fabaceae unknown #2 0.839999999999999969 Fagaceae family 0.939999999999999947 Dumort. http://www.tropicos.org/Name/42000062 \N \N Fagaceae 0.880000000000000004 Fagaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fagaceae Dumort. family http://www.tropicos.org/Name/42000062 \N Fagaceae false tropicos \N f Fagaceae Dumort. unknown #2 \N Fagaceae Fagaceae Dumort. t Fagaceae \N \N \N \N Fagaceae unknown #2 \N \N \N \N Fagaceae Dumort. accepted family Fagaceae \N \N \N Fagaceae unknown #2 \N \N Fagaceae Dumort. Fagaceae Dumort. 2014-07-26 21:26:21.824011-07 18 4 Fabaceae unknown #2 0.67000000000000004 Ficaceae family 0.770000000000000018 Bercht. & J. Presl http://www.tropicos.org/Name/100353631 \N \N Ficaceae 0.75 Ficaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 No opinion \N \N \N \N \N \N false tropicos \N f Ficaceae Bercht. & J. Presl unknown #2 \N Ficaceae Ficaceae Bercht. & J. Presl f \N \N \N \N \N \N \N \N \N \N \N no opinion family Ficaceae \N \N \N Ficaceae unknown #2 \N \N Ficaceae Bercht. & J. Presl Ficaceae Bercht. & J. Presl 2014-07-26 21:26:21.824011-07 19 4 Fabaceae unknown #2 0.67000000000000004 Fucaceae family 0.770000000000000018 \N http://www.tropicos.org/Name/100371040 \N \N Fucaceae 0.75 Fucaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 No opinion \N \N \N \N \N \N false tropicos \N f Fucaceae unknown #2 \N Fucaceae Fucaceae f \N \N \N \N \N \N \N \N \N \N \N no opinion family Fucaceae \N \N \N Fucaceae unknown #2 \N \N Fucaceae \N Fucaceae 2014-07-26 21:26:21.824011-07 20 4 Fabaceae unknown #2 0.900000000000000022 Fabaceae family 1 \N http://plants.usda.gov/java/nameSearch \N \N Fabaceae 1 \N \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fabaceae \N family http://plants.usda.gov/java/nameSearch \N \N false usda \N t Fabaceae unknown #2 \N Fabaceae Fabaceae t Fabaceae \N \N \N \N Fabaceae unknown #2 \N \N \N \N Fabaceae accepted family Fabaceae \N \N \N Fabaceae unknown #2 \N \N Fabaceae \N Fabaceae 2014-07-26 21:26:21.824011-07 21 5 Fam_indet. Boyle#6501 0 No suitable matches found. \N 0 \N \N \N 0 \N 0 \N \N 0 \N 0 \N \N 0 \N \N 0 \N \N \N \N \N \N \N \N \N true \N \N f \N \N \N \N \N f \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N 2014-07-26 21:26:21.824011-07 22 6 Poa annua 1 Poa annua species 1 L. http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN \N \N \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tpl;tropicos;usda \N t Poaceae Poa annua L. \N Poa annua Poa annua Poa annua L. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L. 2014-07-26 21:26:21.824011-07 23 6 Poa annua 1 Poa annua species 1 Cham. & Schltdl. http://www.theplantlist.org/tpl1.1/record/kew-435195 \N \N \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae false tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua Poa annua Cham. & Schltdl. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 24 7 Poa annua L. 1 Poa annua species 1 L. http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN L. 1 \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tpl;tropicos;usda \N t Poaceae Poa annua L. \N Poa annua Poa annua Poa annua L. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L. 2014-07-26 21:26:21.824011-07 25 7 Poa annua L. 0.800000000000000044 Poa annua species 1 Cham. & Schltdl. http://www.theplantlist.org/tpl1.1/record/kew-435195 Cham. & Schltdl. 0 \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae false tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua Poa annua Cham. & Schltdl. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 26 8 Poa annua fo. lanuginosa 1 Poa annua fo. lanuginosa fo. 1 Sennen http://www.theplantlist.org/tpl1.1/record/tro-50267771 \N \N \N \N \N Poa 1 annua 1 fo. lanuginosa 1 \N \N \N \N \N Synonym Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194 Poa annua Poaceae true tpl \N t Poaceae Poa annua L. \N Poa annua Poa annua fo. lanuginosa Poa annua fo. lanuginosa Sennen t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L. 2014-07-26 21:26:21.824011-07 27 8 Poa annua fo. lanuginosa 1 Poa annua fo. lanuginosa forma 1 Sennen http://www.tropicos.org/Name/50267771 \N \N \N \N Poaceae Poa 1 annua 1 fo. lanuginosa 1 \N \N \N \N \N Synonym Poa annua var. annua \N variety http://www.tropicos.org/Name/25517736 Poa annua Poaceae false tropicos \N t Poaceae Poa annua var. annua \N Poa annua Poa annua fo. lanuginosa Poa annua fo. lanuginosa Sennen t Poaceae Poa annua {Poa,annua} Poa annua Poa annua var. annua {var.,annua} var. annua Poa annua var. annua accepted variety Poaceae Poa annua Poa annua Poa annua var. annua Poa annua var. annua \N Poa annua var. annua 2014-07-26 21:26:21.824011-07 28 9 Poa annua ssp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae true tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 29 9 Poa annua ssp. exilis 0.959999999999999964 Poa annua var. exilis variety 0.959999999999999964 Tomm. ex Freyn http://www.tropicos.org/Name/25547854 \N \N \N \N Poaceae Poa 1 annua 1 var. exilis 0.699999999999999956 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae false tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua var. exilis Poa annua var. exilis Tomm. ex Freyn t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 30 10 Poa annua subsp. exilis 1 Poa annua subsp. exilis subsp. 1 (Tomm. ex Freyn.) Asch. & Graebn. http://www.theplantlist.org/tpl1.1/record/kew-435202 \N \N \N \N \N Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae true tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn.) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 31 10 Poa annua subsp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae false tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth 2014-07-26 21:26:21.824011-07 32 11 Poa annua subvar. minima 1 Poa annua subvar. minima subvariety 1 (Schur) Asch. & Graebn. http://www.tropicos.org/Name/50158097 \N \N \N \N Poaceae Poa 1 annua 1 subvar. minima 1 \N \N \N \N \N Accepted Poa annua subvar. minima (Schur) Asch. & Graebn. subvariety http://www.tropicos.org/Name/50158097 Poa annua Poaceae true tropicos \N t Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn. \N Poa annua Poa annua subvar. minima Poa annua subvar. minima (Schur) Asch. & Graebn. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua subvar. minima {subvar.,minima} subvar. minima Poa annua subvar. minima (Schur) Asch. & Graebn. accepted subvariety Poaceae Poa annua Poa annua Poa annua subvar. annua Poa annua subvar. minima (Schur) Asch. & Graebn. Poa annua subvar. minima (Schur) Asch. & Graebn. 2014-07-26 21:26:21.824011-07 33 12 Poa annua var. eriolepis 1 Poa annua var. eriolepis var. 1 ̉ۡ.Desv. http://www.theplantlist.org/tpl1.1/record/kew-435206 \N \N \N \N \N Poa 1 annua 1 var. eriolepis 1 \N \N \N \N \N Synonym Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194 Poa annua Poaceae true tpl \N t Poaceae Poa annua L. \N Poa annua Poa annua var. eriolepis Poa annua var. eriolepis ̉ۡ.Desv. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L. 2014-07-26 21:26:21.824011-07 34 12 Poa annua var. eriolepis 1 Poa annua var. eriolepis variety 1 E. Desv. http://www.tropicos.org/Name/50119145 \N \N \N \N Poaceae Poa 1 annua 1 var. eriolepis 1 \N \N \N \N \N Synonym Poa annua L. species http://www.tropicos.org/Name/25509881 Poa annua Poaceae false tropicos \N t Poaceae Poa annua L. \N Poa annua Poa annua var. eriolepis Poa annua var. eriolepis E. Desv. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L. 2014-07-26 21:26:21.824011-07 35 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subsp. 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.theplantlist.org/tpl1.1/record/tro-6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subsp. http://www.theplantlist.org/tpl1.1/record/tro-6303627 Silene scouleri Caryophyllaceae true tpl [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subsp. Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire 2014-07-26 21:26:21.824011-07 36 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae false tropicos [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire 2014-07-26 21:26:21.824011-07 37 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://plants.usda.gov/java/profile?symbol=SISCP \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://plants.usda.gov/java/profile?symbol=SISCP Silene scouleri Caryophyllaceae false usda [Partial match] \N t Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri ssp. pringlei {ssp.,pringlei} ssp. pringlei Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri ssp. scouleri Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire 2014-07-26 21:26:21.824011-07 38 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subsp. 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.theplantlist.org/tpl1.1/record/tro-6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subsp. http://www.theplantlist.org/tpl1.1/record/tro-6303627 Silene scouleri Caryophyllaceae true tpl [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subsp. Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire 2014-07-26 21:26:21.824011-07 39 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae false tropicos [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire 2014-07-26 21:26:21.824011-07 40 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://plants.usda.gov/java/profile?symbol=SISCP \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://plants.usda.gov/java/profile?symbol=SISCP Silene scouleri Caryophyllaceae false usda [Partial match] \N t Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri ssp. pringlei {ssp.,pringlei} ssp. pringlei Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri ssp. scouleri Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire \. -- -- Data for Name: taxon_match_input__copy_to; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY taxon_match_input__copy_to ("Name_number", "Name_submitted", "Overall_score", "Name_matched", "Name_matched_rank", "Name_score", "Name_matched_author", "Name_matched_url", "Author_matched", "Author_score", "Family_matched", "Family_score", "Name_matched_accepted_family", "Genus_matched", "Genus_score", "Specific_epithet_matched", "Specific_epithet_score", "Infraspecific_rank", "Infraspecific_epithet_matched", "Infraspecific_epithet_score", "Infraspecific_rank_2", "Infraspecific_epithet_2_matched", "Infraspecific_epithet_2_score", "Annotations", "Unmatched_terms", "Taxonomic_status", "Accepted_name", "Accepted_name_author", "Accepted_name_rank", "Accepted_name_url", "Accepted_name_species", "Accepted_name_family", "Selected", "Source", "Warnings", "Accepted_name_lsid") FROM stdin; \. -- -- Data for Name: ~Source.map; Type: TABLE DATA; Schema: TNRS; Owner: bien -- COPY "~Source.map" ("from", "to", filter, notes) FROM stdin; row_num *row_num \N \N :aggregator sourceType \N \N :http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html datasetURL \N \N \. -- -- Name: Source_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY "Source" ADD CONSTRAINT "Source_pkey" PRIMARY KEY ("*row_num"); -- -- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY batch_download_settings ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id); -- -- Name: batch_id_by_time_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY batch ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time); -- -- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY batch ADD CONSTRAINT batch_pkey PRIMARY KEY (id); -- -- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY client_version ADD CONSTRAINT client_version_pkey PRIMARY KEY (id); -- -- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY taxon_match ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num); ALTER TABLE taxon_match CLUSTER ON taxon_match_pkey; -- -- Name: ~Source.map_from_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY "~Source.map" ADD CONSTRAINT "~Source.map_from_key" UNIQUE ("from"); -- -- Name: ~Source.map_to_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: -- ALTER TABLE ONLY "~Source.map" ADD CONSTRAINT "~Source.map_to_key" UNIQUE ("to"); -- -- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: -- CREATE INDEX batch_client_version_idx ON batch USING btree (client_version); -- -- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: -- CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match); -- -- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: -- CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted"); -- -- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: -- CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "*Name_number") WHERE ("*Selected" = 'true'::text); -- -- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill(); -- -- Name: map_filter_insert; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER map_filter_insert BEFORE INSERT ON "~Source.map" FOR EACH ROW EXECUTE PROCEDURE util.map_filter_insert(); ALTER TABLE "~Source.map" DISABLE TRIGGER map_filter_insert; -- -- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER taxon_match__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE taxon_match__batch_begin(); -- -- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER taxon_match__fill BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill(); -- -- Name: taxon_match__fill_derived; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER taxon_match__fill_derived BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill_derived(); -- -- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: bien -- CREATE TRIGGER taxon_match__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__match_num__fill(); -- -- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: bien -- 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(); -- -- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien -- ALTER TABLE ONLY batch ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien -- ALTER TABLE ONLY batch_download_settings ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien -- ALTER TABLE ONLY taxon_match ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE; -- -- Name: TNRS; Type: ACL; Schema: -; Owner: bien -- REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC; REVOKE ALL ON SCHEMA "TNRS" FROM bien; GRANT ALL ON SCHEMA "TNRS" TO bien; GRANT USAGE ON SCHEMA "TNRS" TO bien_read; -- -- Name: taxon_match; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE taxon_match FROM PUBLIC; REVOKE ALL ON TABLE taxon_match FROM bien; GRANT ALL ON TABLE taxon_match TO bien; GRANT SELECT ON TABLE taxon_match TO bien_read; -- -- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC; REVOKE ALL ON TABLE taxon_best_match FROM bien; GRANT ALL ON TABLE taxon_best_match TO bien; GRANT SELECT ON TABLE taxon_best_match TO bien_read; -- -- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC; REVOKE ALL ON TABLE "MatchedTaxon" FROM bien; GRANT ALL ON TABLE "MatchedTaxon" TO bien; GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read; -- -- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC; REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien; GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien; GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read; -- -- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE taxon_match_input FROM PUBLIC; REVOKE ALL ON TABLE taxon_match_input FROM bien; GRANT ALL ON TABLE taxon_match_input TO bien; GRANT SELECT ON TABLE taxon_match_input TO bien_read; -- -- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC; REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien; GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien; GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read; -- -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: bien -- REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC; REVOKE ALL ON TABLE taxon_scrub FROM bien; GRANT ALL ON TABLE taxon_scrub TO bien; GRANT SELECT ON TABLE taxon_scrub TO bien_read; -- -- PostgreSQL database dump complete --