bugfix: bin/make_backups: need to `popd` when done

bugfix: bin/make_backups: need to `set +x` when done

bin/make_backups: run with initial "." so background processes will be owned by the invoking shell

added bin/make_backups

web/links/index.htm: updated to Firefox bookmarks: VirtualBox: performance: added that context-switching also imposes an overhead, in addition to sandboxing.

web/links/index.htm: updated to Firefox bookmarks: BIEN: added list of machines this has been tested on. VirtualBox: added links to performance considerations of running in a VM.

inputs/.TNRS/schema.sql: added new derived columns to derived views

fix: schemas/util.sql: view_def_to_orig(): need to support aliased columns (produced when a column is renamed)

schemas/util.sql: added aliased_col_re()

schemas/util.sql: added alias_re()

fix: schemas/util.sql: view_is_subset(): renamed to view_is_subset_or_renaming() because this also supports views that just rename columns, which should not be .*-ed by view_def_to_orig()

fix: schemas/util.sql: view_def_to_orig(): can't use FROM to alias util.col_re() because that prevents inlining the function

fix: schemas/util.sql: view_def_to_orig(): can't use FROM to alias util.col_re() because that prevents inlining the function

schemas/util.sql: view_def_to_orig(): use util.col_re() for clarity

schemas/util.sql: added col_re()

fix: schemas/util.sql: derived_col_update(): also need steps to drop column, because DROP __ CASCADE doesn't work when there are dependent views

inputs/.TNRS/schema.sql: _accepted_infraspecific{rank,epithet}: use array slice of new _accepted{genus,specific_epithet,infra_{rank,epithet}}, which is simpler than using remove_prefix() in __accepted_infraspecific_label

inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": don't need to use *Accepted_name anymore because _accepted{genus,specific_epithet,infra_{rank,epithet}} is now generated from *Accepted_name

inputs/.TNRS/schema.sql: taxon_match."__accepted_{genus,specific_epithet}": renamed to "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" since this now includes these other ranks as well

bugfix: inputs/.TNRS/schema.sql: taxon_match."__accepted_{genus,specific_epithet}": use "*Accepted_name" instead of "Accepted_species[_binomial]__@TNRS__@vegpath.org" (from "*Accepted_name_species") because Accepted_name_species apparently sometimes does not match the Accepted_name and uses malformed Unicode characters

backups/README.TXT: added backup retention policy

bugfix: schemas/util.sql: derived_cols_export(): also need to first run util.derived_cols_remove() if changing the derived column order

schemas/util.sql: added derived_cols_remove(), which allows derived columns to be re-created in a different order

schemas/util.sql: added drop_derived_col(), which ensures that drop_column() only cascades to views

fix: schemas/util.sql: drop_column(): also need to recreate dependent views

schemas/util.sql: added drop_constraint()

inputs/.TNRS/schema.sql: taxon_match: `inputs/.TNRS/data.sql.run refresh`: documented runtime (1 min)

bugfix: inputs/.TNRS/schema.sql: taxon_match: use "Accepted_species[_binomial]__@TNRS__@vegpath.org" instead of "*Accepted_name_species". this fixes a bug in __accepted_infraspecific_label where Accepted_name_species with trailing whitespace could not be prefix-removed from names that contained just a species binomial.

fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_species[_binomial]__@TNRS__@vegpath.org", which removes trailing whitespace

inputs/.TNRS/schema.sql: added steps to remove a column and to add a non-derived column

inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns at the end: merged into "to add a new derived column"

inputs/.TNRS/schema.sql: to add columns in the middle: renamed to "to move a column to the middle" for clarity

inputs/.TNRS/schema.sql: to populate a new column: updated to use util.derived_col_update()

fix: inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns: also need to run util.recreate_view()

inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns at the end: don't need to run `rm=1 inputs/.TNRS/data.sql.run` because this is now run by `make schemas/remake`

inputs/.TNRS/schema.sql: added new derived columns to derived views

schemas/util.sql: remove_prefix(), remove_suffix(): support case-insensitive matching

bugfix: inputs/.TNRS/schema.sql: taxon_match.__accepted_infraspecific_label: need to use case-insensitive matching of the removed prefix because TNRS lowercases part of the Accepted_name

schemas/util.sql: remove_prefix(), remove_suffix(): support case-insensitive matching

schemas/util.sql: remove_prefix(), remove_suffix(): support case-insensitive matching

schemas/util.sql: added case-sensitive-optional versions of starts_with(), ends_with()

schemas/util.sql: added canon_case(str text, case_sensitive boolean)

schemas/util.sql: check_constraint_disable(): documented that we can't use ADD CONSTRAINT NOT VALID for this because it does not disable the constraint completely, but instead causes new rows to be validated against it. the ADD CONSTRAINT NOT VALID behavior does not work for the derived columns use case of this function.

schemas/public_.sql: *_view_modify(): added repopulate param, which is passed to rematerialize_view()

schemas/util.sql: rematerialize_view(): support updating just the table's schema, without populating it

schemas/util.sql: rematerialize_view(): don't need extra eval() around table_mod_sql because recreate() performs eval()

lib/sh/sync.sh: db_snapshot(): before backing up, trim bloated temp files (eg. from rolled back changes)

