[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
author: Patrick Webster <noxwizard@phpbb.com> 2013-11-03 21:58:05 -0600
committer: Joas Schilling <nickvergessen@gmx.de> 2014-05-02 15:45:20 +0200
commit: b39b0369aa0ac6853bde0504259166f570beb983 (patch)
tree: 5bc685f9ba2fc59a54cc2fb3d61440052f44dbb8 /phpBB/includes
parent: d52c2d1b5caf1382d178e55eff311ceddf4b392f (diff)
download: forums-b39b0369aa0ac6853bde0504259166f570beb983.tar
forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.gz
forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.bz2
forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.xz
forums-b39b0369aa0ac6853bde0504259166f570beb983.zip
9 files changed, 144 insertions, 4 deletions
diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php
index d28ee5b067..fd97e6ee08 100644
--- a/phpBB/includes/acp/acp_database.php
+++ b/phpBB/includes/acp/acp_database.php
@@ -101,6 +101,10 @@ class acp_database
 								$extractor = new sqlite_extractor($format, $filename, $time, $download, $store);
 							break;
 
+							case 'sqlite3':
+								$extractor = new sqlite3_extractor($format, $filename, $time, $download, $store);
+							break;
+
 							case 'postgres':
 								$extractor = new postgres_extractor($format, $filename, $time, $download, $store);
 							break;
@@ -135,6 +139,7 @@ class acp_database
 								switch ($db->sql_layer)
 								{
 									case 'sqlite':
+									case 'sqlite3':
 									case 'firebird':
 										$extractor->flush('DELETE FROM ' . $table_name . ";\n");
 									break;
@@ -325,6 +330,7 @@ class acp_database
 								case 'mysql4':
 								case 'mysqli':
 								case 'sqlite':
+								case 'sqlite3':
 									while (($sql = $fgetd($fp, ";\n", $read, $seek, $eof)) !== false)
 									{
 										$db->sql_query($sql);
@@ -1052,6 +1058,112 @@ class sqlite_extractor extends base_extractor
 /**
 * @package acp
 */
+class sqlite3_extractor extends base_extractor
+{
+	function write_start($prefix)
+	{
+		$sql_data = "--\n";
+		$sql_data .= "-- phpBB Backup Script\n";
+		$sql_data .= "-- Dump of tables for $prefix\n";
+		$sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n";
+		$sql_data .= "--\n";
+		$sql_data .= "BEGIN TRANSACTION;\n";
+		$this->flush($sql_data);
+	}
+
+	function write_table($table_name)
+	{
+		global $db;
+		$sql_data = '-- Table: ' . $table_name . "\n";
+		$sql_data .= "DROP TABLE $table_name;\n";
+
+		$sql = "SELECT sql
+			FROM sqlite_master
+			WHERE type = 'table'
+				AND name = '" . $db->sql_escape($table_name) . "'
+			ORDER BY type DESC, name;";
+		$result = $db->sql_query($sql);
+		$row = $db->sql_fetchrow($result);
+		$db->sql_freeresult($result);
+
+		// Create Table
+		$sql_data .= $row['sql'] . ";\n";
+
+		$result = $db->sql_query("PRAGMA index_list('" . $db->sql_escape($table_name) . "');");
+
+		while ($row = $db->sql_fetchrow($result))
+		{
+			if (strpos($row['name'], 'autoindex') !== false)
+			{
+				continue;
+			}
+
+			$result2 = $db->sql_query("PRAGMA index_info('" . $db->sql_escape($row['name']) . "');");
+
+			$fields = array();
+			while ($row2 = $db->sql_fetchrow($result2))
+			{
+				$fields[] = $row2['name'];
+			}
+			$db->sql_freeresult($result2);
+
+			$sql_data .= 'CREATE ' . ($row['unique'] ? 'UNIQUE ' : '') . 'INDEX ' . $row['name'] . ' ON ' . $table_name . ' (' . implode(', ', $fields) . ");\n";
+		}
+		$db->sql_freeresult($result);
+
+		$this->flush($sql_data . "\n");
+	}
+
+	function write_data($table_name)
+	{
+		global $db;
+
+		$result = $db->sql_query("PRAGMA table_info('" . $db->sql_escape($table_name) . "');");
+
+		$col_types = array();
+		while ($row = $db->sql_fetchrow($result))
+		{
+			$col_types[$row['name']] = $row['type'];
+		}
+		$db->sql_freeresult($result);
+
+		$sql_insert = 'INSERT INTO ' . $table_name . ' (' . implode(', ', array_keys($col_types)) . ') VALUES (';
+
+		$sql = "SELECT *
+			FROM $table_name";
+		$result = $db->sql_query($sql);
+
+		while ($row = $db->sql_fetchrow($result))
+		{
+			foreach ($row as $column_name => $column_data)
+			{
+				if (is_null($column_data))
+				{
+					$row[$column_name] = 'NULL';
+				}
+				else if ($column_data === '')
+				{
+					$row[$column_name] = "''";
+				}
+				else if (stripos($col_types[$column_name], 'text') !== false || stripos($col_types[$column_name], 'char') !== false || stripos($col_types[$column_name], 'blob') !== false)
+				{
+					$row[$column_name] = sanitize_data_generic(str_replace("'", "''", $column_data));
+				}
+			}
+			$this->flush($sql_insert . implode(', ', $row) . ");\n");
+		}
+	}
+
+	function write_end()
+	{
+		$this->flush("COMMIT;\n");
+		parent::write_end();
+	}
+}
+
+/**
+* @package acp
+*/
 class postgres_extractor extends base_extractor
 {
 	function write_start($prefix)
diff --git a/phpBB/includes/acp/acp_icons.php b/phpBB/includes/acp/acp_icons.php
index 9c7acf506c..e4221a86dc 100644
--- a/phpBB/includes/acp/acp_icons.php
+++ b/phpBB/includes/acp/acp_icons.php
@@ -538,6 +538,7 @@ class acp_icons
 						switch ($db->sql_layer)
 						{
 							case 'sqlite':
+							case 'sqlite3':
 							case 'firebird':
 								$db->sql_query('DELETE FROM ' . $table);
 							break;
diff --git a/phpBB/includes/acp/acp_main.php b/phpBB/includes/acp/acp_main.php
index 4512905539..9c1613e24a 100644
--- a/phpBB/includes/acp/acp_main.php
+++ b/phpBB/includes/acp/acp_main.php
@@ -271,6 +271,7 @@ class acp_main
 						switch ($db->sql_layer)
 						{
 							case 'sqlite':
+							case 'sqlite3':
 							case 'firebird':
 								$db->sql_query('DELETE FROM ' . TOPICS_POSTED_TABLE);
 							break;
@@ -376,6 +377,7 @@ class acp_main
 							switch ($db->sql_layer)
 							{
 								case 'sqlite':
+								case 'sqlite3':
 								case 'firebird':
 									$db->sql_query("DELETE FROM $table");
 								break;
diff --git a/phpBB/includes/acp/acp_profile.php b/phpBB/includes/acp/acp_profile.php
index b42b852fba..8940410d72 100644
--- a/phpBB/includes/acp/acp_profile.php
+++ b/phpBB/includes/acp/acp_profile.php
@@ -114,6 +114,7 @@ class acp_profile
 					switch ($db->sql_layer)
 					{
 						case 'sqlite':
+						case 'sqlite3':
 							$sql = "SELECT sql
 								FROM sqlite_master
 								WHERE type = 'table'
@@ -1204,7 +1205,8 @@ class acp_profile
 			break;
 
 			case 'sqlite':
-				if (version_compare(sqlite_libversion(), '3.0') == -1)
+			case 'sqlite3':
+				if (version_compare($db->sql_server_info(true), '3.0') == -1)
 				{
 					$sql = "SELECT sql
 						FROM sqlite_master
diff --git a/phpBB/includes/acp/acp_reasons.php b/phpBB/includes/acp/acp_reasons.php
index 569bb73ab0..3f61a76d3a 100644
--- a/phpBB/includes/acp/acp_reasons.php
+++ b/phpBB/includes/acp/acp_reasons.php
@@ -253,6 +253,7 @@ class acp_reasons
 						case 'oracle':
 						case 'firebird':
 						case 'sqlite':
+						case 'sqlite3':
 							// Change the reports using this reason to 'other'
 							$sql = 'UPDATE ' . REPORTS_TABLE . '
 								SET reason_id = ' . $other_reason_id . ", report_text = '" . $db->sql_escape($reason_row['reason_description']) . "\n\n' || report_text
diff --git a/phpBB/includes/functions.php b/phpBB/includes/functions.php
index 92fe090823..11cc36c294 100644
--- a/phpBB/includes/functions.php
+++ b/phpBB/includes/functions.php
@@ -4036,7 +4036,7 @@ function obtain_guest_count($item_id = 0, $item = 'forum')
 
 	// Get number of online guests
 
-	if ($db->sql_layer === 'sqlite')
+	if ($db->sql_layer === 'sqlite' || $db->sql_layer === 'sqlite3')
 	{
 		$sql = 'SELECT COUNT(session_ip) as num_guests
 			FROM (
diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php
index 50b3b61eaa..db430c6940 100644
--- a/phpBB/includes/functions_admin.php
+++ b/phpBB/includes/functions_admin.php
@@ -2440,6 +2440,7 @@ function phpbb_cache_moderators($db, $cache, $auth)
 	switch ($db->sql_layer)
 	{
 		case 'sqlite':
+		case 'sqlite3':
 		case 'firebird':
 			$db->sql_query('DELETE FROM ' . MODERATOR_CACHE_TABLE);
 		break;
@@ -2907,6 +2908,7 @@ function get_database_size()
 		break;
 
 		case 'sqlite':
+		case 'sqlite3':
 			global $dbhost;
 
 			if (file_exists($dbhost))
diff --git a/phpBB/includes/functions_convert.php b/phpBB/includes/functions_convert.php
index 1646c79161..dbc66446b3 100644
--- a/phpBB/includes/functions_convert.php
+++ b/phpBB/includes/functions_convert.php
@@ -1650,6 +1650,7 @@ function mass_auth($ug_type, $forum_id, $ug_id, $acl_list, $setting = ACL_NO)
 
 					case 'mssql':
 					case 'sqlite':
+					case 'sqlite3':
 					case 'mssqlnative':
 						$sql = implode(' UNION ALL ', preg_replace('#^(.*?)$#', 'SELECT \1', $sql_subary));
 					break;
@@ -2037,6 +2038,7 @@ function update_topics_posted()
 	switch ($db->sql_layer)
 	{
 		case 'sqlite':
+		case 'sqlite3':
 		case 'firebird':
 			$db->sql_query('DELETE FROM ' . TOPICS_POSTED_TABLE);
 		break;
diff --git a/phpBB/includes/functions_install.php b/phpBB/includes/functions_install.php
index 4f8ec99d88..f484461b77 100644
--- a/phpBB/includes/functions_install.php
+++ b/phpBB/includes/functions_install.php
@@ -106,6 +106,15 @@ function get_available_dbms($dbms = false, $return_unavailable = false, $only_20
 			'AVAILABLE'		=> true,
 			'2.0.x'			=> false,
 		),
+		'sqlite3'		=> array(
+			'LABEL'			=> 'SQLite3',
+			'SCHEMA'		=> 'sqlite',
+			'MODULE'		=> 'sqlite3',
+			'DELIM'			=> ';',
+			'DRIVER'		=> 'phpbb\db\driver\sqlite3',
+			'AVAILABLE'		=> true,
+			'2.0.x'			=> false,
+		),
 	);
 
 	if ($dbms)
@@ -206,14 +215,14 @@ function connect_check_db($error_connect, &$error, $dbms_details, $table_prefix,
 	$db->sql_return_on_error(true);
 
 	// Check that we actually have a database name before going any further.....
-	if ($dbms_details['DRIVER'] != 'phpbb\db\driver\sqlite' && $dbms_details['DRIVER'] != 'phpbb\db\driver\oracle' && $dbname === '')
+	if ($dbms_details['DRIVER'] != 'phpbb\db\driver\sqlite' && $dbms_details['DRIVER'] != 'phpbb\db\driver\sqlite3' && $dbms_details['DRIVER'] != 'phpbb\db\driver\oracle' && $dbname === '')
 	{
 		$error[] = $lang['INST_ERR_DB_NO_NAME'];
 		return false;
 	}
 
 	// Make sure we don't have a daft user who thinks having the SQLite database in the forum directory is a good idea
-	if ($dbms_details['DRIVER'] == 'phpbb\db\driver\sqlite' && stripos(phpbb_realpath($dbhost), phpbb_realpath('../')) === 0)
+	if (($dbms_details['DRIVER'] == 'phpbb\db\driver\sqlite' || $dbms_details['DRIVER'] == 'phpbb\db\driver\sqlite3') && stripos(phpbb_realpath($dbhost), phpbb_realpath('../')) === 0)
 	{
 		$error[] = $lang['INST_ERR_DB_FORUM_PATH'];
 		return false;
@@ -243,6 +252,7 @@ function connect_check_db($error_connect, &$error, $dbms_details, $table_prefix,
 		break;
 
 		case 'phpbb\db\driver\sqlite':
+		case 'phpbb\db\driver\sqlite3':
 			$prefix_length = 200;
 		break;
 
@@ -299,6 +309,14 @@ function connect_check_db($error_connect, &$error, $dbms_details, $table_prefix,
 				}
 			break;
 
+			case 'phpbb\db\driver\sqlite3':
+				$version = \SQLite3::version();
+				if (version_compare($version['versionString'], '3.6.15', '<'))
+				{
+					$error[] = $lang['INST_ERR_DB_NO_SQLITE3'];
+				}
+			break;
+
 			case 'phpbb\db\driver\firebird':
 				// check the version of FB, use some hackery if we can't get access to the server info
 				if ($db->service_handle !== false && function_exists('ibase_server_info'))
author	Patrick Webster <noxwizard@phpbb.com>	2013-11-03 21:58:05 -0600
committer	Joas Schilling <nickvergessen@gmx.de>	2014-05-02 15:45:20 +0200
commit	b39b0369aa0ac6853bde0504259166f570beb983 (patch)
tree	5bc685f9ba2fc59a54cc2fb3d61440052f44dbb8 /phpBB/includes
parent	d52c2d1b5caf1382d178e55eff311ceddf4b392f (diff)
download	forums-b39b0369aa0ac6853bde0504259166f570beb983.tar forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.gz forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.bz2 forums-b39b0369aa0ac6853bde0504259166f570beb983.tar.xz forums-b39b0369aa0ac6853bde0504259166f570beb983.zip