One of the greatest current difficulties in web development, undoubtedly, is maintaining a database amongst all users on a team. Every so often, a member of the team makes a change and does not notify the rest of the team members. This always causes extra work, bugs, or a programmer to pull out their hair.
Amongst the main reasons to this problem is the fact that most of us do not track database changes in our projects. We have SCM (source code management), project management tools to control the tasks, Google Docs for documents, but what about the database? Migrations was built to solve this issue. Migrations is an organized way to track the changes in the structure of the database, allowing, among other facilites, to build your database incrementally, back out change that are no longer appropriate or work in environments with multiple developers.
The basic concept of Migrations is to store the modifications of the bank on small files in chronological order. These files should contain information that they can build or destroy your database from one version to any other. How is that? Simply enter in your file and make a particular update (UP) and how to downgrade (DOWN), so when you want to go to a newer version, execute all the UPs of newer migration files, and when they want to downgrade, run up to the DOWNs of older migration files. With the aid of a helper application, which is usually an Automator, you can easily perform tasks such as (re) construction of database, update, change or rollbacks of something that is not what it should be.
In some implementations of Migrations, you may be able to perform data manipulation. This facility is a very interesting and something the DBA’s should worship. Imagine that you, at any given time, please make a change in the “full_name” breaking it in two “name” and “surname”. With the manipulation you can do the processing and migrate, not only the structure of the bank, but also the data that possibly can be stored. This feature gives a little more power to Migrations, but is not always present on all implementations.
What we have built is a plugin for the framework CakePHP, facilitating the distributed development for those using CakePHP.
- Migrations for CakePHP using YAML with PEAR by JoelMoss
- Migrations for CakePHP using YAML without PEAR by Georgi Momchilov
- Ruckusing
- Migrations on Ruby on Rails
- Migrations on Akelos PHP framework
Initially the console will have the following functions:
- help: demonstrates basic usage;
- create: creates an archive of the migrations with a template. Takes as a parameter a name for referencing the migration, without spaces or accents;
- up: will serve to update to the latest version. Can optionally be passed a parameter with the date and time (format YYYYMMDDHHMMSS) to specify the maximum date/time you want to update;
- down: as a parameter will have a date and time (format YYYYMMDDHHMMSS) to specify down to which date to remove. It is possible to instead use the parameter ‘last’ in order to remove the last migration installed;
reset: will run down of all migrations installed. You can use the ‘-force’ to perform all down’s and at the end clear the rest of the database (if any table is not created by the system migrations);
rebuild: implement the reset (may also be passed the parameter ‘-force’) and then run an UP to the last migration.
The control of migrations installed is stored in a table of the database, called ‘schema_migrations’. Take care not to bake this table, let alone change it.
Migrations is the parent class for migration files. This class will have all the facilities necessary for the development of the code. You can put a directory of your application file to include app_migrations.php methods common to all the children of Migrations, and makes files app_model.php, app_controller.php, etc.. Note that this file is optional. The structure you can check below:
<?php
class AppMigrations extends Migrations {
}
?>You can use the following functions (the style will be described shortly):
- run: Run a SQL;
Besides these methods, each migration can have its own callbacks, as in: beforeCreateTable, afterCreateTable, beforeDropTable, … Besides these callbacks, you can also implement beforeInstall, afterInstall, beforeUninstall and afterUninstall. In any of these callbacks, you can return false if there is an interruption in the migration that is in progress.
And to facilitate the migration, one can write something to the screen, using the out function.
You can also instantiate a model using the $uses variable in the class, as done in controllers. The model must exist! In the examples you can see the usefulness. If you want to create a model at runtime, without having the model file ready, you can do this through the getModel method.
createTable($tableName, $columns, $indexes = array(), $options = array())
The field $tableName is the name of the table. $columns represents the fields to be inserted. $indexes are the indices. Primary indices can be defined with the columns (explanation below). The options field can used to specify to not include any of the automatic fields.
Explaining a little about the $columns: it is the same used by CakePHP schema. It should be an array where the keys are the field names and value an array with the following information:
- type: (required) Type field. You can have the values ‘string’, ‘integer’, ‘float’, …
- length: (optional) Defines the size of the field. For example, a field ‘string’ must be informed of the value, otherwise it is a field string of length 1.
- null: (optional) Determine whether the field can be null. The value must be a boolean.
- default: (optional) Tells the default value of the field. It should be set as a string.
- after: (optional) Information to enter the field after the field. This field does not make much sense in createTable, but in addColumn it is useful.
- key: (optional) Set the key name. If you chose the name ‘primary’ it will be the primary field.
The field $options is an array and can contain the following values:
- insertId: tells you if you enter the id field automatically. The default is true.
- insertCreated: tells you if you enter the field created automatically. The default is true.
- insertModified: tells whether the field will be included automatically modified. The default is true.
- insertUpdated: tells you if you enter the field updated automatically. The default is false.
If you set any of these fields in the $columns, it will bypass their automatic insertion.
Under Construction.
dropTable($tableName)
Used to delete a table. Usually used in the down method.
addColumn($tableName, $columnName, $columnConfig = array())
Add a column after inserting a table. The field $columnConfig follows the same rule described in createTable.
removeColumn($tableName, $columnName)
Remove a column.
changeColumn($tableName, $columnName, $newColumnConfig = array(), $verbose = true)
Alter the characteristics of a column. The verbose parameter can be ignored.
renameColumn($tableName, $oldColumnName, $newColumnName)
Rename a column.
Under Construction.
Under Construction.
out($message, $newLine = true)
Write a message in the shell. Just to give some status or information about what is happening …
getModel($tableName)
Creates an object that inherits from AppModel with the settings of the table passed by parameter. Returns an object similar to a model without relationships.
Identical to the variable $uses in a controller.
<?php
/*
Arquivo: 20090228181615_add_user_table.php
Ou seja: Created on 28/02/2009 18:16:15 with a classname of AddUserTable (the name of the class should be the camelized form of the name of the file).
*/
class AddUserTable extends AppMigrations {
public function up() {
$this->createTable('users', array(
'name' => 'string',
'pass' => 'string',
'email' => array('type' => 'string', 'lenght' => '100')
));
// Do Not need to specify created or modified
}
public function down() {
$this->dropTable('users');
}
}
?>The above class will create the users table with the fields id, name, pass, mail, created and modified.
You can also do something more detailed with migrations:
<?php
/*
Class AddUserTable
*/
class AddUserTable extends AppMigrations {
public $uses = array('Profile');
public function up() {
$this->createTable('users',array(
'pass' => 'string',
'email' => array('type' => 'string', 'lenght' => '100')
));
$profiles = $this->Profile->find('all');
$user = $this->getModel('User'); // It assumes that the commit was sent User.php the model, this goes for anyone who update the repository ...
foreach ($profiles as $profile) {
$user->create(array(
'pass' => sha1('123'),
'email' => $profile['Profile']['email']
));
$user->save();
}
}
public function down() {
$this->dropTable('users');
}
}
?>In other words, you can access existing models or even inserting records in the table that is being created at that time.