validation/aggregating/pipeline/validations_on_sparse_datasources.odg: not applicable "✓": increased font size so the size of the character matches the surrounding text
validation/aggregating/pipeline/validations_on_sparse_datasources.odg: removed = lines for each input query, because they clutter up the diagram and the "same, so don't need to rewrite" message now shows this as well
validation/aggregating/pipeline/validations_on_sparse_datasources.odg: added the denormalized VegCore schema approach for comparison, as requested by Mark
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed DDL statements, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#remove-DDL-statements
validation/aggregating/specimens/qualitative_validations_specimens.sql: parameterize queries by datasource
validation/aggregating/**.sql output queries: use `SET join_collapse_limit = 1;` to match public_validations.rematerialize_out_view()
validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: turn off join_collapse_limit instead of enable_mergejoin/enable_hashjoin, because join_collapse_limit is something that we will eventually want to turn off for all queries, which would avoid this query needing special handling. (on the other hand, enable_mergejoin/enable_hashjoin may be necessary for some queries and we probably won't turn them off for all queries.)
added validation/aggregating/pipeline/validations_on_sparse_datasources.odg
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_14_count_of_all_invalid_verbatim_lat_long
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_12_distinct_collector_name_collect_num_date_w_count
validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: fixed whitespace
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed trailing whitespace
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_13_count_of_all_verbatim_and_decimal_lat_long
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_11_list_of_three_standard_political_divisions
validation/aggregating/specimens/qualitative_validations_specimens.sql: *_of_species_binomials: switched back to the old queries that use the split-apart ranks instead of the concatenated taxon name. note that these will not work on all specimens datasources, but now that #6,7 were selected to use the concatenated taxon name, this isn't a problem.
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_species_binomials: renamed columns to species_binomial to reflect reverted query name
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_verbatim_species_excluding_author: renamed to *_species_binomials for clarity
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: _specimens_04_count_of_unique_verbatim_species_with_author, _specimens_05_list_of_unique_verbatim_species_with_author: switched back to original names because #6,7 now do the same thing as #4,5, so we should include the differing result set of #4,5 for datasources that provide it
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_10_count_number_of_records_by_institution
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: use taxon_name*_with_author everywhere instead of custom column names, for consistency
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_verbatim_subspecific_taxa_without_author, etc.: renamed to *_with_author because these now use the concatenated name, rather than the without-author name that only some specimens datasources provide
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_06_count_of_unique_verb_subsp_taxa_without_author, _specimens_07_list_of_verbatim_subspecific_taxa_without_author
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_verbatim_species_without_author, etc.: renamed to *_with_author because these now use the concatenated name, rather than the without-author name that only some specimens datasources provide
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed extra ; at ends of queries
validation/aggregating/specimens/qualitative_validations_specimens.sql: use the concatenated taxon name instead of concatenating the ranks, as decided in the 2014-03-27 conference call (wiki.vegpath.org/2014-03-27_conference_call#aggregating-validations)
validation/aggregating/specimens/qualitative_validations_specimens.sql: use taxonoccurrence instead of location as the table that all specimens should have, as decided in the 2014-03-27 conference call (wiki.vegpath.org/2014-03-27_conference_call#aggregating-validations)
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: stage I: clarified that the database input is intended to be a normalized input, and its corresonding output is intended to be denormalized
bugfix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: _specimens_16_list_distinct_specimen_descriptions: should use DISTINCT
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_16_list_distinct_specimen_descriptions
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_15_list_distinct_locality_descriptions
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_09_list_of_unique_verbatim_author_taxa_with_genus
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_08_count_of_unique_verbatim_author_taxa_with_genus
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_05_list_of_verbatim_species_excluding_author
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_04_count_of_unique_verbatim_species_without_author
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_03_list_of_verbatim_families
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_02_count_of_unique_verbatim_families
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_01_count_of_total_records_specimens_in_source_db
validation/aggregating/specimens/qualitative_validations_specimens.sql: added config statements for datasource and query planner
fix: validation/aggregating/specimens/qualitative_validations_specimens.sql: use pg_dump's formatting for COMMENT ON to facilitate diffing against a pg_dump export of the DDL statements
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: removed DDL statements so that running the query file does not alter the database, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#remove-DDL-statements
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to DB, which pg_dump-formats the views
validation/**.sql: replaced CREATE OR REPLACE VIEW with CREATE VIEW to match pg_dump output for diffing
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: use pg_dump's formatting for COMMENT ON to facilitate diffing against a pg_dump export of the DDL statements
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: decimalLatitude/decimalLongitude: need to cast to double precision for numeric comparisons
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: CollectedDate: updated for refreshed NY data
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: fixed typos in column aliases
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: translated column names to VegCore, using `bin/in_place validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql env text=1 bin/repl inputs/NY/Ecatalog_all/map.csv` from the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
bugfix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: need to enclose additional mixed-case identifiers in "", using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: abbreviated view names longer than 63 chars to prevent them from being truncated
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: escape any ' inside '...' by doubling them
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: translated SQL to Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: changed /* */ comments to COMMENT ON comments, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: removed no longer needed -- comments containing the query name, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
validation/aggregating/specimens/qualitative_validations_specimens.sql: moved notes to comments to after the query
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: moved notes to comments to after the query
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: moved "Check" comments to after the query, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed "Check: should return [#] rows" comments because these only apply to the NY results, not to all specimens datasources
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: prepended CREATE VIEW, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW and the same abbreviations as the output queries (validation/aggregating/specimens/qualitative_validations_specimens.sql)
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: synced "Check" comments to output queries validation/aggregating/specimens/qualitative_validations_specimens.sql
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: enclosed mixed-case identifiers in "" using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to use column names for refreshed NY data
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: use our staging tables instead of the BIEN2 MySQL staging tables
validation/aggregating/specimens/**.sql: removed trailing whitespace, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
archived validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.sql
added validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql, copied from qualitative_validations_source_db_NYBG.sql
validation/aggregating/specimens/qualitative_validations_specimens.sql: added ; at end of `CREATE OR REPLACE VIEW` statements
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed input-query-specific comments
validation/aggregating/specimens/qualitative_validations_specimens.sql: reworded rowcount check comments to apply to the output queries
validation/aggregating/specimens/qualitative_validations_specimens.sql: shortened view names to fit within the 63-char limit without truncation
fix: validation/aggregating/specimens/qualitative_validations_specimens.sql: changed "Full inner join" to "Full outer join" because a FULL JOIN is a type of outer join, not inner join
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql: _plots_19_count_of_inventories_per_plot_in_each_project: renamed to _plots_19_count_of_censuses_per_plot_in_each_project for clarity
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added `SET enable_seqscan = off;` to match what is done by rematerialize_out_view() to run the queries properly
bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: use psql var :datasource instead of current_schema() so that the queries are runnable without special configuration of the search_path
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `CREATE OR REPLACE VIEW` so the validations views are not unintentionally replaced when running this file
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .
fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: renamed truncated views to match the output queries
bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: removed `LIMIT 10` to match output queries
fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: updated table names to match our renamings
added validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql, modified from bien3_validations_salvias_db_original.sql using the steps at http://wiki.vegpath.org/Aggregating_validations_refactoring
added validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_complete.sql from Brad's e-mail
