Project

General

Profile

# Date Author Comment
13441 05/13/2014 03:46 AM Aaron Marcuse-Kubitza

bugfix: inputs/.TNRS/schema.sql: taxon_scrub, etc.: undid rename of accepted name columns to scrubbed_* (r13435), because these are actually not the same (scrubbed_* is the combination of accepted and no-opinion names). the accepted name columns will now be named accepted_*, following the standard naming scheme.

13440 05/13/2014 03:28 AM Aaron Marcuse-Kubitza

bugfix: schemas/public_.sql: _plots_20_tnrs_names: morphospeciesSuffix must still be called morphospecies_suffix, not taxon_morphospecies, because that column has not been updated to the taxon_morphospecies yet (fixes r13435)

13439 05/13/2014 03:13 AM Aaron Marcuse-Kubitza

fix: inputs/.TNRS/schema.sql: taxon_scrub, etc.: scrubbed_*: use columns from MatchedTaxon whenever possible, to as much as possible avoid the need to join to taxon_scrub.scrubbed_unique_taxon_name.*

13438 05/13/2014 02:53 AM Aaron Marcuse-Kubitza

schemas/Makefile: vegbien.sql: exclude the same set of Source tables excluded by inputs/.TNRS/schema.sql, so that reinstalling TNRS doesn't change the contents of this file

13435 05/12/2014 04:28 PM Aaron Marcuse-Kubitza

schemas/public_.sql: _plots_20_tnrs_names: changed to set of columns requested by Martha

13433 05/10/2014 09:17 PM Aaron Marcuse-Kubitza

schemas/public_.sql: _plots_20_tnrs_names: added morphospecies suffix in order to also validate it

13432 05/10/2014 09:13 PM Aaron Marcuse-Kubitza

schemas/public_.sql: _plots_20_tnrs_names: added verbatim name in order to also validate the formation of the concatenated name

13431 05/10/2014 08:49 PM Aaron Marcuse-Kubitza

schemas/public_.sql: merged _plots_20_tnrs_input_names, _plots_21_tnrs_output_names into _plots_20_tnrs_names so the input and output names can be compared side-by-side

13430 05/10/2014 08:10 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced to DB, which adds Source.errors table indexes added in the import

13398 05/03/2014 01:37 AM Aaron Marcuse-Kubitza

schemas/public_.sql: _plots_20_tnrs_input_names, _plots_21_tnrs_output_names: added Brad's comments

13394 05/02/2014 11:34 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_21_tnrs_output_names: use scrubbed_unique_taxon_name (concatenated output name) because this is more similar to the concatenated name used in _plots_20_tnrs_input_names

13390 05/02/2014 10:01 PM Aaron Marcuse-Kubitza

bugfix: schemas/public_.sql: _plots_21_tnrs_output_names: need to use TNRS output rather than input name

13389 05/02/2014 09:54 PM Aaron Marcuse-Kubitza

schemas/public_.sql: added _plots_20_tnrs_input_names, _plots_21_tnrs_output_names

13388 05/02/2014 06:17 PM Aaron Marcuse-Kubitza

schemas/public_.sql: public_validations: schema comment: documented how to run just the output queries on a particular datasource

13387 05/02/2014 04:53 PM Aaron Marcuse-Kubitza

fix: schemas/public_.sql: rm_all_queries(): also remove output-only result tables

13386 05/02/2014 04:50 PM Aaron Marcuse-Kubitza

schemas/public_.sql: query_view_regexp(): also match auxiliary tables

13385 05/02/2014 04:14 PM Aaron Marcuse-Kubitza

schemas/public_.sql: added remake_output_tables(datasource_schema, prefix)

13383 05/01/2014 10:38 PM Aaron Marcuse-Kubitza

schemas/public_.sql: added output_queries(prefix)

13382 05/01/2014 10:27 PM Aaron Marcuse-Kubitza

schemas/public_.sql: validation_views(): added prefix param, which can be used for additional subsetting

13381 05/01/2014 10:13 PM Aaron Marcuse-Kubitza

schemas/public_.sql: query_relations(): removed no longer used max_prefix_len default param

13380 05/01/2014 10:05 PM Aaron Marcuse-Kubitza

schemas/public_.sql: query_relations(): use new public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len

13379 05/01/2014 10:03 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len

13378 05/01/2014 08:55 PM Aaron Marcuse-Kubitza

schemas/public_.sql: rm_output_queries(), validation_views(): use public_validations.query_view_regexp()

13377 05/01/2014 08:42 PM Aaron Marcuse-Kubitza

schemas/public_.sql: added public_validations.query_view_regexp()

