--

-- PostgreSQL database dump

--

SET statement_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";

SET search_path = "TNRS", pg_catalog;

--

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

--

CREATE FUNCTION batch__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

$$;

-- 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: score_ok(double precision); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION score_ok(score double precision) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1 >= 0.8

$_$;

--

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

--

CREATE FUNCTION tnrs_populate_fields() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

$$;

--

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

--

SET default_tablespace = '';

SET default_with_oids = false;

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

--

CREATE TABLE tnrs (

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

    "Accepted_scientific_name" text,

    "Max_score" double precision,

    "Is_homonym" boolean,

    "Is_plant" boolean

);

--

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

--

COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you must regenerate data.sql:

$ <this_file>/../test_taxonomic_names/test_scrub

you must also make the same changes on vegbiendev: e.g.

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")

SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[

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

]::util.col_cast[]);

runtime: 9 min ("531282 ms")';

--

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

--

CREATE VIEW "AcceptedTaxon" AS

    SELECT tnrs.batch AS "*Accepted_name.batch", tnrs."Name_submitted" AS "acceptedScientificName", tnrs."Genus_matched" AS "acceptedGenus", tnrs."Specific_epithet_matched" AS "acceptedSpecificEpithet", tnrs."Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet", tnrs."Taxonomic_status" AS "acceptedTaxonomicStatus", tnrs."Selected" AS "*Accepted_name.Selected", tnrs."Source" AS "*Accepted_name.Source", tnrs."Warnings" AS "*Accepted_name.Warnings", tnrs."Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid" FROM tnrs;

--

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

--

CREATE VIEW "MatchedTaxon" AS

    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."Taxonomic_status" AS "taxonomicStatus", tnrs."Accepted_name" AS "acceptedTaxonName", tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", tnrs."Accepted_name_rank" AS "acceptedTaxonRank", tnrs."Accepted_name_url" AS "acceptedScientificNameID", tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", tnrs."Accepted_name_family" AS "acceptedFamily", 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."Accepted_scientific_name" AS "acceptedScientificName", tnrs."Max_score" AS "matchedTaxonConfidence_fraction" FROM tnrs;

--

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

--

CREATE VIEW "ValidMatchedTaxon" AS

    SELECT "MatchedTaxon"."*Name_matched.batch", "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"."acceptedTaxonName", "MatchedTaxon"."acceptedScientificNameAuthorship", "MatchedTaxon"."acceptedTaxonRank", "MatchedTaxon"."acceptedScientificNameID", "MatchedTaxon"."*Name_matched.Accepted_name_species", "MatchedTaxon"."acceptedFamily", "MatchedTaxon"."*Name_matched.Selected", "MatchedTaxon"."*Name_matched.Source", "MatchedTaxon"."*Name_matched.Warnings", "MatchedTaxon"."*Name_matched.Accepted_name_lsid", "MatchedTaxon"."acceptedScientificName", "MatchedTaxon"."matchedTaxonConfidence_fraction" FROM "MatchedTaxon" WHERE score_ok("MatchedTaxon"."matchedTaxonConfidence_fraction");

--

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

--

COMMENT ON VIEW "ValidMatchedTaxon" IS 'to update, use * as the column list';

--

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

--

CREATE VIEW "ScrubbedTaxon" AS

    SELECT "ValidMatchedTaxon"."acceptedScientificName", "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"."acceptedTaxonName", "ValidMatchedTaxon"."acceptedScientificNameAuthorship", "ValidMatchedTaxon"."acceptedTaxonRank", "ValidMatchedTaxon"."acceptedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Accepted_name_species", "ValidMatchedTaxon"."acceptedFamily", "ValidMatchedTaxon"."*Name_matched.Selected", "ValidMatchedTaxon"."*Name_matched.Source", "ValidMatchedTaxon"."*Name_matched.Warnings", "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", "ValidMatchedTaxon"."matchedTaxonConfidence_fraction", "AcceptedTaxon"."*Accepted_name.batch", "AcceptedTaxon"."acceptedGenus", "AcceptedTaxon"."acceptedSpecificEpithet", "AcceptedTaxon"."acceptedInfraspecificEpithet", "AcceptedTaxon"."acceptedTaxonomicStatus", "AcceptedTaxon"."*Accepted_name.Selected", "AcceptedTaxon"."*Accepted_name.Source", "AcceptedTaxon"."*Accepted_name.Warnings", "AcceptedTaxon"."*Accepted_name.Accepted_name_lsid" FROM ("ValidMatchedTaxon" NATURAL LEFT JOIN "AcceptedTaxon");

--

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

--

COMMENT ON VIEW "ScrubbedTaxon" IS 'to update, use * as the column list';

--

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

--

CREATE TABLE "Source" (

    "*row_num" integer NOT NULL,

    "sourceType" text DEFAULT 'aggregator'::text NOT NULL,

    "datasetURL" text DEFAULT 'http://tnrs.iplantcollaborative.org/TNRSapp.html'::text NOT NULL

);

--

-- Name: COLUMN "Source"."sourceType"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN "Source"."sourceType" IS 'constant';

--

-- Name: COLUMN "Source"."datasetURL"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN "Source"."datasetURL" IS 'constant';

--

-- 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: ~Source.map; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: 

--

CREATE TABLE "~Source.map" (

    "from" text NOT NULL,

    "to" text,

    filter text,

    notes text

);

--

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

--

ALTER TABLE ONLY "Source"

    ADD CONSTRAINT "Source_pkey" PRIMARY KEY ("*row_num");

--

-- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; 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: -; 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_Name_submitted_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace: 

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted");

--

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

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, "Name_number");

--

-- Name: ~Source.map_from_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace: 

--

ALTER TABLE ONLY "~Source.map"

    ADD CONSTRAINT "~Source.map_from_key" UNIQUE ("from");

--

-- Name: ~Source.map_to_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace: 

--

ALTER TABLE ONLY "~Source.map"

    ADD CONSTRAINT "~Source.map_to_key" UNIQUE ("to");

--

-- Name: tnrs_score_ok; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: 

--

CREATE UNIQUE INDEX tnrs_score_ok ON tnrs USING btree ("Name_submitted") WHERE score_ok("Max_score");

--

-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: -

--

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

--

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: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -

--

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;

--

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

--

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: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -

--

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: TNRS; Type: ACL; Schema: -; Owner: -

--

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: tnrs; Type: ACL; Schema: TNRS; Owner: -

--

REVOKE ALL ON TABLE tnrs FROM PUBLIC;

REVOKE ALL ON TABLE tnrs FROM bien;

GRANT ALL ON TABLE tnrs TO bien;

GRANT SELECT ON TABLE tnrs TO bien_read;

--

-- PostgreSQL database dump complete

--