Project

General

Profile

# Date Author Comment
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

10796 08/29/2013 04:50 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added datasource_rm(). this uses an internal schema-scoping parameter to ensure that the function always operates on tables in the schema it was defined in, rather than tables in the search_path. this ensures that when the public schema is renamed (e.g. from an imported version), the function will continue to operate on its own schema rather than whichever schema happens to be called public. this avoids any surprises if you are trying to remove a datasource in one schema, and don't want it to unintentionally be removed in another schema instead.

10792 08/29/2013 01:58 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added covering indexes on foreign keys where needed. this enables rows to be cascadingly deleted without a full table scan.

10711 08/22/2013 03:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: source.revision: renamed to import_revision for clarity

10710 08/22/2013 03:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.my.sql: updated with `make schemas/remake`

10694 08/20/2013 12:59 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem: synced with analytical_stem_view using sync_analytical_stem_to_view()

10351 07/19/2013 04:15 PM Aaron Marcuse-Kubitza

schemas/vegbien.my.sql: updated for new bin/repl text mode matching, which also affects non-regexps. this causes the replacement of a few more occurrences of PostgreSQL-only one-word typenames with their MySQL equivalents.

9971 06/20/2013 07:54 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: re-ran `make schemas/public/reinstall; make schemas/remake` cycle, which apparently changed sort order of statements

9929 06/19/2013 10:36 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: split geoscrub_input_view's new-row-only filtering into separate view geoscrub_input_new, so that the full geoscrub_input rows are still available. the reduction in geoscrub_input from eliminating the already-scrubbed rows was only 280,000 (5076500 - 4799173) out of a possible 1.7 million (1707970), so it makes sense to just run geoscrubbing on the full input. (the lower-than-expected reduction is most likely due to rows from pre-refresh data being present in the original geoscrub_output table, which have been replaced by different, post-refresh input rows.)

9923 06/19/2013 09:30 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: higher_plant_group_nodes: ferns and allies: added Lycopodiophyta node, as requested by Brad in the conference call (wiki.vegpath.org/2013-06-13_conference_call)

9898 06/13/2013 01:00 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: allow public_ to view lookup tables (cultivated_family_locations, higher_plant_group_nodes)

9859 06/12/2013 05:57 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added _filter_genus()

9416 05/16/2013 04:27 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: cultivated_family_locations: documented that table is from sftp://nimoy.nceas.ucsb.edu/home/bien/bien2_scripts/geoscrub/cultivated/cult_by_taxon/flag_by_taxa.inc (i.e. not generated by a function)

9414 05/16/2013 04:09 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: place.geovalid: require it to be NOT NULL so that it's always a 2-valued boolean (but default it to false since it's not a required field)

8779 05/02/2013 08:30 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added range_modeling_input view

8131 03/21/2013 01:53 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.

7869 03/06/2013 10:01 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Merged taxonName_verbatim and scientificNameAuthorship_verbatim into scientificName_verbatim

7846 03/05/2013 10:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Removed no longer needed unscrubbed_taxondetermination_view. Use taxonlabel joined to ScrubbedTaxon instead.

7843 03/05/2013 09:53 PM Aaron Marcuse-Kubitza

*.sql: Replaced concat_delim() with concat_ws(), which is built-in as of PostgreSQL 9.1 (http://stackoverflow.com/questions/1943433/postgresql-concat-ws-like-function)

7807 03/04/2013 11:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Added temperature_C, precipitation_m for possible use in John Wiens' plant climatic niche evolution project ("how fast is the rate of climatic niche evolution among species compared to projected changes in climate over the next 100 years? How do these rates compare for temperature and precipitation variables?")

7721 02/26/2013 06:46 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_specimen/analytical_plot: Added occurrenceRemarks

7714 02/26/2013 05:23 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Added occurrenceRemarks

7673 02/26/2013 11:05 AM Aaron Marcuse-Kubitza

Moved compass_dir from schemas/vegbien.sql to schemas/functions.sql so it can be used by _dms_to_dd()

7672 02/26/2013 10:56 AM Aaron Marcuse-Kubitza

Moved _dms_to_dd() from schemas/vegbien.sql to schemas/functions.sql so it can use functions schema functions

7671 02/26/2013 10:51 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added compass_dir enum

7659 02/26/2013 09:05 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added _dms_to_dd()

7644 02/21/2013 05:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: scientificName_verbatim: Use the taxonname (which omits the family and author) instead when available, at Brad's request

7634 02/20/2013 04:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: provider_count_view: Group data by dataprovider and dataset, rather than by each separately, to satisfy the GBIF attribution requirements as described by Brad

7632 02/20/2013 01:54 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Moved threatened_bien after growthForm as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#SpeciesLink>

7615 02/20/2013 07:58 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added analytical_plot view

7614 02/20/2013 07:55 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added analytical_specimen view

7613 02/20/2013 07:44 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Moved recordedBy, recordNumber before dateCollected as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#ACAD>

7611 02/20/2013 07:38 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added reproductiveCondition

7609 02/20/2013 07:28 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: plantobservation: Added reproductivecondition

7604 02/20/2013 04:51 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Moved identifiedBy, dateIdentified, identificationRemarks right after the *_verbatim terms that they relate to, as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#ACAD>

7602 02/20/2013 02:19 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added concat_delim()

7601 02/20/2013 01:43 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Removed deprecated taxonNameWithMorphospecies now that we have speciesBinomialWithMorphospecies

7600 02/20/2013 01:17 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: speciesBinomial: Added morphospecies suffix to create speciesBinomialWithMorphospecies

7466 02/05/2013 04:14 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added _mm_to_m()

7439 02/05/2013 02:22 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Renamed scientificNameWithMorphospecies to taxonNameWithMorphospecies because it does not contain the scientific name author, as required by DwC scientificName <http://rs.tdwg.org/dwc/terms/#scientificName>

7415 02/01/2013 04:34 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: removed all accessioncode fields, as VegBIEN does not use them

7403 01/31/2013 11:38 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Added family_matched, taxonName_matched, scientificNameAuthorship_matched

7402 01/31/2013 11:02 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Added family_verbatim, scientificName_verbatim, scientificNameAuthorship_verbatim from datasource taxondetermination

7371 01/25/2013 11:27 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination: Added is_datasource_current, which is autopopulated to the most recent datasource-provided taxondetermination

7370 01/25/2013 11:07 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination: Added taxondetermination_single_accepted_determination unique index to facilitate joining on the accepted determination

7369 01/25/2013 11:05 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination: Added taxondetermination_single_matched_determination unique index to facilitate joining on the matched determination

7368 01/25/2013 10:32 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxondetermination: Removed notespublic, notesmgt, which are not used by VegBIEN

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

schemas/vegbien.sql: analytical_stem_view: Added dateIdentified, identificationRemarks per Brad's request (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#E-mail-on-2013-1-16)