--

-- PostgreSQL database dump

--

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

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

--

IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:

# back up existing TNRS schema (in case of an accidental incorrect change):

$ svn di

# make the changes shown in the diff

## to change column types:

  (''col'', ''new_type'')

# repeat until `svn di` shows no diff

# back up new TNRS schema:

';

--

--

    AS $_$

--

--

COMMENT ON FUNCTION "MatchedTaxon_modify"(view_query text) IS '

usage:

SELECT "TNRS"."MatchedTaxon_modify"($$

idempotent

';

--

--

CREATE FUNCTION batch__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

	new.id_by_time = new.time_submitted;

	new.id = COALESCE(new.id, new.id_by_time);

	RETURN new;

END;

--

--

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)

$_$;

--

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)

$_$;

--

    AS $_$

"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)" */

$_$;

--

--

CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

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

$_$;

--

--

    AS $_$

SELECT util.recreate_view('"TNRS".taxon_scrub', $1);

$_$;

--

-- Name: FUNCTION taxon_scrub_modify(view_query text); Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON FUNCTION taxon_scrub_modify(view_query text) IS '

usage:

SELECT "TNRS".taxon_scrub_modify($$

idempotent

';

--

--

CREATE FUNCTION tnrs__batch_begin() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);

	RETURN NULL;

END;

$$;

--

--

CREATE FUNCTION tnrs__match_num__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

		new.match_num = "TNRS".tnrs__match_num__next();

	END IF;

END;

$$;

--

--

CREATE FUNCTION tnrs__match_num__next() RETURNS bigint

    LANGUAGE sql

    AS $$

SELECT nextval('pg_temp.tnrs__match_num__seq');

$$;

--

--

CREATE FUNCTION tnrs_populate_fields() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

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

	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"

		), '');

			new."Accepted_name")

		, new."Accepted_name"

		, new."Accepted_name_author"

		), '');

	a name, but the name is not meaningful because it is not unambiguous). */

		AND COALESCE(CASE

			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)

	RETURN new;

--

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

--

IMPORTANT: when changing this function, you must regenerate the derived cols:

';

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

--

CREATE FUNCTION unsafe_taxon_names() RETURNS text[]

    LANGUAGE sql IMMUTABLE

    AS $$

