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 = "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 concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS "specificEpithet"

   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_unique_verbatim_species_without_author; Type: VIEW; Schema: NY; Owner: -

--

CREATE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS

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

   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_04_count_of_unique_verbatim_species_without_author; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_04_count_of_unique_verbatim_species_without_author IS '

Check: should return 1 row

';

--

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

--

CREATE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS

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

   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_05_list_of_verbatim_species_excluding_author; Type: COMMENT; Schema: NY; Owner: -

--

COMMENT ON VIEW _specimens_05_list_of_verbatim_species_excluding_author IS '

Check: should return 3335 rows

';

--

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

--

CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author AS

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

   FROM "Ecatalog_all"

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

--

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

--

COMMENT ON VIEW _specimens_06_count_of_unique_verb_subsp_taxa_without_author IS '

Check: should return 1 row

';

--

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

--

CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS

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

   FROM "Ecatalog_all"

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

--

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

--

COMMENT ON VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_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 btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text))))) AS "fullScientificNames"

   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 btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text)))) AS "fullScientificName"

   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(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLatitude" IS NOT NULL)) AS "allLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLatitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLats", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLongitude" IS NOT NULL)) AS "allLongs", 

    ( SELECT count(*) AS count

           FROM "Ecatalog_all"

          WHERE ("Ecatalog_all"."decimalLongitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLongs";

--

-- 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_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__main AS "localityDescription"

   FROM "Ecatalog_all"

  WHERE ("Ecatalog_all".locality__main 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 "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