http://databaserefactoring.com/ ( the 'transformations' sections deal with both data+schema)
The one I already recommended! (see the post you quoted). Don't know of any others, didn't need them. Its a simple concept. Here is the code I am using:
My CLI driver,
I can call `php dbrefactor.php` to bring me from whatever version # I'm at, to the latest version (I track the version # in the table), or use the reset flag to drop & recreate the database and run the scripts version 0, basically a full re-play. What I do is set up 2 databases, development and production (those are theyre names, dont be confused, they both resides in development). The scripts then copy tables into the production database, and start chiseling away at the schema. Keep all the scripts in source control and make them small incremental changes. If you are doing 100s of these you're going to end up "going back in time" and changing things here and there.
Code: Select all
<?php
require_once( dirname( __FILE__ ) . '/../application/tests/bootstrap.php' );
$factory = new Shuffler_Db_Factory();
$refactor = new Db_Refactor( $factory );
$refactor->execute( DB_REFACTOR_PATH, isset( $argv[1] ) && $argv[1] == '--reset' );
My Db_Refactor class is just project specific configuration. it extends Shuffler_Db_Refactor which is on my google code repository if you would like to use it. So the "project specific configuration" is something like:
Code: Select all
class DB_Refactor extends Shuffler_DB_Refactor
{
/**
* @return integer
*/
protected function getCurrentVersion()
{
$tables = $this->getTables();
$currentVersion = 0;
if( in_array( 'version', $tables ) )
{
$r = mysql_query(
sprintf(
"
SELECT
`version`
FROM
`version`
LIMIT 1
"
)
);
$currentVersion = @mysql_result( $r, 0 );
}
return $currentVersion;
}
/**
* @return array
*/
protected function getTables()
{
$result = $this->factory->execute('SHOW TABLES');
$tables = array();
while( $row = mysql_fetch_row( $result ) )
{
array_push( $tables, $row[0] );
}
return $tables;
}
/**
* @return array
*/
protected function createDatabases()
{
mysql_query( 'create database ' . $this->getSchemaNameForVersionTable() );
}
protected function dropDatabases()
{
mysql_query( 'drop database ' . $this->getSchemaNameForVersionTable());
}
protected function createVersionTable()
{
mysql_select_db( $this->getSchemaNameForVersionTable() );
// create version
mysql_query(
sprintf(
"
CREATE TABLE `%s`.`version` (
`version` INT( 5 ) NOT NULL
)
",
$this->getSchemaNameForVersionTable()
)
);
mysql_query( " INSERT INTO `version` ( `version` ) VALUES ( 0 ) " );
}
protected function getSchemaNameForVersionTable()
{
return 'vafconform';
}
protected function getMysqlCommand()
{
return MYSQL_COMMAND;
}
}
Basically provides the strategies the "engine" needs to perform tasks like reading the version table. The version table keeps track of the last re factoring script ran. Refactoring scripts can be .sql or .php, I prefer .sql, its faster too, but sometimes you want to use your Model to calculate a value and need a .php script, my "engine" handles different script formats:
Code: Select all
<?php
abstract class Shuffler_DB_Refactor
{
/**
* @var Shuffler_Db_Factory
*/
protected $factory;
public function __construct( Shuffler_Db_Factory $factory )
{
$this->factory = $factory;
}
/**
* @return integer
*/
abstract protected function getCurrentVersion();
/**
* @return array
*/
abstract protected function getTables();
abstract protected function createDatabases();
abstract protected function dropDatabases();
abstract protected function createVersionTable();
abstract protected function getSchemaNameForVersionTable();
abstract protected function getMysqlCommand();
public function execute( $pathToScripts, $reset = false, $toVersion = null )
{
try
{
$currentVersion = $this->getCurrentVersion();
} catch ( Exception $e )
{
$this->createDatabases();
$currentVersion = 0;
}
if( $reset || $currentVersion == 0 )
{
$this->dropDatabases();
$this->createDatabases();
$this->createVersionTable();
$currentVersion = 0;
}
self::migrate( $pathToScripts, $currentVersion, $toVersion );
}
/**
* Bring the database to a requested version #
*/
function migrate( $pathToScripts, $fromVersion = 0, $toVersion = NULL )
{
// get all the database refactoring scripts
$files = glob( $pathToScripts . '/*' );
foreach( $files as $key => $val )
{
$files[ $key ] = basename( $val );
}
// sort the files in version order
asort( $files, SORT_NUMERIC );
foreach( $files as $file )
{
// get the version # from the filename
preg_match( '#^([0-9]+)_#', basename( $file ), $matches );
if( !isset( $matches[1] ) )
{
continue;
}
$version = $matches[1];
if( $version <= $fromVersion )
{
// already loaded this version, skip it
continue;
}
if( !is_null( $toVersion ) )
{
if( $version > $toVersion )
{
echo 'stopping short at version ' . $toVersion;
break;
}
}
self::runScript( $version, $pathToScripts . '/' . $file );
}
}
/**
* run the version + script as php or SQL
* updates the version table
*/
function runScript( $version, $file )
{
ob_implicit_flush();
echo 'running migration script ' . $version . ' ' . $file . "\n";
if( substr( $file, -3 ) == 'php' )
{
require( $file );
}
else // or as sql
{
$cmd = $this->getMysqlCommand() . ' ' . $this->getSchemaNameForVersionTable() . ' < "' . $file . "\"\n";
//echo( $cmd );
passthru( $cmd );
}
mysql_query( 'UPDATE `version` SET `version` = ' . (int)$version );
}
/**
* Drop requested tables
*
* @param Shuffler_Factory
* @param array of tables
*/
function dropTables( $factory, $tables )
{
foreach( $tables as $table )
{
mysql_query( 'DROP TABLE `' . $table . '`' );
}
}
}
An SQL to move from 1Nf to 2Nf would look something like:
Code: Select all
CREATE TABLE `lookup` (
`id` INT( 15 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fk` INT( 200 ) NOT NULL ,
`title` VARCHAR( 200 ) NOT NULL ,
UNIQUE (
`fk`,
`title`
)
) ENGINE = InnoDb ;
INSERT INTO lookup ( fk, title )
SELECT
id, title
FROM non_normalized
GROUP BY id, title;
UPDATE `non_normalized`
SET `non_normalized`.`title` = (
select
`lookup`.`id`
FROM
`lookup`
WHERE
`non_normalized`.`title` = `lookup`.`title`
AND `non_normalized`.`id` = `lookup`.`fk`
)
its slightly more readable in PHP with while loops, but less portable
Summed up this is what this code does: It looks in the database for the version table, to get the current version. It looks in your dbrefactor script path to find dbrefactor scripts. If there are scripts with a larger number than the current version, it runs them.
Scripts should be named
1_create_initial_stuff.sql
2_create_lookup_table.sql
3_normalize_lookup_data.php
4_drop_unneeded_table_col.sql
(Use searchable names)