Open Event Server: Working with Migration Files

FOSSASIA‘s Open Event Server uses alembic migration files to handle all database operations and updations.  From creating tables to updating tables and database, all works with help of the migration files.
However, many a times we tend to miss out that automatically generated migration files mainly drops and adds columns rather than just changing them. One example of this would be:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('session', sa.Column('submission_date', sa.DateTime(), nullable=True))
    op.drop_column('session', 'date_of_submission')

Here, the idea was to change the has_session_speakers(string) to is_session_speakers_enabled (boolean), which resulted in the whole dropping of the column and creation of a new boolean column. We realize that, on doing so we have the whole data under  has_session_speakers lost.

How to solve that? Here are two ways we can follow up:

  • op.alter_column:
    ———————————-

When update is as simple as changing the column names, then we can use this. As discussed above, usually if we migrate directly after changing a column in our model, then the automatic migration created would drop the old column and create a new column with the changes. But on doing this in the production will cause huge loss of data which we don’t want. Suppose we want to just change the name of the column of start_time to starts_at. We don’t want the entire column to be dropped. So an alternative to this is using op.alter_column. The two main necessary parameters of the op.alter_column is the table name and the column which you are willing to alter. The other parameters include the new changes. Some of the commonly used parameters are:

  1. nullable Optional: specify True or False to alter the column’s nullability.
  2. new_column_name – Optional; specify a string name here to indicate the new name within a column rename operation.
  3. type_Optional: a TypeEngine type object to specify a change to the column’s type. For SQLAlchemy types that also indicate a constraint (i.e. Boolean, Enum), the constraint is also generated.
  4. autoincrement –  Optional: set the AUTO_INCREMENT flag of the column; currently understood by the MySQL dialect.
  5. existing_typeOptional: a TypeEngine type object to specify the previous type. This is required for all column alter operations that don’t otherwise specify a new type, as well as for when nullability is being changed on a column.

    So, for example, if you want to change a column name from “start_time” to “starts_at” in events table you would write:
    op.alter_column(‘events’, ‘start_time’, new_column_name=’starts_at’)
def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('sessions_version', 'end_time', new_column_name='ends_at')
    op.alter_column('sessions_version', 'start_time', new_column_name='starts_at')
    op.alter_column('events_version', 'end_time', new_column_name='ends_at')
    op.alter_column('events_version', 'start_time', new_column_name='starts_at')


Here,
session_version and events_version are the tables name altering columns start_time to starts_at and end_time to ends_at with the op_alter_column parameter new_column_name.

  • op.execute:
    ——————–

