individualplant_id integer NOT NULL

    covercode character varying(10)

    stemcount_id integer NOT NULL,

    stemcode character varying(20),

    stemxposition double precision,

    stemyposition double precision,

    stemhealth character varying(50),

    emb_stemlocation integer

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

CREATE TABLE stemcount (

    stemcount_id integer NOT NULL,

    collectiveobservation_id integer NOT NULL,

    stemdiameter double precision,

    stemdiameteraccuracy double precision,

    stemheight double precision,

    stemheightaccuracy double precision,

    stemcount integer NOT NULL,

    stemtaxonarea double precision,

    emb_stemcount integer

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

--

CREATE SEQUENCE stemcount_stemcount_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

--

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

--

ALTER SEQUENCE stemcount_stemcount_id_seq OWNED BY stemcount.stemcount_id;

--

ALTER SEQUENCE stemlocation_stemlocation_id_seq OWNED BY individualplant.individualplant_id;

    individualplant_id integer,

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

--

CREATE VIEW view_all_commnames_code AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Code'::text);

--

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

--

CREATE VIEW view_all_commnames_common AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Common'::text);

--

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

--

CREATE VIEW view_all_commnames_sciname AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Scientific'::text);

--

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

--

CREATE VIEW view_all_commnames_translated AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Translated'::text);

--

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

--

CREATE VIEW view_all_plantnames_code AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Code'::text);

--

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

--

CREATE VIEW view_all_plantnames_common AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'English Common'::text);

--

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

--

CREATE VIEW view_all_plantnames_sciname AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Scientific'::text);

--

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

--

CREATE VIEW view_all_plantnames_scinamenoauth AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Scientific without authors'::text);

--

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

--

CREATE VIEW view_notemb_observation AS

    SELECT plotevent.plotevent_id, plotevent.previousobs_id, plotevent.plot_id, plotevent.project_id, plotevent.authorobscode, plotevent.dateaccuracy, plotevent.covermethod_id, plotevent.coverdispersion, plotevent.autotaxoncover, plotevent.stratummethod_id, plotevent.methodnarrative, plotevent.taxonobservationarea, plotevent.stemsizelimit, plotevent.stemobservationarea, plotevent.stemsamplemethod, plotevent.originaldata, plotevent.effortlevel, plotevent.plotvalidationlevel, plotevent.floristicquality, plotevent.bryophytequality, plotevent.lichenquality, plotevent.observationnarrative, plotevent.landscapenarrative, plotevent.homogeneity, plotevent.phenologicaspect, plotevent.representativeness, plotevent.standmaturity, plotevent.successionalstatus, plotevent.basalarea, plotevent.hydrologicregime, plotevent.soilmoistureregime, plotevent.soildrainage, plotevent.watersalinity, plotevent.waterdepth, plotevent.shoredistance, plotevent.soildepth, plotevent.organicdepth, plotevent.soiltaxon_id, plotevent.soiltaxonsrc, plotevent.percentbedrock, plotevent.percentrockgravel, plotevent.percentwood, plotevent.percentlitter, plotevent.percentbaresoil, plotevent.percentwater, plotevent.percentother, plotevent.nameother, plotevent.treeht, plotevent.shrubht, plotevent.fieldht, plotevent.nonvascularht, plotevent.submergedht, plotevent.treecover, plotevent.shrubcover, plotevent.fieldcover, plotevent.nonvascularcover, plotevent.floatingcover, plotevent.submergedcover, plotevent.dominantstratum, plotevent.growthform1type, plotevent.growthform2type, plotevent.growthform3type, plotevent.growthform1cover, plotevent.growthform2cover, plotevent.growthform3cover, plotevent.totalcover, plotevent.notespublic, plotevent.notesmgt, plotevent.revisions, plotevent.obsstartdate, plotevent.obsenddate, plotevent.dateentered, plotevent.emb_observation, plotevent.interp_orig_ci_id, plotevent.interp_orig_cc_id, plotevent.interp_orig_sciname, plotevent.interp_orig_code, plotevent.interp_orig_party_id, plotevent.interp_orig_partyname, plotevent.interp_current_ci_id, plotevent.interp_current_cc_id, plotevent.interp_current_sciname, plotevent.interp_current_code, plotevent.interp_current_party_id, plotevent.interp_current_partyname, plotevent.interp_bestfit_ci_id, plotevent.interp_bestfit_cc_id, plotevent.interp_bestfit_sciname, plotevent.interp_bestfit_code, plotevent.interp_bestfit_party_id, plotevent.interp_bestfit_partyname, plotevent.toptaxon1name, plotevent.toptaxon2name, plotevent.toptaxon3name, plotevent.toptaxon4name, plotevent.toptaxon5name, plotevent.numberoftaxa, plotevent.accessioncode FROM plotevent WHERE (plotevent.emb_observation < 6);

