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

Added inputs/GBIF/_MySQL/run

lib/util.run: fwd: Check that $subdirs is defined. Added $subdirs to usage.

lib/util.run: fwd: Added usage

lib/table.run: Switched from echo_run to echo_func

lib/util.run: run_cmd: Echo the command being run, including the top-level run script. This is in addition to the echoing of the command in the function itself (using echo_func), which provides both the runscript that was run and the file where the invoked command was actually located (which may be different due to includes).

lib/util.run: Echo the command at the beginning of each function using new echo_func, instead of having to type echo_run before every call to a function. Note that because echo_func uses BASH_SOURCE, the path to the file containing the function will be included in the debug message, which greatly facilitates locating which file a command is in.

lib/util.run: Added echo_func

lib/util.run: Added echo_cmd and use it in echo_run

lib/util.run: echo_cmd(): Renamed to echo_run for clarity, because it also runs the command

lib/util.run: Added inline_make()

lib/util.run: Added echo_stdin()

bin/my2pg_export: Put --password first because it's an authentication-related option

Added lib/table.run, which includes the commands in import.sh but uses run scripts to allow running commands other than just import. (For example, map_table or postprocess can be run separately. Uninstall-related commands which would not belong in an import script can also be added, because import is only one of many commands a run script can offer.)

Added lib/util.run with general functions and template for run scripts (a bash-based replacement for make). Unlike make, run scripts support full bash functionality including multiline commands. The run script template also includes syntax for various kinds of relative includes in bash.

lib/common.Makefile: Added $(require_var)

bin/publish_analytical_db: Fixed bug where need to remove `ESCAPED BY '"'` because this would causing " followed by an escape sequence char to be interpreted specially (e.g. "n -> \n). MySQL automatically takes care of quote doubling when you specify `FIELDS OPTIONALLY ENCLOSED BY`.

lib/common.Makefile: Compression: Added `%:: .gz`, `.gz: %`