OOWP For Developers Part 5: Database Migrations

I want to show how Bonfire allows for easy upgrades and installation of additional modules via the Database Migrations feature.

WHAT ARE MIGRATIONS?

Migrations are organized ways for the Bonfire library to make database upgrades and downgrades without requiring the site administrator to execute SQL scripts within the PhPMyAdmin tool. Instead, there is a simply migration page, which automates the process.

They also help site owners and administrators to safely perform site upgrades without posing serious risks to data loss. Should the changes fail and need to be rolled back, downgrading the migration removes only the exact changes previously made and returns the database to it’s pre-upgrade state.

When a web tool or application makes major changes and requires and upgrade, there are very often changes that need to be made to the database. These can include adding or removing tables, columns in a table or rows of data. This can many times be a cumbersome process especially if the end user will be required to run SQL scripts to make changes to the database.

In the past, this might require providing a SQL text file with SQL commands and the site admin to log in to their PhpMyAdmin tool, a visual web based database administrator tool, and running the SQL scripts.

With Bonfire, there is a better way, Database Migrations. These are scripts written by module authors which are run right within the Bonfire admon dashboard interface.

RUN MIGRATIONS

Running a database migration is done by

  • Clicking the Developer button in the top nav
  • Mouse over Database Tools and click Migrations.
  • On the Migrations page, click the appropriate tab. “Core” manages migrations for the Bonfire dashboard itself. Individual module migrations can be selected on the “Modules” tab.
  • Select the file matching the number in the “Available” column from the drop down for the first module and click Run Migration.
  • Repeat steps 3 and 4 for all modules requiring migration.

ANATOMY OF A MIGRATION FILE

A migration file is a simple PHP class that extends the base Migration class and has two functions:

  • Up – handles execution of scripts when upgrading to a particular migration. For installation migrations, this usually contains functions to add tables and define their structure, INSERT initial data into tables and set basic module permission. For upgrades, this could also contain ALTER table commands and additional data tweaks.
  • Down – executes when downgrading a migration and will usually include DELETE calls and drop table commands.

The following example adds a table with two columns and sets the ID column as the primary key. It then adds a single row of example data.

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Migration_Adding_mailtype_setting extends Migration {

	public function up()
	{
		$prefix = $this->db->dbprefix;

		$default_settings = "
			INSERT INTO `{$prefix}settings` (`name`, `module`, `value`) VALUES
			 ('mailtype', 'email', 'text');
		";

		if ($this->db->query($default_settings))
		{
			return TRUE;
		}
	}

	//--------------------------------------------------------------------

	public function down()
	{
		$prefix = $this->db->dbprefix;

		$default_settings = "
			DELETE FROM `{$prefix}settings` WHERE `name` = 'mailtype';
		";

		$this->db->query($default_settings);
	}

	//--------------------------------------------------------------------

}

Bonfire uses the CodeIgniter DBForge object to handle table creation and destruction. Permissions and data inserts are handled via SQL commands and the standard CodeIgninter Database Class.

As you can see, keeping site admins up to data with migrations is an easy way.

You can find out more information about Migrations in either the Bonfire Help GuidesCodeIgniter User Guide or CodeIgniter Wiki.

Download the OOWP Today!
Download the latest stable release version from the official OOWP forums page. All the documentation you need is available in the installation and setup guide.

Contribute to the development and help the OOWP grow
The OOWP is a 100% free and open source project. The source code is publicly available on GitHub.com. If you want to contribute to the development, simply head over to my official Github page, fork the related projects, hack the code and send pull request with your updates. It’s that simple.

If you’ve built an OOWP module let me know and I’ll add it to the built on the OOWP list.

Want to help test?
Testing assures everything works as expected and that everyone gets the best fantasy experience possible. Simply download and test the site and log issues on the official Github issues pages. Each portion of the site has its own page and issues list so be sure to log the issue in the appropriate module portion of the site.

Donate
While the OOWP is free to download and use, we do very much appreciate any donations made towards its development.

Share

About the Author

By day, Jeff Fox works as a senior software engineer at X Plus One and has 15 years of professional experience in the world of Web Development, Flash and design. By night, he is a husband and father of three amazing children, open source enthusiast, published comic book author, amateur writer and trained musician. Jeff makes him home with his family in Southern Connecticut.