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":1 (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":2 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":3, facilitating the distributed development for those using CakePHP.
h2. Related Projects
- "Migrations for CakePHP using YAML with PEAR by JoelMoss":4
- "Migrations for CakePHP using YAML without PEAR by Georgi Momchilov":8
- "Ruckusing":5
- "Migrations on Ruby on Rails":6
- "Migrations on Akelos PHP framework":7
h2. Usage
h3. Console
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.
h3. Migrations Class
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:
You can use the following functions (the style will be described shortly):
- createTable: Create a table;
- changeTable: alter a table (under construction);
- dropTable: Remove a table;
- renameTable: Renaming a table;
- addColumn: Add a new column in a table;
- removeColumn: Remove a column of the table;
- changeColumn: Change the type of a column;
- renameColumn: Rename a column;
- addIndex: Create a new index (under construction);
- removeIndex: Remove an index (under construction);
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.
h4. createTable
@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.
h4. changeTable
Under Construction.
h4. dropTable
@dropTable($tableName)@
Used to delete a table. Usually used in the down method.
h4. addColumn
@addColumn($tableName, $columnName, $columnConfig = array())@
Add a column after inserting a table. The field $columnConfig follows the same rule described in createTable.
h4. removeColumn
@removeColumn($tableName, $columnName)@
Remove a column.
h4. changeColumn
@changeColumn($tableName, $columnName, $newColumnConfig = array(), $verbose = true)@
Alter the characteristics of a column. The verbose parameter can be ignored.
h4. renamColumn
@renameColumn($tableName, $oldColumnName, $newColumnName)@
Rename a column.
h4. addIndex
Under Construction.
h4. removeIndex
Under Construction.
h4. out
@out($message, $newLine = true)@
Write a message in the shell. Just to give some status or information about what is happening ...
h4. getModel
@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.
h4. $uses
Identical to the variable $uses in a controller.
h3. Examples of classes using AppMigrations
bc. createTable('users', array( 'name' => 'string', 'pass' => 'string', 'email' => array('type' => 'string', 'lenght' => '100') )); // Do Not need to specify created or modified } 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:
bc. 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(); } } 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.
[1]http://en.wikipedia.org/wiki/Revision_control [2]http://en.wikipedia.org/wiki/Database_administrator [3]http://www.cakephp.org/ [4]http://github.com/joelmoss/cakephp-db-migrations/tree/master [5]http://code.google.com/p/ruckusing/ [6]http://api.rubyonrails.org/classes/ActiveRecord/Migration.html [7]http://www.akelos.org/wiki/creating-and-running-migrations [8]http://bakery.cakephp.org/articles/view/cakephp-yaml-migrations-and-fixtures-withour-pear
