diff options
| -rw-r--r-- | apps/bookmarks/ajax/addBookmark.php | 12 | ||||
| -rw-r--r-- | apps/bookmarks/ajax/editBookmark.php | 2 | ||||
| -rw-r--r-- | apps/bookmarks/ajax/updateList.php | 55 | ||||
| -rw-r--r-- | lib/db.php | 11 |
4 files changed, 59 insertions, 21 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)); diff --git a/lib/db.php b/lib/db.php index fa3995be125..78a4da10ce8 100644 --- a/lib/db.php +++ b/lib/db.php @@ -28,7 +28,7 @@ class OC_DB { const BACKEND_PDO=0; const BACKEND_MDB2=1; - static private $connection; //the prefered conenction to use, either PDO or MDB2 + static private $connection; //the prefered connection to use, either PDO or MDB2 static private $backend=null; static private $MDB2=false; static private $PDO=false; @@ -480,4 +480,13 @@ class PDOStatementWrapper{ public function __call($name,$arguments){ return call_user_func_array(array($this->statement,$name),$arguments); } + + /** + * Provide a simple fetchOne. + * fetch single column from the next row + * @param int $colnum the column number to fetch + */ + public function fetchOne($colnum = 0){ + return $this->statement->fetchColumn($colnum); + } } |