add limit support to OC_DB & OCP/DB

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().'"<br />';
 				$entry .= 'Offending command was: '.$query.'<br />';
 				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().'"<br />';
 				$entry .= 'Offending command was: '.$query.'<br />';
 				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));
 	}