Project

General

Profile

# Date Author Comment
12389 02/23/2014 07:43 PM Aaron Marcuse-Kubitza

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

12387 02/23/2014 07:36 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validatable_datasources()

12383 02/23/2014 07:01 PM Aaron Marcuse-Kubitza

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

12367 02/23/2014 12:13 PM Aaron Marcuse-Kubitza

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.

12366 02/23/2014 12:08 PM Aaron Marcuse-Kubitza

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

12365 02/23/2014 12:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added query_view_relation_max_prefix_len()

12352 02/22/2014 07:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query

12351 02/22/2014 07:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added new_query_view_relation_name()

12338 02/22/2014 04:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query

12336 02/22/2014 03:48 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views

12335 02/22/2014 03:35 PM Aaron Marcuse-Kubitza

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

12318 02/20/2014 06:14 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)

12317 02/20/2014 05:52 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: renamed output views to match input views

12316 02/20/2014 04:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)

12309 02/18/2014 07:16 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_tables()

12307 02/17/2014 09:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added validation_views()

12287 02/17/2014 02:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added remake_diff_table()

12264 02/16/2014 11:59 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_in_view()

12261 02/16/2014 10:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)

12259 02/16/2014 09:47 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)

12258 02/16/2014 09:39 PM Aaron Marcuse-Kubitza

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

12255 02/16/2014 08:55 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for

12253 02/16/2014 08:49 PM Aaron Marcuse-Kubitza

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

12252 02/16/2014 08:46 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)

12248 02/16/2014 05:56 PM Aaron Marcuse-Kubitza

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

12237 02/16/2014 06:42 AM Aaron Marcuse-Kubitza

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

12224 02/14/2014 03:09 PM Aaron Marcuse-Kubitza

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.

12186 02/13/2014 04:06 PM Aaron Marcuse-Kubitza

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

12182 02/13/2014 02:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*

12169 02/13/2014 08:34 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validation_plots: added Brad's notes as comments

12168 02/13/2014 08:29 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql

12165 02/13/2014 08:20 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validation_traits: added Brad's notes as comments

12164 02/13/2014 08:17 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validation_traits: populated queries from validation/aggregating/traits/bien3_validations_traits_bien3.sql

12156 02/13/2014 05:58 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem.datasource: removed fkey to source.shortname because this prevents individually-reloaded datasources from being published

12154 02/13/2014 05:49 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added datasource_unpublish()

12147 02/08/2014 10:38 PM Aaron Marcuse-Kubitza

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

12138 02/08/2014 03:55 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm(), which deletes this schema

12137 02/08/2014 03:17 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added publish(), which publishes this schema

12136 02/08/2014 02:47 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added public_schema_publish()

12130 02/07/2014 06:58 PM Aaron Marcuse-Kubitza

bugfix: schemas/Makefile,vegbien.sql: renamed validation to public_validation to match the suffixed name applied by `make schemas/public/install`

12042 02/05/2014 01:02 AM Aaron Marcuse-Kubitza

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.

12024 02/02/2014 07:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added top_plot view

12014 01/28/2014 04:56 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: location: added autopopulated top_plot

12013 01/26/2014 11:09 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent: added locationevent_pull_forward_from_parent() trigger which populates project_id from the parent locationevent

12010 01/25/2014 11:17 AM Aaron Marcuse-Kubitza

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

12006 01/23/2014 11:21 AM Aaron Marcuse-Kubitza

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)

11990 01/22/2014 12:24 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: projectcontributor: added missing unique constraint

11970 01/20/2014 11:33 AM Aaron Marcuse-Kubitza

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

11963 01/15/2014 11:23 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plot.**, analytical_stem_view, analytical_plot: added project_contributors

11962 01/15/2014 11:14 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added project_contributors()

11908 12/12/2013 08:35 AM Aaron Marcuse-Kubitza

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.

11907 12/12/2013 08:27 AM Aaron Marcuse-Kubitza

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.

11838 12/05/2013 08:35 AM Aaron Marcuse-Kubitza

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

11826 12/04/2013 10:00 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields

11736 11/25/2013 12:59 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: collected_dates: documented runtime (2.5 min)

11735 11/25/2013 12:57 AM Aaron Marcuse-Kubitza

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.

11730 11/24/2013 11:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added collected_date_min view

11710 11/21/2013 08:53 AM Aaron Marcuse-Kubitza

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!

11667 11/14/2013 02:32 PM Aaron Marcuse-Kubitza

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

11622 11/09/2013 10:02 PM Aaron Marcuse-Kubitza

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

11529 10/31/2013 05:36 AM Aaron Marcuse-Kubitza

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

11527 10/31/2013 05:29 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: added individualCount

11526 10/31/2013 04:42 AM Aaron Marcuse-Kubitza

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

11504 10/30/2013 07:17 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination.isoriginal: made it nullable like iscurrent__verbatim, because this is populated from the datasource. taxondetermination_set_iscurrent() now supports isoriginal=NULL, so this is not a problem.

