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