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(),

      REFERENCES client_version (id) MATCH SIMPLE

  CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time )

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 batch_download_settings_id_fkey FOREIGN KEY (id)

      ON UPDATE CASCADE ON DELETE CASCADE

WITH (

  OIDS=FALSE

);

--

  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$

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

  CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch)

      REFERENCES batch (id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

WITH (

  OIDS=FALSE

);

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

  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;

UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"

runtime: 25 min ("5221748 rows affected, 1524228 ms execution time")

VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows

runtime: 1 min ("62350 ms")';

  ON tnrs

  FOR EACH ROW

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

;

  IS 'to update, use * as the column list';

SELECT

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

;

  IS 'to update, use * as the column list';