--

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

--

CREATE VIEW view_browseparty_classcontrib AS

    SELECT observation_2.plotevent_id, classcontributor.party_id FROM (view_notemb_observation observation_2 JOIN (commclass JOIN classcontributor ON ((commclass.commclass_id = classcontributor.commclass_id))) ON ((observation_2.plotevent_id = commclass.plotevent_id))) GROUP BY observation_2.plotevent_id, classcontributor.party_id;

--

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

--

CREATE VIEW view_browseparty_obscontrib AS

    SELECT observationcontributor.party_id, observationcontributor.plotevent_id FROM (view_notemb_observation plotevent JOIN observationcontributor ON ((plotevent.plotevent_id = observationcontributor.plotevent_id))) GROUP BY observationcontributor.party_id, observationcontributor.plotevent_id;

--

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

--

CREATE VIEW view_browseparty_projectcontrib AS

    SELECT observation_1.plotevent_id, projectcontributor.party_id FROM ((project JOIN view_notemb_observation observation_1 ON ((project.project_id = observation_1.project_id))) JOIN projectcontributor ON ((project.project_id = projectcontributor.project_id))) GROUP BY observation_1.plotevent_id, projectcontributor.party_id;

--

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

--

CREATE VIEW view_browseparty_all AS

    (SELECT view_browseparty_classcontrib.plotevent_id, view_browseparty_classcontrib.party_id FROM view_browseparty_classcontrib UNION SELECT view_browseparty_projectcontrib.plotevent_id, view_browseparty_projectcontrib.party_id FROM view_browseparty_projectcontrib) UNION SELECT view_browseparty_obscontrib.plotevent_id, view_browseparty_obscontrib.party_id FROM view_browseparty_obscontrib;

--

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

--

CREATE VIEW view_browseparty_all_count AS

    SELECT count(1) AS countallcontrib, view_browseparty_all.party_id FROM view_browseparty_all GROUP BY view_browseparty_all.party_id;

--

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

--

CREATE VIEW view_browseparty_classcontrib_count AS

    SELECT count(1) AS countclasscontrib, view_browseparty_classcontrib.party_id FROM view_browseparty_classcontrib GROUP BY view_browseparty_classcontrib.party_id;

--

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

--

CREATE VIEW view_browseparty_obscontrib_count AS

    SELECT count(1) AS countobscontrib, view_browseparty_obscontrib.party_id FROM view_browseparty_obscontrib GROUP BY view_browseparty_obscontrib.party_id;

--

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

--

CREATE VIEW view_browseparty_projectcontrib_count AS

    SELECT count(1) AS countprojectcontrib, view_browseparty_projectcontrib.party_id FROM view_browseparty_projectcontrib GROUP BY view_browseparty_projectcontrib.party_id;

--

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

--

CREATE VIEW view_browseparty_all_count_combined AS

    SELECT view_browseparty_all_count.party_id, view_browseparty_all_count.countallcontrib, view_browseparty_classcontrib_count.countclasscontrib, view_browseparty_obscontrib_count.countobscontrib, view_browseparty_projectcontrib_count.countprojectcontrib FROM (((view_browseparty_all_count LEFT JOIN view_browseparty_classcontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_classcontrib_count.party_id))) LEFT JOIN view_browseparty_obscontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_obscontrib_count.party_id))) LEFT JOIN view_browseparty_projectcontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_projectcontrib_count.party_id))) ORDER BY view_browseparty_all_count.countallcontrib DESC;

--

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

--

CREATE VIEW view_busrule_duplcovercode AS

    SELECT count(1) AS count, coverindex.covermethod_id, coverindex.covercode FROM coverindex GROUP BY coverindex.covermethod_id, coverindex.covercode HAVING (count(1) > 1);

--

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

--

CREATE VIEW view_busrule_duplstratumtype AS

    SELECT count(1) AS count, stratumtype.stratummethod_id, stratumtype.stratumindex FROM stratumtype GROUP BY stratumtype.stratummethod_id, stratumtype.stratumindex HAVING (count(1) > 1);

--

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

--

CREATE VIEW view_busrule_plotsizeshape AS

    SELECT plotevent.project_id, (SELECT project.projectname FROM project WHERE (project.project_id = plotevent.project_id)) AS projectname, count(1) AS plotcount FROM (plot JOIN plotevent ON ((plot.plot_id = plotevent.plot_id))) WHERE ((plot.area IS NULL) AND ((plot.shape IS NULL) OR (((plot.shape)::text <> 'Plotless'::text) AND (upper((plot.shape)::text) !~~ 'RELEV%'::text)))) GROUP BY plotevent.project_id;

