Elixir to Declarative database schema migration guide¶
This guide is meant for those who opt to change older Elixir-based model classes that they wrote themselves to use SqlALchemy Declarative instead. If you go down this route, and you need to upgrade a database, then you may need some Migrations for your own code.
This guide explains what changed in the database, and how to migrate to cater for these changes.
Summary of changes¶
Any object decorated with @session_scoped has a column (used as a foreign key to the session). In the Elixir implementation, this column was named ‘session_id’. The name of this column was changed to ‘user_session_id’ for clarity: the word ‘session’ is used for SqlAlchemy’s Session as well as for Reahl UserSession. This name makes explicit which ‘session’ it refers to.
For any @session_scoped classes, this column needs to be renamed, and its associated foreign key constraint should be updated.
The new (0.9) SqlAlchemy provides a mechanism for dictating the naming convention used to name database objects (constraints, indexes, etc). Using this means the same conventions will be used across different database backends – something that will make migrations easier in future. Reahl 3.0 now uses these conventions, and they differ from names chosen automatically by PostgreSQL in the past.
This means that all of the following need to be dropped, and recreated with their new names: primary keys, foreign keys and indexes.
All our Elixir classes used joined table inheritance (where they did indeed inherit). Perhaps yours do too... In this case, Elixir automatically created a column on the table of a child class to refer to the table of the parent class. This column is a primary key but is also a foreign key to the parent. The column is used for joins to implement polymorphism.
Elixir used to include the name of the parent in this column name, ie: party_id. In your own code, you can stick to that convention to ease the migration. We opted to clean house and rename all these just to ‘id’.
As mentioned, previously, a Party always had a SystemAccount. It makes more sense to be able to have Party objects with or without SystemAccounts, hence the relationship was changed. Going forward, a Party does not have any knowledge of a SystemAccount, but a SystemAccount has an ‘owner’, which is a Party.
The MigrateElixirToDeclarative class¶
Reahl 3.0 includes the class reahl.sqlalchemysupport.elixirmigration.MigrateElixirToDeclarative. You can inherit from this class to create your own Migrations relating to this move from Elixir to Declarative. It includes a number of handy methods you can call:
MigrateElixirToDeclarative¶
- class reahl.sqlalchemysupport.elixirmigration.MigrateElixirToDeclarative(changes)¶
Inherit your own Migrations from this class in order to get access to a number of methods that help with migrating common changes between Elixir and Declarative
- rename_primary_key_constraints()¶
This method will be called to schedule changes to renamed primary keys. Override it if you have any in your component.
- rename_foreign_keys_constraints()¶
This method will be called to schedule changes to renamed foreign keys. Override it if you have any in your component.
- change_inheriting_table_ids()¶
This method will be called to schedule changes to the id columns of tables that inherit. Override it if you have any in your component.
- change_session_scoped_classes()¶
This method will be called to schedule changes to classes that are @session_scoped. Override it if you have any in your component.
- rename_pk_column(table_name, old_name, new_name, primary_key_columns)¶
Schedule changes necessary to rename a primary key column itself. It recreates the primary key, hence needs all info for doing so.
Parameters: - table_name – The name of the table to which the primary key belongs.
- old_name – The old name of the primary key column.
- new_name – The new name of the primary key column.
- primary_key_columns – A list of strings (unicode in Py2, str in Py3) containing the names of the columns that should be included in the primary key.
- rename_pk(table_name, primary_key_columns, old_table_name=None)¶
Schedule changes necessary to rename a primary key (not the column) according to new naming conventions. It recreates the primary key, hence needs all info for doing so.
Parameters: - table_name – The name of the table to which the primary key belongs.
- primary_key_columns – A list of strings (unicode in Py2, str in Py3) containing the names of the columns that should be included in the primary key.
- old_table_name – Specify old_table_name if the table is also renamed during this migration (even if for other reasons).
- change_session_scoped(table_name)¶
Rename the old session_id relationship on @session_scoped things to user_session_id, and update the foreign keys and indexes accordingly.
Parameters: table_name – The name of the table underlying the @session_scoped class.
- change_inheriting_table(table_name, old_id_column_name, parent_table_name)¶
Tables of classes that inherit from other classes (using joint table inheritance) named their primary key columns xxx_id (assuming the parent is called xxx here). These were also foreign keys to the primary key of the parent table. In our Declarative implementation we just always use the name id for a primary key regardless of the situation.
This method renames such primary key columns, and deal with the knock-on effect of this change to related primary and foreign key as well.
Parameters: - table_name – The name of the table underlying the child/inheriting class.
- old_id_column_name – The old name of the primary key column of the child/inheriting class.
- parent_table_name – The name of the table underlying the parent class.
- recreate_foreign_key_constraint(old_fk_name, table_name, column_name, other_table_name, other_column_name, **create_kwargs)¶
The names of foreign key constraints change according to naming convention. This method affects such a name change, but in order to do it, it needs all details necessary to recreate the foreign key constraint.
Parameters: - old_fk_name – The previous name of the foreign key.
- table_name – The name of the table from which the foreign key points.
- column_name – The name of the column in which the foreign key pointer is stored.
- other_table_name – The name of the table to which the foreign key points.
- other_column_name – The name of the column to which the foreign key points.
- create_kwargs – Additional keyword arguments to be passes to alembic’s op.create_foreign_key