inputs/GBIF/_MySQL/Makefile: %.data.sql: Added agent table

Added inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.data.sql.md5

Added inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.schema.sql

Added web/main/svn*/, now using .htaccess to forward to Redmine/*

Removed web/main/svn, svn-web symlinks because they need to be .htaccess-es in order for the relative mod_rewrite commands to work correctly

Added web/main/svn, svn-web symlinks to Redmine/* for shorter URLs

Added web/main/Redmine/svn-web/

inputs/GBIF/: Added scripts for subsetting refresh

lib/sql.py: table_order_by(): Documented that it returns None if table is a view, because table_cluster_on() would return None. This is necessary for inputs/FIA/occurrence_all/ sorting to work correctly, because specifying a manual sort order would prevent the query planner from just using fast nested loop joins and instead cause it to perform a slow sort. (This appears to be a bug in the query planner, because when the column list specified matches the joined-on indexes, there should be no need for post-nested loop re-sorting.)

inputs/FIA/occurrence_all/test.xml.ref: Updated inserted row count for new row sort order

lib/db_xml.py: put_table(): Fixed bug where also need to advance start to fetch next set when table is a view, because the views that are now being used with the import (inputs/FIA/occurrence_all/) are static rather than dynamic and do not return different rows after the previous set of rows has been imported

inputs/FIA/occurrence_all/import: Removed no longer applicable comment that directional joins are needed for PostgreSQL query planner to avoid slow sorts

inputs/FIA/TREE/import: Reclustered table by TREE.parent path index, to facilitate path-order joins

inputs/FIA/occurrence_all/import: Changed all RIGHT JOINs to inner joins so that tables would be joined in path order (i.e. general->specific). This optimizes the incremental joins so that the small tables are joined to each other before being joined to the large tables, rather than each row of the large tables being looked up in the small tables. This effect may not be noticeable for small LIMIT values, but would become apparent for large LIMIT values, such as the 1-million-row partitions used by db_xml.put_table() for column-based import. Note that inner joins used to cause the query planner to produce incorrect results containing slow sorts, but now this appears to no longer be an issue, perhaps because the result is not sorted by the TREE.ID index (which is not in the same order as the path indexes *.unique, *.parent).

inputs/FIA/occurrence_all/import: Removed trailing whitespace

Removed unused inputs/FIA/COND_unique/. Use COND instead.

inputs/FIA/import: Use `set -o errexit` instead of putting ` || exit` after each command

lib/import.sh: map_table(): Removed unneeded () around psql. This also fixes a bug where an error exit status from psql would not have aborted the script because `set -o errexit` does not apply to commands enclosed in (). For () you need to use ` || exit` instead (or ` || return` inside a function).

lib/import.sh: Use `set -o errexit` so any command that exits with an error aborts the script. Note that a command's exit status can still be ignored using ` || true`. Removed no longer needed ` || return` in functions.

schemas/util.sql: Renamed rename_if_exists() to try_create() because it can be used to create a column in any way, not just by renaming another column

lib/import.sh: functions: abort if a command encounters an error

schemas/VegCore/mk_derived: Added cultivated from oldGrowth

schemas/util.sql: Added try_mk_derived_col()

inputs/FIA/*/import: Run mk_derived after postprocessing commands

inputs/FIA/import_order.txt: Added occurrence_all/

mappings/VegCore-VegBIEN.csv: subplotID,subplot -> location.sourceaccessioncode: Fixed bug where need /_first to handle the case where both subplotID and subplot are provided

Added inputs/FIA/map.csv, which maps shared columns to VegCore

inputs/FIA/FIA_COND_unique/test.xml.ref: Updated now that PLOT, CONDID have been mapped

