diff options
| -rw-r--r-- | CHANGELOG | 1 | ||||
| -rw-r--r-- | INSTALL | 4 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_db_mssql.php | 30 | ||||
| -rw-r--r-- | program/lib/Roundcube/rcube_db_sqlsrv.php | 28 |
4 files changed, 33 insertions, 30 deletions
@@ -1,6 +1,7 @@ CHANGELOG Roundcube Webmail =========================== +- Fix LIMIT/OFFSET queries handling on MS SQL Server (#1488984) - Fix so task name can really contain all from a-z0-9_- characters (#1488941) - Support IMAP MOVE extension [RFC 6851] - Fix javascript errors when working in a page opened with taget="_blank" @@ -34,7 +34,7 @@ REQUIREMENTS - magic_quotes_runtime disabled - magic_quotes_sybase disabled * PHP compiled with OpenSSL to connect to IMAPS and to use the spell checker -* A MySQL (4.0.8 or newer), PostgreSQL, MSSQL database engine +* A MySQL (4.0.8 or newer), PostgreSQL, MS SQL Server (2005 or newer) database engine or SQLite support in PHP * One of the above databases with permission to create tables * An SMTP server (recommended) or PHP configured for mail delivery @@ -232,5 +232,3 @@ $HTTP["host"] == "www.example.com" { compress.filetype = ("text/plain", "text/html", "text/javascript", "text/css", "text/xml", "image/gif", "image/png") } - - diff --git a/program/lib/Roundcube/rcube_db_mssql.php b/program/lib/Roundcube/rcube_db_mssql.php index a1ce80a87..37a42678a 100644 --- a/program/lib/Roundcube/rcube_db_mssql.php +++ b/program/lib/Roundcube/rcube_db_mssql.php @@ -100,30 +100,30 @@ class rcube_db_mssql extends rcube_db { $limit = intval($limit); $offset = intval($offset); + $end = $offset + $limit; - $orderby = stristr($query, 'ORDER BY'); - if ($orderby !== false) { - $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc'; - $order = str_ireplace('ORDER BY', '', $orderby); - $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order)); - } - - $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($limit + $offset) . ' ', $query); - + // query without OFFSET if (!$offset) { + $query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query); return $query; } - $query = 'SELECT * FROM (SELECT TOP ' . $limit . ' * FROM (' . $query . ') AS inner_tbl'; + $orderby = stristr($query, 'ORDER BY'); + $offset += 1; + if ($orderby !== false) { - $query .= ' ORDER BY ' . $order . ' '; - $query .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC'; + $query = trim(substr($query, 0, -1 * strlen($orderby))); } - $query .= ') AS outer_tbl'; - if ($orderby !== false) { - $query .= ' ORDER BY ' . $order . ' ' . $sort; + else { + // it shouldn't happen, paging without sorting has not much sense + // @FIXME: I don't know how to build paging query without ORDER BY + $orderby = "ORDER BY 1"; } + $query = preg_replace('/^SELECT\s/i', '', $query); + $query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)" + . " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]"; + return $query; } diff --git a/program/lib/Roundcube/rcube_db_sqlsrv.php b/program/lib/Roundcube/rcube_db_sqlsrv.php index e69678025..e5dfb1154 100644 --- a/program/lib/Roundcube/rcube_db_sqlsrv.php +++ b/program/lib/Roundcube/rcube_db_sqlsrv.php @@ -100,26 +100,30 @@ class rcube_db_sqlsrv extends rcube_db { $limit = intval($limit); $offset = intval($offset); + $end = $offset + $limit; - $orderby = stristr($query, 'ORDER BY'); - if ($orderby !== false) { - $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc'; - $order = str_ireplace('ORDER BY', '', $orderby); - $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order)); + // query without OFFSET + if (!$offset) { + $query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query); + return $query; } - $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($limit + $offset) . ' ', $query); + $orderby = stristr($query, 'ORDER BY'); + $offset += 1; - $query = 'SELECT * FROM (SELECT TOP ' . $limit . ' * FROM (' . $query . ') AS inner_tbl'; if ($orderby !== false) { - $query .= ' ORDER BY ' . $order . ' '; - $query .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC'; + $query = trim(substr($query, 0, -1 * strlen($orderby))); } - $query .= ') AS outer_tbl'; - if ($orderby !== false) { - $query .= ' ORDER BY ' . $order . ' ' . $sort; + else { + // it shouldn't happen, paging without sorting has not much sense + // @FIXME: I don't know how to build paging query without ORDER BY + $orderby = "ORDER BY 1"; } + $query = preg_replace('/^SELECT\s/i', '', $query); + $query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)" + . " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]"; + return $query; } |