PHP DatabaseConnection::sql_query方法代码示例

 /**
  * Runs the update.
  */
 public function main()
 {
     if ($this->pathCacheNeedsUpdates()) {
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathcache CHANGE cache_id uid int(11) NOT NULL');
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathcache DROP PRIMARY KEY');
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathcache MODIFY uid int(11) NOT NULL auto_increment primary key');
     }
 }

 /**
  * @return boolean TRUE on success, FALSE otherwise
  */
 public function release()
 {
     $res = $this->connection->sql_query(sprintf('SELECT RELEASE_LOCK("%s") AS res', $this->identifier));
     if ($res) {
         $releaseLockRes = $res->fetch_assoc();
         return (int) $releaseLockRes['res'] === 1;
     }
     return false;
 }

 /**
  * Sores the association for future use
  *
  * @param string $serverUrl Server URL
  * @param \Auth_OpenID_Association $association OpenID association
  * @return void
  */
 public function storeAssociation($serverUrl, $association)
 {
     /* @var $association \Auth_OpenID_Association */
     $this->databaseConnection->sql_query('START TRANSACTION');
     if ($this->doesAssociationExist($serverUrl, $association)) {
         $this->updateExistingAssociation($serverUrl, $association);
     } else {
         $this->storeNewAssociation($serverUrl, $association);
     }
     $this->databaseConnection->sql_query('COMMIT');
 }

    /**
     * @param $query
     * @return Tx_PtExtlist_Domain_DataBackend_DataSource_Typo3DataSource
     * @throws Exception
     */
    public function executeQuery($query)
    {
        try {
            $this->startTimeMeasure();
            $this->resource = $this->connection->sql_query($query);
            $this->stopTimeMeasure();
        } catch (Exception $e) {
            throw new Exception('Error while retrieving data from database using typo3 db object.<br> 
							     Error: ' . $e->getMessage() . ' sql_error says: ' . $this->connection->sql_error() . ' 1280400023<br><br>
							     SQL QUERY: <br>
							     </strong><hr>' . nl2br($query) . '<hr><strong>', 1280400023);
        }
        return $this;
    }

    /**
     * Stores the keywords from the current query to the database.
     *
     * @param string $keywords The current query's keywords
     * @return void
     */
    protected function storeKeywordsToDatabase($keywords)
    {
        $nextSequenceId = $this->getNextSequenceId();
        $this->database->sql_query('INSERT INTO tx_solr_last_searches (sequence_id, tstamp, keywords)
			VALUES (' . $nextSequenceId . ', ' . time() . ', ' . $this->database->fullQuoteStr($keywords, 'tx_solr_last_searches') . ')
			ON DUPLICATE KEY UPDATE tstamp = ' . time() . ', keywords = ' . $this->database->fullQuoteStr($keywords, 'tx_solr_last_searches'));
    }

 /**
  * Get column names
  *
  * @since 1.0.0
  *
  * @param $table
  *
  * @return array
  */
 protected function getColumnNames($table)
 {
     $table = preg_replace('/[^a-z0-9_]/', '', $table);
     if (isset($this->tableColumnCache[$table])) {
         return $this->tableColumnCache[$table];
     } else {
         $result = $this->databaseConnection->exec_SELECTgetSingleRow('*', $table, '1 = 1');
         if ($result) {
             $columnNames = array_keys($result);
             $this->tableColumnCache[$table] = $columnNames;
         } else {
             $columnNames = array();
             $result = $this->databaseConnection->sql_query('SELECT DATABASE();');
             $row = $this->databaseConnection->sql_fetch_row($result);
             $databaseName = $row[0];
             $this->databaseConnection->sql_free_result($result);
             $result = $this->databaseConnection->sql_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . $databaseName . "' AND TABLE_NAME = '" . $table . "';");
             while ($row = $this->databaseConnection->sql_fetch_row($result)) {
                 $columnNames[] = $row[0];
             }
             $this->databaseConnection->sql_free_result($result);
             $this->tableColumnCache[$table] = $columnNames;
         }
         return $columnNames;
     }
 }

 /**
  * Creates file identifier hashes for a single storage.
  *
  * @param ResourceStorage $storage The storage to update
  * @return array The executed database queries
  */
 protected function updateIdentifierHashesForStorage(ResourceStorage $storage)
 {
     $queries = array();
     if (!ExtensionManagementUtility::isLoaded('dbal')) {
         // if DBAL is not loaded, we're using MySQL and can thus use their
         // SHA1() function
         if ($storage->usesCaseSensitiveIdentifiers()) {
             $updateCall = 'SHA1(identifier)';
         } else {
             $updateCall = 'SHA1(LOWER(identifier))';
         }
         $queries[] = $query = sprintf('UPDATE sys_file SET identifier_hash = %s WHERE storage=%d', $updateCall, $storage->getUid());
         $this->db->sql_query($query);
         // folder hashes cannot be done with one call: so do it manually
         $files = $this->db->exec_SELECTgetRows('uid, storage, identifier', 'sys_file', sprintf('storage=%d AND folder_hash=""', $storage->getUid()));
         foreach ($files as $file) {
             $folderHash = $storage->hashFileIdentifier($storage->getFolderIdentifierFromFileIdentifier($file['identifier']));
             $queries[] = $query = $this->db->UPDATEquery('sys_file', 'uid=' . $file['uid'], array('folder_hash' => $folderHash));
             $this->db->sql_query($query);
         }
     } else {
         // manually hash the identifiers when using DBAL
         $files = $this->db->exec_SELECTgetRows('uid, storage, identifier', 'sys_file', sprintf('storage=%d AND identifier_hash=""', $storage->getUid()));
         foreach ($files as $file) {
             $hash = $storage->hashFileIdentifier($file['identifier']);
             $folderHash = $storage->hashFileIdentifier($storage->getFolderIdentifierFromFileIdentifier($file['identifier']));
             $queries[] = $query = $this->db->UPDATEquery('sys_file', 'uid=' . $file['uid'], array('identifier_hash' => $hash, 'folder_hash' => $folderHash));
             $this->db->sql_query($query);
         }
     }
     return $queries;
 }

 /**
  * Checks if the primary key needs updates (this is something that TYPO3
  * sql parser fails to do for years) and does necessary changes.
  *
  * @return void
  */
 protected function checkAndUpdatePathCachePrimaryKey()
 {
     if ($this->pathCacheNeedsUpdates()) {
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathdata CHANGE cache_id uid int(11) NOT NULL');
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathdata DROP PRIMARY KEY');
         $this->databaseConnection->sql_query('ALTER TABLE tx_realurl_pathdata MODIFY uid int(11) NOT NULL auto_increment PRIMARY KEY');
     }
 }

 /**
  * Remove dummy record and drop field
  *
  * @return void
  */
 private function dropDummyField()
 {
     $sql = 'ALTER TABLE %s DROP COLUMN is_dummy_record';
     foreach ($this->tables as $table) {
         $_sql = sprintf($sql, $table);
         $this->database->sql_query($_sql);
     }
 }

 /**
  * @test
  */
 public function importingExtension()
 {
     $this->importExtensions(array('extbase'));
     /** @var mysqli_result|resource $res */
     $res = $this->db->sql_query('show tables');
     $rows = $this->db->sql_num_rows($res);
     self::assertNotSame(0, $rows);
 }

 /**
  * Limits amount of records in the table. This does not run often.
  * Records are removed in the uid order (oldest first). This is not a true
  * clean up, which would be based on the last access timestamp but good
  * enough to maintain performance.
  *
  * @param string $tableName
  * @return bool
  */
 protected function limitTableRecords($tableName)
 {
     $cleanedUp = false;
     if (mt_rand(0, mt_getrandmax()) % 5 == 0) {
         $this->databaseConnection->sql_query('DELETE FROM ' . $tableName . ' WHERE uid <= (SELECT t2.uid FROM (SELECT uid FROM ' . $tableName . ' ORDER BY uid DESC LIMIT ' . self::$maximumNumberOfRecords . ',1) t2)');
         $cleanedUp = $this->databaseConnection->sql_affected_rows() > 0;
     }
     return $cleanedUp;
 }

 /**
  * Removes existing relations from the mm table from the database
  */
 protected function deleteExistingRelations()
 {
     $deleteQuery = $this->typo3Db->DELETEquery($this->table, $this->uidLocalField . '=' . $this->localUid);
     $this->utilityFuncs->debugMessage('sql_request', array($deleteQuery));
     $deleteResult = $this->typo3Db->sql_query($deleteQuery);
     if (!$deleteResult) {
         $this->utilityFuncs->throwException('Error in SQL query for deleting existing relations: ' . $this->typo3Db->sql_error());
     }
 }

 /**
  * Fetches the UIDs of all maximal versions for all extensions.
  * This is done by doing a LEFT JOIN to itself ("a" and "b") and comparing
  * both integer_version fields.
  *
  * @param int $repositoryUid
  * @return array
  */
 protected function fetchMaximalVersionsForAllExtensions($repositoryUid)
 {
     $queryResult = $this->databaseConnection->sql_query('SELECT a.uid AS uid ' . 'FROM ' . self::TABLE_NAME . ' a ' . 'LEFT JOIN ' . self::TABLE_NAME . ' b ON a.repository = b.repository AND a.extension_key = b.extension_key AND a.integer_version < b.integer_version ' . 'WHERE a.repository = ' . (int) $repositoryUid . ' AND b.extension_key IS NULL ' . 'ORDER BY a.uid');
     $extensionUids = array();
     while ($row = $this->databaseConnection->sql_fetch_assoc($queryResult)) {
         $extensionUids[] = $row['uid'];
     }
     $this->databaseConnection->sql_free_result($queryResult);
     return $extensionUids;
 }

 /**
  * Check if there are still resources left for the process with the given id
  * Used to determine timeouts and to ensure a proper cleanup if there's a timeout
  *
  * @param  string  identification string for the process
  * @return boolean determines if the process is still active / has resources
  */
 function CLI_checkIfProcessIsActive($pid)
 {
     $ret = false;
     $this->db->sql_query('BEGIN');
     $res = $this->db->exec_SELECTquery('process_id,active,ttl', 'tx_crawler_process', 'process_id = \'' . $pid . '\'  AND deleted=0', '', 'ttl', '0,1');
     if ($row = $this->db->sql_fetch_assoc($res)) {
         $ret = intVal($row['active']) == 1;
     }
     $this->db->sql_query('COMMIT');
     return $ret;
 }

 /**
  * Add is_dummy_record record and create dummy record
  *
  * @return void
  */
 private function prepareTables()
 {
     $sql = 'ALTER TABLE %s ADD is_dummy_record tinyint(1) unsigned DEFAULT \'0\' NOT NULL';
     foreach ($this->tables as $table) {
         $_sql = sprintf($sql, $table);
         $this->database->sql_query($_sql);
     }
     $values = array('title' => $this->getUniqueId('title'), 'l10n_diffsource' => '', 'description' => '', 'is_dummy_record' => 1);
     $this->database->exec_INSERTquery('sys_category', $values);
     $this->categoryUid = $this->database->sql_insert_id();
 }