PHP MDB2_Driver_Common::quote方法代码示例

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

 /**
  * 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 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 getSqlListOrder($listOrder, $orderDirection)
 {
     $direction = $this->getOrderDirection($this->oDbh->quote($orderDirection, 'text'));
     $nameColumn = $this->getOrderColumn($this->oDbh->quote($listOrder, 'text'));
     if (is_array($nameColumn)) {
         $sqlTableOrder = ' ORDER BY ' . implode($direction . ',', $nameColumn) . $direction;
     } else {
         $sqlTableOrder = !empty($nameColumn) ? " ORDER BY {$nameColumn} {$direction}" : '';
     }
     return $sqlTableOrder;
 }

    private function getSubTagWhereClause()
    {
        $photo_id_sql = 'select PinholePhoto.id from PinholePhoto
			inner join ImageSet on PinholePhoto.image_set = ImageSet.id';
        $join_clauses = implode(' ', $this->getJoinClauses());
        if ($join_clauses != '') {
            $photo_id_sql .= ' ' . $join_clauses . ' ';
        }
        $where_clause = $this->getWhereClause();
        if ($where_clause != '') {
            $photo_id_sql .= ' where ' . $where_clause;
        }
        $sql = sprintf('PinholeTag.id in
			(select tag from PinholePhotoTagBinding where photo in
			(%s))', $photo_id_sql);
        if ($this->app->getInstance() !== null) {
            $sql .= sprintf(' and PinholeTag.instance = %s', $this->db->quote($this->app->getInstanceId(), 'integer'));
        }
        return $sql;
    }

    /**
     * Efficiently loads tags for a set of posts
     *
     * @param BlorgPostWrapper $posts the posts for which to efficiently load
     *                                 tags.
     */
    protected function loadPostTags(BlorgPostWrapper $posts)
    {
        $instance_id = $this->instance === null ? null : $this->instance->id;
        $wrapper = SwatDBClassMap::get('BlorgTagWrapper');
        // get post ids
        $post_ids = array();
        foreach ($posts as $post) {
            $post_ids[] = $post->id;
        }
        $post_ids = $this->db->implodeArray($post_ids, 'integer');
        // build SQL to select all tags
        $sql = sprintf('select BlorgTag.*, BlorgPostTagBinding.post
			from BlorgTag
				inner join BlorgPostTagBinding on
					BlorgTag.id = BlorgPostTagBinding.tag
			where post in (%s) and BlorgTag.instance %s %s
			order by post, createdate desc', $post_ids, SwatDB::equalityOperator($instance_id), $this->db->quote($instance_id, 'integer'));
        // get all tags
        $tags = SwatDB::query($this->db, $sql, $wrapper);
        // assign empty recordsets for all posts
        foreach ($posts as $post) {
            $recordset = new $wrapper();
            $post->setTags($recordset);
        }
        // assign tags to correct posts
        $current_post_id = null;
        $current_recordset = null;
        foreach ($tags as $tag) {
            $post_id = $tag->getInternalValue('post');
            if ($post_id !== $current_post_id) {
                $current_post_id = $post_id;
                $current_recordset = $posts[$post_id]->getTags();
            }
            $current_recordset->add($tag);
        }
    }

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

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

    /**
     * Get a list of popular/successful search keywords
     *
     * This is used to query the database for a list of keywords from the
     * NateGoSearchHistory table. The results are based upon the document_count
     * of each of the keywords and if the words have been searched recently.
     *
     * @param MDB2_Driver_Common $db the database driver to use.
     * @param integer $document_threshold optional. The minimum number of
     *                                     results in which a word must be
     *                                     contained to be considered
     *                                     popular. If not specified, 150
     *                                     is used.
     * @param string $date_threshold optional. Search keywords must be after
     *                                this date to be considered popular. Uses
     *                                strtotime format. If not specified,
     *                                '6 months ago' is used.
     *
     * @return array an array of popular search words
     */
    public static function getSearchHistoryPopularWords(MDB2_Driver_Common $db, $document_threshold = 150, $date_threshold = '6 months ago')
    {
        $date = strtotime($date_threshold);
        $date = date('c', $date);
        $sql = sprintf('select distinct keywords from NateGoSearchHistory
			where document_count > %s and creation_date > %s', $db->quote($document_threshold, 'integer'), $db->quote($date, 'date'));
        $words = $db->queryCol($sql, 'text');
        if (MDB2::isError($words)) {
            throw new NateGoSearchDBException($words);
        }
        return $words;
    }