Fix bookmarks app to work with postgresql.

Rework the bookmark app to manage postgresql.
Add a fetchOne function into the PdoStmtWrapper
Fix a tipo in comments.

3 files changed, 49 insertions, 20 deletions

diff --git a/apps/bookmarks/ajax/addBookmark.php b/apps/bookmarks/ajax/addBookmark.php
index 0a7cdfc9be3..3975fd15f81 100644
--- a/apps/bookmarks/ajax/addBookmark.php
+++ b/apps/bookmarks/ajax/addBookmark.php
@@ -33,6 +33,8 @@ OC_JSON::checkAppEnabled('bookmarks');
 $CONFIG_DBTYPE = OC_Config::getValue( "dbtype", "sqlite" );
 if( $CONFIG_DBTYPE == 'sqlite' or $CONFIG_DBTYPE == 'sqlite3' ){
 	$_ut = "strftime('%s','now')";
+} elseif($CONFIG_DBTYPE == 'pgsql') {
+	$_ut = 'date_part(\'epoch\',now())::integer';
 } else {
 	$_ut = "UNIX_TIMESTAMP()";
 }
@@ -51,7 +53,15 @@ $params=array(
 	OC_User::getUser()
 	);
 $query->execute($params);
-$b_id = OC_DB::insertid();
+
+if($CONFIG_DBTYPE == 'pgsql')
+{
+	$query = OC_DB::prepare("SELECT currval('*PREFIX*bookmarks_id_seq')");
+	$b_id = $query->execute()->fetchOne();
+} else {
+	$b_id = OC_DB::insertid();
+}
+
 
 if($b_id !== false) {
 	$query = OC_DB::prepare("
diff --git a/apps/bookmarks/ajax/editBookmark.php b/apps/bookmarks/ajax/editBookmark.php
index e205f69bf5a..35f30ebcb7a 100644
--- a/apps/bookmarks/ajax/editBookmark.php
+++ b/apps/bookmarks/ajax/editBookmark.php
@@ -33,6 +33,8 @@ OC_JSON::checkAppEnabled('bookmarks');
 $CONFIG_DBTYPE = OC_Config::getValue( "dbtype", "sqlite" );
 if( $CONFIG_DBTYPE == 'sqlite' or $CONFIG_DBTYPE == 'sqlite3' ){
 	$_ut = "strftime('%s','now')";
+} elseif($CONFIG_DBTYPE == 'pgsql') {
+	$_ut = 'date_part(\'epoch\',now())::integer';
 } else {
 	$_ut = "UNIX_TIMESTAMP()";
 }
diff --git a/apps/bookmarks/ajax/updateList.php b/apps/bookmarks/ajax/updateList.php
index f2c81256bb6..7a5c0476b43 100644
--- a/apps/bookmarks/ajax/updateList.php
+++ b/apps/bookmarks/ajax/updateList.php
@@ -38,6 +38,9 @@ $filterTag = isset($_GET['tag']) ? '%' . htmlspecialchars_decode($_GET['tag']) .
 if($filterTag){
 	$sqlFilterTag = 'HAVING tags LIKE ?';
 	$params[] = $filterTag;
+	if($CONFIG_DBTYPE == 'pgsql' ) {
+		$sqlFilterTag = 'HAVING array_to_string(array_agg(tag), \' \')  LIKE ?';
+	}
 } else {
 	$sqlFilterTag = '';
 }
@@ -58,26 +61,40 @@ if( $CONFIG_DBTYPE == 'sqlite' or $CONFIG_DBTYPE == 'sqlite3' ){
 	$_gc_separator = 'SEPARATOR \' \'';
 }
 
-$query = OC_DB::prepare('
-	SELECT id, url, title, 
-	CASE WHEN *PREFIX*bookmarks.id = *PREFIX*bookmarks_tags.bookmark_id
-			THEN GROUP_CONCAT( tag ' .$_gc_separator. ' )
-			ELSE \' \'
-		END
-		AS tags
-	FROM *PREFIX*bookmarks
-	LEFT JOIN *PREFIX*bookmarks_tags ON 1=1
-	WHERE (*PREFIX*bookmarks.id = *PREFIX*bookmarks_tags.bookmark_id 
-			OR *PREFIX*bookmarks.id NOT IN (
-				SELECT *PREFIX*bookmarks_tags.bookmark_id FROM *PREFIX*bookmarks_tags
+if($CONFIG_DBTYPE == 'pgsql' ){
+	$query = OC_DB::prepare('
+		SELECT id, url, title, array_to_string(array_agg(tag), \' \') as tags
+		FROM *PREFIX*bookmarks
+		LEFT JOIN *PREFIX*bookmarks_tags ON *PREFIX*bookmarks.id = *PREFIX*bookmarks_tags.bookmark_id 
+		WHERE 
+			*PREFIX*bookmarks.user_id = ?
+		GROUP BY id, url, title
+		'.$sqlFilterTag.'
+		ORDER BY *PREFIX*bookmarks.'.$sqlSort.' 
+		LIMIT 10
+		OFFSET ?');
+} else {
+	$query = OC_DB::prepare('
+		SELECT id, url, title, 
+		CASE WHEN *PREFIX*bookmarks.id = *PREFIX*bookmarks_tags.bookmark_id
+				THEN GROUP_CONCAT( tag ' .$_gc_separator. ' )
+				ELSE \' \'
+			END
+			AS tags
+		FROM *PREFIX*bookmarks
+		LEFT JOIN *PREFIX*bookmarks_tags ON 1=1
+		WHERE (*PREFIX*bookmarks.id = *PREFIX*bookmarks_tags.bookmark_id 
+				OR *PREFIX*bookmarks.id NOT IN (
+					SELECT *PREFIX*bookmarks_tags.bookmark_id FROM *PREFIX*bookmarks_tags
+				)
 			)
-		)
-		AND *PREFIX*bookmarks.user_id = ?
-	GROUP BY url
-	'.$sqlFilterTag.'
-	ORDER BY *PREFIX*bookmarks.'.$sqlSort.' 
-	LIMIT ?,  10');
-	
+			AND *PREFIX*bookmarks.user_id = ?
+		GROUP BY url
+		'.$sqlFilterTag.'
+		ORDER BY *PREFIX*bookmarks.'.$sqlSort.' 
+		LIMIT ?,  10');
+}
+
 $bookmarks = $query->execute($params)->fetchAll();
 
 OC_JSON::success(array('data' => $bookmarks));