Activity
From 02/27/2014 to 03/28/2014
03/28/2014
- 07:17 AM Revision 12966: schemas/vegbien.sql: updated _specimens_01_count_of_total_records_specimens_in_source_db
- 07:10 AM Revision 12965: 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)
- 07:03 AM Revision 12964: lib/runscripts/util.run: support conventional main() method as well as `all` target
- 03:03 AM Task #562 (New): flatten the mappings
- normalized VegCore's @traceable.id_by_source@ now provides an alternate pkey that can be used for duplicate-merging, ...
- 02:55 AM Task #539 (Rejected): get analytical_stem_view to use merge joins instead of hash joins
- the query planner is likely right that hash joins are faster when joining entire tables rather than just the first fe...
- 02:53 AM Task #440: aggregating validations of imports
- see [[Aggregating validations]]
- 02:52 AM Task #290 (Resolved): benchmark tests for database loading
- this is now the [[Aggregating validations]]
- 02:39 AM Revision 12963: fix: inputs/*/*/map.csv: remapped occurrenceID-mapped fields to dataProviderRecordID when these were not globally unique DwC occurrenceIDs (http://rs.tdwg.org/dwc/terms/#occurrenceID)
- 02:34 AM Revision 12962: fix: inputs/CTFS/AggregateObservation/map.csv: field mapped to occurrenceID: remapped to aggregateOrganismObservationID because these are not specimen occurrences
- 02:32 AM Revision 12961: fix: mappings/VegCore-VegBIEN.csv: taxonoccurrence.sourceaccessioncode: need to populate from aggregateOrganismObservationID when only that is available
- 02:03 AM Revision 12960: bugfix: inputs/NY/Ecatalog_all/map.csv: can't use CatalogNumber as pkey because it's not unique and not always populated. this fixes the NY NULL accessionNumbers bug (wiki.vegpath.org/Aggregating_validations_status#bugs).
- 01:31 AM Revision 12959: /README.TXT: moved "to back up e-mails" and "to back up the version history" before settings backup so that the local backup of these is up to date when everything gets backed up
- 01:29 AM Revision 12958: inputs/XAL/Specimen/header.csv: updated
- 12:45 AM Revision 12957: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: backups/TNRS.backup: do this before the general sync so that any reverse sync that's needed won't include it
- 12:44 AM Revision 12956: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: backups/TNRS.backup: use bin/sync_upload now that this works for rsync-ignored files
- 12:36 AM Revision 12955: bugfix: lib/sh/sync.sh: don't unintentionally rsync-ignore explicitly-specified files
- 12:32 AM Revision 12954: lib/sh/util.sh: filesystem: added is_*(), could_be_*()
- 12:31 AM Revision 12953: lib/sh/util.sh: added contains_match()
- 12:31 AM Revision 12952: lib/sh/util.sh: added ends_with()
03/27/2014
- 11:13 PM Revision 12951: fix: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: run `up` on all machines, not just jupiter, because all must be up-to-date to avoid extraneous diffs
- 11:11 PM Revision 12950: bugfix: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: `svn up` on jupiter: need to use up alias because that adds --force
- 11:10 PM Revision 12949: bugfix: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: added `svn up` on jupiter: needs to be in main dir (~/bien), not ~/Dropbox/svn/
- 11:08 PM Revision 12948: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: added `svn up` on jupiter to avoid extraneous diffs when rsyncing
- 10:41 AM Revision 12947: planning/workflow/bien3_architecture/stage_I.png, stages.png: synced to bien3_architecture.pptx
- 10:32 AM Revision 12946: planning/workflow/bien3_architecture.pptx: stage I: clarified that the database input is intended to be a *normalized* input, and its corresonding output is intended to be *denormalized*
- 10:29 AM Revision 12945: 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*
- 09:02 AM Revision 12944: bugfix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: _specimens_16_list_distinct_specimen_descriptions: should use DISTINCT
- 09:01 AM Revision 12943: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_16_list_distinct_specimen_descriptions
- 09:00 AM Revision 12942: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_16_list_distinct_specimen_descriptions
- 08:53 AM Revision 12941: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_15_list_distinct_locality_descriptions
- 08:48 AM Revision 12940: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_09_list_of_unique_verbatim_author_taxa_with_genus
- 08:47 AM Revision 12939: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_08_count_of_unique_verbatim_author_taxa_with_genus
- 08:36 AM Revision 12938: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_05_list_of_verbatim_species_excluding_author
- 08:35 AM Revision 12937: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_04_count_of_unique_verbatim_species_without_author
- 08:23 AM Revision 12936: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_03_list_of_verbatim_families
- 08:18 AM Revision 12935: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_02_count_of_unique_verbatim_families
- 08:06 AM Revision 12934: schemas/vegbien.ERD.mwb: regenerated exports
- 08:04 AM Revision 12933: schemas/vegbien.sql: public_validations: added _specimens_01_count_of_total_records_specimens_in_source_db
- 07:35 AM Revision 12932: validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_01_count_of_total_records_specimens_in_source_db
- 07:34 AM Revision 12931: validation/aggregating/specimens/qualitative_validations_specimens.sql: added config statements for datasource and query planner
- 05:06 AM Revision 12930: web/links/index.htm: updated to Firefox bookmarks: Firefox: added instructions for enabling security.password_lifetime and making all tabs load when the browser is opened
- 04:43 AM Revision 12929: /README.TXT: Schema changes: manually apply schema changes to the live public schema: moved under "update mappings and staging table column names" because this is a necessary part of that step
- 04:43 AM Revision 12928: /README.TXT: Schema changes: manually apply schema changes to the live public schema: moved under "update mappings and staging table column names" because this is a necessary part of that step
- 04:40 AM Revision 12927: /README.TXT: Schema changes: changed "update staging table column names" to "update mappings and staging table column names"
- 04:13 AM Revision 12926: 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
- 04:07 AM Revision 12925: 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
- 04:01 AM Revision 12924: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to DB, which pg_dump-formats the views
- 03:57 AM Revision 12923: validation/**.sql: replaced CREATE OR REPLACE VIEW with CREATE VIEW to match pg_dump output for diffing
- 03:36 AM Revision 12922: added inputs/NY/validations*.sql*
- 03:34 AM Revision 12921: 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
- 03:31 AM Revision 12920: bugfix: lib/common.Makefile: $(add*): need to wrap w/ $(wildcard) to prevent "targets don't exist" error, because svn 1.7 does not suppress this error even with --force
- 03:27 AM Revision 12919: bugfix: inputs/input.Makefile: add!: add* of $(svnFiles): need to ignore errors because svn 1.7 does not suppress the "targets don't exist" error even with --force
03/26/2014
- 09:34 PM Revision 12918: fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: decimalLatitude/decimalLongitude: need to cast to double precision for numeric comparisons
- 09:33 PM Revision 12917: fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: CollectedDate: updated for refreshed NY data
- 09:30 PM Revision 12916: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: fixed typos in column aliases
- 09:23 PM Revision 12915: 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
- 09:23 PM Revision 12914: fix: bin/repl: text mode (whether all patterns are plain text) should default to on, not off, if matching entire cells in a spreadsheet
- 07:16 PM Revision 12913: 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
- 07:15 PM Revision 12912: 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
- 06:09 PM Revision 12911: 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
- 06:07 PM Revision 12910: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: escape any ' inside '...' by doubling them
- 06:04 PM Revision 12909: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: translated SQL to Postgres
- 05:32 PM Revision 12908: 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
- 04:58 PM Revision 12907: 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
- 03:47 PM Revision 12906: validation/aggregating/specimens/qualitative_validations_specimens.sql: moved notes to comments to after the query
- 03:46 PM Revision 12905: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: moved notes to comments to after the query
- 03:44 PM Revision 12904: 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
- 03:22 PM Revision 12903: 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
- 03:16 PM Revision 12902: 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)
- 03:01 PM Revision 12901: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: synced "Check" comments to output queries validation/aggregating/specimens/qualitative_validations_specimens.sql
- 02:49 PM Revision 12900: 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
- 02:37 PM Revision 12899: 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
- 02:29 PM Revision 12898: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to use column names for refreshed NY data
- 02:17 PM Revision 12897: fix: bin/repl: don't consider uppercase SQL keywords to indicate that a word is in a sentence
- 12:02 AM Revision 12896: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: use our staging tables instead of the BIEN2 MySQL staging tables
03/25/2014
- 11:52 PM Revision 12895: validation/aggregating/specimens/**.sql: removed trailing whitespace, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
- 11:39 PM Revision 12894: archived validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.sql
- 11:39 PM Revision 12893: added validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql, copied from qualitative_validations_source_db_NYBG.sql
- 11:33 PM Revision 12892: validation/aggregating/specimens/qualitative_validations_specimens.sql: added ; at end of `CREATE OR REPLACE VIEW` statements
- 04:18 AM Revision 12891: inputs/run: postprocess(): documented runtime on vegbiendev (1 h)
03/24/2014
- 06:22 PM Revision 12890: validation/aggregating/specimens/qualitative_validations_specimens.sql: removed input-query-specific comments
- 06:21 PM Revision 12889: validation/aggregating/specimens/qualitative_validations_specimens.sql: reworded rowcount check comments to apply to the output queries
- 06:18 PM Revision 12888: validation/aggregating/specimens/qualitative_validations_specimens.sql: shortened view names to fit within the 63-char limit without truncation
- 05:45 PM Revision 12887: /README.TXT: `make inputs/{NVS,SALVIAS,TEAM}/test`: updated runtime (1 min)
- 05:35 PM Revision 12886: 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)
- 05:32 PM Revision 12885: inputs/run: postprocess(): updated runtime (25 min)
- 05:22 PM Revision 12884: 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
- 05:04 PM Revision 12883: /README.TXT: calls to `inputs/run postprocess`: direct user to refer to inputs/run for this, so the runtime doesn't have to be updated in multiple places
- 05:02 PM Revision 12882: inputs/run: postprocess(): updated runtime (20 min)
- 05:01 PM Revision 12881: /README.TXT: Schema changes: added steps to update staging table column names on the local machine and vegbiendev
- 04:50 PM Revision 12880: fix: schemas/VegCore/mk_derived: added `EOF` at end to avoid (benign) "here-document delimited by end-of-file" warnings on Linux
- 01:49 AM Revision 12879: mappings/VegCore.htm: regenerated from wiki: rename specimenHolderInstitutions to specimen_duplicate_institutions, as decided in the 2014-03-13 conference call (wiki.vegpath.org/2014-03-13_conference_call#schema-changes-2). note that most schema changes (such as this one) involve mappings changes, which are handled automatically by `inputs/run postprocess; yes|make inputs/{NVS,SALVIAS,TEAM}/test`.
- 01:43 AM Revision 12878: bugfix: lib/runscripts/table.run: schema/make calls: need to use `make schema` instead because old-style datasources don't have a top-level runscript (the absence of this identifies them as old-style so inputs/input.Makefile works correctly)
- 01:21 AM Revision 12877: /README.TXT: Maintenance: VegCore data dictionary: `make inputs/{NVS,SALVIAS,TEAM}/test`: recorded runtime (30 s)
- 01:17 AM Revision 12876: /README.TXT: Maintenance: VegCore data dictionary: `make inputs/{NVS,SALVIAS,TEAM}/test`: prepended `time` to enable obtaining the runtime
- 01:11 AM Revision 12875: /README.TXT: Maintenance: VegCore data dictionary: `inputs/run postprocess`: updated runtime (20 min)
- 12:45 AM Revision 12874: 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.
03/23/2014
03/22/2014
- 06:20 AM Revision 12872: bugfix: lib/sh/util.sh: **DON'T** do `shopt -s lastpipe` because this causes a segfault on Linux in stderr_matches(). (it also isn't supported on Mac.) use @PIPESTATUS instead. note that we do not currently need lastpipe, since we use @PIPESTATUS (which actually provides more functionality for our purposes).
- 06:02 AM Revision 12871: fix: lib/sh/util.sh: echo_func(): file/line #: display with regular color because the lighter color actually draws attention *to* rather than *away from* the faded text
- 05:59 AM Revision 12870: lib/sh/util.sh: added plain()
- 05:56 AM Revision 12869: inputs/XAL/Specimen/test.xml.ref: updated for sample data.csv, which contains the columns as a CSV. this fixes a bug where a map.csv must be used on a table that contains the same set of columns (ie. not one with no columns if there are any mappings).
- 05:50 AM Revision 12868: bugfix: lib/sql_io.py: put_table(): is_literals: `return sql.value(cur): need to use sql.value_or_none() instead to support multi-row functions, such as _split() used in specimens data`
- 05:06 AM Revision 12867: fix: inputs/input.Makefile: don't treat *.xml as data files since these are not currently supported
- 04:55 AM Revision 12866: lib/runscripts/util.run: on_exit(): documented that users can also override gateway()/fallback() to perform other commands (or no commands) after the script is read
- 04:53 AM Revision 12865: bugfix: lib/sh/db.sh: pg_table_exists(): need ! to negate boolean result
- 04:44 AM Revision 12864: fix: lib/runscripts/table.run: table_make_install(): need to inform the user when it skips installing a table, because this is often unexpected
- 04:43 AM Revision 12863: fix: lib/runscripts/util.run: run_args_cmd(): need to indent the output of the target that it's running
- 04:15 AM Revision 12862: lib/runscripts/table.run: removed no longer used datasrc_make_install()
- 04:07 AM Revision 12861: fix: lib/sh/util.sh: fade(): use medium gray instead of light gray because it fades on white *and* black backgrounds
- 03:54 AM Revision 12860: lib/sh/util.sh: echo_func(): fade the file/line # to avoid distracting from the function call in the default log output
- 03:51 AM Revision 12859: lib/sh/util.sh: added fade()
- 03:37 AM Revision 12858: lib/sh/util.sh: highlight_msg(): renamed to highlight_log_msg() to clarify that this contains log++-specific functionality
- 03:35 AM Revision 12857: lib/sh/util.sh: moved terminal formatting commands to own section
- 03:34 AM Revision 12856: lib/sh/util.sh: highlight_msg(): moved formatting code into separate format() function
- 03:21 AM Revision 12855: lib/sh/util.sh: dp(): renamed to ps() to corresponding with pv/pf
- 03:19 AM Revision 12854: lib/sh/make.sh: echo_target: use `log-- echo_func`, which now puts the target name first but also provides much-needed indentation
- 03:16 AM Revision 12853: lib/sh/util.sh: echo_func(): put file/line # *after* function call instead of before so the function name is listed first
- 03:13 AM Revision 12852: lib/sh/util.sh: echo_func(): usage: removed no longer used/implemented minor=1 switch. use log++ instead.
- 03:07 AM Revision 12851: lib/runscripts/datasrc_dir.run: import(): use new schema/make, schema/rm
- 02:59 AM Revision 12850: lib/runscripts/table.run: load_data(): use the much simpler `schema/make` run target, rather than outsourcing to the legacy Makefile via the convoluted datasrc_make_install()/table_make_install()
- 02:26 AM Revision 12849: lib/runscripts/datasrc_dir.run: added schema/rm(), schema/make()
- 02:19 AM Revision 12848: lib/sh/util.sh: ignore_err_msg(): usage: added $ignore_e param from stderr_matches()
- 02:14 AM Revision 12847: lib/runscripts/table.run: psql: always include ; at end of statement
- 01:39 AM Revision 12846: fix: lib/sh/db.sh: pg_cmd(): hide PGPASSWORD at the normal verbosity so that the value of it doesn't appear in any log files
- 01:08 AM Revision 12845: lib/sh/util.sh: log_hint(): renamed to log_err_hint() for clarity, because this applies only to hints for errors
- 01:06 AM Revision 12844: bugfix: lib/sh/util.sh: log_hint!(): use log_err instead of log_info because hints as used here are attached to (possibly benign) errors. for other uses, use mk_hint().
- 01:00 AM Revision 12843: fix: lib/sh/util.sh: highlight_msg(): don't ' '-pad already-formatted text
- 12:57 AM Revision 12842: lib/sh/util.sh: manual terminal escape sequences: use highlight_msg() instead
- 12:53 AM Revision 12841: lib/sh/util.sh: highlight_msg(): auto-add padding around text if there is a background
- 12:51 AM Revision 12840: lib/sh/util.sh: highlight_msg(): use $format itself as the $highlight boolean
- 12:48 AM Revision 12839: lib/sh/util.sh: highlight_msg(): split apart the testing of $format and can_highlight_msg
- 12:39 AM Revision 12838: lib/sh/util.sh: added has_bg()
- 12:28 AM Revision 12837: bugfix: lib/sh/util.sh: highlight_msg(): need to reset any existing formatting before applying new formatting
- 12:25 AM Revision 12836: lib/sh/util.sh: added mk_hint() and use it in log_hint!()
- 12:16 AM Revision 12835: lib/sh/util.sh: bg_cmd(): also log the command being run
- 12:07 AM Revision 12834: fix: lib/sh/util.sh: need `function` before functions that have an alias with the same name
- 12:04 AM Revision 12833: lib/sh/util.sh: log!(): use new log:()
- 12:00 AM Revision 12832: lib/sh/util.sh: added log:(), which sets an explicit log_level. this also simplifies log+().
03/21/2014
- 11:55 PM Revision 12831: lib/sh/util.sh: log+(): set log_level before PS4 so that the PS4 expr doesn't also need to add to log_level
- 11:51 PM Revision 12830: lib/sh/util.sh: removed no longer needed log+ alias (which had been renamed from clog+)
- 11:48 PM Revision 12829: lib/sh/util.sh: clog*: renamed to log* for clarity (possible now that log* is no longer used for function-local log_level setting)
- 11:44 PM Revision 12828: *{.sh,run}: local setting of log_level: use log_local instead of relying on the log* aliases, so that these aliases can instead be used for wrapping commands (the more common use case)
- 11:40 PM Revision 12827: bugfix: lib/sh/util.sh: verbosity_compat alias: need to use `declare verbosity="$verbosity"` instead of `declare verbosity`, which would just clear $verbosity
- 11:38 PM Revision 12826: bugfix: lib/sh/util.sh: verbosity_min alias: need to use `declare verbosity="$verbosity"` instead of log_local now that verbosity is not one of the vars changed by log++
- 11:30 PM Revision 12825: lib/sh/util.sh: log+(): use easier-to-understand log_local instead of prefix-assignments to limit assignments to the invoked command
- 11:30 PM Revision 12824: lib/sh/util.sh: log+(): use easier-to-understand log_local instead of prefix-assignments to limit assignments to the invoked command
- 10:57 PM Revision 12823: *{.sh,run}: use clog* instead of "log*"
- 10:45 PM Revision 12822: bugfix: lib/sh/util.sh: log+(): removed spurious ; between setting of PS4 and log_level, which was causing erratic mismatches between PS4 and log_level. (the ; caused $PS4 to be set in the *caller* when invoked via one of the clog* aliases, rather than being passed as a command-specific env var.)
- 10:30 PM Revision 12821: lib/sh/util.sh: $verbosity: stay constant at what the user set it to instead of changing in tandem with $log_level, to facilitate debugging verbosity/log_level-related issues
- 10:11 PM Revision 12820: lib/sh/util.sh: log+(): usage: use aliases instead of ""-ed function names
- 06:58 PM Revision 12819: added schemas/VegCore.ERD.pdf symlink for easy access
- 06:50 PM Revision 12818: lib/sh/util.sh: log_err(): use red background for better visibility of errors, in the same way that lib/exc.py print_ex() does for column-based import
- 06:44 PM Revision 12817: bugfix: lib/sh/util.sh: removed echo_func in functions used by log++, to avoid spurious highlighted output
- 06:40 PM Revision 12816: lib/sh/util.sh: added missing clog+ alias
- 06:35 PM Revision 12815: bugfix: lib/sh/util.sh: log_hint(): use the standard log_fd and log_info() format, not err_fd and log_err() format, for hint messages
- 06:27 PM Revision 12814: fix: lib/sh/util.sh: log_msg!(): indent each line, not just the first
- 06:26 PM Revision 12813: lib/sh/util.sh: added split_lines()
- 06:05 PM Revision 12812: lib/sh/util.sh: log(): factored out helper function log_msg!()
- 06:00 PM Revision 12811: fix: lib/sh/util.sh: highlight_msg(): bold instead of underlining because the underlining interferes with the readability of the commands
- 05:57 PM Revision 12810: lib/sh/util.sh: highlight_msg(): allow turning off formatting w/ empty $format
- 05:53 PM Revision 12809: fix: lib/sh/util.sh: log_err() calls: removed manual highlighting
- 05:51 PM Revision 12808: lib/sh/util.sh: log_err(): highlight all error messages using highlight_msg()'s new $format
- 05:45 PM Revision 12807: lib/sh/util.sh: highlight_msg(): support custom format
- 05:35 PM Revision 12806: lib/sh/db.sh: pg_*_exists(): log the DB statements to check this at a higher log_level so that they don't clutter up the log output
- 05:25 PM Revision 12805: lib/sh/util.sh: log(): highlight log_level 1 messages to stand out against other output, for easier debugging
- 04:31 PM Revision 12804: *{.sh,run}: stderr_matches() wrapper calls: removed no longer needed prep_try/rethrow
- 04:12 PM Revision 12803: bugfix: catch(): also need to support $1='' because this is a now a use case of ignore_e()
- 04:02 PM Revision 12802: bugfix: lib/sh/util.sh: ignore_err_msg(): also need to ignore false exit status on no match
- 03:49 PM Revision 12801: lib/sh/util.sh: stderr_matches(): moved prep_try/rethrow into the function itself so that callers don't have to wrap this function in a complex sequence of prep_try/rethrow statements
- 03:42 PM Revision 12800: *{.sh,run}: stderr_matches() wrapper calls: removed no longer needed prep_try/rethrow
- 03:42 PM Revision 12799: lib/sh/util.sh: stderr_matches(): moved prep_try/rethrow into the function itself so that callers don't have to wrap this function in a complex sequence of prep_try/rethrow statements
- 03:25 PM Revision 12798: lib/sh/util.sh: added rethrow_exit alias
- 03:10 PM Revision 12797: fix: lib/sh/db.sh: pg_table_exists(): use stderr_matches() rather than just the exit status. this also avoids highlighting the benign error.
- 03:00 PM Revision 12796: fix: lib/sh/db.sh: pg_table_exists(): use stderr_matches() rather than just the exit status. this also avoids highlighting the benign error.
- 02:16 AM Revision 12795: fix: inputs/input.Makefile: removed no longer used special handling of XML inputs, support for which was never added to the Makefile. (bin/map, however, does support importing an XML file into a database.) this fixes a bug in XAL, which used to abort with an error but now just imports an empty table.
- 12:34 AM Revision 12794: fix: inputs/input.Makefile: %/install: don't ignore errors if table does not exist, to ensure a proper errexit. this is now possible because every dir that this target is being run on should be a data dir. (Source/ used to be a metadata-only dir.)
- 12:31 AM Revision 12793: bugfix: inputs/input.Makefile: $(cleanup): need `set -o pipefail`
- 12:02 AM Revision 12792: inputs/VegBank/run: `rm=1 import()`: updated runtime (1 h)
03/20/2014
- 11:54 PM Revision 12791: inputs/VegBank/taxon_observation.**/test.xml.ref: updated inserted row count
- 11:54 PM Revision 12790: inputs/VegBank/projectcontributor_/test.xml.ref: updated inserted row count
- 10:53 PM Revision 12789: bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)
- 10:44 PM Revision 12788: bugfix: inputs/VegBank/import_order.txt: added missing project, needed to trigger the staging table renaming for the project table
- 10:42 PM Revision 12787: inputs/VegBank/run: documented `rm=1 import()` runtime (>1.5 h)
- 10:40 PM Revision 12786: inputs/VegBank/run: documented `datasrc_make sql/install` runtime (25 min)
- 08:27 PM Revision 12785: inputs/MO/Specimen/test.xml.ref: updated, which adds dateCollected mappings
- 08:20 PM Revision 12784: inputs/WIN/Specimen/test.xml.ref: updated to map.csv, which has eventDate->dateCollected
- 08:13 PM Revision 12783: inputs/VegBank/plantconcept_/create.sql: updated runtime (25 min, ~same)
- 08:08 PM Revision 12782: lib/sh/make.sh: begin_target: echo all targets to facilitate debugging without needing the verbose stack trace mode
- 08:06 PM Revision 12781: bugfix: lib/sh/make.sh: echo_target: don't include filename/line #, since this is not for the stack trace mode
- 07:59 PM Revision 12780: lib/sh/make.sh: added echo_target
- 07:58 PM Revision 12779: *{.sh,run}: use new begin_target instead of `echo_func; set_make_vars`
- 07:51 PM Revision 12778: lib/runscripts/util.run: runscript template: added sample make target, using new make target template
- 07:48 PM Revision 12777: lib/sh/make.sh: added make target template
- 07:47 PM Revision 12776: inputs/VegBank/plot/postprocess.sql: remove institutions that we have direct data for: CVS: updated runtime (same)
- 07:41 PM Revision 12775: lib/sh/make.sh: added begin_target alias
- 07:17 PM Revision 12774: lib/runscripts/datasrc_dir.run: documented how to reinstall staging tables (`rm=1 .../run import`)
- 07:13 PM Revision 12773: bugfix: *{.sh,run}: stderr_matches() wrapper callers: use the required wrapper caller usage, which now includes rethrow and prep_try
- 07:09 PM Revision 12772: bugfix: lib/sh/util.sh: rethrow*: only `return` if $e is actually nonzero, because rethrow is now being used as a catch-all in situations where there might not be an error
- 07:06 PM Revision 12771: lib/sh/util.sh: prep_try: initialize $e to 0 to simplify error-handling coding
- 06:56 PM Revision 12770: stderr_matches(): wrapper caller usage: added alternative usage when using `||`
- 06:50 PM Revision 12769: lib/sh/util.sh: stderr_matches(): wrapper caller usage: documented usage for a negated condition (ie. prefixed w/ !)
- 06:48 PM Revision 12768: lib/sh/util.sh: stderr_matches(): usage: split into wrapper usage and wrapper caller usage for clarity
- 06:45 PM Revision 12767: fix: *{.sh,run}: stderr_matches() wrappers: usage: added `rethrow`
- 06:45 PM Revision 12766: fix: *{.sh,run}: stderr_matches() wrappers: usage: added `rethrow`
- 06:43 PM Revision 12765: fix: lib/sh/util.sh: stderr_matches(): usage: `rethrow` must be called right after stderr_matches(), to avoid calling running other commands if there is an error
- 06:40 PM Revision 12764: fix: lib/sh/util.sh: stderr_matches(): when using $ignore_e, also set benign_error=1 to suppress the highlighting of the error
- 06:36 PM Revision 12763: bugfix: lib/sh/db.sh: pg_schema_exists(): need to ignore benign error exit status from the "cannot create temporary relation in non-temporary schema" error
- 06:34 PM Revision 12762: lib/sh/util.sh: stderr_matches(): supporting ignoring any benign error exit status associated with the error message being tested for
- 06:18 PM Revision 12761: lib/sh/util.sh: stderr_matches(): usage: documented where any ignore_e statement would go
- 05:37 PM Revision 12760: bugfix: lib/sh/util.sh: stderr_matches(): can't use `try` because this clears the exit status, which is needed for @PIPESTATUS to work. to support this, also need to avoid errexiting since @PIPESTATUS will be used instead.
- 01:25 AM Task #878 (New): fix crow's foot notation in ERD
- * when there is an open circle on the straight end, also put an open circle on the crow's foot end, so that the outgo...
03/18/2014
- 06:18 PM Revision 12759: lib/sh/util.sh: added dp(), which debug-prints a message
- 05:47 PM Revision 12758: bugfix: inputs/VegBank/plot/postprocess.sql: use CVS.plot_ instead because that has the renamed staging table columns, and is compatible with auto-renaming of the SQL script columns
- 05:41 PM Revision 12757: inputs/CVS/plot_/postprocess.sql: add unique constraint on locationName (analogous to the unique constraint in plot), for use by inputs/VegBank/plot/postprocess.sql in removing inter-datasource duplication
- 05:26 PM Revision 12756: fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output
- 05:22 PM Revision 12755: schemas/util.sql: added fold_explain_msg()
- 05:22 PM Revision 12754: bugfix: bin/repl: only use excluded_prefix_re/excluded_suffix_re in text mode (used in renaming columns in SQL scripts), to prevent the special coding for column renames from also affecting regular regexp/word replacements
- 05:10 PM Revision 12753: inputs/VegBank/taxon_observation.**/test.xml.ref: updated inserted row count
- 05:34 AM Revision 12752: inputs/run: postprocess(): documented runtime (30 min)
- 05:16 AM Revision 12751: bugfix: inputs/input.Makefile: %/postprocess.sql: don't perform replacements using map.csv, because map.csv is not idempotent. this functionality was only there to facilitate switching to new-style import, which is now largely done. (the remaining datasources NVS, SALVIAS, TEAM contain only 1 postprocess.sql: inputs/SALVIAS/projects/postprocess.sql (`st inputs/{NVS,SALVIAS,TEAM}/*/postprocess.sql`).)
- 04:59 AM Revision 12750: bugfix: bin/repl: text mode: also don't match if it's part of a '-'-separated identifier
- 04:57 AM Revision 12749: bugfix: bin/repl: text mode: also don't match if it's a word in a sentence
- 04:42 AM Revision 12748: bugfix: bin/repl: text mode: turned off the suffix matching, because there are cases where a mapping adds a suffix which would cause the same replacement to be performed repeatedly
- 04:33 AM Revision 12747: inputs/input.Makefile: %/postprocess.sql: *always* run this, not just if the associated map spreadsheets change, to avoid needing to `touch` them to cause %/postprocess.sql to run
- 04:25 AM Revision 12746: bin/repl: text mode: exclude prefixes that should not cause replacement, to avoid doubling leading *
- 04:24 AM Revision 12745: fix: inputs/*/*/postprocess.sql: un-doubled *
- 04:06 AM Revision 12744: bugfix: inputs/input.Makefile: %/postprocess.sql: also need to apply renames from mappings/VegCore.thesaurus.csv, as these have been applied to map.csv
- 04:04 AM Revision 12743: bugfix: lib/runscripts/table.run: custom_postprocess(): need to apply renames to SQL statements in postprocess.sql before it can be run
- 04:03 AM Revision 12742: bin/repl: text mode: also match w/ suffix (eg. _verbatim)
- 03:10 AM Task #577 (Rejected): use views instead of map spreadsheets to store the datasource mappings
- the staging table columns are now renamed instead of creating a view that maps the columns
- 03:07 AM Task #584 (Resolved): enable running all the import steps from one runscript
- 02:59 AM Revision 12741: bugfix: /README.TXT: Maintenance: VegCore data dictionary: apply new data dict mappings: need to use postprocess rather than import runscript target, so that the command also works on an svn checkout without the flat files (the flat files are not needed for the staging table renaming)
03/15/2014
- 07:20 PM Revision 12740: lib/sh/db.sh: psql(): $verbose_ok: renamed to $bypass_ok for clarity, because this applies only to the `--output /dev/fd/41` bypass (which when not possible, requires turning off verbose output
- 07:15 PM Revision 12739: fix: lib/sh/db.sh: psql(): added $output_data switch analogous to what mysql() has. this causes query results of eg. void-returning functions to be correctly filtered by the logging mechanism, rather than output to stdout.
- 06:42 PM Revision 12738: fix: lib/sh/db.sh: psql(): verbosity=0 (errors only) mode: use `SET client_min_messages = WARNING;` instead of NOTICE to hide verbose messages within psql as well
- 06:31 PM Revision 12737: lib/sh/db.sh: psql(): replaced `test "$verbose_ok" && can_log` with bool var $verbose_
- 06:29 PM Revision 12736: fix: lib/sh/db.sh: psql(): $verbose_: renamed to $verbose_ok for clarity
- 06:13 PM Revision 12735: fix: lib/sh/util.sh: stdout_contains(): add another pipe_delay because the `grep` statement was sometimes getting printed before its filtered output
- 05:47 PM Revision 12734: 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)
- 05:43 PM Revision 12733: schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()
- 05:37 PM Revision 12732: schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY(...)) for clarity
- 05:28 PM Revision 12731: bugfix: lib/sh/util.sh: die_error_hidden(): min verbosity to display error should not be hardcoded
- 05:18 PM Revision 12730: lib/sh/db.sh: psql(): "to see error details" msg: use new die_error_hidden()
- 05:18 PM Revision 12729: lib/sh/util.sh: added die_error_hidden()
- 05:13 PM Revision 12728: lib/sh/db.sh: psql(): "to see error details" msg: use new log_hint()
- 05:13 PM Revision 12727: lib/sh/util.sh: added log_hint(), whose msg is only displayed if not a benign error
- 05:03 PM Revision 12726: bugfix: lib/sh/db.sh: psql(): "to see error details" msg: also don't print it for benign errors ($benign_error)
- 05:00 PM Revision 12725: schemas/util.sql: added mk_not_null()
- 04:42 PM Revision 12724: lib/sh/db.sh: psql(): on error, display message describing how to see error details (prepend `vb=2` to the command)
- 04:31 PM Revision 12723: bugfix: lib/sh/util.sh: log_err(): don't override verbosity manually, as this will not set log_level or PS4. instead, use new log! , which sets these correctly.
- 04:24 PM Revision 12722: lib/sh/util.sh: added log! , which force-displays next log message
- 03:59 PM Revision 12721: lib/sh/util.sh: save_e: made it idempotent so that it also works if save_e was already called
- 03:57 PM Revision 12720: lib/sh/util.sh: save_e: made it idempotent so that it also works if save_e was already called
- 03:37 PM Revision 12719: lib/sh/util.sh: rethrow: documented why can't use `(exit "$e")` (bash bug that prevents errexit)
03/14/2014
- 09:09 PM Revision 12718: bugfix: /README.TXT: Maintenance: VegCore data dictionary: apply new data dict mappings: need to use import rather than mappings runscript target, to rename the staging tables
- 09:06 PM Revision 12717: bugfix: /README.TXT: Maintenance: VegCore data dictionary: also need to apply new data dict mappings on vegbiendev
- 08:19 PM Revision 12716: fix: /README.TXT: Maintenance: VegCore data dictionary: added steps to apply the new data dictionary mappings to the datasource mappings and staging tables
- 07:53 PM Revision 12715: bugfix: lib/runscripts/util.run: $auto_ignore: need to unexport it so don't pass this to invoked scripts except through fwd()
- 07:35 PM Revision 12714: added inputs/run, which runs all the inputs' runscripts using the new auto-forwarding
- 07:34 PM Revision 12713: bugfix: lib/runscripts/util.run: auto_fwd's fallback() must be set *after* auto_ignore's fallback() to overwrite it (auto_ignore should only apply if an error would otherwise have been generated by the fallback)
- 07:30 PM Revision 12712: lib/runscripts/util.run: fwd(): support subdirs that don't contain a runscript, so that the default value of @subdirs will work in most cases
- 07:29 PM Revision 12711: lib/runscripts/util.run: fwd(): set default @subdirs (`{.,}*/`)
- 07:26 PM Revision 12710: lib/sh/util.sh: added enter_top_dir and use it in in_top_dir
- 07:12 PM Revision 12709: fix: lib/sh/util.sh: commands run inside $(...): need to run with log++ so that these aren't normally debug-printed
- 06:41 PM Revision 12708: lib/sh/util.sh: added pv(), which debug-prints var(s)
- 06:40 PM Revision 12707: lib/sh/util.sh: added wildcard.()
- 06:40 PM Revision 12706: lib/sh/util.sh: added wildcard/()
- 06:40 PM Revision 12705: lib/sh/util.sh: added esc_args()
- 06:33 PM Revision 12704: web/links/index.htm: updated to Firefox bookmarks: Google Drive: listed bugs that make it very difficult to use (the need to re-download all files when reconnecting a client to an account). added recommendation not to use it (unstable).
- 05:25 PM Revision 12703: removed unused inputs/table.run. inputs/*/table.run include lib/runscripts/table.run directly.
- 05:02 PM Revision 12702: lib/runscripts/datasrc_dir.run: removed postprocess(), which now does the same thing its auto-forwarded equivalent would
- 05:01 PM Revision 12701: lib/runscripts/datasrc_dir.run: removed separate @table_subdirs, because the table-only targets can now safely be invoked on all subdirs, being auto-ignored in subdirs that don't support them
- 04:53 PM Revision 12700: lib/runscripts/util.run: fwd(): enable $auto_ignore so that each subdir doesn't have to have a definition for the forwarded target
- 04:52 PM Revision 12699: lib/runscripts/util.run: added $auto_ignore switch, which causes fallback() not to generate an error that a non-existant target doesn't exist
- 03:55 PM Revision 12698: lib/runscripts/datasrc_dir.run: use new fwd_self alias
- 03:55 PM Revision 12697: lib/runscripts/util.run: added fwd_self alias
- 03:49 PM Revision 12696: lib/runscripts/datasrc_dir.run: enable $auto_fwd, to create the functionality of lib/forwarding.Makefile's `%` target
- 03:47 PM Revision 12695: lib/runscripts/util.run: added $auto_fwd switch
- 03:36 PM Revision 12694: bugfix: lib/runscripts/util.run: gateway(): need to use is_callable() rather than func_exists() to check whether the target exists, because external commands (eg. echo) are supported as targets, too
- 03:32 PM Revision 12693: lib/sh/util.sh: added is_callable()
- 03:23 PM Revision 12692: lib/runscripts/util.run: support custom handlers for *all* targets (gateway()) as well as targets w/o function (fallback())
- 03:03 PM Revision 12691: lib/runscripts/table.run: remake_VegBIEN_mappings(): renamed to just mappings() since action make targets should be short names
- 07:32 AM Revision 12690: lib/sh/util.sh: stderr_matches(): inline the stderr_matches alias to avoid needing to quote stderr_matches as "stderr_matches" in the most common use case (with pattern as a prefix env var)
- 07:29 AM Revision 12689: bugfix: lib/sh/util.sh: stderr_matches(): when passing `pattern=...` as a prefix env var, must be invoked as `"stderr_matches"` to avoid the env var applying to the prep_try portion of the stderr_matches alias
- 06:38 AM Revision 12688: added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad
03/13/2014
- 06:53 PM Revision 12687: 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.)
- 06:42 PM Revision 12686: bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query
- 06:40 PM Revision 12685: 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)
- 06:33 PM Revision 12684: schemas/util.sql: try_create(): also handle "child table is missing column" errors
- 05:33 PM Revision 12683: schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE()
- 05:14 PM Revision 12682: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 05:13 PM Revision 12681: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
- 05:04 PM Revision 12680: schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
- 05:03 PM Revision 12679: inputs/SALVIAS/validations.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
- 09:08 AM Revision 12678: 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
- 09:00 AM Revision 12677: validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
- 02:06 AM Revision 12676: schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block
- 01:50 AM Revision 12675: 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
- 01:46 AM Revision 12674: 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
- 01:14 AM Revision 12673: 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
- 01:12 AM Revision 12672: schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries
03/12/2014
- 11:56 PM Revision 12671: 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
- 11:53 PM Revision 12670: 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())
- 11:49 PM Revision 12669: bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
- 11:40 PM Revision 12668: schemas/util.sql: added debug_print_func_call(text) and use it where applicable
- 11:33 PM Revision 12667: schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match
- 06:26 PM Revision 12666: schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
- 06:18 PM Revision 12665: bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)
- 05:41 PM Revision 12664: schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.
- 05:40 PM Revision 12663: bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if *all* tables have all 1s
- 05:38 PM Revision 12662: schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if *all* tables have all 1s
- 05:36 PM Revision 12661: schemas/util.sql: added freq_always_1(regclass[])
- 05:35 PM Revision 12660: schemas/util.sql: added drop_column(regclass[])
- 05:04 PM Revision 12659: schemas/util.sql: added parent(regclass)
- 04:48 PM Revision 12658: 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
- 04:44 PM Revision 12657: 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
- 04:13 PM Revision 12656: schemas/util.sql: added to_freq(regclass, drop_if_always_1)
- 04:04 PM Revision 12655: schemas/util.sql: added auto_rm_freq(regclass)
- 03:53 PM Revision 12654: schemas/util.sql: added freq_always_1(regclass)
- 03:00 PM Revision 12653: bugfix: schemas/util.sql: diff(regclass, regclass): need to create a diff when the # of copies of a row differs between the tables. this uses new util.to_freq().
- 02:44 PM Revision 12652: schemas/util.sql: added to_freq(regclass)
- 02:43 PM Revision 12651: schemas/util.sql: added populate_table(regclass, text)
- 01:11 PM Revision 12650: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 12:53 PM Revision 12649: schemas/util.sql: added copy_types_and_data(regclass, text)
- 04:44 AM Revision 12648: schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query
- 04:41 AM Revision 12647: schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj
- 03:44 AM Revision 12646: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 03:44 AM Revision 12645: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
- 03:35 AM Revision 12644: schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept
- 03:20 AM Revision 12643: 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
- 03:04 AM Revision 12642: schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover
- 12:04 AM Revision 12641: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 12:01 AM Revision 12640: schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species
03/11/2014
- 09:57 PM Revision 12639: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 09:56 PM Revision 12638: bugfix: inputs/SALVIAS/validations.sql: _plots_07_list_of_plots_with_counts_of_individuals_per_species: renamed to _plots_07_list_of_plots_*which_use*_... because this query is not intended to include the actual counts, just to say which plots have them (the correct "which use" wording is also used in queries #8, 9)
- 04:05 PM Revision 12637: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)
- 03:38 PM Revision 12636: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented that incorrect query plans are an ongoing bug in Postgres, because it does not support index hints and by default does not follow the join order. specifically, Postgres often does the following things in query plans which should normally never be done:
- * performs a sequential scan when an index is available (because it incorrectly thinks there are too many dead rows i...
03/07/2014
- 10:49 PM Revision 12635: 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
- 09:53 PM Revision 12634: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 09:50 PM Revision 12633: schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id
- 09:41 PM Revision 12632: schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.
- 09:18 PM Revision 12631: schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location
- 08:55 PM Revision 12630: bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id
- 04:42 PM Revision 12629: 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.)
- 05:51 AM Revision 12628: 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
- 05:42 AM Revision 12627: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
- 05:35 AM Revision 12626: 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.
- 05:25 AM Revision 12625: 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).
- 05:23 AM Revision 12624: schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)
- 04:53 AM Revision 12623: fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
- 02:26 AM Revision 12622: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added description of join_collapse_limit config param (which should be turned off, although it is on by default). added links for using TIDs ("the fastest possible access to a single row").
03/06/2014
- 10:45 PM Revision 12621: 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).
- 10:26 PM Revision 12620: schemas/postgresql.conf: log_min_messages: dropped the verbosity back down to the default, to avoid clogging up the logs
- 10:21 PM Revision 12619: schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)
- 09:51 PM Revision 12618: schemas/postgresql.conf: log_min_messages: show what autovacuum is doing
- 09:40 PM Revision 12617: fix: schemas/postgresql.conf: disable autovacuum_vacuum_cost_delay to avoid stalling autovacuuming due to a concurrent query, as this can prevent autovacuuming from happening altogether (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Resource%20Consumption:%2018.4.4.%20Cost-based%20Vacuum%20Delay)
- 09:37 PM Revision 12616: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for troubleshooting autovacuuming (which can slow queries down significantly when it isn't happening for any tables)
- 07:35 PM Revision 12615: schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)
- 07:30 PM Revision 12614: 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.
- 11:30 AM Revision 12613: planning/meetings/BIEN conference call availability.xlsx: updated
- 11:06 AM Revision 12612: bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
- 11:04 AM Revision 12611: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
- 11:03 AM Revision 12610: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
- 11:02 AM Revision 12609: 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
- 10:59 AM Revision 12608: 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
- 10:57 AM Revision 12607: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
- 08:57 AM Revision 12606: schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table
- 08:52 AM Revision 12605: fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query
- 08:31 AM Revision 12604: schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table
- 08:29 AM Revision 12603: 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
- 08:14 AM Revision 12602: 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
- 08:12 AM Revision 12601: bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query
- 02:18 AM Revision 12600: 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.
- 01:59 AM Revision 12599: bugfix: schemas/Makefile: `%/install: vegbien.sql`: sed expr: need to use '' instead of "" because $(*q) may contain "
- 12:33 AM Revision 12598: bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated
- 12:09 AM Revision 12597: 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())
- 12:07 AM Revision 12596: 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
- 12:00 AM Revision 12595: schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)
03/05/2014
- 11:53 PM Revision 12594: 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)
- 01:01 PM Revision 12593: 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())
- 12:50 PM Revision 12592: schemas/util.sql: added show_types_like()
- 12:42 PM Revision 12591: 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**)
- 12:25 PM Revision 12590: 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**)
- 12:13 PM Revision 12589: schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)
- 07:28 AM Revision 12588: 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().
- 07:24 AM Revision 12587: schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()
- 07:23 AM Revision 12586: 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
- 01:02 AM Revision 12585: schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.
- 12:59 AM Revision 12584: 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.
- 12:32 AM Revision 12583: fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger
03/04/2014
- 05:56 PM Revision 12582: bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix
- 05:52 PM Revision 12581: 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
- 05:48 PM Revision 12580: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
- 05:47 PM Revision 12579: bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()
- 05:40 PM Revision 12578: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
- 05:39 PM Revision 12577: 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
- 05:22 PM Revision 12576: schemas/util.sql: added mk_typed_cols_list(col_cast[])
- 05:20 PM Revision 12575: schemas/util.sql: added prefixed_name()
- 05:18 PM Revision 12574: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity
- 04:56 PM Revision 12573: schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries
- 04:53 PM Revision 12572: 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
- 04:45 PM Revision 12571: bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword
- 04:31 PM Revision 12570: schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table
- 04:19 PM Revision 12569: schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function
- 03:55 PM Revision 12568: schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error
- 03:53 PM Revision 12567: 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
- 03:49 PM Revision 12566: schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries
- 03:41 PM Revision 12565: 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.
- 03:35 PM Revision 12564: schemas/util.sql: added mk_keys_func(regtype)
- 03:34 PM Revision 12563: 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
- 03:02 PM Revision 12562: schemas/util.sql: added typed_cols(regtype)
- 02:29 PM Revision 12561: schemas/util.sql: added mk_keys_func(regtype, col_cast[])
- 02:27 PM Revision 12560: bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)
- 02:23 PM Revision 12559: bugfix: schemas/util.sql: mk_out_params(): need COALESCE(..., ) around string_agg()
- 02:22 PM Revision 12558: bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL
- 02:20 PM Revision 12557: schemas/vegbien.sql: synced with DB, which reorders ~type tables
- 01:59 PM Revision 12556: schemas/util.sql: added mk_out_params()
- 01:42 AM Revision 12555: schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names
- 01:40 AM Revision 12554: 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.
- 01:34 AM Revision 12553: 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.
02/28/2014
- 11:17 PM Revision 12552: bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()
- 11:13 PM Revision 12551: 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.
- 11:11 PM Revision 12550: schemas/vegbien.sql: added new ~type tables
- 10:45 PM Revision 12549: 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
- 10:28 PM Revision 12548: /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)
- 10:03 PM Revision 12547: 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().
- 09:49 PM Revision 12546: fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries
- 08:15 PM Revision 12545: 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())
- 07:57 PM Revision 12544: 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
- 07:56 PM Revision 12543: 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
- 07:51 PM Revision 12542: schemas/vegbien.sql: synced with DB, which reorders ~type tables
- 07:34 PM Revision 12541: fix: schemas/Makefile: moved comment about publishing a schema to %/publish
- 05:27 PM Revision 12540: 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
- 05:25 PM Revision 12539: 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.
02/27/2014
- 07:56 PM Revision 12538: fix: inputs/SALVIAS/validations.sql: renamed SiteCode to plot_code to match output queries
- 07:53 PM Revision 12537: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
- 07:49 PM Revision 12536: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
- 07:45 PM Revision 12535: schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.
- 07:41 PM Revision 12534: schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()
- 07:37 PM Revision 12533: schemas/util.sql: raise(type text, msg text): documented the possible options for the type param
- 07:34 PM Revision 12532: fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements
- 07:32 PM Revision 12531: schemas/util.sql: eval(): added verbose_ option like for eval2set()
- 07:28 PM Revision 12530: schemas/util.sql: added raise(type text, msg text)
- 07:09 PM Revision 12529: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
- 07:08 PM Revision 12528: fix: schemas/vegbien.sql: added back _traits_* ~type tables
- 07:02 PM Revision 12527: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
- 06:58 PM Revision 12526: inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability
- 06:42 PM Revision 12525: 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
- 06:36 PM Revision 12524: 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
- 04:37 PM Revision 12523: schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
- 04:12 PM Revision 12522: 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)
- 03:51 PM Revision 12521: 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)
- 02:20 PM Revision 12520: schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)
- 02:07 PM Revision 12519: schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql
- 02:05 PM Revision 12518: 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)
- 01:49 PM Revision 12517: 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
- 01:27 PM Revision 12516: 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 .
- 12:43 PM Revision 12515: 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)
- 07:59 AM Revision 12514: 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.)
- 06:51 AM Revision 12513: fix: schemas/vegbien.sql: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)
- 01:11 AM Revision 12512: fix: schemas/vegbien.sql: location: added index on top_plot
- 01:11 AM Revision 12511: fix: schemas/vegbien.sql: location: added index on top_plot
- 01:06 AM Revision 12510: fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query
- 01:04 AM Revision 12509: schemas/vegbien.sql: added more ~type tables for plots queries
Also available in: Atom