11503 10/30/2013 07:08 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination.is_datasource_current: renamed to iscurrent__verbatim and made it nullable, so that this can be used to store the verbatim iscurrent status

11483 10/30/2013 02:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added 2013-7-10.Naia.range_limiting_factors

11482 10/30/2013 02:45 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: sync_analytical_stem_to_view(): row_num: renamed to taxon_occurrence__pkey because previous taxon determinations have been removed, so each row is in fact a taxon_occurrence (~= VegCore.vegpath.org?ERD.taxon_occurrence)

11480 10/30/2013 01:58 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: added row_num, which can serve as the taxon_observation ID (DwC occurrenceID)

11477 10/30/2013 12:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: locationID... index: use eventDate instead of dateCollected since it's now eventDate that identifies the locationevent

11476 10/30/2013 04:41 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: use plot.** to obtain plot-related fields, so that the same code does not need to be maintained in both analytical_stem_view and plot.**

11475 10/30/2013 04:32 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: moved specimen-specific fields to occurrence section

11474 10/30/2013 03:50 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view, plot.**: added separate location__cultivated__bien

11473 10/30/2013 03:11 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added separate eventDate, in addition to dateCollected

11469 10/29/2013 06:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**

11460 10/29/2013 07:42 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added 2013-10-18.Brian_Enquist.Canadensys view

11459 10/29/2013 06:51 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: sync_analytical_stem_to_view(): added index on speciesBinomialWithMorphospecies for Brian Enquist's Canadensys request

11401 10/22/2013 06:21 PM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: analytical_stem_view: renamed specimens columns to use the VegCore names, where these differ from DwC, so that the now-VegCore staging table column names are the same as the analytical_stem_view column names

11400 10/22/2013 06:16 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: regenerated using `make schemas/remake`. note that analytical_stem_view column renamings need this step after a search-and-replace of the column names, in order to remove excess "" around all-lowercase names and reset generated index names.

11166 10/03/2013 03:44 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added plot.** subset view of analytical_stem_view, used for VegBank validation

11155 10/02/2013 03:58 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: added instructions for what to do after updating this

11154 10/02/2013 03:55 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view, analytical_plot: added communities using new locationevent__communities__array()

11153 10/02/2013 03:52 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added locationevent__communities*()

11147 09/30/2013 07:34 AM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: locationevent: added pull-forward trigger to populate location_id from the parent event. a pull-forward trigger must be used for this (not a COALESCE in analytical_stem_view) because the locationevent cannot even be found by analytical_stem_view if it does not have an associated location.

11103 09/28/2013 08:23 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: locationeventcontributor: added missing unique constraint (locationeventcontributor_unique)

11102 09/28/2013 08:21 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: locationeventcontributor.role: made it optional because some locationeventcontributors may not have an associated role

11101 09/28/2013 06:45 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view, analytical_plot: added plot__collectors, which uses locationevent__contributors__array()

11100 09/28/2013 06:42 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: locationevent__contributors(): split into set-returning and array-returning functions. sort results by fullname so there is a predictable ordering (VegBank does not include a sort_order in the observationContributor table).

11099 09/28/2013 05:56 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added locationevent__contributors() aggregating function for use in analytical_stem_view

11083 09/24/2013 02:47 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: added stratum table (stratum__name)

11079 09/24/2013 01:51 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: stratum: added unique constraint on source_id/stratumname, and require stratumname, so that strata are identified by name within their datasource

11078 09/24/2013 01:45 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added source_id to allow different datasources to have their own strata

11074 09/24/2013 01:01 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: link locationevent to stratum instead of the other way around (similar to how it's represented in normalized VegCore as stratum_event->stratum, rather than stratum->taxa_sampling_event). this works around column-based import's lack of support for linking two tables together via two separate routes (in this case locationevent->taxonoccurrence->stratum and locationevent->stratum).

11072 09/24/2013 12:09 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: stratum: added stratumname

10861 09/04/2013 02:55 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: source: removed testing row that had gotten in during `make schemas/remake`

10859 09/04/2013 02:32 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: datasource_publish(): need to remove the current live datasource instead of the datasource to publish. note that datasource_rename() does not currently generate an error if the specified datasource doesn't exist.

10858 09/04/2013 02:27 PM Aaron Marcuse-Kubitza

bugfix: schemas/vegbien.sql: datasource_publish(): run it in a nested transaction so that there is always one published copy of the datasource. (note that a nested transaction is not automatically created for each function, http://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures?In_PG_your_procedures_aren%27t_separate_transactions#answer-6283201 .)

10857 09/04/2013 01:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added datasource_publish()

10856 09/04/2013 01:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added datasource_rename()

10855 09/04/2013 01:51 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added rm_version_suffix()

10824 08/30/2013 03:03 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxon_trait: added query to use to export