Project

General

Profile

Statistics
| Revision:
  • svn:ignore: extern

# Date Author Comment
12613 03/06/2014 11:30 AM Aaron Marcuse-Kubitza

planning/meetings/BIEN conference call availability.xlsx: updated

12612 03/06/2014 11:06 AM Aaron Marcuse-Kubitza

bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'

12611 03/06/2014 11:04 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default

12610 03/06/2014 11:03 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views

12609 03/06/2014 11:02 AM Aaron Marcuse-Kubitza

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

12608 03/06/2014 10:59 AM Aaron Marcuse-Kubitza

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

12607 03/06/2014 10:57 AM Aaron Marcuse-Kubitza

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB

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

12605 03/06/2014 08:52 AM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query

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

12603 03/06/2014 08:29 AM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed types to match output query

12602 03/06/2014 08:14 AM Aaron Marcuse-Kubitza

bugfix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed summarizing column from mean_cover->totalpercentcover to match output query

12601 03/06/2014 08:12 AM Aaron Marcuse-Kubitza

bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query

12600 03/06/2014 02:18 AM Aaron Marcuse-Kubitza

bugfix: schemas/Makefile: `%/install: vegbien.sql`: also need to match `public_validations` when used as a schema-qualifier (public_validations._), and after a cast (::) to a schema-qualified type. these occur in schema-qualified casts to the custom return type in the keys() functions.

12599 03/06/2014 01:59 AM Aaron Marcuse-Kubitza

bugfix: schemas/Makefile: `%/install: vegbien.sql`: sed expr: need to use '' instead of "" because $(*q) may contain "

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

12595 03/06/2014 12:00 AM Aaron Marcuse-Kubitza

schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)

12594 03/05/2014 11:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(): create the keys() function directly from the keys record type, to support creating just a custom keys record type rather than also a custom keys() function (which is fairly complicated to do, thanks to the need to define a separate custom return type)

12593 03/05/2014 01:01 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: relation_type(relation_type_char "char"): need to handle TYPEs as well, because these are also listed in pg_class (and should be deleted by drop_relations_like())

12592 03/05/2014 12:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: added show_types_like()

12591 03/05/2014 12:42 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

12590 03/05/2014 12:25 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

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)

12588 03/05/2014 07:28 AM Aaron Marcuse-Kubitza

schemas/util.sql: added relation_type(regtype) so drop_relation() would work on TYPEs, too. TYPEs are sometimes used as a function return type linked to a particular table (eg. in the keys() functions), and should be dropped along with the table by util.drop_relations_like().

12587 03/05/2014 07:24 AM Aaron Marcuse-Kubitza

schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()

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

12581 03/04/2014 05:52 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use `CREATE FUNCTION` instead of `CREATE OR REPLACE FUNCTION` so that the function is only created if it does not exist, to avoid overwriting a custom keys() function

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

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

12579 03/04/2014 05:47 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()

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

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

12577 03/04/2014 05:39 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use named return type to avoid complicated edge cases with 1-column records, which prevent (values_(...)).* from working correctly

12576 03/04/2014 05:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_typed_cols_list(col_cast[])

12575 03/04/2014 05:20 PM Aaron Marcuse-Kubitza

schemas/util.sql: added prefixed_name()

12574 03/04/2014 05:18 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity

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

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

12572 03/04/2014 04:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: util.remake_diff_table(): viewing the table in human-readable form: use just the output side's values_() columns, to avoid repeating columns on both sides that are the same and put just the changed columns side-by-side

12571 03/04/2014 04:45 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword

12570 03/04/2014 04:31 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table

12569 03/04/2014 04:19 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function

12568 03/04/2014 03:55 PM Aaron Marcuse-Kubitza

schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error

12567 03/04/2014 03:53 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.create_if_not_exists() to avoid trying to overwrite a custom keys() function the user has written

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

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

12565 03/04/2014 03:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: diff(text, text): automatically create a keys() function for the base type. this avoids the need to create keys() functions manually for the numerous queries that need them.

12564 03/04/2014 03:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_keys_func(regtype)

12563 03/04/2014 03:34 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): need to handle 1-column records specially, because Postgres does not allow returning a 1-column record when there are OUT params

12562 03/04/2014 03:02 PM Aaron Marcuse-Kubitza

schemas/util.sql: added typed_cols(regtype)

12561 03/04/2014 02:29 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_keys_func(regtype, col_cast[])

12560 03/04/2014 02:27 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)

12559 03/04/2014 02:23 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_out_params(): need COALESCE around string_agg()

