PHP IDBConnection::prepare方法代码示例

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