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