--

-- 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: -

--

--CREATE SCHEMA "TNRS";

--

-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -

--

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: -

--

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;

$$;

--

-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -

--

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)

$_$;

--

-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -

--

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)

$_$;

--

-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -

--

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

$_$;

--

-- Name: remove_prefix(text, text, boolean, boolean); Type: FUNCTION; Schema: TNRS; Owner: -

--

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)

$$;

--

-- Name: FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean); Type: COMMENT; Schema: TNRS; Owner: -

--

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: -

--

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;

$$;

--

-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -

--

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;

$$;

--

-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: -

--

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: -

--

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_species[_binomial]__@TNRS__@vegpath.org" = NULL;

	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = NULL;

	new."[accepted_]genus__@DwC__@vegpath.org" = NULL;

	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;

	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = 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_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);

	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = (SELECT regexp_split_to_array("*Accepted_name", ' '::text) FROM (SELECT new.*) new);

	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1] FROM (SELECT new.*) new);

	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2] 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_species[_binomial]__@TNRS__@vegpath.org"

END FROM (SELECT new.*) new);

	new."__accepted_infraspecific_{rank,epithet}" = (SELECT NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::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_species[_binomial]__@TNRS__@vegpath.org"

    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;

$$;

--

-- Name: FUNCTION taxon_match__fill_derived(); Type: COMMENT; Schema: TNRS; Owner: -

--

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: -

--

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;

$$;

--

-- Name: taxon_match__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION taxon_match__match_num__next() RETURNS bigint

    LANGUAGE sql

    AS $$

SELECT nextval('pg_temp.taxon_match__match_num__seq');

$$;

--

-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -

--

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;

$$;

--

-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))

$_$;

--

-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION unsafe_taxon_names() RETURNS text[]

    LANGUAGE sql IMMUTABLE

    AS $$

SELECT ARRAY[

]::text[]

$$;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: 

--

CREATE TABLE batch (

    id text NOT NULL,

    id_by_time text,

    time_submitted timestamp with time zone DEFAULT now(),

    client_version text

);

--

-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; 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

);

--

-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: -

--

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: -; Tablespace: 

--

CREATE TABLE client_version (

    id text NOT NULL,

    global_rev integer NOT NULL,

    "/lib/tnrs.py rev" integer,

    "/bin/tnrs_db rev" integer

);

--

-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON TABLE client_version IS '

contains svn revisions

';

--

-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: -

--

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: -

--

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: -

--

COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS '

from `svn info bin/tnrs_db` > Last Changed Rev

';

--

-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; 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,infra_{rank,epithet}}" text[],

    "[accepted_]genus__@DwC__@vegpath.org" text,

    "[accepted_]specificEpithet__@DwC__@vegpath.org" text,

    "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" 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 "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]))),

    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_species[_binomial]__@TNRS__@vegpath.org"

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,infra_{rank,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_species[_binomial]__@TNRS__@vegpath.org"

    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_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[])))),

    CONSTRAINT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" CHECK ((NOT ("__accepted_{genus,specific_epithet,infra_{rank,epithet}}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name", ' '::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))))

);

--

-- Name: TABLE taxon_match; Type: COMMENT; Schema: TNRS; Owner: -

--

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: 6 h, 2.6 ms/row ("22030570 ms"; "rows=8353235")

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 derived column:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''col''));

$ make schemas/remake

to remove a non-derived column:

SELECT util.drop_column((''"TNRS".taxon_match'', ''col''));

$ 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; runtime: 1 min ("1m2.629s")

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: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match.matched_has_accepted; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''Accepted_species[_binomial]__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet,infra_{rank,epithet}}"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" IS '

= regexp_split_to_array("*Accepted_name", '' ''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet,infra_{rank,epithet}}'')::util.col, $$regexp_split_to_array("*Accepted_name", '' ''::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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet,infra_{rank,epithet}}'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS '

= "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet,infra_{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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS '

= "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2]

derived column