inputs/FIA/*/map.csv for pre-refresh tables: Added back * before unmapped column names

lib/csvs.py: stream_info(): Fixed bug where headers with multiline columns were not supported because only the first line (not the first multiline row) is sniffed for the dialect

inputs/input.Makefile: %/header.csv: Fixed bug where newlines inside column names were incorrectly formatted by psql's table header formatting, by using COPY TO STDOUT instead

schemas/util.sql: Added do_optionally_ignore()

schemas/util.sql: Renamed rename_if_exists() to try_create() because it can be used to create a column in any way, not just by renaming another column

lib/import.sh: Added mk_derived(). Added mk_derived to usage template.

Added schemas/VegCore/mk_derived, which will be run in the import scripts

lib/import.sh: psql(): Set psql vars :schema, :table, :table_str for use by the psql commands

lib/import.sh: Export $schema, $table so they are available to programs invoked within an import script, which should not reset these vars if they include import.sh

lib/import.sh: Only set $table, $schema if they don't already exist

lib/import.sh: Added $root_dir and use it in $bin_dir

inputs/FIA/*/import: Use new mk_*_col()

schemas/*functions.sql: Renamed to *util.sql because now that these schemas are used by the new-style import scripts, there can be more than just functions in them

schemas/util.sql: Added mk_const_col()

schemas/util.sql: Added type_qual()

schemas/util.sql: mk_derived_col(): Added "idempotent" comment

schemas/util.sql: Added mk_derived_col()

inputs/FIA/COND/import: oldGrowth: Updated expr column names

schemas/util.sql: Added typeof(text, regtype)

inputs/FIA/*/import: Removed util. before function names because util is in the search_path

schemas/*functions.sql: Renamed to *util.sql because now that these schemas are used by the new-style import scripts, there can be more than just functions in them

schemas/functions.sql: Added existing_cols()

schemas/functions.sql: col_type(): Fixed bug where a NULL col name crashed the undefined_column throw, because MESSAGE can't be NULL and the NULL name was nulling out the entire message

schemas/functions.sql: Added col_exists()

inputs/FIA/COND/map.csv: Mapped SLOPE, ASPECT

web/main/.htaccess: remove linewraps (of the form table.path.vg/_-term) used to create a newline for Google spreadsheets

inputs/FIA/*/map.csv: Replaced . between table and column name with newline, so that table viewers like pgAdmin will display both the table and column name at the left edge of the header cell, rather than displaying only the table name because the column name doesn't fit. This fixes the problem of seeing a bunch of columns whose names all start with a table name, and not knowing what each of them is. It also preserves the ability to see at a glance which table a column is in, which helps in navigating wide tables. Removed * before unmapped terms, because whether a term is mapped is generally obvious from the table name itself.

inputs/input.Makefile: %/.map.csv.last_cleanup: Run fix_line_endings after canon/translate to standardize Python's \r\n line endings back to \n. This prevents issues with mixed line endings because LibreOffice (and probably Excel) treat all cell-internal line endings as \n but row line endings as whatever the file had, while text editors like jEdit translate all line endings to whatever the autodetected line ending is. (This creates spurious line ending diffs when a map spreadsheet containing multiline cells is edited in a text editor.)

Added bin/fix_line_endings to standardize \r\n line endings to \n

inputs/FIA/COND/import: Renamed COND.oldgrowth to VegCore name oldGrowth

inputs/FIA/*/map.csv: Ensured that joined columns are globally unique, so they don't map to an ambiguous VegCore term in the future

inputs/FIA/*/map.csv: Mapped terms to VegCore

schemas/functions.sql: col_type(): Include column name in error message

inputs/FIA/*/import: Updated column names to match map.csv

schemas/functions.sql: col_type(): Raise undefined_column exception if column does not exist, instead of silently returning NULL

inputs/FIA/import: Abort if any invoked script encounters an error

planning/timeline/timeline.2013.xls: Updated for current progress

inputs/FIA/*/map.csv: Removed no longer needed leading . from joined fields (globally-unique terms), because functions.to_global_col_names() is not used anymore