--

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

--

CREATE VIEW view_reference_transl AS

    SELECT CASE WHEN (reference.shortname IS NULL) THEN CASE WHEN (reference.title IS NULL) THEN (CASE WHEN (reference.fulltext IS NULL) THEN '[poorly documented reference]'::text ELSE CASE WHEN (length(reference.fulltext) > 35) THEN (substr(reference.fulltext, 1, 32) || '...'::text) ELSE reference.fulltext END END)::character varying ELSE reference.title END ELSE reference.shortname END AS reference_id_transl, reference.reference_id FROM reference;

--

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

--

CREATE VIEW view_commconcept_transl AS

    SELECT commconcept.commconcept_id, commconcept.commname_id, commconcept.commname, commconcept.reference_id, (((commconcept.commname || ' ['::text) || ((SELECT view_reference_transl.reference_id_transl FROM view_reference_transl WHERE (view_reference_transl.reference_id = commconcept.reference_id)))::text) || ']'::text) AS commconcept_id_transl FROM commconcept;

--

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

--

CREATE VIEW view_notemb_commclass AS

    SELECT commclass.commclass_id, commclass.plotevent_id, commclass.inspection, commclass.tableanalysis, commclass.multivariateanalysis, commclass.classpublication_id, commclass.classnotes, commclass.commname, commclass.commcode, commclass.commframework, commclass.commlevel, commclass.classstartdate, commclass.classstopdate, commclass.emb_commclass, commclass.expertsystem, commclass.accessioncode FROM commclass WHERE (commclass.emb_commclass < 6);

--

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

--

CREATE VIEW view_comminterp_more AS

    SELECT commclass.plotevent_id, commclass.inspection, commclass.tableanalysis, commclass.multivariateanalysis, commclass.expertsystem, commclass.classpublication_id, commclass.classnotes, commclass.accessioncode, commclass.classstartdate, commclass.classstopdate, comminterpretation.comminterpretation_id, comminterpretation.commclass_id, comminterpretation.commconcept_id, comminterpretation.commcode, comminterpretation.commname, comminterpretation.classfit, comminterpretation.classconfidence, comminterpretation.commauthority_id, comminterpretation.notes, comminterpretation.type, comminterpretation.nomenclaturaltype, comminterpretation.emb_comminterpretation, CASE WHEN ((comminterpretation.classfit)::text = 'Absolutely wrong'::text) THEN 6 WHEN ((comminterpretation.classfit)::text = 'Understandable but wrong'::text) THEN 4 WHEN ((comminterpretation.classfit)::text = 'Reasonable or acceptable answer'::text) THEN 3 WHEN ((comminterpretation.classfit)::text = 'Good answer'::text) THEN 2 WHEN ((comminterpretation.classfit)::text = 'Absolutely correct'::text) THEN 1 ELSE 5 END AS classfitnum, CASE WHEN ((comminterpretation.classconfidence)::text = 'High'::text) THEN 1 WHEN ((comminterpretation.classconfidence)::text = 'Medium'::text) THEN 2 WHEN ((comminterpretation.classconfidence)::text = 'Low'::text) THEN 3 ELSE 4 END AS classconfidencenum FROM view_notemb_commclass commclass, comminterpretation WHERE (commclass.commclass_id = comminterpretation.commclass_id);

--

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

--

CREATE VIEW view_csv_taxonimportance_pre AS

    SELECT taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname AS plant, CASE WHEN (collectiveobservation.stratum_id IS NULL) THEN '-all-'::character varying ELSE (SELECT stratumtype.stratumname FROM stratumtype, stratum WHERE ((stratum.stratumtype_id = stratumtype.stratumtype_id) AND (stratum.stratum_id = collectiveobservation.stratum_id))) END AS stratum, collectiveobservation.cover, (SELECT min((coverindex.covercode)::text) AS min FROM coverindex WHERE (((coverindex.coverpercent = collectiveobservation.cover) AND (coverindex.covermethod_id = plotevent.covermethod_id)) AND (plotevent.plotevent_id = taxonoccurrence.plotevent_id))) AS covercode_exact, (SELECT min((coverindex.covercode)::text) AS min FROM coverindex WHERE ((((coverindex.upperlimit >= collectiveobservation.cover) AND (coverindex.lowerlimit <= collectiveobservation.cover)) AND (coverindex.covermethod_id = plotevent.covermethod_id)) AND (plotevent.plotevent_id = taxonoccurrence.plotevent_id))) AS covercode_byrange, collectiveobservation.basalarea, taxonoccurrence.accessioncode FROM taxonoccurrence, collectiveobservation, view_notemb_observation plotevent WHERE ((taxonoccurrence.taxonoccurrence_id = collectiveobservation.taxonoccurrence_id) AND (taxonoccurrence.plotevent_id = plotevent.plotevent_id));

