PHP Connection::executeQuery方法代码示例

 /**
  * @brief drop a table
  * @param string $tableName the table to drop
  */
 public function dropTable($tableName)
 {
     $sm = $this->conn->getSchemaManager();
     $fromSchema = $sm->createSchema();
     $toSchema = clone $fromSchema;
     $toSchema->dropTable($tableName);
     $sql = $fromSchema->getMigrateToSql($toSchema, $this->conn->getDatabasePlatform());
     $this->conn->executeQuery($sql);
 }

 /**
  * Gets the users for a preference
  * @param string $app
  * @param string $key
  * @param string $value
  * @return array
  */
 public function getUsersForValue($app, $key, $value)
 {
     $users = array();
     $query = 'SELECT `userid` ' . ' FROM `*PREFIX*preferences` ' . ' WHERE `appid` = ? AND `configkey` = ? AND ';
     if (\OC_Config::getValue('dbtype', 'sqlite') === 'oci') {
         //FIXME oracle hack: need to explicitly cast CLOB to CHAR for comparison
         $query .= ' to_char(`configvalue`)= ?';
     } else {
         $query .= ' `configvalue` = ?';
     }
     $result = $this->conn->executeQuery($query, array($app, $key, $value));
     while ($row = $result->fetch()) {
         $users[] = $row['userid'];
     }
     return $users;
 }

 /**
  * get multiply values, either the app or key can be used as wildcard by setting it to false
  *
  * @param string|false $app
  * @param string|false $key
  * @return array|false
  */
 public function getValues($app, $key)
 {
     if (($app !== false) == ($key !== false)) {
         return false;
     }
     if ($app !== false) {
         return $this->getAppValues($app);
     } else {
         $query = 'SELECT `configvalue`, `appid` FROM `*PREFIX*appconfig` WHERE `configkey` = ?';
         $result = $this->conn->executeQuery($query, array($key));
         $values = array();
         while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
             $values[$row['appid']] = $row['configvalue'];
         }
         return $values;
     }
 }

    /**
     * @brief Resynchronizes all sequences of a database after using INSERTs
     *        without leaving out the auto-incremented column.
     * @param \OC\DB\Connection $conn
     * @return null
     */
    public function resynchronizeDatabaseSequences(Connection $conn)
    {
        $databaseName = $conn->getDatabase();
        $conn->getConfiguration()->setFilterSchemaAssetsExpression('/^' . $this->config->getSystemValue('dbtableprefix', 'oc_') . '/');
        foreach ($conn->getSchemaManager()->listSequences() as $sequence) {
            $sequenceName = $sequence->getName();
            $sqlInfo = 'SELECT table_schema, table_name, column_name
				FROM information_schema.columns
				WHERE column_default = ? AND table_catalog = ?';
            $sequenceInfo = $conn->fetchAssoc($sqlInfo, array("nextval('{$sequenceName}'::regclass)", $databaseName));
            $tableName = $sequenceInfo['table_name'];
            $columnName = $sequenceInfo['column_name'];
            $sqlMaxId = "SELECT MAX({$columnName}) FROM {$tableName}";
            $sqlSetval = "SELECT setval('{$sequenceName}', ({$sqlMaxId}))";
            $conn->executeQuery($sqlSetval);
        }
    }

 /**
  * Gets the preference for an array of users
  * @param string $app
  * @param string $key
  * @param array $users
  * @return array Mapped values: userid => value
  */
 public function getValueForUsers($app, $key, $users)
 {
     if (empty($users) || !is_array($users)) {
         return array();
     }
     $chunked_users = array_chunk($users, 50, true);
     $placeholders_50 = implode(',', array_fill(0, 50, '?'));
     $userValues = array();
     foreach ($chunked_users as $chunk) {
         $queryParams = $chunk;
         array_unshift($queryParams, $key);
         array_unshift($queryParams, $app);
         $placeholders = sizeof($chunk) == 50 ? $placeholders_50 : implode(',', array_fill(0, sizeof($chunk), '?'));
         $query = 'SELECT `userid`, `configvalue` ' . ' FROM `*PREFIX*preferences` ' . ' WHERE `appid` = ? AND `configkey` = ?' . ' AND `userid` IN (' . $placeholders . ')';
         $result = $this->conn->executeQuery($query, $queryParams);
         while ($row = $result->fetch()) {
             $userValues[$row['userid']] = $row['configvalue'];
         }
     }
     return $userValues;
 }

 /**
  * Converts legacy home storage ids in the format
  * "local::/data/dir/path/userid/" to the new format "home::userid"
  */
 public function run()
 {
     // only run once
     if ($this->config->getAppValue('core', 'repairlegacystoragesdone') === 'yes') {
         return;
     }
     $dataDir = $this->config->getSystemValue('datadirectory', \OC::$SERVERROOT . '/data/');
     $dataDir = rtrim($dataDir, '/') . '/';
     $dataDirId = 'local::' . $dataDir;
     $count = 0;
     $hasWarnings = false;
     $this->connection->beginTransaction();
     // note: not doing a direct UPDATE with the REPLACE function
     // because regexp search/extract is needed and it is not guaranteed
     // to work on all database types
     $sql = 'SELECT `id`, `numeric_id` FROM `*PREFIX*storages`' . ' WHERE `id` LIKE ?' . ' ORDER BY `id`';
     $result = $this->connection->executeQuery($sql, array($dataDirId . '%'));
     while ($row = $result->fetch()) {
         $currentId = $row['id'];
         // one entry is the datadir itself
         if ($currentId === $dataDirId) {
             continue;
         }
         try {
             if ($this->fixLegacyStorage($currentId, (int) $row['numeric_id'])) {
                 $count++;
             }
         } catch (\OC\RepairException $e) {
             $hasWarnings = true;
             $this->emit('\\OC\\Repair', 'warning', array('Could not repair legacy storage ' . $currentId . ' automatically.'));
         }
     }
     // check for md5 ids, not in the format "prefix::"
     $sql = 'SELECT COUNT(*) AS "c" FROM `*PREFIX*storages`' . ' WHERE `id` NOT LIKE \'%::%\'';
     $result = $this->connection->executeQuery($sql);
     $row = $result->fetch();
     // find at least one to make sure it's worth
     // querying the user list
     if ((int) $row['c'] > 0) {
         $userManager = \OC_User::getManager();
         // use chunks to avoid caching too many users in memory
         $limit = 30;
         $offset = 0;
         do {
             // query the next page of users
             $results = $userManager->search('', $limit, $offset);
             $storageIds = array();
             $userIds = array();
             foreach ($results as $uid => $userObject) {
                 $storageId = $dataDirId . $uid . '/';
                 if (strlen($storageId) <= 64) {
                     // skip short storage ids as they were handled in the previous section
                     continue;
                 }
                 $storageIds[$uid] = $storageId;
             }
             if (count($storageIds) > 0) {
                 // update the storages of these users
                 foreach ($storageIds as $uid => $storageId) {
                     $numericId = \OC\Files\Cache\Storage::getNumericStorageId($storageId);
                     try {
                         if (!is_null($numericId) && $this->fixLegacyStorage($storageId, (int) $numericId)) {
                             $count++;
                         }
                     } catch (\OC\RepairException $e) {
                         $hasWarnings = true;
                         $this->emit('\\OC\\Repair', 'warning', array('Could not repair legacy storage ' . $storageId . ' automatically.'));
                     }
                 }
             }
             $offset += $limit;
         } while (count($results) >= $limit);
     }
     $this->emit('\\OC\\Repair', 'info', array('Updated ' . $count . ' legacy home storage ids'));
     $this->connection->commit();
     if ($hasWarnings) {
         $this->emit('\\OC\\Repair', 'warning', array('Some legacy storages could not be repaired. Please manually fix them then re-run ./occ maintenance:repair'));
     } else {
         // if all were done, no need to redo the repair during next upgrade
         $this->config->setAppValue('core', 'repairlegacystoragesdone', 'yes');
     }
 }

 protected function copyTable(Connection $fromDB, Connection $toDB, $table, InputInterface $input, OutputInterface $output)
 {
     /** @var $progress \Symfony\Component\Console\Helper\ProgressHelper */
     $progress = $this->getHelperSet()->get('progress');
     $query = 'SELECT COUNT(*) FROM ' . $table;
     $count = $fromDB->fetchColumn($query);
     $query = 'SELECT * FROM ' . $table;
     $statement = $fromDB->executeQuery($query);
     $progress->start($output, $count);
     $progress->setRedrawFrequency($count > 100 ? 5 : 1);
     while ($row = $statement->fetch()) {
         $progress->advance();
         if ($input->getArgument('type') === 'oci') {
             $data = $row;
         } else {
             $data = array();
             foreach ($row as $columnName => $value) {
                 $data[$toDB->quoteIdentifier($columnName)] = $value;
             }
         }
         $toDB->insert($table, $data);
     }
     $progress->finish();
 }