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()

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

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

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"

schemas/public_.sql: analytical_stem: synced to analytical_stem_view

bugfix: schemas/util.sql: view_body(): need to match view defs without trailing \n

bugfix: schemas/util.sql: view_body(): need to support both CREATE statements and view bodies

bugfix: schemas/util.sql: raise(): use eval_raw() so that functions called by eval() (such as view_def_to_orig()) can use raise() for debugging

schemas/util.sql: eval(): use new eval_raw()

schemas/util.sql: added eval_raw()

bugfix: schemas/util.sql: view_is_automatically_updatable(), view_is_subset(): use util.view_body() so not matching COMMENT statements, etc

schemas/util.sql: added view_body()

bugfix: schemas/util.sql: view_is_subset(): need to support "(x)" in col name

schemas/util.sql: added copy_struct(from_ regtype, to_ text) and copy_data(from_ regtype, to_ text), and use these in a general-purpose copy()

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

fix: schemas/util.sql: derived_col_update(): column comment: formula: put on own line to support multiline exprs

bugfix: schemas/util.sql: derived_col_update(): column comment: need to use expr from DB, not as provided by user, to avoid this changing when derived_col_update() is rerun

inputs/.TNRS/schema.sql: taxon_match: added derived columns scrubbed_*

bugfix: schemas/util.sql: derived_col_expr_from_check_constraint(): need to handle strs with \n after IS DISTINCT FROM

inputs/.TNRS/schema.sql: taxon_match: put matched-taxon derived columns before accepted-taxon derived columns

inputs/.TNRS/schema.sql: taxon_match: added derived column "[matched_]species[_binomial]~(Accepted_)__@TNRS__@vegpath.org"

inputs/.TNRS/schema.sql: _matched_has_accepted: renamed to matched_has_accepted because the leading _ did not create vertical alignment anyway, due to different quoting syntaxes

schemas/util.sql: added derived_col_name_from_check_constraint()

schemas/util.sql: derived_col_update(): add steps to rename column

bugfix: schemas/util.sql: derived_col_update(): don't set_comment() until CHECK constraint exists so that we can use its canon-ed formula

bugfix: schemas/util.sql: check_constraint_expr_enable(): don't remove suffix without also removing prefix. this is done by using extract_by_regexp() instead. also changed check_constraint_enabled() to use regexps to match this.

bugfix: schemas/util.sql: extract_by_regexp(): needs surrounding COALESCE in case no match

schemas/util.sql: added extract_by_regexp()

inputs/.TNRS/schema.sql: taxon_match: added derived column _matched_has_accepted

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

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived column "__accepted_infraspecific_{rank,epithet}"

inputs/.TNRS/schema.sql: taxon_match: added derived column __accepted_infraspecific_label

bugfix: schemas/util.sql: canon_sql(): need to use the right search_path

bugfix: schemas/util.sql: check_constraint view: def: must use our wrapper so that the right search_path is used

schemas/util.sql: added pg_get_expr() wrapper, which sets search_path to pg_temp

bugfix: schemas/util.sql: check_constraint_expr_*(): updated strings for new Mac Postgres update, which was installed on reboot

bugfix: schemas/util.sql: check_constraint_expr_*(): updated strings for new Mac Postgres update, which was installed on reboot

bugfix: schemas/util.sql: derived_col_update(): steps to modify: also need to run util.derived_cols_populate()

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org"

bugfix: schemas/util.sql: check_constraint view: can't use consrc because this doesn't get auto-updated with column renames

bugfix: schemas/util.sql: derived_col_constraints_enable(): need final `SELECT NULL::void` so don't fold away functions called in previous query

inputs/.TNRS/schema.sql: taxon_match: ran derived_cols_update()