#!/bin/bash -e

. "$(dirname "${BASH_SOURCE[0]}")"/../../lib/runscripts/validations.pg.sql.run

--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET search_path = bien2_traits, pg_catalog;

--

-- Name: _traits_01_count_records; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_01_count_records AS

 SELECT count(*) AS "totalRecords"

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

-- Name: _traits_02_count_trait_names; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_02_count_trait_names AS

 SELECT count(DISTINCT "TraitObservation"."measurementName") AS traits

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

-- Name: _traits_03_list_trait_names; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_03_list_trait_names AS

 SELECT DISTINCT "TraitObservation"."measurementName" AS trait

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

  ORDER BY "TraitObservation"."measurementName";

--

-- Name: _traits_04_count_records_per_trait; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_04_count_records_per_trait AS

 SELECT "TraitObservation"."measurementName" AS trait, 

    count(*) AS measurements

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

  GROUP BY "TraitObservation"."measurementName"

  ORDER BY "TraitObservation"."measurementName";

--

-- Name: _traits_05_count_taxa; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_05_count_taxa AS

 SELECT count(DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text)))) AS taxa

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text));

--

-- Name: _traits_06_list_distinct_taxa; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_06_list_distinct_taxa AS

 SELECT DISTINCT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

  ORDER BY btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text)));

--

-- Name: _traits_07_trait_value_and_units; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_07_trait_value_and_units AS

 SELECT "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value, 

    "TraitObservation"."measurementUnit" AS units

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

  ORDER BY "TraitObservation"."measurementName", "TraitObservation"."measurementValue", "TraitObservation"."measurementUnit";

--

-- Name: _traits_08_taxonname_trait_and_value; Type: VIEW; Schema: bien2_traits; Owner: -

--

CREATE VIEW _traits_08_taxonname_trait_and_value AS

 SELECT btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))) AS taxonwithauthor, 

    "TraitObservation"."measurementName" AS trait, 

    "TraitObservation"."measurementValue" AS value

   FROM "TraitObservation"

  WHERE (((("TraitObservation"."taxonName" IS NOT NULL) AND ("TraitObservation"."taxonName" <> ''::text)) AND ("TraitObservation"."measurementName" IS NOT NULL)) AND ("TraitObservation"."measurementName" <> ''::text))

  ORDER BY btrim(concat_ws(' '::text, ifnull("TraitObservation"."taxonName", ''::text), ifnull("TraitObservation"."scientificNameAuthorship", ''::text))), "TraitObservation"."measurementName", "TraitObservation"."measurementValue";

--

-- PostgreSQL database dump complete

--

--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET search_path = "SALVIAS", pg_catalog;

--

-- Name: _plots_01_count_of_projects; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_01_count_of_projects AS

 SELECT count(*) AS projects

   FROM projects;

--

-- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS

 SELECT projects.project_name

   FROM projects;

--

-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS

 SELECT count(DISTINCT (pm."SiteCode")::text) AS plots

   FROM (projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)));

--

-- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS

 SELECT p.project_name, 

    count(*) AS plots

   FROM (projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

  GROUP BY p.project_name;

--

-- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM (projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)));

--

-- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM (((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

   JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_06a_list_of_stems AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    (s.stem_id)::text AS stem_id

   FROM (((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

   JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))

  ORDER BY p.project_name, (pm."SiteCode")::text, (s.stem_id)::text;

--

-- Name: _plots_07_list_of_plots_which_use_counts_of_indiv_per_species; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po."NoInd" > 1))))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    sum(po."NoInd") AS individuals

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: VIEW _plots_10_count_of_individuals_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS '

Method: Sum on NoInd (number of individuals) column.

Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.

Note 2: Do not count records in plotObservations table, as this will give incorrect total for plots such as Gentry plot, which count individuals per species per subplot

';

--

-- Name: _plots_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_10a_aggregate_observation_individual_counts AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    (po."PlotObsID")::text AS individual_id, 

    po."NoInd" AS individuals

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

  ORDER BY p.project_name, (pm."SiteCode")::text, (po."PlotObsID")::text;

--

-- Name: VIEW _plots_10a_aggregate_observation_individual_counts; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_10a_aggregate_observation_individual_counts IS '

Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.

';

--

-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT s.stem_id) AS stems

   FROM (((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

   JOIN stems s ON ((po."PlotObsID" = s.plotobs_id)))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: VIEW _plots_11_count_of_stems_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS '

Method: count records in stems table

';

--

-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS

 SELECT a.project_name, 

    a.plot_code, 

    count(a.taxon) AS taxa

   FROM ( SELECT DISTINCT p.project_name, 

            (pm."SiteCode")::text AS plot_code, 

            btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon

           FROM ((projects p

      JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))) a

  GROUP BY a.project_name, a.plot_code

  ORDER BY a.project_name, a.plot_code;

--

-- Name: VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS '

Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN

';

--

-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));

--

-- Name: VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS '

Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN.

';

--

-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

    sum(po."NoInd") AS individuals

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))

  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));