--

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

--

CREATE VIEW view_csv_taxonimportance AS

    SELECT view_csv_taxonimportance_pre.plotevent_id, view_csv_taxonimportance_pre.plant, view_csv_taxonimportance_pre.stratum, view_csv_taxonimportance_pre.cover, CASE WHEN (view_csv_taxonimportance_pre.covercode_exact IS NULL) THEN view_csv_taxonimportance_pre.covercode_byrange ELSE view_csv_taxonimportance_pre.covercode_exact END AS covercode, view_csv_taxonimportance_pre.basalarea, view_csv_taxonimportance_pre.accessioncode FROM view_csv_taxonimportance_pre;

--

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

--

CREATE VIEW view_dbafielddesc_notimpl AS

    SELECT dba_fielddescription.dba_fielddescription_id, dba_fielddescription.tablename, (SELECT dba_tabledescription.tablelabel FROM dba_tabledescription WHERE ((dba_fielddescription.tablename)::text = (dba_tabledescription.tablename)::text) LIMIT 1) AS tablename_transl, dba_fielddescription.fieldname, dba_fielddescription.fieldlabel, dba_fielddescription.fieldmodel, dba_fielddescription.fieldnulls, CASE WHEN ((dba_fielddescription.fieldnulls)::text = 'no'::text) THEN 'required'::text ELSE 'no'::text END AS fieldnulls_transl, dba_fielddescription.fieldtype, dba_fielddescription.fieldkey, CASE WHEN ((dba_fielddescription.fieldkey)::text = 'PK'::text) THEN dba_fielddescription.fieldkey WHEN ((dba_fielddescription.fieldkey)::text = 'FK'::text) THEN dba_fielddescription.fieldkey WHEN ((dba_fielddescription.fieldmodel)::text = 'denorm'::text) THEN dba_fielddescription.fieldmodel WHEN ((dba_fielddescription.fieldmodel)::text = 'implementation'::text) THEN dba_fielddescription.fieldmodel ELSE 'n/a'::character varying END AS fieldkey_transl, dba_fielddescription.fieldreferences, dba_fielddescription.fieldlist, dba_fielddescription.fieldnotes, dba_fielddescription.fielddefinition, dba_fielddescription.fieldkeywords FROM dba_fielddescription WHERE ((lower((dba_fielddescription.fieldmodel)::text) <> 'implementation'::text) AND (lower((dba_fielddescription.fieldmodel)::text) <> 'denorm'::text)) ORDER BY dba_fielddescription.dba_fielddescription_id;

--

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

--

CREATE VIEW view_emb_embargo_currentfullonly AS

    SELECT embargo.embargo_id, embargo.plot_id, embargo.embargoreason, embargo.defaultstatus, embargo.embargostart, embargo.embargostop FROM embargo WHERE (((embargo.defaultstatus = 6) AND (embargo.embargostart < now())) AND (embargo.embargostop > now()));

--

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

--

CREATE VIEW view_emb_embargo_complete AS

    SELECT COALESCE(emb.defaultstatus, 0) AS currentemb, plot.plot_id FROM (plot LEFT JOIN view_emb_embargo_currentfullonly emb ON ((plot.plot_id = emb.plot_id)));

--

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

--

CREATE VIEW view_keywprojplaces AS

    SELECT project.project_id, plot.stateprovince FROM plot, project, plotevent WHERE ((plot.plot_id = plotevent.plot_id) AND (project.project_id = plotevent.project_id)) GROUP BY project.project_id, plot.stateprovince;

--

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

--

CREATE VIEW view_kwhelper_projcontrib AS

    SELECT project.project_id, concat((((' '::text || (COALESCE(party.givenname, ''::character varying))::text) || ' '::text) || (COALESCE(party.surname, ''::character varying))::text)) AS kw FROM party, project, projectcontributor WHERE ((party.party_id = projectcontributor.party_id) AND (project.project_id = projectcontributor.project_id)) GROUP BY project.project_id;

--

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

--

