CREATE VIEW analytical_db_view AS

    SELECT datasource.organizationname AS `dataSourceName`, accepted_taxonverbatim.family, accepted_taxonverbatim.genus, accepted_taxonverbatim.species, COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS taxon, accepted_taxonverbatim.author AS `taxonAuthor`, NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], CAST(' ' AS text)), CAST('' AS text)) AS `taxonMorphospecies`, placepath.country, placepath.stateprovince AS `stateProvince`, placepath.county AS `countyParish`, taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS `collectionNumber`, NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], CAST(' ' AS text)), CAST('' AS text)) AS `identifiedBy`, aggregateoccurrence.collectiondate AS `observationDate`, location.sourceaccessioncode 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`, taxonoccurrence.growthform, stemobservation.xposition_m, stemobservation.yposition_m FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) 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 taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (datasource.organizationname IS NOT NULL);

CREATE VIEW plot_change_over_time AS

    SELECT analytical_db_view.`dataSourceName`, analytical_db_view.family, analytical_db_view.genus, analytical_db_view.species, analytical_db_view.taxon, analytical_db_view.`taxonAuthor`, analytical_db_view.`taxonMorphospecies`, analytical_db_view.country, analytical_db_view.`stateProvince`, analytical_db_view.`countyParish`, analytical_db_view.collector, analytical_db_view.`collectionNumber`, analytical_db_view.`identifiedBy`, analytical_db_view.`observationDate`, analytical_db_view.`plotCode`, analytical_db_view.`plotAreaHa`, analytical_db_view.`plotMethod`, analytical_db_view.latitude, analytical_db_view.longitude, analytical_db_view.elevation_m, analytical_db_view.`isCultivated`, analytical_db_view.`isCultivatedReason`, analytical_db_view.`pctCover`, analytical_db_view.growthform, analytical_db_view.xposition_m, analytical_db_view.yposition_m FROM analytical_db_view WHERE ((analytical_db_view.`dataSourceName` = CAST('CTFS' AS text)) AND (analytical_db_view.xposition_m IS NOT NULL));

CREATE VIEW tnrs_input_name AS

    SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN `TNRS`.tnrs ON ((tnrs.`Name_submitted` = taxonlabel.taxonomicname))) WHERE (((NOT (taxonlabel.taxonomicname IS NULL)) AND (taxonlabel.matched_label_id IS NULL)) AND (tnrs.`Name_submitted` IS NULL)) ORDER BY taxonlabel.taxonomicname;

"^(?:CREATE|ALTER) (?:AGGREGATE|PROCEDURAL LANGUAGE|SEQUENCE|(?:CONSTRAINT )?TRIGGER|TYPE)\b[^;]*;",,