PHP OC_DB::prepare方法代码示例

 function cleanDB()
 {
     $query1 = OC_DB::prepare('DELETE FROM *PREFIX*bookmarks');
     $query1->execute();
     $query2 = OC_DB::prepare('DELETE FROM *PREFIX*bookmarks_tags');
     $query2->execute();
 }

 public static function check()
 {
     // get mimetype code for directory
     $query = \OC_DB::prepare('SELECT `id` FROM `*PREFIX*mimetypes` WHERE `mimetype` = ?');
     $result = $query->execute(array('httpd/unix-directory'));
     if ($row = $result->fetchRow()) {
         $dir_mimetype = $row['id'];
     } else {
         $dir_mimetype = 0;
     }
     // locate files that are not checked yet
     $sql = 'SELECT `*PREFIX*filecache`.`fileid`, `path`, `*PREFIX*storages`.`id`' . ' FROM `*PREFIX*filecache`' . ' LEFT JOIN `*PREFIX*files_antivirus` ON `*PREFIX*files_antivirus`.`fileid` = `*PREFIX*filecache`.`fileid`' . ' JOIN `*PREFIX*storages` ON `*PREFIX*storages`.`numeric_id` = `*PREFIX*filecache`.`storage`' . ' WHERE `mimetype` != ? AND `*PREFIX*storages`.`id` LIKE ? AND (`*PREFIX*files_antivirus`.`fileid` IS NULL OR `mtime` > `check_time`)';
     $stmt = OCP\DB::prepare($sql, 5);
     try {
         $result = $stmt->execute(array($dir_mimetype, 'local::%'));
         if (\OC_DB::isError($result)) {
             \OC_Log::write('files_antivirus', __METHOD__ . 'DB error: ' . \OC_DB::getErrorMessage($result), \OCP\Util::ERROR);
             return;
         }
     } catch (\Exception $e) {
         \OCP\Util::writeLog('files_antivirus', __METHOD__ . ', exception: ' . $e->getMessage(), \OCP\Util::ERROR);
         return;
     }
     // scan the found files
     while ($row = $result->fetchRow()) {
         $storage = self::getStorage($row['id']);
         if ($storage !== null) {
             self::scan($row['fileid'], $row['path'], $storage);
         } else {
             \OCP\Util::writeLog('files_antivirus', 'File "' . $row['path'] . '" has a non local storage backend "' . $row['id'] . '"', \OCP\Util::ERROR);
         }
     }
 }

 /**
  * test if the mount point moves up if the parent folder no longer exists
  */
 function testShareMountLoseParentFolder()
 {
     // share to user
     $fileinfo = $this->view->getFileInfo($this->folder);
     $result = \OCP\Share::shareItem('folder', $fileinfo['fileid'], \OCP\Share::SHARE_TYPE_USER, self::TEST_FILES_SHARING_API_USER2, 31);
     $statement = "UPDATE `*PREFIX*share` SET `file_target` = ? where `share_with` = ?";
     $query = \OC_DB::prepare($statement);
     $arguments = array('/foo/bar' . $this->folder, self::TEST_FILES_SHARING_API_USER2);
     $query->execute($arguments);
     $query = \OC_DB::prepare('SELECT * FROM `*PREFIX*share`');
     $result = $query->execute();
     $shares = $result->fetchAll();
     $this->assertSame(1, count($shares));
     $share = reset($shares);
     $this->assertSame('/foo/bar' . $this->folder, $share['file_target']);
     self::loginHelper(self::TEST_FILES_SHARING_API_USER2);
     // share should have moved up
     $query = \OC_DB::prepare('SELECT * FROM `*PREFIX*share`');
     $result = $query->execute();
     $shares = $result->fetchAll();
     $this->assertSame(1, count($shares));
     $share = reset($shares);
     $this->assertSame($this->folder, $share['file_target']);
     //cleanup
     self::loginHelper(self::TEST_FILES_SHARING_API_USER1);
     \OCP\Share::unshare('folder', $fileinfo['fileid'], \OCP\Share::SHARE_TYPE_USER, self::TEST_FILES_SHARING_API_USER2);
     $this->view->unlink($this->folder);
 }

 public function getChildren($itemSource)
 {
     $children = array();
     $parents = array($itemSource);
     $query = \OC_DB::prepare('SELECT `id` FROM `*PREFIX*mimetypes` WHERE `mimetype` = ?');
     $result = $query->execute(array('httpd/unix-directory'));
     if ($row = $result->fetchRow()) {
         $mimetype = $row['id'];
     } else {
         $mimetype = -1;
     }
     while (!empty($parents)) {
         $parents = "'" . implode("','", $parents) . "'";
         $query = OC_DB::prepare('SELECT `fileid`, `name`, `mimetype` FROM `*PREFIX*filecache`' . ' WHERE `parent` IN (' . $parents . ')');
         $result = $query->execute();
         $parents = array();
         while ($file = $result->fetchRow()) {
             $children[] = array('source' => $file['fileid'], 'file_path' => $file['name']);
             // If a child folder is found look inside it
             if ($file['mimetype'] == $mimetype) {
                 $parents[] = $file['fileid'];
             }
         }
     }
     return $children;
 }

