inputs/VegBank/taxon_observation.**/postprocess.sql: run mk_subset_by_row_num_func() to add a subset function that uses sort_col. this is used by column-based import, and also provides a common subsetting/sorting API for all the left-joined views. test.xml.ref: the inserted row count most likely changes because the sort order changes.
schemas/util.sql: added mk_subset_by_row_num_func(regclass), which uses a sort_col instead of a row_num column
schemas/util.sql: mk_subset_by_row_num_func(): factored creation of no-sort subset function into mk_subset_by_row_num_no_sort_func() so it can be used by other functions
bugfix: inputs/VegBank/stemlocation_/map.csv: remapped stemcount-related fields to OMIT, so that these don't collide with fields of the same name in stemcount_ when they are left-joined together in taxon_observation.** . having the same name causes these to be incorrectly interpreted as shared fkey columns in the NATURAL JOIN (and without the NATURAL JOIN, they would instead be collision errors).
bugfix: inputs/VegBank/stemlocation_/postprocess.sql: added missing index on aggregateOrganismObservationID, needed for the 1:many portion of the taxon_observation.** left-join
inputs/VegBank/stemcount_/postprocess.sql: moved stemcount___parent index before the derived columns section because it does not depend on them
bugfix: inputs/VegBank/stemcount_/postprocess.sql: added missing index on taxonOccurrenceID, needed for the 1:many portion of the taxon_observation.** left-join
schemas/util.sql: added ||% operator to append to escaped strings (the % indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: added sort_col (=identificationID) at beginning because column-based import will always sort a view by the first column, which may lead to slow query plans if the first column is not a joined table's pkey
inputs/VegBank/taxon_observation.**/postprocess.sql: documented that there is no row_num because left-join to stemcount_, stemlocation_ adds rows to each taxonobservation_
bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: removed row_num (=identificationID), because there is actually more than one row per VegBank taxonobservation_, so this does not properly enumerate the view rows. this is because there is a 1:many left-join to stemcount_, stemlocation_ which adds rows to each taxonobservation_. since the row_num is gone, any row-subsetting of the view using OFFSET will always need to materialize the entire view up to the OFFSET value. this works for smaller datasources like VegBank that fit almost entirely into one column-based import chunk (1 million rows), but not for larger datasources like FIA where it would be much slower to materialize all preceding 16 million rows on the last chunk (which is what OFFSET normally does with left-joins).
bugfix: inputs/VegBank/taxon_observation.**/: generated header.csv and related files, which were previously not generated because the error in `rm header.csv` aborted the runscript
bugfix: lib/runscripts/*: calls to rm: use `rm -f` instead to avoid an error (which aborts the program) if the file does not yet exist
inputs/VegBank/: added taxon_observation.** left-join of the tables, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
inputs/VegBank/taxonobservation_/create.sql: join starting with taxoninterpretation so that we can use the taxoninterpretation_id as the row_num (text strings, formed from concatenated #s cannot be used as a row_num). there is only 1 taxonobservation without a taxoninterpretation, so we can just include one row for each taxoninterpretation.
bugfix: inputs/VegBank/taxonobservation_/test.xml.ref: updated after reloading staging table. this fixed a bug where observationGranularity apparently either did not exist or was not the right type of constant column to be properly inlined the last time the tester was run. the inlining is important for using metadata switches to generate the correct XML import script.
bugfix: lib/sh/make.sh: don't allow rm to override remake if an invoked script uses this file. this fixes a bug in `rm=1 inputs/.../.../run` where the remake action would be invoked on the map_table command even though it had been suppressed, because it was run externally (i.e. make.sh was reloaded) and the rm=1 flag was still active
schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order: moved traceable before party because party is a subclass of it
bugfix: inputs/input.Makefile: `%/install: %/create.sql`: don't include %/header.csv as a target, so that it won't get deleted if the install fails (especially on a step that happens after the header is exported)
inputs/VegBank/taxonobservation_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
inputs/VegBank/stemcount_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
bugfix: schemas/util.sql: mk_subset_by_row_num_func(): need explicit ORDER BY on the row_num_col, to prevent PostgreSQL from sorting the rows in reverse (they will be sorted because of the index scan, but the direction is otherwise indeterminate)
schemas/util.sql: mk_subset_by_row_num_func(): support tables whose pkeys don't start with 1 (such as the VegBank tables), by calculating the smallest row_num from the table
schemas/util.sql: offset2row_num(), limit2row_num(): added optional min_row_num, for tables whose serial pkeys start from a value other than 1
schemas/util.sql: added qual_name(regclass)
schemas/util.sql: added esc_name__append()
schemas/util.sql: added col__min()
schemas/util.sql: added limit2row_num() and use it in mk_subset_by_row_num_func() for clarity
schemas/util.sql: added offset2row_num() and use it in mk_subset_by_row_num_func() for clarity
bugfix: schemas/Makefile: `%/install: vegbien.sql`: when replacing public with the specified schema name, only perform the replacement if the schema is named something other than public. this prevents text like "public schema" inside comments from being "-escaped.
planning/timeline/timeline.2013.xls: updated for progress
bugfix: /README.TXT: to backup files not in Time Machine: need to use -E option to sudo to preserve env, after installing the latest system update
schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order
schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
schemas/VegCore/VegCore.ERD.mwb: party: added required name field
schemas/VegCore/VegCore.ERD.mwb: fixed table positions
schemas/VegCore/VegCore.ERD.mwb: event: added event_unique_within_parent, event_unique_within_place unique indexes
schemas/VegCore/VegCore.ERD.mwb: source: split into source and dataset tables, since many of the source fields (first_publisher, data_owners, contacts) were only applicable to datasets. note that a dataset is specifically something requiring attribution, while a source just indicates where something came from. non-dataset sources are useful e.g. as the taxon_concept.according_to.
schemas/VegCore/VegCore.ERD.mwb: source.owners: renamed to data_owners for clarity. documented that this is the parties who must be given attribution, such as copyrightholders.
schemas/VegCore/VegCore.ERD.mwb: source: allow multiple contacts
schemas/VegCore/VegCore.ERD.mwb: source: added contact (different from owners)
schemas/VegCore/VegCore.ERD.mwb: model options: templates for new column names, etc.: configured to our conventions (pkey is "id"; columns are unique within the table rather than being prefixed with the table name)
schemas/VegCore/VegCore.ERD.mwb: source: allow multiple data owners
schemas/VegCore/VegCore.ERD.mwb: documented that record is the record in the source data, and traceable is a row that can have associated source information
schemas/VegCore/VegCore.ERD.mwb: removed relationship table, because this is too general, and custom relationships between entities are better indicated in the applicable hstore extender field for the table in question. record: split into record and traceable tables, where record is instead the record in the source data, and traceable indicates that a row has associated source information.
bugfix: schemas/VegCore/VegCore.ERD.mwb: party_list_entry: use just a single id field as the primary key, because the pkey must be a single text string in order to uniquely identify the row by a global ID
schemas/VegCore/VegCore.ERD.mwb: party_list_entry: added role field
schemas/VegCore/VegCore.ERD.mwb: taxa_sampling_event: link directly to project, so that you don't have to search the parent event hierarchy to find the associated project
schemas/VegCore/VegCore.ERD.mwb: spaced tables out more
schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: require all taxon_occurrences to have an associated within_place (which may be set to Earth if the coordinates/placename is unknown)
schemas/VegCore/VegCore.ERD.mwb: moved the plot table next the the section label for the plots half of the page
schemas/VegCore/VegCore.ERD.mwb: taxon_observation: allow any taxon_observation to link directly to a taxa_sampling_event, not just aggregate_observation. this allows any taxon_observation's methodology information to be found directly by joining to taxa_sampling_event. previously, one had to join to event.parent and then taxa_sampling_event, and it was not clear that event.parent would even be a taxa_sampling_event.
schemas/VegCore/VegCore.ERD.mwb: aggregate_observation: link to rather than being a taxa_sampling_event, because taxa_sampling_event was actually intended to be the container event in which various types of taxon_observations occur, rather than a type of taxon_observation itself. rearranged tables to avoid them covering any lines.
schemas/VegCore/VegCore.ERD.mwb: renamed GNRS & geovalidation steps to just geoscrubbing steps for clarity
schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: added within_place to allow easily locating all the taxon_occurrences in a plot. (some taxon_occurrences require within_place, but there was no common superclass field to allow joining to just a single table.)
schemas/VegCore/ERD/VegCore.ERD.png.map.htm: moved reobservable tables (individual, specimen, etc.) before aggregate_observation tables because these are less abstract
schemas/VegCore/VegCore.ERD.mwb: individual_count: inherit from taxon_presence because this is also a type of taxon_observation (and specifically, a taxon_presence), in addition to the general taxon_presence it's associated with. it is possible e.g. for different diameter classes to be counted by different collectors, and thus to need separate taxon_observations to store the different collectors.
schemas/VegCore/VegCore.ERD.mwb: added taxon_occurrence to store the underlying occurrence for each taxon_observation. we cannot use reobservable for this because not all taxon_observations are reobservable, but the one-time observations still have an associated occurrence. moved reobservable.current_determination to taxon_occurrence and renamed it to current_observation (this can be used to find the latest observation for each occurrence).
planning/timeline/timeline.2013.xls: added spacing between later column and weeks to make clear that dots in the later column do not correspond to a week
inputs/FIA/occurrence_all/: renamed to taxon_observation.** to clarify what an occurrence is. taxon_observation has the same meaning as in VegCore, where each taxon_determination is considered a separate taxon_observation of the associated specimen or vouchered plant (vegpath.org/VegCore/ERD/). (note that having multiple taxon_determinations only makes sense when there is something to reobserve.) the .** is SQL dotpath syntax (wiki.vegpath.org/SQL_dotpaths) for the recursive expansion of all tables to which taxon_observation has forward fkeys (i.e. "the left-join").
inputs/FIA/occurrence_all/postprocess.sql: use :table_str var instead of hardcoding the table name as occurrence_all, to avoid needing to change this file when renaming the view
inputs/FIA/occurrence_all/postprocess.sql: renamed occurrence_all--row_num to just row_num because this is for the entire view, rather than a specific table in it, and thus does not need a disambiguating table prefix. this also avoids embedding the view name in its own columns.
inputs/FIA/*/map.csv: use -- to separate the table and column name instead of - , to conform with the u-name format (wiki.vegpath.org/u-name#format), which works even when only one of _- can be used in the name. -s are needed in this case to linewrap the column on a separate line as the table in phpPgAdmin.
lib/runscripts/datasrc_dir.run: added postprocess target to run postprocess in just the table subdirs, skipping any additional subdirs that don't have this target
lib/runscripts/datasrc_dir.run: @subdirs: moved import_order.txt subdirs into separate @table_subdirs, which provides access to just the table subdirs when the user adds other dirs to @subdirs
inputs/VegBank/: prepended the table name to each column name to prevent column collisions, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
inputs/VegBank/: switched to new-style import, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
bugfix: inputs/VegBank/stemlocation_/map.csv: put columns in table order, which is needed by new-style import
inputs/VegBank/stemlocation_/: translated one-to-many mappings to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
bugfix: inputs/VegBank/taxonobservation_/map.csv: put columns in table order, which is needed by new-style import
bugfix: inputs/VegBank/plot_/postprocess.sql: coordinateUncertaintyInMeters: need to use GREATEST instead of _alt() to handle cases where the coordinate uncertainty is > than the fuzzing uncertainty, where you wouldn't want to just use the smaller fuzzing uncertainty
inputs/VegBank/plot_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
inputs/VegBank/plot_/postprocess.sql: map_*() derived cols: updated runtime
inputs/VegBank/plot_/: translated single-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
inputs/VegBank/stemcount_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
inputs/VegBank/stemlocation_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
inputs/VegBank/taxonobservation_/postprocess.sql: scientificName: recorded runtime (15 s)
inputs/VegBank/taxonobservation_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
inputs/FIA/occurrence_all/postprocess.sql: use much simpler LEFT JOINs instead of nested RIGHT JOINs, which required lots of () to get them to happen in the right order. note that the columns are now provided in reverse instead of forwards path order, but this is still much clearer than the nested mess of RIGHT JOINs. this approach can also be used to simplify VegBank's joins.
bugfix: lib/runscripts/view.run: remake_VegBIEN_mappings(): also need to remake header.csv, not just map.csv as for tables, because view columns may change when the view is regenerated
schemas/VegCore/VegCore.ERD.mwb: specimen: changed definition to "something collected from a plant" rather than just "a physical part of a plant", to support using this table for identifying pictures and descriptions of a plant (as DwC does)
schemas/VegCore/VegCore.ERD.mwb: reobservable_presence: allow it to be vouchered by any reobservable element (including a tagged individual), not just a specimen
schemas/VegCore/VegCore.ERD.mwb: specimen.defining_data: clarified that the observations in this are actually a subset of individual_observation.traits (specifically, the subset that can be used to make a taxonomic redetermination). information in this field should therefore always also be stored in individual_observation.traits.
schemas/VegCore/VegCore.ERD.mwb: specimen: added specimen_unique_in_individual_observation unique constraint, analogous to specimen_unique_in_individual
schemas/VegCore/VegCore.ERD.mwb: specimen: added defining_data, which for a digital-only specimen, stores the information that comprises the specimen. note that a taxon_presence without a physical voucher can still qualify as reobservable if a detailed description of it is provided in this field, to make taxonomic redeterminations on. for datasources like VegBank, which incorrectly allow multiple taxon_determinations for any type of taxon_observation, their taxonomic redeterminations would actually be considered invalid if made on a purely taxon_presence observation (i.e. just a taxon name) without a detailed description that could be used to make a redetermination. this is different than the scrubbing of a taxon name, which relates a taxon name to another taxon name, rather than a taxon_observation to a completely different taxon name.
bugfix: lib/sh/util.sh: set_fds(): don't add surrounding quotes to empty redirect dest
bugfix: lib/sh/util.sh: set_fds(): need to check if redirect is empty before escaping it with `printf %q`, which may add surrounding quotes to an empty string
planning/timeline/timeline.2013.xls: attribution and conditions of use: documented that Brad/Brian/Bob should work on this, as decided in the conference call (wiki.vegpath.org/2013-09-12_conference_call#data-provider-metadata)
planning/timeline/timeline.2013.xls: reformatted to fit all rows and all per-week columns on one page
planning/timeline/timeline.2013.xls: streamline process of mapping and adding a new datasource: added subtask to create interactive scripts for each import step
planning/timeline/timeline.2013.xls: improve and complete data provider metadata: moved to end because this can also been added manually to the source table, and does not have to be in place before running column-based import
planning/timeline/timeline.2013.xls: flatten the datasources to a common schema: added subtask to left-join unvalidated datasources since they need the flattening in order to validate them properly
planning/timeline/timeline.2013.xls: rebalanced dots
planning/timeline/timeline.2013.xls: moved items marked later to separate section at bottom
