web/: datasources indexed in VegPath: Added .htaccess files to corresponding inputs/ dir so the short URLs can also be accessed via VegBIEN's own dir for the datasource

inputs/input.Makefile: Removed .PRECIOUS from %/header.csv, %/map.csv so that these scripts are deleted on error. This is useful for the runscripts and for non-data dirs whose header.csv cannot be made.

inputs/input.Makefile: SVN: Only run %/add on subdirs with visible (non-hidden) files. Subdirs with only hidden files (e.g. .htaccess) are assumed to be non-data dirs.

inputs/input.Makefile: Staging tables installation: %/install: $(logInstall): Only use log file if log dir exists, to support non-data dirs

inputs/input.Makefile: Staging tables installation: %/install: ignore errors in $(exportHeader) and $(cleanup) if the table does not exist (i.e. a non-data dir)

inputs/input.Makefile: Staging tables installation: %/install: Don't run $(import_install_) for empty dirs because there is no data to import

lib/sql.py: parse_exception(): typed_name_re: Fixed bug where need to require the "" around the table/column name, because otherwise, the regexp will try to match as few characters as possible, causing it to match only the first letter of the name

web/**/.htaccess: Removed "handle DirectoryIndex" rule, which does not appear to be needed with the new dotpath format

web/VegBank/.htaccess: RewriteRule: Fixed bug where need to match tables by themselves as well as followed by columns

web/**/.htaccess: Added "don't rewrite existing paths" and "handle DirectoryIndex" rules to all .htaccess files, not just those for dirs that contain subdirs, to facilitate linking directly to files in the filesystem without first needing to retrofit the applicable .htaccess file.

web/**/.htaccess: Restrict redirection only of fully-existing paths, rather than of any path whose subdir exists, in order to avoid having to hardcode the absolute path to the directory containing the .htaccess file. This more restrictive approach still works because if the subdir exists and contains an .htaccess file, this (outer) .htaccess file's RewriteRules will be ignored completely until the inner .htaccess file's rules have been run, so that any subdir redirects will happen first. This "hole-punch" effect causes an existing filesystem path to always take priority over a virtual path (i.e. a redirect) defined in an outer .htaccess file.

Added web/datasources/ symlink to inputs/

web/servers/vegbiendev/fs: Updated symlink for new web/ dir nesting level

Deleted no longer used www/

Renamed www/ back to web/

