-- Name: iucn_red_list_view; Type: VIEW; Schema: public; Owner: -

--

--

-- Name: VIEW iucn_red_list_view; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxondetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxondetermination (

    taxondetermination_id int(11) NOT NULL,

    taxonoccurrence_id int(11) NOT NULL,

    source_id int(11) NOT NULL,

    taxonverbatim_id int(11) NOT NULL,

    party_id int(11),

    role varchar(255) DEFAULT 'unknown' NOT NULL,

    determinationtype varchar(255),

    reference_id int(11),

    isoriginal int(1),

    iscurrent__verbatim int(1),

    iscurrent int(1) DEFAULT false NOT NULL,

    taxonfit varchar(255),

    taxonconfidence varchar(255),

    grouptype varchar(255),

    notes varchar(255),

    revisions int(1),

    determinationdate date

);

--

-- Name: TABLE taxondetermination; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: taxonverbatim; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonverbatim (

    taxonverbatim_id int(11) NOT NULL,

    source_id int(11) NOT NULL,

    taxonlabel_id int(11),

    verbatimrank varchar(255),

    taxonomicname varchar(255),

    taxonname varchar(255),

    author varchar(255),

    family varchar(255),

    genus varchar(255),

    specific_epithet varchar(255),

    subspecies varchar(255),

    morphospecies varchar(255),

    morphoname varchar(255),

    growthform varchar(255),

    description varchar(255)

);

--

-- Name: TABLE taxonverbatim; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.taxonname; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.family; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.genus; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.specific_epithet; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN taxonverbatim.morphospecies; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE trait (

    trait_id int(11) NOT NULL,

    taxonoccurrence_id int(11) NOT NULL,

    name varchar(255) NOT NULL,

    value varchar(255),

    units varchar(255)

);

--

-- Name: taxon_trait_view; Type: VIEW; Schema: public; Owner: -

--

--

-- Name: taxonverbatim_taxonverbatim_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

-- Name: taxonverbatim_taxonverbatim_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

--

-- Name: telephone; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE telephone (

    telephone_id int(11) NOT NULL,

    party_id int(11) NOT NULL,

    phonenumber varchar(255) NOT NULL,

    phonetype varchar(255) NOT NULL

-- Name: telephone_telephone_id_seq; Type: SEQUENCE; Schema: public; Owner: -

-- Name: telephone_telephone_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: tnrs_input_name; Type: VIEW; Schema: public; Owner: -

-- Name: trait_trait_id_seq; Type: SEQUENCE; Schema: public; Owner: -

-- Name: trait_trait_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: userdefined; Type: TABLE; Schema: public; Owner: -; Tablespace: 

CREATE TABLE userdefined (

    userdefined_id int(11) NOT NULL,

    userdefinedname varchar(255) NOT NULL,

    userdefinedmetadata varchar(255),

    userdefinedcategory varchar(255),

    userdefinedtype varchar(255) DEFAULT 'varchar(255)' NOT NULL,

    tablename varchar(255) NOT NULL

);

--

-- Name: userdefined_userdefined_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

-- Name: userdefined_userdefined_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: view_full_occurrence_CVS; Type: VIEW; Schema: public; Owner: -

-- Name: VIEW `view_full_occurrence_CVS`; Type: COMMENT; Schema: public; Owner: -

-- Name: view_full_occurrence_NCU; Type: VIEW; Schema: public; Owner: -

-- Name: VIEW `view_full_occurrence_NCU`; Type: COMMENT; Schema: public; Owner: -

-- Name: view_full_occurrence_VegBank; Type: VIEW; Schema: public; Owner: -

-- Name: VIEW `view_full_occurrence_VegBank`; Type: COMMENT; Schema: public; Owner: -

-- Name: view_full_occurrence_individual_GBIF; Type: VIEW; Schema: public; Owner: -

-- Name: view_full_occurrence_individual__new_taxonobservation_id_seq1; Type: SEQUENCE; Schema: public; Owner: -

-- Name: view_full_occurrence_individual__new_taxonobservation_id_seq1; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

-- Name: view_full_occurrence_individual_non_GBIF; Type: VIEW; Schema: public; Owner: -

-- Name: view_full_occurrence_individual_view; Type: VIEW; Schema: public; Owner: -

-- Name: VIEW view_full_occurrence_individual_view; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.datasource; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.country; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.state_province; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.county; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.locality; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.latitude; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.longitude; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.coord_uncertainty_m; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.georef_sources; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.georef_protocol; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.is_geovalid; Type: COMMENT; Schema: public; Owner: -

-- Name: COLUMN view_full_occurrence_individual_view.is_new_world; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.project_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.project_contributors; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.location_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.plot_name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.subplot; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.is_location_cultivated; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.locationevent_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.event_date; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.elevation_m; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.slope_aspect_deg; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.slope_gradient_deg; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.plot_area_ha; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.sampling_protocol; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.temperature_c; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.precip_mm; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.stratum_name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.community_concept_name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.observation_contributors; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.custodial_institution_codes; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.collection_code; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.catalog_number; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.occurrence_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.recorded_by; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.record_number; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.date_collected; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.verbatim_family; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.verbatim_scientific_name; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.identified_by; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.date_identified; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.identification_remarks; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.family_matched; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.name_matched; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.name_matched_author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.higher_plant_group; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.taxonomic_status; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_family; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_genus; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_specific_epithet; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_no_author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_with_author; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial_with_morphospecies; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.growth_form; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.reproductive_condition; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.is_cultivated; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.is_cultivated_basis; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.occurrence_remarks; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.cover_percent; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.taxon_observation_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.taxon_name_usage_concept_author_code; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.aggregate_organism_observation_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_organism_observation_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_count; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN view_full_occurrence_individual_view.plantobservation_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: _plots_20_tnrs_names; Type: VIEW; Schema: public_validations; Owner: -

--

--

-- Name: VIEW _plots_20_tnrs_names; Type: COMMENT; Schema: public_validations; Owner: -

--

--

-- Name: taxonlabel_taxonomicname_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

--

-- Name: iucn_red_list_view; Type: ACL; Schema: public; Owner: -

--

--

-- Name: taxondetermination; Type: ACL; Schema: public; Owner: -

-- Name: taxonverbatim; Type: ACL; Schema: public; Owner: -

-- Name: trait; Type: ACL; Schema: public; Owner: -

-- Name: taxon_trait_view; Type: ACL; Schema: public; Owner: -

-- Name: taxonalt; Type: ACL; Schema: public; Owner: -

-- Name: taxonconcept; Type: ACL; Schema: public; Owner: -

-- Name: taxoncorrelation; Type: ACL; Schema: public; Owner: -

-- Name: taxonlabel_relationship; Type: ACL; Schema: public; Owner: -

-- Name: taxonlineage; Type: ACL; Schema: public; Owner: -

--

--

-- Name: taxonstatus; Type: ACL; Schema: public; Owner: -

--

--

-- Name: tnrs_input_name; Type: ACL; Schema: public; Owner: -

-- Name: view_full_occurrence_individual_view; Type: ACL; Schema: public; Owner: -

--

--

-- Name: iucn_red_list_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW iucn_red_list_view AS

 SELECT DISTINCT taxon_scrub."*Accepted_name_family" AS accepted_family,

    taxon_scrub."*Accepted_name_species" AS accepted_species_binomial

   FROM (taxonlabel

   JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

  WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND (taxon_scrub."*Accepted_name_family" IS NOT NULL)) AND (taxon_scrub."*Accepted_name_species" IS NOT NULL));