--

-- Name: VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS '

Note: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegBIEN

';

--

-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

    sum((po.cover_percent)::double precision) AS totalpercentcover

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.cover_percent IS NOT NULL))))

  GROUP BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))))

  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));

--

-- Name: VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS '

Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien

Note 2: Grouping mean cover for entire plot allows inclusion of plots with subplots (separate cover measure for each species in each subplot),  plots with strata (separate cover measures for each species in each stratum), and plots without subplots or strata (one cover measure per species per plot) in same query.

Note 3: currently, there are no SALVIAS percent cover plots in BIEN

';

--

-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text)))))) AS taxon, 

    po.intercept_cm

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND (po.intercept_cm IS NOT NULL))))

  ORDER BY p.project_name, (pm."SiteCode")::text, btrim(concat_ws(' '::text, ifnull((po."Family")::text, ''::text), ifnull((po."Genus")::text, ''::text), ifnull((po."Species")::text, ''::text), if((po.infra_ep_1 IS NULL), ifnull((po.auth)::text, ''::text), btrim(concat_ws(' '::text, ifnull((po.infra_rank_1)::text, ''::text), ifnull((po.infra_ep_1)::text, ''::text), ifnull((po.infra_auth_1)::text, ''::text))))));

--

-- Name: VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj IS '

Note 1: Assumes identical concatenation of taxonomic fields to form verbatim taxon name in vegbien

Note 2: Grouping mean cover for entire plot combines plots with subplots (separate cover measure for each species in each subplot) with plots with strata (separate cover measures for each species in each stratum) with plots without subplots or strata (one cover measure per species per plot.

';

--

-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT po."Line") AS subplots

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON ((pm."PlotID" = po."PlotID")))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS

 SELECT DISTINCT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    po."Line" AS subplot_code

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))

  ORDER BY p.project_name, (pm."SiteCode")::text, po."Line";

--

-- Name: VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: COMMENT; Schema: SALVIAS; Owner: -

--

COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS '

Note: includes plots with and without subplots

';

--

-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: -

--

CREATE OR REPLACE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS

 SELECT p.project_name, 

    (pm."SiteCode")::text AS plot_code, 

    count(DISTINCT ARRAY[po.census_date]) AS inventories

   FROM ((projects p

   JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id)))

   LEFT JOIN "plotObservations" po ON (((pm."PlotID" = po."PlotID") AND ((pm."SiteCode")::text IS NOT NULL))))

  GROUP BY p.project_name, (pm."SiteCode")::text

  ORDER BY p.project_name, (pm."SiteCode")::text;

--

-- PostgreSQL database dump complete

--

#!/bin/bash -e

. "$(dirname "${BASH_SOURCE[0]}")"/../../lib/runscripts/validations.pg.sql.run

--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET search_path = "NY", pg_catalog;

--

-- Name: _specimens_01_count_of_total_records_specimens_in_source_db; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS

 SELECT count(*) AS "totalSpecimenRecords"

   FROM "Ecatalog_all";

--

-- Name: VIEW _specimens_01_count_of_total_records_specimens_in_source_db; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_01_count_of_total_records_specimens_in_source_db IS '

Check: should return 1 row

';

--

-- Name: _specimens_02_count_of_unique_verbatim_families; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS

 SELECT count(DISTINCT "Ecatalog_all".family) AS families

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".family IS NOT NULL);

--

-- Name: VIEW _specimens_02_count_of_unique_verbatim_families; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_02_count_of_unique_verbatim_families IS '

Check: should return 1 row

';

--

-- Name: _specimens_03_list_of_verbatim_families; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_03_list_of_verbatim_families AS

 SELECT DISTINCT "Ecatalog_all".family

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

--

-- Name: VIEW _specimens_03_list_of_verbatim_families; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_03_list_of_verbatim_families IS '

Check: should return same number of rows

';

--

-- Name: _specimens_04_count_of_species_binomials; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_04_count_of_species_binomials AS

 SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS species_binomials

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

--

-- Name: VIEW _specimens_04_count_of_species_binomials; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_04_count_of_species_binomials IS '

Check: should return 1 row

';

--

-- Name: _specimens_05_list_of_species_binomials; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_05_list_of_species_binomials AS

 SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS species_binomial

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL));

--

-- Name: VIEW _specimens_05_list_of_species_binomials; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_05_list_of_species_binomials IS '

Check: should return 3335 rows

';

--

-- Name: _specimens_06_count_of_unique_verb_subsp_taxa_with_author; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS

 SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author

   FROM "Ecatalog_all"

  WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));

--

-- Name: VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author IS '

Check: should return 1 row

';

--

-- Name: _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS

 SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".subspecies IS NOT NULL);

--

-- Name: VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author IS '

Check: should return 40145 rows

';

--

-- Name: _specimens_08_count_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS

 SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".genus IS NOT NULL);

--

-- Name: VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus IS '

Check: should return 1 row

';

--

-- Name: _specimens_09_list_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS

 SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".genus IS NOT NULL);

