From 0d94fd45f422fe0d0460f5db7a7761f56bc18236 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Tue, 19 Jun 2012 10:46:41 +0200 Subject: New database layer based on PHP PDO --- program/include/rcube_db_mssql.php | 135 +++++++++++++++++++++++++++++++++++++ 1 file changed, 135 insertions(+) create mode 100644 program/include/rcube_db_mssql.php (limited to 'program/include/rcube_db_mssql.php') diff --git a/program/include/rcube_db_mssql.php b/program/include/rcube_db_mssql.php new file mode 100644 index 000000000..1f7328c3e --- /dev/null +++ b/program/include/rcube_db_mssql.php @@ -0,0 +1,135 @@ + | + +-----------------------------------------------------------------------+ +*/ + + +/** + * Database independent query interface + * + * This is a wrapper for the PHP PDO + * + * @package Database + * @version 1.0 + */ +class rcube_db_mssql extends rcube_db +{ + protected function init() + { + $this->options['identifier_start'] = '['; + $this->options['identifier_end'] = ']'; + } + + protected function set_charset($charset) + { + } + + + /** + * Return SQL function for current time and date + * + * @return string SQL function to use in query + */ + public function now() + { + return "getdate()"; + } + + + /** + * Return SQL statement to convert a field value into a unix timestamp + * + * This method is deprecated and should not be used anymore due to limitations + * of timestamp functions in Mysql (year 2038 problem) + * + * @param string $field Field name + * + * @return string SQL statement to use in query + * @deprecated + */ + public function unixtimestamp($field) + { + return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; + } + + + /** + * Abstract SQL statement for value concatenation + * + * @return string SQL statement to be used in query + */ + public function concat(/* col1, col2, ... */) + { + $args = func_get_args(); + + if (is_array($args[0])) { + $args = $args[0]; + } + + return '(' . join('+', $args) . ')'; + } + + + /** + * Adds TOP (LIMIT,OFFSET) clause to the 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); + } + +// @TODO: proper OFFSET handling i _fetch_row() + + return $query; + } + + + /** + * Returns PDO DSN string from DSN array (parse_dsn() result) + */ + protected function dsn_string($dsn) + { + $params = array(); + $result = $dsn['phptype'] . ':'; + + if ($dsn['hostspec']) { + $host = $dsn['hostspec']; + if ($dsn['port']) { + $host .= ',' . $dsn['port']; + } + $params[] = 'host=' . $host; + } + + if ($dsn['database']) { + $params[] = 'dbname=' . $dsn['database']; + } + + if (!empty($params)) { + $result .= implode(';', $params); + } + + return $result; + } +} -- cgit v1.2.3 From 8c2375a07443231cad32bd4cbd1d9ffbd1aa5087 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 4 Jul 2012 13:11:09 +0200 Subject: More CS fixes and comments --- program/include/rcube_db.php | 5 +++++ program/include/rcube_db_mssql.php | 44 +++++++++++++++++++++++++++---------- program/include/rcube_db_mysql.php | 7 +++--- program/include/rcube_db_pgsql.php | 3 --- program/include/rcube_db_sqlite.php | 18 ++++++++++----- program/include/rcube_db_sqlsrv.php | 18 ++++++++++----- 6 files changed, 66 insertions(+), 29 deletions(-) (limited to 'program/include/rcube_db_mssql.php') 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) { -- cgit v1.2.3 From 3d231c88fa299787fe52e00773d03a4efa51590d Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Sun, 8 Jul 2012 10:58:11 +0200 Subject: CS fixes --- program/include/rcube_db.php | 186 +++++++++++++++++++----------------- program/include/rcube_db_mssql.php | 16 ++-- program/include/rcube_db_mysql.php | 10 +- program/include/rcube_db_pgsql.php | 18 ++-- program/include/rcube_db_sqlite.php | 8 +- program/include/rcube_db_sqlsrv.php | 16 ++-- 6 files changed, 131 insertions(+), 123 deletions(-) (limited to 'program/include/rcube_db_mssql.php') diff --git a/program/include/rcube_db.php b/program/include/rcube_db.php index b1cbd8505..f24e95913 100644 --- a/program/include/rcube_db.php +++ b/program/include/rcube_db.php @@ -1,6 +1,6 @@ a_query_results); - $this->last_res_id = $res_id; - $this->a_query_results[$res_id] = $res; + $this->last_res_id = sizeof($this->a_query_results); + $this->a_query_results[$this->last_res_id] = $res; - return $res_id; + return $this->last_res_id; } /** * Resolves a given handle ID and returns the according query handle * If no ID is specified, the last resource handle will be returned * - * @param number $res_id Handle ID + * @param int $res_id Handle ID * - * @return mixed Resource handle or false on failure + * @return mixed Resource handle or false on failure */ protected function _get_result($res_id = null) { @@ -844,7 +846,8 @@ class rcube_db if (($pos = strpos($dsn, '://')) !== false) { $str = substr($dsn, 0, $pos); $dsn = substr($dsn, $pos + 3); - } else { + } + else { $str = $dsn; $dsn = null; } @@ -854,7 +857,8 @@ class rcube_db if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) { $parsed['phptype'] = $arr[1]; $parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2]; - } else { + } + else { $parsed['phptype'] = $str; $parsed['dbsyntax'] = $str; } @@ -871,7 +875,8 @@ class rcube_db if (($pos = strpos($str, ':')) !== false) { $parsed['username'] = rawurldecode(substr($str, 0, $pos)); $parsed['password'] = rawurldecode(substr($str, $pos + 1)); - } else { + } + else { $parsed['username'] = rawurldecode($str); } } @@ -900,9 +905,11 @@ class rcube_db $pos = strrpos($proto_opts, '/'); $dsn = substr($proto_opts, $pos + 1); $proto_opts = substr($proto_opts, 0, $pos); - } elseif (strpos($dsn, '/') !== false) { + } + else if (strpos($dsn, '/') !== false) { list($proto_opts, $dsn) = explode('/', $dsn, 2); - } else { + } + else { $proto_opts = $dsn; $dsn = null; } @@ -934,7 +941,8 @@ class rcube_db $dsn = substr($dsn, $pos + 1); if (strpos($dsn, '&') !== false) { $opts = explode('&', $dsn); - } else { // database?param1=value1 + } + else { // database?param1=value1 $opts = array($dsn); } foreach ($opts as $opt) { @@ -953,7 +961,7 @@ class rcube_db /** * Returns PDO DSN string from DSN array * - * @param array $dsn DSN parameters + * @param array $dsn DSN parameters * * @return string DSN string */ @@ -984,7 +992,7 @@ class rcube_db /** * Returns driver-specific connection options * - * @param array $dsn DSN parameters + * @param array $dsn DSN parameters * * @return array Connection options */ diff --git a/program/include/rcube_db_mssql.php b/program/include/rcube_db_mssql.php index 3a64c3b1d..5cbcfab36 100644 --- a/program/include/rcube_db_mssql.php +++ b/program/include/rcube_db_mssql.php @@ -1,6 +1,6 @@