Removed www/logs/ because it was actually successfully moved into main/ (but some of its files weren't)

Added www/logs/

web/: Moved auxiliary files into the main/ subdir in preparation for having just the web/ dir. Renamed web/ to www/ so it can be replaced with web/main/.

web/main/servers/vegbiendev/: Split it into db and fs forks, with db being the default. The fs fork links directly to /home/bien/svn on vegbiendev, which makes world-readable files directly web-accessible. (Permissions on /home/bien/svn and subdirs have been checked to ensure that private files are not world-readable.)

Added web/main/IH/db/

web/main/index.php: Updated fragment redirect for new dotpath format (using ? instead of a relative path)

web/main/index.php: Updated path templates for new dotpath format (using ? instead of /)

web/main/**/.htaccess: Support dotpaths in the query string instead of in the path, so that non-dotpath paths don't need to be suffixed with / to prevent their filenames from being interpreted as dotpaths. Putting dotpaths in the query string still requires only one character between the host and the path, but it's ? instead of / . ? is in many ways more natural, because the dotpath is a non-filesystem string to be parsed rather than something that's already a filesystem path. This change also avoids the need to strip trailing /s in many RewriteRules, because the dotpath mechanism is no longer appending them.

Added web/main/dotpath.php, which parses any dotpath in the query string

web/main/util.php: Added coalesce()

web/main/svn*: Fixed symlinks to use .redmine instead of Redmine

web/main/IH/.htaccess: Fixed whitespace

web/main/IH/.htaccess: RewriteRule: Fixed bug where need to \-escape % because it's a special character in the replacement string (it references a regexp group matched by the last RewriteCond)

Removed web/main/Redmine symlink so that it isn't listed in the directory listing as a visible (non-hidden) file. (There is already a .redmine symlink which matches all case variations via web/main/.htaccess logic.)

Added web/main/svn* symlinks to VegBIEN/Redmine/svn*

web/main/svn*/: Removed in preparation for replacing with symlinks, which will work now that absolute paths are used where needed

web/main/**/.htaccess: internal redirects using relative paths with .. : Use absolute paths instead so that when the directory is reached through a symlink, the redirect will still work. Note that relative paths without .. do not need to be absolute paths because the subtree structure is the same (just the parent dirs are different).

schemas/VegCore/VegCore.ERD.mwb: Regenerated exports

schemas/VegCore/VegCore.ERD.mwb legend images: resized to medium-sized squares because apparently MySQL Workbench can't handle rectangular or very large images (it distorts them or resets them to their original size when the diagram is reloaded)

schemas/VegCore/VegCore.ERD.mwb: Legend: Added IS-A, HAS-A, and "inherits from record" entries

Added lib/MySQL_Workbench/connector.png, solid_line.png, dotted_line.png

schemas/VegCore/VegCore.ERD.mwb: Fixed lines

schemas/VegCore/VegCore.ERD.mwb: Renamed placename to named_place to match the table name

schemas/VegCore/VegCore.ERD.mwb: Renamed measurement to trait because this is the more commonly used name for the entity

schemas/VegCore/VegCore.ERD.mwb: Added VegCore logo

Added schemas/VegCore/VegCore.logo.svg

schemas/VegCore/VegCore.ERD.mwb: taxon: Added recursive parent fkey for (optionally) storing taxon hierarchies

schemas/VegCore/VegCore.ERD.mwb: Repositioned taxon_determination

schemas/VegCore/VegCore.ERD.mwb: Moved taxon next to qualified_taxon instead of above it, because inheritance (IS-A) is shown vertically while HAS-A is shown horizontally

schemas/VegCore/VegCore.ERD.mwb: Populated legend

schemas/VegCore/VegCore.ERD.mwb: Added exports

schemas/VegCore/VegCore.ERD.mwb: Fixed lines and settings for the Linux MySQL Workbench

schemas/VegCore/VegCore.ERD.mwb: Added table colors

Removed backup file schemas/VegCore/VegCore.ERD.mwb.bak

Added schemas/VegCore/VegCore.ERD.mwb, VegCore.my.sql with first VegCore ERD and MySQL schema. All tables are in the ERD, but contain only pkey and fkey columns.

lib/sql.py: mk_select(): using subset function: Turn off enable_sort (within the transaction) to avoid unwanted slow sorts. This change (along with the subset functions themselves) should significantly reduce the long FIA.occurrence_all table subset time (~8 hours altogether) and with it the total import time, which had more than doubled as a result of the FIA refresh. Note that this issue would have been even more pronounced for larger datasets, such as the GBIF refresh, which would have taken ~2.5 days longer (400 million rows * ~30% are plants * (FIA: ~8 hours/16.7 million rows) * 1 day/24 hours).

lib/sql.py: mk_select(): Use subset function when it's available for fast querying at large OFFSET values

lib/sql.py: Added has_subset_func()

inputs/FIA/occurrence_all/import: Run mk_subset_by_row_num_func() to make the subset functions available for fast querying at large OFFSET values

schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where need to add 1 to the 0-based offset_ to get the 1-based row_num (which is usually a serial column)

schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where need to subtract 1 from the end row_num because BETWEEN limits are inclusive of the bounds

schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where also need to COALESCE offset_ to 0 when it's added to the limit_

schemas/util.sql: mk_subset_by_row_num_func(): subset function which turns off enable_sort: Fixed bug where need to pass ($2, $3) instead of ($1, $2) to the regular subset function

inputs/FIA/occurrence_all/import: Added occurrence_all-row_num column for use with mk_subset_by_row_num_func()

schemas/util.sql: mk_subset_by_row_num_func(): Also create subset function which turns off enable_sort. This is used for limit values greater than ~100,000 to avoid unwanted slow sorts. The regular subset function is still needed to work with EXPLAIN, so that it produces expanded output instead of just a function scan.

schemas/util.sql: Added mk_subset_by_row_num_func()

schemas/util.sql: Added type_qual_name()

schemas/util.sql: force_update_view(): Fixed bug where also need to drop view for "cannot change name of view column" errors

inputs/FIA/occurrence_all/import: Use new force_update_view(), which only drops the view if its columns have changed and otherwise just uses CREATE OR REPLACE VIEW, rather than always first running DROP VIEW IF EXISTS

schemas/util.sql: Added force_update_view()

bin/make_analytical_db: Commented out export_analytical_db because we are not yet using the analytical DB in MySQL, and it doesn't make sense to generate a large, unused CSV export each time

bin/export_analytical_db: Replaced analytical_aggregate with analytical_stem

inputs/FIA/occurrence_all/: Updated header.csv for new column order

inputs/FIA/occurrence_all/import: Use directional joins (LEFT/RIGHT JOIN) instead of inner joins to ensure that the PostgreSQL query planner always joins starting with the TREE table. Note that the directional joins are now needed for a different reason than when they were initially added, which had been to avoid slow sorts. The sorts (at least for LIMIT-only queries) went away when small tables such as COUNTY and REF_UNIT were added to the joins.

inputs/FIA/*/map.csv: Changed newlines between table and field name to - because the newlines mess up the flow of queries and also break pgAdmin's display of EXPLAIN output. The - was chosen because it's a non-whitespace character that linewraps in browsers, phpPgAdmin, and Google spreadsheets (although unfortunately not in pgAdmin). It is better than space because you can set a text editor to treat it as a word character, allowing the entire column name (<table>-<field>) to be selected by double-clicking it.

Added planning/workflow/normalized_vs_denormalized/denormalized.generic_standardizations.png (a slide from Brad's bien3_architecture_denormalized.pptx PowerPoint), which shows the staging table preprocessing particularly well

README.TXT: Full database import: record the import times in inputs/import.stats.xls: Added instructions for what to do if the rightmost imports start getting truncated due to the 255-column limit in spreadsheets. (This will occur in 8 imports.)

inputs/import.stats.xls: Removed the previous imports from the current tab because they are also in the 2012-6~9 tab, and should not be in two places

inputs/import.stats.xls: Updated import times. MO and FIA have been refreshed.

Removed no longer needed inputs/GBIF/import. Use ./run instead.

Removed no longer needed inputs/GBIF/_MySQL/import. Use ./run instead.

inputs/GBIF/_MySQL/run: import: Run make directly instead of via ./import

inputs/GBIF/_MySQL/run: Use new import.run, which defines all()

Added planning/workflow/normalized_vs_denormalized/bien3_architecture_(de)normalized.pptx

Added planning/workflow/normalized_vs_denormalized/BIEN-modArch-Dec2010 NS-SBD 1.4.ppt.url

planning/workflow/: Moved normalized vs. denormalized files to separate normalized_vs_denormalized/ subfolder

Regenerated inputs/ACAD/Specimen/logs/steps.by_col.log.sql

inputs/GBIF/raw_occurrence_record/run: Override MySQL_export() so $filter can be customized

inputs/GBIF/table.run: import(): Updated for lib/table.run template changes

lib/table.run: template: import(): Also pass "$@" to superclass method

lib/table.run: template: Use "$FUNCNAME" instead of hardcoding import

Added inputs/GBIF/MySQL_export, used by ./table.run

lib/util.run: echo_func: Fixed bug where need to use BASH_LINENO⁰ for the line #s to match up with the files. For some reason the required array indexes for BASH_SOURCE (1) and BASH_LINENO (0) differ by one.

inputs/GBIF/run: Use new import.run, which defines all()

lib/table.run: Use new import.run, which defines all()

Added lib/import.run

lib/util.run: echo_func: Include the line # of the function to make it easier to find where the code being run is

lib/table.run: Added all (default target)

lib/util.run: run_cmd: If bash exited with an error, don't run the "$@" command. This test is necessary because `trap run_cmd EXIT` will run run_cmd as the result of any exit from the shell, including an error.

*run: Use -e option to bash on the #! line instead of separate `set -o errexit` line so that there is no issue with the `set -o errexit` line getting separated from the #! line (errexit is required for the scripts to work properly)

lib/util.run: run_cmd: When no command specified, default to running the `all` command, just like make

lib/util.run: Run run_cmd at shell exit (using trap) instead of requiring every runscript to have `run_cmd ` at the end of it

Added inputs/GBIF/run

Added inputs/GBIF/raw_occurrence_record/run

Added inputs/GBIF/table.run