--

-- Name: VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus IS '

Check: should return 45997 rows

';

--

-- Name: _specimens_10_count_number_of_records_by_institution; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_10_count_number_of_records_by_institution AS

 SELECT "Ecatalog_all".specimen_duplicate_institutions, 

    count(*) AS records

   FROM "Ecatalog_all"

  GROUP BY "Ecatalog_all".specimen_duplicate_institutions;

--

-- Name: VIEW _specimens_10_count_number_of_records_by_institution; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_10_count_number_of_records_by_institution IS '

Note: Majority should be from ''NY''; these are standard herbarium acronyms

Check: should return 6 rows

';

--

-- Name: _specimens_11_list_of_three_standard_political_divisions; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS

 SELECT DISTINCT "Ecatalog_all".country, 

    "Ecatalog_all"."stateProvince", 

    "Ecatalog_all".county

   FROM "Ecatalog_all";

--

-- Name: VIEW _specimens_11_list_of_three_standard_political_divisions; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_11_list_of_three_standard_political_divisions IS '

Note: character set issues may cause mis-matches. This query is a good way to reveal character set issues, either in source db or in BIEN

Check: should return 5232 rows

';

--

-- Name: _specimens_12_distinct_collector_name_collect_num_date_w_count; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS

 SELECT ifnull("Ecatalog_all"."recordedBy", ''::text) AS "collectorName", 

    ifnull("Ecatalog_all"."collectorNumber", ''::text) AS "collectionNumber", 

    concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected") AS "dateCollected", 

    count(*) AS "specimenRecords"

   FROM "Ecatalog_all"

  GROUP BY "Ecatalog_all"."recordedBy", "Ecatalog_all"."collectorNumber", concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected")

  ORDER BY "Ecatalog_all"."recordedBy", "Ecatalog_all"."collectorNumber", concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected");

--

-- Name: VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count IS '

Check: should return 309396 rows

';

--

-- Name: _specimens_13_count_of_all_verbatim_and_decimal_lat_long; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS

 SELECT ( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count

           FROM "Ecatalog_all"

          WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL))) AS "allLatLongs", 

    ( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count

           FROM "Ecatalog_all"

          WHERE (is_castable("Ecatalog_all"."decimalLatitude", NULL::double precision) AND is_castable("Ecatalog_all"."decimalLongitude", NULL::double precision))) AS "decimalLatLongs";

--

-- Name: VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long IS '

Check: should return 1 row

';

--

-- Name: _specimens_13a_list_of_all_verbatim_lat_long; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_13a_list_of_all_verbatim_lat_long AS

 SELECT DISTINCT "Ecatalog_all"."decimalLatitude" AS "verbatimLatitude", 

    "Ecatalog_all"."decimalLongitude" AS "verbatimLongitude"

   FROM "Ecatalog_all"

  WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL));

--

-- Name: _specimens_13b_list_of_all_decimal_lat_long; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_13b_list_of_all_decimal_lat_long AS

 SELECT DISTINCT ("Ecatalog_all"."decimalLatitude")::double precision AS "decimalLatitude", 

    ("Ecatalog_all"."decimalLongitude")::double precision AS "decimalLongitude"

   FROM "Ecatalog_all"

  WHERE (is_castable("Ecatalog_all"."decimalLatitude", NULL::double precision) AND is_castable("Ecatalog_all"."decimalLongitude", NULL::double precision));

--

-- Name: _specimens_14_count_of_all_invalid_verbatim_lat_long; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS

 SELECT ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ((("Ecatalog_all"."decimalLatitude")::double precision > (90)::double precision) OR (("Ecatalog_all"."decimalLatitude")::double precision < ((-90))::double precision))) AS "badLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ((("Ecatalog_all"."decimalLongitude")::double precision > (180)::double precision) OR (("Ecatalog_all"."decimalLongitude")::double precision < ((-180))::double precision))) AS "badLongs";

--

-- Name: VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long IS '

Check: should return 1 row

';

--

-- Name: _specimens_15_list_distinct_locality_descriptions; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS

 SELECT DISTINCT "Ecatalog_all".locality AS "localityDescription"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".locality IS NOT NULL);

--

-- Name: VIEW _specimens_15_list_distinct_locality_descriptions; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_15_list_distinct_locality_descriptions IS '

Check: should return 125592 records

';

--

-- Name: _specimens_16_list_distinct_specimen_descriptions; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS

 SELECT DISTINCT "Ecatalog_all"."specimenDescription"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all"."specimenDescription" IS NOT NULL);

--

-- Name: VIEW _specimens_16_list_distinct_specimen_descriptions; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_16_list_distinct_specimen_descriptions IS '

Note: specimens descriptions in nybg extract is in column "specimenDescription"

Check: should return 158460 records

';

--

-- PostgreSQL database dump complete

--

#!/bin/bash -e

. "$(dirname "${BASH_SOURCE[0]}")"/../../lib/runscripts/validations.pg.sql.run

validate/install: _always validations.sql/run ;