Project

General

Profile

# Date Author Comment
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

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()

13157 04/17/2014 03:31 AM Aaron Marcuse-Kubitza

fix: lib/PostgreSQL-MySQL.csv: preserve schema assignments by translating `SET search_path` to `USE`

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.

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

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)

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

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

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

schemas/vegbien.sql: _specimens_10_count_number_of_records_by_institution: ran through pipeline

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.

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

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

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

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

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

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

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

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

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

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

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

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

12578 03/04/2014 05:40 PM Aaron Marcuse-Kubitza

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

12573 03/04/2014 04:56 PM Aaron Marcuse-Kubitza

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

12566 03/04/2014 03:49 PM Aaron Marcuse-Kubitza

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

12557 03/04/2014 02:20 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced with DB, which reorders ~type tables

12551 02/28/2014 11:13 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: added back keys() matchup functions, which get cascadingly deleted when the queries are re-created to change column names, etc. note that this causes the associated ~type tables to sort before them.

12550 02/28/2014 11:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added new ~type tables

12545 02/28/2014 08:15 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: rm_query_group(): support removing all validations as well as just those with a particular group prefix (and renamed to rm_output_queries())

12542 02/28/2014 07:51 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced with DB, which reorders ~type tables

12528 02/27/2014 07:08 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: added back traits* ~type tables

12527 02/27/2014 07:02 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)

12523 02/27/2014 04:37 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

12521 02/27/2014 03:51 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

12516 02/27/2014 01:27 PM Aaron Marcuse-Kubitza

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 .

12515 02/27/2014 12:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: source_by_shortname(): documented that it must be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for each row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)

12511 02/27/2014 01:11 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: location: added index on top_plot

12510 02/27/2014 01:06 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query

12509 02/27/2014 01:04 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added more ~type tables for plots queries

12507 02/26/2014 11:44 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added public_validations.rm_all_queries(schema), which removes all validations queries in a schema, or in all schemas (useful when recreating validations queries from the DDL export file)

12505 02/26/2014 10:58 PM Aaron Marcuse-Kubitza

schemas/util.sql: query_relations(): support passing in an input query instead of an output query, which will remove only in the datasource's schema. (note that it was not clear that passing in an input-side query was not previously supported.)

12503 02/26/2014 10:30 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema_anchor: clarified that this identifies this function's schema (for use in locating helper functions), but is not necessarily the schema operated on, as the comment implied

12497 02/26/2014 11:37 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: replaced custom %== operators with much simpler custom keys() functions. this avoids both the need to write out an = comparison for each field, and especially, also the need to write both an operator and a function implementing that operator.

12441 02/25/2014 11:37 AM Aaron Marcuse-Kubitza

schemas/util.sql, vegbien.sql: do not declare IMMUTABLE SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment)

12435 02/25/2014 10:29 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

12425 02/25/2014 07:52 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name

12424 02/25/2014 07:42 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(text, text) and types that use its custom FULL JOIN matching: need to use separate operator %== for determining which records to put on the same row, so that the = filter for identical rows only excludes rows that are actually the same, not all rows with the same keys (which would usually remove all rows)

12422 02/25/2014 07:14 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

12421 02/25/2014 12:24 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _plots_02_list_of_project_names: added = operator that causes FULL JOIN to only use some columns, so that rows for the same label column are put on the same row in the diff table

12418 02/24/2014 11:17 PM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

12415 02/24/2014 04:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix

12414 02/24/2014 04:50 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

12412 02/24/2014 03:26 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: public_validations._plots_* that use projectname: re-alias to project_name to match input queries. (reexporting apparently changes the relative order of the ~type tables.)

12410 02/24/2014 02:33 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully

12399 02/24/2014 12:24 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)

12389 02/23/2014 07:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources

12387 02/23/2014 07:36 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validatable_datasources()

12383 02/23/2014 07:01 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables

12367 02/23/2014 12:13 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.

12366 02/23/2014 12:08 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param

12365 02/23/2014 12:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added query_view_relation_max_prefix_len()

12352 02/22/2014 07:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query

12351 02/22/2014 07:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added new_query_view_relation_name()

12338 02/22/2014 04:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query

12336 02/22/2014 03:48 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views

12335 02/22/2014 03:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added public_validations.query_views()

12318 02/20/2014 06:14 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)

12317 02/20/2014 05:52 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: renamed output views to match input views

12316 02/20/2014 04:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)

12309 02/18/2014 07:16 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_tables()

12307 02/17/2014 09:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validation_views()

12287 02/17/2014 02:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_table()

12264 02/16/2014 11:59 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_in_view()

12261 02/16/2014 10:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)

12259 02/16/2014 09:47 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)

12258 02/16/2014 09:39 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path

12255 02/16/2014 08:55 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for