Cake DB Migrations v3.2
This article may be a little outdated. For the latest version and more info, please go to http://developingwithstyle.com
Cake DB Migrations has now been updated to work with the very latest version of CakePHP 1.2 and the new (hopefully final) console/shells system. I have also added a few extra goodies to make life a little easier.
The new features and changes are as follows:
* You can now specify a column without the need to specify the column type. Type is set to string, which is simply a varchar(255) column.
* Ability to add user definable foreign keys by simply specifying the 'fkey' as a column name, followed by the name(s) of the foreign key(s).
* You can now include PHP code within the YAML migration files
Because the console system in Cake 1.2 has changed a bit, ou now have to place the below script in a slightly different place. Within your main 'vendors' directory above your app and cake core directories, paste the below code into a file called 'migrate.php' and place that file in a directory called 'shells'.
Now just bring up your favourite command line tool and cd into your cake applications root directory and run the following:
./cake migrate
And that is it! That will migrate to the lastest version. You can specify the version like this:
./cake migrate -v 3
As promised, I hope to create a screencast going through all aspects of migrations and how they can save your life.
Cake DB Migrations has now been updated to work with the very latest version of CakePHP 1.2 and the new (hopefully final) console/shells system. I have also added a few extra goodies to make life a little easier.
The new features and changes are as follows:
* You can now specify a column without the need to specify the column type. Type is set to string, which is simply a varchar(255) column.
* Ability to add user definable foreign keys by simply specifying the 'fkey' as a column name, followed by the name(s) of the foreign key(s).
* You can now include PHP code within the YAML migration files
Because the console system in Cake 1.2 has changed a bit, ou now have to place the below script in a slightly different place. Within your main 'vendors' directory above your app and cake core directories, paste the below code into a file called 'migrate.php' and place that file in a directory called 'shells'.
Now just bring up your favourite command line tool and cd into your cake applications root directory and run the following:
./cake migrate
And that is it! That will migrate to the lastest version. You can specify the version like this:
./cake migrate -v 3
As promised, I hope to create a screencast going through all aspects of migrations and how they can save your life.
Download code
<?php
/**
* The MigrateTask runs your database migrations to the specified scheam version.
* If no version is specified, migrations are run to the latest version.
*
* PHP versions 4 and 5
*
* Licensed under The MIT License
* Redistributions of files must retain the above copyright notice.
*
* @filesource
* @copyright Copyright 2006-2007, Joel Moss
* @link http://joelmoss.info
* @package cake
* @subpackage cake.cake.scripts.bake
* @since CakePHP(tm) v 1.2
* @version $Version: 1.0 $
* @modifiedby $LastChangedBy: joelmoss $
* @lastmodified $Date: 2007-02-16 09:09:45 +0000 (Fri, 16 Feb 2007) $
* @license http://www.opensource.org/licenses/mit-license.php The MIT License
*
*
* @Changelog (started as of v3.0)
*
* v 3.2
* [+] refactored (again!) to work with the new Cake console
* [+] default type is now 'string', which means that a 'text(255)' column can be created without specifying the type
* [+] can now specify 'fkey' or 'fkeys' as a column name, then passing one or more foreign key names
* Example:
* fkey: user # an integer column will be created called 'user_id'
* or
* fkeys: [user, group] # two integer columns will be created called 'user_id' and 'group_id'
* v 3.1
* [+] can now parse and run PHP code within migration files
* [+] added string type. any column with a type of 'string' without a length set will default to varchar(255)
* [+] automatically detects any column name ending with '_id' as an integer
* v 3.0
* [+] refactored as a bake2 task compatible with CakePHP 1.2
*
*/
uses('file', 'folder');
class MigrateShell extends Shell
{
var $dataSource = 'default';
var $db;
function initialize()
{
$this->welcome();
$this->hr();
$this->out('App: '. APP_DIR);
$this->out('Path: '. ROOT . DS . APP_DIR);
$this->hr();
$this->initDatabase($this->dataSource);
define('FIXTURES_PATH', APP_PATH .'config' .DS. 'fixtures');
define('MIGRATIONS_PATH', APP_PATH .'config' .DS. 'migrations');
$this->getMigrationVersion();
$this->getMigrations();
}
function main()
{
$this->to_version = isset($this->params['v']) ? $this->params['v'] : $this->migration_count;
if ($this->to_version === 'reset')
{
$this->reset();
exit;
}
$this->run();
}
/**
* Reset migration version to zero without running migrations up or down
*/
function reset()
{
$this->hr();
$this->out('');
$this->out('Resetting Migrations:');
$tables = $this->_db->listTables();
foreach ($tables as $table)
{
if ($table == 'schema_info' || $table == CAKE_SESSION_TABLE) continue;
$r = $this->_db->dropTable($table);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
$this->out('');
$this->out(' Table \''.$table.'\' have been dropped.');
}
$r = $this->_db->exec("UPDATE `schema_info` SET version=0");
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
$this->out('');
$this->out(' Database schema reset to zero.');
$this->hr();
exit;
}
function run()
{
$this->hr();
if ($this->migration_count === 0)
{
$this->out('');
$this->out(' ** No migrations found **');
$this->out('');
$this->hr();
$this->out('');
exit;
}
$new_version = $this->to_version;
if (!is_numeric($new_version))
{
$this->out('');
$this->out(' ** Migration version number ('.$new_version.') is invalid. **');
$this->out('');
$this->hr();
$this->out('');
exit;
}
if ($new_version > $this->migration_count)
{
$this->out('');
$this->out(' ** Version number entered ('.$new_version.') does not exist. **');
$this->out('');
$this->hr();
$this->out('');
exit;
}
if ($this->current_version == $new_version)
{
$this->out('');
$this->out(' ** Migrations are up to date **');
$this->out('');
$this->hr();
$this->out('');
exit;
}
$direction = ($new_version < $this->current_version) ? 'down' : 'up';
if ($direction == 'down') usort($this->migrations, array($this, '_downMigrations'));
$this->out('');
$this->out(" Migrating database $direction from version {$this->current_version} to $new_version ...");
$this->out('');
foreach($this->migrations as $migration_name)
{
preg_match("/^([0-9]+)\_(.+)$/", $migration_name, $match);
$num = $match[1];
$name = $match[2];
if ($direction == 'up')
{
if ($num <= $this->current_version) continue;
if ($num > $new_version) break;
}
else
{
if ($num > $this->current_version) continue;
if ($num == $new_version) break;
}
$this->out(" [$num] $name ... ", false);
$this->running_migration_name = $migration_name;
$res = $this->startMigration(MIGRATIONS_PATH . '/' . $migration_name, $direction);
if ($res == 1)
{
$this->out('Complete.');
$this->out('');
if ($direction == 'up')
{
$r = $this->_db->exec("UPDATE `schema_info` SET version=version+1");
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
else
{
$r = $this->_db->exec("UPDATE `schema_info` SET version=version-1");
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
else
{
$this->out(" ERROR: $res");
$this->hr();
exit;
}
}
$this->out(' Migrations completed.');
$this->out('');
$this->hr();
$this->out('');
exit;
}
function startMigration($file, $direction)
{
$yml = $this->_parsePhp($file);
if (function_exists('syck_load'))
{
$array = @syck_load($yml);
}
else
{
vendor('Spyc');
$array = Spyc::YAMLLoad($yml);
}
if (!is_array($array)) return "Unable to parse YAML Migration file";
if (!$array[up($direction)]) return "Direction does not exist!";
return $this->_array_to_sql($array[up($direction)]);
}
function _array_to_sql($array)
{
foreach ($array as $name=>$action)
{
if ($name == 'create_table' || $name == 'create_tables')
{
/*
* Valid fields: text, string, integer, blob, boolean, float, date, time, timestamp(datetime)
* Read: http://cvs.php.net/viewcvs.cgi/pear/MDB2/docs/datatypes.html?view=co
*/
foreach ($action as $table=>$fields)
{
$rfields = array();
$indexes = array();
$uniques = array();
$pk = array();
if (!isset($fields['no_id']))
{
$rfields['id']['type'] = 'integer';
$rfields['id']['notnull'] = true;
$rfields['id']['autoincrement'] = true;
}
foreach ($fields as $field=>$props)
{
if($field == 'no_id' || $field == 'created' || $field == 'modified' || $field == 'no_dates' || $field == 'fkey' || $field == 'fkeys') continue;
if (preg_match("/\\_id$/", $field) && count($props) < 1)
{
$rfields[$field]['type'] = 'integer';
continue;
}
$props['type'] = isset($props['type']) ? $props['type'] : 'string';
$rfields[$field]['type'] = $props['type'];
if ($props['type'] == 'string')
{
$rfields[$field]['type'] = 'text';
if (!isset($props['length'])) $rfields[$field]['length'] = 255;
}
if (isset($props['length']))
$rfields[$field]['length'] = $props['length'];
if (isset($props['notnull']))
$rfields[$field]['notnull'] = $props['notnull'] ? true : false;
if (isset($props['default']))
$rfields[$field]['default'] = $props['default'];
if (isset($props['index'])) $indexes[] = $field;
if (isset($props['unique'])) $uniques[] = $field;
if (isset($props['primary'])) $pk[$field] = '';
}
if (!isset($fields['created'])) $fields['created'] = null;
if (!isset($fields['no_dates'])) $fields['no_dates'] = null;
if (!isset($fields['modified'])) $fields['modified'] = null;
if ($fields['created'] !== false && $fields['no_dates'] !== true)
{
$rfields['created']['type'] = 'timestamp';
$rfields['created']['notnull'] = false;
$rfields['created']['default'] = NULL;
}
if ($fields['modified'] !== false && $fields['no_dates'] !== true)
{
$rfields['modified']['type'] = 'timestamp';
$rfields['modified']['notnull'] = false;
$rfields['modified']['default'] = NULL;
}
if (isset($fields['fkey']))
{
$rfields[$fields['fkey'].'_id']['type'] = 'integer';
}
if (isset($fields['fkeys']))
{
foreach($fields['fkeys'] as $key)
{
$rfields[$key.'_id']['type'] = 'integer';
}
}
$r = $this->_db->createTable($table, $rfields, array('primary'=>$pk));
if (PEAR::isError($r)) $this->err($r->getUserInfo());
if (count($indexes) > 0)
{
foreach ($indexes as $field)
{
$r = $this->_db->createIndex($table, $field, array(
'fields'=>
array($field=>array())
));
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
if (count($uniques) > 0)
{
foreach ($uniques as $field)
{
$r = $this->_db->createConstraint($table, $field, array(
'unique'=>true,
'fields'=>
array($field=>array())
));
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
}
}
elseif ($name == 'drop_table' || $name == 'drop_tables')
{
if (is_array($action))
{
foreach ($action as $table)
{
$r = $this->_db->dropTable($table);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
else
{
$r = $this->_db->dropTable($action);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
elseif ($name == 'add_fields' || $name == 'add_field')
{
/*
* Valid fields: text, integer, blob, boolean, float, date, time, timestamp(datetime)
* Read: http://cvs.php.net/viewcvs.cgi/pear/MDB2/docs/datatypes.html?view=co
*/
foreach ($action as $table=>$fields)
{
$rfields = array();
$indexes = array();
$uniques = array();
$pk = array();
foreach ($fields as $field=>$props)
{
if ($field == 'created' || $field == 'modified')
{
$rfields[$field]['type'] = 'timestamp';
$rfields[$field]['notnull'] = false;
$rfields[$field]['default'] = NULL;
}
else
{
if (preg_match("/\\_id$/", $field) && count($props) < 1)
{
$rfields[$field]['type'] = 'integer';
continue;
}
$rfields[$field]['type'] = $props['type'];
if ($props['type'] == 'string')
{
$rfields[$field]['type'] = 'text';
if (!$props['length']) $rfields[$field]['length'] = 255;
}
if ($props['type'] == 'text' && !$props['length'])
$rfields[$field]['length'] = 255;
if (isset($props['length']))
$rfields[$field]['length'] = $props['length'];
$rfields[$field]['notnull'] = $props['notnull'] ? true : false;
if (isset($props['default']))
$rfields[$field]['default'] = $props['default'];
if ($props['index']) $indexes[] = $field;
if ($props['unique']) $uniques[] = $field;
if ($props['primary_key']) $pk = $field;
}
}
if (isset($fields['fkey']))
{
$rfields[$fields['fkey'].'_id']['type'] = 'integer';
}
if (isset($fields['fkeys']))
{
foreach($fields['fkeys'] as $key)
{
$rfields[$key.'_id']['type'] = 'integer';
}
}
$r = $this->_db->alterTable($table, array('add'=>$rfields), false);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
if ($pk)
{
$r = $this->_db->createConstraint($table, $pk, array(
'primary'=>true,
'fields'=>
array($pk=>array())
));
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
if (count($indexes) > 0)
{
foreach ($indexes as $field)
{
$r = $this->_db->createIndex($table, $field, array(
'fields'=>
array($field=>array())
));
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
if (count($uniques) > 0)
{
foreach ($uniques as $field)
{
$r = $this->_db->createConstraint($table, $field, array(
'unique'=>true,
'fields'=>
array($field=>array())
));
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
}
}
}
elseif ($name == 'drop_fields' || $name == 'drop_field')
{
foreach ($action as $table=>$fields)
{
if (is_array($fields))
{
foreach($fields as $nil=>$field) $rfields[$field] = array();
$r = $this->_db->alterTable($table, array('remove'=>$rfields), false);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
}
else
{
$r = $this->_db->alterTable($table, array('remove'=>array($fields=>array())), false);
if (PEAR::isError($r)) $this->err($r->getDebugInfo());
&nbs