Tilda Database functions: Schema DDL Management

<-- Common Helper Database Functions

Source code in PostgreSQL.helpers-start.sql

Schema/DDL Management

One of Tilda's strong feature is the Migrate utility. It manages a variety of schema changes and to help do that, we have created a number of database functions. Many dynamic Schema/DDL operations require accessing the catalog (either standard or the proprietary Postgres version). It's certainly not hard to do, but still requires some knowledge to figure out the exact queries and in some cases, can become quite complex. For example, many such DDL operations would require dynamic SQL, and as such, guarding against injection attacks is critical.

Table/Column Tests

We have 2 simple functions to test if a table or column exists. This can be used to prepare a piece of DDL-related logic and return decent errors to the developer, or for SQL-injection guarding.

select TILDA.existsTable('TILDA', 'Key'); -- true
select TILDA.existsColumn('TILDA', 'Key', 'refnum'); -- true

🎈 NOTE: The functions assume that schema/table/column names are case-insensitive.

View dependency management

Some databases do not allow some view modifications without dropping the view in question first. In PostgreSQL for example, one can update the where-clause of a view, or column expressions as long as the resulting type is not changed, and simply replace the view. But adding or removing a column, or modifying a column's type is not allowed and requires the view be dropped first. Of course, if that view is used by another view somewhere, then that needs to be dropped too etc... It's all cascade.

Essentially, many upgrade scenarios require view or table dependencies to be managed properly. Tilda provides a helper function that returns the list of ALL dependencies for a table of a view, as well as their full DDL so that dependencies can be dropped and then re-created.

select * from Tilda.getDependenciesDDLs('some_schema','some_table');

This function returns the following columns:

• srcSchemaName: the source schema name, i.e., the first parameter.
• srcTVName: the source table/view name, i.e., the second parameter.
• seq: a sequence number for the dependencies in order of reference
• depSchemaName: the dependency schema name.
• depViewName: the dependency table/view name.
• restoreScript: the DDL script to restore the view.
• created: the timestamp this record was created
• lastUpdated: the timestamp this record was created
• deleted: the timestamp this record was created

🎈 NOTE: The life-cycle timestamps "created", "lastUpdated" and "deleted" are used to conform to Tilda's general pattern of life-cycle management and can be ignored if not needed. The field "deleted" will always be null, and "created" and "lastUpdated" will always be set to the current timestamp.

That data can be used to drop dependent views (order by seq asc), and restore the views back (order by seq desc). The restore scripts include the following:

• The "create view" statement, likely as it was originally specified.
• Access control (grant on PostgreSQL)
• View comment
• Columns comments

For example:

select "restoreScript"
  from Tilda.getDependenciesDDLs('some_schema','some_table')
 order by seq desc;

select 'drop view '||"depSchemaName"||'.'||"depViewName"||';'
  from Tilda.getDependenciesDDLs('some_schema','some_table')
 order by seq asc;

🎈 NOTE: Make sure you gather the restore scripts first before you run the drop sequence. You can either go low-tech and copy the results from the restore script query and paste that into an editor of your choice for later access, or save the results in a temp table for example. In the end, you

• get the restore scripts,
• run the drops,
• run your alter operations you need,
• and then run the restore scripts.

🎈 NOTE: It is very important to understand that there are scenarios where the restore scripts may fail. For example, imagine renaming or dropping a column in the source table and one of the views refer to that column that now no longer exists: the restore script will fail. For that reason, it is VERY important that you run your drop/alter/restore scripts in a single transaction (i.e., altogether at once).

Column Management

In order to support flexible migration scenarios, it's often useful to be able to rename columns, or migrate some contents from one column to another. One could think that the standard ALTER TABLE ... ALTER COLUMN facilities would be enough, but we need more flexibility and better error handling. We have 2 functions:

• tilda.renameColumnIfExists: Renames a column and properly handles cases where the table doesn't exist, source column doesn't exist, or dest column already exists. Furthermore, the function handles a list of possible source names. For example, V2 of a schema renames column a to b, and then V3 renames b to c. This function can then handle a case of renaming either a or b to c. This can be useful when migrating different version levels of a schema. A check will be performed to make sure there is only one valid source column. Return values:
  • 1: the operation was completed successfully and the column was renamed
  • 0: none of the source columns exist and the destination column already exists. We assume it was previously renamed.
  • -1: the table couldn't be found
  • -2: there were multiple matches in the table for the source column list. Only one match is expected.
  • -3: none of the source columns can be found.
  • -4: the destination column already exists.
• tilda.copyColumnAndDrop: Copies the contents of srcColumnName to destColumnName and if successful, drops srcColumnName. This is useful when for example some migration has already occurred to create the destination column and a copy/drop is needed vs the previous renameColumnIfExists() function which just renames a column. Return values:
  • 1: the operation was completed successfully and the column was renamed
  • 0: the source column cannot be found and the dest column already exists. We assume a previous operation was already performed successfully.
  • -1: the table couldn't be found
  • -3: source columns can be found.
  • -4: the destination column cannot be found.

select * from Tilda.renameColumnIfExists('TILDA', 'Key', ARRAY['created1', 'created2'], 'created');
-- (0,"Destination column TILDA.Key.created already exists. Maybe it has been renamed already?")

select * from Tilda.renameColumnIfExists('DUMB_SCHEMA', 'DumbTable', ARRAY['x1', 'x2', 'x3'], 'x4');
-- (-1,"Table DUMB_SCHEMA.DumbTable cannot be found.")

select * from Tilda.renameColumnIfExists('TILDA', 'Key', ARRAY['created', 'lastUpdated'], 'toto');
-- (-2,"Multiple potential source columns TILDA.Key.{created,lastUpdated} exist. There should be only one match.")

select * from Tilda.renameColumnIfExists('TILDA', 'Key', ARRAY['x1', 'x2', 'x3'], 'x4');
-- (-3,"Source column(s) TILDA.Key.{x1,x2,x3} cannot be found.")

select * from Tilda.renameColumnIfExists('TILDA', 'Key', ARRAY['created', 'created2'], 'deleted');
-- (-4,"Destination column TILDA.Key.deleted already exists.")

select * from Tilda.copyColumnAndDrop('TILDA', 'Key', 'created1', 'deleted');
-- (0,"Source column deleted does not exist and destination column deleted exists. Maybe it has been copied and dropped already?")

select * from Tilda.copyColumnAndDrop('DUMB_SCHEMA', 'DumbTable', 'x', 'x2');
-- (-1,"Table DUMB_SCHEMA.DumbTable cannot be found.")

select * from Tilda.copyColumnAndDrop('TILDA', 'Key', 'createdxxx', 'toto');
-- (-3,"Source column(s) TILDA.Key.createdxxx cannot be found.")

select * from Tilda.copyColumnAndDrop('TILDA', 'Key', 'lastUpdated', 'deletedxxx');
-- (-4,"Destination column TILDA.Key.deletedxxx does not exist.")

Table row-based comparison

Often, after doing some complex migration, changing business logic or data models, you need t0 be able to compare 2 tables and make sure they are identical. For example, you have a complex view that you have changed in non-trivial ways. You would dump the original view into a temp table and then compare with results from the new view.

The function returns the count of rows that are different (so 0 means they are the same). The parameters are:

• table1: the full name of the first table or view
• identity1: an array of the columns to join on for identity from the first table
• columns1: the columns to test values from
• table2: the full name of the second table or view
• identity2: an array of the columns to join on for identity from the second table
• columns2: the columns to test values from

Logically, the function creates a json representation of all the columns requested for table1 and table2, and does an outer join on the identities specified. It then returns the count of records where the outer join generates a NULL set of values for the first or second table, or the json text representation of the specified columns and values don't match.

select tilda.CompareTables('some_schema.some_table',ARRAY['pk'], ARRAY['col1','col2','col3']
                          ,'some_schema.some_view' ,ARRAY['pk'], ARRAY['cola','colb','colc']
                          );