CREATE OR REPLACE FUNCTION if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown)

  RETURNS anyelement AS

$BODY$

SELECT util._if($1, $2, $3)

$BODY$

  LANGUAGE sql IMMUTABLE

  COST 100;

COMMENT ON FUNCTION if(boolean, anyelement, anyelement) IS '

wrapper that prevents views from getting dropped when the util schema is reinstalled

';

CREATE OR REPLACE FUNCTION ifnull(value anyelement, null_ anyelement)

  RETURNS anyelement AS

$BODY$

SELECT util.ifnull($1, $2)

$BODY$

  LANGUAGE sql IMMUTABLE

  COST 100;

COMMENT ON FUNCTION ifnull(anyelement, anyelement) IS '

wrapper that prevents views from getting dropped when the util schema is reinstalled

';

--

-- 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 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 VIEW _plots_02_list_of_project_names AS

 SELECT projects.project_name AS projectname

   FROM projects;

--

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

--

CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS

 SELECT count(DISTINCT pm."SiteCode") 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 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 VIEW _plots_05_list_of_plot_codes_by_project AS

 SELECT p.project_name, 

    pm."SiteCode"

   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 VIEW _plots_06_list_of_plots_with_stem_measurements AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode"

   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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode"

   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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode"

   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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode"

   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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS

 SELECT p.project_name, 

    pm."SiteCode" AS plotcode, 

    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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS

 SELECT p.project_name, 

    pm."SiteCode" AS plotcode, 

    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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS

 SELECT a.project_name, 

    a.plotcode, 

    count(a.taxon) AS taxa

   FROM ( SELECT DISTINCT p.project_name, 

            pm."SiteCode" AS plotcode, 

            btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::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.plotcode

  ORDER BY a.project_name, a.plotcode;

--

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

--

CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode" AS plotcode, 

    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::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", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));

--

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

--

CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode" AS plotcode, 

    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::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", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))

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

--

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

--

CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode" AS plotcode, 

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

    avg(po.cover_percent) AS mean_cover

   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", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))))

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

--

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

--

CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode" AS plotcode, 

    btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::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", btrim(concat_ws(' '::text, ifnull(po."Family", ''::text), ifnull(po."Genus", ''::text), ifnull(po."Species", ''::text), if((po.infra_ep_1 IS NULL), ifnull(po.auth, ''::text), btrim(concat_ws(' '::text, ifnull(po.infra_rank_1, ''::text), ifnull(po.infra_ep_1, ''::text), ifnull(po.infra_auth_1, ''::text))))));

--

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

--

CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS

 SELECT p.project_name, 

    pm."SiteCode" AS plotcode, 

    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"

  ORDER BY p.project_name, pm."SiteCode";

--

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

--

CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS

 SELECT DISTINCT p.project_name, 

    pm."SiteCode" AS plotcode, 

    po."Line" AS subplot

   FROM ((projects p

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

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

  ORDER BY p.project_name, pm."SiteCode", po."Line";

--

-- PostgreSQL database dump complete

--

#!/bin/bash -e

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