From e13f381189788c74fe08a3f3336d961977ff9dc4 Mon Sep 17 00:00:00 2001 From: =?utf8?q?J=C3=B6rn=20Friedrich=20Dreyer?= Date: Mon, 30 Jul 2012 22:42:43 +0200 Subject: [PATCH] add limit support to OC_DB & OCP/DB --- apps/bookmarks/lib/bookmarks.php | 9 +++-- apps/files_sharing/lib_share.php | 14 ++++---- apps/remoteStorage/lib_remoteStorage.php | 6 ++-- lib/db.php | 46 ++++++++++++++++++++++-- lib/public/db.php | 4 +-- 5 files changed, 59 insertions(+), 20 deletions(-) diff --git a/apps/bookmarks/lib/bookmarks.php b/apps/bookmarks/lib/bookmarks.php index 4b9e3d6438a..d569bf528a1 100644 --- a/apps/bookmarks/lib/bookmarks.php +++ b/apps/bookmarks/lib/bookmarks.php @@ -78,9 +78,8 @@ class OC_Bookmarks_Bookmarks{ `*PREFIX*bookmarks`.`user_id` = ? GROUP BY `id`, `url`, `title` '.$sqlFilterTag.' - ORDER BY `*PREFIX*bookmarks`.`'.$sqlSortColumn.'` DESC - LIMIT 10 - OFFSET '. $offset); + ORDER BY `*PREFIX*bookmarks`.`'.$sqlSortColumn.'` DESC', + 10,$offset); } else { if( $CONFIG_DBTYPE == 'sqlite' or $CONFIG_DBTYPE == 'sqlite3' ) $concatFunction = '(url || title || '; @@ -106,8 +105,8 @@ class OC_Bookmarks_Bookmarks{ AND `*PREFIX*bookmarks`.`user_id` = ? GROUP BY `url` '.$sqlFilterTag.' - ORDER BY `*PREFIX*bookmarks`.`'.$sqlSortColumn.'` DESC - LIMIT '.$offset.', 10'); + ORDER BY `*PREFIX*bookmarks`.`'.$sqlSortColumn.'` DESC', + 10, $offset); } $bookmarks = $query->execute($params)->fetchAll(); diff --git a/apps/files_sharing/lib_share.php b/apps/files_sharing/lib_share.php index 05a80b79161..fe312ad9ed7 100644 --- a/apps/files_sharing/lib_share.php +++ b/apps/files_sharing/lib_share.php @@ -92,7 +92,7 @@ class OC_Share { // Check if the target already exists for the user, if it does append a number to the name $sharedFolder = '/'.$uid.'/files/Shared'; $target = $sharedFolder."/".basename($source); - $checkTarget = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups($uid, false).' LIMIT 1'); + $checkTarget = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups($uid, false),1); $result = $checkTarget->execute(array($target))->fetchAll(); if (count($result) > 0) { if ($pos = strrpos($target, ".")) { @@ -222,7 +222,7 @@ class OC_Share { */ public static function getItem($target) { $target = self::cleanPath($target); - $query = OCP\DB::prepare('SELECT `uid_owner`, `source`, `permissions` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` = ? LIMIT 1'); + $query = OCP\DB::prepare('SELECT `uid_owner`, `source`, `permissions` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` = ?',1); return $query->execute(array($target, OCP\USER::getUser()))->fetchAll(); } @@ -279,7 +279,7 @@ class OC_Share { */ public static function getParentFolders($target) { $target = self::cleanPath($target); - $query = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups().' LIMIT 1'); + $query = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups(),1); // Prevent searching for user directory e.g. '/MTGap/files' $userDirectory = substr($target, 0, strpos($target, "files") + 5); $target = dirname($target); @@ -307,7 +307,7 @@ class OC_Share { */ public static function getSource($target) { $target = self::cleanPath($target); - $query = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups().' LIMIT 1'); + $query = OCP\DB::prepare('SELECT `source` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups(),1); $result = $query->execute(array($target))->fetchAll(); if (count($result) > 0) { return $result[0]['source']; @@ -323,7 +323,7 @@ class OC_Share { public static function getTarget($source) { $source = self::cleanPath($source); - $query = OCP\DB::prepare('SELECT `target` FROM `*PREFIX*sharing` WHERE `source` = ? AND `uid_owner` = ? LIMIT 1'); + $query = OCP\DB::prepare('SELECT `target` FROM `*PREFIX*sharing` WHERE `source` = ? AND `uid_owner` = ',1); $result = $query->execute(array($source, OCP\USER::getUser()))->fetchAll(); if (count($result) > 0) { return $result[0]['target']; @@ -340,7 +340,7 @@ class OC_Share { */ public static function getPermissions($target) { $target = self::cleanPath($target); - $query = OCP\DB::prepare('SELECT `permissions` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups().' LIMIT 1'); + $query = OCP\DB::prepare('SELECT `permissions` FROM `*PREFIX*sharing` WHERE `target` = ? AND `uid_shared_with` '.self::getUsersAndGroups(),1); $result = $query->execute(array($target))->fetchAll(); if (count($result) > 0) { return $result[0]['permissions']; @@ -372,7 +372,7 @@ class OC_Share { * @return The token of the public link, a sha1 hash */ public static function getTokenFromSource($source) { - $query = OCP\DB::prepare('SELECT `target` FROM `*PREFIX*sharing` WHERE `source` = ? AND `uid_shared_with` = ? AND `uid_owner` = ? LIMIT 1'); + $query = OCP\DB::prepare('SELECT `target` FROM `*PREFIX*sharing` WHERE `source` = ? AND `uid_shared_with` = ? AND `uid_owner` = ?',1); $result = $query->execute(array($source, self::PUBLICLINK, OCP\USER::getUser()))->fetchAll(); if (count($result) > 0) { return $result[0]['target']; diff --git a/apps/remoteStorage/lib_remoteStorage.php b/apps/remoteStorage/lib_remoteStorage.php index 7c7e455d823..b6934e87d67 100644 --- a/apps/remoteStorage/lib_remoteStorage.php +++ b/apps/remoteStorage/lib_remoteStorage.php @@ -2,7 +2,7 @@ class OC_remoteStorage { public static function getValidTokens($ownCloudUser, $category) { - $query=OCP\DB::prepare("SELECT `token`,`appUrl`,`category` FROM `*PREFIX*authtoken` WHERE `user`=? LIMIT 100"); + $query=OCP\DB::prepare("SELECT `token`,`appUrl`,`category` FROM `*PREFIX*authtoken` WHERE `user`=?",100); $result=$query->execute(array($ownCloudUser)); $ret = array(); while($row=$result->fetchRow()){ @@ -15,7 +15,7 @@ class OC_remoteStorage { public static function getTokenFor($appUrl, $categories) { $user=OCP\USER::getUser(); - $query=OCP\DB::prepare("SELECT `token` FROM `*PREFIX*authtoken` WHERE `user`=? AND `appUrl`=? AND `category`=? LIMIT 1"); + $query=OCP\DB::prepare("SELECT `token` FROM `*PREFIX*authtoken` WHERE `user`=? AND `appUrl`=? AND `category`=?",1); $result=$query->execute(array($user, $appUrl, $categories)); $ret = array(); if($row=$result->fetchRow()) { @@ -27,7 +27,7 @@ class OC_remoteStorage { public static function getAllTokens() { $user=OCP\USER::getUser(); - $query=OCP\DB::prepare("SELECT `token`,`appUrl`,`category` FROM `*PREFIX*authtoken` WHERE `user`=? LIMIT 100"); + $query=OCP\DB::prepare("SELECT `token`,`appUrl`,`category` FROM `*PREFIX*authtoken` WHERE `user`=?",100); $result=$query->execute(array($user)); $ret = array(); while($row=$result->fetchRow()){ diff --git a/lib/db.php b/lib/db.php index a2dbc5dfac9..0d272f63037 100644 --- a/lib/db.php +++ b/lib/db.php @@ -132,7 +132,7 @@ class OC_DB { $dsn='pgsql:dbname='.$name.';host='.$host; } break; - case 'oci': + case 'oci': // Oracle with PDO is unsupported if ($port) { $dsn = 'oci:dbname=//' . $host . ':' . $port . '/' . $name; } else { @@ -214,6 +214,19 @@ class OC_DB { 'database' => $name ); break; + case 'oci': + $dsn = array( + 'phptype' => 'oci8', + 'username' => $user, + 'password' => $pass, + ); + if ($host != '') { + $dsn['hostspec'] = $host; + $dsn['database'] = $name; + } else { // use dbname for hostspec + $dsn['hostspec'] = $name; + } + break; } // Try to establish connection @@ -242,8 +255,33 @@ class OC_DB { * * SQL query via MDB2 prepare(), needs to be execute()'d! */ - static public function prepare( $query ){ - // Optimize the query + static public function prepare( $query , $limit=null, $offset=null ){ + + if (!is_null($limit)) { + if (self::$backend == self::BACKEND_MDB2) { + //MDB2 uses or emulates limits & offset internally + self::$MDB2->setLimit($limit, $offset); + } else { + //PDO does not handle limit and offset. + //FIXME: check limit notation for other dbs + //the following sql thus might needs to take into account db ways of representing it + //(oracle has no LIMIT / OFFSET) + $limitsql = ' LIMIT ' . $limit; + if (!is_null($offset)) { + $limitsql .= ' OFFSET ' . $offset; + } + //insert limitsql + if (substr($query, -1) == ';') { //if query ends with ; + $query = substr($query, 0, -1) . $limitsql . ';'; + } else { + $query.=$limitsql; + } + } + } + + + + // Optimize the query $query = self::processQuery( $query ); self::connect(); @@ -256,6 +294,7 @@ class OC_DB { $entry = 'DB Error: "'.$result->getMessage().'"
'; $entry .= 'Offending command was: '.$query.'
'; OC_Log::write('core',$entry,OC_Log::FATAL); + error_log('DB error: '.$entry); die( $entry ); } }else{ @@ -265,6 +304,7 @@ class OC_DB { $entry = 'DB Error: "'.$e->getMessage().'"
'; $entry .= 'Offending command was: '.$query.'
'; OC_Log::write('core',$entry,OC_Log::FATAL); + error_log('DB error: '.$entry); die( $entry ); } $result=new PDOStatementWrapper($result); diff --git a/lib/public/db.php b/lib/public/db.php index af2e46c9da2..39df58bf8cf 100644 --- a/lib/public/db.php +++ b/lib/public/db.php @@ -43,8 +43,8 @@ class DB { * * SQL query via MDB2 prepare(), needs to be execute()'d! */ - static public function prepare( $query ){ - return(\OC_DB::prepare($query)); + static public function prepare( $query, $limit=null, $offset=null ){ + return(\OC_DB::prepare($query,$limit,$offset)); } -- 2.39.5