/**
  * Delete all mail queue entries
  *
  * @param IDBConnection $connection
  * @param string $user
  */
 protected static function deleteUserMailQueue(IDBConnection $connection, $user)
 {
     // Delete entries from mail queue
     $queryBuilder = $connection->getQueryBuilder();
     $queryBuilder->delete('activity_mq')->where($queryBuilder->expr()->eq('amq_affecteduser', $queryBuilder->createParameter('user')))->setParameter('user', $user);
     $queryBuilder->execute();
 }

    function testAddAccept()
    {
        $query = $this->connection->prepare('
			INSERT INTO `*PREFIX*share_external`
			(`remote`, `share_token`, `password`, `name`, `owner`, `user`, `mountpoint`, `mountpoint_hash`, `remote_id`, `accepted`)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		');
        for ($i = 0; $i < 10; $i++) {
            $query->execute(array('remote', 'token', 'password', 'name', 'owner', 'user', 'mount point', $i, $i, 0));
        }
        $query = $this->connection->prepare('SELECT `id` FROM `*PREFIX*share_external`');
        $query->execute();
        $dummyEntries = $query->fetchAll();
        $this->assertSame(10, count($dummyEntries));
        $m = new Migration();
        $m->addAcceptRow();
        // verify result
        $query = $this->connection->prepare('SELECT `accepted` FROM `*PREFIX*share_external`');
        $query->execute();
        $results = $query->fetchAll();
        $this->assertSame(10, count($results));
        foreach ($results as $r) {
            $this->assertSame(1, (int) $r['accepted']);
        }
        // cleanup
        $cleanup = $this->connection->prepare('DELETE FROM `*PREFIX*share_external`');
        $cleanup->execute();
    }

 public function test()
 {
     // insert test data
     $builder = $this->db->getQueryBuilder();
     $builder->insert('contacts_addressbooks')->values(['userid' => $builder->createNamedParameter('test-user-666'), 'displayname' => $builder->createNamedParameter('Display Name'), 'uri' => $builder->createNamedParameter('contacts'), 'description' => $builder->createNamedParameter('An address book for testing'), 'ctag' => $builder->createNamedParameter('112233'), 'active' => $builder->createNamedParameter('1')])->execute();
     $builder = $this->db->getQueryBuilder();
     $builder->insert('contacts_cards')->values(['addressbookid' => $builder->createNamedParameter(6666), 'fullname' => $builder->createNamedParameter('Full Name'), 'carddata' => $builder->createNamedParameter('datadatadata'), 'uri' => $builder->createNamedParameter('some-card.vcf'), 'lastmodified' => $builder->createNamedParameter('112233')])->execute();
     $builder = $this->db->getQueryBuilder();
     $builder->insert('share')->values(['share_type' => $builder->createNamedParameter(1), 'share_with' => $builder->createNamedParameter('user01'), 'uid_owner' => $builder->createNamedParameter('user02'), 'item_type' => $builder->createNamedParameter('addressbook'), 'item_source' => $builder->createNamedParameter(6666), 'item_target' => $builder->createNamedParameter('Contacts (user02)')])->execute();
     // test the adapter
     $this->adapter->foreachBook('test-user-666', function ($row) {
         $this->books[] = $row;
     });
     $this->assertArrayHasKey('id', $this->books[0]);
     $this->assertEquals('test-user-666', $this->books[0]['userid']);
     $this->assertEquals('Display Name', $this->books[0]['displayname']);
     $this->assertEquals('contacts', $this->books[0]['uri']);
     $this->assertEquals('An address book for testing', $this->books[0]['description']);
     $this->assertEquals('112233', $this->books[0]['ctag']);
     $this->adapter->foreachCard(6666, function ($row) {
         $this->cards[] = $row;
     });
     $this->assertArrayHasKey('id', $this->cards[0]);
     $this->assertEquals(6666, $this->cards[0]['addressbookid']);
     // test getShares
     $shares = $this->adapter->getShares(6666);
     $this->assertEquals(1, count($shares));
 }

    /**
     * Removes orphaned data from the database
     */
    public function cleanUp()
    {
        $sqls = array('UPDATE `*PREFIX*music_albums` SET `cover_file_id` = NULL
				WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (
					SELECT `cover_file_id` FROM (
						SELECT `cover_file_id` FROM `*PREFIX*music_albums`
						LEFT JOIN `*PREFIX*filecache`
							ON `cover_file_id`=`fileid`
						WHERE `fileid` IS NULL
					) mysqlhack
				);', 'DELETE FROM `*PREFIX*music_tracks` WHERE `file_id` IN (
				SELECT `file_id` FROM (
					SELECT `file_id` FROM `*PREFIX*music_tracks`
					LEFT JOIN `*PREFIX*filecache`
						ON `file_id`=`fileid`
					WHERE `fileid` IS NULL
					) mysqlhack
				);', 'DELETE FROM `*PREFIX*music_albums` WHERE `id` NOT IN (
					SELECT `album_id` FROM `*PREFIX*music_tracks`
					GROUP BY `album_id`
				);', 'DELETE FROM `*PREFIX*music_album_artists` WHERE `album_id` NOT IN (
					SELECT `id` FROM `*PREFIX*music_albums`
					GROUP BY `id`
				);', 'DELETE FROM `*PREFIX*music_artists` WHERE `id` NOT IN (
					SELECT `artist_id` FROM `*PREFIX*music_album_artists`
					GROUP BY `artist_id`
				);');
        foreach ($sqls as $sql) {
            $query = $this->db->prepare($sql);
            $query->execute();
        }
    }

 public function test()
 {
     // insert test data
     $builder = $this->db->getQueryBuilder();
     $builder->insert('clndr_calendars')->values(['userid' => $builder->createNamedParameter('test-user-666'), 'displayname' => $builder->createNamedParameter('Display Name'), 'uri' => $builder->createNamedParameter('events'), 'ctag' => $builder->createNamedParameter('112233'), 'active' => $builder->createNamedParameter('1')])->execute();
     $builder = $this->db->getQueryBuilder();
     $builder->insert('clndr_objects')->values(['calendarid' => $builder->createNamedParameter(6666), 'objecttype' => $builder->createNamedParameter('VEVENT'), 'startdate' => $builder->createNamedParameter(new \DateTime(), 'datetime'), 'enddate' => $builder->createNamedParameter(new \DateTime(), 'datetime'), 'repeating' => $builder->createNamedParameter(0), 'summary' => $builder->createNamedParameter('Something crazy will happen'), 'uri' => $builder->createNamedParameter('event.ics'), 'lastmodified' => $builder->createNamedParameter('112233')])->execute();
     $builder = $this->db->getQueryBuilder();
     $builder->insert('share')->values(['share_type' => $builder->createNamedParameter(1), 'share_with' => $builder->createNamedParameter('user01'), 'uid_owner' => $builder->createNamedParameter('user02'), 'item_type' => $builder->createNamedParameter('calendar'), 'item_source' => $builder->createNamedParameter(6666), 'item_target' => $builder->createNamedParameter('Contacts (user02)')])->execute();
     // test the adapter
     $this->adapter->foreachCalendar('test-user-666', function ($row) {
         $this->cals[] = $row;
     });
     $this->assertArrayHasKey('id', $this->cals[0]);
     $this->assertEquals('test-user-666', $this->cals[0]['userid']);
     $this->assertEquals('Display Name', $this->cals[0]['displayname']);
     $this->assertEquals('events', $this->cals[0]['uri']);
     $this->assertEquals('112233', $this->cals[0]['ctag']);
     $this->adapter->foreachCalendarObject(6666, function ($row) {
         $this->calObjs[] = $row;
     });
     $this->assertArrayHasKey('id', $this->calObjs[0]);
     $this->assertEquals(6666, $this->calObjs[0]['calendarid']);
     // test getShares
     $shares = $this->adapter->getShares(6666);
     $this->assertEquals(1, count($shares));
 }

 public function run(IOutput $output)
 {
     $qb = $this->connection->getQueryBuilder();
     $qb->update('filecache')->set('etag', $qb->expr()->literal('xxx'))->where($qb->expr()->eq('etag', $qb->expr()->literal('')))->orWhere($qb->expr()->isNull('etag'));
     $result = $qb->execute();
     $output->info("ETags have been fixed for {$result} files/folders.");
 }

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

 /**
  * Test clearing orphaned files
  */
 public function testClearFiles()
 {
     $input = $this->getMockBuilder('Symfony\\Component\\Console\\Input\\InputInterface')->disableOriginalConstructor()->getMock();
     $output = $this->getMockBuilder('Symfony\\Component\\Console\\Output\\OutputInterface')->disableOriginalConstructor()->getMock();
     $this->loginAsUser($this->user1);
     $view = new \OC\Files\View('/' . $this->user1 . '/');
     $view->mkdir('files/test');
     $fileInfo = $view->getFileInfo('files/test');
     $storageId = $fileInfo->getStorage()->getId();
     $this->assertCount(1, $this->getFile($fileInfo->getId()), 'Asserts that file is available');
     $this->command->execute($input, $output);
     $this->assertCount(1, $this->getFile($fileInfo->getId()), 'Asserts that file is still available');
     $deletedRows = $this->connection->executeUpdate('DELETE FROM `*PREFIX*storages` WHERE `id` = ?', [$storageId]);
     $this->assertNotNull($deletedRows, 'Asserts that storage got deleted');
     $this->assertSame(1, $deletedRows, 'Asserts that storage got deleted');
     // parent folder, `files`, ´test` and `welcome.txt` => 4 elements
     $output->expects($this->once())->method('writeln')->with('4 orphaned file cache entries deleted');
     $this->command->execute($input, $output);
     $this->assertCount(0, $this->getFile($fileInfo->getId()), 'Asserts that file gets cleaned up');
     // since we deleted the storage it might throw a (valid) StorageNotAvailableException
     try {
         $view->unlink('files/test');
     } catch (StorageNotAvailableException $e) {
     }
 }

 private function cleanupRelation()
 {
     $sql = 'DELETE FROM `*PREFIX*music_album_artists` ' . 'WHERE `album_id` NOT IN (SELECT `id` FROM `*PREFIX*music_albums`) ' . 'OR `artist_id` NOT IN (SELECT `id` FROM `*PREFIX*music_artists`)';
     $this->db->prepareQuery($sql)->execute();
     $sql = 'DELETE FROM `*PREFIX*music_playlist_tracks` ' . 'WHERE `track_id` NOT IN (SELECT `id` FROM `*PREFIX*music_tracks`)';
     $this->db->prepareQuery($sql)->execute();
 }

 public function run()
 {
     $qb = $this->connection->getQueryBuilder();
     $qb->update('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."));
 }

 /**
  * Make sure all avatar files in the user roots have permission 27
  */
 protected function fixAvatarPermissions()
 {
     $qb = $this->connection->getQueryBuilder();
     $qb2 = $this->connection->getQueryBuilder();
     $qb->select('numeric_id')->from('storages')->where($qb->expr()->like('id', $qb2->createParameter('like')));
     $qb2->update('filecache')->set('permissions', $qb2->createNamedParameter(27))->where($qb2->expr()->like('path', $qb2->createNamedParameter('avatar.%')))->andWhere($qb2->expr()->in('storage', $qb2->createFunction($qb->getSQL())))->andWhere($qb2->expr()->neq('permissions', $qb2->createNamedParameter(27)))->setParameter('like', 'home::%');
     $qb2->execute();
 }

 /**
  * @param string $path
  * @param int $type self::LOCK_SHARED or self::LOCK_EXCLUSIVE
  */
 public function releaseLock($path, $type)
 {
     // we DONT keep shared locks till the end of the request
     if ($type === self::LOCK_SHARED) {
         $this->db->executeUpdate('UPDATE `*PREFIX*file_locks` SET `lock` = 0 WHERE `key` = ? AND `lock` = 1', [$path]);
     }
     parent::releaseLock($path, $type);
 }

 /**
  * Past bugs would make it possible to set an expiration date on user shares even
  * though it is not supported. This functions removes the expiration date from such entries.
  */
 private function removeExpirationDateFromNonLinkShares()
 {
     $builder = $this->connection->getQueryBuilder();
     $builder->update('share')->set('expiration', 'null')->where($builder->expr()->isNotNull('expiration'))->andWhere($builder->expr()->neq('share_type', $builder->expr()->literal(\OC\Share\Constants::SHARE_TYPE_LINK)));
     $updatedEntries = $builder->execute();
     if ($updatedEntries > 0) {
         $this->emit('\\OC\\Repair', 'info', array('Removed invalid expiration date from ' . $updatedEntries . ' shares'));
     }
 }

 /**
  * @return int
  */
 protected function getLastShareId()
 {
     // select because lastInsertId does not work with OCI
     $query = $this->connection->getQueryBuilder();
     $result = $query->select('id')->from('share')->orderBy('id', 'DESC')->execute();
     $row = $result->fetch();
     $result->closeCursor();
     return $row['id'];
 }

 public function testRun()
 {
     $this->assertFalse($this->connection->tableExists('sharing'), 'Asserting that the table oc_sharing does not exist before repairing');
     $this->assertTrue($this->connection->tableExists('permissions'), 'Asserting that the table oc_permissions does exist before repairing');
     $repair = new \OC\Repair\DropOldTables($this->connection);
     $repair->run();
     $this->assertFalse($this->connection->tableExists('sharing'), 'Asserting that the table oc_sharing does not exist after repairing');
     $this->assertFalse($this->connection->tableExists('permissions'), 'Asserting that the table oc_permissions does not exist after repairing');
 }

 /**
  * Deleting orphaned user tag mappings
  *
  * @return int Number of deleted entries
  */
 protected function cleanUserTags()
 {
     $subQuery = $this->connection->getQueryBuilder();
     $subQuery->select($subQuery->expr()->literal('1'))->from('filecache', 'f')->where($subQuery->expr()->eq('objid', 'f.fileid'));
     $query = $this->connection->getQueryBuilder();
     $deletedEntries = $query->delete('vcategory_to_object')->where($query->expr()->eq('type', $query->expr()->literal('files')))->andWhere($query->expr()->isNull($query->createFunction('(' . $subQuery->getSql() . ')')))->execute();
     $this->logger->debug("{$deletedEntries} orphaned user tag relations deleted", ['app' => 'DeleteOrphanedTagsJob']);
     return $deletedEntries;
 }

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

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

 public function testRetrieve()
 {
     $userId = 'abc';
     $identifier = 'foo';
     $this->crypto->expects($this->once())->method('decrypt')->with('baz')->willReturn(json_encode('bar'));
     $qb = $this->getMockBuilder('\\OC\\DB\\QueryBuilder\\QueryBuilder')->setConstructorArgs([$this->dbConnection])->setMethods(['execute'])->getMock();
     $qb->expects($this->once())->method('execute')->willReturn($this->getQeuryResult(['credentials' => 'baz']));
     $this->dbConnection->expects($this->once())->method('getQueryBuilder')->willReturn($qb);
     $this->manager->retrieve($userId, $identifier);
 }

 public function testDeleteSubAdmin()
 {
     $subAdmin = new \OC\SubAdmin($this->userManager, $this->groupManager, $this->dbConn);
     $this->assertTrue($subAdmin->createSubAdmin($this->users[0], $this->groups[0]));
     $this->assertTrue($subAdmin->deleteSubAdmin($this->users[0], $this->groups[0]));
     // DB query should be empty
     $qb = $this->dbConn->getQueryBuilder();
     $result = $qb->select(['gid', 'uid'])->from('group_admin')->where($qb->expr()->eq('gid', $qb->createNamedParameter($this->groups[0]->getGID())))->andWHere($qb->expr()->eq('uid', $qb->createNamedParameter($this->users[0]->getUID())))->execute()->fetch();
     $this->assertEmpty($result);
 }