/**
* If it is proven to be a valid SQL Statement worthy of logging, the persistQuery() function will
* write the statement to file and log it
* @param string $query The SQL Statement that will be saved to file and CDI log
*/
public static function persistQuery($query) {
try {
$ts = time();
if(self::$lastEntryTS != $ts) {
self::$lastEntryTS = $ts;
self::$lastEntryOrder = 0;
} else {
self::$lastEntryOrder++;
}
$id = $ts . '-' . self::$lastEntryOrder;
$hash = md5($id . $query);
$date = date('Y-m-d H:i:s', $ts);
try{
//We are only logging this to file because we do not execute CDI queries on the MASTER instance
//The database table `tbl_cdi_log` is removed from the database on the MASTER instance.
//It is the repsonsibility of the user to ensure that they only have a single MASTER instance
//and that they protect the integrity of the Symphony database
$entries = CdiLogQuery::getCdiLogEntries();
$entries[$id] = array(0 => $ts, 1 => self::$lastEntryOrder, 2 => $hash, 3 => $query);
file_put_contents(CDI_FILE, json_encode($entries));
return true;
} catch(Exception $e) {
//TODO: think of some smart way of dealing with errors, perhaps through the preference screen or a CDI Status content page?
CdiLogQuery::rollback($hash,$ts,$order);
return false;
}
} catch(Exception $e) {
//TODO: think of some smart way of dealing with errors, perhaps through the preference screen or a CDI Status content page?
return false;
}
}
/**
* Imports the SQL statements from the db_sync.sql file, or from the file provided through file upload.
* This function can only be called from the Symphony backend, the CDI extension must be enabled and running in Database Synchronisation mode.
*/
public static function import() {
// We should not be processing any queries when the extension is disabled or when it is in 'Continuous Database Integration' mode
if((!class_exists('Administration')) || !CdiUtil::isEnabled() || !CdiUtil::isCdiDBSyncSlave()) {
throw new Exception("You can only import the Database Synchroniser file from the Preferences page. The CDI extension must be enabled and should be a 'Slave' instance in 'Database Synchronisation' mode.");
}
// Prevent the CdiLogQuery::log() from persisting queries that are executed by CDI itself
// This should not be possible anyway because we can only import in "Slave" mode, but just to be sure!
CdiLogQuery::isUpdating(true);
// Handle file upload
$syncFile = CDI_DB_SYNC_FILE;
if(!empty($_FILES['cdi_import_file']['tmp_name'])) {
$syncFile = $_FILES['cdi_import_file']['tmp_name'];
}
//Execute the queries from file
try {
if(file_exists($syncFile)) {
$contents = file_get_contents($syncFile);
$queries = explode(';',$contents);
foreach($queries as $query) {
$query = trim($query);
// ommit comments and empty statements
if(!preg_match('/^--/i', $query) && !$query=='') {
Symphony::Database()->query($query);
}
}
if(isset($_POST['settings']['cdi']['deleteSyncFile'])) {
unlink($syncFile);
}
}
} catch (Exception $e) {
// Re-enable CdiLogQuery::log() to persist queries
CdiLogQuery::isUpdating(false);
throw $e;
}
// Save the last update date to configuration
Symphony::Configuration()->set('last-update', time(), 'cdi');
Symphony::Configuration()->write();
// Re-enable CdiLogQuery::log() to persist queries
CdiLogQuery::isUpdating(false);
}
public static function isUpdating($status) {
self::$isUpdating = $status;
}
public function postQueryExecution($context) {
CdiLogQuery::log($context['query']);
}
public static function appendCdiMasterQueries() {
$div = new XMLElement('div', NULL,array('style'=>'margin-bottom: 1.5em;', 'class' => 'cdiLastQueries'));
$div->appendChild(new XMLElement('h3','The last 5 queries logged',array('style' => 'margin-bottom: 5px;')));
$table = new XMLElement('table', NULL, array('cellpadding' => '0', 'cellspacing' => '0', 'border' => '0'));
$cdiLogEntries = CdiLogQuery::getCdiLogEntries();
if(count($cdiLogEntries) > 0) {
rsort($cdiLogEntries);
foreach($cdiLogEntries as $entry) {
if($entryCount == 5) { break; }
$tr = new XMLElement('tr',null);
$tr->appendChild(new XMLElement('td',date('d-m-Y h:m:s', $entry[0]),array('width' => '150')));
$tr->appendChild(new XMLElement('td',htmlspecialchars($entry[3])));
$table->appendChild($tr);
$entryCount++;
}
}
$tr = new XMLElement('tr',null,array('class' => 'cdiNoLastQueriesCell'));
if($entryCount != 0) { $tr->setAttribute('style','display:none;'); }
$tr->appendChild(new XMLElement('td','There are no entries in the CDI log'));
$table->appendChild($tr);
$div->appendChild($table);
return $div;
}
public static function restore() {
// We should only backup the database when the extension is enabled and version 1.09 of the Dump_DB extension is installed.
if((!class_exists('Administration')) || !CdiUtil::isEnabled()) {
throw new Exception("You can only restore the Symphony database from the Preferences page");
}
if(!CdiUtil::hasDumpDBInstalled()) {
throw new Exception('No valid version of <a href="http://symphony-cms.com/download/extensions/view/40986/">Dump DB</a> found. Please make sure it is installed.');
} else {
require_once(EXTENSIONS . '/dump_db/lib/class.mysqlrestore.php');
// Prevent the CdiLogQuery::log() from persisting queries that are executed by CDI itself
CdiLogQuery::isUpdating(true);
// COPIED FROM Dump_DB version 1.09
// Adjust to only support FULL database dump
$restore = new MySQLRestore(Symphony::Database());
$filename = CDI_BACKUP_ROOT . '/' . $_POST["ref"];
if(isset($_FILES['dumpdb_restore_file'])) {
$filename = self::getFileName('manual');
rename($_FILES['dumpdb_restore_file']['tmp_name'],$filename);
}
if(file_exists($filename)) {
$data = file_get_contents($filename);
$data = str_replace('tbl_', Symphony::Configuration()->get('tbl_prefix', 'database'), $data);
$restore->import($data);
} else {
throw new Exception("The provided restore file '" . $filename . "' could not be found.");
}
// Re-enable CdiLogQuery::log() to persist queries
CdiLogQuery::isUpdating(false);
}
}
/**
* The executeQueries() function will try to execute all SQL statements that are available in the manifest folder.
* It will check the CDI log to see if the statement has already been executed based on the MD5 hash.
* This function can only be called by SLAVE instances
*/
public static function update() {
// We should not be processing any queries when the extension is disabled or when we are the Master instance
// Check also exists on content page, but just to be sure!
if((!class_exists('Administration')) || !CdiUtil::isEnabled() || !CdiUtil::isCdiSlave()) {
echo "WARNING: CDI is disabled or you are running the queryies on the Master instance. No queries have been executed.";
return;
}
// Prevent the CdiLogQuery::log() from persisting queries that are executed by CDI itself
// Technically this should not be possible because it will not log queries on a SLAVE instance
// and you can only run the executeQueries when in SLAVE mode. This is just to be sure.
CdiLogQuery::isUpdating(true);
// Switch to maintenance mode
if(Symphony::Configuration()->get('maintenance-enabled', 'cdi') == 'yes') {
Symphony::Configuration()->set('enabled', 'yes', 'maintenance_mode');
Symphony::Configuration()->write();
}
// Implement automatic backup before processing structural changes
if(Symphony::Configuration()->get('backup-enabled', 'cdi') == 'yes') {
try {
if(CdiUtil::hasDumpDBInstalled()) {
require_once(EXTENSIONS . '/cdi/lib/class.cdidumpdb.php');
$currentBackup = CdiDumpDB::backup("automatic");
} else {
throw new Exception('You can only enable automatic backup files when the "Dump DB" extension (version 1.10 or higher) is installed and enabled.');
}
}catch (Exception $e) {
echo "ERROR: " . $e->getMessage() , ". Failed to backup database before update, aborting.";
die();
}
}
try {
$skipped = 0;
$executed = 0;
$entries = CdiLogQuery::getCdiLogEntries();
foreach($entries as $entry) {
$ts = $entry[0];
$order = $entry[1];
$hash = $entry[2];
$query = $entry[3];
$date = date('Y-m-d H:i:s', $ts);
// Replace the table prefix in the query
// Rename the generic table prefix to the prefix used by this instance
$tbl_prefix = Symphony::Configuration()->get('tbl_prefix', 'database');
$query = str_replace('tbl_', $tbl_prefix, $query);
try {
// Look for available CDI log entries based on the provided MD5 hash
$cdiLogEntry = Symphony::Database()->fetchRow(0,"SELECT * FROM tbl_cdi_log WHERE `query_hash` LIKE '" . $hash . "'");
if(empty($cdiLogEntry)) {
// The query has not been found in the log, thus it has not been executed
// So let's execute the query and add it to the log!
Symphony::Database()->query("INSERT INTO `tbl_cdi_log` (`query_hash`,`author`,`url`,`date`,`order`)
VALUES ('" . $hash . "','" . CdiUtil::getAuthor() . "','" . CdiUtil::getURL() . "','" . $date . "'," . $order . ")");
Symphony::Database()->query($query);
$executed++;
} else {
// The query has already been executed, let's do nothing;
$skipped++;
}
} catch (Exception $e) {
//TODO: think of some smart way of dealing with errors, perhaps through the preference screen or a CDI Status content page?
//Due to the error we need to perform a rollback to allow this query to be executed at a later stage.
CdiLogQuery::rollback($hash,$ts,$order);
// Implement automatic restore on error
if((Symphony::Configuration()->get('backup-enabled', 'cdi') == 'yes') &&
(Symphony::Configuration()->get('restore-enabled', 'cdi') == 'yes')) {
try {
CdiDumpDB::restore($currentBackup);
}catch (Exception $r) {
echo "ERROR: " . $r->getMessage() , ". Failed to restore latest database backup. This instance needs immediate attention!";
die();
}
// Restore was succesful, at least we can jump out of maintenance mode
if(Symphony::Configuration()->get('maintenance-enabled', 'cdi') == 'yes') {
Symphony::Configuration()->set('enabled', 'no', 'maintenance_mode');
Symphony::Configuration()->write();
}
echo "ERROR: " . $e->getMessage() , ". Rollback & Restore have been executed.";
} else {
if(Symphony::Configuration()->get('maintenance-enabled', 'cdi') == 'yes') {
echo "ERROR: " . $e->getMessage() , ". Rollback has been executed. This instance is now in maintenance mode and needs immediate attention!";
} else {
echo "ERROR: " . $e->getMessage() , ". Rollback has been executed.";
}
}
// Stop processing
die();
}
}
echo "OK: " . $executed . " queries executed, " . $skipped . " skipped.";
} catch (Exception $e) {
//TODO: think of some smart way of dealing with errors, perhaps through the preference screen or a CDI Status content page?
echo "ERROR: " . $e->getMessage();
}
// Save the last update date to configuration
Symphony::Configuration()->set('last-update', time(), 'cdi');
// No more maintenance mode
if(Symphony::Configuration()->get('maintenance-enabled', 'cdi') == 'yes') {
Symphony::Configuration()->set('enabled', 'no', 'maintenance_mode');
}
Symphony::Configuration()->write();
// Re-enable CdiLogQuery::log() to persist queries
CdiLogQuery::isUpdating(false);
}
