PHP DBC::NewRecordSet方法代码示例

 function testMigrateData()
 {
     $prefix = $this->getPrefix();
     $this->initDatabase(126, array('zones', 'ad_zone_assoc', 'placement_zone_assoc', 'banners'));
     $aAValues = array(array('zoneid' => 1, 'zonetype' => 0, 'what' => ''), array('zoneid' => 2, 'zonetype' => 0, 'what' => 'bannerid:3'), array('zoneid' => 3, 'zonetype' => 3, 'what' => 'clientid:3'), array('zoneid' => 4, 'zonetype' => 3, 'what' => 'clientid:5', 'delivery' => phpAds_ZoneText), array('zoneid' => 5, 'zonetype' => 3, 'what' => 'clientid:5', 'delivery' => phpAds_ZoneBanner, 'width' => 468, 'height' => 60), array('zoneid' => 6, 'zonetype' => 0, 'what' => 'bannerid:2,bannerid:3'), array('zoneid' => 7, 'zonetype' => 3, 'what' => 'clientid:3,clientid:4'), array('zoneid' => 8, 'zonetype' => 0, 'what' => 'bannerid:2,bannerid:3,bannerid:4,bannerid:5'), array('zoneid' => 9, 'zonetype' => 3, 'what' => 'clientid:,clientid:3'));
     foreach ($aAValues as $aValues) {
         // Set empty defaults for NOT NULL fields
         $aValues['chain'] = $aValues['prepend'] = $aValues['append'] = '';
         $sql = OA_DB_Sql::sqlForInsert('zones', $aValues);
         $this->oDbh->exec($sql);
     }
     $aABannerValues = array(array('bannerid' => 1, 'campaignid' => 3), array('bannerid' => 2, 'campaignid' => 3), array('bannerid' => 3, 'campaignid' => 4), array('bannerid' => 4, 'campaignid' => 4), array('bannerid' => 5, 'campaignid' => 5, 'storagetype' => 'txt'), array('bannerid' => 6, 'campaignid' => 5, 'storagetype' => 'sql', 'width' => 468, 'height' => 60), array('bannerid' => 7, 'campaignid' => 5, 'storagetype' => 'sql', 'width' => 125, 'height' => 125));
     foreach ($aABannerValues as $aBannerValues) {
         // Set empty defaults for NOT NULL fields
         $aBannerValues['htmltemplate'] = $aBannerValues['htmlcache'] = $aBannerValues['bannertext'] = $aBannerValues['compiledlimitation'] = $aBannerValues['append'] = '';
         $sql = OA_DB_Sql::sqlForInsert('banners', $aBannerValues);
         $this->oDbh->exec($sql);
     }
     $this->upgradeToVersion(127);
     $aAssocTables = array("{$prefix}ad_zone_assoc WHERE link_type = 1" => 17, "{$prefix}ad_zone_assoc WHERE link_type = 0" => 7, "{$prefix}placement_zone_assoc" => 6);
     foreach ($aAssocTables as $assocTable => $cAssocs) {
         $rsCAssocs = DBC::NewRecordSet("SELECT count(*) AS cassocs FROM {$assocTable}");
         $this->assertTrue($rsCAssocs->find());
         $this->assertTrue($rsCAssocs->fetch());
         $this->assertEqual($cAssocs, $rsCAssocs->get('cassocs'), "%s: The table involved: {$assocTable}");
     }
 }

 function testMigrateData()
 {
     $prefix = $this->getPrefix();
     $this->initDatabase(121, array('clients', 'campaigns'));
     $aCampaigns = array(array('clientid' => 3, 'parent' => 1, 'views' => '100', target => '1000'), array('clientid' => 4, 'parent' => 1, 'views' => '200', target => '1'), array('clientid' => 5, 'parent' => 1, 'views' => '200', target => '0'));
     $cCampaigns = count($aCampaigns);
     $aAValues = array(array('clientid' => 1, 'parent' => 0, 'views' => '0', target => '0'), array('clientid' => 2, 'parent' => 0, 'views' => '0', target => '0'));
     $aAValues = array_merge($aAValues, $aCampaigns);
     foreach ($aAValues as $aValues) {
         $sql = OA_DB_Sql::sqlForInsert('clients', $aValues);
         $this->oDbh->exec($sql);
     }
     $this->upgradeToVersion(122);
     $tableCampaigns = $this->oDbh->quoteIdentifier($prefix . 'campaigns', true);
     $rsCampaigns = DBC::NewRecordSet("SELECT * from {$tableCampaigns}");
     $this->assertTrue($rsCampaigns->find());
     $this->assertEqual($cCampaigns, $rsCampaigns->getRowCount());
     for ($idxCampaign = 0; $idxCampaign < $cCampaigns; $idxCampaign++) {
         $this->assertTrue($rsCampaigns->fetch());
         $this->assertEqual($aCampaigns[$idxCampaign]['clientid'], $rsCampaigns->get('campaignid'));
         $this->assertEqual($aCampaigns[$idxCampaign]['parent'], $rsCampaigns->get('clientid'));
         $this->assertEqual($aCampaigns[$idxCampaign]['views'], $rsCampaigns->get('views'));
         $priority = $aCampaigns[$idxCampaign]['target'] > 0 ? 5 : 0;
         $this->assertEqual($priority, $rsCampaigns->get('priority'));
     }
     $tableClients = $this->oDbh->quoteIdentifier($prefix . 'clients', true);
     $rsClients = DBC::NewRecordSet("SELECT count(*) AS nclients FROM {$tableClients}");
     $this->assertTrue($rsClients->find());
     $this->assertTrue($rsClients->fetch());
     $this->assertEqual(count($aAValues) - $cCampaigns, $rsClients->get('nclients'));
     if ($this->oDbh->dbsyntax == 'pgsql') {
         $value = $this->oDbh->queryOne("SELECT NEXTVAL('{$prefix}campaigns_campaignid_seq')");
         $this->assertTrue($value > 5, "The current sequence value is {$value}.");
     }
 }

 function getChannelsAndAffiliates()
 {
     $prefix = $this->getTablePrefix();
     $oDbh = OA_DB::singleton();
     $tableCh = $oDbh->quoteIdentifier($prefix . 'channel', true);
     $tableAf = $oDbh->quoteIdentifier($prefix . 'affiliates', true);
     $query = "\n            SELECT\n                ch.channelid,\n                ch.affiliateid,\n                ch.name,\n                af.name AS affiliatename\n            FROM\n                {$tableCh} AS ch,\n                {$tableAf} AS af\n            WHERE\n                af.affiliateid=ch.affiliateid\n            ORDER BY ch.channelid\n    ";
     return DBC::NewRecordSet($query);
 }

 /**
  * Returns the record set for either 'acls' or 'acls_channels' table,
  * all records and rows.
  *
  * @param string $table Either 'acls' or 'acls_channels'
  * @return RecordSet
  */
 function &getRsAcls($table, $orderBy = false)
 {
     $table = $this->oDbh->quoteIdentifier($this->getTablePrefix() . $table);
     $query = "\n            SELECT\n                *\n            FROM\n                {$table}";
     if ($orderBy) {
         $query .= " ORDER BY " . $this->oDbh->quoteIdentifier($orderBy);
     }
     return DBC::NewRecordSet($query);
 }

 /**
  * Selects specified columns from the $table and returns
  * initialized (after find()) RecordSet or PEAR::Error
  * if initialization didn't work correctly.
  *
  * @param string $table
  * @param string $idColumn
  * @param string $id
  * @param array $aColumns List of columns, defaults to '*'.
  * @return DataSpace
  */
 function &selectWhereOne($table, $idColumn, $id, $aColumns = array('*'))
 {
     $sColumns = implode(' ', $aColumns);
     $table = OA_DB_Sql::modifyTableName($table);
     $sql = "SELECT {$sColumns} FROM {$table} WHERE {$idColumn} = {$id}";
     $rs =& DBC::NewRecordSet($sql);
     $result = $rs->find();
     if (PEAR::isError($result)) {
         return $result;
     }
     return $rs;
 }

 /**
  * A method to retrieve all advertisers where the advertiser name
  * contains a given string. Also returns any advertiser where the
  * advertiser ID equals the given keyword, should the keyword be
  * numeric.
  *
  * @param $keyword  string  Keyword to look for
  * @param $agencyId integer Limit results to advertisers owned by a given Agency ID
  * @return RecordSet
  */
 function getClientByKeyword($keyword, $agencyId = null)
 {
     $conf = $GLOBALS['_MAX']['CONF'];
     $whereClient = is_numeric($keyword) ? " OR c.clientid = {$keyword}" : '';
     $oDbh = OA_DB::singleton();
     $tableC = $oDbh->quoteIdentifier($this->getTablePrefix() . 'clients', true);
     $query = "\n            SELECT\n                c.clientid AS clientid,\n                c.clientname AS clientname\n            FROM\n                {$tableC} AS c\n            WHERE\n                (\n                    c.clientname LIKE " . DBC::makeLiteral('%' . $keyword . '%') . $whereClient . "\n                )";
     if ($agencyId !== null) {
         $query .= " AND c.agencyid=" . DBC::makeLiteral($agencyId);
     }
     return DBC::NewRecordSet($query);
 }

 function testMigrateCampaignIds()
 {
     $this->initDatabase(123, array('banners'));
     $sql = OA_DB_Sql::sqlForInsert('banners', array('bannerid' => '1', 'clientid' => '4', 'htmltemplate' => '', 'htmlcache' => '', 'bannertext' => '', 'compiledlimitation' => '', 'append' => ''));
     $this->oDbh->exec($sql);
     $this->upgradeToVersion(124);
     $table = $this->oDbh->quoteIdentifier($this->getPrefix() . 'banners', true);
     $rsBanners = DBC::NewRecordSet("SELECT campaignid FROM {$table}");
     $this->assertTrue($rsBanners->find());
     $this->assertTrue($rsBanners->fetch());
     $this->assertEqual(4, $rsBanners->get('campaignid'));
 }

 function getPublishersByTracker($trackerid)
 {
     $prefix = $this->getTablePrefix();
     $oDbh = OA_DB::singleton();
     $tableAza = $oDbh->quoteIdentifier($prefix . 'ad_zone_assoc', true);
     $tableZ = $oDbh->quoteIdentifier($prefix . 'zones', true);
     $tableP = $oDbh->quoteIdentifier($prefix . 'affiliates', true);
     $tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
     $tableCt = $oDbh->quoteIdentifier($prefix . 'campaigns_trackers', true);
     $query = "\n            SELECT\n                p.affiliateid AS affiliateid,\n                p.name AS name\n            FROM\n                {$tableAza} aza\n                JOIN {$tableZ} z ON (aza.zone_id = z.zoneid)\n                JOIN {$tableP} p USING (affiliateid)\n                JOIN {$tableB} b ON (aza.ad_id = b.bannerid)\n                JOIN {$tableCt} ct USING (campaignid)\n            WHERE\n                ct.trackerid = " . DBC::makeLiteral($trackerid) . "\n            GROUP BY\n                p.affiliateid,\n                name\n            ORDER BY\n                name\n        ";
     return DBC::NewRecordSet($query);
 }

 /**
  * A method to determine the number of impressions, clicks and conversions
  * delivered by a given ecpm campaign to date.
  *
  * Can also determine the delivery information up to a given operation
  * interval end date.
  *
  * @param integer    $agencyId The agency ID.
  * @param PEAR::Date $oDate      Limits delivery information to that which is
  *                               after this date.
  * @param integer    $priority Campaign priority (by default eCPM priority).
  * @return array
  */
 function getDeliveredEcpmCampainImpressionsByAgency($agencyId, $oDate, $priority = null)
 {
     $prefix = $this->getTablePrefix();
     $oDbh = OA_DB::singleton();
     if (is_null($priority)) {
         $priority = DataObjects_Campaigns::PRIORITY_ECPM;
     }
     $query = "\n            SELECT\n                c.campaignid AS campaignid,\n                SUM(dia.impressions) AS impressions_delivered\n            FROM\n                {$oDbh->quoteIdentifier($prefix . 'clients', true)} AS cl,\n                {$oDbh->quoteIdentifier($prefix . 'campaigns', true)} AS c,\n                {$oDbh->quoteIdentifier($prefix . 'banners', true)} AS b,\n                {$oDbh->quoteIdentifier($prefix . 'data_intermediate_ad', true)} AS dia\n            WHERE\n                cl.agencyid = " . DBC::makeLiteral($agencyId) . "\n                AND c.status = " . OA_ENTITY_STATUS_RUNNING . "\n                AND c.priority = " . $priority . "\n                AND cl.clientid = c.clientid\n                AND b.bannerid = dia.ad_id\n                AND b.campaignid = c.campaignid\n                AND dia.interval_end >= '" . $oDate->format('%Y-%m-%d %H:%M:%S') . "'\n            GROUP BY\n                c.campaignid";
     $rs = DBC::NewRecordSet($query);
     if (PEAR::isError($rs)) {
         return false;
     }
     return $rs->getAll(array(), 'campaignid');
 }

 function _checkDataTable($table, $cEntries, $mapCImpressions, $mapCClicks)
 {
     $table = $this->oDbh->quoteIdentifier($this->getPrefix() . $table, true);
     $rsDsah = DBC::NewRecordSet("SELECT * FROM {$table}");
     $this->assertTrue($rsDsah->find());
     $this->assertEqual($cEntries, $rsDsah->getRowCount());
     while ($rsDsah->fetch()) {
         $bannerId = $rsDsah->get('ad_id');
         $zoneId = $rsDsah->get('zone_id');
         $day = $rsDsah->get('day');
         $hour = $rsDsah->get('hour');
         $this->assertEqual($mapCImpressions[$bannerId][$zoneId][$day][$hour], $rsDsah->get('impressions'));
         $this->assertEqual($mapCClicks[$bannerId][$zoneId][$day][$hour], $rsDsah->get('clicks'));
     }
 }

 /**
  * A method to retrieve all advertisers where the advertiser name
  * contains a given string. Also returns any advertiser where the
  * advertiser ID equals the given keyword, should the keyword be
  * numeric.
  *
  * @param $keyword  string  Keyword to look for
  * @param $agencyId integer Limit results to advertisers owned by a given Agency ID
  * @return RecordSet
  */
 function getClientByKeyword($keyword, $agencyId = null, $aIncludeSystemTypes = array())
 {
     // always add default type
     $aIncludeSystemTypes = array_merge(array(DataObjects_Clients::ADVERTISER_TYPE_DEFAULT), $aIncludeSystemTypes);
     foreach ($aIncludeSystemTypes as $k => $v) {
         $aIncludeSystemTypes[$k] = DBC::makeLiteral((int) $v);
     }
     $conf = $GLOBALS['_MAX']['CONF'];
     $whereClient = is_numeric($keyword) ? " OR c.clientid = {$keyword}" : '';
     $oDbh = OA_DB::singleton();
     $tableC = $oDbh->quoteIdentifier($this->getTablePrefix() . 'clients', true);
     $query = "\n            SELECT\n                c.clientid AS clientid,\n                c.clientname AS clientname\n            FROM\n                {$tableC} AS c\n            WHERE\n                (\n                    c.clientname LIKE " . DBC::makeLiteral('%' . $keyword . '%') . $whereClient . "\n                )\n                AND c.type IN (" . implode(',', $aIncludeSystemTypes) . ")";
     if ($agencyId !== null) {
         $query .= " AND c.agencyid=" . DBC::makeLiteral($agencyId);
     }
     return DBC::NewRecordSet($query);
 }

 function testMigrateData()
 {
     $this->initDatabase(129, array('config', 'preference'));
     $this->setupPanConfig();
     $migration = new Migration_129();
     $migration->init($this->oDbh, MAX_PATH . '/var/DB_Upgrade.test.log');
     $aValues = array('warn_limit_days' => 1);
     $migration->migrateData();
     $table = $this->oDbh->quoteIdentifier($this->getPrefix() . 'preference');
     $rsPreference = DBC::NewRecordSet("SELECT * from {$table}");
     $rsPreference->find();
     $this->assertTrue($rsPreference->fetch());
     $aDataPreference = $rsPreference->toArray();
     foreach ($aValues as $column => $value) {
         $this->assertEqual($value, $aDataPreference[$column]);
     }
     $this->restorePanConfig();
 }

 function getTrackerVariables($zoneid, $affiliateid, $selectForAffiliate)
 {
     $prefix = $this->getTablePrefix();
     $oDbh = OA_DB::singleton();
     $tableZ = $oDbh->quoteIdentifier($prefix . 'zones', true);
     $tableAza = $oDbh->quoteIdentifier($prefix . 'ad_zone_assoc', true);
     $tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
     $tableCt = $oDbh->quoteIdentifier($prefix . 'campaigns_trackers', true);
     $tableT = $oDbh->quoteIdentifier($prefix . 'trackers', true);
     $tableV = $oDbh->quoteIdentifier($prefix . 'variables', true);
     $tableVp = $oDbh->quoteIdentifier($prefix . 'variable_publisher', true);
     $whereZoneAffiliate = empty($zoneid) ? "z.affiliateid = " . DBC::makeLiteral($affiliateid) : "z.zoneid = " . DBC::makeLiteral($zoneid);
     $query = "\n            SELECT DISTINCT\n                v.variableid AS variable_id,\n                v.name AS variable_name,\n                v.description AS variable_description,\n                t.trackerid AS tracker_id,\n                t.trackername AS tracker_name,\n                t.description AS tracker_description\n            FROM\n                {$tableAza} aza JOIN\n                {$tableZ} z ON (aza.zone_id = z.zoneid) JOIN\n                {$tableB} b ON (aza.ad_id = b.bannerid) JOIN\n                {$tableCt} ct USING (campaignid) JOIN\n                {$tableT} t USING (trackerid) JOIN\n                {$tableV} v USING (trackerid) LEFT JOIN\n                {$tableVp} vp ON (vp.variable_id = v.variableid AND vp.publisher_id = z.affiliateid)\n            WHERE\n                {$whereZoneAffiliate} AND\n                v.datatype = 'numeric'\n            ";
     if ($selectForAffiliate) {
         $query .= " AND (v.hidden = 'f' OR vp.visible = 1)";
     }
     return DBC::NewRecordSet($query);
 }

 function migrateAcls()
 {
     $tableAcls = $this->getPrefix() . "acls";
     $sql = "SELECT * FROM {$tableAcls}";
     $rsAcls = DBC::NewRecordSet($sql);
     if (!$rsAcls->find()) {
         return false;
     }
     $aUpdates = array();
     while ($rsAcls->fetch()) {
         $bannerid = $rsAcls->get('bannerid');
         $executionorder = $rsAcls->get('executionorder');
         $oldType = $rsAcls->get('type');
         if (!isset($this->aAclsTypes[$oldType])) {
             $this->_logError("Unknown acls type: {$oldType}");
             return false;
         }
         $type = $this->aAclsTypes[$oldType];
         $oldComparison = $rsAcls->get('comparison');
         $oldData = $rsAcls->get('data');
         $oPlugin =& $this->_getDeliveryLimitationPlugin($type);
         if (!$oPlugin) {
             $this->_logError("Can't find code for delivery limitation plugin: {$type}.");
             return false;
         }
         $aNewAclsData = $oPlugin->getUpgradeFromEarly($oldComparison, $oldData);
         $comparison = $aNewAclsData['op'];
         $data = $aNewAclsData['data'];
         $aUpdates[] = "UPDATE {$tableAcls} SET type = '{$type}', comparison = '{$comparison}', data = '{$data}'\n\t        WHERE bannerid = {$bannerid}\n\t        AND executionorder = {$executionorder}";
     }
     foreach ($aUpdates as $update) {
         $result = $this->oDBH->exec($update);
         if (PEAR::isError($result)) {
             $this->_logError("Couldn't execute update: {$update}");
             return false;
         }
     }
     return true;
 }

 function testMigrateData()
 {
     $prefix = $this->getPrefix();
     $this->initDatabase(108, array('config', 'preference'));
     $this->setupPanConfig();
     $migration = new Migration_108();
     $migration->init($this->oDbh, MAX_PATH . '/var/DB_Upgrade.test.log');
     $aValues = array('gui_show_parents' => 't', 'updates_enabled' => 'f', 'client_welcome_msg' => '', 'updates_cache' => '');
     $sql = OA_DB_Sql::sqlForInsert('config', $aValues);
     $this->oDbh->exec($sql);
     $aValues += array('warn_admin' => 't', 'warn_limit' => '100');
     $migration->migrateData();
     $table = $this->oDbh->quoteIdentifier($prefix . 'preference', true);
     $rsPreference = DBC::NewRecordSet("SELECT * from {$table}");
     $rsPreference->find();
     $this->assertTrue($rsPreference->fetch());
     $aDataPreference = $rsPreference->toArray();
     foreach ($aValues as $column => $value) {
         $this->assertEqual($value, $aDataPreference[$column]);
     }
     $this->restorePanConfig();
 }