/** * insert the @input values when they do not exist yet * @param string $table name * @param array $input key->value pairs * @return int count of inserted rows */ public function insertIfNotExist($table, $input) { $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE '; $inserts = array_values($input); foreach ($input as $key => $value) { $query .= '`' . $key . '`'; if (is_null($value)) { $query .= ' IS NULL AND '; } else { $inserts[] = $value; $query .= ' = ? AND '; } } $query = substr($query, 0, strlen($query) - 5); $query .= ' HAVING COUNT(*) = 0'; try { return $this->conn->executeUpdate($query, $inserts); } catch (\Doctrine\DBAL\DBALException $e) { $entry = 'DB Error: "' . $e->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . $query . '<br />'; \OC_Log::write('core', $entry, \OC_Log::FATAL); error_log('DB error: ' . $entry); \OC_Template::printErrorPage($entry); } }
/** * insert the @input values when they do not exist yet * @param string $table name * @param array $input key->value pair, key has to be sanitized properly * @throws \OC\HintException * @return int count of inserted rows */ public function insertIfNotExist($table, $input) { $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE '; $inserts = array_values($input); foreach ($input as $key => $value) { $query .= '`' . $key . '`'; if (is_null($value)) { $query .= ' IS NULL AND '; } else { $inserts[] = $value; $query .= ' = ? AND '; } } $query = substr($query, 0, strlen($query) - 5); $query .= ' HAVING COUNT(*) = 0'; try { return $this->conn->executeUpdate($query, $inserts); } catch (\Doctrine\DBAL\DBALException $e) { $entry = 'DB Error: "' . $e->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . $query . '<br />'; \OC_Log::write('core', $entry, \OC_Log::FATAL); $l = \OC::$server->getL10N('lib'); throw new \OC\HintException($l->t('Database Error'), $l->t('Please contact your system administrator.'), 0, $e); } }
/** * @dataProvider dataTestRemoveDeletedFiles * @param boolean $nodeExists */ public function testRemoveDeletedFiles($nodeExists) { $this->initTable(); $this->rootFolder->expects($this->once())->method('nodeExists')->with('/' . $this->user0 . '/files_trashbin')->willReturn($nodeExists); if ($nodeExists) { $this->rootFolder->expects($this->once())->method('get')->with('/' . $this->user0 . '/files_trashbin')->willReturn($this->rootFolder); $this->rootFolder->expects($this->once())->method('delete'); } else { $this->rootFolder->expects($this->never())->method('get'); $this->rootFolder->expects($this->never())->method('delete'); } $this->invokePrivate($this->cleanup, 'removeDeletedFiles', [$this->user0]); if ($nodeExists) { // if the delete operation was execute only files from user1 // should be left. $query = $this->dbConnection->createQueryBuilder(); $result = $query->select('`user`')->from($this->trashTable)->execute()->fetchAll(); $this->assertSame(5, count($result)); foreach ($result as $r) { $this->assertSame('user1', $r['user']); } } else { // if no delete operation was execute we should still have all 10 // database entries $getAllQuery = $this->dbConnection->createQueryBuilder(); $result = $getAllQuery->select('`id`')->from($this->trashTable)->execute()->fetchAll(); $this->assertSame(10, count($result)); } }
public function run() { $qb = $this->connection->createQueryBuilder(); $qb->update('`*PREFIX*filecache`')->set('`etag`', $qb->expr()->literal('xxx'))->where($qb->expr()->eq('`etag`', $qb->expr()->literal('')))->orWhere($qb->expr()->isNull('`etag`')); $result = $qb->execute(); $this->emit('\\OC\\Repair', 'info', array("ETags have been fixed for {$result} files/folders.")); }
/** * Run repair step. * Must throw exception on error. * * @throws \Exception in case of failure */ public function run() { foreach ($this->oldDatabaseTables() as $tableName) { if ($this->connection->tableExists($tableName)) { $this->emit('\\OC\\Repair', 'info', [sprintf('Table %s has been deleted', $tableName)]); $this->connection->dropTable($tableName); } } }
/** * Fix mime types */ public function run() { if (!$this->connection->getDatabasePlatform() instanceof MySqlPlatform) { $this->emit('\\OC\\Repair', 'info', array('Not a mysql database -> nothing to no')); return; } $tables = $this->getAllNonUTF8BinTables($this->connection); foreach ($tables as $table) { $query = $this->connection->prepare('ALTER TABLE `' . $table . '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;'); $query->execute(); } }
/** * Fix mime types */ public function run(IOutput $output) { if (!$this->connection->getDatabasePlatform() instanceof MySqlPlatform) { $output->info('Not a mysql database -> nothing to no'); return; } $tables = $this->getAllNonUTF8BinTables($this->connection); foreach ($tables as $table) { $output->info("Change collation for {$table} ..."); $query = $this->connection->prepare('ALTER TABLE `' . $table . '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;'); $query->execute(); } }
/** * @param \OC\DB\Connection $connection */ private function createDatabase($connection) { try { $name = $this->dbName; $user = $this->dbUser; //we cant use OC_BD functions here because we need to connect as the administrative user. $query = "CREATE DATABASE IF NOT EXISTS `{$name}` CHARACTER SET utf8 COLLATE utf8_bin;"; $connection->executeUpdate($query); //this query will fail if there aren't the right permissions, ignore the error $query = "GRANT ALL PRIVILEGES ON `{$name}` . * TO '{$user}'"; $connection->executeUpdate($query); } catch (\Exception $ex) { $this->logger->error('Database creation failed: {error}', ['app' => 'mysql.setup', 'error' => $ex->getMessage()]); } }
/** * @param string $file * @param \OC\DB\Connection $conn * @return bool */ public static function saveSchemaToFile($file, \OC\DB\Connection $conn) { $config = \OC::$server->getConfig(); $xml = new SimpleXMLElement('<database/>'); $xml->addChild('name', $config->getSystemValue('dbname', 'owncloud')); $xml->addChild('create', 'true'); $xml->addChild('overwrite', 'false'); $xml->addChild('charset', 'utf8'); $conn->getConfiguration()->setFilterSchemaAssetsExpression('/^' . $config->getSystemValue('dbtableprefix', 'oc_') . '/'); foreach ($conn->getSchemaManager()->listTables() as $table) { self::saveTable($table, $xml->addChild('table')); } file_put_contents($file, $xml->asXML()); return true; }
/** * update database */ public function updateDB() { // make sure that we don't update the file cache multiple times // only update during the first run if ($this->installedVersion === '-1') { return; } // delete left-over from old encryption which is no longer needed $this->config->deleteAppValue('files_encryption', 'ocsid'); $this->config->deleteAppValue('files_encryption', 'types'); $this->config->deleteAppValue('files_encryption', 'enabled'); $oldAppValues = $this->connection->createQueryBuilder(); $oldAppValues->select('*')->from('`*PREFIX*appconfig`')->where($oldAppValues->expr()->eq('`appid`', ':appid'))->setParameter('appid', 'files_encryption'); $appSettings = $oldAppValues->execute(); while ($row = $appSettings->fetch()) { // 'installed_version' gets deleted at the end of the migration process if ($row['configkey'] !== 'installed_version') { $this->config->setAppValue('encryption', $row['configkey'], $row['configvalue']); $this->config->deleteAppValue('files_encryption', $row['configkey']); } } $oldPreferences = $this->connection->createQueryBuilder(); $oldPreferences->select('*')->from('`*PREFIX*preferences`')->where($oldPreferences->expr()->eq('`appid`', ':appid'))->setParameter('appid', 'files_encryption'); $preferenceSettings = $oldPreferences->execute(); while ($row = $preferenceSettings->fetch()) { $this->config->setUserValue($row['userid'], 'encryption', $row['configkey'], $row['configvalue']); $this->config->deleteUserValue($row['userid'], 'files_encryption', $row['configkey']); } }
/** * returns the error code and message as a string for logging * works with DoctrineException * @param mixed $error * @return string */ public static function getErrorMessage($error) { if (self::$connection) { return self::$connection->getError(); } return ''; }
/** * Remove app from all users * @param string $app app * * Removes all keys in preferences belonging to the app. */ public function deleteAppFromAllUsers($app) { $where = array('appid' => $app); $this->conn->delete('*PREFIX*preferences', $where); foreach ($this->cache as &$userCache) { unset($userCache[$app]); } }
/** * @param bool $enabled */ public static function enableCaching($enabled) { if ($enabled) { self::$connection->enableQueryStatementCaching(); } else { self::$connection->disableQueryStatementCaching(); } }
/** * @param \Doctrine\DBAL\Schema\Schema $schema * @return string */ public function generateChangeScript($schema) { $script = ''; $sqls = $schema->toSql($this->conn->getDatabasePlatform()); foreach ($sqls as $sql) { $script .= $sql . ';'; $script .= PHP_EOL; } return $script; }
/** * Insert a row if the matching row does not exists. * * @param string $table The table name (will replace *PREFIX* with the actual prefix) * @param array $input data that should be inserted into the table (column name => value) * @param array|null $compare List of values that should be checked for "if not exists" * If this is null or an empty array, all keys of $input will be compared * Please note: text fields (clob) must not be used in the compare array * @return int number of inserted rows * @throws \Doctrine\DBAL\DBALException */ public function insertIfNotExist($table, $input, array $compare = null) { if (empty($compare)) { $compare = array_keys($input); } $query = 'INSERT INTO `' . $table . '` (`' . implode('`,`', array_keys($input)) . '`) SELECT ' . str_repeat('?,', count($input) - 1) . '? ' . 'FROM `' . $table . '` WHERE '; $inserts = array_values($input); foreach ($compare as $key) { $query .= '`' . $key . '`'; if (is_null($input[$key])) { $query .= ' IS NULL AND '; } else { $inserts[] = $input[$key]; $query .= ' = ? AND '; } } $query = substr($query, 0, strlen($query) - 5); $query .= ' HAVING COUNT(*) = 0'; return $this->conn->executeUpdate($query, $inserts); }
/** * remove deleted files for the given user * * @param string $uid */ protected function removeDeletedFiles($uid) { \OC_Util::tearDownFS(); \OC_Util::setupFS($uid); if ($this->rootFolder->nodeExists('/' . $uid . '/files_trashbin')) { $this->rootFolder->get('/' . $uid . '/files_trashbin')->delete(); $query = $this->dbConnection->createQueryBuilder(); $query->delete('`*PREFIX*files_trash`')->where($query->expr()->eq('`user`', ':uid'))->setParameter('uid', $uid); $query->execute(); } }
/** * Deletes all entries from $deleteTable that do not have a matching entry in $sourceTable * * A query joins $deleteTable.$deleteId = $sourceTable.$sourceId and checks * whether $sourceNullColumn is null. If it is null, the entry in $deleteTable * is being deleted. * * @param string $repairInfo * @param string $deleteTable * @param string $deleteId * @param string $sourceTable * @param string $sourceId * @param string $sourceNullColumn If this column is null in the source table, * the entry is deleted in the $deleteTable */ protected function deleteOrphanEntries($repairInfo, $deleteTable, $deleteId, $sourceTable, $sourceId, $sourceNullColumn) { $qb = $this->connection->createQueryBuilder(); $qb->select('d.`' . $deleteId . '`')->from('`' . $deleteTable . '`', 'd')->leftJoin('d', '`' . $sourceTable . '`', 's', 'd.`' . $deleteId . '` = s.`' . $sourceId . '`')->where('d.`type` = ' . $qb->expr()->literal('files'))->andWhere($qb->expr()->isNull('s.`' . $sourceNullColumn . '`')); $result = $qb->execute(); $orphanItems = array(); while ($row = $result->fetch()) { $orphanItems[] = (int) $row[$deleteId]; } if (!empty($orphanItems)) { $orphanItemsBatch = array_chunk($orphanItems, 200); foreach ($orphanItemsBatch as $items) { $qb->delete('`' . $deleteTable . '`')->where('`type` = ' . $qb->expr()->literal('files'))->andWhere($qb->expr()->in('`' . $deleteId . '`', ':ids')); $qb->setParameter('ids', $items, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY); $qb->execute(); } } if ($repairInfo) { $this->emit('\\OC\\Repair', 'info', array(sprintf($repairInfo, sizeof($orphanItems)))); } }
/** * @param string $file * @param \OC\DB\Connection $conn * @return bool */ public static function saveSchemaToFile($file, \OC\DB\Connection $conn) { $config = \OC::$server->getConfig(); $xml = new SimpleXMLElement('<database/>'); $xml->addChild('name', $config->getSystemValue('dbname', 'owncloud')); $xml->addChild('create', 'true'); $xml->addChild('overwrite', 'false'); $xml->addChild('charset', 'utf8'); // FIX ME: bloody work around if ($config->getSystemValue('dbtype', 'sqlite') === 'oci') { $filterExpression = '/^"' . preg_quote($conn->getPrefix()) . '/'; } else { $filterExpression = '/^' . preg_quote($conn->getPrefix()) . '/'; } $conn->getConfiguration()->setFilterSchemaAssetsExpression($filterExpression); foreach ($conn->getSchemaManager()->listTables() as $table) { self::saveTable($table, $xml->addChild('table')); } file_put_contents($file, $xml->asXML()); return true; }
/** * @param \Doctrine\DBAL\Schema\Schema $schema * @return bool */ private function executeSchemaChange($schema) { $this->conn->beginTransaction(); foreach ($schema->toSql($this->conn->getDatabasePlatform()) as $sql) { $this->conn->query($sql); } $this->conn->commit(); if ($this->conn->getDatabasePlatform() instanceof SqlitePlatform) { \OC_DB::reconnect(); } return true; }
/** * update database */ public function updateDB() { // delete left-over from old encryption which is no longer needed $this->config->deleteAppValue('files_encryption', 'ocsid'); $this->config->deleteAppValue('files_encryption', 'types'); $this->config->deleteAppValue('files_encryption', 'enabled'); $query = $this->connection->createQueryBuilder(); $query->update('`*PREFIX*appconfig`')->set('`appid`', ':newappid')->where($query->expr()->eq('`appid`', ':oldappid'))->setParameter('oldappid', 'files_encryption')->setParameter('newappid', 'encryption'); $query->execute(); $query = $this->connection->createQueryBuilder(); $query->update('`*PREFIX*preferences`')->set('`appid`', ':newappid')->where($query->expr()->eq('`appid`', ':oldappid'))->setParameter('oldappid', 'files_encryption')->setParameter('newappid', 'encryption'); $query->execute(); }
/** * Fix mime types */ public function run(IOutput $out) { if (!$this->connection->getDatabasePlatform() instanceof SqlitePlatform) { return; } $sourceSchema = $this->connection->getSchemaManager()->createSchema(); $schemaDiff = new SchemaDiff(); foreach ($sourceSchema->getTables() as $tableSchema) { $primaryKey = $tableSchema->getPrimaryKey(); if (!$primaryKey) { continue; } $columnNames = $primaryKey->getColumns(); // add a column diff for every primary key column, // but do not actually change anything, this will // force the generation of SQL statements to alter // those tables, which will then trigger the // specific SQL code from OCSqlitePlatform try { $tableDiff = new TableDiff($tableSchema->getName()); $tableDiff->fromTable = $tableSchema; foreach ($columnNames as $columnName) { $columnSchema = $tableSchema->getColumn($columnName); $columnDiff = new ColumnDiff($columnSchema->getName(), $columnSchema); $tableDiff->changedColumns[] = $columnDiff; $schemaDiff->changedTables[] = $tableDiff; } } catch (SchemaException $e) { // ignore } } $this->connection->beginTransaction(); foreach ($schemaDiff->toSql($this->connection->getDatabasePlatform()) as $sql) { $this->connection->query($sql); } $this->connection->commit(); }
/** * 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); } }
/** * Retrieve the owner of a connection * * @param Connection $connection * @param int $shareId * @throws \Exception * @return string uid of share owner */ private static function getShareOwner(Connection $connection, $shareId) { $qb = $connection->createQueryBuilder(); $qb->select('`uid_owner`')->from('`*PREFIX*share`')->where('`id` = :shareId')->setParameter(':shareId', $shareId); $result = $qb->execute(); $result = $result->fetch(); if (empty($result)) { throw new \Exception('Share not found'); } return $result['uid_owner']; }
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(); }
/** * 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) { $chunkSize = $input->getOption('chunk-size'); /** @var $progress \Symfony\Component\Console\Helper\ProgressHelper */ $progress = $this->getHelperSet()->get('progress'); $query = $fromDB->getQueryBuilder(); $query->automaticTablePrefix(false); $query->selectAlias($query->createFunction('COUNT(*)'), 'num_entries')->from($table); $result = $query->execute(); $count = $result->fetchColumn(); $result->closeCursor(); $numChunks = ceil($count / $chunkSize); if ($numChunks > 1) { $output->writeln('chunked query, ' . $numChunks . ' chunks'); } $progress->start($output, $count); $redraw = $count > $chunkSize ? 100 : ($count > 100 ? 5 : 1); $progress->setRedrawFrequency($redraw); $query = $fromDB->getQueryBuilder(); $query->automaticTablePrefix(false); $query->select('*')->from($table)->setMaxResults($chunkSize); $insertQuery = $toDB->getQueryBuilder(); $insertQuery->automaticTablePrefix(false); $insertQuery->insert($table); $parametersCreated = false; for ($chunk = 0; $chunk < $numChunks; $chunk++) { $query->setFirstResult($chunk * $chunkSize); $result = $query->execute(); while ($row = $result->fetch()) { $progress->advance(); if (!$parametersCreated) { foreach ($row as $key => $value) { $insertQuery->setValue($key, $insertQuery->createParameter($key)); } $parametersCreated = true; } foreach ($row as $key => $value) { $insertQuery->setParameter($key, $value); } $insertQuery->execute(); } $result->closeCursor(); } $progress->finish(); }