Added inputs/FIA/occurrence_all/, which combines all the core tables in a denormalized view. Note that it is not necessary to materialize this view into a (large) denormalized table, because the unique indexes and left/right joins allow the rows to be denormalized on the fly.

inputs/FIA/*/import: Use map_table to set column names based on the contents of map.csv, instead of using functions.to_global_col_names() and functions.rename_if_exists(). Added map.csv for all tables.

inputs/FIA/: Changed postprocess.sql scripts to import scripts that can be run directly. Added top-level inputs/FIA/import to run all of them together.

inputs/FIA/COND/postprocess.sql: Removed trailing whitespace

Added lib/import.sh, for use by new, simpler import scripts used by FIA. Note that for now, input.Makefile is still used to create map.csv.

inputs/input.Makefile: Moved postprocess.sql from $(exportHeader) to %/install because that is not part of the $(exportHeader) functionality. Added %/header.csv and use it in $(exportHeader).

inputs/input.Makefile: $(catSrcs): Fixed bug where need to use $(nonHeaderSrcs) instead of $(srcs) to exclude header.csv

schemas/functions.sql: map: Added additional columns that are present in the standard map spreadsheet format (filter, notes). These columns are necessary to make COPY FROM work, because it requires the # of columns to be the same in the input data and the output table.

inputs/input.Makefile: Moved $(cleanup) from $(exportHeader) to %/install because this is not part of exportHeader's functionality

inputs/input.Makefile: $(mkSrcMap): Use header.csv instead of the header of the CSVs, so that the column list in the map spreadsheet matches the actual DB table

inputs/input.Makefile: %.sql/run: Change to the directory the file is located in, so that includes (\i) are relative to the file, rather than relative to whatever happens to be the current directory

inputs/input.Makefile: %/install: Always generate a header.csv, even for CSV inputs with their own header. This will include the actual column names in the staging table, which may differ from their names in the CSVs (e.g. the addition of row_num). Note that header.csv is not included in the CSVs list itself, and will not override the header or dialect in them.

schemas/functions.sql: Added set_col_names()

schemas/functions.sql: rename_if_exists(): Also ignore duplicate_column exceptions, which are generated when a column is renamed to itself (as well as when two columns are renamed to the same place)

schemas/functions.sql: Added col_names(regclass), which unlike col_names(regtype) returns names in the order they are in the table

schemas/functions.sql: Added map_values()

schemas/functions.sql: map_get(): Fixed bug where can't use STRICT in EXECUTE INTO because there will sometimes be no match, causing a "query returned no rows" error

schemas/functions.sql: rename_cols(): Support any renames type with an -> operator

schemas/functions.sql: Added operator ->(regclass, text)

schemas/functions.sql: Added map_get()

schemas/functions.sql: table2hstore(): Made it STABLE instead of IMMUTABLE because the input table is not constant

schemas/functions.sql: Added table2hstore()

schemas/functions.sql: Added reset_map_table()

schemas/functions.sql: Added truncate()

schemas/functions.sql: mk_map_table(): Use the sql language instead of plpgsql because EXECUTE is not used directly, so plpgsql is not actually needed

schemas/functions.sql: mk_map_table(): Store map table schema in separate `map` table and extend it using LIKE, for easier maintainability of the map schema

schemas/functions.sql: Added mk_map_table()

schemas/functions.sql: ensure_prefix(): Made it IMMUTABLE instead of STABLE

schemas/functions.sql: Added rename_cols()

inputs/FIA/*/postprocess.sql: Avoid using :table, :table_str so that the commands in the script can also be run by pasting them into pgAdmin

README.TXT: Full database import: Manual steps to run TNRS/remake analytical DB: Added `export version=<version>` to ensure that the import is run into the correct schema. Since these instructions are for running commands separately from the rest of the import, it's important to first ensure that the import environment is set up properly.

schemas/vegbien.ERD.mwb: Added taxon_trait to ERD

schemas/vegbien.ERD.mwb: Regenerated exports

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.