schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.

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.

fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger

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

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

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

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

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

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.

schemas/vegbien.sql: added new ~type tables

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

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

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

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

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

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)

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 .

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

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

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

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

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

schemas/vegbien.sql: added validatable_datasources()

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

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: query_views(): renamed to query_relations() because this also returns non-views

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

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)

schemas/vegbien.sql: added remake_diff_tables()

schemas/vegbien.sql: added validation_views()

schemas/vegbien.sql: added remake_diff_table()

schemas/vegbien.sql: added rematerialize_in_view()

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: schema_null param: renamed to schema_anchor to clarify what this is for

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/vegbien.sql: added schema_anchor, for use with schema_null params

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, 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: added datasource_unpublish()

schemas/vegbien.sql: public_validation: split into separate subschemas for plots, specimens, traits

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

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

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

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.

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: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields

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.

schemas/vegbien.sql: added collected_date_min view

schemas/vegbien.sql: analytical_stem_view: added taxonomic_status. notice that PostgreSQL 9.3 puts each view column on a separate line, making it much easier to review the svn diff!

schemas/*.sql: updated for PostgreSQL 9.3. this reorders some functions, adds empty comment headers for omitted SEQUENCE SET commands, and (best of all) finally splits view columns onto multiple lines, so that changes in the columns are actually legible (and produce their own svn diff!)

schemas/vegbien.sql: analytical_stem_view, etc.: renamed scrubbed fields with the scrubbed_* prefix, to clearly distinguish these from the equivalent fields for other taxon names

schemas/vegbien.sql: analytical_plot: documented that this contains all of the analytical_stem columns, minus specimenHolderInstitutions, collection, accessionNumber, occurrenceID

schemas/vegbien.sql: analytical_stem_view: added individualCount

schemas/vegbien.sql: plot.**, analytical_stem_view: added slopeAspect, slopeGradient