Revision 13367
Added by Aaron Marcuse-Kubitza about 10 years ago
| validations.sql | ||
|---|---|---|
| 15 | 15 |
-- Name: _plots_01_count_of_projects; Type: VIEW; Schema: SALVIAS; Owner: - |
| 16 | 16 |
-- |
| 17 | 17 |
|
| 18 |
CREATE VIEW _plots_01_count_of_projects AS |
|
| 18 |
CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
|
|
| 19 | 19 |
SELECT count(*) AS projects |
| 20 | 20 |
FROM projects; |
| 21 | 21 |
|
| ... | ... | |
| 24 | 24 |
-- Name: _plots_02_list_of_project_names; Type: VIEW; Schema: SALVIAS; Owner: - |
| 25 | 25 |
-- |
| 26 | 26 |
|
| 27 |
CREATE VIEW _plots_02_list_of_project_names AS |
|
| 27 |
CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
|
|
| 28 | 28 |
SELECT projects.project_name |
| 29 | 29 |
FROM projects; |
| 30 | 30 |
|
| ... | ... | |
| 33 | 33 |
-- Name: _plots_03_count_of_all_plots_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: - |
| 34 | 34 |
-- |
| 35 | 35 |
|
| 36 |
CREATE VIEW _plots_03_count_of_all_plots_in_this_source AS |
|
| 36 |
CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
|
|
| 37 | 37 |
SELECT count(DISTINCT (pm."SiteCode")::text) AS plots |
| 38 | 38 |
FROM (projects p |
| 39 | 39 |
JOIN "plotMetadata" pm ON ((p.project_id = pm.project_id))); |
| ... | ... | |
| 43 | 43 |
-- Name: _plots_04_count_of_plots_in_each_project_in_this_source; Type: VIEW; Schema: SALVIAS; Owner: - |
| 44 | 44 |
-- |
| 45 | 45 |
|
| 46 |
CREATE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS |
|
| 46 |
CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
|
|
| 47 | 47 |
SELECT p.project_name, |
| 48 | 48 |
count(*) AS plots |
| 49 | 49 |
FROM (projects p |
| ... | ... | |
| 55 | 55 |
-- Name: _plots_05_list_of_plot_codes_by_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 56 | 56 |
-- |
| 57 | 57 |
|
| 58 |
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS |
|
| 58 |
CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
|
|
| 59 | 59 |
SELECT p.project_name, |
| 60 | 60 |
(pm."SiteCode")::text AS plot_code |
| 61 | 61 |
FROM (projects p |
| ... | ... | |
| 66 | 66 |
-- Name: _plots_06_list_of_plots_with_stem_measurements; Type: VIEW; Schema: SALVIAS; Owner: - |
| 67 | 67 |
-- |
| 68 | 68 |
|
| 69 |
CREATE VIEW _plots_06_list_of_plots_with_stem_measurements AS |
|
| 69 |
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
|
|
| 70 | 70 |
SELECT DISTINCT p.project_name, |
| 71 | 71 |
(pm."SiteCode")::text AS plot_code |
| 72 | 72 |
FROM (((projects p |
| ... | ... | |
| 81 | 81 |
-- Name: _plots_06a_list_of_stems; Type: VIEW; Schema: SALVIAS; Owner: - |
| 82 | 82 |
-- |
| 83 | 83 |
|
| 84 |
CREATE VIEW _plots_06a_list_of_stems AS |
|
| 84 |
CREATE OR REPLACE VIEW _plots_06a_list_of_stems AS
|
|
| 85 | 85 |
SELECT p.project_name, |
| 86 | 86 |
(pm."SiteCode")::text AS plot_code, |
| 87 | 87 |
(s.stem_id)::text AS stem_id |
| ... | ... | |
| 96 | 96 |
-- Name: _plots_07_list_of_plots_which_use_counts_of_indiv_per_species; Type: VIEW; Schema: SALVIAS; Owner: - |
| 97 | 97 |
-- |
| 98 | 98 |
|
| 99 |
CREATE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS |
|
| 99 |
CREATE OR REPLACE VIEW _plots_07_list_of_plots_which_use_counts_of_indiv_per_species AS
|
|
| 100 | 100 |
SELECT DISTINCT p.project_name, |
| 101 | 101 |
(pm."SiteCode")::text AS plot_code |
| 102 | 102 |
FROM ((projects p |
| ... | ... | |
| 110 | 110 |
-- Name: _plots_08_list_of_plots_which_use_percent_cover; Type: VIEW; Schema: SALVIAS; Owner: - |
| 111 | 111 |
-- |
| 112 | 112 |
|
| 113 |
CREATE VIEW _plots_08_list_of_plots_which_use_percent_cover AS |
|
| 113 |
CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
|
|
| 114 | 114 |
SELECT DISTINCT p.project_name, |
| 115 | 115 |
(pm."SiteCode")::text AS plot_code |
| 116 | 116 |
FROM ((projects p |
| ... | ... | |
| 124 | 124 |
-- Name: _plots_09_list_of_plots_which_use_line_intercept; Type: VIEW; Schema: SALVIAS; Owner: - |
| 125 | 125 |
-- |
| 126 | 126 |
|
| 127 |
CREATE VIEW _plots_09_list_of_plots_which_use_line_intercept AS |
|
| 127 |
CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
|
|
| 128 | 128 |
SELECT DISTINCT p.project_name, |
| 129 | 129 |
(pm."SiteCode")::text AS plot_code |
| 130 | 130 |
FROM ((projects p |
| ... | ... | |
| 138 | 138 |
-- Name: _plots_10_count_of_individuals_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 139 | 139 |
-- |
| 140 | 140 |
|
| 141 |
CREATE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS |
|
| 141 |
CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
|
|
| 142 | 142 |
SELECT p.project_name, |
| 143 | 143 |
(pm."SiteCode")::text AS plot_code, |
| 144 | 144 |
sum(po."NoInd") AS individuals |
| ... | ... | |
| 164 | 164 |
-- Name: _plots_10a_aggregate_observation_individual_counts; Type: VIEW; Schema: SALVIAS; Owner: - |
| 165 | 165 |
-- |
| 166 | 166 |
|
| 167 |
CREATE VIEW _plots_10a_aggregate_observation_individual_counts AS |
|
| 167 |
CREATE OR REPLACE VIEW _plots_10a_aggregate_observation_individual_counts AS
|
|
| 168 | 168 |
SELECT p.project_name, |
| 169 | 169 |
(pm."SiteCode")::text AS plot_code, |
| 170 | 170 |
(po."PlotObsID")::text AS individual_id, |
| ... | ... | |
| 188 | 188 |
-- Name: _plots_11_count_of_stems_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 189 | 189 |
-- |
| 190 | 190 |
|
| 191 |
CREATE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS |
|
| 191 |
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
|
|
| 192 | 192 |
SELECT p.project_name, |
| 193 | 193 |
(pm."SiteCode")::text AS plot_code, |
| 194 | 194 |
count(DISTINCT s.stem_id) AS stems |
| ... | ... | |
| 213 | 213 |
-- Name: _plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 214 | 214 |
-- |
| 215 | 215 |
|
| 216 |
CREATE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS |
|
| 216 |
CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
|
|
| 217 | 217 |
SELECT a.project_name, |
| 218 | 218 |
a.plot_code, |
| 219 | 219 |
count(a.taxon) AS taxa |
| ... | ... | |
| 240 | 240 |
-- Name: _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p; Type: VIEW; Schema: SALVIAS; Owner: - |
| 241 | 241 |
-- |
| 242 | 242 |
|
| 243 |
CREATE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS |
|
| 243 |
CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
|
|
| 244 | 244 |
SELECT DISTINCT p.project_name, |
| 245 | 245 |
(pm."SiteCode")::text AS plot_code, |
| 246 | 246 |
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
|
| ... | ... | |
| 263 | 263 |
-- Name: _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr; Type: VIEW; Schema: SALVIAS; Owner: - |
| 264 | 264 |
-- |
| 265 | 265 |
|
| 266 |
CREATE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS |
|
| 266 |
CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
|
|
| 267 | 267 |
SELECT DISTINCT p.project_name, |
| 268 | 268 |
(pm."SiteCode")::text AS plot_code, |
| 269 | 269 |
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,
|
| ... | ... | |
| 288 | 288 |
-- Name: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro; Type: VIEW; Schema: SALVIAS; Owner: - |
| 289 | 289 |
-- |
| 290 | 290 |
|
| 291 |
CREATE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS |
|
| 291 |
CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
|
|
| 292 | 292 |
SELECT DISTINCT p.project_name, |
| 293 | 293 |
(pm."SiteCode")::text AS plot_code, |
| 294 | 294 |
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,
|
| ... | ... | |
| 315 | 315 |
-- Name: _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj; Type: VIEW; Schema: SALVIAS; Owner: - |
| 316 | 316 |
-- |
| 317 | 317 |
|
| 318 |
CREATE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS |
|
| 318 |
CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj AS
|
|
| 319 | 319 |
SELECT DISTINCT p.project_name, |
| 320 | 320 |
(pm."SiteCode")::text AS plot_code, |
| 321 | 321 |
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,
|
| ... | ... | |
| 340 | 340 |
-- Name: _plots_17_count_of_subplots_per_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 341 | 341 |
-- |
| 342 | 342 |
|
| 343 |
CREATE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS |
|
| 343 |
CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
|
|
| 344 | 344 |
SELECT p.project_name, |
| 345 | 345 |
(pm."SiteCode")::text AS plot_code, |
| 346 | 346 |
count(DISTINCT po."Line") AS subplots |
| ... | ... | |
| 355 | 355 |
-- Name: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 356 | 356 |
-- |
| 357 | 357 |
|
| 358 |
CREATE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS |
|
| 358 |
CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
|
|
| 359 | 359 |
SELECT DISTINCT p.project_name, |
| 360 | 360 |
(pm."SiteCode")::text AS plot_code, |
| 361 | 361 |
po."Line" AS subplot_code |
| ... | ... | |
| 378 | 378 |
-- Name: _plots_19_count_of_censuses_per_plot_in_each_project; Type: VIEW; Schema: SALVIAS; Owner: - |
| 379 | 379 |
-- |
| 380 | 380 |
|
| 381 |
CREATE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS |
|
| 381 |
CREATE OR REPLACE VIEW _plots_19_count_of_censuses_per_plot_in_each_project AS
|
|
| 382 | 382 |
SELECT p.project_name, |
| 383 | 383 |
(pm."SiteCode")::text AS plot_code, |
| 384 | 384 |
count(DISTINCT ARRAY[po.census_date]) AS inventories |
Also available in: Unified diff
lib/runscripts/validations.pg.sql.run: export_(): make the export idempotent for easier re-runnability