12558 03/04/2014 02:22 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL

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

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

12556 03/04/2014 01:59 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_out_params()

12555 03/04/2014 01:42 AM Aaron Marcuse-Kubitza

schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names

12554 03/04/2014 01:40 AM Aaron Marcuse-Kubitza

schemas/util.sql: mk_diff_query(): removed special handling for CROSS JOIN because this is now handled by diff(text, text) using `FULL JOIN ON true`. this simplification allows mk_diff_query() to contain just the template structure of the FULL JOIN, without _if() calls that decrease readability.

12553 03/04/2014 01:34 AM Aaron Marcuse-Kubitza

schemas/util.sql: diff(text, text): handle the CROSS JOIN special case using `FULL JOIN ON true`, to allow mk_diff_query() to support just a straighforward FULL JOIN. this also ensures that all diff queries use the same FULL JOIN template.

12552 02/28/2014 11:17 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()

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

12549 02/28/2014 10:45 PM Aaron Marcuse-Kubitza

fix: lib/phpPgAdmin.login.php.diff: "For user public_, leave password blank" instruction: moved this to right under the Password field and increased the font size so people would be more likely to see it

12548 02/28/2014 10:28 PM Aaron Marcuse-Kubitza

/Makefile: added separate phppgadmin-Linux target to avoid needing to run the entire postgres-Linux target whenever http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down (after some system updates)

12547 02/28/2014 10:03 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: rm_all_queries(schema): documented this function's unexpected behavior when schema = public_validations (it removes in all schemas, not just public_validations). this is because this uses rm_query_view().

12546 02/28/2014 09:49 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries

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

12544 02/28/2014 07:57 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: public_validations: schema comment: removed incorrect "" in `SET search_path = "public_validations", pg_catalog;` now that they are no longer auto-added

12543 02/28/2014 07:56 PM Aaron Marcuse-Kubitza

bugfix: schemas/Makefile: `%/install: vegbien.sql`: don't enclose public_validations in "" because this causes a comment that says to search for `SET search_path = public_validations, pg_catalog;` to become mangled

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

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

12541 02/28/2014 07:34 PM Aaron Marcuse-Kubitza

fix: schemas/Makefile: moved comment about publishing a schema to %/publish

12540 02/28/2014 05:27 PM Aaron Marcuse-Kubitza

lib/Firefox_bookmarks.reformat.csv: "page's description": changed to "page's own description" to clarify that this is a description provided by the page itself

12539 02/28/2014 05:25 PM Aaron Marcuse-Kubitza

web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for error reporting levels and how to hide stack traces in psql. SQL: added links for recursive queries, which can be used to traverse hierarchical (parent-pointer) tables.

12538 02/27/2014 07:56 PM Aaron Marcuse-Kubitza

fix: inputs/SALVIAS/validations.sql: renamed SiteCode to plot_code to match output queries

12537 02/27/2014 07:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

12536 02/27/2014 07:49 PM Aaron Marcuse-Kubitza

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

12535 02/27/2014 07:45 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.

12534 02/27/2014 07:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()

12533 02/27/2014 07:37 PM Aaron Marcuse-Kubitza

schemas/util.sql: raise(type text, msg text): documented the possible options for the type param

12532 02/27/2014 07:34 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements

12531 02/27/2014 07:32 PM Aaron Marcuse-Kubitza

schemas/util.sql: eval(): added verbose_ option like for eval2set()

12530 02/27/2014 07:28 PM Aaron Marcuse-Kubitza

schemas/util.sql: added raise(type text, msg text)

12529 02/27/2014 07:09 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)

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)

12526 02/27/2014 06:58 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability

12525 02/27/2014 06:42 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: rm_all_queries(): all-schemas mode: also remove queries that are only present on the input side, so that the input side can also be re-created from the DDL file

12524 02/27/2014 06:36 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: rm_all_queries(): documented that this actually does remove queries in all schemas when schema is NULL, because rm_query_view(public_validations.view) searches all schemas

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)

12522 02/27/2014 04:12 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)

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)

12520 02/27/2014 02:20 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)

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

schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql

12518 02/27/2014 02:05 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)

12517 02/27/2014 01:49 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: rm_query_group(): documented that this only removes validations output queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB

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)

12514 02/27/2014 07:59 AM Aaron Marcuse-Kubitza

schemas/util.sql: eval2val(): made it STABLE so that functions that use it can themselves be inlined. (Postgres apparently ignores the STABLE qualifier, without warning, if the function contains any VOLATILE statements.)