PHP Zend_Db_Adapter_Abstract::select方法代码示例

 public function load($printQuery = false, $logQuery = false)
 {
     $this->_select = $this->_read->select();
     $entityTable = $this->getEntity()->getEntityTable();
     $paidTable = $this->getAttribute('grand_total')->getBackend()->getTable();
     $idField = $this->getEntity()->getIdFieldName();
     $this->getSelect()->from(array('sales' => $entityTable), array('store_id', 'lifetime' => 'sum(sales.base_grand_total)', 'avgsale' => 'avg(sales.base_grand_total)', 'num_orders' => 'count(sales.base_grand_total)'))->where('sales.entity_type_id=?', $this->getEntity()->getTypeId())->group('sales.store_id');
     if ($this->_customer instanceof Mage_Customer_Model_Customer) {
         $this->getSelect()->where('sales.customer_id=?', $this->_customer->getId());
     }
     $this->printLogQuery($printQuery, $logQuery);
     try {
         $values = $this->_read->fetchAll($this->getSelect()->__toString());
     } catch (Exception $e) {
         $this->printLogQuery(true, true, $this->getSelect()->__toString());
         throw $e;
     }
     $stores = Mage::getResourceModel('core/store_collection')->setWithoutDefaultFilter()->load()->toOptionHash();
     if (!empty($values)) {
         foreach ($values as $v) {
             $obj = new Varien_Object($v);
             $storeName = isset($stores[$obj->getStoreId()]) ? $stores[$obj->getStoreId()] : null;
             $this->_items[$v['store_id']] = $obj;
             $this->_items[$v['store_id']]->setStoreName($storeName);
             $this->_items[$v['store_id']]->setAvgNormalized($obj->getAvgsale() * $obj->getNumOrders());
             foreach ($this->_totals as $key => $value) {
                 $this->_totals[$key] += $obj->getData($key);
             }
         }
         if ($this->_totals['num_orders']) {
             $this->_totals['avgsale'] = $this->_totals['lifetime'] / $this->_totals['num_orders'];
         }
     }
     return $this;
 }

 /**
  * Creates a model for getModel(). Called only for each new $action.
  *
  * The parameters allow you to easily adapt the model to the current action. The $detailed
  * parameter was added, because the most common use of action is a split between detailed
  * and summarized actions.
  *
  * @param boolean $detailed True when the current action is not in $summarizedActions.
  * @param string $action The current action.
  * @return \MUtil_Model_ModelAbstract
  */
 protected function createModel($detailed, $action)
 {
     $fields = array();
     // Export all
     if ('export' === $action) {
         $detailed = true;
     }
     $organizations = $this->util->getDbLookup()->getOrganizations();
     $fields[] = 'gtr_track_name';
     $sql = "CASE WHEN gtr_organizations LIKE '%%|%s|%%' THEN 1 ELSE 0 END";
     foreach ($organizations as $orgId => $orgName) {
         $fields['O' . $orgId] = new \Zend_Db_Expr(sprintf($sql, $orgId));
     }
     $fields['total'] = new \Zend_Db_Expr("(LENGTH(gtr_organizations) - LENGTH(REPLACE(gtr_organizations, '|', ''))-1)");
     $fields[] = 'gtr_id_track';
     $select = $this->db->select();
     $select->from('gems__tracks', $fields);
     $model = new \MUtil_Model_SelectModel($select, 'track-verview');
     $model->setKeys(array('gtr_id_track'));
     $model->resetOrder();
     $model->set('gtr_track_name', 'label', $this->_('Track name'));
     $model->set('total', 'label', $this->_('Total'));
     $model->setOnTextFilter('total', array($this, 'noTextFilter'));
     foreach ($organizations as $orgId => $orgName) {
         $model->set('O' . $orgId, 'label', $orgName, 'tdClass', 'rightAlign', 'thClass', 'rightAlign');
         $model->setOnTextFilter('O' . $orgId, array($this, 'noTextFilter'));
         if ($action !== 'export') {
             $model->set('O' . $orgId, 'formatFunction', array($this, 'formatCheckmark'));
         }
     }
     // \MUtil_Model::$verbose = true;
     return $model;
 }

 public function __construct()
 {
     // automatically define table name (if not defined)
     if (!isset($this->_name)) {
         // delete prefix
         $className = preg_replace('`^.*Model_Table_`', '', get_class($this));
         // add slash after upper case letters
         $name = preg_replace('`[^_]([A-Z])`', '_$1', $className);
         // table name in lower case
         $this->_name = strtolower($name);
     }
     // load tables structure
     if (is_null(self::$_structure)) {
         self::$_structure = Days_Config::load('database')->get();
     }
     // check table definition
     if (!array_key_exists($this->_name, self::$_structure)) {
         throw new Days_Exception("Not defined table structure for `{$this->_name}`");
     }
     // set adapter for all tables
     if (!$this->_db) {
         $this->_db = Days_Db::factory();
         $this->_select = $this->_db->select();
     }
 }

 public function loadByName(Mage_Directory_Model_Region $region, $regionName, $countryId)
 {
     $locale = Mage::app()->getLocale()->getLocaleCode();
     $select = $this->_read->select()->from(array('region' => $this->_regionTable))->where('region.country_id=?', $countryId)->where('region.default_name=?', $regionName)->join(array('rname' => $this->_regionNameTable), 'rname.region_id=region.region_id AND rname.locale=\'' . $locale . '\'', array('name'));
     $region->setData($this->_read->fetchRow($select));
     return $this;
 }

 public function getTableDataDump($tableName, $step = 100)
 {
     $sql = '';
     if ($this->_read) {
         $quotedTableName = $this->_read->quoteIdentifier($tableName);
         $colunms = $this->_read->fetchRow('SELECT * FROM ' . $quotedTableName . ' LIMIT 1');
         if ($colunms) {
             $arrSql = array();
             $colunms = array_keys($colunms);
             $quote = $this->_read->getQuoteIdentifierSymbol();
             $sql = 'INSERT INTO ' . $quotedTableName . ' (' . $quote . implode($quote . ', ' . $quote, $colunms) . $quote . ')';
             $sql .= ' VALUES ';
             $startRow = 0;
             $select = $this->_read->select();
             $select->from($tableName)->limit($step, $startRow);
             while ($data = $this->_read->fetchAll($select)) {
                 $dataSql = array();
                 foreach ($data as $row) {
                     $dataSql[] = $this->_read->quoteInto('(?)', $row);
                 }
                 $arrSql[] = $sql . implode(', ', $dataSql) . ';';
                 $startRow += $step;
                 $select->limit($step, $startRow);
             }
             $sql = implode("\n", $arrSql) . "\n";
         }
     }
     return $sql;
 }

 function find($parameters)
 {
     $select = $this->db->select()->from('user');
     foreach ($parameters as $field => $value) {
         $select->where("{$field}=?", $value);
     }
     return $select->query()->fetch();
 }

 public function setConnection($conn)
 {
     if (!$conn instanceof Zend_Db_Adapter_Abstract) {
         throw new Zend_Exception('dbModel read resource does not implement Zend_Db_Adapter_Abstract');
     }
     $this->_conn = $conn;
     $this->_select = $this->_conn->select();
 }

 /**
  * get version number of a given table
  * version is stored in database table "applications_tables"
  *
  * @param Tinebase_Application application
  * @return int version number 
  */
 public function getTableVersion($_tableName)
 {
     $select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'application_tables')->where($this->_db->quoteIdentifier('name') . ' = ?', $_tableName)->orwhere($this->_db->quoteIdentifier('name') . ' = ?', SQL_TABLE_PREFIX . $_tableName);
     $stmt = $select->query();
     $rows = $stmt->fetchAll();
     //if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . $select->__toString());
     $result = isset($rows[0]['version']) ? $rows[0]['version'] : 0;
     return $result;
 }

 /**
  * Fetch the schema version of the current database
  *
  * @return string
  */
 public final function getSchemaVersion()
 {
     if (!in_array('schema_info', $this->dbAdapter->listTables())) {
         $this->createTable('schema_info', array('primary' => false), array(array('version', 'string')));
         $this->dbAdapter->insert('schema_info', array('version' => '00000000000000'));
         return '00000000000000';
     }
     return $this->dbAdapter->fetchOne($this->dbAdapter->select()->from('schema_info', 'version')->limit(1));
 }

 /**
  * @param $shadowPath
  * @param $newPath
  * @param $oldPath
  * @param $newShadowPath
  * @param $oldShadowPath
  */
 public function copyTreeByShadowPath($shadowPath, $newPath, $oldPath, $newShadowPath, $oldShadowPath)
 {
     $select = $this->_db->select()->from($this->_tablePrefix . $this->_tableName, array('path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(path, ?', $oldPath) . ', ?)', $newPath)), 'shadow_path' => new Zend_Db_Expr($this->_db->quoteInto($this->_db->quoteInto('REPLACE(shadow_path, ?', $oldShadowPath) . ', ?)', $newShadowPath)), 'record_id' => 'record_id', 'creation_time' => new Zend_Db_Expr('NOW()')))->where($this->_db->quoteInto($this->_db->quoteIdentifier('shadow_path') . ' like ?', $shadowPath . '/%'));
     $stmt = $this->_db->query($select);
     $entries = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
     foreach ($entries as $entry) {
         $entry['id'] = Tinebase_Record_Abstract::generateUID();
         $this->_db->insert($this->_tablePrefix . $this->_tableName, $entry);
     }
 }

 /**
  * return device for this user
  * 
  * @param  string  $userId
  * @param  string  $deviceId
  * @throws Syncope_Exception_NotFound
  * @return Syncope_Model_Device
  */
 public function getUserDevice($ownerId, $deviceId)
 {
     $select = $this->_db->select()->from($this->_tablePrefix . 'device')->where('owner_id = ?', $ownerId)->where('deviceid = ?', $deviceId);
     $stmt = $this->_db->query($select);
     $device = $stmt->fetchObject('Syncope_Model_Device');
     if (!$device instanceof Syncope_Model_IDevice) {
         throw new Syncope_Exception_NotFound('device not found');
     }
     return $device;
 }

 /**
  * Get all migrated version numbers
  *
  * @return array
  */
 public function fetchAll()
 {
     $select = $this->adapter->select();
     $select->from($this->tableName, 'version');
     $select->order('version ASC');
     $all = $this->adapter->fetchAll($select);
     return array_map(function ($v) {
         return $v['version'];
     }, $all);
 }

 /**
  * read syncstate from database 
  *
  * @param ActiceSync_Model_SyncState $_syncState
  * @return ActiceSync_Model_SyncState
  */
 public function get(ActiveSync_Model_SyncState $_syncState)
 {
     $select = $this->_db->select()->from(SQL_TABLE_PREFIX . 'acsync_synckey')->where('device_id = ?', $_syncState->device_id)->where('type = ?', $_syncState->type)->order('counter DESC')->limit(1);
     if (!empty($_syncState->counter)) {
         $select->where('counter = ?', $_syncState->counter);
     }
     $row = $this->_db->fetchRow($select);
     if (!$row) {
         throw new ActiveSync_Exception_SyncStateNotFound('syncState not found: ' . $select);
     }
     $result = new ActiveSync_Model_SyncState($row);
     return $result;
 }

 /**
  * Returns true if and only if $value meets the validation requirements
  *
  * If $value fails validation, then this method returns false, and
  * getMessages() will return an array of messages that explain why the
  * validation failed.
  *
  * @param  mixed $value
  * @return boolean
  * @throws \Zend_Valid_Exception If validation of $value is impossible
  */
 public function isValid($value)
 {
     if ($throttleSettings = $this->project->getAskThrottleSettings()) {
         // Prune the database for (very) old attempts
         $where = $this->db->quoteInto('gta_datetime < DATE_SUB(NOW(), INTERVAL ? second)', $throttleSettings['period'] * 20);
         $this->db->delete('gems__token_attempts', $where);
         // Retrieve the number of failed attempts that occurred within the specified window
         $select = $this->db->select();
         $select->from('gems__token_attempts', array(new \Zend_Db_Expr('COUNT(*) AS attempts'), new \Zend_Db_Expr('UNIX_TIMESTAMP(MAX(gta_datetime)) - UNIX_TIMESTAMP() AS last')))->where('gta_datetime > DATE_SUB(NOW(), INTERVAL ? second)', $throttleSettings['period']);
         $attemptData = $this->db->fetchRow($select);
         $remainingDelay = $attemptData['last'] + $throttleSettings['delay'];
         // \MUtil_Echo::track($throttleSettings, $attemptData, $remainingDelay);
         if ($attemptData['attempts'] > $throttleSettings['threshold'] && $remainingDelay > 0) {
             $this->logger->log("Possible token brute force attack, throttling for {$remainingDelay} seconds", \Zend_Log::ERR);
             $this->_messages = $this->translate->_('The server is currently busy, please wait a while and try again.');
             return false;
         }
     }
     // The pure token check
     if ($this->isValidToken($value)) {
         return true;
     }
     $max_length = $this->tracker->getTokenLibrary()->getLength();
     $this->db->insert('gems__token_attempts', array('gta_id_token' => substr($value, 0, $max_length), 'gta_ip_address' => $this->getRequest()->getClientIp()));
     return false;
 }

 /**
  * Checks if the user is allowed to login or is blocked
  *
  * An adapter authorizes and if the end resultis boolean, string or array
  * it is converted into a \Zend_Auth_Result.
  *
  * @return mixed \Zend_Auth_Adapter_Interface|\Zend_Auth_Result|boolean|string|array
  */
 protected function authorizeBlock()
 {
     try {
         $select = $this->db->select();
         $select->from('gems__user_login_attempts', array('UNIX_TIMESTAMP(gula_block_until) - UNIX_TIMESTAMP() AS wait'))->where('gula_block_until is not null')->where('gula_login = ?', $this->getLoginName())->where('gula_id_organization = ?', $this->getCurrentOrganizationId())->limit(1);
         // Not the first login
         if ($block = $this->db->fetchOne($select)) {
             if ($block > 0) {
                 $minutes = intval($block / 60) + 1;
                 // Report all is not well
                 return sprintf($this->plural('Your account is temporarily blocked, please wait a minute.', 'Your account is temporarily blocked, please wait %d minutes.', $minutes), $minutes);
             } else {
                 // Clean the block once it's past
                 $values['gula_failed_logins'] = 0;
                 $values['gula_last_failed'] = null;
                 $values['gula_block_until'] = null;
                 $where = $this->db->quoteInto('gula_login = ? AND ', $this->getLoginName());
                 $where .= $this->db->quoteInto('gula_id_organization = ?', $this->getCurrentOrganizationId());
                 $this->db->update('gems__user_login_attempts', $values, $where);
             }
         }
     } catch (\Zend_Db_Exception $e) {
         // Fall through as this does not work if the database upgrade did not run
         // \MUtil_Echo::r($e);
     }
     return true;
 }