From b39b0369aa0ac6853bde0504259166f570beb983 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sun, 3 Nov 2013 21:58:05 -0600 Subject: [feature/sqlite3] Add support for SQLite 3 Minimum version requirement is 3.6.15 as that's what ships with PHP 5.3.0 when support for SQLite 3 was added. PHPBB3-9728 --- phpBB/phpbb/db/driver/sqlite3.php | 350 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 350 insertions(+) create mode 100644 phpBB/phpbb/db/driver/sqlite3.php (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php new file mode 100644 index 0000000000..fcb21b39c9 --- /dev/null +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -0,0 +1,350 @@ +persistency = false; + $this->user = $sqluser; + $this->server = $sqlserver . (($port) ? ':' . $port : ''); + $this->dbname = $database; + + if (!class_exists('SQLite3', false)) + { + $this->connect_error = 'SQLite3 not found, is the extension installed?'; + return $this->sql_error(''); + } + + try + { + $this->dbo = new \SQLite3($this->server, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE); + $this->db_connect_id = true; + } + catch (Exception $e) + { + return array('message' => $e->getMessage()); + } + + return true; + } + + /** + * {@inheritDoc} + */ + function sql_server_info($raw = false, $use_cache = true) + { + global $cache; + + if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('sqlite_version')) === false) + { + $version = \SQLite3::version(); + + $this->sql_server_version = $version['versionString']; + + if (!empty($cache) && $use_cache) + { + $cache->put('sqlite_version', $this->sql_server_version); + } + } + + return ($raw) ? $this->sql_server_version : 'SQLite ' . $this->sql_server_version; + } + + /** + * SQL Transaction + * @access private + */ + function _sql_transaction($status = 'begin') + { + switch ($status) + { + case 'begin': + return $this->dbo->exec('BEGIN IMMEDIATE'); + break; + + case 'commit': + return $this->dbo->exec('COMMIT'); + break; + + case 'rollback': + return $this->dbo->exec('ROLLBACK'); + break; + } + + return true; + } + + /** + * {@inheritDoc} + */ + function sql_query($query = '', $cache_ttl = 0) + { + if ($query != '') + { + global $cache; + + // EXPLAIN only in extra debug mode + if (defined('DEBUG')) + { + $this->sql_report('start', $query); + } + + $this->last_query_text = $query; + $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false; + $this->sql_add_num_queries($this->query_result); + + if ($this->query_result === false) + { + if (($this->query_result = @$this->dbo->query($query)) === false) + { + $this->sql_error($query); + } + + if (defined('DEBUG')) + { + $this->sql_report('stop', $query); + } + + if ($cache && $cache_ttl) + { + $this->query_result = $cache->sql_save($this, $query, $this->query_result, $cache_ttl); + } + } + else if (defined('DEBUG')) + { + $this->sql_report('fromcache', $query); + } + } + else + { + return false; + } + + return $this->query_result; + } + + /** + * Build LIMIT query + */ + function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) + { + $this->query_result = false; + + // if $total is set to 0 we do not want to limit the number of rows + if ($total == 0) + { + $total = -1; + } + + $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total); + + return $this->sql_query($query, $cache_ttl); + } + + /** + * {@inheritDoc} + */ + function sql_affectedrows() + { + return ($this->db_connect_id) ? $this->dbo->changes() : false; + } + + /** + * {@inheritDoc} + */ + function sql_fetchrow($query_id = false) + { + global $cache; + + if ($query_id === false) + { + $query_id = $this->query_result; + } + + if ($cache && !is_object($query_id) && $cache->sql_exists($query_id)) + { + return $cache->sql_fetchrow($query_id); + } + + return is_object($query_id) ? $query_id->fetchArray(SQLITE3_ASSOC) : false; + } + + /** + * {@inheritDoc} + */ + function sql_nextid() + { + return ($this->db_connect_id) ? $this->dbo->lastInsertRowID() : false; + } + + /** + * {@inheritDoc} + */ + function sql_freeresult($query_id = false) + { + global $cache; + + if ($query_id === false) + { + $query_id = $this->query_result; + } + + if ($cache && !is_object($query_id) && $cache->sql_exists($query_id)) + { + return $cache->sql_freeresult($query_id); + } + + if ($query_id) + { + return @$query_id->finalize(); + } + } + + /** + * {@inheritDoc} + */ + function sql_escape($msg) + { + return \SQLite3::escapeString($msg); + } + + /** + * {@inheritDoc} + * + * For SQLite an underscore is a not-known character... + */ + function sql_like_expression($expression) + { + // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it! + // We only catch * and ? here, not the character map possible on file globbing. + $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); + + $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression); + $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression); + + return 'GLOB \'' . $this->sql_escape($expression) . '\''; + } + + /** + * return sql error array + * @access private + */ + function _sql_error() + { + if (class_exists('SQLite3', false)) + { + $error = array( + 'message' => $this->dbo->lastErrorMsg(), + 'code' => $this->dbo->lastErrorCode(), + ); + } + else + { + $error = array( + 'message' => $this->connect_error, + 'code' => '', + ); + } + + return $error; + } + + /** + * Build db-specific query data + * @access private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** + * Close sql connection + * @access private + */ + function _sql_close() + { + return $this->dbo->close(); + } + + /** + * Build db-specific report + * @access private + */ + function _sql_report($mode, $query = '') + { + switch ($mode) + { + case 'start': + + $explain_query = $query; + if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) + { + $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; + } + else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) + { + $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; + } + + if (preg_match('/^SELECT/', $explain_query)) + { + $html_table = false; + + if ($result = $this->dbo->query("EXPLAIN QUERY PLAN $explain_query")) + { + while ($row = $result->fetchArray(SQLITE3_ASSOC)) + { + $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); + } + } + + if ($html_table) + { + $this->html_hold .= ''; + } + } + + break; + + case 'fromcache': + $endtime = explode(' ', microtime()); + $endtime = $endtime[0] + $endtime[1]; + + $result = $this->dbo->query($query); + while ($void = $result->fetchArray(SQLITE3_ASSOC)) + { + // Take the time spent on parsing rows into account + } + + $splittime = explode(' ', microtime()); + $splittime = $splittime[0] + $splittime[1]; + + $this->sql_report('record_fromcache', $query, $endtime, $splittime); + + break; + } + } +} -- cgit v1.2.1 From 8c2b80e3d25c716ae9c81fe833cc4bb62eb03279 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Fri, 25 Apr 2014 18:24:39 -0500 Subject: [feature/sqlite3] Update docblocks and function visibility Added missing docblock annotations and added visibility identifiers. PHPBB3-9728 --- phpBB/phpbb/db/driver/sqlite3.php | 71 ++++++++++++++++++++++++++------------- 1 file changed, 48 insertions(+), 23 deletions(-) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index fcb21b39c9..971b3e55d3 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -16,15 +16,20 @@ namespace phpbb\db\driver; */ class sqlite3 extends \phpbb\db\driver\driver { - var $connect_error = ''; + /** + * @var string Stores errors during connection setup in case the driver is not available + */ + protected $connect_error = ''; - /** @var \SQLite3 */ - var $dbo = null; + /** + * @var \SQLite3 The SQLite3 database object to operate against + */ + protected $dbo = null; /** * {@inheritDoc} */ - function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false) + public function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false) { $this->persistency = false; $this->user = $sqluser; @@ -53,7 +58,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_server_info($raw = false, $use_cache = true) + public function sql_server_info($raw = false, $use_cache = true) { global $cache; @@ -74,9 +79,12 @@ class sqlite3 extends \phpbb\db\driver\driver /** * SQL Transaction - * @access private + * + * @param string $status Should be one of the following strings: + * begin, commit, rollback + * @return bool Success/failure of the transaction query */ - function _sql_transaction($status = 'begin') + protected function _sql_transaction($status = 'begin') { switch ($status) { @@ -99,7 +107,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_query($query = '', $cache_ttl = 0) + public function sql_query($query = '', $cache_ttl = 0) { if ($query != '') { @@ -147,8 +155,15 @@ class sqlite3 extends \phpbb\db\driver\driver /** * Build LIMIT query + * + * @param string $query The SQL query to execute + * @param int $total The number of rows to select + * @param int $offset + * @param int $cache_ttl Either 0 to avoid caching or + * the time in seconds which the result shall be kept in cache + * @return mixed Buffered, seekable result handle, false on error */ - function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) + protected function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { $this->query_result = false; @@ -166,7 +181,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_affectedrows() + public function sql_affectedrows() { return ($this->db_connect_id) ? $this->dbo->changes() : false; } @@ -174,7 +189,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_fetchrow($query_id = false) + public function sql_fetchrow($query_id = false) { global $cache; @@ -194,7 +209,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_nextid() + public function sql_nextid() { return ($this->db_connect_id) ? $this->dbo->lastInsertRowID() : false; } @@ -202,7 +217,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_freeresult($query_id = false) + public function sql_freeresult($query_id = false) { global $cache; @@ -225,7 +240,7 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} */ - function sql_escape($msg) + public function sql_escape($msg) { return \SQLite3::escapeString($msg); } @@ -235,7 +250,7 @@ class sqlite3 extends \phpbb\db\driver\driver * * For SQLite an underscore is a not-known character... */ - function sql_like_expression($expression) + public function sql_like_expression($expression) { // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it! // We only catch * and ? here, not the character map possible on file globbing. @@ -249,9 +264,10 @@ class sqlite3 extends \phpbb\db\driver\driver /** * return sql error array - * @access private + * + * @return array */ - function _sql_error() + protected function _sql_error() { if (class_exists('SQLite3', false)) { @@ -273,27 +289,36 @@ class sqlite3 extends \phpbb\db\driver\driver /** * Build db-specific query data - * @access private + * + * @param string $stage Available stages: FROM, WHERE + * @param mixed $data A string containing the CROSS JOIN query or an array of WHERE clauses + * + * @return string The db-specific query fragment */ - function _sql_custom_build($stage, $data) + protected function _sql_custom_build($stage, $data) { return $data; } /** * Close sql connection - * @access private + * + * @return bool False if failure */ - function _sql_close() + protected function _sql_close() { return $this->dbo->close(); } /** * Build db-specific report - * @access private + * + * @param string $mode Available modes: display, start, stop, + * add_select_row, fromcache, record_fromcache + * @param string $query The Query that should be explained + * @return mixed Either a full HTML page, boolean or null */ - function _sql_report($mode, $query = '') + protected function _sql_report($mode, $query = '') { switch ($mode) { -- cgit v1.2.1 From a759704b39fc1c1353f865a633759b1369589b67 Mon Sep 17 00:00:00 2001 From: Yuriy Rusko Date: Tue, 27 May 2014 20:18:06 +0200 Subject: [ticket/12594] Remove @package tags and update file headers PHPBB3-12594 --- phpBB/phpbb/db/driver/sqlite3.php | 11 +++++++---- 1 file changed, 7 insertions(+), 4 deletions(-) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 971b3e55d3..2c6bf99497 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -1,9 +1,13 @@ +* @license GNU General Public License, version 2 (GPL-2.0) +* +* For full copyright and license information, please see +* the docs/CREDITS.txt file. * */ @@ -12,7 +16,6 @@ namespace phpbb\db\driver; /** * SQLite3 Database Abstraction Layer * Minimum Requirement: 3.6.15+ -* @package dbal */ class sqlite3 extends \phpbb\db\driver\driver { -- cgit v1.2.1 From f850d5fa90584bb73ef1a77fd21f825e1d0794ed Mon Sep 17 00:00:00 2001 From: rxu Date: Sat, 14 Jun 2014 20:53:34 +0800 Subject: [ticket/12704] Improve the load time information in the footer when enabled PR #2570 has added new constant to display load time information without debug mode is being on (https://tracker.phpbb.com/browse/PHPBB3-12687). This patch expands the total load time info with SQL/PHP load times, while hiding the additional info with element. PHPBB3-12704 --- phpBB/phpbb/db/driver/sqlite3.php | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 2c6bf99497..6511c755a0 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -121,6 +121,10 @@ class sqlite3 extends \phpbb\db\driver\driver { $this->sql_report('start', $query); } + else if (defined('PHPBB_DISPLAY_LOAD_TIME')) + { + $this->curtime = microtime(true); + } $this->last_query_text = $query; $this->query_result = ($cache && $cache_ttl) ? $cache->sql_load($query) : false; @@ -137,6 +141,10 @@ class sqlite3 extends \phpbb\db\driver\driver { $this->sql_report('stop', $query); } + else if (defined('PHPBB_DISPLAY_LOAD_TIME')) + { + $this->sql_time += microtime(true) - $this->curtime; + } if ($cache && $cache_ttl) { -- cgit v1.2.1 From 3ca708edadce1c82903559f384fd15638d0ab2de Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Thu, 3 Jul 2014 13:05:54 -0500 Subject: [ticket/12802] Properly handle connection failures in SQLite3 PHPBB3-12802 --- phpBB/phpbb/db/driver/sqlite3.php | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 2c6bf99497..36d8a83f4b 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -50,9 +50,10 @@ class sqlite3 extends \phpbb\db\driver\driver $this->dbo = new \SQLite3($this->server, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE); $this->db_connect_id = true; } - catch (Exception $e) + catch (\Exception $e) { - return array('message' => $e->getMessage()); + $this->connect_error = $e->getMessage(); + return array('message' => $this->connect_error); } return true; @@ -272,7 +273,7 @@ class sqlite3 extends \phpbb\db\driver\driver */ protected function _sql_error() { - if (class_exists('SQLite3', false)) + if (class_exists('SQLite3', false) && isset($this->dbo)) { $error = array( 'message' => $this->dbo->lastErrorMsg(), -- cgit v1.2.1 From 01943adbf72e137b9dfbda3ac85120c21a30fab8 Mon Sep 17 00:00:00 2001 From: Geolim4 Date: Fri, 8 Aug 2014 18:32:17 +0200 Subject: [ticket/12671] Possibility to use NOT LIKE expression PHPBB3-12671 --- phpBB/phpbb/db/driver/sqlite3.php | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 0922229e0a..f79245afb1 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -274,6 +274,23 @@ class sqlite3 extends \phpbb\db\driver\driver return 'GLOB \'' . $this->sql_escape($expression) . '\''; } + /** + * {@inheritDoc} + * + * For SQLite an underscore is a not-known character... + */ + public function sql_not_like_expression($expression) + { + // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it! + // We only catch * and ? here, not the character map possible on file globbing. + $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); + + $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression); + $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression); + + return 'GLOB \'' . $this->sql_escape($expression) . '\''; + } + /** * return sql error array * -- cgit v1.2.1 From 635cde218c786f76397c3354e59f96a8a151c132 Mon Sep 17 00:00:00 2001 From: Geolim4 Date: Sat, 9 Aug 2014 15:04:39 +0200 Subject: [ticket/12671] Possibility to use NOT LIKE expression PHPBB3-12671 --- phpBB/phpbb/db/driver/sqlite3.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index f79245afb1..2bf4ed51aa 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -281,14 +281,14 @@ class sqlite3 extends \phpbb\db\driver\driver */ public function sql_not_like_expression($expression) { - // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it! + // Unlike NOT LIKE,NOT GLOB is case sensitive (unfortunatly). SQLite users need to live with it! // We only catch * and ? here, not the character map possible on file globbing. $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); $expression = str_replace(array('?', '*'), array("\?", "\*"), $expression); $expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression); - return 'GLOB \'' . $this->sql_escape($expression) . '\''; + return 'NOT GLOB \'' . $this->sql_escape($expression) . '\''; } /** -- cgit v1.2.1 From db0815f680d4ed9da845facb6e8e3975ac14621e Mon Sep 17 00:00:00 2001 From: Geolim4 Date: Sat, 9 Aug 2014 19:11:21 +0200 Subject: [ticket/12671] Possibility to use NOT LIKE expression PHPBB3-12671 --- phpBB/phpbb/db/driver/sqlite3.php | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 2bf4ed51aa..4e3e0d3329 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -260,11 +260,11 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} * - * For SQLite an underscore is a not-known character... + * For SQLite an underscore is an unknown character. */ public function sql_like_expression($expression) { - // Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it! + // Unlike LIKE, GLOB is unfortunately case sensitive. // We only catch * and ? here, not the character map possible on file globbing. $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); @@ -277,11 +277,11 @@ class sqlite3 extends \phpbb\db\driver\driver /** * {@inheritDoc} * - * For SQLite an underscore is a not-known character... + * For SQLite an underscore is an unknown character. */ public function sql_not_like_expression($expression) { - // Unlike NOT LIKE,NOT GLOB is case sensitive (unfortunatly). SQLite users need to live with it! + // Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive // We only catch * and ? here, not the character map possible on file globbing. $expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression); -- cgit v1.2.1 From ea594d00f750843997fcbbd829a64c8cc983fca2 Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Mon, 7 Sep 2015 12:23:49 +0200 Subject: [ticket/13423] Set busyTimeout on connect to prevent db locking PHPBB3-13423 --- phpBB/phpbb/db/driver/sqlite3.php | 1 + 1 file changed, 1 insertion(+) (limited to 'phpBB/phpbb/db/driver/sqlite3.php') diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php index 4e3e0d3329..cc3352af34 100644 --- a/phpBB/phpbb/db/driver/sqlite3.php +++ b/phpBB/phpbb/db/driver/sqlite3.php @@ -48,6 +48,7 @@ class sqlite3 extends \phpbb\db\driver\driver try { $this->dbo = new \SQLite3($this->server, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE); + $this->dbo->busyTimeout(60000); $this->db_connect_id = true; } catch (\Exception $e) -- cgit v1.2.1