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