schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)
fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage
schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources
schemas/vegbien.sql: added validatable_datasources()
schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources
schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas
schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables
schemas/vegbien.sql: public_validations.remake_diff_table(in_view regclass): document how to regenerate the diff table, in a place that's readily available to someone using the table
fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.
bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param
schemas/vegbien.sql: added query_view_relation_max_prefix_len()
schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query
schemas/vegbien.sql: added new_query_view_relation_name()
schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query
fix: schemas/vegbien.sql: remake_diff_tables(): need to set function option search_path in order to limit the effects of `SET LOCAL search_path` (mk_set_search_path()) to the current function
fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views
schemas/vegbien.sql: added public_validations.query_views()
schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()
schemas/vegbien.sql: remake_diff_tables(): updated runtime now that row limit has been removed (same, 45 min)
fix: schemas/vegbien.sql: _traits_07_trait_value_and_units, _traits_08_taxonname_trait_and_value_for_first_5000_records: removed LIMIT to match the input queries
schemas/vegbien.sql: remake_diff_tables(): updated bien2_traits runtime (45 min) to include the now-properly-renamed views 6-8
schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)
fix: schemas/vegbien.sql: renamed output views to match input views
schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)
bugfix: schemas/vegbien.sql: source_by_shortname(): resolved ambiguous column references by renaming PL/pgSQL vars and adding table qualifiers
bugfix: schemas/vegbien.sql: source_by_shortname(): changed from STABLE to VOLATILE because the search_path can only be set in VOLATILE functions
bugfix: schemas/vegbien.sql: source_by_shortname(): can't be declared STRICT because it now has a 2nd param (schema_anchor) which is always NULL
bugfix: schemas/vegbien.sql: remake_diff_tables(): can't raise exception at end if had errors, because this would roll back the transaction. instead use new util.raise_error_notice().
schemas/vegbien.sql: added remake_diff_tables()
schemas/vegbien.sql: added validation_views()
schemas/vegbien.sql: remake_diff_table(): prepend ~ to the type table so that it sorts at the end, away from the main tables
bugfix: schemas/vegbien.sql: remake_diff_table(): can't have in_table/out_table inherit from each other, because inheritance also causes the rows of the parent table to be included in the child table. instead, they need to inherit from a common, empty table, as implemented by the new util.remake_diff_table().
schemas/vegbien.sql: added remake_diff_table()
bugfix: schemas/vegbien.sql: setting search_path locally: also need to set function option search_path, in order to limit the effects of `SET LOCAL search_path` (mk_set_search_path()) to the current function (http://www.postgresql.org/docs/9.1/static/sql-set.html#AEN75423)
bugfix: schemas/vegbien.sql: setting search_path locally: need to use `SET LOCAL search_path TO` instead of `PERFORM set_config(search_path, ..., is_local := true)` because the meaning of is_local refers to the entire transaction, not just the current function (as for LOCAL)
schemas/vegbien.sql: added rematerialize_in_view()
schemas/vegbien.sql: rematerialize_out_view(): pass a schema-qualified name to util.rematerialize_view() to avoid depending on the search_path to create it in the right schema
schemas/vegbien.sql: materialize_query(): support schema-qualified tables. this requires escaping tables in calls to this function.
schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)
schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)
bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path
schemas/vegbien.sql: functions with schema_anchors: documented that they must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime changes of search_path (schema elements are bound at inline time rather than runtime)
bugfix: schemas/vegbien.sql: functions with schema_anchors: must use plpgsql for these because the sql language does not support runtime changes of search_path (the schema elements they use are bound at inline time rather than runtime)
schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for
fix: schemas/vegbien.sql: schema_null: clarified that this should always be left as the default value, not just usually
schemas/vegbien.sql: schema_null params: use schema_anchor instead of source for this, since it is guaranteed to exist regardless of which tables are in the VegBIEN schema
schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)
schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.
schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity
schemas/vegbien.sql: added schema_anchor, for use with schema_null params
schemas/*.sql: make sure every COMMENT starts and ends on its own line, so that it appears correctly in the formats it's most likely to be read in (ie. in the DDL export, not the COMMENT edit box in pgAdmin)
validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.
schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using
schemas/vegbien.sql: regenerated from DB, which adds an autogenerated column alias
schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*
schemas/vegbien.sql: public_validation_plots: added Brad's notes as comments
schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
schemas/vegbien.sql: public_validation_traits: added Brad's notes as comments
schemas/vegbien.sql: public_validation_traits: populated queries from validation/aggregating/traits/bien3_validations_traits_bien3.sql
schemas/vegbien.sql: analytical_stem.datasource: removed fkey to source.shortname because this prevents individually-reloaded datasources from being published
schemas/vegbien.sql: publish(), rm(): documented runtime (1 min)
schemas/vegbien.sql: added datasource_unpublish()
schemas/vegbien.sql: public_validation: split into separate subschemas for plots, specimens, traits
fix: schemas/vegbien.sql: public_validation comment: undid incorrect replacement of "validation" with "public_validation"
schemas/vegbien.sql: added rm(), which deletes this schema
schemas/vegbien.sql: added publish(), which publishes this schema
schemas/vegbien.sql: added public_schema_publish()
schemas/vegbien.sql: datasource_publish(): removed nested transaction, which is not needed because Postgres runs each top-level command in a transaction (including in autocommit mode)
bugfix: schemas/Makefile,vegbien.sql: renamed validation to public_validation to match the suffixed name applied by `make schemas/public/install`
schemas/vegbien.sql: added validation schema, which contains queries used in the aggregating validations. schemas/Makefile: include schemas that depend on `public` (eg. `validation`) so they are restored along with it.
schemas/vegbien.sql: added top_plot view
schemas/vegbien.sql: location_set_top_plot(): only do the lookup of the parent's top_plot when there is a parent_id
schemas/vegbien.sql: location: added autopopulated top_plot
schemas/vegbien.sql: locationevent: added locationevent_pull_forward_from_parent() trigger which populates project_id from the parent locationevent
schemas/vegbien.sql: locationevent__pull_forward_from_parent(): renamed to locationevent_pull_forward_from_parent_for_stratum() since it actually has a filter that causes it to apply only to stratum events
bugfix: schemas/vegbien.sql: commconcept: added missing unique constraint on commdescription, which is also an identifying field in addition to the ID (which is in commname.commname)
bugfix: schemas/vegbien.sql: locationevent__communities(): inner-join to commdetermination instead of left-joining, so that this does not produce community entries (which occur because there is one commclass per locationevent, but only some commclasses will then have a commdetermination)
schemas/vegbien.sql: projectcontributor: added missing unique constraint
moved everything into /trunk/ to create the standard svn layout, for use with tools that require this (eg. git-svn). IMPORTANT: do NOT do an `svn up`. instead, re-use your working copy's existing files with `svn switch` (http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.switch.html).
schemas/vegbien.sql: plot.**, analytical_stem_view, analytical_plot: added project_contributors
schemas/vegbien.sql: added project_contributors()
schemas/vegbien.sql: analytical_stem_view: scientificName_verbatim: don't use taxonverbatim.taxonname+author as the scientificName_verbatim if only the author is provided. (this lead to weird scientificName_verbatims that contain just the author.)
bugfix: schemas/vegbien.sql: location: use the place_id from the parent location when no place_id is specified. this fixes a bug in analytical_stem_view where the parent location's place_id was used because it was sometimes missing from the sublocation, but the parent place_id itself was sometimes missing instead if sublocations each had their own place information. this way, it is always available directly in the sublocation, populated from the parent location if needed.
bugfix: schemas/vegbien.sql: location: added place_id which is autopopulated from the current locationplace. join on this in plot.**, to avoid a 1:many join when a location has multiple locationplaces.
bugfix: schemas/vegbien.sql: locationevent_unique_within_parent_by_location unique index: need COALESCE around location_id since it's nullable
fix: schemas/vegbien.sql: analytical_specimen: added specimens-related columns that are in analytical_plot
bugfix: schemas/vegbien.sql: updated for renamed county_centroids column names
schemas/vegbien.sql: include the family_higher_plant_group lookup table values so that these don't need to be regenerated from the NCBI nodes whenever the DB is reloaded
schemas/vegbien.sql: taxonlabel_update_ancestors(): don't do an index scan if the value being scanned for is NULL, to support testing this function without the indexes in place, without extra full-table scans for NULL values affecting things. this can be used to determine if the function is actually using the indexes, by turning them off and seeing if the runtime changes.
schemas/vegbien.sql: taxonlabel_update_ancestors(): don't create a performance-intensive nested transaction (EXCEPTION block) for each INSERT, because there should no longer be duplicate ancestors, so it's OK to abort the whole transaction if this assertion fails
bugfix: schemas/vegbien.sql: taxonlabel_update_ancestors_on_{insert,update}(): only use either the matched taxon's ancestors or the parent's ancestors, to avoid issues related to duplication between these two ancestors lists. this also fixes a bug where the 2nd taxonlabel_update_ancestors() call assumes that the existing ancestors are for the old parent, when in fact they have actually just been set to those for the new matched taxon (which horribly confuses taxonlabel_update_ancestors()).
schemas/vegbien.sql: _taxonlabel_set_parent_id(): just use a plain UPDATE statement, to avoid the significant parsing and stringification overhead of EXECUTE and quote_nullable(). it is not clear that EXECUTE is actually necessary to avoid caching the query plan, because the cache should be invalidated automatically when the table's ANALYZE statistics are regenerated.
schemas/vegbien.sql: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields
schemas/vegbien.sql: synced to DB (the view renderer apparently changed the text of a view)
schemas/vegbien.sql: analytical_stem_view: scrubbed_author: removed empty COALESCE around value (left over from when multiple values needed to be combined for many TNRS fields)
schemas/vegbien.sql: provider_count_view: source totals: use the much faster query developed for Brad (wiki.vegpath.org/VegBIEN_FAQ#from-Brad-on-2013-12-4), which avoids the need to do a GROUP BY on all of analytical_stem. eventually, we will want to apply the same optimization to the first publisher subtotals.
schemas/vegbien.sql: collected_dates: documented runtime (2.5 min)
schemas/vegbien.sql: collected_date_min: replaced with collected_dates view that lists all dates we have, so that we can determine which of these may be valid. it turns out that we have data collected from very far back (to the year 1), which are not merely 2-digit years because PostgreSQL will only parse early years when there are 4 digits.
bugfix: schemas/vegbien.sql: collected_date_min: exclude invalid dates < 1000-01-01
