/**
  * Class constructor
  *
  * @param MDB2_Driver_Common $oDbh
  * @return OA_DB_Charset
  */
 function __construct(&$oDbh)
 {
     $aVersion = $oDbh->getServerVersion();
     if (version_compare($aVersion['native'], '4.1.2', '>=')) {
         parent::__construct($oDbh);
     }
 }

 /**
  * Class constructor
  *
  * @param MDB2_Driver_Common $oDbh
  * @return OA_DB_Charset
  */
 function OA_DB_Charset_mysql(&$oDbh)
 {
     $aVersion = $oDbh->getServerVersion();
     if (version_compare($aVersion['native'], '4.1.2', '>=')) {
         parent::OA_DB_Charset($oDbh);
     }
 }

 /**
  * Private method that will use MDB2_Driver_Common::query() for simple and
  * MDB2_Driver_Common::prepare() & MDB2_Statement_Common::execute() for complex
  * query specifications.
  *
  * @param mixed  $sql        A string or an array.
  * @param string $configPath The config path used for exception messages.
  *
  * @return MDB2_Result
  * @throws XML_Query2XML_DBException If a database related error occures.
  */
 private function _prepareAndExecute($sql, $configPath)
 {
     $preparedQuery = $sql['query'];
     if (isset($sql['limit'])) {
         $preparedQuery .= '; LIMIT:' . $sql['limit'];
         $preparedQuery .= '; OFFSET:' . $sql['offset'];
         $this->_db->setLimit($sql['limit'], $sql['offset']);
     }
     if (isset($this->_preparedQueries[$preparedQuery])) {
         $queryHandle = $this->_preparedQueries[$preparedQuery];
     } else {
         // PREPARE
         $queryHandle = $this->_db->prepare($sql['query']);
         if (PEAR::isError($queryHandle)) {
             /*
              * unit tests: (only if mysql or pgsql is used)
              *  MDB2/_prepareAndExecute/throwDBException_complexQuery.phpt
              */
             throw new XML_Query2XML_DBException($configPath . ': Could not prepare the following SQL query: ' . $sql['query'] . '; ' . $queryHandle->toString());
         }
         $this->_preparedQueries[$preparedQuery] =& $queryHandle;
     }
     // EXECUTE
     if (isset($sql['data'])) {
         $result = $queryHandle->execute($sql['data']);
     } else {
         $result = $queryHandle->execute();
     }
     if (PEAR::isError($result)) {
         /*
          * unit tests:
          *  if sqlite is used: MDB2/_prepareAndExecute/
          *   throwDBException_complexQuery.phpt
          *  if sqlite or mysql is sued: MDB2/getXML/
          *   throwDBException_nullResultSet_complexQuery_multipleRecords.phpt
          *   throwDBException_nullResultSet_complexQuery_singleRecord.phpt
          */
         throw new XML_Query2XML_DBException($configPath . ': Could not execute the following SQL query: ' . $sql['query'] . '; ' . $result->toString());
     }
     return $result;
 }

 /**
  * Create a new MDB2 connection object and connect to the specified
  * database
  *
  * IMPORTANT: In order for MDB2 to work properly it is necessary that
  * you make sure that you work with a reference of the original
  * object instead of a copy (this is a PHP4 quirk).
  *
  * For example:
  *     $mdb =& MDB2::connect($dsn);
  *          ^^
  * And not:
  *     $mdb = MDB2::connect($dsn);
  *          ^^
  *
  * @param   mixed   $dsn      'data source name', see the MDB2::parseDSN
  *                            method for a description of the dsn format.
  *                            Can also be specified as an array of the
  *                            format returned by MDB2::parseDSN.
  * @param   array   $options  An associative array of option names and
  *                            their values.
  * @return  mixed   a newly created MDB2 connection object, or a MDB2
  *                  error object on error
  * @access  public
  * @see     MDB2::parseDSN
  */
 function &connect($dsn, $options = false)
 {
     $dsninfo = MDB2::parseDSN($dsn);
     if (!isset($dsninfo['phptype'])) {
         $error =& MDB2_Driver_Common::raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'no RDBMS driver specified');
         return $error;
     }
     $type = $dsninfo['phptype'];
     if (is_array($options) && isset($options['debug']) && $options['debug'] >= 2) {
         $debug = true;
     } else {
         $debug = false;
     }
     $db =& MDB2::factory($type, $debug);
     if (MDB2::isError($db)) {
         return $db;
     }
     $db->setDSN($dsninfo);
     $err = MDB2::setOptions($db, $options);
     if (MDB2::isError($err)) {
         $db->disconnect();
         return $err;
     }
     if (isset($dsninfo['database'])) {
         $err = $db->connect();
         if (MDB2::isError($err)) {
             $dsn = $db->getDSN();
             $err->addUserInfo($dsn);
             return $err;
         }
     }
     return $db;
 }

 /**
  * Tests that the MDB2::escape() method correctly escapes strings.
  */
 function test_escape()
 {
     $tmp = $this->db->string_quoting;
     $this->string_quoting['escape'] = '\\';
     $this->string_quoting['end'] = '"';
     $text = 'xxx"z"xxx';
     $this->assertEquals('xxx\\"z\\"xxx', MDB2_Driver_Common::escape($text), 'escape');
     $this->db->string_quoting = $tmp;
 }

 /**
  * Gets the available document type shortnames
  *
  * @param MDB2_Driver_Common $db the database driver to use to get the
  *                                available document type shortnames.
  *
  * @return array an array containing the available document type shortnames.
  *
  * @throws NateGoSearchDBException if a database error occurs.
  */
 public static function getDocumentTypes(MDB2_Driver_Common $db)
 {
     $sql = 'select shortname from NateGoSearchType';
     $values = $db->queryCol($sql, 'text');
     if (MDB2::isError($values)) {
         throw new NateGoSearchDBException($values);
     }
     return $values;
 }

 function rollback()
 {
     if ($this->hasTransactions) {
         $this->oDbh->rollback();
         return true;
     }
     return false;
 }

 /**
  * A method to test when there are no old format raw requests,
  * impressions and clicks.
  */
 function testNoData()
 {
     $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);
     }
     // 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);
     // Re-test that there are still 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);
     }
     // Uninstall the installed plugin
     TestEnv::uninstallPluginPackage('openXDeliveryLog', false);
     // Restore the test environment configuration
     TestEnv::restoreConfig();
 }

    /**
     * Gets a recordset of tag dataobjects.
     *
     * @param SwatDBRange $range optional. Range of tags to retrieve. If not
     *                           specified, all tags are loaded.
     * @param string $order_by_clause optional. SQL order by clause of the tag
     *                                list.
     *
     * @return PinholeTagDataObjectWrapper
     */
    private function getSubTagDataObjects(SwatDBRange $range = null, $order_by_clause = null)
    {
        $args = func_get_args();
        $cache_key = $this->getCacheKey(__FUNCTION__, $args);
        $value = $this->app->getCacheRecordset($cache_key, 'PinholeTagDataObjectWrapper', 'photos');
        if ($value !== false) {
            return $value;
        }
        if ($order_by_clause === null) {
            $order_by_clause = 'PinholeTagDateView.first_modified desc';
        }
        $sql = sprintf('select PinholeTag.*,
				PinholeTagDateView.first_modified,
				PinholeTagDateView.last_modified
			from PinholeTag
			inner join PinholeTagDateView on
				PinholeTagDateView.tag = PinholeTag.id
			where %s
			order by %s', $this->getSubTagWhereClause(), $order_by_clause);
        if ($range !== null) {
            $this->db->setLimit($range->getLimit(), $range->getOffset());
        }
        $tag_data_objects = SwatDB::query($this->db, $sql, 'PinholeTagDataObjectWrapper');
        $this->app->addCacheRecordset($tag_data_objects, $cache_key, 'photos');
        return $tag_data_objects;
    }

 /**
  * Build global statistics array to be sent through Sync
  *
  * @return array
  */
 function buildStats()
 {
     $lastRun = OA_Dal_ApplicationVariables::get('sync_last_run');
     if ($lastRun) {
         $oStart = new Date($lastRun);
     } else {
         $oStart = new Date();
         $oStart->subtractSpan(new Date_Span('1-0-0-0'));
     }
     $oStart->setMinute(0);
     $oStart->setSecond(0);
     $oEnd = new Date();
     $oEnd->setMinute(0);
     $oEnd->setSecond(0);
     $doDsah = OA_Dal::factoryDO('data_summary_ad_hourly');
     $doDsah->selectAdd();
     $doDsah->selectAdd('date_time');
     $doDsah->selectAdd('SUM(impressions) AS total_impressions');
     $doDsah->selectAdd('SUM(clicks) AS total_clicks');
     $doDsah->whereAdd("date_time >= " . $this->oDbh->quote($oStart->format('%Y-%m-%d %H:%M:%S')));
     $doDsah->whereAdd("date_time < " . $this->oDbh->quote($oEnd->format('%Y-%m-%d %H:%M:%S')));
     $doDsah->groupBy('date_time');
     $doDsah->orderBy('date_time');
     $doDsah->find();
     $aStats = array();
     while ($doDsah->fetch()) {
         $row = $doDsah->toArray();
         $aStats[$row['date_time']] = array('impressions' => $row['total_impressions'], 'clicks' => $row['total_clicks']);
     }
     return $aStats;
 }

 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}'");
 }

 function execute($aParams)
 {
     $this->oUpgrade =& $aParams[0];
     $this->oDbh =& OA_DB::singleton();
     $aConf = $GLOBALS['_MAX']['CONF']['table'];
     $this->prefix = $aConf['prefix'];
     $this->tblPreferences = $aConf['prefix'] . ($aConf['preferences'] ? $aConf['preferences'] : 'preferences');
     $query = "INSERT INTO " . $this->oDbh->quoteIdentifier($this->tblPreferences, true) . "\n                  (preference_name, account_type)\n                 VALUES('campaign_ecpm_enabled', 'MANAGER')";
     $ret = $this->oDbh->query($query);
     //check for error
     if (PEAR::isError($ret)) {
         $this->logError($ret->getUserInfo());
         return false;
     }
     $this->logOnly("Added 'campaign_ecpm_enabled' preference to 'MANAGER' account");
     return true;
 }

 public function tableExists($table)
 {
     $this->db->loadModule('Manager', null, true);
     $tables = $this->db->manager->listTables();
     if (MDB2::isError($tables)) {
         //$this->fail('Cannot list tables: '. $tables->getUserInfo());
         return false;
     }
     return in_array(strtolower($table), array_map('strtolower', $tables));
 }

 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;
 }

 /**
  * Commit the database changes done during a transaction that is in progress
  * @return bool
  */
 public static function commit()
 {
     self::connect();
     if (!self::$inTransaction) {
         return false;
     }
     self::$connection->commit();
     self::$inTransaction = false;
     return true;
 }

 function getLinkedSequence($table, $field_name)
 {
     $query = "SELECT\n                    (SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)\n                        FROM pg_attrdef d\n                        WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as default\n                    FROM pg_attribute a, pg_class c\n                    WHERE c.relname = " . $this->oDbh->quote($table, 'text') . "\n                        AND c.oid = a.attrelid\n                        AND NOT a.attisdropped\n                        AND a.attnum > 0\n                        AND a.attname = " . $this->oDbh->quote($field_name, 'text') . "\n                    ORDER BY a.attnum";
     $column = $this->oDbh->queryRow($query, null, MDB2_FETCHMODE_ASSOC);
     if (!PEAR::isError($column)) {
         if (preg_match('/nextval\\(\'(.*?)\'/', $column['default'], $m)) {
             return $m[1];
         }
     }
     return false;
 }

 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];
     $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;
 }

 /**
  * Update the "Administrator Account" account to "System Administrator", if
  * it still exists for the user, for improved understanding of the account
  * purpose
  */
 function _updateAdministratorAccountToSystemAdministrator()
 {
     $this->oDbh =& OA_DB::singleton();
     $aConf = $GLOBALS['_MAX']['CONF']['table'];
     $this->logOnly("Attempting to rename the 'Administrator Account' account to 'System Administrator' in the 'accounts' table");
     $tblAccounts = $aConf['prefix'] . ($aConf['accounts'] ? $aConf['accounts'] : 'accounts');
     $query = "UPDATE " . $this->oDbh->quoteIdentifier($tblAccounts, true) . " SET account_name = 'System Administrator' WHERE account_name = 'Administrator account'";
     $result = $this->oDbh->query($query);
     if (!PEAR::isError($result)) {
         $this->logOnly("Renamed the old 'Administrator Account' account in the 'accounts' table");
     } else {
         $this->logError("Failed to rename the old 'Administrator Account' account in the 'accounts' table");
     }
 }

 /**
  * @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;
 }