This is a Zend_Application_Resource that I wrote to update the schema automatically when changes are made in a deploy.
<?php
/**
* Makes sure the current schema version matches what we're expecting for this
* particular version of the application.
*
* The version of the database is compared against the value configured in
* application.ini. SQL scripts corresponding to versions between the database
* version's and the current local SQL scripts are run against the database to
* get things up to date.
*
* @copyright 2011 Case Western Reserve University, College of Arts and Sciences
* @author Billy O'Neal III ([email protected])
*/
class Cas_Application_Resource_Schema extends Zend_Application_Resource_ResourceAbstract
{
/**
* Creates an array of filepaths corresponding to the scripts that need
* to run.
*
* @param int $from
* @param int $to
* @return array
*/
private function GetScriptsToRun($from, $to)
{
$application = APPLICATION_PATH . '/configs/sql/';
$result = array();
for($cur = $from + 1; $cur <= $to; $cur++)
{
$result[] = "{$application}{$cur}.sql";
}
return $result;
}
/**
* Returns the version the application is locally configured to expect.
*
* @return int
*/
private function GetLocalVersion()
{
$options = $this->getOptions();
$version = (int)$options['version'];
return $version;
}
/**
* Returns the version the database thinks it is.
*
* @return int
*/
private function GetDbVersion()
{
$adapter = Zend_Db_Table::getDefaultAdapter();
$metadataTable = new Cas_Model_Table_Metadata;
$verQuery = $metadataTable->select()->from($metadataTable, array('Value'));
$verQuery->where("{$adapter->quoteIdentifier('Key')} = ?", 'Version');
$dbVersion = $adapter->fetchOne($verQuery);
return (int)$dbVersion;
}
/**
* Runs the specified filepath's file contents as a SQL script.
*
* @param string $scriptPath
*/
private function RunSqlScript($scriptPath)
{
$adapter = Zend_Db_Table::getDefaultAdapter();
$contents = file_get_contents($scriptPath);
$adapter->query($contents);
}
/**
* Updates the version number in the database to match the version
* specified in the local configuration file.
*/
private function UpdateVersion()
{
$metadataTable = new Cas_Model_Table_Metadata;
$metadataTable->delete(array("{$metadataTable->getAdapter()->quoteIdentifier('Key')} = ?" => 'Version'));
$metadataTable->insert(array('Version' => $this->GetLocalVersion()));
}
/**
* Performs the actual schema checks.
*/
public function init()
{
//We depend on the database being connected.
$this->getBootstrap()->bootstrap('db');
$local = $this->GetLocalVersion();
$remote = $this->GetDbVersion();
if ($local < $remote)
{
throw new Exception('Database version is newer than local version.');
}
else if ($remote < $local)
{
$scripts = self::GetScriptsToRun($remote, $local);
foreach($scripts as $script)
{
$this->RunSqlScript($script);
}
$this->UpdateVersion();
}
}
}
This fails because the scripts (e.g. configs/sql/1.sql, configs/sql/2.sql, etc.) in question contain multiple SQL statements, separated by ;s, with a message something like:
Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';'
