Tilda JSON Syntax: Migration

<-- Main Schema Syntax

Migrations

Tilda handles a lot of migration automatically based simply on standard declarations of tables and views. For example, you can modify the type or comment of an existing column, add a column, change an index or foreign key etc... There are some migrations though where the original information "is lost". By that, we mean moving or renaming operations. Sometimes, you need to refactor your database and the "migrations" element defined three possible operations:

 ,"migrations":{
      "renames":[
         { "object":"CodesLOINC"   , "column":"code_class" , "oldNames":["class"]       }
        ,{ "object":"CodesHcpcsCpt"                        , "oldNames":["HCPCS_CODES"] }
       ]
     ,"moves":[
         { "schema":"MORECODES", "objects":["TableA", "TableC", "TableC"]
                               , "views"  :["ViewA","ViewB"]
         }
       ]
     ,"notNulls":[
         { "object":"AlertInstance", "columns":["signature"    ], "default":"${note}"  }
        ,{ "object":"AlertInstance", "columns":["creatorRefnum"], "default":"-666"     }
        ,{ "object":"AlertInstance", "columns":["creatorId"    ], "default":"SYSTEM"   }
       ]

   }

• Renaming a column
• Renaming a tale or view
• Moving tables and views from one schema into another
• Setting a default value for new not-null columns addition or transition from null to not-null

🎈 NOTE: For renaming operations, we allow providing multiple "old" names. This is especially useful during development where a table's design might go through several iterations (the 'I' in Tilda). In such cases, it's possible for a table to start with a column "col1" that then gets renamed to "col2" which later gets renamed again to "col3". Because Tilda migration's design allows "any-point to current" migration, that history of old names becomes important to make sure that all is migrated properly.

🎈 NOTE: We had conversations internally as to whether migration definitions should be integrated directly in the markup specifically for each table/view/column. Eventually, we decided to group all those operations into a separate element to (1) avoid adding more distracting elements to the original definitions, and (2) because we felt that migration was a holistic thing at the schema level and we wanted to keep those operations together.

🎈 NOTE: for not-nulls, you can provide a migration that reuses existing column, with the ${col} notation. This is useful when creating a new column that should be not-null, while not wanting to specify a default value. With the migration step, we provide a one-time default value for migration only.