Now with alter_column, most of the alteration in the column name or constraints or types is achievable. But there can be a separate scenario for changing the column properties. Suppose I change a table with column “aspect_ratio” which was a string column and had values “on” and “off” and want to convert the type to Boolean True/False. Just changing the column type using alte_column() function won’t work since we need to also modify the whole data. So, sometimes we need to execute raw SQL commands. To do that, we can use the op.execute() function.
The way it is done:

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute("ALTER TABLE image_sizes ALTER full_aspect TYPE boolean USING CASE 
            full_aspect WHEN 'on' THEN TRUE ELSE FALSE END", execution_options=None)

    op.execute("ALTER TABLE image_sizes ALTER icon_aspect TYPE boolean USING CASE 
            icon_aspect WHEN 'on' THEN TRUE ELSE FALSE END", execution_options=None)

    op.execute("ALTER TABLE image_sizes ALTER thumbnail_aspect TYPE boolean USING CASE 
            thumbnail_aspect WHEN 'on' THEN TRUE ELSE FALSE END"execution_options=None)

For a little more advanced use of op.execute() command will be:

op.alter_column('events', 'type', new_column_name='event_type_id')
    op.alter_column('events_version', 'type', new_column_name='event_type_id')
    op.execute('INSERT INTO event_types(name, slug) SELECT DISTINCT event_type_id, 
                lower(replace(regexp_replace(event_type_id, \'& |,\', \'\', \'g\'),
                \' \', \'-\')) FROM events where not exists (SELECT 1 FROM event_types 
                where event_types.name=events.event_type_id) and event_type_id is not
                null;')
    op.execute('UPDATE events SET event_type_id = (SELECT id FROM event_types WHERE 
                event_types.name=events.event_type_id)')
    op.execute('ALTER TABLE events ALTER COLUMN event_type_id TYPE integer USING 
                event_type_id::integer')

In this example:

  • op.alter_column() renames the column type to event_type_id of events table
  • op.execute() does the following:
  • Inserts into column name of event_types table the value of event_type_idN (which previously contained the name of the event_type) from events table, and
  • Inserts into slug column of event_types table the value of event_type_id where all letters are changed to lowercase; “& ” and “,” to “”; and spaces to “-”.
    1. Checks whether a type with that name already exists so as to disallow any duplicate entries in the event_types table.
    2. Checks whether the event_type_id is null because name of event_types table cannot be null.

You can learn more on Alembic migrations here: http://alembic.zzzcomputing.com/en/latest/ops.html

Implementing Database Migrations

Database Migrations Using Phinx

Database migrations can transform your database in many ways such as creating new tables, inserting rows, adding indexes and modifying columns. It avoids the use of writing MYSQL by hand and instead offers a powerful API for creating migrations using PHP code.

Advantages of using Phinx

  • Phinx keeps track of which migrations have been run so you can worry less about the state of your database and instead focus on building better software
  • Each migration is represented by a PHP class in a unique file. We can write our migrations using the Phinx PHP API, or raw SQL.
  • Phinx has an easy installation process and easy to use command line instructions and easy to Integrate with various other PHP tools (Phing, PHPUnit) and web frameworks.

Installating Phinx

Phinx should be installed using Composer. Composer is a tool for dependency management in PHP. We need to require the dependency in composer.json.

php composer.phar require robmorgan/phinx

Then run Composer:

php composer.phar install --no-dev

Now Create a folder in your database directory called migrations with adequate permissions. It is where we write our migrations. In engelsystem it is created in db directory

Phinx can now be executed from within your project:

php vendor/bin/phinx init

Writing Migrations For SQL files

Creating a New Migration

Let’s start by creating a new Phinx migration. Run Phinx using the create command. This will create a new migration in the format YYYYMMDDHHMMSS_my_new_migration.php where the first 14 characters are replaced with the current timestamp down to the second. This will create a skeleton file with a single method.

$ php vendor/bin/phinx create MyNewMigration

The File looks something like this

Screenshot from 2016-07-18 08:22:42

Explaining the File

The AbstractMigration Class

Abstraction class provides the necessary support to create your database migrations. All Phinx migrations extend from the AbstractMigration class. Phinx provides different methods in the abstraction class like change, up and down method.

The Change Method

This is the default migration method. I will explain how to write the change method for an example MYSQL query. For example following MYSQL query can also be executed using Phinx change method.

MYSQL Query

ALTER TABLE `AngelTypes` ADD `requires_driver_license` BOOLEAN NOT NULL;

Equivalent change method

public function change()
 {
   $table = $this->table('AngelTypes');
   $table->addColumn('requires_driver_license', 'boolean', array('null' => 'false'))
               ->update();
 }

The Up Method

We should use the up method to transform the database with your intended changes. For example following MYSQL query to create a new settings table can be executed using equivalent up method.

MYSQL Query

DROP TABLE IF EXISTS `Settings`;
 CREATE TABLE IF NOT EXISTS `Settings` (
    `event_name` varchar(255) DEFAULT NULL,
   `buildup_start_date` int(11) DEFAULT NULL,
   `event_start_date` int(11) DEFAULT NULL,
   `event_end_date` int(11) DEFAULT NULL,
   `teardown_end_date` int(11) DEFAULT NULL,
   `event_welcome_msg` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Equivalent up method

public function up()
 {
 $table = $this->table('Settings');
 $table->addColumn('event_name', 'string', array('limit' => 255))
 ->addColumn('buildup_start_date', 'integer', array('limit' => 11))
 ->addColumn('event_start_date', 'integer', array('limit' => 11))
 ->addColumn('event_end_date', 'integer', array('limit' => 11))
 ->addColumn('teardown_end_date', 'integer', array( 'limit' => 11))
 ->addColumn('event_welcome_msg', 'string', array('limit' => 255))
 ->save();
 }We have now created a table. Now we will learn to insert data into the tables using migrations.MYSQL QueryINSERT INTO `Privileges` (`id`, `name`, `desc`) VALUES (39, 'admin_settings', 'Admin Settings');
 INSERT INTO `GroupPrivileges` (`id`, `group_id`, `privilege_id`) VALUES (218, -4, 39);public function up()
{
    // inserting into Privileges
   $Rows = [
     [
     'id'   => 39,
     'name' => 'admin_settings',
     'desc' => 'Admin Settings'
     ]
   ];
  $this->insert('Privileges', $Rows);
  // inserting into GroupPrivileges.
   $rows = [
    [
    'id'    => 218,
    'group_id'  => -4,
    'privilege_id' => 39                                                 
  ]
   ];
  $this->insert('GroupPrivileges', $rows);
}

The Down Method

The down method is automatically run by Phinx when you are migrating down. We should use the down method to reverse/undo the transformations described in the up method.

MYSQL Query

DELETE * FROM  `Users`;

Equivalent Down method

public function down()
    {
        $this->execute('DELETE FROM Users');
    }

Since we have learned how to write migrations. Now we will execute the created migrations.

Configuring phinx.yml

When you initialize your project using the init command, Phinx creates a default file called phinx.yml.We can edit the database name, environment. We need to add the password for mysql user. The file looks something like this.Screenshot from 2016-07-18 07:56:13

Executing the migrations

To Migrate the database we use Migrate command. It runs over all the available migrations. Command to migrate for development environment is:

$ phinx migrate -e development

To migrate to a specific version we use the --target parameter or -t for short.

$ phinx migrate -e development -t 20110103081132

To know whether all your migrations have run successfully we use the status command

$ phinx status -e development

After migrating the database for engelsystem. The status command gives the following output.

88114b3e-4753-11e6-9afa-207e55650c1e