--

-- Name: VIEW iucn_red_list_view; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON VIEW iucn_red_list_view IS '

after updating this:

SELECT iucn_red_list_view_modify();

';

--

-- Name: taxondetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxondetermination (

    taxondetermination_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    source_id integer NOT NULL,

    taxonverbatim_id integer NOT NULL,

    party_id integer,

    role role DEFAULT 'unknown'::role NOT NULL,

    determinationtype text,

    reference_id integer,

    isoriginal boolean,

    iscurrent__verbatim boolean,

    iscurrent boolean DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    revisions boolean,

    determinationdate date

);

--

-- Name: TABLE taxondetermination; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE taxondetermination IS '

Equivalent to VegBank''s taxoninterpretation table.

';

--

-- Name: taxonverbatim; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonverbatim (

    taxonverbatim_id integer NOT NULL,

    source_id integer NOT NULL,

    taxonlabel_id integer,

    verbatimrank text,

    taxonomicname text,

    taxonname text,

    author text,

    family text,

    genus text,

    specific_epithet text,

    subspecies text,

    morphospecies text,

    morphoname text,

    growthform growthform,

    description text,

    CONSTRAINT taxonverbatim_required_key CHECK (((taxonlabel_id IS NOT NULL) OR (morphoname IS NOT NULL)))

);

--

-- Name: TABLE taxonverbatim; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE taxonverbatim IS '

Component parts of the taxonlabel. Contains the datasource''s original taxonomic name components, as well as any parsed components produced by name resolution.

';

--

-- Name: COLUMN taxonverbatim.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.verbatimrank IS '

The taxon''s verbatim level in the taxonomic hierarchy. Does not need to be in the taxonrank closed list.

';

--

-- Name: COLUMN taxonverbatim.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.taxonomicname IS '

The full taxonomic name which uniquely identifies this taxon, including the author of that name. The family should be omitted if possible.

Equivalent to Darwin Core''s scientificName.

';

--

-- Name: COLUMN taxonverbatim.taxonname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.taxonname IS '

The taxonomic name without the author. The family should be omitted if possible.

';

--

-- Name: COLUMN taxonverbatim.author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.author IS '

The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.

';

--

-- Name: COLUMN taxonverbatim.family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.family IS '

The family of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.genus; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.genus IS '

The genus portion of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.specific_epithet; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.specific_epithet IS '

The specific epithet portion of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.morphospecies; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.morphospecies IS '

The morphospecies suffix.

';

--

-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE trait (

    trait_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    name text NOT NULL,

    value text,

    units text

);

--

-- Name: taxon_trait_view; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW taxon_trait_view AS

 SELECT taxon_scrub."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" AS "scientificName",

    trait.name AS "measurementType",

    trait.value AS "measurementValue",

    trait.units AS "measurementUnit",

    "plot.**".country,

    "plot.**".state_province,

    "plot.**".county,

    "plot.**".locality,

    "plot.**".latitude,

    "plot.**".longitude,

    "plot.**".coord_uncertainty_m

   FROM ((((((trait

   LEFT JOIN taxonoccurrence USING (taxonoccurrence_id))

   LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent)))

   LEFT JOIN taxonverbatim USING (taxonverbatim_id))

   LEFT JOIN taxonlabel USING (taxonlabel_id))

   LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

   LEFT JOIN "plot.**" USING (locationevent_id))

  WHERE (taxon_scrub."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS NOT NULL);

--