$query = "SELECT * INTO {$table} FROM {$FANNIE_TRANS_DB}.dbo.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; } $chk1 = $sql->query($query, $FANNIE_ARCHIVE_DB); $chk2 = true; if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) { // mysql doesn't create & populate in one step $chk2 = $sql->query("INSERT INTO {$table} SELECT * FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"); } if ($chk1 === false || $chk2 === false) { echo cron_msg("Error creating new archive {$table}"); } else { echo cron_msg("Created new table {$table} and archived dtransactions"); } if ($UPDATED_DLOG_SCHEMA) { $model = new DTransactionsModel($sql); $model->normalizeLog('dlog' . $str, $table, BasicModel::NORMALIZE_MODE_APPLY); } else { createViews($dstr, $sql); } } else { $query = "INSERT INTO {$table} SELECT * FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; if ($FANNIE_SERVER_DBMS == 'MSSQL') { $query = "INSERT INTO {$table} SELECT * FROM {$FANNIE_TRANS_DB}.dbo.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; } $chk = $sql->query($query, $FANNIE_ARCHIVE_DB); if ($chk === false) { echo cron_msg("Error archiving dtransactions"); } else { echo cron_msg("Success archiving dtransactions"); } }
public function run() { global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB, $FANNIE_ARCHIVE_METHOD; $sql = FannieDB::get($FANNIE_TRANS_DB); $sql->throwOnFailure(true); $today = date('Y-m-d 00:00:00'); set_time_limit(0); $cols = $sql->table_definition('dtransactions'); if (isset($cols['date_id'])) { $sql->query("UPDATE dtransactions SET date_id=DATE_FORMAT(datetime,'%Y%m%d')"); } /* Find date(s) in dtransactions */ $dates = array(); try { $datesP = $sql->prepare(' SELECT YEAR(datetime) AS year, MONTH(datetime) as month, DAY(datetime) as day FROM dtransactions WHERE datetime < ? GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime) ORDER BY YEAR(datetime), MONTH(datetime), DAY(datetime) '); $datesR = $sql->execute($datesP, array($today)); while ($datesW = $sql->fetch_row($datesR)) { $dates[] = sprintf('%d-%02d-%02d', $datesW['year'], $datesW['month'], $datesW['day']); } } catch (Exception $ex) { /** @severity: this query should not fail unless the database server is down or inaccessible */ $this->cronMsg('Failed to find dates in dtransactions. Details: ' . $ex->getMessage(), FannieLogger::ALERT); } if (count($dates) == 0) { $this->cronMsg('No data to rotate', FannieLogger::INFO); return true; } /* Load dtransactions into the archive, trim to 90 days */ $chkP = $sql->prepare("INSERT INTO transarchive \n SELECT * \n FROM dtransactions \n WHERE " . $sql->datediff('datetime', '?') . '= 0'); foreach ($dates as $date) { try { $chk1 = $sql->execute($chkP, array($date)); } catch (Exception $ex) { /** @severity: generally should not fail, but this isn't as important as the long-term archive table(s) */ $this->cronMsg('Failed to archive ' . $date . ' in transarchive (last quarter table) Details: ' . $ex->getMessage(), FannieLogger::ERROR); } } try { $chk2 = $sql->query("DELETE FROM transarchive WHERE " . $sql->datediff($sql->now(), 'datetime') . " > 92"); } catch (Exception $ex) { /** @severity: should not happen, but impact is limited. performance issues may eventually crop up if the table gets very large. */ $this->cronMsg('Failed to trim transarchive (last quarter table) Details: ' . $ex->getMessage(), FannieLogger::ERROR); } /* reload all the small snapshot */ try { $chk1 = $sql->query("TRUNCATE TABLE dlog_15"); $chk2 = $sql->query("INSERT INTO dlog_15 \n SELECT * \n FROM dlog_90_view \n WHERE " . $sql->datediff($sql->now(), 'tdate') . " <= 15"); } catch (Exception $ex) { /** @severity: no long term impact but may lead to reporting oddities */ $this->cronMsg('Failed to reload dlog_15. Details: ' . $ex->getMessage(), FannieLogger::ERROR); } $added_partition = false; $created_view = false; $sql = FannieDB::get($FANNIE_ARCHIVE_DB); $sql->throwOnFailure(true); /* get table definition in partitioning mode to have a list of existing partitions */ $bigArchive = false; if ($FANNIE_ARCHIVE_METHOD == "partitions") { $bigArchive = $sql->query('SHOW CREATE TABLE bigArchive'); $bigArchive = $sql->fetchRow($bigArchive); $bigArchive = $bigArchive['Create Table']; } foreach ($dates as $date) { /* figure out which monthly archive dtransactions data belongs in */ list($year, $month, $day) = explode('-', $date); $yyyymm = $year . $month; $table = 'transArchive' . $yyyymm; if ($FANNIE_ARCHIVE_METHOD == "partitions") { // we're just partitioning // create a partition if it doesn't exist $partition_name = "p" . date("Ym", strtotime($date)); if (strstr($bigArchive, 'PARTITION ' . $partition_name . ' VALUES') === false) { $ts = strtotime($date); $boundary = date("Y-m-d", mktime(0, 0, 0, date("n", $ts) + 1, 1, date("Y", $ts))); // new partition named pYYYYMM // ends on first day of next month $newQ = sprintf("ALTER TABLE bigArchive ADD PARTITION \n (PARTITION %s \n VALUES LESS THAN (TO_DAYS('%s'))\n )", $partition_name, $boundary); try { $newR = $sql->query($newQ); /* refresh table definition after adding partition */ $bigArchive = $sql->query('SHOW CREATE TABLE bigArchive'); $bigArchive = $sql->fetchRow($bigArchive); $bigArchive = $bigArchive['Create Table']; } catch (Exception $ex) { /** @severity lack of partitions will eventually cause performance problems in large data sets */ $this->cronMsg("Error creating new partition {$partition_name}. Details: " . $ex->getMessage(), FannieLogger::ERROR); } } // now just copy rows into the partitioned table $loadQ = "INSERT INTO bigArchive \n SELECT * \n FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; try { $loadR = $sql->query($loadQ); } catch (Exception $ex) { /** @severity: transaction data was not archived. absolutely needs to be addressed. */ $this->cronMsg('Failed to properly archive transaction data for ' . $date . ' Details: ' . $ex->getMessage(), FannieLogger::ALERT); } } else { if (!$sql->table_exists($table)) { $query = "CREATE TABLE {$table} LIKE {$FANNIE_TRANS_DB}.dtransactions"; if ($sql->dbms_name() == 'mssql') { $query = "SELECT * INTO {$table} FROM {$FANNIE_TRANS_DB}.dbo.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; } try { $chk1 = $sql->query($query, $FANNIE_ARCHIVE_DB); if ($sql->dbms_name() != 'mssql') { // mysql doesn't create & populate in one step $chk2 = $sql->query("INSERT INTO {$table} \n SELECT * \n FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"); } if (!$created_view) { $model = new DTransactionsModel($sql); $model->dlogMode(true); $model->normalizeLog('dlog' . $yyyymm, $table, BasicModel::NORMALIZE_MODE_APPLY); $created_view = true; } } catch (Exception $ex) { /** @severity: missing monthly table will prevent proper transaction archiving. absolutely needs to be addressed. */ $this->cronMsg("Error creating new archive structure {$table} Details: " . $ex->getMessage(), FannieLogger::ALERT); } } else { $query = "INSERT INTO " . $table . "\n SELECT * FROM " . $FANNIE_TRANS_DB . $sql->sep() . "dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0"; try { $chk = $sql->query($query, $FANNIE_ARCHIVE_DB); } catch (Exception $ex) { /** @severity: transaction data was not archived. absolutely needs to be addressed. */ $this->cronMsg('Failed to properly archive transaction data for ' . $date . ' Details: ' . $ex->getMessage(), FannieLogger::ALERT); } } } } // for loop on dates in dtransactions /* drop dtransactions data DO NOT TRUNCATE; that resets AUTO_INCREMENT column */ $sql = FannieDB::get($FANNIE_TRANS_DB); $sql->throwOnFailure(true); try { $chk = $sql->query("DELETE FROM dtransactions WHERE datetime < '{$today}'"); } catch (Exception $ex) { /** @severity: should not fail. could eventually create duplicate archive records if this is failing and queries above are not */ $this->cronMsg("Error clearing dtransactions. Details: " . $ex->getMessage(), FannieLogger::ALERT); } }