CREATE VIEW view_kwhelper_refjournal AS

    SELECT reference.reference_id, (((((COALESCE(referencejournal.journal, ''::character varying))::text || ' '::text) || (COALESCE(referencejournal.issn, ''::character varying))::text) || ' '::text) || (COALESCE(referencejournal.abbreviation, ''::character varying))::text) AS kw FROM reference, referencejournal WHERE (reference.referencejournal_id = referencejournal.referencejournal_id) GROUP BY reference.reference_id, referencejournal.journal, referencejournal.issn, referencejournal.abbreviation;

--

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

--

CREATE VIEW view_kwhelper_refparty AS

    SELECT reference.reference_id, concat((((((((' '::text || (COALESCE(referenceparty.givenname, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.surname, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.suffix, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.organizationname, ''::character varying))::text)) AS kw FROM referenceparty, reference, referencecontributor WHERE ((referencecontributor.referenceparty_id = referenceparty.referenceparty_id) AND (reference.reference_id = referencecontributor.reference_id)) GROUP BY reference.reference_id;

--

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

--

CREATE VIEW view_notemb_classcontributor AS

    SELECT classcontributor.classcontributor_id, classcontributor.commclass_id, classcontributor.party_id, classcontributor.role_id, classcontributor.emb_classcontributor FROM classcontributor WHERE (classcontributor.emb_classcontributor < 6);

--

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

--

CREATE VIEW view_notemb_comminterpretation AS

    SELECT comminterpretation.comminterpretation_id, comminterpretation.commclass_id, comminterpretation.commconcept_id, comminterpretation.commcode, comminterpretation.commname, comminterpretation.classfit, comminterpretation.classconfidence, comminterpretation.commauthority_id, comminterpretation.notes, comminterpretation.type, comminterpretation.nomenclaturaltype, comminterpretation.emb_comminterpretation FROM comminterpretation WHERE (comminterpretation.emb_comminterpretation < 6);

--

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

--

CREATE VIEW view_notemb_disturbanceobs AS

    SELECT disturbanceobs.disturbanceobs_id, disturbanceobs.plotevent_id, disturbanceobs.disturbancetype, disturbanceobs.disturbanceintensity, disturbanceobs.disturbanceage, disturbanceobs.disturbanceextent, disturbanceobs.disturbancecomment, disturbanceobs.emb_disturbanceobs FROM disturbanceobs WHERE (disturbanceobs.emb_disturbanceobs < 6);

--

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

--

CREATE VIEW view_notemb_plot AS

    SELECT plot.plot_id, plot.authorplotcode, plot.reference_id, plot.parent_id, plot.reallatitude, plot.reallongitude, plot.locationaccuracy, plot.confidentialitystatus, plot.confidentialityreason, plot.latitude, plot.longitude, plot.authore, plot.authorn, plot.authorzone, plot.authordatum, plot.authorlocation, plot.locationnarrative, plot.azimuth, plot.dsgpoly, plot.shape, plot.area, plot.standsize, plot.placementmethod, plot.permanence, plot.layoutnarrative, plot.elevation, plot.elevationaccuracy, plot.elevationrange, plot.slopeaspect, plot.minslopeaspect, plot.maxslopeaspect, plot.slopegradient, plot.minslopegradient, plot.maxslopegradient, plot.topoposition, plot.landform, plot.surficialdeposits, plot.rocktype, plot.stateprovince, plot.country, plot.submitter_surname, plot.submitter_givenname, plot.submitter_email, plot.notespublic, plot.notesmgt, plot.revisions, plot.dateentered, plot.emb_plot, plot.plotrationalenarrative, plot.accessioncode FROM plot WHERE (plot.emb_plot < 6);

--

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

--

CREATE VIEW view_notemb_soilobs AS

    SELECT soilobs.soilobs_id, soilobs.plotevent_id, soilobs.soilhorizon, soilobs.soildepthtop, soilobs.soildepthbottom, soilobs.soilcolor, soilobs.soilorganic, soilobs.soiltexture, soilobs.soilsand, soilobs.soilsilt, soilobs.soilclay, soilobs.soilcoarse, soilobs.soilph, soilobs.exchangecapacity, soilobs.basesaturation, soilobs.soildescription, soilobs.emb_soilobs FROM soilobs WHERE (soilobs.emb_soilobs < 6);

--

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

--

CREATE VIEW view_notemb_stemcount AS

    SELECT stemcount.stemcount_id, stemcount.collectiveobservation_id, stemcount.stemdiameter, stemcount.stemdiameteraccuracy, stemcount.stemheight, stemcount.stemheightaccuracy, stemcount.stemcount, stemcount.stemtaxonarea, stemcount.emb_stemcount FROM stemcount WHERE (stemcount.emb_stemcount < 6);

--

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

--

CREATE VIEW view_notemb_stemlocation AS

    SELECT individualplant.individualplant_id, individualplant.stemcount_id, individualplant.stemcode, individualplant.stemxposition, individualplant.stemyposition, individualplant.stemhealth, individualplant.emb_stemlocation FROM individualplant WHERE (individualplant.emb_stemlocation < 6);

--

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

--

CREATE VIEW view_notemb_taxonalt AS

    SELECT taxonalt.taxonalt_id, taxonalt.taxondetermination_id, taxonalt.plantconcept_id, taxonalt.taxonaltfit, taxonalt.taxonaltconfidence, taxonalt.taxonaltnotes, taxonalt.emb_taxonalt FROM taxonalt WHERE (taxonalt.emb_taxonalt < 6);

--

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

--

CREATE VIEW view_notemb_taxonimportance AS

    SELECT collectiveobservation.collectiveobservation_id, collectiveobservation.taxonoccurrence_id, collectiveobservation.stratum_id, collectiveobservation.cover, collectiveobservation.basalarea, collectiveobservation.biomass, collectiveobservation.inferencearea, collectiveobservation.stratumbase, collectiveobservation.stratumheight, collectiveobservation.emb_taxonimportance, collectiveobservation.covercode FROM collectiveobservation WHERE (collectiveobservation.emb_taxonimportance < 6);

--

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

--

CREATE VIEW view_notemb_taxoninterpretation AS

    SELECT taxondetermination.taxondetermination_id, taxondetermination.taxonoccurrence_id, taxondetermination.individualplant_id, taxondetermination.plantconcept_id, taxondetermination.plantname_id, taxondetermination.party_id, taxondetermination.role_id, taxondetermination.interpretationtype, taxondetermination.reference_id, taxondetermination.originalinterpretation, taxondetermination.currentinterpretation, taxondetermination.taxonfit, taxondetermination.taxonconfidence, taxondetermination.collector_id, taxondetermination.collectionnumber, taxondetermination.museum_id, taxondetermination.museumaccessionnumber, taxondetermination.grouptype, taxondetermination.notes, taxondetermination.notespublic, taxondetermination.notesmgt, taxondetermination.revisions, taxondetermination.interpretationdate, taxondetermination.collectiondate, taxondetermination.emb_taxoninterpretation, taxondetermination.accessioncode FROM taxondetermination WHERE (taxondetermination.emb_taxoninterpretation < 6);

--

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

--

CREATE VIEW view_notemb_taxonobservation AS

    SELECT taxonoccurrence.taxonoccurrence_id, taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname, taxonoccurrence.reference_id, taxonoccurrence.taxoninferencearea, taxonoccurrence.emb_taxonobservation, taxonoccurrence.int_origplantconcept_id, taxonoccurrence.int_origplantscifull, taxonoccurrence.int_origplantscinamenoauth, taxonoccurrence.int_origplantcommon, taxonoccurrence.int_origplantcode, taxonoccurrence.int_currplantconcept_id, taxonoccurrence.int_currplantscifull, taxonoccurrence.int_currplantscinamenoauth, taxonoccurrence.int_currplantcommon, taxonoccurrence.int_currplantcode, taxonoccurrence.accessioncode FROM taxonoccurrence WHERE (taxonoccurrence.emb_taxonobservation < 6);

--

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

--

CREATE VIEW view_observation_transl AS

    SELECT plotevent.plotevent_id, COALESCE(plotevent.authorobscode, plot.authorplotcode) AS observation_id_transl FROM view_notemb_observation plotevent, plot WHERE (plotevent.plot_id = plot.plot_id);

--

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

--

CREATE VIEW view_party_public AS

    SELECT party.party_id, party.accessioncode, party.salutation, party.surname, party.givenname, party.middlename, party.organizationname, party.contactinstructions, party.email FROM (party LEFT JOIN usr ON ((party.party_id = usr.party_id))) WHERE ((usr.usr_id IS NULL) OR (usr.permission_type > 1));

--

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

--

CREATE VIEW view_party_transl AS

    SELECT CASE WHEN (party.surname IS NULL) THEN CASE WHEN (party.organizationname IS NULL) THEN '[poorly documented party]'::text ELSE ((party.organizationname)::text || ' (organization)'::text) END ELSE ((party.surname)::text || CASE WHEN (party.givenname IS NULL) THEN ''::text ELSE (', '::text || (COALESCE(party.givenname, ''::character varying))::text) END) END AS party_id_transl, party.party_id FROM party;

--

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

--

CREATE VIEW view_plantconcept_transl AS

    SELECT plantconcept.plantconcept_id, plantconcept.plantname_id, plantconcept.plantname, plantconcept.reference_id, ((((plantconcept.plantname)::text || ' ['::text) || ((SELECT view_reference_transl.reference_id_transl FROM view_reference_transl WHERE (view_reference_transl.reference_id = plantconcept.reference_id)))::text) || ']'::text) AS plantconcept_id_transl FROM plantconcept;

--

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

--

CREATE VIEW view_plotall_withembargo AS

    SELECT plot.plot_id, plot.authorplotcode, plot.reference_id, plot.parent_id, plot.reallatitude, plot.reallongitude, plot.locationaccuracy, plot.confidentialitystatus, plot.confidentialityreason, plot.latitude, plot.longitude, plot.authore, plot.authorn, plot.authorzone, plot.authordatum, plot.authorlocation, plot.locationnarrative, plot.azimuth, plot.dsgpoly, plot.shape, plot.area, plot.standsize, plot.placementmethod, plot.permanence, plot.layoutnarrative, plot.elevation, plot.elevationaccuracy, plot.elevationrange, plot.slopeaspect, plot.minslopeaspect, plot.maxslopeaspect, plot.slopegradient, plot.minslopegradient, plot.maxslopegradient, plot.topoposition, plot.landform, plot.surficialdeposits, plot.rocktype, plot.stateprovince, plot.country, plot.submitter_surname, plot.submitter_givenname, plot.submitter_email, plot.notespublic, plot.notesmgt, plot.revisions, plot.dateentered, plot.emb_plot, plot.plotrationalenarrative, plot.accessioncode, embargo.embargo_id, embargo.embargoreason, embargo.defaultstatus, embargo.embargostart, embargo.embargostop, (embargo.defaultstatus + CASE WHEN (embargo.embargostop < now()) THEN 100 ELSE 0 END) AS embstatusinclexpired FROM (plot LEFT JOIN embargo ON ((plot.plot_id = embargo.plot_id)));

--

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

--

CREATE VIEW view_std_commnames_code AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Code'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_commnames_common AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Common'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_commnames_sciname AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Scientific'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_commnames_translated AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Translated'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_plantnames_code AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Code'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_plantnames_common AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'English Common'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_plantnames_sciname AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Scientific'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_std_plantnames_scinamenoauth AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Scientific without authors'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

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

--

CREATE VIEW view_taxoninterp_more AS

    SELECT taxondetermination.taxondetermination_id, taxondetermination.taxonoccurrence_id, taxondetermination.individualplant_id, taxondetermination.plantconcept_id, taxondetermination.plantname_id, taxondetermination.party_id, taxondetermination.role_id, taxondetermination.interpretationtype, taxondetermination.reference_id, taxondetermination.originalinterpretation, taxondetermination.currentinterpretation, taxondetermination.taxonfit, taxondetermination.taxonconfidence, taxondetermination.collector_id, taxondetermination.collectionnumber, taxondetermination.museum_id, taxondetermination.museumaccessionnumber, taxondetermination.grouptype, taxondetermination.notes, taxondetermination.notespublic, taxondetermination.notesmgt, taxondetermination.revisions, taxondetermination.interpretationdate, taxondetermination.collectiondate, taxondetermination.emb_taxoninterpretation, taxondetermination.accessioncode, plantconcept.accessioncode AS pc_accessioncode, plantconcept.plantname AS pc_plantname, taxonoccurrence.plotevent_id FROM view_notemb_taxoninterpretation taxondetermination, taxonoccurrence, plantconcept WHERE ((taxonoccurrence.taxonoccurrence_id = taxondetermination.taxonoccurrence_id) AND (plantconcept.plantconcept_id = taxondetermination.plantconcept_id));

--

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

--

CREATE VIEW view_taxonobs_withmaxcover AS

    SELECT taxonoccurrence.taxonoccurrence_id, taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname, taxonoccurrence.reference_id, taxonoccurrence.taxoninferencearea, taxonoccurrence.emb_taxonobservation, taxonoccurrence.int_origplantconcept_id, taxonoccurrence.int_origplantscifull, taxonoccurrence.int_origplantscinamenoauth, taxonoccurrence.int_origplantcommon, taxonoccurrence.int_origplantcode, taxonoccurrence.int_currplantconcept_id, taxonoccurrence.int_currplantscifull, taxonoccurrence.int_currplantscinamenoauth, taxonoccurrence.int_currplantcommon, taxonoccurrence.int_currplantcode, taxonoccurrence.accessioncode, (SELECT max(collectiveobservation.cover) AS max FROM collectiveobservation WHERE (collectiveobservation.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id)) AS maxcover FROM view_notemb_taxonobservation taxonoccurrence;

--

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

--

CREATE VIEW view_taxonobs_distinctid_curr AS

    SELECT userdatasetitem.userdataset_id, taxonoccurrence.plotevent_id, taxonoccurrence.int_currplantconcept_id AS plantconcept_id, max(taxonoccurrence.maxcover) AS maxplantcover FROM view_taxonobs_withmaxcover taxonoccurrence, userdatasetitem WHERE ((taxonoccurrence.plotevent_id = userdatasetitem.itemrecord) AND ((userdatasetitem.itemtable)::text = 'plotevent'::text)) GROUP BY taxonoccurrence.int_currplantconcept_id, userdatasetitem.userdataset_id, taxonoccurrence.plotevent_id;

--

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

--

CREATE VIEW view_taxonobs_distinctid_curr_counts AS

    SELECT view_taxonobs_distinctid_curr.userdataset_id, view_taxonobs_distinctid_curr.plantconcept_id, count(1) AS countobs, avg(view_taxonobs_distinctid_curr.maxplantcover) AS avgcovernoround, min(view_taxonobs_distinctid_curr.maxplantcover) AS mincovernoround, max(view_taxonobs_distinctid_curr.maxplantcover) AS maxcovernoround FROM view_taxonobs_distinctid_curr GROUP BY view_taxonobs_distinctid_curr.userdataset_id, view_taxonobs_distinctid_curr.plantconcept_id;

--

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

--

CREATE VIEW view_taxonobs_distinctid_curr_counts_plants AS

    SELECT view_taxonobs_distinctid_curr_counts.userdataset_id, view_taxonobs_distinctid_curr_counts.plantconcept_id, view_taxonobs_distinctid_curr_counts.countobs, view_taxonobs_distinctid_curr_counts.avgcovernoround, view_taxonobs_distinctid_curr_counts.mincovernoround, view_taxonobs_distinctid_curr_counts.maxcovernoround, round((view_taxonobs_distinctid_curr_counts.avgcovernoround)::numeric, 3) AS avgcover, round((view_taxonobs_distinctid_curr_counts.mincovernoround)::numeric, 3) AS mincover, round((view_taxonobs_distinctid_curr_counts.maxcovernoround)::numeric, 3) AS maxcover, temptbl_std_plantnames.plantname, temptbl_std_plantnames.sciname, temptbl_std_plantnames.scinamenoauth, temptbl_std_plantnames.code, temptbl_std_plantnames.common FROM temptbl_std_plantnames, view_taxonobs_distinctid_curr_counts WHERE (view_taxonobs_distinctid_curr_counts.plantconcept_id = temptbl_std_plantnames.plantconcept_id);

--

ALTER TABLE individualplant ALTER COLUMN individualplant_id SET DEFAULT nextval('stemlocation_stemlocation_id_seq'::regclass);

-- Name: stemcount_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE stemcount ALTER COLUMN stemcount_id SET DEFAULT nextval('stemcount_stemcount_id_seq'::regclass);

-- Name: stemcount_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY stemcount

    ADD CONSTRAINT stemcount_pkey PRIMARY KEY (stemcount_id);

--

ALTER TABLE ONLY individualplant

    ADD CONSTRAINT stemlocation_pkey PRIMARY KEY (individualplant_id);

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

--

CREATE INDEX emb_stemcount_idx ON stemcount USING btree (emb_stemcount);

--

CREATE INDEX emb_stemlocation_idx ON individualplant USING btree (emb_stemlocation);

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

CREATE INDEX stemcount_taxonimportance_id_x ON stemcount USING btree (collectiveobservation_id);

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

--

CREATE INDEX stemlocation_stemcount_id_x ON individualplant USING btree (stemcount_id);

--

CREATE INDEX taxoninterpretation_stemlocation_id_x ON taxondetermination USING btree (individualplant_id);

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

    ADD CONSTRAINT collection_individualplant_id_fkey FOREIGN KEY (individualplant_id) REFERENCES individualplant(individualplant_id);

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

ALTER TABLE ONLY stemcount

    ADD CONSTRAINT r1stemcount_taxonimportance_id FOREIGN KEY (collectiveobservation_id) REFERENCES collectiveobservation(collectiveobservation_id);

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

--

ALTER TABLE ONLY individualplant

    ADD CONSTRAINT r1stemlocation_stemcount_id FOREIGN KEY (stemcount_id) REFERENCES stemcount(stemcount_id);

--

    ADD CONSTRAINT r2taxoninterpretation_stemlocation_id FOREIGN KEY (individualplant_id) REFERENCES individualplant(individualplant_id);