Database Migration

During the process of developing and maintaining a database-driven application, the structure of the database being used evolves just like the source code does.

For example, during the development of an application, a new table or tables may be found necessary; after the application is deployed to production, it may be discovered that an index should be created to improve the query performance, new fields need to be added; and so on.

Because a database structure change often requires some source code changes, ApPHP Framework supports the so-called database migration feature that allows you to keep track of database changes in terms of database migrations which are version-controlled together with the source code.

Currently ApPHP Framework supports only manually created migrations.


The following steps show how database migration can be used by a team during development:
  1. Developer 1 adds migration changes (e.g. creates a new table, changes a column definition, etc.).
  2. Developer 1 commits the new migration into the source control system (e.g. Git, Mercurial, etc.).
  3. Developer 2 updates his repository from the source control system and receives the new migration.
  4. Developer 2 applies the migration to his local development database, thereby synchronizing his database to reflect the changes that Developer 1 has made.

ApPHP Framework recommend to yse migrations by creating SQL files with following naming:


For standard Directy CMF (already supported):

  1. schema.mysql.sql
  2. schema.update.mysql.sql

For standard Directy CMF modules (already supported):

  1. install.mysql.sql
  2. uninstall.mysql.sql
  3. update.002.mysql.sql
  4. update.003.mysql.sql
  5. update.004.mysql.sql

Example of migration update file:

UPDATE `<DB_PREFIX>modules` SET `version` = '0.0.3', `updated_at` = '<CURRENT_DATETIME>' WHERE `code` = 'cms';


INSERT INTO `<DB_PREFIX>module_settings`
(`id`, `module_code`, `property_group`, `property_key`, `property_value`, `name`, `is_required`) VALUES
(NULL, 'cms', '', 'page_link_format', 'pages/view/id/ID', 'Page Link Format', 0);


ALTER TABLE `<DB_PREFIX>cms_pages` DROP `tag_title`;
ALTER TABLE `<DB_PREFIX>cms_pages` DROP `tag_keywords`;
ALTER TABLE `<DB_PREFIX>cms_pages` DROP `tag_description`;

ALTER TABLE `<DB_PREFIX>cms_page_translations` ADD `tag_title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER  `id`;
ALTER TABLE `<DB_PREFIX>cms_page_translations` ADD `tag_keywords` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER  `tag_title`;
ALTER TABLE `<DB_PREFIX>cms_page_translations` ADD `tag_description` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL AFTER  `tag_keywords`;

When you install Directy CMF module with migrations, the system will take the install.mysql.sql file and run it.

When you remove Directy CMF module with migrations, the system will take the uninstall.mysql.sql file and run it.

When you update Directy CMF module with migrations, the system will check current version of installed module run migrations that have version number greater, than current version of installed module.

For example, the current version of installed CMS module is 0.0.2, you uploaded new version of this module to your server and rum update procedure. The system will take all migrations, that have version number greater, than 0.0.2 and run all them consistently: 0.0.3, 0.0.4 etc.

Remember, that your module XML description file must have version information and description of all migration files in following format.

<version>0.0.4</version>
...
<data>
    <install>install.mysql.sql</install>
    <update>
        <filename>update.002.mysql.sql</filename>
        <filename>update.003.mysql.sql</filename>
        <filename>update.004.mysql.sql</filename>
    </update>
    <uninstall>uninstall.mysql.sql</uninstall>
</data>