Project

General

Profile

Statistics
| Revision:
  • svn:ignore: *.bak *.log

# Date Author Comment
13385 05/02/2014 04:14 PM Aaron Marcuse-Kubitza

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

13384 05/02/2014 03:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: added loop_ignore_errors()

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

13355 04/30/2014 05:32 PM Aaron Marcuse-Kubitza

schemas/util.sql: added _km_to_m() for use when public._km_to_m() is not defined yet

13274 04/22/2014 03:57 PM Aaron Marcuse-Kubitza

schemas/postgresql.conf: wal_level: set to hot_standby to enable online backup with pg_start_backup()

13158 04/17/2014 03:32 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: regenerated exports

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`

13156 04/17/2014 02:54 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: regenerated exports

13155 04/17/2014 02:53 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: added geoscrub, TNRS tables, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema)

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.

13153 04/16/2014 11:02 PM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: regenerated exports

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

13139 04/15/2014 07:00 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: is_castable(): need to pass NULL through, for proper NULL propagation

13136 04/15/2014 06:12 PM Aaron Marcuse-Kubitza

schemas/util.sql: added is_castable()

13135 04/15/2014 06:10 PM Aaron Marcuse-Kubitza

schemas/util.sql: added try_cast()

13134 04/15/2014 05:51 PM Aaron Marcuse-Kubitza

schemas/util.sql: added util.cast(), which allows casting to an arbitrary type without eval()

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

13098 04/10/2014 05:26 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: remake_diff_table(): need to rm_freq() type_table, because left/right_table don't have freq yet

13097 04/10/2014 05:18 AM Aaron Marcuse-Kubitza

schemas/util.sql: auto_rm_freq(): use new rm_freq()

13096 04/10/2014 05:17 AM Aaron Marcuse-Kubitza

schemas/util.sql: added rm_freq(regclass[])

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

13092 04/10/2014 03:10 AM Aaron Marcuse-Kubitza

schemas/util.sql: remake_diff_table(): result table comment: documented how to display NULL values that are extra or missing

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

12968 03/29/2014 04:06 AM Aaron Marcuse-Kubitza

*{.sh,run}: runscript targets: use begin_target instead of echo_func so the target name is properly echoed. note that this requires using with_rm so that $rm is properly progagated to applicable invoked targets. (previously, $rm was progagated to all invoked targets. note that with_rm only works inside a runscript target that starts with begin_target.)

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

schemas/vegbien.sql: updated _specimens_01_count_of_total_records_specimens_in_source_db

12934 03/27/2014 08:06 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: regenerated exports

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)

12880 03/24/2014 04:50 PM Aaron Marcuse-Kubitza

fix: schemas/VegCore/mk_derived: added `EOF` at end to avoid (benign) "here-document delimited by end-of-file" warnings on Linux

12874 03/24/2014 12:45 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: trim(): by default, cascadingly drop dependent columns so that they don't prevent trim() from succeeding. note that this requires the dependent columns to then be manually re-created.

12819 03/21/2014 06:58 PM Aaron Marcuse-Kubitza

added schemas/VegCore.ERD.pdf symlink for easy access

12789 03/20/2014 10:53 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)

12779 03/20/2014 07:58 PM Aaron Marcuse-Kubitza

*{.sh,run}: use new begin_target instead of `echo_func; set_make_vars`

12756 03/18/2014 05:26 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output

12755 03/18/2014 05:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added fold_explain_msg()

12734 03/15/2014 05:47 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: set_col_types(): need to COALESCE the executed SQL to '' because util.eval() does not support NULL (and shouldn't, because this indicates a missing COALESCE in constructing the statement)

12733 03/15/2014 05:43 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()

12732 03/15/2014 05:37 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY) for clarity

12725 03/15/2014 05:00 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_not_null()

12688 03/14/2014 06:38 AM Aaron Marcuse-Kubitza

added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad

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

12686 03/13/2014 06:42 PM Aaron Marcuse-Kubitza

bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query

12685 03/13/2014 06:40 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)

12684 03/13/2014 06:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: try_create(): also handle "child table is missing column" errors

12683 03/13/2014 05:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE

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

schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project

12676 03/13/2014 02:06 AM Aaron Marcuse-Kubitza

schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block

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

12671 03/12/2014 11:56 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types

12670 03/12/2014 11:53 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())

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

12668 03/12/2014 11:40 PM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_func_call(text) and use it where applicable

12667 03/12/2014 11:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match

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)

12664 03/12/2014 05:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.

12663 03/12/2014 05:40 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s

12662 03/12/2014 05:38 PM Aaron Marcuse-Kubitza

schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s

12661 03/12/2014 05:36 PM Aaron Marcuse-Kubitza

schemas/util.sql: added freq_always_1(regclass[])

12660 03/12/2014 05:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: added drop_column(regclass[])

12659 03/12/2014 05:04 PM Aaron Marcuse-Kubitza

schemas/util.sql: added parent(regclass)

12658 03/12/2014 04:48 PM Aaron Marcuse-Kubitza

schemas/util.sql: try_create(): also handle not_null_violation, which is thrown when trying to add a NOT NULL column to a parent table, which cascades to a child table whose values for the new column will be NULL

12657 03/12/2014 04:44 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(text, text): also need to cast left_/right_ to base type for the IS DISTINCT FROM filter, because the WHERE clause apparently does not use columns from the SELECT list, even though GROUP BY and ORDER BY do

12656 03/12/2014 04:13 PM Aaron Marcuse-Kubitza

schemas/util.sql: added to_freq(regclass, drop_if_always_1)