/**
* @param \OCP\IConfig $config
* @param \OCP\IDBConnection $connection
*/
public function __construct($config, $connection)
{
$this->connection = $connection;
$this->config = $config;
$this->findStorageInCacheStatement = $this->connection->prepare('SELECT DISTINCT `storage` FROM `*PREFIX*filecache`' . ' WHERE `storage` in (?, ?)');
$this->renameStorageStatement = $this->connection->prepare('UPDATE `*PREFIX*storages`' . ' SET `id` = ?' . ' WHERE `id` = ?');
}
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();
}
/**
* 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();
}
}
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->prepare($sql)->execute();
$sql = 'DELETE FROM `*PREFIX*music_playlist_tracks` ' . 'WHERE `track_id` NOT IN (SELECT `id` FROM `*PREFIX*music_tracks`)';
$this->db->prepare($sql)->execute();
}
public function tearDown() {
$query = $this->connection->prepare('DELETE FROM `*PREFIX*appconfig` WHERE `appid` = ?');
$query->execute(array('testapp'));
$query->execute(array('someapp'));
$query->execute(array('123456'));
$query->execute(array('anotherapp'));
$this->registerAppConfig(new \OC\AppConfig(\OC::$server->getDatabaseConnection()));
parent::tearDown();
}
protected function importItem($userId, Item $item)
{
/** @var \PDOStatement $stmt */
$sql = 'INSERT INTO `*PREFIX*sipgate_phone_book` SET userId=:userId, source=:source, externalId=:externalId, name=:name, phoneNumber=:phoneNumber';
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':userId', $userId, \PDO::PARAM_STR);
$stmt->bindValue(':source', $this->source, \PDO::PARAM_STR);
$stmt->bindValue(':externalId', $item->getExternalId(), \PDO::PARAM_STR);
$stmt->bindValue(':name', $item->getName(), \PDO::PARAM_STR);
$stmt->bindValue(':phoneNumber', $item->getPhoneNumber(), \PDO::PARAM_STR);
$stmt->execute();
return $stmt->rowCount();
}
private function dropTables($userID = null)
{
$tables = array('tracks', 'albums', 'artists');
foreach ($tables as $table) {
$sql = 'DELETE FROM `*PREFIX*music_' . $table . '` ';
$params = array();
if ($userID) {
$sql .= 'WHERE `user_id` = ?';
$params[] = $userID;
}
$query = $this->db->prepare($sql);
$query->execute($params);
}
}
/**
* @param string $path
* @param int $type self::LOCK_SHARED or self::LOCK_EXCLUSIVE
* @return bool
*/
public function isLocked($path, $type)
{
$query = $this->connection->prepare('SELECT `lock` from `*PREFIX*file_locks` WHERE `key` = ?');
$query->execute([$path]);
$lockValue = (int) $query->fetchColumn();
if ($type === self::LOCK_SHARED) {
return $lockValue > 0;
} else {
if ($type === self::LOCK_EXCLUSIVE) {
return $lockValue === -1;
} else {
return false;
}
}
}
public function getMountsForUser(IUser $user, IStorageFactory $loader)
{
$query = $this->connection->prepare('
SELECT `remote`, `share_token`, `password`, `mountpoint`, `owner`
FROM `*PREFIX*share_external`
WHERE `user` = ? AND `accepted` = ?
');
$query->execute([$user->getUID(), 1]);
$mounts = [];
while ($row = $query->fetch()) {
$row['manager'] = $this;
$row['token'] = $row['share_token'];
$mounts[] = $this->getMount($user, $row, $loader);
}
return $mounts;
}
/**
* Adds a change record to the calendarchanges table.
*
* @param mixed $calendarId
* @param string $objectUri
* @param int $operation 1 = add, 2 = modify, 3 = delete.
* @return void
*/
protected function addChange($calendarId, $objectUri, $operation)
{
$stmt = $this->db->prepare('INSERT INTO `*PREFIX*calendarchanges` (`uri`, `synctoken`, `calendarid`, `operation`) SELECT ?, `synctoken`, ?, ? FROM `*PREFIX*calendars` WHERE `id` = ?');
$stmt->execute([$objectUri, $calendarId, $operation, $calendarId]);
$stmt = $this->db->prepare('UPDATE `*PREFIX*calendars` SET `synctoken` = `synctoken` + 1 WHERE `id` = ?');
$stmt->execute([$calendarId]);
}
/**
* finds out whether the user has active shares. The result is stored in
* $this->hasActiveShares
*/
protected function determineShares()
{
$query = $this->db->prepare('
SELECT COUNT(`uid_owner`)
FROM `*PREFIX*share`
WHERE `uid_owner` = ?
', 1);
$query->execute(array($this->ocName));
$sResult = $query->fetchColumn(0);
if (intval($sResult) === 1) {
$this->hasActiveShares = true;
return;
}
$query = $this->db->prepare('
SELECT COUNT(`owner`)
FROM `*PREFIX*share_external`
WHERE `owner` = ?
', 1);
$query->execute(array($this->ocName));
$sResult = $query->fetchColumn(0);
if (intval($sResult) === 1) {
$this->hasActiveShares = true;
return;
}
$this->hasActiveShares = false;
}
/**
* Adds a change record to the addressbookchanges table.
*
* @param mixed $addressBookId
* @param string $objectUri
* @param int $operation 1 = add, 2 = modify, 3 = delete
* @return void
*/
protected function addChange($addressBookId, $objectUri, $operation)
{
$sql = 'INSERT INTO `*PREFIX*addressbookchanges`(`uri`, `synctoken`, `addressbookid`, `operation`) SELECT ?, `synctoken`, ?, ? FROM `*PREFIX*addressbooks` WHERE `id` = ?';
$stmt = $this->db->prepare($sql);
$stmt->execute([$objectUri, $addressBookId, $operation, $addressBookId]);
$stmt = $this->db->prepare('UPDATE `*PREFIX*addressbooks` SET `synctoken` = `synctoken` + 1 WHERE `id` = ?');
$stmt->execute([$addressBookId]);
}
/**
* Delete all entries we dealt with
*
* @param array $affectedUsers
* @param int $maxTime
*/
public function deleteSentItems($affectedUsers, $maxTime)
{
$placeholders = implode(',', array_fill(0, sizeof($affectedUsers), '?'));
$queryParams = $affectedUsers;
array_unshift($queryParams, (int) $maxTime);
$query = $this->connection->prepare('DELETE FROM `*PREFIX*activity_mq` ' . ' WHERE `amq_timestamp` <= ? ' . ' AND `amq_affecteduser` IN (' . $placeholders . ')');
$query->execute($queryParams);
}
/**
* find a folder in the cache which has not been fully scanned
*
* If multiple incomplete folders are in the cache, the one with the highest id will be returned,
* use the one with the highest id gives the best result with the background scanner, since that is most
* likely the folder where we stopped scanning previously
*
* @return string|bool the path of the folder or false when no folder matched
*/
public function getIncomplete()
{
$query = $this->connection->prepare('SELECT `path` FROM `*PREFIX*filecache`' . ' WHERE `storage` = ? AND `size` = -1 ORDER BY `fileid` DESC', 1);
$query->execute([$this->getNumericStorageId()]);
if ($row = $query->fetch()) {
return $row['path'];
} else {
return false;
}
}
public function removeShare($mountPoint)
{
$mountPoint = $this->stripPath($mountPoint);
$hash = md5($mountPoint);
$query = $this->connection->prepare('
DELETE FROM `*PREFIX*share_external`
WHERE `mountpoint_hash` = ?
AND `user` = ?
');
return (bool) $query->execute(array($hash, $this->uid));
}
/**
* returns a batch of users from the mappings table
* @param int $limit
* @param int $offset
* @return array
*/
public function getMappedUsers($limit, $offset)
{
$query = $this->db->prepare('
SELECT
`ldap_dn` AS `dn`,
`owncloud_name` AS `name`,
`directory_uuid` AS `uuid`
FROM `*PREFIX*ldap_user_mapping`', $limit, $offset);
$query->execute();
return $query->fetchAll();
}
/**
* return a list of shares for the user
*
* @param bool|null $accepted True for accepted only,
* false for not accepted,
* null for all shares of the user
* @return array list of open server-to-server shares
*/
private function getShares($accepted)
{
$query = 'SELECT * FROM `*PREFIX*share_external` WHERE `user` = ?';
$parameters = [$this->uid];
if (!is_null($accepted)) {
$query .= ' AND `accepted` = ?';
$parameters[] = (int) $accepted;
}
$query .= ' ORDER BY `id` ASC';
$shares = $this->connection->prepare($query);
$result = $shares->execute($parameters);
return $result ? $shares->fetchAll() : [];
}
/**
* Delete activities that match certain conditions
*
* @param array $conditions Array with conditions that have to be met
* 'field' => 'value' => `field` = 'value'
* 'field' => array('value', 'operator') => `field` operator 'value'
* @return null
*/
public function deleteActivities($conditions)
{
$sqlWhere = '';
$sqlParameters = $sqlWhereList = array();
foreach ($conditions as $column => $comparison) {
$sqlWhereList[] = " `{$column}` " . (is_array($comparison) && isset($comparison[1]) ? $comparison[1] : '=') . ' ? ';
$sqlParameters[] = is_array($comparison) ? $comparison[0] : $comparison;
}
if (!empty($sqlWhereList)) {
$sqlWhere = ' WHERE ' . implode(' AND ', $sqlWhereList);
}
$query = $this->connection->prepare('DELETE FROM `*PREFIX*activity`' . $sqlWhere);
$query->execute($sqlParameters);
}
public function getScannedFiles($userId = NULL)
{
$sql = 'SELECT `file_id` FROM `*PREFIX*music_tracks`';
$params = array();
if ($userId) {
$sql .= ' WHERE `user_id` = ?';
$params = array($userId);
}
$query = $this->db->prepare($sql);
// TODO: switch to executeQuery with 8.0
$query->execute($params);
$fileIds = array_map(function ($i) {
return $i['file_id'];
}, $query->fetchAll());
return $fileIds;
}
private function createDummyS2SShares()
{
$query = $this->connection->prepare('
INSERT INTO `*PREFIX*share_external`
(`remote`, `share_token`, `password`, `name`, `owner`, `user`, `mountpoint`, `mountpoint_hash`, `remote_id`, `accepted`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
');
$users = array('user1', 'user2', 'user3');
for ($i = 0; $i < 10; $i++) {
$user = $users[$i % 3];
$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));
}
/**
* @param string $path
* @param int $type self::LOCK_SHARED or self::LOCK_EXCLUSIVE
* @return bool
*/
public function isLocked($path, $type) {
if ($this->hasAcquiredLock($path, $type)) {
return true;
}
$query = $this->connection->prepare('SELECT `lock` from `*PREFIX*file_locks` WHERE `key` = ?');
$query->execute([$path]);
$lockValue = (int)$query->fetchColumn();
if ($type === self::LOCK_SHARED) {
if ($this->isLocallyLocked($path)) {
// if we have a shared lock we kept open locally but it's released we always have at least 1 shared lock in the db
return $lockValue > 1;
} else {
return $lockValue > 0;
}
} else if ($type === self::LOCK_EXCLUSIVE) {
return $lockValue === -1;
} else {
return false;
}
}
/**
* set the lastRun of $job to now
*
* @param Job $job
*/
public function setLastRun($job)
{
$query = $this->conn->prepare('UPDATE `*PREFIX*jobs` SET `last_run` = ? WHERE `id` = ?');
$query->execute(array(time(), $job->getId()));
}
/**
* This method is called after a successful MOVE
*
* @param string $source
* @param string $destination
*
* @return void
*/
public function move($source, $destination)
{
$statement = $this->connection->prepare('UPDATE `*PREFIX*properties` SET `propertypath` = ?' . ' WHERE `userid` = ? AND `propertypath` = ?');
$statement->execute(array('/' . $destination, $this->user, '/' . $source));
$statement->closeCursor();
}
/**
* Truncate's the mapping table
* @return bool
*/
public function clear()
{
$sql = $this->dbc->getDatabasePlatform()->getTruncateTableSQL('`' . $this->getTableName() . '`');
return $this->dbc->prepare($sql)->execute();
}
/**
* Used to abstract the ownCloud database access away
* @param string $sql the sql query with ? placeholder for params
* @param int $limit the maximum number of rows
* @param int $offset from which row we want to start
* @return \Doctrine\DBAL\Driver\Statement The prepared statement.
*/
public function prepare($sql, $limit = null, $offset = null)
{
return $this->connection->prepare($sql, $limit, $offset);
}
/**
* Used to abstract the owncloud database access away
*
* @param string $sql the sql query with ? placeholder for params
* @param int $limit the maximum number of rows
* @param int $offset from which row we want to start
* @return \OC_DB_StatementWrapper prepared SQL query
*/
public function prepareQuery($sql, $limit = null, $offset = null)
{
$isManipulation = \OC_DB::isManipulation($sql);
$statement = $this->connection->prepare($sql, $limit, $offset);
return new \OC_DB_StatementWrapper($statement, $isManipulation);
}
private function getLockEntryCount()
{
$query = $this->connection->prepare('SELECT count(*) FROM `*PREFIX*file_locks`');
$query->execute();
return $query->fetchColumn();
}
/**
* return a list of shares which are not yet accepted by the user
*
* @return array list of open server-to-server shares
*/
public function getOpenShares()
{
$openShares = $this->connection->prepare('SELECT * FROM `*PREFIX*share_external` WHERE `accepted` = ? AND `user` = ?');
$result = $openShares->execute(array(0, $this->userSession->getUser()->getUID()));
return $result ? $openShares->fetchAll() : array();
}