SELECT ARRAY[

SET default_with_oids = false;

--

    batch text DEFAULT now() 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,

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

--

$ rm=1 inputs/.TNRS/data.sql.run

$ make schemas/remake

to add columns in the middle:

make the changes in inputs/.TNRS/schema.sql

$ inputs/.TNRS/data.sql.run refresh # re-run TNRS

$ make schemas/remake

to populate a new column:

ALTER TABLE "TNRS".tnrs DISABLE TRIGGER tnrs_populate_fields; --speeds up update

UPDATE "TNRS".tnrs SET "col" = value;

	-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")

ALTER TABLE "TNRS".tnrs ENABLE TRIGGER tnrs_populate_fields;

VACUUM ANALYZE "TNRS".tnrs --remove previous rows; runtime: 1.5 min ("92633 ms")

to add a constraint: runtime: 3 min ("173620 ms")

--

-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -

--

CREATE VIEW "MatchedTaxon" AS

    s."concatenatedScientificName",

    s."matchedTaxonName",

    s."matchedTaxonRank",

    s."*Name_matched.Name_score",

    s."matchedScientificNameAuthorship",

    s."matchedScientificNameID",

    s."*Name_matched.Author_score",

    s."matchedFamilyConfidence_fraction",

    s."matchedFamily",

    s."matchedGenus",

    s."matchedGenusConfidence_fraction",

    s."matchedSpecificEpithet",

    s."matchedSpeciesConfidence_fraction",

    s."matchedInfraspecificEpithet",

    s."*Name_matched.Infraspecific_epithet_score",

    s."identificationQualifier",

    s."morphospeciesSuffix",

    s."taxonomicStatus",

    s.accepted_taxon_name_no_author,

    s.accepted_author,

    s.accepted_taxon_rank,

    s."acceptedScientificNameID",

    s.accepted_species_binomial,

    s.accepted_family,

    s."*Name_matched.Selected",

    s."*Name_matched.Source",

    s."*Name_matched.Warnings",

    s."*Name_matched.Accepted_name_lsid",

    s.taxon_scrub__is_valid_match,

    s.scrubbed_unique_taxon_name,

        CASE

            WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")

            WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")

            ELSE s.accepted_species_binomial

        END AS accepted_morphospecies_binomial

   FROM ( SELECT tnrs.batch AS "*Name_matched.batch",

            tnrs."Name_submitted" AS "concatenatedScientificName",

            tnrs."Name_matched" AS "matchedTaxonName",

            tnrs."Name_matched_rank" AS "matchedTaxonRank",

            tnrs."Name_score" AS "*Name_matched.Name_score",

            tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",

            tnrs."Name_matched_url" AS "matchedScientificNameID",

            tnrs."Author_score" AS "*Name_matched.Author_score",

            tnrs."Family_score" AS "matchedFamilyConfidence_fraction",

            COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",

            tnrs."Genus_matched" AS "matchedGenus",

            tnrs."Genus_score" AS "matchedGenusConfidence_fraction",

            tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",

            tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",

            tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",

            tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",

            tnrs."Annotations" AS "identificationQualifier",

            tnrs."Unmatched_terms" AS "morphospeciesSuffix",

            tnrs."Accepted_name_author" AS accepted_author,

            tnrs."Accepted_name_rank" AS accepted_taxon_rank,

            tnrs."Accepted_name_url" AS "acceptedScientificNameID",

            tnrs."Accepted_name_species" AS accepted_species_binomial,

            tnrs."Accepted_name_family" AS accepted_family,

            tnrs."Selected" AS "*Name_matched.Selected",

            tnrs."Source" AS "*Name_matched.Source",

            tnrs."Warnings" AS "*Name_matched.Warnings",

            tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",

            tnrs.is_valid_match AS taxon_scrub__is_valid_match,

            tnrs.scrubbed_unique_taxon_name

           FROM tnrs) s;

--

--

COMMENT ON VIEW "MatchedTaxon" IS '

--

--

CREATE VIEW "ValidMatchedTaxon" AS

    "MatchedTaxon"."concatenatedScientificName",

    "MatchedTaxon"."matchedTaxonName",

    "MatchedTaxon"."matchedTaxonRank",

    "MatchedTaxon"."*Name_matched.Name_score",

    "MatchedTaxon"."matchedScientificNameAuthorship",

    "MatchedTaxon"."matchedScientificNameID",

    "MatchedTaxon"."*Name_matched.Author_score",

    "MatchedTaxon"."matchedFamilyConfidence_fraction",

    "MatchedTaxon"."matchedFamily",

    "MatchedTaxon"."matchedGenus",

    "MatchedTaxon"."matchedGenusConfidence_fraction",

    "MatchedTaxon"."matchedSpecificEpithet",

    "MatchedTaxon"."matchedSpeciesConfidence_fraction",

    "MatchedTaxon"."matchedInfraspecificEpithet",

    "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",

    "MatchedTaxon"."identificationQualifier",

    "MatchedTaxon"."morphospeciesSuffix",

    "MatchedTaxon"."taxonomicStatus",

    "MatchedTaxon".accepted_author,

    "MatchedTaxon".accepted_taxon_rank,

    "MatchedTaxon"."*Name_matched.Source",

    "MatchedTaxon"."*Name_matched.Warnings",

    "MatchedTaxon"."*Name_matched.Accepted_name_lsid",

    "MatchedTaxon".taxon_scrub__is_valid_match,

    "MatchedTaxon".accepted_morphospecies_binomial

  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;

--

-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -

--

to update, use * as the column list

';

--

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

--

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

--

contains svn revisions

';

--

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

--

from `svn info .` > Last Changed Rev

';

--

-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: -

--

from `svn info lib/tnrs.py` > Last Changed Rev

';

--

-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: -

--

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

';

--

--

CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS

 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,

    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family,

    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,

    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,

    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,

    (tnrs."Name_matched" || COALESCE((' '::text || tnrs."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author

--

--

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.

';

--

--

CREATE VIEW taxon_scrub AS

 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,

    "ValidMatchedTaxon"."*Name_matched.batch",

    "ValidMatchedTaxon"."concatenatedScientificName",

    "ValidMatchedTaxon"."matchedTaxonName",

    "ValidMatchedTaxon"."matchedTaxonRank",

    "ValidMatchedTaxon"."*Name_matched.Name_score",

    "ValidMatchedTaxon"."matchedScientificNameAuthorship",

    "ValidMatchedTaxon"."matchedScientificNameID",

    "ValidMatchedTaxon"."*Name_matched.Author_score",

    "ValidMatchedTaxon"."matchedFamilyConfidence_fraction",

    "ValidMatchedTaxon"."matchedFamily",

    "ValidMatchedTaxon"."matchedGenus",

    "ValidMatchedTaxon"."matchedGenusConfidence_fraction",

    "ValidMatchedTaxon"."matchedSpecificEpithet",

    "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",

    "ValidMatchedTaxon"."matchedInfraspecificEpithet",

    "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",

    "ValidMatchedTaxon"."identificationQualifier",

    "ValidMatchedTaxon"."morphospeciesSuffix",

    "ValidMatchedTaxon"."taxonomicStatus",

    "ValidMatchedTaxon".accepted_author,

    "ValidMatchedTaxon".accepted_taxon_rank,

    "ValidMatchedTaxon"."*Name_matched.Source",

    "ValidMatchedTaxon"."*Name_matched.Warnings",

    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",

    "ValidMatchedTaxon".taxon_scrub__is_valid_match,

    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,

    "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,

            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"."morphospeciesSuffix")

            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"."morphospeciesSuffix")

            ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)

        END AS scrubbed_morphospecies_binomial

   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));

--

-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -

--

SELECT "TNRS".taxon_scrub_modify($$

--

--

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

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);

--

-- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_pkey PRIMARY KEY (id);

--

-- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:

--

ALTER TABLE ONLY client_version

    ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);

--

-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:

--

ALTER TABLE ONLY tnrs

--

--

CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);

--

--

CREATE INDEX "tnrs_Name_submitted_idx" ON tnrs USING btree ("Name_submitted");

--

--

--

--

CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();

--

--

CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON tnrs FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();

--

--

CREATE TRIGGER tnrs__match_num__fill BEFORE INSERT ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs__match_num__fill();

--

--

CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();

--

-- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -

--

ALTER TABLE ONLY batch

    ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;