function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$prefix = $GLOBALS['_MAX']['CONF']['table']['prefix'];
if ($this->oDbh->dbsyntax == 'pgsql') {
$oTable =& $this->oUpgrade->oDBUpgrader->oTable;
foreach ($oTable->aDefinition['tables'] as $tableName => $aTable) {
foreach ($aTable['fields'] as $fieldName => $aField) {
if (!empty($aField['autoincrement'])) {
// Check actual sequence name
$oldSequenceName = $this->getLinkedSequence($prefix . $tableName, $fieldName);
if ($oldSequenceName) {
$newSequenceName = OA_DB::getSequenceName($this->oDbh, $tableName, $fieldName);
if ($oldSequenceName != $newSequenceName) {
$this->logOnly("Non standard sequence name found: " . $oldSequenceName);
$qTable = $this->oDbh->quoteIdentifier($prefix . $tableName, true);
$qField = $this->oDbh->quoteIdentifier($fieldName, true);
$qOldSequence = $this->oDbh->quoteIdentifier($oldSequenceName, true);
$qNewSequence = $this->oDbh->quoteIdentifier($newSequenceName, true);
OA::disableErrorHandling();
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
if (PEAR::isError($result)) {
if ($result->getCode() == MDB2_ERROR_ALREADY_EXISTS) {
$result = $this->oDbh->exec("DROP SEQUENCE {$qNewSequence}");
if (PEAR::isError($result)) {
$this->logError("Could not drop existing sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qOldSequence} RENAME TO {$qNewSequence}");
}
}
if (PEAR::isError($result)) {
$this->logError("Could not rename {$oldSequenceName} to {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$result = $this->oDbh->exec("ALTER TABLE {$qTable} ALTER {$qField} SET DEFAULT nextval(" . $this->oDbh->quote($qNewSequence) . ")");
if (PEAR::isError($result)) {
$this->logError("Could not set column default to sequence {$newSequenceName}: " . $result->getUserInfo());
return false;
}
OA::enableErrorHandling();
$result = $oTable->resetSequenceByData($tableName, $fieldName);
if (PEAR::isError($result)) {
$this->logError("Could not reset sequence value for {$newSequenceName}: " . $result->getUserInfo());
return false;
}
$this->logOnly("Successfully renamed {$oldSequenceName} to {$newSequenceName}");
}
} else {
$this->logOnly("No sequence found for {$tableName}.{$fieldName}");
}
}
}
}
}
return true;
}
function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$prefix = $aConf['prefix'];
foreach (array('tblAppVar' => 'application_variable', 'tblAccounts' => 'accounts', 'tblAgency' => 'agency', 'tblClients' => 'clients', 'tblCampaigns' => 'campaigns', 'tblBanners' => 'banners', 'tblAcls' => 'acls', 'tblPrefs' => 'preferences', 'tblAccPrefs' => 'account_preference_assoc') as $k => $v) {
${$k} = $this->oDbh->quoteIdentifier($prefix . ($aConf[$v] ? $aConf[$v] : $v), true);
}
// Get admin account ID
$adminAccountId = (int) $this->oDbh->queryOne("SELECT value FROM {$tblAppVar} WHERE name = 'admin_account_id'");
if (PEAR::isError($adminAccountId)) {
$this->logError("No admin account ID");
return false;
}
// Get preference ID for timezone
$tzId = $this->oDbh->queryOne("SELECT preference_id FROM {$tblPrefs} WHERE preference_name = 'timezone'");
if (empty($tzId) || PEAR::isError($tzId)) {
// Upgrading from 2.4 maybe?
$tzId = 0;
$this->logOnly("No timezone preference available, using default server timezone");
$adminTz = date_default_timezone_get();
if (empty($adminTz)) {
// C'mon you should have set the timezone in your php.ini!
$this->logOnly("No default server timezone, using UTC");
$adminTz = 'UTC';
}
} else {
// Get admin timezone
$adminTz = $this->oDbh->queryOne("SELECT value FROM {$tblAccPrefs} WHERE preference_id = {$tzId} AND account_id = {$adminAccountId}");
if (empty($adminTz) || PEAR::isError($adminTz)) {
$this->logOnly("No admin timezone, using UTC");
$adminTz = 'UTC';
}
}
$joinList = "{$tblBanners} b JOIN\n {$tblCampaigns} ca USING (campaignid) JOIN\n {$tblClients} cl USING (clientid) JOIN\n {$tblAgency} a USING (agencyid) LEFT JOIN\n {$tblAccPrefs} p ON (p.account_id = a.account_id AND p.preference_id = {$tzId})";
$tzPart = "COALESCE(p.value, " . $this->oDbh->quote($adminTz) . ")";
$wherePart = "\n ac.bannerid = b.bannerid AND\n \tac.type LIKE 'deliveryLimitations:Time:%' AND\n \tac.data NOT LIKE '%@%'\n ";
if ($this->oDbh->dbsyntax == 'pgsql') {
$query = "\n UPDATE\n {$tblAcls} ac\n SET\n data = data || '@' || {$tzPart}\n FROM\n {$joinList}\n WHERE\n {$wherePart}\n ";
} else {
$query = "\n UPDATE\n {$tblAcls} ac,\n {$joinList}\n SET\n ac.data = CONCAT(ac.data, '@', {$tzPart})\n WHERE\n {$wherePart}\n ";
}
$ret = $this->oDbh->exec($query);
if (PEAR::isError($ret)) {
$this->logError($ret->getUserInfo());
return false;
}
// Rebuild ACLs
$this->oUpgrade->addPostUpgradeTask('Recompile_Acls');
// Also rebuild banner cache for OX-5184
$this->oUpgrade->addPostUpgradeTask('Rebuild_Banner_Cache');
$this->logOnly("Appended timezone information to {$ret} time based delivery limitations");
return true;
}
function outdateSession()
{
$sessionId = SESSIONID;
$prefix = OA_Dal::getTablePrefix();
$table = $this->dbh->quoteIdentifier($prefix . 'session');
$this->dbh->exec("UPDATE {$table} set lastused = '2005-01-01 01:00:00' WHERE sessionid = '{$sessionId}'");
}
public function clearTables()
{
if (!$this->clear_tables) {
return;
}
$this->db->exec('DELETE FROM ' . $this->table_users);
$this->db->exec('DELETE FROM ' . $this->table_files);
}
function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$prefix = $aConf['prefix'];
$tblBanners = $prefix . ($aConf['banners'] ? $aConf['banners'] : 'banners');
$tblAza = $prefix . ($aConf['ad_zone_assoc'] ? $aConf['ad_zone_assoc'] : 'ad_zone_assoc');
$query = "\n SELECT\n bannerid,\n 0 AS zoneid,\n " . MAX_AD_ZONE_LINK_DIRECT . " AS link_type\n FROM\n " . $this->oDbh->quoteIdentifier($tblBanners, true) . " b LEFT JOIN\n " . $this->oDbh->quoteIdentifier($tblAza, true) . " aza ON (b.bannerid = aza.ad_id AND aza.zone_id = 0)\n WHERE\n aza.ad_id IS NULL\n ";
$query = "\n INSERT INTO " . $this->oDbh->quoteIdentifier($tblAza, true) . "\n (ad_id, zone_id, link_type)\n " . $query;
$ret = $this->oDbh->exec($query);
//check for error
if (PEAR::isError($ret)) {
$this->logError($ret->getUserInfo());
return false;
}
$this->logOnly("Added {$ret} missing banner links to zone 0");
return true;
}
function execute($aParams)
{
// Insert the required application variable flag to ensure that
// when the maintenance script next runs, it will process all
// raw data into the new bucket format, so that any raw data not
// previously summarised will be accounted for
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$this->tblApplicationVariable = $aConf['prefix'] . ($aConf['application_variable'] ? $aConf['application_variable'] : 'application_variable');
$query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($this->tblApplicationVariable, true) . "\n (\n name,\n value\n )\n VALUES\n (\n 'mse_process_raw',\n '1'\n )";
$this->logOnly("Setting application variable flag to ensure ME processes old sytle raw data on next run...");
$rs = $this->oDbh->exec($query);
// Check for errors
if (PEAR::isError($rs)) {
$this->logError($rs->getUserInfo());
return false;
}
$this->logOnly("Application variable flag to ensure ME processes old sytle raw data on next run correctly set.");
return true;
}
function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
// Recompile the delivery limitations to update the compiled limitations as well
$this->oUpgrade->addPostUpgradeTask('Recompile_Acls');
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$this->prefix = $aConf['prefix'];
$this->tblPreferences = $aConf['prefix'] . ($aConf['preferences'] ? $aConf['preferences'] : 'preferences');
$this->tblAccountPreferenceAssoc = $aConf['prefix'] . ($aConf['account_preference_assoc'] ? $aConf['account_preference_assoc'] : 'account_preference_assoc');
$query = "SELECT preference_id\n FROM " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . "\n WHERE preference_name = 'auto_alter_html_banners_for_click_tracking'";
$rs = $this->oDbh->query($query);
//check for error
if (PEAR::isError($rs)) {
$this->logError($rs->getUserInfo());
return false;
}
$preferenceId = $rs->fetchRow(MDB2_FETCHMODE_ASSOC);
$preferenceId = $preferenceId['preference_id'];
if (!empty($preferenceId)) {
$sql = "DELETE FROM " . $this->oDbh->quoteIdentifier($this->tblAccountPreferenceAssoc, true) . " WHERE preference_id = {$preferenceId}";
$rs = $this->oDbh->exec($sql);
//check for error
if (PEAR::isError($rs)) {
$this->logError($rs->getUserInfo());
return false;
}
$this->logOnly("Removed entries in account_preferences_assoc table related to auto_alter_html_banners_for_click_tracking");
$sql = "DELETE FROM " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . " WHERE preference_id = {$preferenceId}";
$rs = $this->oDbh->exec($sql);
//check for error
if (PEAR::isError($rs)) {
$this->logError($rs->getUserInfo());
return false;
}
$this->logOnly("Removed auto_alter_html_banners_for_click_tracking preference in preferences table");
}
return true;
}
/**
* @param string $fromTable
* @param string $fromColumn
* @param string $toTable
* @param string $toColumn
* @return boolean
*/
function updateColumn($fromTable, $fromColumn, $toTable, $toColumn)
{
// ERROR: $this not initialised
$prefix = $this->getPrefix();
$statement = $this->aSQLStatements['table_update_col'];
$query = sprintf($statement, $prefix . $toTable, $toColumn, $prefix . $fromTable, $fromColumn);
$this->_log('select query prepared: ' . $query);
$result = $this->oDBH->exec($query);
if (PEAR::isError($result)) {
$this->_logError('error executing statement: ' . $result->getUserInfo());
return false;
}
$this->affectedRows = $result;
$this->_log('update complete: ' . $this->affectedRows . ' rows affected');
return true;
}
/**
* Drop a FUNCTION
*/
public function dropFunction($name)
{
return $this->db->exec('DROP FUNCTION ' . $name);
}
/**
* Removes a document type
*
* After a document type is removed, documents of that type can no longer
* be indexed or queried.
*
* @param MDB2_Driver_Common $db the database driver to use to remove the
* document type identifier.
* @param string $type_shortname the shortname of the document type to
* remove.
*
* @throws NateGoSearchDBException if a database error occurs.
*/
public static function removeDocumentType(MDB2_Driver_Common $db, $type_shortname)
{
$type_shortname = (string) $type_shortname;
$sql = sprintf('delete from NateGoSearchType where shortname = %s', $db->quote($type_shortname, 'text'));
$result = $db->exec($sql);
if (MDB2::isError($result)) {
throw new NateGoSearchDBException($result);
}
}
/**
* Inserts associations between zone and ad objects (campaign or banner)
* represented by this handler.
*
* @param MDB2_Driver_Common $oDbh
*/
function insertAssocs($oDbh)
{
$assocTable = $this->getAssocTable();
$adObjectColumn = $this->getAdObjectColumn();
$result = true;
foreach ($this->aAdObjectIds as $adObjectId) {
$sql = "\n INSERT INTO {$this->prefix}{$assocTable} (zone_id, {$adObjectColumn})\n VALUES ({$this->zone_id}, {$adObjectId})";
if (is_numeric($adObjectId)) {
$result = $oDbh->exec($sql);
if (PEAR::isError($result)) {
return $result;
}
} else {
$aResult[] = 'Invalid data found during migration_tables_core_127: ' . $sql;
}
$result = $aResult;
}
return true;
}
/**
* Clears this search index
*
* The index is cleared for this indexer's document type
*
* @see NateGoSearchIndexer::__construct()
*
* @throws NateGoSearchDBException if a database error occurs.
*/
protected function clear()
{
$sql = sprintf('delete from NateGoSearchIndex where document_type = %s', $this->db->quote($this->document_type, 'integer'));
$result = $this->db->exec($sql);
if (MDB2::isError($result)) {
throw new NateGoSearchDBException($result);
}
}
/**
* A method to search for any aligned hours in the data_intermediate_ad and
* data_summary_ad_hourly tables where the number of requests, impressions,
* clicks or conversions do not agree with each other, and, where any such
* hours are found, to search these hours for any cases of the
* data_summary_ad_hourly table containing fewer requests, impressions,
* clicks or conversions that the data_intermediate_ad table, and where
* these cases are found, to update the data_summary_ad_hourly to match
* the values found in the data_intermediate_ad table.
*
* @param Date $oStartDate The start date/time of the range to operate on.
* @param Date $oEndDate The end date/time of the farnce to operate on.
*/
function issueTwo($oStartDate, $oEndDate)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$sQuery = "\n SELECT\n t1.hour_date_time AS date_time,\n t1.requests AS intermediate_requests,\n t2.requests AS summary_requests,\n t1.impressions AS intermediate_impressions,\n t2.impressions AS summary_impressions,\n t1.clicks AS intermediate_clicks,\n t2.clicks AS summary_clicks,\n t1.conversions AS intermediate_conversions,\n t2.conversions AS summary_conversions\n FROM\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_intermediate_ad\n WHERE\n date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n GROUP BY\n hour_date_time\n ) AS t1,\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_summary_ad_hourly\n WHERE\n date_time >= " . $this->oDbh->quote($oStartDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:%M:%S'), 'timestamp') . "\n GROUP BY\n hour_date_time\n ) AS t2\n WHERE\n t1.hour_date_time = t2.hour_date_time\n HAVING\n intermediate_requests > summary_requests\n OR\n intermediate_impressions > summary_impressions\n OR\n intermediate_clicks > summary_clicks\n OR\n intermediate_conversions > summary_conversions\n ORDER BY\n date_time";
OX::disableErrorHandling();
$rsResult = $this->oDbh->query($sQuery);
OX::enableErrorHandling();
if (PEAR::isError($rsResult)) {
$message = "\n Database error while searching for invalid data:\n " . $rsResult->getMessage() . "\n Cannot detect issues, so will not attepmt any corrections.\n";
echo $message;
return;
}
$rows = $rsResult->numRows();
if ($rows == 0) {
$message = "\n Did not detect any issues; no statistics data correction required.\n";
echo $message;
return;
}
$message = "\n Detected {$rows} operation intervals that need further inspection...\n";
echo $message;
while ($aRow = $rsResult->fetchRow()) {
$message = " Inspecting operation interval {$aRow['date_time']}...\n";
echo $message;
$message = " Correcting...\n";
echo $message;
$oDate = new Date($aRow['date_time']);
$sInnerQuery = "\n SELECT\n t1.hour_date_time AS date_time,\n t1.ad_id AS ad_id,\n t1.zone_id AS zone_id,\n t1.requests AS intermediate_requests,\n t2.requests AS summary_requests,\n t1.impressions AS intermediate_impressions,\n t2.impressions AS summary_impressions,\n t1.clicks AS intermediate_clicks,\n t2.clicks AS summary_clicks,\n t1.conversions AS intermediate_conversions,\n t2.conversions AS summary_conversions\n FROM\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n ad_id AS ad_id,\n zone_id AS zone_id,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_intermediate_ad\n WHERE\n date_time >= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:00:00'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:59:59'), 'timestamp') . "\n GROUP BY\n hour_date_time, ad_id, zone_id\n ) AS t1,\n (\n SELECT\n DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS hour_date_time,\n ad_id AS ad_id,\n zone_id AS zone_id,\n SUM(requests) AS requests,\n SUM(impressions) AS impressions,\n SUM(clicks) AS clicks,\n SUM(conversions) AS conversions\n FROM\n {$aConf['table']['prefix']}data_summary_ad_hourly\n WHERE\n date_time >= " . $this->oDbh->quote($oDate->format('%Y-%m-%d %H:00:00'), 'timestamp') . "\n AND\n date_time <= " . $this->oDbh->quote($oEndDate->format('%Y-%m-%d %H:59:59'), 'timestamp') . "\n GROUP BY\n hour_date_time, ad_id, zone_id\n ) AS t2\n WHERE\n t1.hour_date_time = t2.hour_date_time\n AND\n t1.ad_id = t2.ad_id\n AND\n t1.zone_id = t2.zone_id\n HAVING\n intermediate_requests > summary_requests\n OR\n intermediate_impressions > summary_impressions\n OR\n intermediate_clicks > summary_clicks\n OR\n intermediate_conversions > summary_conversions\n ORDER BY\n date_time, ad_id, zone_id";
OX::disableErrorHandling();
$rsInnerResult = $this->oDbh->query($sInnerQuery);
OX::enableErrorHandling();
if (PEAR::isError($rsInnerResult)) {
$message = " Error while selecting unsummarised rows, please re-run script later!\n";
echo $message;
continue;
}
while ($aInnerRow = $rsInnerResult->fetchRow()) {
$message = " Correcting data for '{$aRow['date_time']}', Creative ID '{$aInnerRow['ad_id']}', Zone ID '{$aInnerRow['zone_id']}'...\n";
echo $message;
$sUpdateQuery = "\n UPDATE\n {$aConf['table']['prefix']}data_summary_ad_hourly\n SET\n requests = " . $this->oDbh->quote($aInnerRow['intermediate_requests'], 'integer') . ",\n impressions = " . $this->oDbh->quote($aInnerRow['intermediate_impressions'], 'integer') . ",\n clicks = " . $this->oDbh->quote($aInnerRow['intermediate_clicks'], 'integer') . ",\n conversions = " . $this->oDbh->quote($aInnerRow['intermediate_conversions'], 'integer') . "\n WHERE\n date_time = " . $this->oDbh->quote($aInnerRow['date_time'], 'timestamp') . "\n AND\n ad_id = " . $this->oDbh->quote($aInnerRow['ad_id'], 'integer') . "\n AND\n zone_id = " . $this->oDbh->quote($aInnerRow['zone_id'], 'integer') . "\n AND\n requests = " . $this->oDbh->quote($aInnerRow['summary_requests'], 'integer') . "\n AND\n impressions = " . $this->oDbh->quote($aInnerRow['summary_impressions'], 'integer') . "\n AND\n clicks = " . $this->oDbh->quote($aInnerRow['summary_clicks'], 'integer') . "\n AND\n conversions = " . $this->oDbh->quote($aInnerRow['summary_conversions'], 'integer') . "\n LIMIT 1";
if (defined('DEBUG_ONLY')) {
$message = " Running in debug mode only, if running correctly, the following would have been performed:\n";
echo $message;
$message = $sUpdateQuery;
$message = preg_replace('/\\n/', '', $message);
$message = preg_replace('/^ +/', '', $message);
$message = preg_replace('/ +/', ' ', $message);
$message = wordwrap($message, 75, "\n ");
$message = " " . $message . ";\n";
echo $message;
} else {
OX::disableErrorHandling();
$rsUpdateResult = $this->oDbh->exec($sUpdateQuery);
OX::enableErrorHandling();
if (PEAR::isError($rsUpdateResult)) {
$message = " Error while updating an incomplete row, please re-run script later!\n";
echo $message;
continue;
}
if ($rsUpdateResult > 1) {
$message = " Error: More than one incomplete row updated, please manually inspect data!\n once the script has completed running!\n";
echo $message;
continue;
}
$message = " Updated {$rsUpdateResult} incomplete row.\n";
echo $message;
}
if (defined('DEBUG_ONLY')) {
sleep(1);
}
}
}
}
/**
* A method to test when there are old format raw requests,
* impressions and clicks, in the operation interval being
* migrated.
*/
function testSimpleValidData()
{
$aConf =& $GLOBALS['_MAX']['CONF'];
$aConf['maintenance']['operationInterval'] = 60;
// Prepare an array of the required tables used in testing
$aTables = array($aConf['table']['prefix'] . $aConf['table']['data_raw_ad_request'] => $aConf['table']['prefix'] . 'data_bkt_r', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_impression'] => $aConf['table']['prefix'] . 'data_bkt_m', $aConf['table']['prefix'] . $aConf['table']['data_raw_ad_click'] => $aConf['table']['prefix'] . 'data_bkt_c');
// Install the openXDeliveryLog plugin, which will create the
// data bucket tables required for testing
TestEnv::installPluginPackage('openXDeliveryLog', false);
// Ensure that there are no old format raw data
foreach ($aTables as $rawTable => $bucketTable) {
$query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true);
$rsResult = $this->oDbh->query($query);
$this->assertNotA($rsReults, 'PEAR_Error');
$rows = $rsResult->numRows();
$this->assertEqual($rows, 1);
$aRow = $rsResult->fetchRow();
$this->assertEqual($aRow['count'], 0);
}
// Ensure that there are no new format bucket data
foreach ($aTables as $rawTable => $bucketTable) {
$query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true);
$rsResult = $this->oDbh->query($query);
$this->assertNotA($rsReults, 'PEAR_Error');
$rows = $rsResult->numRows();
$this->assertEqual($rows, 1);
$aRow = $rsResult->fetchRow();
$this->assertEqual($aRow['count'], 0);
}
// Insert some old style raw data in an operation interval
// that will be migrated
foreach ($aTables as $rawTable => $bucketTable) {
$query = "\n INSERT INTO\n " . $this->oDbh->quoteIdentifier($rawTable, true) . "\n (\n date_time,\n ad_id,\n creative_id,\n zone_id\n )\n VALUES\n (\n '2009-01-09 12:30:00',\n 1,\n 0,\n 1\n )";
$this->oDbh->exec($query);
}
// Ensure that the old format raw data was inserted correctly
foreach ($aTables as $rawTable => $bucketTable) {
$query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($rawTable, true);
$rsResult = $this->oDbh->query($query);
$this->assertNotA($rsReults, 'PEAR_Error');
$rows = $rsResult->numRows();
$this->assertEqual($rows, 1);
$aRow = $rsResult->fetchRow();
$this->assertEqual($aRow['count'], 1);
}
// Run the migration of raw data DAL code for a given OI
$oStart = new Date('2009-01-09 12:00:00');
$oEnd = new Date('2009-01-09 12:59:59');
$this->oDal->migrateRawRequests($oStart, $oEnd);
$this->oDal->migrateRawImpressions($oStart, $oEnd);
$this->oDal->migrateRawClicks($oStart, $oEnd);
// Test that the data was migrated correctly
foreach ($aTables as $rawTable => $bucketTable) {
$query = "\n SELECT\n COUNT(*) AS count\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true);
$rsResult = $this->oDbh->query($query);
$this->assertNotA($rsReults, 'PEAR_Error');
$rows = $rsResult->numRows();
$this->assertEqual($rows, 1);
$aRow = $rsResult->fetchRow();
$this->assertEqual($aRow['count'], 1);
$query = "\n SELECT\n *\n FROM\n " . $this->oDbh->quoteIdentifier($bucketTable, true);
$rsResult = $this->oDbh->query($query);
$this->assertNotA($rsReults, 'PEAR_Error');
$rows = $rsResult->numRows();
$this->assertEqual($rows, 1);
$aRow = $rsResult->fetchRow();
$this->assertEqual($aRow['interval_start'], '2009-01-09 12:00:00');
$this->assertEqual($aRow['creative_id'], 1);
$this->assertEqual($aRow['zone_id'], 1);
$this->assertEqual($aRow['count'], 1);
}
// Uninstall the installed plugin
TestEnv::uninstallPluginPackage('openXDeliveryLog', false);
// Restore the test environment configuration
TestEnv::restoreConfig();
}
function execute($aParams)
{
$this->oUpgrade =& $aParams[0];
$this->oDbh =& OA_DB::singleton();
$aConf = $GLOBALS['_MAX']['CONF']['table'];
$this->prefix = $aConf['prefix'];
$this->tblCampaigns = $aConf['prefix'] . ($aConf['campaigns'] ? $aConf['campaigns'] : 'campaigns');
$this->queryUpdateTemplate = "UPDATE " . $this->oDbh->quoteIdentifier($this->tblCampaigns, true) . "\n SET revenue_type = %s\n WHERE campaignid in (%s)";
$query = 'SELECT campaignid, clicks, conversions
FROM ' . $this->oDbh->quoteIdentifier($this->tblCampaigns, true) . ' c
WHERE c.revenue_type IS NULL ';
$rs = $this->oDbh->query($query);
//check for error
if (PEAR::isError($rs)) {
$this->logError($rs->getUserInfo());
return false;
}
/*
process campaigns and derive the revenue_type from set limits
if (conversions set) -> revenue type is CPA
else if (clicks set) -> revenue type is CPC
else if (impressions set) -> revenue type is CPM
else -> default to CPM
*/
$aCPMCampaigns = array();
$aCPCCampaigns = array();
$aCPACampaigns = array();
while ($aCampaign = $rs->fetchRow(MDB2_FETCHMODE_ASSOC)) {
if ($aCampaign['conversions'] > 0) {
$aCPACampaigns[] = $aCampaign['campaignid'];
} else {
if ($aCampaign['clicks'] > 0) {
$aCPCCampaigns[] = $aCampaign['campaignid'];
} else {
//views set or no limits CPM as well
$aCPMCampaigns[] = $aCampaign['campaignid'];
}
}
}
/*
update campaigns accordingly
'MAX_FINANCE_CPM', 1);
'MAX_FINANCE_CPC', 2);
'MAX_FINANCE_CPA', 3);
*/
$cpmCount = count($aCPMCampaigns);
$cpcCount = count($aCPCCampaigns);
$cpaCount = count($aCPACampaigns);
$count = $cpmCount + $cpcCount + $cpaCount;
$this->logOnly("Found " + $count + " campaign(s) to set missing revenue type: " + ($cpmCount > 0 ? "{$cpmCount} to CPM," : '') + ($cpcCount > 0 ? "{$cpcCount} to CPC," : '') + ($cpaCount > 0 ? "{$cpaCount} to CPA" : ''));
if ($cpmCount > 0) {
$query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPM, implode(',', $aCPMCampaigns));
$result = $this->oDbh->exec($query);
if (PEAR::isError($result)) {
$this->logError($result->getUserInfo());
return false;
} else {
$this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPM");
}
}
if ($cpcCount > 0) {
$query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPC, implode(',', $aCPCCampaigns));
$result = $this->oDbh->exec($query);
if (PEAR::isError($result)) {
$this->logError($result->getUserInfo());
return false;
} else {
$this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPC");
}
}
if ($cpaCount > 0) {
$query = sprintf($this->queryUpdateTemplate, MAX_FINANCE_CPA, implode(',', $aCPACampaigns));
$result = $this->oDbh->exec($query);
if (PEAR::isError($result)) {
$this->logError($result->getUserInfo());
return false;
} else {
$this->logOnly("Successfully updated 'revenue_type' of {$result} campaign(s) to CPA");
}
}
return true;
}