/**
 * lookup file path and owner by fetching it from the fscache
 * needed becaus OC_FileCache::getPath($id, $user) already requires the user
 * @param int $id
 * @return array
 */
function getPathAndUser($id)
{
    $query = \OC_DB::prepare('SELECT `user`, `path` FROM `*PREFIX*fscache` WHERE `id` = ?');
    $result = $query->execute(array($id));
    $row = $result->fetchRow();
    return $row;
}

 /**
  * Function that is called after a user is added to a group.
  * TODO what does it do?
  * @param array $arguments
  */
 public static function post_addToGroup($arguments)
 {
     // Find the group shares and check if the user needs a unique target
     $query = \OC_DB::prepare('SELECT * FROM `*PREFIX*share` WHERE `share_type` = ? AND `share_with` = ?');
     $result = $query->execute(array(self::SHARE_TYPE_GROUP, $arguments['gid']));
     $query = \OC_DB::prepare('INSERT INTO `*PREFIX*share` (`item_type`, `item_source`,' . ' `item_target`, `parent`, `share_type`, `share_with`, `uid_owner`, `permissions`,' . ' `stime`, `file_source`, `file_target`) VALUES (?,?,?,?,?,?,?,?,?,?,?)');
     while ($item = $result->fetchRow()) {
         $sourceExists = \OC\Share\Share::getItemSharedWithBySource($item['item_type'], $item['item_source'], self::FORMAT_NONE, null, true, $arguments['uid']);
         if ($sourceExists) {
             $fileTarget = $sourceExists['file_target'];
             $itemTarget = $sourceExists['item_target'];
         } else {
             $itemTarget = Helper::generateTarget($item['item_type'], $item['item_source'], self::SHARE_TYPE_USER, $arguments['uid'], $item['owner'], null, $item['parent']);
             // do we also need a file target
             if ($item['item_type'] === 'file' || $item['item_type'] === 'folder') {
                 $fileTarget = Helper::generateTarget('file', $item['file_target'], self::SHARE_TYPE_USER, $arguments['uid'], $item['owner'], null, $item['parent']);
             } else {
                 $fileTarget = null;
             }
         }
         // Insert an extra row for the group share if the item or file target is unique for this user
         if ($itemTarget != $item['item_target'] || $fileTarget != $item['file_target']) {
             $query->execute(array($item['item_type'], $item['item_source'], $itemTarget, $item['id'], self::$shareTypeGroupUserUnique, $arguments['uid'], $item['uid_owner'], $item['permissions'], $item['stime'], $item['file_source'], $fileTarget));
             \OC_DB::insertid('*PREFIX*share');
         }
     }
 }

    private static function updateByNameStmt()
    {
        return \OC_DB::prepare('
			UPDATE `*PREFIX*filecache`
			SET `mimetype` = ?
			WHERE `mimetype` <> ? AND `name` ILIKE ?
		');
    }

 /**
  * find the user that can be authenticated with an openid identity
  */
 public static function findUserForIdentity($identity)
 {
     $query = OC_DB::prepare('SELECT userid FROM *PREFIX*preferences WHERE appid=? AND configkey=? AND configvalue=?');
     $result = $query->execute(array('user_openid', 'identity', $identity))->fetchAll();
     if (count($result) > 0) {
         return $result[0]['userid'];
     } else {
         return false;
     }
 }

 /**
  * Retrieves the contents of a trash bin directory.
  * @param string $dir path to the directory inside the trashbin
  * or empty to retrieve the root of the trashbin
  * @return array of files
  */
 public static function getTrashFiles($dir)
 {
     $result = array();
     $user = \OCP\User::getUser();
     if ($dir && $dir !== '/') {
         $view = new \OC_Filesystemview('/' . $user . '/files_trashbin/files');
         $dirContent = $view->opendir($dir);
         if ($dirContent === false) {
             return null;
         }
         if (is_resource($dirContent)) {
             while (($entryName = readdir($dirContent)) !== false) {
                 if (!\OC\Files\Filesystem::isIgnoredDir($entryName)) {
                     $pos = strpos($dir . '/', '/', 1);
                     $tmp = substr($dir, 0, $pos);
                     $pos = strrpos($tmp, '.d');
                     $timestamp = substr($tmp, $pos + 2);
                     $result[] = array('id' => $entryName, 'timestamp' => $timestamp, 'mime' => $view->getMimeType($dir . '/' . $entryName), 'type' => $view->is_dir($dir . '/' . $entryName) ? 'dir' : 'file', 'location' => $dir);
                 }
             }
             closedir($dirContent);
         }
     } else {
         $query = \OC_DB::prepare('SELECT `id`,`location`,`timestamp`,`type`,`mime` FROM `*PREFIX*files_trash` WHERE `user` = ?');
         $result = $query->execute(array($user))->fetchAll();
     }
     $files = array();
     $id = 0;
     foreach ($result as $r) {
         $i = array();
         $i['id'] = $id++;
         $i['name'] = $r['id'];
         $i['date'] = \OCP\Util::formatDate($r['timestamp']);
         $i['timestamp'] = $r['timestamp'];
         $i['etag'] = $i['timestamp'];
         // dummy etag
         $i['mimetype'] = $r['mime'];
         $i['type'] = $r['type'];
         if ($i['type'] === 'file') {
             $fileinfo = pathinfo($r['id']);
             $i['basename'] = $fileinfo['filename'];
             $i['extension'] = isset($fileinfo['extension']) ? '.' . $fileinfo['extension'] : '';
         }
         $i['directory'] = $r['location'];
         if ($i['directory'] === '/') {
             $i['directory'] = '';
         }
         $i['permissions'] = \OCP\PERMISSION_READ;
         $i['isPreviewAvailable'] = \OC::$server->getPreviewManager()->isMimeSupported($r['mime']);
         $i['icon'] = \OCA\Files\Helper::determineIcon($i);
         $files[] = $i;
     }
     usort($files, array('\\OCA\\Files\\Helper', 'fileCmp'));
     return $files;
 }

    private static function updateByNameStmt()
    {
        return \OC_DB::prepare('
			UPDATE `*PREFIX*filecache`
			SET `mimetype` = (
				SELECT `id`
				FROM `*PREFIX*mimetypes`
				WHERE `mimetype` = ?
			) WHERE `name` LIKE ?
		');
    }

 /**
  * Function that is called after a user is deleted. Cleans up the shares of that user.
  * @param array $arguments
  */
 public static function post_deleteUser($arguments)
 {
     // Delete any items shared with the deleted user
     $query = \OC_DB::prepare('DELETE FROM `*PREFIX*share`' . ' WHERE `share_with` = ? AND `share_type` = ? OR `share_type` = ?');
     $query->execute(array($arguments['uid'], self::SHARE_TYPE_USER, self::$shareTypeGroupUserUnique));
     // Delete any items the deleted user shared
     $query = \OC_DB::prepare('SELECT `id` FROM `*PREFIX*share` WHERE `uid_owner` = ?');
     $result = $query->execute(array($arguments['uid']));
     while ($item = $result->fetchRow()) {
         Helper::delete($item['id']);
     }
 }

 /**
  * @param array $data
  * @return array
  */
 public function add(array $data)
 {
     $SQL = "INSERT INTO {$this->tableName} (`id`, `type`, `text`, `users`, `start_date`, `end_date`, `duration`, `order`, `progress`, `sortorder`, `parent`, `open`, `buffers`) VALUES ";
     //$this->connect->setSessionTimeZoneToZero();
     //$this->connect->db->executeQuery("SET @@session.time_zone = '00:00'");
     //$query = \OC_DB::prepare('SET @@session.time_zone = "00:00"');
     //$query = \OC_DB::prepare('SET GLOBAL time_zone = "00:00"');
     $query = \OC_DB::prepare("set @@session.time_zone = '+00:00'");
     $query->execute();
     //$this->connect->db->executeQuery("SET time_zone = 'UTC'");
     $rowData = [];
     for ($iRow = 0; $iRow < count($data); $iRow++) {
         $SQL .= (empty($rowData) ? '' : ',') . "( :id_{$iRow}, :type_{$iRow}, :text_{$iRow}, :users_{$iRow}, :start_date_{$iRow}, :end_date_{$iRow}, :duration_{$iRow}, :order_{$iRow}, :progress_{$iRow}, :sortorder_{$iRow}, :parent_{$iRow}, :open_{$iRow}, :buffers_{$iRow} )";
         for ($i = 0; $i < count($this->fields); $i++) {
             $field = $this->fields[$i];
             $value = $data[$iRow][$field];
             if (isset($data[$iRow][$field])) {
                 if ($field == 'id') {
                     $value = (int) $value;
                 } elseif ($field == 'type') {
                     $value = (string) $value;
                 } elseif ($field == 'text') {
                     $value = (string) $value;
                 } elseif ($field == 'users') {
                     $value = (string) $value;
                 } elseif ($field == 'start_date') {
                     $value = empty($value) ? date("Y-m-d H:i:s", time()) : $value;
                 } elseif ($field == 'end_date') {
                     $value = empty($value) ? date("Y-m-d H:i:s", time() + 3600 * 24 * 7) : $value;
                 } elseif ($field == 'duration') {
                     $value = (int) (empty($value) ? 0 : $value);
                 } elseif ($field == 'order') {
                     $value = (int) (empty($value) ? 0 : $value);
                 } elseif ($field == 'progress') {
                     $value = (double) (empty($value) ? 0 : $value);
                 } elseif ($field == 'sortorder') {
                     $value = (int) (empty($value) ? 0 : $value);
                 } elseif ($field == 'parent') {
                     $value = (int) (empty($value) ? 1 : $value);
                 } elseif ($field == 'open') {
                     $value = (int) (empty($value) ? 1 : $value);
                 } elseif ($field == 'buffers') {
                     $value = (string) (empty($value) ? '' : $value);
                 }
                 $rowData[":{$field}_{$iRow}"] = $value;
             } else {
                 $rowData[":{$field}_{$iRow}"] = null;
             }
         }
     }
     return $this->connect->db->prepare($SQL)->execute($rowData);
 }

 /**
  * @brief remove all shares for a given file if the file was deleted
  *
  * @param string $path
  */
 private static function removeShare($path)
 {
     $fileSource = self::$toRemove[$path];
     if (!\OC\Files\Filesystem::file_exists($path)) {
         $query = \OC_DB::prepare('DELETE FROM `*PREFIX*share` WHERE `file_source`=?');
         try {
             \OC_DB::executeAudited($query, array($fileSource));
         } catch (\Exception $e) {
             \OCP\Util::writeLog('files_sharing', "can't remove share: " . $e->getMessage(), \OCP\Util::WARN);
         }
     }
     unset(self::$toRemove[$path]);
 }

 private static function addToken($token, $appUrl, $userAddress, $dataScope)
 {
     $user = OC_User::getUser();
     $query = OC_DB::prepare("INSERT INTO *PREFIX*authtoken (`token`,`appUrl`,`user`,`userAddress`,`dataScope`) VALUES(?,?,?,?,?)");
     $result = $query->execute(array($token, $appUrl, $user, $userAddress, $dataScope));
     if (PEAR::isError($result)) {
         $entry = 'DB Error: "' . $result->getMessage() . '"<br />';
         $entry .= 'Offending command was: ' . $result->getDebugInfo() . '<br />';
         if (defined("DEBUG") && DEBUG) {
             error_log($entry);
         }
         die($entry);
     }
 }

 /**
  * get the next fileid in the cache
  *
  * @param int $previous
  * @param bool $folder
  * @return int
  */
 private static function getNextFileId($previous, $folder)
 {
     if ($folder) {
         $stmt = \OC_DB::prepare('SELECT `fileid` FROM `*PREFIX*filecache` WHERE `fileid` > ? AND `mimetype` = ? ORDER BY `fileid` ASC', 1);
     } else {
         $stmt = \OC_DB::prepare('SELECT `fileid` FROM `*PREFIX*filecache` WHERE `fileid` > ? AND `mimetype` != ? ORDER BY `fileid` ASC', 1);
     }
     $result = \OC_DB::executeAudited($stmt, array($previous, self::getFolderMimetype()));
     if ($row = $result->fetchRow()) {
         return $row['fileid'];
     } else {
         return 0;
     }
 }