schemas/util.sql: added qual_name()

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/util.sql: use new util.debug_print_sql() instead of `RAISE NOTICE` to improve readability of the debug output (which is used throughout the runscripts)

schemas/util.sql: added debug_print_sql()

schemas/util.sql: added raise_notice(), to allow NOTICEs to be raised in sql-language functions

schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.

schemas/util.sql: added rematerialize_view()

schemas/util.sql: schema(regtype), schema(regclass): use new schema(pg_namespace_oid oid)

schemas/util.sql: added schema(pg_namespace_oid oid)

schemas/util.sql: schema(regclass): use joins instead of nested SELECTs for consistency with schema(regtype)

schemas/util.sql: regenerated from DB, which reorders the functions

schemas/util.sql: table_name(): renamed to name(regclass) for consistency with other name(...) functions

schemas/util.sql: table_schema(): renamed to schema(regclass) for consistency with other schema(...) functions

schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity

schemas/vegbien.sql: added schema_anchor, for use with schema_null params

schemas/util.sql: added mk_search_path()

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)

schemas/util.sql: added materialize_view()

schemas/util.sql: added table_name()

schemas/util.sql: table_schema(): removed unneeded STRICT, which prevents inlining

fix: schemas/util.sql: fix_array(): removed no longer accurate comment about effects of STRICT, which is no longer used

fix: schemas/util.sql: materialize_query(): 1st param should be named table_, not view_

schemas/util.sql: added drop_view()

schemas/util.sql: added materialize_query()

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/util.sql: ifnull(): use COALESCE instead, because it turns out that MySQL's IFNULL is just a special case of this

schemas/util.sql: added ifnull(), equivalent to MySQL's IFNULL (Postgres auto-lowercases the name)

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/util.sql: diff_cols(): documented how to run EXPLAIN on the FULL JOIN query

schemas/util.sql: diff_cols(): embed left_query/right_query directly in the main SQL statement, so that the query planner can take the sort order of the queries into account in planning the FULL JOIN (and where possible, use a fast merge join)

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/util.sql: added eval2col_pair()

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

schemas/Makefile: vegbien.sql: support auxiliary schemas besides *_validation

fix: schemas/Makefile: %/publish: removed no longer applicable instructions about running `make schemas/rotate`. you should not publish a schema until you are satisfied that it can replace the previous public schema, so that the previous public schema doesn't need to be saved.

fix: schemas/vegbien.sql: public_validation comment: undid incorrect replacement of "validation" with "public_validation"

bugfix: schemas/Makefile: "public": dont

schemas/Makefile: %/publish: don't ignore errors if schema doesn't exist, because it should always exist if you are publishing it

schemas/Makefile: `%/uninstall: vegbien.sql`: use util.schema_bundle_rm() instead of public.rm(), because when the public schema is incompletely imported, public.rm() will not yet exist

schemas/Makefile: `%/install: vegbien.sql`: support auxiliary schemas besides *_validation

schemas/Makefile: %/uninstall, %/publish: use new public.rm(), publish(), which encapsulate the process of renaming multiple related schemas and also allow additional auxiliary schemas beyond just *_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/util.sql: added functions for maintaining schema bundles, groups of schemas with a common prefix

schemas/util.sql: added schema_bundle_get_schemas()

schemas/util.sql: added schema_replace()

schemas/util.sql: added schema_rm(), schema_rename()

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/Makefile: %/install, %/publish for public schema: added usage

removed schemas/VegX/run.py, which is a duplicate of _archive/bienDjango/trunk/bien/v/models.py (this may have ended up here because it was a wiki page attachment, but the initial revision did not indicate the source)

schemas/VegCore/vegbien_identifier_examples.xlsx: changed to symlink to Brad_Boyle/vegbien_identifier_examples.xlsx, to avoid duplicate copies

schemas/Makefile: removed no longer used rename/%, rotate. use %/install, %/publish instead to manage public schema versions.

schemas/Makefile: removed no longer needed public/uninstall, which is handled more generally by `%/uninstall: vegbien.sql` instead

schemas/Makefile: `%/uninstall: vegbien.sql`: added support for *_validation schema

schemas/Makefile: %/uninstall: split into 2 rules like for %/install, so that different actions can easily be added for the public schema

schemas/Makefile: %/publish: support auxiliary *_validation schemas

bugfix: schemas/Makefile: `%/install: vegbien.sql`: need -e before each s/// expression

schemas/Makefile: `%/install: vegbien.sql`: create a custom _validation schema for *each public schema, rather than one for the most recently-created public schema. this allows validations to continue to be run against a previous version of the DB while a new version is being imported.

schemas/vegbien.ERD.mwb: regenerated exports

schemas/vegbien.ERD.mwb: synced to schemas/vegbien.my.sql, which adds the validation schema

schemas/util.sql: added diff_views()

schemas/util.sql: added diff_cols(), for use with the aggregating validations

bugfix: schemas/Makefile: public/uninstall: need to uninstall the validation schema in addition to the public schema (deleting the public schema would only cascade to the contents of the validation schema)

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.

fix: schemas/Makefile: %/uninstall: don't prompt to delete auxiliary schemas, as these do not contain data

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