Tag Archives: db migrations

Ruckusing PHP DB Migrations Framework

10 Feb

Introduction

  • Ruckusing is a framework written in PHP5 for generating and managing a set of “database migrations”. Database migrations are declarative files which represent the state of a DB (its tables, columns, indexes, etc) at a particular state of time. By using database migrations, multiple developers can work on the same application and be guaranteed that the application is in a consistent state across all remote developer machines.

Setting up the Ruckusing PHP Framework

  • Ensure that the PHP CLI (usually in /usr/bin/php) is your PATH environment variable.
  • Ensure that the php executable has been built with support for Postgres and/or MySQL
  • If you have open_basedir in effect in your php.ini file (usually /etc/php.ini), edit and add an open_basedir entry for the folder that contains the ruckusing framework.
  • Set the date.timezone configuration parameter in /etc/php.ini as follows. This is required for the Timestamp based migrations to work correctly.
    date.timezone =  'America/New_York'

     

Configure Database Connectivity

  • Edit config/database.inc.php and enter DB details.
  • This should match the $configName entry in Service_Request/Config.php For example:
<?php
//---------------------------- // DATABASE CONFIGURATION // 'development' is default and required by ruckusing // 'sun' (Sunrise), 'tmk' (Terremark), 'vmw' (VMWare CentOS), 'win' (Windows XAMPP) //---------------------------- $ruckusing_db_config = array(

    'development' => array(
        'type'      => 'mysql',
        'host'      => 'localhost',
        'port'      => 3306,
        'database'  => 'item_1_1_1_m',
        'user'      => 'root',
        'password'  => 'XXXXXXXXX'
    ),
    'sun' => array(
 'type'    => 'mysql',
 'host'    => '10.16.35.213',
 'port'   => 3306,
 'database'  => 'item_1_1_1_m'',
 'user'    => 'root',
 'password'  => 'XXXXXXXX'
    )
);

?>

Initialize Database Migration

  • This task will create a schema_migrations table in your DB with a single column version which is used to keep track of the current version of your DB.
  • Ensure you are in the top level directory of the Ruckusing Framework and execute”
    • /usr/bin/php main.php db:setup

Test Current Version of Database Migration

  • This task will check the schema_migrations table in your DB and return the value of the “version” column
  • /usr/bin/php main.php db:version

Create a Migration Template – CREATE A TABLE

  • Let’s assume that we want to add a table for some new functionality to support blogging. For example, we want to create a table called posts.
  • Execute the following command:
    • /usr/bin/php generate.php create_posts_table
  • The previous command will create a PHP file under db/migrate/<timestamp>_CreatePostsTable.php

Edit the Migration Template File

  • Open the <timestamp>_CreatePostsTable.php in your favorite editor.
  • Note the empty up() and down() methods
    • The up() method contains the set of actions for migrations that have NOT been executed against your copy of the database yet and that are based in increasing timestamped order.
    • The down() method is used for rollback/UNDO of migration actions
      class CreatePostsTable extends Ruckusing_BaseMigration {
      
       public function up() {
      
                    $t = $this->create_table("posts", array("id" => false));
                    $t->column("post_id", "integer", array("null" => false, "primary_key" => true));
                    $t->column("subject", "string");
                    $t->column("body", "string");
                    $t->column("created_at", "datetime", array('null' => false, "primary_key" => true));
                    $t->column("author_id", "integer");
                    $t->finish();
                    $this->add_index("posts", "author_id");
      
       }//up() 
       public function down() {
                    $this->drop_table("posts");
       }//down() 

       

  • To see a list of column datatype mappings see https://github.com/ruckus/ruckusing-migrations/wiki/Adding-Columns
  • To see a list of Migration APIs you can use in the up()/down() methods see https://github.com/ruckus/ruckusing-migrations/wiki/Migration-Methods

Run the Migrations to create the new table

  • /usr/bin/php main.php db:migrate

Verify the current DB migration version

  • /usr/bin/php main.php db:version

Create a Migration Template – ADD A COLUMN

  • Execute the following command:
    • /usr/bin/php generate.php add_deleted_at_column_to_posts_table
  • The previous command will create a PHP file under db/migrate/<timestamp>_AddDeletedAtColumnToPostsTable.php

Edit the Migration Template File

  • Open the <timestamp>_AddDeletedAtColumnToPostsTable.php in your favorite editor and edit it as follows:
class CreatePostsTable extends Ruckusing_BaseMigration {

      public function up() {
                  $this->add_column("posts", "deleted_at", "datetime");
      }//up() 
      public function down() {
           $this->remove_column("posts", "deleted_at");
      }//down()

Run the Migrations to add the new column

  • /usr/bin/php main.php db:migrate

Verify the current DB migration version

  • /usr/bin/php main.php db:version

Undo/Rollback to a previous Migration version

  • If for example, the current version of the DB is 20101006114707 and we want to go down to 20100921114643:
    • /usr/bin/php main.php db:migrate VERSION=20100921114643

References

Advertisements