/** * 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."); } } }