-- Name: taxonlabel_2_propagate_accepted_label_id(); Type: FUNCTION; Schema: public; Owner: -

    accepted_label_id int(11),

-- Name: COLUMN taxonlabel.accepted_label_id; Type: COMMENT; Schema: public; Owner: -

    SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonlabel.family, accepted_taxonlabel.genus, accepted_taxonlabel.species, COALESCE(accepted_taxonlabel.taxonomicnamewithauthor, accepted_taxonlabel.taxonomicname) AS taxon, accepted_taxonlabel.author AS `taxonAuthor`, accepted_taxonlabel.taxonepithet AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.authorlocationcode AS `plotCode`, functions._m2_to_ha(location.area_m2) AS `plotAreaHa`, method.name AS `plotMethod`, locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS `isCultivated`, taxonoccurrence.cultivatedbasis AS `isCultivatedReason`, _fraction_to_percent(aggregateoccurrence.cover_fraction) AS `pctCover` FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.accepted_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

-- Name: taxonlabel_2_propagate_accepted_label_id; Type: TRIGGER; Schema: public; Owner: -

-- Name: taxonlabel_accepted_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT taxonlabel_accepted_label_id_fkey FOREIGN KEY (accepted_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;

-- Name: taxonlabel_2_propagate_accepted_label_id(); Type: FUNCTION; Schema: public; Owner: -

CREATE FUNCTION taxonlabel_2_propagate_accepted_label_id() RETURNS trigger

            new.accepted_label_id = new.taxonlabel_id;-- make self-reference

            new.accepted_label_id = (

                SELECT accepted_label_id

        -- Update accepted_label_id on labels that resolve to this label

        SET accepted_label_id = new.accepted_label_id

    accepted_label_id integer,

-- Name: COLUMN taxonlabel.accepted_label_id; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonlabel.accepted_label_id IS 'The accepted synonym of the taxonlabel.';

    SELECT datasource.organizationname AS "dataSourceName", accepted_taxonlabel.family, accepted_taxonlabel.genus, accepted_taxonlabel.species, COALESCE(accepted_taxonlabel.taxonomicnamewithauthor, accepted_taxonlabel.taxonomicname) AS taxon, accepted_taxonlabel.author AS "taxonAuthor", accepted_taxonlabel.taxonepithet AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.accepted_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

-- Name: taxonlabel_2_propagate_accepted_label_id; Type: TRIGGER; Schema: public; Owner: -

CREATE TRIGGER taxonlabel_2_propagate_accepted_label_id BEFORE INSERT OR UPDATE ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_propagate_accepted_label_id();

-- Name: taxonlabel_accepted_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

    ADD CONSTRAINT taxonlabel_accepted_label_id_fkey FOREIGN KEY (accepted_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;