diff options
| -rw-r--r-- | program/include/rcube_db.php | 5 | ||||
| -rw-r--r-- | program/include/rcube_db_mssql.php | 44 | ||||
| -rw-r--r-- | program/include/rcube_db_mysql.php | 7 | ||||
| -rw-r--r-- | program/include/rcube_db_pgsql.php | 3 | ||||
| -rw-r--r-- | program/include/rcube_db_sqlite.php | 18 | ||||
| -rw-r--r-- | program/include/rcube_db_sqlsrv.php | 18 |
6 files changed, 66 insertions, 29 deletions
diff --git a/program/include/rcube_db.php b/program/include/rcube_db.php index feb16b2ac..2ee562395 100644 --- a/program/include/rcube_db.php +++ b/program/include/rcube_db.php @@ -463,6 +463,11 @@ class rcube_db /** * Adds LIMIT,OFFSET clauses to the query * + * @param string $query SQL query + * @param int $limit Number of rows + * @param int $offset Offset + * + * @return string SQL query */ protected function set_limit($query, $limit = 0, $offset = 0) { diff --git a/program/include/rcube_db_mssql.php b/program/include/rcube_db_mssql.php index 1f7328c3e..3a64c3b1d 100644 --- a/program/include/rcube_db_mssql.php +++ b/program/include/rcube_db_mssql.php @@ -31,17 +31,23 @@ */ class rcube_db_mssql extends rcube_db { + /** + * Driver initialization + */ protected function init() { $this->options['identifier_start'] = '['; $this->options['identifier_end'] = ']'; } + /** + * Character setting + */ protected function set_charset($charset) { + // UTF-8 is default } - /** * Return SQL function for current time and date * @@ -52,7 +58,6 @@ class rcube_db_mssql extends rcube_db return "getdate()"; } - /** * Return SQL statement to convert a field value into a unix timestamp * @@ -69,7 +74,6 @@ class rcube_db_mssql extends rcube_db return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; } - /** * Abstract SQL statement for value concatenation * @@ -86,28 +90,44 @@ class rcube_db_mssql extends rcube_db return '(' . join('+', $args) . ')'; } - /** * Adds TOP (LIMIT,OFFSET) clause to the query * + * @param string $query SQL query + * @param int $limit Number of rows + * @param int $offset Offset + * + * @return string SQL query */ protected function set_limit($query, $limit = 0, $offset = 0) { - // code from MDB2 package - if ($limit > 0) { - $fetch = $offset + $limit; - return preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i', - "\\1SELECT\\2 TOP $fetch", $query); + $limit = intval($limit); + $offset = intval($offset); + + $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)); } -// @TODO: proper OFFSET handling i _fetch_row() + $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($limit + $offset) . ' ', $query); + + $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 .= ') AS outer_tbl'; + if ($orderby !== false) { + $query .= ' ORDER BY ' . $order . ' ' . $sort; + } return $query; } - /** - * Returns PDO DSN string from DSN array (parse_dsn() result) + * Returns PDO DSN string from DSN array */ protected function dsn_string($dsn) { diff --git a/program/include/rcube_db_mysql.php b/program/include/rcube_db_mysql.php index 3eeee2ada..84a324701 100644 --- a/program/include/rcube_db_mysql.php +++ b/program/include/rcube_db_mysql.php @@ -31,6 +31,9 @@ */ class rcube_db_mysql extends rcube_db { + /** + * Driver initialization/configuration + */ protected function init() { // SQL identifiers quoting @@ -38,7 +41,6 @@ class rcube_db_mysql extends rcube_db $this->options['identifier_end'] = '`'; } - /** * Abstract SQL statement for value concatenation * @@ -55,9 +57,8 @@ class rcube_db_mysql extends rcube_db return 'CONCAT(' . join(', ', $args) . ')'; } - /** - * Returns PDO DSN string from DSN array (parse_dsn() result) + * Returns PDO DSN string from DSN array */ protected function dsn_string($dsn) { diff --git a/program/include/rcube_db_pgsql.php b/program/include/rcube_db_pgsql.php index 63bd92b40..641b884d6 100644 --- a/program/include/rcube_db_pgsql.php +++ b/program/include/rcube_db_pgsql.php @@ -31,7 +31,6 @@ */ class rcube_db_pgsql extends rcube_db { - /** * Get last inserted record ID * For Postgres databases, a table name is required @@ -55,7 +54,6 @@ class rcube_db_pgsql extends rcube_db return $id; } - /** * Return SQL statement to convert a field value into a unix timestamp * @@ -72,7 +70,6 @@ class rcube_db_pgsql extends rcube_db return "EXTRACT (EPOCH FROM $field)"; } - /** * Return SQL statement for case insensitive LIKE * diff --git a/program/include/rcube_db_sqlite.php b/program/include/rcube_db_sqlite.php index fe6b0dca9..1fcecd6da 100644 --- a/program/include/rcube_db_sqlite.php +++ b/program/include/rcube_db_sqlite.php @@ -31,11 +31,16 @@ */ class rcube_db_sqlite extends rcube_db { - + /** + * Database character set + */ protected function set_charset($charset) { } + /** + * Prepare connection + */ protected function conn_prepare($dsn) { // Create database file, required by PDO to exist on connection @@ -44,6 +49,9 @@ class rcube_db_sqlite extends rcube_db } } + /** + * Configure connection, create database if not exists + */ protected function conn_configure($dsn, $dbh) { // we emulate via callback some missing functions @@ -74,7 +82,6 @@ class rcube_db_sqlite extends rcube_db } } - /** * Callback for sqlite: unix_timestamp() */ @@ -94,7 +101,6 @@ class rcube_db_sqlite extends rcube_db return $ret; } - /** * Callback for sqlite: now() */ @@ -103,7 +109,6 @@ class rcube_db_sqlite extends rcube_db return date("Y-m-d H:i:s"); } - /** * Returns list of tables in database * @@ -126,7 +131,6 @@ class rcube_db_sqlite extends rcube_db return $this->tables; } - /** * Returns list of columns in database table * @@ -161,7 +165,9 @@ class rcube_db_sqlite extends rcube_db return $columns; } - + /** + * Build DSN string for PDO constructor + */ protected function dsn_string($dsn) { return $dsn['phptype'] . ':' . $dsn['database']; diff --git a/program/include/rcube_db_sqlsrv.php b/program/include/rcube_db_sqlsrv.php index 4ea7e7318..143e02038 100644 --- a/program/include/rcube_db_sqlsrv.php +++ b/program/include/rcube_db_sqlsrv.php @@ -31,17 +31,23 @@ */ class rcube_db_sqlsrv extends rcube_db { + /** + * Driver initialization + */ protected function init() { $this->options['identifier_start'] = '['; $this->options['identifier_end'] = ']'; } + /** + * Database character set setting + */ protected function set_charset($charset) { + // UTF-8 is default } - /** * Return SQL function for current time and date * @@ -52,7 +58,6 @@ class rcube_db_sqlsrv extends rcube_db return "getdate()"; } - /** * Return SQL statement to convert a field value into a unix timestamp * @@ -69,7 +74,6 @@ class rcube_db_sqlsrv extends rcube_db return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; } - /** * Abstract SQL statement for value concatenation * @@ -86,10 +90,14 @@ class rcube_db_sqlsrv extends rcube_db return '(' . join('+', $args) . ')'; } - /** * Adds TOP (LIMIT,OFFSET) clause to the query * + * @param string $query SQL query + * @param int $limit Number of rows + * @param int $offset Offset + * + * @return string SQL query */ protected function set_limit($query, $limit = 0, $offset = 0) { @@ -119,7 +127,7 @@ class rcube_db_sqlsrv extends rcube_db } /** - * Returns PDO DSN string from DSN array (parse_dsn() result) + * Returns PDO DSN string from DSN array */ protected function dsn_string($dsn) { |