/**
* Reverts a series of SQL patches by looking up their down_sql code and executing it.
*/
public function rollback()
{
$this->logger->log('Database rollback: ' . implode(', ', $this->sql_revert_patches), LOG_DEBUG);
if (!count($this->sql_revert_patches)) {
return;
}
foreach ($this->sql_revert_patches as $patch_name) {
// get the code to revert the patch
$result = $this->driver->query("\n SELECT id, down_sql\n FROM db_patches\n WHERE patch_name = '" . $this->driver->escape($patch_name) . "'\n ORDER BY applied_at DESC, id DESC;\n ");
if (!($patch_info = $this->driver->fetchAssoc($result))) {
return;
}
if ($patch_info['down_sql'] != '') {
// mark the patch as being reverted
$this->driver->query("\n UPDATE db_patches\n SET reverted_at = '" . $this->driver->escape($this->timestamp) . "'\n WHERE patch_timestamp = '" . $this->driver->escape($patch_name) . "';\n ");
// revert the patch
$this->driver->startTransaction();
$this->driver->multiQuery($patch_info['down_sql']);
if (false === $result) {
throw new DatabaseException('Error reverting patch ' . $patch_name . ': ' . $this->driver->getLastError(), 1);
}
$this->driver->doCommit();
}
// remove the patch from the db_patches table
$this->driver->query("\n DELETE FROM db_patches\n WHERE id = " . $this->driver->escape($patch_info['id']) . ";\n ");
$this->logger->log("Patch '{$patch_name}' reverted.");
}
}
/**
* Applies a series of SQL patches to the database and registers them in the db_patches table.
*
* @param bool $register_only Only register the patches as done, don't run their code
* @throws \LemonWeb\Deployer\Exceptions\DatabaseException
*/
public function update($register_only = false)
{
$this->logger->log('Database update: ' . implode(', ', array_keys($this->sql_patch_objects)), LOG_DEBUG, true);
if (!count($this->sql_patch_objects)) {
return;
}
foreach ($this->sql_patch_objects as $filename => $sql_patch_object) {
$patch_name = DatabaseHelper::getClassnameFromFilepath($filename);
$patch_timestamp = DatabaseHelper::convertFilenameToDateTime($filename);
// Register the patch in the db_patches table (except for the db_patches table patch itself, that wouldn't be possible yet).
// Add the revert (down) code to the record so the update can be reverted when the file doesn't exist, which can happen when code is rolled back.
// Also add the patch' dependencies list so depending patches won't be reverted before it is reverted first.
if ('19700101000000' != $patch_timestamp) {
$this->driver->query("\n INSERT INTO db_patches (\n patch_name,\n patch_timestamp,\n down_sql,\n dependencies\n )\n VALUES (\n '" . $this->driver->escape($patch_name) . "',\n '" . $this->driver->escape($patch_timestamp) . "',\n " . (trim($sql_patch_object->down()) != '' ? "'" . $this->driver->escape(trim($sql_patch_object->down())) . "'" : 'null') . ",\n " . (count($sql_patch_object->getDependencies()) > 0 ? "'" . $this->driver->escape(implode("\n", $sql_patch_object->getDependencies())) . "'" : 'null') . "\n );\n ");
}
// apply the patch
if (!$register_only) {
$this->driver->startTransaction();
$result = $this->driver->multiQuery($sql_patch_object->up());
if (false === $result) {
throw new DatabaseException('Error applying patch ' . $patch_name . ': ' . $this->driver->getLastError(), 1);
} else {
$this->driver->closeResult($result);
}
$this->driver->doCommit();
}
// if there were no errors, mark the patch as applied
if ('19700101000000' != $patch_timestamp) {
$this->driver->query("\n UPDATE db_patches\n SET applied_at = '" . $this->driver->escape($this->timestamp) . "'\n WHERE patch_name = '" . $this->driver->escape($patch_name) . "';\n ");
if ($register_only) {
$this->logger->log("Patch '{$filename}' registered.");
} else {
$this->logger->log("Patch '{$filename}' succeeded.");
}
} else {
// the db_patches patch has no record set, insert it now
$this->driver->query("\n INSERT INTO db_patches (\n patch_name,\n patch_timestamp,\n applied_at\n )\n VALUES (\n '" . $this->driver->escape($patch_name) . "',\n '" . $this->driver->escape($patch_timestamp) . "',\n '" . $this->driver->escape($this->timestamp) . "'\n );\n ");
$this->logger->log("Patch '{$filename}' succeeded.");
}
}
}
