4.5. Migration generators

Migration generators are a flexible mechanism that allows you to create migrations for your ORM based on the actual difference between the model and the DBMS state.

Like the structure generators, the migration generator is written in Python and takes an xml structure as input to generate code in your programming language for your ORM.

How to add migration generator

Note: Python must be installed to run the generator. You can check if the installation is correct by running python from the project folder.

To add a migration generator, double-click on the database in the project explorer (the top level containing the schemas):

images/generators/select_database.png

A window with the database properties will open, where one of the properties will be the path to the migration generator:

images/generators/add_python_script_to_database.png

You can specify the path to an existing Python script or download an example generator from the site using the button to the right of the file name input field.

Note: the path to the script is specified relative to the project file to avoid conflicts during team work. When running the generator, the working folder is also the project file folder. Save the project if it has just been created.

The button to download the script from the site will open a window for selecting an example of the migration generator:

images/generators/download_migration_generator.png

There can only be one migration script per database. Once the path to the script is set, a new option appears in the database context menu:

images/generators/create_migration_action.png

The synchronization process will then start, as described in the article 3.2. Synchronization. First, you will need to select the objects that will be synchronized at the schema and table level, and then you will be asked to specify the details of which changes will be included in the migration generator.

XML Structure for Database Migrations

This document describes the structure of the XML used to represent database migration changes. The XML document contains a list of changes to be applied to a database schema, such as creating or altering tables, adding or removing columns, and defining constraints like primary keys, indexes, and foreign keys.

Root Element

The root element of the XML document is <Diff>. It holds all the migration changes as its child elements.

<Diff>
  <!-- Migration changes go here -->
</Diff>

Create Schema

The <CreateSchema> element represents the creation of a new database schema. It includes an attribute Name that specifies the name of the schema.

<CreateSchema Name="SchemaName" />

Create Table

The <CreateTable> element defines a new table to be created in the database. It includes the table's name, an optional comment, and several child elements to define columns, primary keys, indexes, unique constraints, and foreign keys.

<CreateTable Name="TableName" Comment="Table description">
  <Column Name="ColumnName" Type="DataType" Nullable="true" AutoIncrement="false" Default="value" Comment="Column description" />
  <PrimaryKey Name="PrimaryKeyName" Columns="Column1, Column2" />
  <Index Columns="Column1, Column2" />
  <Unique Columns="Column1, Column2" />
  <ForeignKey Name="ForeignKeyName" FromColumn="Column1" ToColumn="Column2" ToTable="TargetTable" OnUpdate="CASCADE" OnDelete="NO ACTION" />
</CreateTable>

Attributes:

  • Name: The name of the table to be created.
  • Comment: An optional description or comment for the table.

Child Elements:

  • <Column>: Defines a column in the table, including attributes such as name, data type, nullable status, auto-increment status, default value, and an optional comment.
  • <PrimaryKey>: Defines the primary key for the table, with comma-separated columns specified.
  • <Index>: Defines an index on one or more columns.
  • <Unique>: Defines a unique constraint on one or more columns.
  • <ForeignKey>: Defines a foreign key constraint linking this table to another table.

Drop Table

The <DropTable> element specifies a table that should be dropped (deleted) from the database. It includes a Name attribute that defines the table to be removed.

<DropTable Name="TableName" />

Attributes:

  • Name: The name of the table to be dropped.

Alter Table

The <AlterTable> element defines changes to an existing table. These changes can include renaming the table, altering columns, modifying primary keys, adding or removing indexes, unique constraints, and foreign keys.

<AlterTable Name="TableName" Comment="New comment" RenameTo="NewTableName">
  <DropColumn Name="ColumnName" />
  <ChangeColumn>
    <OldColumn Name="OldColumnName" Type="OldType" />
    <NewColumn Name="NewColumnName" Type="NewType" />
  </ChangeColumn>
  <AddColumn Name="NewColumnName" Type="DataType" Nullable="true" />
  <DropPrimaryKey Name="PrimaryKeyName" />
  <AddPrimaryKey Name="NewPrimaryKey" Columns="Column1, Column2" />
</AlterTable>

Attributes:

  • Name: The name of the table to be altered.
  • Comment: An optional new comment for the table. Empty if removed.
  • RenameTo: The new name of the table (if renaming).

Child Elements:

  • <DropColumn>: Defines a column to be dropped from the table.
  • <ChangeColumn>: Defines a change to an existing column, including both the old and new column definitions.
  • <AddColumn>: Defines a new column to be added to the table.
  • <DropPrimaryKey>: Specifies the removal of the primary key from the table.
  • <AddPrimaryKey>: Specifies the addition of a primary key to the table.

Example XML Document

Here is an example of a complete XML document representing a migration with schema creation, table creation, and table alteration:

<Diff>
  <CreateSchema Name="MySchema" />
  <CreateTable Name="Users" Comment="User table">
    <Column Name="Id" Type="INT(11)" AutoIncrement="true" />
    <Column Name="Username" Type="VARCHAR" Default="''" />
    <PrimaryKey Columns="Id" />
    <ForeignKey Name="FK_Users_Roles" FromColumn="RoleId" ToColumn="Id" ToTable="Roles" OnDelete="CASCADE" />
  </CreateTable>
  <AlterTable Name="Users" RenameTo="UserAccounts">
    <ChangeColumn>
      <OldColumn Name="Username" Type="VARCHAR" />
      <NewColumn Name="Username" Type="TEXT" />
    </ChangeColumn>
    <AddColumn Name="Email" Type="VARCHAR" Nullable="true" />
  </AlterTable>
  <DropTable Name="OldTable" />
</Diff>