PHP Zend_Db_Adapter_Abstract::query方法代码示例

 /**
  * setAutocommit
  * 
  * @param Zend_Db_Adapter_Abstract $adapter
  * @param boolean $on
  */
 public static function setAutocommit($adapter, $on)
 {
     if ($on) {
         $adapter->query('SET AUTOCOMMIT=1;');
     } else {
         $adapter->query('SET AUTOCOMMIT=0;');
     }
 }

 /**
  * Should handle execution of the task, taking as much (optional) parameters as needed
  *
  * The parameters should be optional and failing to provide them should be handled by
  * the task
  *
  * @param int $patchLevel Only execute patches for this patchlevel
  */
 public function execute($patchLevel = null)
 {
     //Update the patchlevel only when we have executed at least one patch
     $batch = $this->getBatch();
     if ($batch->getCounter('executed')) {
         $this->db->query('INSERT IGNORE INTO gems__patch_levels (gpl_level, gpl_created) VALUES (?, CURRENT_TIMESTAMP)', $patchLevel);
     }
 }

 /**
  * Inserts a table row with specified data.
  *
  * @param mixed $table The table to insert data into.
  * @param array $bind Column-value pairs.
  * @return int The number of affected rows.
  */
 function insert($table, array $bind)
 {
     $sql = $this->insertSql($table, $bind);
     // execute the statement and return the number of affected rows
     $stmt = $this->wrappedAdapter->query($sql, array_values($bind));
     $result = $stmt->rowCount();
     return $result;
 }

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

 public function __construct()
 {
     $this->_oConfig = Zend_Registry::get(REGISTRY_CONFIG);
     $this->_oDBAdapter = Zend_Db::factory($this->_oConfig->db->adapter, $this->_oConfig->db->config->toArray());
     if ($this->_oConfig->db->adapter == 'PDO_MYSQL') {
         $this->_oDBAdapter->query("SET NAMES 'utf8'");
         $this->_oDBAdapter->query("SET CHARACTER SET utf8");
     }
     $this->_sPrefix = $this->_oConfig->db->prefix;
 }

 /**
  * import dump in database
  * @param $name
  * @param null $module
  * @throws Zend_Exception
  */
 public function import($name, $module = null)
 {
     $path = $this->getDumpsDirectoryPath($module);
     if (file_exists($path . DIRECTORY_SEPARATOR . $name)) {
         $dump = file_get_contents($path . DIRECTORY_SEPARATOR . $name);
         return $this->_db->query($dump);
     } else {
         throw new Zend_Exception("Dump file not found!");
     }
 }

 /**
  * Test the connection, and if needed, reconnect.
  *
  * @return void
  */
 protected function _test()
 {
     try {
         $this->_adapter->query('SELECT 1');
     } catch (Exception $e) {
         // there is an error, so we reconnect
         $this->_adapter = null;
         $this->_connect();
     }
 }

    /**
     * Code to run after deleting an associated attachment.
     */
    public function attachmentPostDelete(array $attachment, Zend_Db_Adapter_Abstract $db)
    {
        $db->query('
			UPDATE xf_custom_field_attachment
			SET attach_count = IF(attach_count > 0, attach_count - 1, 0)
			WHERE field_attachment_id = ?
		', $attachment['content_id']);
        $db->query('
            DELETE FROM xf_custom_field_attachment
            WHERE field_attachment_id = ? AND attach_count = 0
        ', $attachment['content_id']);
    }

 /**
  *
  * @param string $tableName
  * @param array $config
  * @return boolean 
  */
 private function setConfigTable($tableName, $config)
 {
     $sql = "SELECT utc.comments \n                  FROM user_tab_comments utc\n                 WHERE utc.table_name = :tableName\n                   AND utc.TABLE_TYPE = 'TABLE'";
     $rows = $this->_dbAdapter->fetchAll($sql, array('tableName' => $tableName));
     if (count($rows) > 0) {
         $comments = $rows[0]['comments'];
         $strZF = substr($comments, strpos($comments, '<?zf'), strpos($data, 'zf?>') + 4);
         $comments = str_replace($strZF, '', $comments);
         $comments .= '<?zf ' . Zend_Json::decode($config) . ' zf?>';
         $sql = "comment on table " . $tableName . "  is :comment ";
         $this->_dbAdapter->query($sql, array('comment' => $comments));
         return true;
     }
 }

 protected function _bootstrap(array $config)
 {
     if ($this->_sourceDb) {
         // already run
         return;
     }
     @set_time_limit(0);
     $this->_config = $config;
     $this->_sourceDb = Zend_Db::factory('mysqli', array('host' => $config['db']['host'], 'port' => $config['db']['port'], 'username' => $config['db']['username'], 'password' => $config['db']['password'], 'dbname' => $config['db']['dbname'], 'charset' => str_replace('-', '', $config['db']['charset'])));
     if (empty($config['db']['charset'])) {
         $this->_sourceDb->query('SET character_set_results = NULL');
     }
     $this->_prefix = preg_replace('/[^a-z0-9_]/i', '', $config['db']['prefix']);
     $this->_charset = $config['charset'];
 }

 /**
  * update foreign key values
  * 
  * @param string $_mode create|update
  * @param Tinebase_Record_Interface $_record
  */
 protected function _updateForeignKeys($_mode, Tinebase_Record_Interface $_record)
 {
     if (!empty($this->_foreignTables)) {
         foreach ($this->_foreignTables as $modelName => $join) {
             if (!(isset($join['field']) || array_key_exists('field', $join))) {
                 continue;
             }
             $idsToAdd = array();
             $idsToRemove = array();
             if (!empty($_record->{$modelName})) {
                 $idsToAdd = Tinebase_Record_RecordSet::getIdsFromMixed($_record->{$modelName});
             }
             $transactionId = Tinebase_TransactionManager::getInstance()->startTransaction(Tinebase_Core::getDb());
             if ($_mode == 'update') {
                 $select = $this->_db->select();
                 $select->from(array($join['table'] => $this->_tablePrefix . $join['table']), array($join['field']))->where($this->_db->quoteIdentifier($join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId());
                 Tinebase_Backend_Sql_Abstract::traitGroup($select);
                 $stmt = $this->_db->query($select);
                 $currentIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
                 $stmt->closeCursor();
                 $idsToRemove = array_diff($currentIds, $idsToAdd);
                 $idsToAdd = array_diff($idsToAdd, $currentIds);
             }
             if (!empty($idsToRemove)) {
                 $where = '(' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['joinOn']) . ' = ?', $_record->getId()) . ' AND ' . $this->_db->quoteInto($this->_db->quoteIdentifier($this->_tablePrefix . $join['table'] . '.' . $join['field']) . ' IN (?)', $idsToRemove) . ')';
                 $this->_db->delete($this->_tablePrefix . $join['table'], $where);
             }
             foreach ($idsToAdd as $id) {
                 $recordArray = array($join['joinOn'] => $_record->getId(), $join['field'] => $id);
                 $this->_db->insert($this->_tablePrefix . $join['table'], $recordArray);
             }
             Tinebase_TransactionManager::getInstance()->commitTransaction($transactionId);
         }
     }
 }

 /**
  * Retrive table partical data SQL insert
  *
  * @param string $tableName
  * @param int $count
  * @param int $offset
  * @return string
  */
 public function getTableDataSql($tableName, $count, $offset = 0)
 {
     $sql = null;
     $quotedTableName = $this->_read->quoteIdentifier($tableName);
     $select = $this->_read->select()->from($tableName)->limit($count, $offset);
     $query = $this->_read->query($select);
     while ($row = $query->fetch()) {
         if (is_null($sql)) {
             $sql = 'INSERT INTO ' . $quotedTableName . ' VALUES ';
         } else {
             $sql .= ',';
         }
         //$sql .= $this->_read->quoteInto('(?)', $row);
         $rowData = array();
         foreach ($row as $v) {
             if (is_null($v)) {
                 $value = 'NULL';
             } elseif (is_numeric($v) && $v == intval($v)) {
                 $value = $v;
             } else {
                 $value = $this->_read->quoteInto('?', $v);
             }
             $rowData[] = $value;
         }
         $sql .= '(' . join(',', $rowData) . ')';
     }
     if (!is_null($sql)) {
         $sql .= ';' . "\n";
     }
     return $sql;
 }

 /**
  * @param string|int $eId
  * @param string|int $pId
  * @param string|int $aId
  * @return void
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  * @SuppressWarnings(PHPMD.NPathComplexity)
  * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
  * @SuppressWarnings(PHPMD.UnusedLocalVariable)
  * @SuppressWarnings(PHPMD.ExitExpression)
  */
 public function moveNodes($eId, $pId, $aId = 0)
 {
     $eInfo = $this->getNodeInfo($eId);
     if ($pId != 0) {
         $pInfo = $this->getNodeInfo($pId);
     }
     if ($aId != 0) {
         $aInfo = $this->getNodeInfo($aId);
     }
     $level = $eInfo[$this->_level];
     $leftKey = $eInfo[$this->_left];
     $rightKey = $eInfo[$this->_right];
     if ($pId == 0) {
         $levelUp = 0;
     } else {
         $levelUp = $pInfo[$this->_level];
     }
     $rightKeyNear = 0;
     $leftKeyNear = 0;
     if ($pId == 0) {
         //move to root
         $rightKeyNear = $this->_db->fetchOne('SELECT MAX(' . $this->_right . ') FROM ' . $this->_table);
     } elseif ($aId != 0 && $pId == $eInfo[$this->_pid]) {
         // if we have after ID
         $rightKeyNear = $aInfo[$this->_right];
         $leftKeyNear = $aInfo[$this->_left];
     } elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) {
         // if we do not have after ID
         $rightKeyNear = $pInfo[$this->_left];
     } elseif ($pId != $eInfo[$this->_pid]) {
         $rightKeyNear = $pInfo[$this->_right] - 1;
     }
     $skewLevel = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
     $skewTree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
     echo "alert('" . $rightKeyNear . "');";
     if ($rightKeyNear > $rightKey) {
         // up
         echo "alert('move up');";
         $skewEdit = $rightKeyNear - $leftKey + 1;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_right . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' < ' . $eInfo[$this->_left] . ', ' . $this->_right . ' + ' . $skewTree . ', ' . $this->_right . ')), ' . $this->_level . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_left . ' = IF(' . $this->_left . ' >= ' . $eInfo[$this->_left] . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKeyNear . ', ' . $this->_left . ' + ' . $skewTree . ', ' . $this->_left . '))' . ' WHERE ' . $this->_right . ' > ' . $rightKeyNear . ' AND ' . $this->_left . ' < ' . $eInfo[$this->_right];
     } elseif ($rightKeyNear < $rightKey) {
         // down
         echo "alert('move down');";
         $skewEdit = $rightKeyNear - $leftKey + 1 - $skewTree;
         $sql = 'UPDATE ' . $this->_table . ' SET ' . $this->_left . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_left . ' + ' . $skewEdit . ', IF(' . $this->_left . ' > ' . $rightKey . ', ' . $this->_left . ' - ' . $skewTree . ', ' . $this->_left . ')), ' . $this->_level . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_level . ' + ' . $skewLevel . ', ' . $this->_level . '), ' . $this->_right . ' = IF(' . $this->_right . ' <= ' . $rightKey . ', ' . $this->_right . ' + ' . $skewEdit . ', IF(' . $this->_right . ' <= ' . $rightKeyNear . ', ' . $this->_right . ' - ' . $skewTree . ', ' . $this->_right . '))' . ' WHERE ' . $this->_right . ' > ' . $leftKey . ' AND ' . $this->_left . ' <= ' . $rightKeyNear;
     }
     $this->_db->beginTransaction();
     try {
         $this->_db->query($sql);
         $this->_db->commit();
     } catch (\Exception $e) {
         $this->_db->rollBack();
         echo $e->getMessage();
         echo "<br>\r\n";
         echo $sql;
         echo "<br>\r\n";
         exit;
     }
     echo "alert('node added')";
 }

 /**
  * Create Schema
  *
  * @throws \InvalidArgumentException
  * @return AdapterInterface
  */
 public function createSchema()
 {
     $sql = $this->createStatement;
     if ($sql === null) {
         switch (get_class($this->adapter)) {
             case 'Zend_Db_Adapter_Pdo_Mssql':
                 $createStatement = static::MSSQL_CREATE_STATEMENT;
                 break;
             case 'Zend_Db_Adapter_Pdo_Mysql':
             case 'Zend_Db_Adapter_Mysqli':
                 $createStatement = static::MYSQL_CREATE_STATEMENT;
                 break;
             case 'Zend_Db_Adapter_Pdo_Pgsql':
                 $createStatement = static::PGSQL_CREATE_STATEMENT;
                 break;
             case 'Zend_Db_Adapter_Pdo_Sqlite':
                 $createStatement = static::SQLITE_CREATE_STATEMENT;
                 break;
             default:
                 throw new \InvalidArgumentException('Please provide a valid SQL statement for your database system in the config file as phpmig.createStatement');
                 break;
         }
         $sql = sprintf($createStatement, $this->tableName);
     }
     try {
         $this->adapter->query($sql);
     } catch (\Zend_Db_Statement_Exception $exception) {
         throw new \InvalidArgumentException('Please provide a valid SQL statement for your database system in the config file as phpmig.createStatement');
     }
     return $this;
 }