CREATE TABLE client_version

(

  id text NOT NULL,

  "/lib/tnrs.py rev" integer, -- from `svn info lib/tnrs.py` > Last Changed Rev

  "/bin/tnrs_db rev" integer, -- from `svn info bin/tnrs_db` > Last Changed Rev

)

WITH (

  OIDS=FALSE

);

  IS 'contains svn revisions';

COMMENT ON COLUMN client_version.global_rev IS 'from `svn info .` > Last Changed Rev';

COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS 'from `svn info lib/tnrs.py` > Last Changed Rev';

COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS 'from `svn info bin/tnrs_db` > Last Changed Rev';

--

  id_by_time text,

  time_submitted timestamp with time zone DEFAULT now(),

  CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version)

      REFERENCES client_version (id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

WITH (

  OIDS=FALSE

);

$BODY$

BEGIN

	new.id_by_time = new.time_submitted;

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

	RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

--

(

  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,

  CONSTRAINT download_settings_pkey PRIMARY KEY (id )

)

WITH (

  OIDS=FALSE

);

COMMENT ON TABLE download_settings

  IS 'stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt';

--

  RETURNS boolean AS

$BODY$

SELECT $1 >= 0.8

$BODY$

  RETURNS boolean AS

$BODY$

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

$BODY$

CREATE OR REPLACE FUNCTION genus_is_homonym(genus text)

  RETURNS boolean AS

$BODY$

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

$BODY$

$ <this_file>/../test_taxonomic_names/test_scrub

  ('col', 'new_type')

]::util.col_cast[]);

runtime: 4 min ("242100 ms")

  "Name_matched_rank" text,

  "Name_matched_url" text,

  "Author_matched" text,

  "Infraspecific_epithet_matched" text,

  "Infraspecific_epithet_2_matched" text,

  "Unmatched_terms" text,

  "Taxonomic_status" text,

  "Accepted_name" text,

  "Accepted_name_author" text,

  "Accepted_name_rank" text,

  "Accepted_name_url" text,

  "Accepted_name_family" text,

  "Source" text,

  "Warnings" text,

  "Accepted_name_lsid" text,

WITH (

  OIDS=FALSE

);

  ON tnrs

  USING btree

  ("Name_submitted" )

  WHERE score_ok("Max_score");

$BODY$

    "Specific_epithet_is_plant" boolean :=

        (CASE

        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL

            OR new."Infraspecific_epithet_2_matched" IS NOT NULL

        ELSE NULL -- ambiguous

        END);

    genus_is_homonym  boolean = NOT never_homonym AND "TNRS".genus_is_homonym( new."Genus_matched" );

        , new."Accepted_name_author"

        , new."Family_score"

        , new."Genus_score"

        , new."Specific_epithet_score"

            THEN true

        ELSE -- consider genus

                (CASE

                WHEN NOT genus_is_homonym THEN true

                ELSE "Specific_epithet_is_plant"

                END)

            ELSE NULL -- ambiguous

            END)

        END);

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

  ON tnrs

  FOR EACH ROW

  "Time_submitted" AS "*Name_matched.Time_submitted"

, "Name_score" AS "*Name_matched.Name_score"

, "Name_matched_author" AS "matchedScientificNameAuthorship"

, "Name_matched_url" AS "matchedScientificNameID"

, "Author_score" AS "*Name_matched.Author_score"

, "Family_score" AS "matchedFamilyConfidence_fraction"

, "Specific_epithet_matched" AS "matchedSpecificEpithet"

, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"

, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"

, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"

, "Annotations" AS "identificationQualifier"

, "Unmatched_terms" AS "morphospeciesSuffix"

, "Accepted_name_author" AS "acceptedScientificNameAuthorship"

, "Accepted_name_rank" AS "acceptedTaxonRank"

, "Accepted_name_url" AS "acceptedScientificNameID"

, "Accepted_name_family" AS "acceptedFamily"

, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"

, "Accepted_scientific_name" AS "acceptedScientificName"

, "Max_score" AS "matchedTaxonConfidence_fraction"

;

SELECT *

FROM "MatchedTaxon"

WHERE score_ok("matchedTaxonConfidence_fraction")

;

SELECT

  "Time_submitted" AS "*Accepted_name.Time_submitted"

, "Name_submitted" AS "acceptedScientificName"

, "Genus_matched" AS "acceptedGenus"

, "Specific_epithet_matched" AS "acceptedSpecificEpithet"

, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"

, "Taxonomic_status" AS "acceptedTaxonomicStatus"

, "Selected" AS "*Accepted_name.Selected"

, "Source" AS "*Accepted_name.Source"

, "Warnings" AS "*Accepted_name.Warnings"

, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"

;

CREATE OR REPLACE VIEW "ScrubbedTaxon" AS

SELECT *

;