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_sqlite.php | 169 ++++++++++++++++++++++++++++++++++++ 1 file changed, 169 insertions(+) create mode 100644 program/include/rcube_db_sqlite.php (limited to 'program/include/rcube_db_sqlite.php') diff --git a/program/include/rcube_db_sqlite.php b/program/include/rcube_db_sqlite.php new file mode 100644 index 000000000..fe6b0dca9 --- /dev/null +++ b/program/include/rcube_db_sqlite.php @@ -0,0 +1,169 @@ + | + +-----------------------------------------------------------------------+ +*/ + + +/** + * Database independent query interface + * + * This is a wrapper for the PHP PDO + * + * @package Database + * @version 1.0 + */ +class rcube_db_sqlite extends rcube_db +{ + + protected function set_charset($charset) + { + } + + protected function conn_prepare($dsn) + { + // Create database file, required by PDO to exist on connection + if (!empty($dsn['database']) && !file_exists($dsn['database'])) { + touch($dsn['database']); + } + } + + protected function conn_configure($dsn, $dbh) + { + // we emulate via callback some missing functions + $dbh->sqliteCreateFunction('unix_timestamp', array('rcube_db_sqlite', 'sqlite_unix_timestamp'), 1); + $dbh->sqliteCreateFunction('now', array('rcube_db_sqlite', 'sqlite_now'), 0); + + // Initialize database structure in file is empty + if (!empty($dsn['database']) && !filesize($dsn['database'])) { + $data = file_get_contents(INSTALL_PATH . 'SQL/sqlite.initial.sql'); + + if (strlen($data)) { + if ($this->options['debug_mode']) { + $this::debug('INITIALIZE DATABASE'); + } + + $q = $dbh->exec($data); + + if ($q === false) { + $error = $this->dbh->errorInfo(); + $this->db_error = true; + $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]); + + rcube::raise_error(array('code' => 500, 'type' => 'db', + 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_error_msg), true, false); + } + } + } + } + + + /** + * Callback for sqlite: unix_timestamp() + */ + public static function sqlite_unix_timestamp($timestamp = '') + { + $timestamp = trim($timestamp); + if (!$timestamp) { + $ret = time(); + } + else if (!preg_match('/^[0-9]+$/s', $timestamp)) { + $ret = strtotime($timestamp); + } + else { + $ret = $timestamp; + } + + return $ret; + } + + + /** + * Callback for sqlite: now() + */ + public static function sqlite_now() + { + return date("Y-m-d H:i:s"); + } + + + /** + * Returns list of tables in database + * + * @return array List of all tables of the current database + */ + public function list_tables() + { + if ($this->tables === null) { + $q = $this->query('SELECT name FROM sqlite_master' + .' WHERE type = \'table\' ORDER BY name'); + + if ($res = $this->_get_result($q)) { + $this->tables = $res->fetchAll(PDO::FETCH_COLUMN, 0); + } + else { + $this->tables = array(); + } + } + + return $this->tables; + } + + + /** + * Returns list of columns in database table + * + * @param string Table name + * + * @return array List of table cols + */ + public function list_cols($table) + { + $q = $this->query('SELECT sql FROM sqlite_master WHERE type = ? AND name = ?', + array('table', $table)); + + $columns = array(); + + if ($sql = $this->fetch_array($q)) { + $sql = $sql[0]; + $start_pos = strpos($sql, '('); + $end_pos = strrpos($sql, ')'); + $sql = substr($sql, $start_pos+1, $end_pos-$start_pos-1); + $lines = explode(',', $sql); + + foreach ($lines as $line) { + $line = explode(' ', trim($line)); + + if ($line[0] && strpos($line[0], '--') !== 0) { + $column = $line[0]; + $columns[] = trim($column, '"'); + } + } + } + + return $columns; + } + + + protected function dsn_string($dsn) + { + return $dsn['phptype'] . ':' . $dsn['database']; + } +} -- 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_sqlite.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_sqlite.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 @@ Date: Mon, 9 Jul 2012 19:29:18 +0200 Subject: Fix debugging in sqlite driver --- program/include/rcube_db.php | 16 +++++++++++++--- program/include/rcube_db_sqlite.php | 4 +--- 2 files changed, 14 insertions(+), 6 deletions(-) (limited to 'program/include/rcube_db_sqlite.php') diff --git a/program/include/rcube_db.php b/program/include/rcube_db.php index 31385d860..d0d213cd0 100644 --- a/program/include/rcube_db.php +++ b/program/include/rcube_db.php @@ -251,6 +251,18 @@ class rcube_db $this->options['debug_mode'] = $dbg; } + /** + * Writes debug information/query to 'sql' log file + * + * @param string $query SQL query + */ + protected function debug($query) + { + if ($this->options['debug_mode']) { + rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';'); + } + } + /** * Getter for error state * @@ -378,9 +390,7 @@ class rcube_db $query = rtrim($query, ';'); - if ($this->options['debug_mode']) { - rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';'); - } + $this->debug($query); $query = $this->dbh->query($query); diff --git a/program/include/rcube_db_sqlite.php b/program/include/rcube_db_sqlite.php index 0b41ef551..57f8ddc2a 100644 --- a/program/include/rcube_db_sqlite.php +++ b/program/include/rcube_db_sqlite.php @@ -63,9 +63,7 @@ class rcube_db_sqlite extends rcube_db $data = file_get_contents(INSTALL_PATH . 'SQL/sqlite.initial.sql'); if (strlen($data)) { - if ($this->options['debug_mode']) { - $this::debug('INITIALIZE DATABASE'); - } + $this->debug('INITIALIZE DATABASE'); $q = $dbh->exec($data); -- cgit v1.2.3 From 146106a827b84fa3342fb7afef5919b94c10a797 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Mon, 9 Jul 2012 19:57:50 +0200 Subject: Support sqlite file mode setting --- program/include/rcube_db_sqlite.php | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) (limited to 'program/include/rcube_db_sqlite.php') diff --git a/program/include/rcube_db_sqlite.php b/program/include/rcube_db_sqlite.php index 57f8ddc2a..a9774cd51 100644 --- a/program/include/rcube_db_sqlite.php +++ b/program/include/rcube_db_sqlite.php @@ -45,7 +45,12 @@ class rcube_db_sqlite extends rcube_db { // Create database file, required by PDO to exist on connection if (!empty($dsn['database']) && !file_exists($dsn['database'])) { - touch($dsn['database']); + $created = touch($dsn['database']); + + // File mode setting, for compat. with MDB2 + if (!empty($dsn['mode']) && $created) { + chmod($dsn['database'], octdec($dsn['mode'])); + } } } @@ -68,7 +73,7 @@ class rcube_db_sqlite extends rcube_db $q = $dbh->exec($data); if ($q === false) { - $error = $this->dbh->errorInfo(); + $error = $dbh->errorInfo(); $this->db_error = true; $this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]); -- cgit v1.2.3