Version control database structure

This article is How To, which will help you to easily migrate between PHP versions of your PHP applications using Phing and dbdeploy .

Phing installation

The author of the article admits that he always uses Beta and RC releases of Phing and if you use the material of the article for compatibility do the same. The easiest way to install phing is using PEAR . You can do this on any system with three commands:

 
 > pear channel-discover pear.phing.info
 > pear config-set preferred_state beta
 > pear install phing / phing

Application structure example

In the example, a simple application with the following structure will be considered:

 example /
  | - db / ← sql files for database management are stored here
  |  `- deltas /
  | - deploy / ← scripts for migration are stored here
  |  `- scripts /
  | - library / ← here is a developed application
  `- public / ← here indicates the directive DOCUMENT ROOT 

Collector scripts

In this section, we will look at how to develop collector scripts that will initialize the database migration. First we need to create a simple configuration (ini) file, comments to which will be unnecessary. Place it here: deploy / build.properties.

')

 # Property files contain key / value pairs
 # key = value
 
 # This dir must contain the local application
 build.dir = .. /
 
 # Credentials for the database migrations
 db.host = localhost
 db.user = user
 db.pass = password
 db.name = example
 
 # paths to programs
 progs.mysql = / usr / bin / mysql

The second file we need to create is deploy / build.xml. From him Phing learns what we want from him. The author has provided an example with some comments, but if you have more detailed questions, refer to the Phing documentation .

<? xml version ="1.0" ? >

< project name ="PurpleMonkey" basedir ="." default ="build" >



<!-- Sets the DSTAMP, TSTAMP and TODAY properties -->

< tstamp />



<!-- Load our configuration -->

< property file ="./build.properties" />



<!-- create our migration task -->

< target name ="migrate" description ="Database Migrations" >



<!-- load the dbdeploy task -->

< taskdef

name ="dbdeploy"

classname ="phing.tasks.ext.dbdeploy.DbDeployTask" />



<!--

these two filenames will contain the generated SQL

to do the deploy and roll it back

-->

< property

name ="build.dbdeploy.deployfile"

value ="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" />

< property

name ="build.dbdeploy.undofile"

value ="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" />



<!-- generate the deployment scripts -->

< dbdeploy

url ="mysql:host=${db.host};dbname=${db.name}"

userid ="${db.user}"

password ="${db.pass}"

dir ="${build.dir}/db/deltas"

outputfile ="${build.dir}/${build.dbdeploy.deployfile}"

undooutputfile ="${build.dir}/${build.dbdeploy.undofile}" />



<!--

Execute the SQL

Use mysql command line to avoid trouble with large files

or many statements and PDO

-->

< exec

command ="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} < ${build.dbdeploy.deployfile}"

dir ="${build.dir}"

checkreturn ="true" />

</ target >

</ project >



* This source code was highlighted with Source Code Highlighter .

In principle, this is all that needs to be done, it remains to create the database itself.

Work with dbdeploy

Since we, in principle, have not yet created our base, so instead of doing this in the traditional way, we will use migrations to create the initial structure. We still have no idea what our application will do, but since many examples use the concept of blogs, then why shouldn't we start with the same ... we will start with one “post” table containing 3 fields:

Field	Type	Comment
title	VARCHAR (255)	The title of our post
time_created	DATETIME	The time we created our post
content	MEDIUMTEXT	The content of our post

Dbdeploy's work is based on creating numbered differences files, each file contains SQL to apply changes and roll back them, the base file looks like this:

--//

-- Run SQL to do the changes

--//@UNDO

-- RUN SQL to undo the changes

--//



* This source code was highlighted with Source Code Highlighter .

We create our original structure, so put a dump in db / deltas / 1-create_initial_schema.sql

--//



CREATE TABLE `post` (

`title` VARCHAR (255),

`time_created` DATETIME,

`content` MEDIUMTEXT

);



--//@UNDO



DROP TABLE `post`;



--//



* This source code was highlighted with Source Code Highlighter .

Migrations

We are one step away from our first migration. To track the current version of the dbdeploy database requires a table in the database to store service information. This is the only time we need to directly interact with the mysql client directly.

 
 > mysql -hlocalhost -uroot -ppassword example
 > CREATE TABLE changelog (
   change_number BIGINT NOT NULL,
   delta_set VARCHAR (10) NOT NULL,
   start_dt TIMESTAMP NOT NULL,
   complete_dt TIMESTAMP NULL,
   applied_by VARCHAR (100) NOT NULL,
   description VARCHAR (500) NOT NULL
 );
 > ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set);

Now we are ready to start our first migration and create the initial structure for the application.

 
 > cd deploy
 > phing migrate

Now we have a table with posts in our database, but what about adding author information? We will need to create another table and foreign key to create another file for dbdeploy and name it db / deltas / 2-create_author_and_link_to_post.sql

--//



CREATE TABLE `author` (

`author_id` INT (10) unsigned auto_increment,

`name` VARCHAR (255),

PRIMARY KEY (`author_id`)

);



ALTER TABLE `post` ADD `author_id` INT (10) unsigned NULL ;



--//@UNDO



ALTER TABLE `post` DROP `author_id`;



DROP TABLE `author`;



--//



* This source code was highlighted with Source Code Highlighter .

Run the migration again.

 
 shell> cd deploy
 shell> phing migrate

Conclusion

That's all, now we know how you can easily and easily provide migration between versions of the database. If you do not want to paste the code to get a closer look, you can download the application archive .



There are many moments when it comes to controlling database versions, especially if you branch out and merge the code for your application, some of which are described in detail in the dbdeploy documentation.



This guide is incomplete and if you think there is something to add, please leave your comment below.





PS

The GNU Phing Is Not introductory article for a general introduction to Phing.