PHP IDBConnection::executeQuery方法代码示例

 public function testRun()
 {
     $userName = 'removePropertiesUser';
     $data = [[$userName, '/abc.def.txt', '{DAV:}getetag', 'abcdef'], [$userName, '/abc.def.txt', '{DAV:}anotherRandomProperty', 'ghi']];
     // insert test data
     $sqlToInsertProperties = 'INSERT INTO `*PREFIX*properties` (`userid`, `propertypath`, `propertyname`, `propertyvalue`) VALUES (?, ?, ? ,?)';
     foreach ($data as $entry) {
         $this->connection->executeUpdate($sqlToInsertProperties, $entry);
     }
     // check if test data is written to DB
     $sqlToFetchProperties = 'SELECT `userid`, `propertypath`, `propertyname`, `propertyvalue` FROM `*PREFIX*properties` WHERE `userid` = ?';
     $stmt = $this->connection->executeQuery($sqlToFetchProperties, [$userName]);
     $entries = $stmt->fetchAll(\PDO::FETCH_NUM);
     $this->assertCount(2, $entries, 'Asserts that two entries are returned as we have inserted two');
     foreach ($entries as $entry) {
         $this->assertTrue(in_array($entry, $data), 'Asserts that the entries are the ones from the test data set');
     }
     /** @var IOutput | \PHPUnit_Framework_MockObject_MockObject $outputMock */
     $outputMock = $this->getMockBuilder('\\OCP\\Migration\\IOutput')->disableOriginalConstructor()->getMock();
     // run repair step
     $repair = new RemoveGetETagEntries($this->connection);
     $repair->run($outputMock);
     // check if test data is correctly modified in DB
     $stmt = $this->connection->executeQuery($sqlToFetchProperties, [$userName]);
     $entries = $stmt->fetchAll(\PDO::FETCH_NUM);
     $this->assertCount(1, $entries, 'Asserts that only one entry is returned after the repair step - the other one has to be removed');
     $this->assertSame($data[1], $entries[0], 'Asserts that the returned entry is the correct one from the test data set');
     // remove test data
     $sqlToRemoveProperties = 'DELETE FROM `*PREFIX*properties` WHERE `userid` = ?';
     $this->connection->executeUpdate($sqlToRemoveProperties, [$userName]);
 }

 private function getShares()
 {
     $shares = [];
     $result = $this->connection->executeQuery('SELECT * FROM `*PREFIX*share`');
     while ($row = $result->fetch()) {
         $shares[] = $row;
     }
     $result->closeCursor();
     return $shares;
 }

 protected function tearDown()
 {
     $user = \OC::$server->getUserManager()->get($this->user);
     if ($user) {
         $user->delete();
     }
     $sql = 'DELETE FROM `*PREFIX*storages`';
     $this->connection->executeQuery($sql);
     $sql = 'DELETE FROM `*PREFIX*filecache`';
     $this->connection->executeQuery($sql);
     $this->config->setSystemValue('datadirectory', $this->oldDataDir);
     $this->config->setAppValue('core', 'repairlegacystoragesdone', 'no');
     parent::tearDown();
 }

 /**
  * Bulk load properties for directory children
  *
  * @param Directory $node
  * @param array $requestedProperties requested properties
  *
  * @return void
  */
 private function loadChildrenProperties(Directory $node, $requestedProperties)
 {
     $path = $node->getPath();
     if (isset($this->cache[$path])) {
         // we already loaded them at some point
         return;
     }
     $childNodes = $node->getChildren();
     // pre-fill cache
     foreach ($childNodes as $childNode) {
         $this->cache[$childNode->getPath()] = [];
     }
     $sql = 'SELECT * FROM `*PREFIX*properties` WHERE `userid` = ? AND `propertypath` LIKE ?';
     $sql .= ' AND `propertyname` in (?) ORDER BY `propertypath`, `propertyname`';
     $result = $this->connection->executeQuery($sql, array($this->user, $this->connection->escapeLikeParameter(rtrim($path, '/')) . '/%', $requestedProperties), array(null, null, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
     $oldPath = null;
     $props = [];
     while ($row = $result->fetch()) {
         $path = $row['propertypath'];
         if ($oldPath !== $path) {
             // save previously gathered props
             $this->cache[$oldPath] = $props;
             $oldPath = $path;
             // prepare props for next path
             $props = [];
         }
         $props[$row['propertyname']] = $row['propertyvalue'];
     }
     if (!is_null($oldPath)) {
         // save props from last run
         $this->cache[$oldPath] = $props;
     }
     $result->closeCursor();
 }

 /**
  * Sharing a file or folder with a group
  * @param array $params The hook params
  */
 protected function shareFileOrFolderWithGroup($params)
 {
     // User performing the share
     $this->shareNotificationForSharer('shared_group_self', $params['shareWith'], $params['fileSource'], $params['itemType']);
     // Members of the new group
     $affectedUsers = array();
     $usersInGroup = \OC_Group::usersInGroup($params['shareWith']);
     foreach ($usersInGroup as $user) {
         $affectedUsers[$user] = $params['fileTarget'];
     }
     // Remove the triggering user, we already managed his notifications
     unset($affectedUsers[$this->currentUser]);
     if (empty($affectedUsers)) {
         return;
     }
     $filteredStreamUsersInGroup = $this->userSettings->filterUsersBySetting($usersInGroup, 'stream', Files_Sharing::TYPE_SHARED);
     $filteredEmailUsersInGroup = $this->userSettings->filterUsersBySetting($usersInGroup, 'email', Files_Sharing::TYPE_SHARED);
     // Check when there was a naming conflict and the target is different
     // for some of the users
     $query = $this->connection->executeQuery('SELECT `share_with`, `file_target` FROM `*PREFIX*share` WHERE `parent` = ? ', [(int) $params['id']]);
     while ($row = $query->fetch()) {
         $affectedUsers[$row['share_with']] = $row['file_target'];
     }
     foreach ($affectedUsers as $user => $path) {
         if (empty($filteredStreamUsersInGroup[$user]) && empty($filteredEmailUsersInGroup[$user])) {
             continue;
         }
         $this->addNotificationsForUser($user, 'shared_with_by', array($path, $this->currentUser), $path, $params['itemType'] === 'file', !empty($filteredStreamUsersInGroup[$user]), !empty($filteredEmailUsersInGroup[$user]) ? $filteredEmailUsersInGroup[$user] : 0);
     }
 }

 /**
  * Quick update record
  * @param $table
  * @param $where
  * @param null $bind
  * @return \Doctrine\DBAL\Driver\Statement
  */
 public function update($table, array $columnData, $where, $bind = null)
 {
     $columns = array_keys($columnData);
     $where = preg_replace('|:\\w+|', '?', $where);
     if (empty($bind)) {
         $bind = array_values($columnData);
     } else {
         $bind = array_values(array_merge($columnData, (array) $bind));
     }
     $sql = sprintf("UPDATE %s SET %s WHERE %s;", $table, implode('=?, ', $columns) . '=?', $where);
     return $this->db->executeQuery($sql, $bind);
 }

 /**
  * get the path of a file on this storage by it's file id
  *
  * @param int $id the file id of the file or folder to search
  * @return string|null the path of the file (relative to the storage) or null if a file with the given id does not exists within this cache
  */
 public function getPathById($id)
 {
     $sql = 'SELECT `path` FROM `*PREFIX*filecache` WHERE `fileid` = ? AND `storage` = ?';
     $result = $this->connection->executeQuery($sql, array($id, $this->getNumericStorageId()));
     if ($row = $result->fetch()) {
         // Oracle stores empty strings as null...
         if ($row['path'] === null) {
             return '';
         }
         return $row['path'];
     } else {
         return null;
     }
 }

 /**
  * Determines the users that have the given value set for a specific app-key-pair
  *
  * @param string $appName the app to get the user for
  * @param string $key the key to get the user for
  * @param string $value the value to get the user for
  * @return array of user IDs
  */
 public function getUsersForUserValue($appName, $key, $value)
 {
     // TODO - FIXME
     $this->fixDIInit();
     $sql = 'SELECT `userid` FROM `*PREFIX*preferences` ' . 'WHERE `appid` = ? AND `configkey` = ? ';
     if ($this->getSystemValue('dbtype', 'sqlite') === 'oci') {
         //oracle hack: need to explicitly cast CLOB to CHAR for comparison
         $sql .= 'AND to_char(`configvalue`) = ?';
     } else {
         $sql .= 'AND `configvalue` = ?';
     }
     $result = $this->connection->executeQuery($sql, array($appName, $key, $value));
     $userIDs = array();
     while ($row = $result->fetch()) {
         $userIDs[] = $row['userid'];
     }
     return $userIDs;
 }

 public function testDeleteAppFromAllUsers()
 {
     $config = $this->getConfig();
     // preparation - add something to the database
     $data = array(array('userFetch5', 'appFetch1', 'keyFetch1', 'value1'), array('userFetch5', 'appFetch1', 'keyFetch2', 'value2'), array('userFetch5', 'appFetch2', 'keyFetch3', 'value3'), array('userFetch5', 'appFetch1', 'keyFetch4', 'value4'), array('userFetch5', 'appFetch4', 'keyFetch1', 'value5'), array('userFetch5', 'appFetch5', 'keyFetch1', 'value6'), array('userFetch6', 'appFetch2', 'keyFetch1', 'value7'));
     foreach ($data as $entry) {
         $this->connection->executeUpdate('INSERT INTO `*PREFIX*preferences` (`userid`, `appid`, ' . '`configkey`, `configvalue`) VALUES (?, ?, ?, ?)', $entry);
     }
     $config->deleteAppFromAllUsers('appFetch1');
     $result = $this->connection->executeQuery('SELECT COUNT(*) AS `count` FROM `*PREFIX*preferences`')->fetch();
     $actualCount = $result['count'];
     $this->assertEquals(4, $actualCount, 'After removing `appFetch1` there should be exactly 4 entries left.');
     $config->deleteAppFromAllUsers('appFetch2');
     $result = $this->connection->executeQuery('SELECT COUNT(*) AS `count` FROM `*PREFIX*preferences`')->fetch();
     $actualCount = $result['count'];
     $this->assertEquals(2, $actualCount, 'After removing `appFetch2` there should be exactly 2 entries left.');
     // cleanup
     $this->connection->executeUpdate('DELETE FROM `*PREFIX*preferences`');
 }

 /**
  * Returns a list of properties for this nodes.;
  * @param string $path
  * @param array $requestedProperties requested properties or empty array for "all"
  * @return array
  * @note The properties list is a list of propertynames the client
  * requested, encoded as xmlnamespace#tagName, for example:
  * http://www.example.org/namespace#author If the array is empty, all
  * properties should be returned
  */
 private function getProperties($path, array $requestedProperties)
 {
     if (isset($this->cache[$path])) {
         return $this->cache[$path];
     }
     // TODO: chunking if more than 1000 properties
     $sql = 'SELECT * FROM `*PREFIX*properties` WHERE `userid` = ? AND `propertypath` = ?';
     $whereValues = array($this->user, $path);
     $whereTypes = array(null, null);
     if (!empty($requestedProperties)) {
         // request only a subset
         $sql .= ' AND `propertyname` in (?)';
         $whereValues[] = $requestedProperties;
         $whereTypes[] = \Doctrine\DBAL\Connection::PARAM_STR_ARRAY;
     }
     $result = $this->connection->executeQuery($sql, $whereValues, $whereTypes);
     $props = [];
     while ($row = $result->fetch()) {
         $props[$row['propertyname']] = $row['propertyvalue'];
     }
     $result->closeCursor();
     $this->cache[$path] = $props;
     return $props;
 }

 /**
  * Converts legacy home storage ids in the format
  * "local::/data/dir/path/userid/" to the new format "home::userid"
  */
 public function run(IOutput $out)
 {
     // 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 (RepairException $e) {
             $hasWarnings = true;
             $out->warning('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::$server->getUserManager();
         // 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();
             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 = Storage::getNumericStorageId($storageId);
                     try {
                         if (!is_null($numericId) && $this->fixLegacyStorage($storageId, (int) $numericId)) {
                             $count++;
                         }
                     } catch (RepairException $e) {
                         $hasWarnings = true;
                         $out->warning('Could not repair legacy storage ' . $storageId . ' automatically.');
                     }
                 }
             }
             $offset += $limit;
         } while (count($results) >= $limit);
     }
     $out->info('Updated ' . $count . ' legacy home storage ids');
     $this->connection->commit();
     if ($hasWarnings) {
         $out->warning('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');
     }
 }

    /**
     * Copy of phpBB's get_database_size()
     * @link https://github.com/phpbb/phpbb/blob/release-3.1.6/phpBB/includes/functions_admin.php#L2908-L3043
     *
     * @copyright (c) phpBB Limited <https://www.phpbb.com>
     * @license GNU General Public License, version 2 (GPL-2.0)
     *
     * @return int|string
     */
    protected function databaseSize()
    {
        $database_size = false;
        // This code is heavily influenced by a similar routine in phpMyAdmin 2.2.0
        switch ($this->config->getSystemValue('dbtype')) {
            case 'mysql':
                $sql = 'SELECT VERSION() AS mysql_version';
                $result = $this->connection->executeQuery($sql);
                $row = $result->fetch();
                $result->closeCursor();
                if ($row) {
                    $version = $row['mysql_version'];
                    if (preg_match('#(3\\.23|[45]\\.)#', $version)) {
                        $db_name = preg_match('#^(?:3\\.23\\.(?:[6-9]|[1-9]{2}))|[45]\\.#', $version) ? "`{$this->config->getSystemValue('dbname')}`" : $this->config->getSystemValue('dbname');
                        $sql = 'SHOW TABLE STATUS
							FROM ' . $db_name;
                        $result = $this->connection->executeQuery($sql);
                        $database_size = 0;
                        while ($row = $result->fetch()) {
                            if (isset($row['Type']) && $row['Type'] != 'MRG_MyISAM' || isset($row['Engine']) && ($row['Engine'] == 'MyISAM' || $row['Engine'] == 'InnoDB')) {
                                $database_size += $row['Data_length'] + $row['Index_length'];
                            }
                        }
                        $result->closeCursor();
                    }
                }
                break;
            case 'sqlite':
                if (file_exists($this->config->getSystemValue('dbhost'))) {
                    $database_size = filesize($this->config->getSystemValue('dbhost'));
                }
                break;
            case 'pgsql':
                $sql = "SELECT proname\n\t\t\t\t\tFROM pg_proc\n\t\t\t\t\tWHERE proname = 'pg_database_size'";
                $result = $this->connection->executeQuery($sql);
                $row = $result->fetch();
                $result->closeCursor();
                if ($row['proname'] == 'pg_database_size') {
                    $database = $this->config->getSystemValue('dbname');
                    if (strpos($database, '.') !== false) {
                        list($database, ) = explode('.', $database);
                    }
                    $sql = "SELECT oid\n\t\t\t\t\t\tFROM pg_database\n\t\t\t\t\t\tWHERE datname = '{$database}'";
                    $result = $this->connection->executeQuery($sql);
                    $row = $result->fetch();
                    $result->closeCursor();
                    $oid = $row['oid'];
                    $sql = 'SELECT pg_database_size(' . $oid . ') as size';
                    $result = $this->connection->executeQuery($sql);
                    $row = $result->fetch();
                    $result->closeCursor();
                    $database_size = $row['size'];
                }
                break;
            case 'oci':
                $sql = 'SELECT SUM(bytes) as dbsize
					FROM user_segments';
                $result = $this->connection->executeQuery($sql);
                $database_size = ($row = $result->fetch()) ? $row['dbsize'] : false;
                $result->closeCursor();
                break;
        }
        return $database_size !== false ? $database_size : 'N/A';
    }

 /**
  * Executes an, optionally parameterized, SQL query.
  *
  * If the query is parameterized, a prepared statement is used.
  * If an SQLLogger is configured, the execution is logged.
  *
  * @param string $query The SQL query to execute.
  * @param string[] $params The parameters to bind to the query, if any.
  * @param array $types The types the previous parameters are in.
  * @return \Doctrine\DBAL\Driver\Statement The executed statement.
  */
 public function executeQuery($query, array $params = array(), $types = array())
 {
     return $this->connection->executeQuery($query, $params, $types);
 }

 /**
  * @param $username
  * @param IDBConnection $connection
  * @return array
  */
 private function createSpecificUser($username, $connection)
 {
     try {
         //user already specified in config
         $oldUser = $this->config->getSystemValue('dbuser', false);
         //we don't have a dbuser specified in config
         if ($this->dbUser !== $oldUser) {
             //add prefix to the admin username to prevent collisions
             $adminUser = substr('oc_' . $username, 0, 16);
             $i = 1;
             while (true) {
                 //this should be enough to check for admin rights in mysql
                 $query = 'SELECT user FROM mysql.user WHERE user=?';
                 $result = $connection->executeQuery($query, [$adminUser]);
                 //current dbuser has admin rights
                 if ($result) {
                     $data = $result->fetchAll();
                     //new dbuser does not exist
                     if (count($data) === 0) {
                         //use the admin login data for the new database user
                         $this->dbUser = $adminUser;
                         //create a random password so we don't need to store the admin password in the config file
                         $this->dbPassword = $this->random->getMediumStrengthGenerator()->generate(30);
                         $this->createDBUser($connection);
                         break;
                     } else {
                         //repeat with different username
                         $length = strlen((string) $i);
                         $adminUser = substr('oc_' . $username, 0, 16 - $length) . $i;
                         $i++;
                     }
                 } else {
                     break;
                 }
             }
         }
     } catch (\Exception $ex) {
         $this->logger->error('Specific user creation failed: {error}', ['app' => 'mysql.setup', 'error' => $ex->getMessage()]);
     }
     $this->config->setSystemValues(['dbuser' => $this->dbUser, 'dbpassword' => $this->dbPassword]);
 }

 public function tearDown()
 {
     $this->connection->executeQuery('DELETE FROM `*PREFIX*file_locks`');
     parent::tearDown();
 }