DB Migrations for Yii

DbMigrateCommand.php

<?php

/*
* Manages migrations for Yii database
*
* Author: Ryan Bales <[email protected]>, 2011
*/
class DbMigrateCommand extends CConsoleCommand
{
	const MIGRATION_TABLE = 'Migration';
	private $migration_dir_path;

	public function run($args)
	{
		if( count($args) > 0 )
			$action = $args[0];

		if( isset($action) && is_callable(array($this,$action)) )
			$this->$action(@array_slice($args,1));
		else
			$this->usage();
	}

	private function usage()
	{
		$usage = <<<USAGE
Usage: php <entry.php> dbmigrate <action> [arguments ...]

actions:
	update: runs migrations
		arguments: 	if one argument given, any migrations later than given will be executed,
					if two arguments are given, migrations between arguments inclusive will be executed
					defaults to all migrations that haven't been executed
	create: create a new migration
	init_table: deletes and recreates migration table

USAGE;
		printf("%s",$usage);
	}

	/**
	 * update 
	 * 		Creates and runs migrations
	 * @param array $args migration to and from, optional
	 * @access private
	 * @return void
	 */
	private function update($args=array())
	{
		$this->migration_dir_path = $this->getMigrationFilePath();
		$migrations = array();
		if( count($args) > 0 )
		{
			// default to run all from first argument
			$migrate_from = $args[0];
			$migrate_to = null;
			if( count($args) > 1 ){ // run all between first and second argument, inclusive
				$migrate_to = $args[1];
			} 
			foreach( $this->getMigrationFileIndices($migrate_from,$migrate_to) as $migration_file_index )
				$migrations[] = $this->getNewMigration($this->getFullMigrationFilePath($migration_file_index));
		} 
		else { // run all not run since last run
			$migration_files = $this->getAllMigrationFileIndices();
			$latest_migration_run = $this->getLatestMigrationRun();
			if( empty($latest_migration_run) )
			{
				foreach( $migration_files as $migration_file_index )
					$migrations[] = $this->getNewMigration($this->getFullMigrationFilePath($migration_file_index));
			} else {
				foreach( $this->getMigrationFileIndices(($latest_migration_run + 1)) as $migration_file_index )
					$migrations[] = $this->getNewMigration($this->getFullMigrationFilePath($migration_file_index));
			}
		}
		foreach( $migrations as $migration )
		{
			$migration->run();
		}
	}


	private function create($args=array())
	{
		$new_migration_file_name = $this->getNewMigrationFileName();
		printf("\nCreating new db migration %s...\n",$new_migration_file_name);
		$migration_file = fopen($new_migration_file_name,'w');
		fclose($migration_file);
	}

	private function init_table($args=array())
	{
		$q = array(sprintf('DROP TABLE IF EXISTS `%s`',self::MIGRATION_TABLE));
		$q[] = <<<SQL
CREATE TABLE `<<TABLE>>` (
	`id` INT UNSIGNED NOT NULL PRIMARY KEY,
	`created_at` TIMESTAMP NOT NULL DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQL;
		foreach($q as $query)
		{
			$command = Yii::app()->db->createCommand(str_replace('<<TABLE>>',self::MIGRATION_TABLE,$query));
			$command->execute();
		}
	}

	/**
	 * getMigrationFilePath 
	 * 	Returns default filepath to migration files,
	 *  creates the filepath if it doesn't exist
	 * @access private
	 * @return string file_path
	 */
	private function getMigrationFilePath()
	{
		$file_path = implode('/',array(
					Yii::app()->basePath,
					'scripts',
					'db',
					'migrations'
				));
		if( !is_dir($file_path) )
			mkdir($file_path, 0775, TRUE);
		return $file_path;
	}

	/**
	 * getNewMigrationFileName 
	 * 	Reads the migration files director, searches for new migration index
	 * @access private
	 * @return string the new migration file name
	 */
	private function getNewMigrationFileName()
	{
		$new_migration_file_index = 1;
		$migration_dir_path = $this->getMigrationFilePath();
		foreach( scandir($migration_dir_path) as $migration_file )
		{
			$migration_file_segments = explode('.',$migration_file);
			$migration_file_index = $migration_file_segments[0];
			if( $migration_file_index > $new_migration_file_index )
				$new_migration_file_index = $migration_file_index;
		}
		return $this->getFullMigrationFilePath(($new_migration_file_index + 1),$migration_dir_path);
	}

	private function getFullMigrationFilePath($migration_file_index,$migration_dir_path=null)
	{
		if( is_null($migration_dir_path) )
			$migration_dir_path = $this->migration_dir_path;
		return sprintf('%s/%d.sql',$migration_dir_path,($migration_file_index));
	}

	/**
	 * getLatestMigrationRun 
	 * 	Gets latest migration executed
	 * @access private
	 * @return int last migration run
	 */
	private function getLatestMigrationRun()
	{
		$q = sprintf("SELECT id FROM %s ORDER BY id DESC limit 1",self::MIGRATION_TABLE);
		$command = Yii::app()->db->createCommand($q);
		return $command->queryScalar();
	}

	private function getMigrationFileIndices($migrate_from,$migrate_to=null)
	{
		$migration_file_indices = $this->getAllMigrationFileIndices();
		$return_migration_file_indices = array();
		foreach( $migration_file_indices as $migration_file_index )
		{
			if( $migration_file_index >= $migrate_from ) {
				if( (is_null($migrate_to)) || ($migrate_to >= $migration_file_index) )
					$return_migration_file_indices[] = $migration_file_index;
			}
		}
		return $return_migration_file_indices;
	}

	private function getAllMigrationFileIndices()
	{
		$migration_file_indices = array();
		foreach( scandir($this->migration_dir_path) as $migration_file )
		{
			$migration_file_segments = explode('.',$migration_file);
			if( count($migration_file_segments) > 1 && $migration_file_segments[1] == 'sql' )
				$migration_file_indices[] = $migration_file_segments[0];
		}
		return $migration_file_indices;
	}

	private function getNewMigration($migration_file_path)
	{
		return new Migration($migration_file_path);
	}
}

class Migration
{
	private $migration_file;
	private $migration_file_index;

	public function __construct($migration_file_path)
	{
		$migration_file_path_segments = explode('/',$migration_file_path);
		$this->migration_file_index = $migration_file_path_segments[(count($migration_file_path_segments) - 1)];
		$this->migration_file = fopen($migration_file_path,'r');
	}

	public function __destruct()
	{
		fclose($this->migration_file);
	}

	public function run()
	{
		printf("Running migration %s...\n",$this->migration_file_index);
		$q = readfile($this->migration_file_path);
		$command = Yii::app()->db->createCommand($q);
		$command->execute();
	}
}