13376 05/01/2014 08:06 PM Aaron Marcuse-Kubitza

bugfix: schemas/public_.sql: public_validations.rm_output_queries(): default regexp needs to be anchored at beginning of string, and should match the regexp in public_validations.validation_views()

13356 04/30/2014 05:36 PM Aaron Marcuse-Kubitza

schemas/public_.sql: _km_to_m(): made it a wrapper around util._km_to_m() so this function is only in one place

13154 04/17/2014 02:39 AM Aaron Marcuse-Kubitza

schemas/Makefile: vegbien.sql: also include geoscrub, TNRS schemas, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema). this involves having a separate public_.sql file for restoring the public schema.

13147 04/16/2014 04:24 PM Aaron Marcuse-Kubitza

bugfix: inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13*: also need to include coordinate pairs which have one of their coordinates NULL, by using OR instead of AND

13144 04/16/2014 03:13 PM Aaron Marcuse-Kubitza

inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: added breakdowns _specimens_13a_list_of_all_verbatim_lat_long, _specimens_13b_list_of_all_decimal_lat_long to help troubleshoot the diff

13143 04/16/2014 02:04 PM Aaron Marcuse-Kubitza

fix: inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: count lat/longs together instead of separately, because the DISTINCT is by coordinate pair, not individual coordinate value (which wouldn't make much sense)

13142 04/15/2014 08:12 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: rm_output_queries(): need to account for the fact that util.truncated_prefixed_name_regexp() returns a whole-string regexp. this drops support for removing output queries with a particular group prefix, which we no longer use.

13141 04/15/2014 07:59 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: rm_output_queries(): need to include relations whose names were truncated, as well

13140 04/15/2014 07:14 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: public_validations schema comment: to remove a validations query so its columns can be changed: use rm_output_queries() rather than rm_query_view() because that also removes input queries

13133 04/14/2014 05:04 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: DISTINCT: added coordsaccuracy_m

13132 04/14/2014 05:02 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: coordinates_unique: added coordsaccuracy_m

13131 04/14/2014 04:56 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: need to DISTINCT the values that are being counted, because the coordinates_unique unique constraint includes other columns as well, so there may be multiple instances of each lat/long

13120 04/10/2014 03:41 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: specimens*_of_unique_verb_subsp_taxa_with_author: include only names with subspecies (filtering by taxonverbatim.subspecies rather than taxonlabel.taxonomicname)

13106 04/10/2014 11:41 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonverbatim: added subspecies, as decided in the conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema-2)

13105 04/10/2014 06:54 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: plots* with duplicated rows: removed duplicated rows

13104 04/10/2014 06:45 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: specimens*: ran through pipeline

13100 04/10/2014 06:07 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _specimens_16_list_distinct_specimen_descriptions: re-ran through pipeline after removing duplicated rows

13099 04/10/2014 06:02 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: rm_output_queries(): also support removing just a particular output query

13094 04/10/2014 03:33 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _specimens_11_list_of_three_standard_political_divisions: ran through pipeline

13093 04/10/2014 03:31 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _specimens_11_list_of_three_standard_political_divisions: use same column names as input query

13091 04/10/2014 02:40 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: ran through pipeline

13090 04/10/2014 02:38 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: dateCollected: also need to convert to text in GROUP BY/ORDER BY

13087 04/10/2014 02:07 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql, inputs/NY/validations.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: dateCollected: cast this to text rather than date because some values for this field are not valid dates and will throw an error if cast to date

13084 04/09/2014 02:55 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _specimens_10_count_number_of_records_by_institution: ran through pipeline

13082 04/09/2014 02:46 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_10_count_number_of_records_by_institution: need to dereference specimenreplicate.duplicate_institutions_sourcelist_id to the corresponding sourcelist.name

13081 04/09/2014 02:40 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations._specimens_*: added comments from validation/aggregating/specimens/qualitative_validations_specimens.sql

13071 04/08/2014 01:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: remake_diff_tables(schema text): removed bien2_traits runtime because this applies only to one datasource. the bien2_traits runtime is now documented in inputs/bien2_traits/run.

13069 04/08/2014 01:38 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: schema comment: documented how to run the validations. this information is also in the usage comment for public_validations.remake_diff_table(), but is copied here for easy reference.

13066 04/07/2014 06:21 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: specimens queries: added autogenerated ~type tables

13063 04/07/2014 06:07 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: added specimens queries to pipeline

