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

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

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

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

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

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

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

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