bugfix: inputs/.TNRS/schema.sql: taxon_match: use wrapper for util.remove_prefix() so CHECK constraints that use it don't get dropped when the util schema is reinstalled

schemas/public_.sql: *_view_modify(): use util.rematerialize_view(), which now supports table mods

schemas/util.sql: rematerialize_view(): added support for table mods to run after creating table

bugfix: schemas/util.sql: rematerialize_view(): don't populate unless recreate() can successfully recreate the dependent views, to avoid populating the entire table (which for some views can take hours) only to have the changes rolled back by a failing dependent view

schemas/util.sql: removed no longer used materialize_view(). use util.copy() instead.

schemas/util.sql: rematerialize_view(): call util.copy() directly instead of via util.materialize_view()

schemas/util.sql: removed no longer used copy_types_and_data(). use util.materialize_view() instead.

schemas/util.sql: to_freq(): use util.copy() instead of util.copy_types_and_data() to avoid a dependency on util.copy_types_and_data()

fix: schemas/util.sql: copy_data(): also need to ANALYZE table after loading data into it

schemas/public_.sql: analytical_stem_view: use viewFullOccurrence_individual_view to avoid duplicated code between the views. this moves the stem-related columns to the end of the view.

schemas/public_.sql: added viewFullOccurrence_individual_view

web/username_prefix.php: substitutions: also support "" because Postgres doubles " to escape them

web/username_prefix.php: added support for substitutions ( x."_y@url -> x.x_y@url ), which can be used when a table name is repeated in a column name

backups/TNRS.backup.md5: updated

schemas/util.sql: derived_col_constraint_initially_enabled(): clarified that this is always based on whether the existing constraint is enabled

inputs/.TNRS/schema.sql: taxon_match: COMMENT: added steps to port derived column changes to vegbiendev

schemas/util.sql: added derived_cols_export(), which creates SQL to port the derived cols to another DB

bugfix: inputs/.TNRS/schema.sql: taxon_match: derived columns: use new "matched~Name[_no_author]___@TNRS__@vegpath.org" instead of "*Name_matched" so that "No suitable matches found." is removed before concatenating with other fields

inputs/.TNRS/schema.sql: taxon_match: added derived column "matched~Name[_no_author]___@TNRS__@vegpath.org", which removes the "No suitable matches found." string

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

bugfix: inputs/.TNRS/schema.sql: "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org": need to use "[accepted_]genus__@DwC__@vegpath.org" rather than "*Accepted_name" for this for rank = genus

inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"

bugfix: inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": need to populate this for rank = genus

inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"

bugfix: inputs/.TNRS/schema.sql: derived columns: use "Accepted_family__@TNRS__@vegpath.org" instead of "*Accepted_name_family" because "*Accepted_name_family" is sometimes missing

bugfix: derived_col_constraints_enable(): also need to update col comments, in addition to table__fill_derived()

schemas/util.sql: check_constraint_enabled(): made it IMMUTABLE

bugfix: schemas/util.sql: derived_col_constraints_enable(): need to update table__fill_derived() w/ formatting changes from enabling constraints

bugfix: schemas/util.sql: derived_col_constraints_enable(): need to update table__fill_derived() w/ formatting changes from enabling constraints

fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_family__@TNRS__@vegpath.org", which is needed because "*Accepted_name_family" isn't always populated

bugfix: inputs/.TNRS/schema.sql: taxon_match: to add columns in the middle: also need to run util.derived_cols_repopulate() since the dependency order has changed

fix: schemas/util.sql: derived_cols_populate(): documented that this function will sometimes not repopulate columns when needed, such as after changing the column order. in these cases, it is necessary to run util.derived_cols_repopulate() manually.

schemas/util.sql: derived_cols_populate(): use new derived_cols_repopulate()

schemas/util.sql: added derived_cols_repopulate()

fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: to add columns in the middle: also need to run util.derived_cols_update()

fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: updated util.derived_cols_sync() to util.derived_cols_update()

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

bugfix: schemas/util.sql: derived_cols_trigger_update(): need to clear derived cols first so old values won't be used in calculations

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): put expr on same line as var, to save space

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): removed lines between statements, which add too much space

fix: schemas/util.sql: derived_cols_trigger_update(): don't indent expr because may have multiple lines

bugfix: schemas/util.sql: derived_col_update(): set_comment(): vars can't have same name as params, which will be substituted

inputs/.TNRS/schema.sql: taxon_match: added derived column "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived column "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"

added web/people/Brad_Boyle/morphosp symlink to morphospecies

added derived/TNRS/Morphospecies and scope.eml* from Brad

added web/people/Brad_Boyle/morphospecies redirect to Morphospecies and scope.eml.txt

added web/people/Brad_Boyle/Morphospecies and scope.eml.txt symlink

added derived/TNRS/Morphospecies and scope.eml* from Brad

added web/people/Brad_Boyle/TNRS redirect to Modifications to procedure to scrubbing names using TNRS.docx

added web/people/Brad_Boyle/Modifications to procedure to scrubbing names using TNRS.docx symlink

added derived/TNRS/Modifications to procedure to scrubbing names using TNRS.docx from Brad