Cake DB Update Shell

Time for another useful CakePHP code example. This was created for our hosting system at work, and is used to manage DB updates on installs of the hosted websites. I have removed some code to simplify this example, mainly relating to our directory structure and to the ‘global’ database updates. Once you understand the code below, it should be relatively simple to extend it to work with other setups. We also have code to support ‘script’ updates that are held on a Task, but I’ve removed that as well.

First, you need a couple models to support this:

config/sql/schema.sql:

CREATE TABLE IF NOT EXISTS `schema_updates` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `file` VARCHAR(128) NOT NULL,
  `status` enum('error','success') NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `file` (`file`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `serialize` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `name` VARCHAR(100) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

models/schema_update.php:

class SchemaUpdate extends AppModel {
 
}

models/setting.php:

class Setting extends AppModel {
	public $cacheQueries = false;
 
	public function getValue($name, $onEmpty = '') {
		$result = $this->find(
			'first',
			array(
				'conditions' => array(
					'name' => $name
				)
			)
		);
 
		if (isset($result['Setting']['serialize']) && $result['Setting']['serialize']) {
			$result['Setting']['value'] = $this->unserialize($result['Setting']['value']);
		}
 
		return isset($result['Setting']['value']) ? $result['Setting']['value'] : $onEmpty;
	}
 
	public function setValue($name, $value) {
		$record = array(
			'Setting' => array(
				'name' => $name,
				'value' => $value
			)
		);
 
		$existing = $this->find(
			'first',
			array(
				'conditions' => array(
					'name' => $name
				)
			)
		);
 
		if (!empty($existing['Setting']['id'])) {
			$record['Setting']['id'] = $existing['Setting']['id'];
			$record['Setting']['serialize'] = $existing['Setting']['serialize'];
		}
 
		if (!empty($record['Setting']['serialize'])) {
			$record['Setting']['value'] = $this->serialize($record['Setting']['value']);
		}
 
		$this->create();
		return $this->save($record);
	}
 
	public function addSetting($name, $value, $serialize = false) {
		$record = array(
			'Setting' => array(
				'name' => $name,
				'value' => $value,
				'serialize' => $serialize
			)
		);
 
		$existing = $this->find(
			'first',
			array(
				'conditions' => array(
					'name' => $name
				)
			)
		);
 
		if (!empty($existing['Setting']['id'])) {
			$record['Setting']['id'] = $existing['Setting']['id'];
		}
 
		if ($serialize) {
			$record['Setting']['value'] = $this->serialize($record['Setting']['value']);
		}
 
		$this->create();
		return $this->save($record);
	}
 
	public function removeSetting($name) {
		$existing = $this->find(
			'first',
			array(
				'conditions' => array(
					'name' => $name
				)
			)
		);
 
		if(!empty($existing)) {
			return $this->delete($existing['Setting']['id']);
		}
 
		return true;
	}
 
	public function serialize($value) {
		return base64_encode(serialize($value));
	}
 
	public function unserialize($value) {
		return unserialize(rtrim(base64_decode($value)));
	}
}

vendors/shells/site.php:

<?php
class SiteShell extends Shell {
	protected $dbh = null;
 
	public function main() {
		$this->help();
	}
 
	public function help() {
		$this->out("
Please specify which task you want to run:
 
	db_install:	 Install the full schema
	db_update:	 Install any updates that are pending.
	db_update_retry: Retry any previously errored updates.
	db_reset:	 Reset the DB back to pristine condition.
 
	help:		 This message.
 
");
	}
 
	public function db_install() {
		$this->_db_connect();
 
		$res = mysql_query("SELECT value FROM {$this->dbconfig->default['prefix']}settings WHERE name = 'schema_version'", $this->dbh);
		if (!mysql_error($this->dbh)) {
			$this->out("Schema already installed.");
		} else {
			$status = $this->_process_sql(CONFIGS.'sql/schema.sql');
			if ($status != 0) {
				exit($status);
			}
		}
 
		$this->out("Database installed successfully. Now running 'db_update'.");
		$this->db_update();
	}
 
	public function db_update_retry() {
		$this->_db_connect();
 
		if(!isset($this->SchemaUpdate)) {
			$this->SchemaUpdate = ClassRegistry::init('SchemaUpdate');
			$this->SchemaUpdate->cacheQueries = false;
		}
		$this->SchemaUpdate->deleteAll(array('SchemaUpdate.status' => 'error'));
 
		$this->db_update();
	}
 
	public function db_update() {
		$this->_db_connect();
 
		if(!isset($this->Setting)) {
			$this->Setting = ClassRegistry::init('Setting');
			$this->Setting->cacheQueries = false;
		}
 
		if(!isset($this->SchemaUpdate)) {
			$this->SchemaUpdate = ClassRegistry::init('SchemaUpdate');
			$this->SchemaUpdate->cacheQueries = false;
		}
 
		$version = $this->Setting->field('value', array('Setting.name' => 'schema_version'));
		if (!$version) {
			$version = 1;
		}
		$version = sprintf('%04d', $version);
 
		$files = glob(CONFIGS.'sql/schema_updates/'.$version.'-*.sql');
 
		foreach ($files AS $file) {
			$record = array();
 
			$filenameInfo = pathinfo($file);
			$filename = $filenameInfo['basename'];
 
			if (0 == $this->SchemaUpdate->find('count', array('conditions' => array('SchemaUpdate.file' => $filename)))) {
				$this->out("Applying file '{$filename}' ... ", false);
				$status = $this->_process_sql($file);
 
				$record['SchemaUpdate']['plugin'] = 'core';
				$record['SchemaUpdate']['file'] = $filename;
				$record['SchemaUpdate']['status'] = $status == 0 ? 'success' : 'error';
 
				$this->SchemaUpdate->create();
				$this->SchemaUpdate->save($record);
 
				if ($status != 0) {
					$this->out("ERROR");
					exit($status);
				}
				$this->out("SUCCESS");
			} else {
				$record = $this->SchemaUpdate->find('first', array('conditions' => array('SchemaUpdate.file' => $filename)));
				if ($record['SchemaUpdate']['status'] == 'success') {
					$this->out("Update file '{$filename}' already applied.");
				} else {
					$this->out("Update file '{$filename}' previously errored.");
				}
			}
		}
 
		//force a table recache
		foreach (glob(TMP . "/cache/models/*") AS $file) {
			unlink($file);
		}
 
		$new_version = $this->Setting->field('value', array('Setting.name' => 'schema_version'));
		if (!$new_version) {
			$new_version = 1;
		}
 
		if ($new_version > $version) {
			$this->out("DB schema version update detected. Checking for more updates...");
			$this->db_update();
		}
 
		exit(0);
	}
 
	public function db_reset() {
		$this->_db_connect();
 
		$this->out("Deleting old tables...");
		$res = mysql_query('SHOW TABLES', $this->dbh);
		while ($row = mysql_fetch_array($res)) {
			$this->out('    '.$row[0]);
			mysql_query('DROP TABLE `'.$row[0].'`', $this->dbh);
		}
 
		//force a table recache
		foreach (glob(TMP . "/cache/models/*") AS $file) {
			unlink($file);
		}
 
		$this->db_install();
	}
 
	protected function _db_connect() {
		if ($this->dbh == null || !mysql_ping($this->dbh)) {
			require_once(CONFIGS.'database.php');
			$this->dbconfig = new DATABASE_CONFIG();
 
			$this->dbh = mysql_connect($this->dbconfig->default['host'], $this->dbconfig->default['login'], $this->dbconfig->default['password'], true);
			if (mysql_error($this->dbh)) {
				$this->out("Error connecting to default user DB. Check config, and try again.");
				exit(1);
			}
 
			mysql_select_db($this->dbconfig->default['database'], $this->dbh);
			if (mysql_error($this->dbh)) {
				$this->out("Unable to select default user DB. Check config, and try again.");
				exit(2);
			}
		}
	}
 
	protected function _process_sql($file) {
		$sql = file_get_contents($file);
		$sql = preg_replace('/^--.*[\r\n]*$/m', '', $sql);
		$sql = preg_replace('/\r\n/', "\n", $sql);
		$sql = explode(";\n", $sql);
 
		for($i = 0; $i < count($sql); ++$i) {
			$sql[$i] = trim($sql[$i]);
 
			if ($sql[$i]) {
				$sql[$i] = preg_replace('/__PREFIX__/', $this->dbconfig->default['prefix'] ? $this->dbconfig->default['prefix'] : '', $sql[$i]);
 
				mysql_query($sql[$i], $this->dbh);
				if (mysql_error($this->dbh)) {
					$this->out("ERROR: ".mysql_error($this->dbh));
					return 4;
				}
			}
		}
 
		return 0;
	}
 
	protected function _welcome() {
		Configure::write('debug', 1);
 
		$this->out();
		$this->out('Welcome to CakePHP v' . Configure::version() . ' Console');
		$this->hr();
		$this->out('App : '. $this->params['app']);
		$this->out('Path: '. $this->params['working']);
		$this->hr();
	}
}

With the above code, and a working database connection, you should have the basics of the system. To create a DB update, simply add a file in the ‘config/sql/schema_updates/’ directory, named ‘0001-0001.sql’, where the first group of 4 digits is the schema version (defaults to 1), and the second group of 4 digits is the update number within that schema. To increment to a new schema version, update the schema.sql to have a snapshot of that version in it, and make the last update for the previous version increment the ‘schema_version’ record in the Setting table (this change should be included in the schema.sql as well, although that’s not mandatory).

An example run of the shell looks like this:

mwalker@armitage ~ $ cake site db_update

Welcome to CakePHP v1.3.5 Console
---------------------------------------------------------------
App : core
Path: /home/user/app/
---------------------------------------------------------------
Update file '0002-0001.sql' already applied.
Update file '0002-0002.sql' already applied.
Update file '0002-0003.sql' already applied.
Update file '0002-0004.sql' already applied.
Update file '0002-0005.sql' already applied.
Update file '0002-0006.sql' already applied.
Update file '0002-0007.sql' already applied.
Update file '0002-0008.sql' already applied.
Update file '0002-0009.sql' already applied.
Applying file '0002-0010.sql' ... SUCCESS
Applying file '0002-0011.sql' ... SUCCESS

mwalker@armitage ~ $

I’m not sure if this would be possible to package as a plugin. I will investigate that possibility and may release it on github if I succeed. As always, feel free to post questions and/or comments.

%d bloggers like this: