public function testRun() { $userName = '******'; $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(); }
public function setSessionTimeZoneToZero() { $this->db->executeQuery("SET @@session.time_zone = '+00:00'"); }
protected function getFile($fileId) { $stmt = $this->connection->executeQuery('SELECT * FROM `*PREFIX*filecache` WHERE `fileid` = ?', [$fileId]); return $stmt->fetchAll(); }