13060 04/07/2014 05:17 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: public_validations.rematerialize_out_view(text, regclass): run with join_collapse_limit = 1 to fix query planner issues. this option has been tested on the queries that do not yet use the standard join sequence (plots #11,12,13,14,16,17,18), and all of these queries also work fine with join_collapse_limit = 1. (the standard join sequence is used to ensure both correctness of the query and compatibility with join_collapse_limit = 1, but in some cases is not needed for join_collapse_limit.)

12994 03/30/2014 06:28 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: schemas/vegbien.sql(): need to util.use_schema(schema_anchor) before initializing vars that use own-schema functions

12966 03/28/2014 07:17 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: updated _specimens_01_count_of_total_records_specimens_in_source_db

12933 03/27/2014 08:04 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: added _specimens_01_count_of_total_records_specimens_in_source_db

12886 03/24/2014 05:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: specimenreplicate.institution_id: renamed to duplicate_institutions_sourcelist_id, as decided in the conference calls (wiki.vegpath.org/2014-03-13_conference_call#schema-changes-2)

12687 03/13/2014 06:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_08_list_of_plots_which_use_percent_cover, _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: reran with fixes, which removes the incorrectly auto-added copies columns. (they were only able to be auto-added because the tables had no rows.)

12680 03/13/2014 05:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project

12675 03/13/2014 01:50 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: schema comment: changed "to sync the queries with schemas/vegbien.sql" to "to reset the queries to what's in schemas/vegbien.sql" for clarity

12674 03/13/2014 01:46 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: schema comment: to reset the key and value columns for all validations queries: updated running of custom keys() functions to use keys() types instead

12673 03/13/2014 01:14 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema comment: to sync the queries with schemas/vegbien.sql: use new public_validations.rm_output_queries() instead of rm_all_queries() to leave the input queries in place

12672 03/13/2014 01:12 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries

12669 03/12/2014 11:49 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query

12666 03/12/2014 06:26 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row

12665 03/12/2014 06:18 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)

12648 03/12/2014 04:44 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query

12647 03/12/2014 04:41 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj

12644 03/12/2014 03:35 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept

12643 03/12/2014 03:20 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: queries that use EXISTS: join locationevent.plot_id to plot.plot_id directly instead of going via location.plot_location_id

12642 03/12/2014 03:04 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover

12640 03/12/2014 12:01 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species

12635 03/07/2014 10:49 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_06a_list_of_stems, for use in figuring out the diff in _plots_06_list_of_plots_with_stem_measurements

12633 03/07/2014 09:50 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id

12632 03/07/2014 09:41 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.

12631 03/07/2014 09:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location

12630 03/07/2014 08:55 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id

12629 03/07/2014 04:42 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: only include stemobservation records which have actual stem IDs, not merely stem-related measurements (DBH, etc.)

12626 03/07/2014 05:35 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: LEFT JOIN to project instead of inner joining, to get Postgres to use the right query plan. this is the last change needed to make query #6 runnable.

12625 03/07/2014 05:25 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: rematerialize_out_view(): run all queries with `SET enable_seqscan = off` to avoid slow query plans. this fixes _plots_06_list_of_plots_with_stem_measurements and significantly speeds up _plots_10_count_of_individuals_per_plot_in_each_project (and possibly others).

12624 03/07/2014 05:23 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)

12623 03/07/2014 04:53 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries

12621 03/06/2014 10:45 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: source_by_shortname(): documented that in some cases, it is actually a bad idea to use a nested SELECT, because this will prevent Postgres from using an index scan (causing an equally bad slowdown as not inlining in cases where a nested SELECT is required).

12619 03/06/2014 10:21 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)

12615 03/06/2014 07:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)

12614 03/06/2014 07:30 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: location: added plot_source_id index to provide the equivalent of the location.source_id index for outer plots. this will help Postgres choose the right query plans in queries involving outer plots.

12606 03/06/2014 08:57 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table

12604 03/06/2014 08:31 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table

12598 03/06/2014 12:33 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated

12597 03/06/2014 12:09 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations schema comment: documented how to remove a validations query so its columns can be changed (use public_validations.rm_query_view())

12596 03/06/2014 12:07 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_10a_aggregate_observation_individual_counts, for use in debugging diffs in _plots_10_count_of_individuals_per_plot_in_each_proj

12589 03/05/2014 12:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)

12586 03/05/2014 07:23 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _plots_10_count_of_individuals_per_plot_in_each_project: fixed to handle subplots properly, using new locationevent.place_visit_id

12585 03/05/2014 01:02 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.

12584 03/05/2014 12:59 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.

12583 03/05/2014 12:32 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger

12582 03/04/2014 05:56 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix

12580 03/04/2014 05:48 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries