1 files changed, 221 insertions, 113 deletions
diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 2b0132075b..a983ed91b5 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -257,6 +257,36 @@ class tools
 				'VARBINARY'	=> 'blob',
 			),
 
+			'sqlite3'	=> array(
+				'INT:'		=> 'INT(%d)',
+				'BINT'		=> 'BIGINT(20)',
+				'UINT'		=> 'INTEGER UNSIGNED',
+				'UINT:'		=> 'INTEGER UNSIGNED',
+				'TINT:'		=> 'TINYINT(%d)',
+				'USINT'		=> 'INTEGER UNSIGNED',
+				'BOOL'		=> 'INTEGER UNSIGNED',
+				'VCHAR'		=> 'VARCHAR(255)',
+				'VCHAR:'	=> 'VARCHAR(%d)',
+				'CHAR:'		=> 'CHAR(%d)',
+				'XSTEXT'	=> 'TEXT(65535)',
+				'STEXT'		=> 'TEXT(65535)',
+				'TEXT'		=> 'TEXT(65535)',
+				'MTEXT'		=> 'MEDIUMTEXT(16777215)',
+				'XSTEXT_UNI'=> 'TEXT(65535)',
+				'STEXT_UNI'	=> 'TEXT(65535)',
+				'TEXT_UNI'	=> 'TEXT(65535)',
+				'MTEXT_UNI'	=> 'MEDIUMTEXT(16777215)',
+				'TIMESTAMP'	=> 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
+				'DECIMAL'	=> 'DECIMAL(5,2)',
+				'DECIMAL:'	=> 'DECIMAL(%d,2)',
+				'PDECIMAL'	=> 'DECIMAL(6,3)',
+				'PDECIMAL:'	=> 'DECIMAL(%d,3)',
+				'VCHAR_UNI'	=> 'VARCHAR(255)',
+				'VCHAR_UNI:'=> 'VARCHAR(%d)',
+				'VCHAR_CI'	=> 'VARCHAR(255)',
+				'VARBINARY'	=> 'BLOB',
+			),
+
 			'postgres'	=> array(
 				'INT:'		=> 'INT4',
 				'BINT'		=> 'INT8',
@@ -299,7 +329,7 @@ class tools
 	* A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
 	* @var array
 	*/
-	var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
+	var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite', 'sqlite3');
 
 	/**
 	* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
@@ -389,6 +419,13 @@ class tools
 					WHERE type = "table"';
 			break;
 
+			case 'sqlite3':
+				$sql = 'SELECT name
+					FROM sqlite_master
+					WHERE type = "table"
+						AND name <> "sqlite_sequence"';
+			break;
+
 			case 'mssql':
 			case 'mssql_odbc':
 			case 'mssqlnative':
@@ -567,6 +604,7 @@ class tools
 					case 'mysql_41':
 					case 'postgres':
 					case 'sqlite':
+					case 'sqlite3':
 						$table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
 					break;
 
@@ -604,6 +642,7 @@ class tools
 
 			case 'mysql_40':
 			case 'sqlite':
+			case 'sqlite3':
 				$table_sql .= "\n);";
 				$statements[] = $table_sql;
 			break;
@@ -722,7 +761,7 @@ class tools
 		$sqlite = false;
 
 		// For SQLite we need to perform the schema changes in a much more different way
-		if ($this->db->sql_layer == 'sqlite' && $this->return_statements)
+		if (($this->db->sql_layer == 'sqlite' || $this->db->sql_layer == 'sqlite3') && $this->return_statements)
 		{
 			$sqlite_data = array();
 			$sqlite = true;
@@ -1140,6 +1179,7 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 				$sql = "SELECT sql
 					FROM sqlite_master
 					WHERE type = 'table'
@@ -1273,6 +1313,7 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 				$sql = "PRAGMA index_list('" . $table_name . "');";
 				$col = 'name';
 			break;
@@ -1293,6 +1334,7 @@ class tools
 				case 'oracle':
 				case 'postgres':
 				case 'sqlite':
+				case 'sqlite3':
 					$row[$col] = substr($row[$col], strlen($table_name) + 1);
 				break;
 			}
@@ -1377,6 +1419,7 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 				$sql = "PRAGMA index_list('" . $table_name . "');";
 				$col = 'name';
 			break;
@@ -1390,7 +1433,7 @@ class tools
 				continue;
 			}
 
-			if ($this->sql_layer == 'sqlite' && !$row['unique'])
+			if (($this->sql_layer == 'sqlite' || $this->sql_layer == 'sqlite3') && !$row['unique'])
 			{
 				continue;
 			}
@@ -1418,6 +1461,7 @@ class tools
 				case 'firebird':
 				case 'postgres':
 				case 'sqlite':
+				case 'sqlite3':
 					$row[$col] = substr($row[$col], strlen($table_name) + 1);
 				break;
 			}
@@ -1629,11 +1673,17 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 				$return_array['primary_key_set'] = false;
 				if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
 				{
 					$sql .= ' INTEGER PRIMARY KEY';
 					$return_array['primary_key_set'] = true;
+
+					if ($this->sql_layer === 'sqlite3')
+					{
+						$sql .= ' AUTOINCREMENT';
+					}
 				}
 				else
 				{
@@ -1770,67 +1820,63 @@ class tools
 			break;
 
 			case 'sqlite':
-
 				if ($inline && $this->return_statements)
 				{
 					return $column_name . ' ' . $column_data['column_type_sql'];
 				}
 
-				if (version_compare(sqlite_libversion(), '3.0') == -1)
+				$recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+				if (empty($recreate_queries))
 				{
-					$sql = "SELECT sql
-						FROM sqlite_master
-						WHERE type = 'table'
-							AND name = '{$table_name}'
-						ORDER BY type DESC, name;";
-					$result = $this->db->sql_query($sql);
-
-					if (!$result)
-					{
-						break;
-					}
+					break;
+				}
 
-					$row = $this->db->sql_fetchrow($result);
-					$this->db->sql_freeresult($result);
+				$statements[] = 'begin';
 
-					$statements[] = 'begin';
+				$sql_create_table = array_shift($recreate_queries);
 
-					// Create a backup table and populate it, destroy the existing one
-					$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
-					$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
-					$statements[] = 'DROP TABLE ' . $table_name;
+				// Create a backup table and populate it, destroy the existing one
+				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
+				$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
+				$statements[] = 'DROP TABLE ' . $table_name;
 
-					preg_match('#\((.*)\)#s', $row['sql'], $matches);
+				preg_match('#\((.*)\)#s', $sql_create_table, $matches);
 
-					$new_table_cols = trim($matches[1]);
-					$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
-					$column_list = array();
+				$new_table_cols = trim($matches[1]);
+				$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
+				$column_list = array();
 
-					foreach ($old_table_cols as $declaration)
+				foreach ($old_table_cols as $declaration)
+				{
+					$entities = preg_split('#\s+#', trim($declaration));
+					if ($entities[0] == 'PRIMARY')
 					{
-						$entities = preg_split('#\s+#', trim($declaration));
-						if ($entities[0] == 'PRIMARY')
-						{
-							continue;
-						}
-						$column_list[] = $entities[0];
+						continue;
 					}
+					$column_list[] = $entities[0];
+				}
 
-					$columns = implode(',', $column_list);
+				$columns = implode(',', $column_list);
 
-					$new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
+				$new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
 
-					// create a new table and fill it up. destroy the temp one
-					$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
-					$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
-					$statements[] = 'DROP TABLE ' . $table_name . '_temp';
+				// create a new table and fill it up. destroy the temp one
+				$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
+				$statements = array_merge($statements, $recreate_queries);
 
-					$statements[] = 'commit';
-				}
-				else
+				$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
+				$statements[] = 'DROP TABLE ' . $table_name . '_temp';
+
+				$statements[] = 'commit';
+			break;
+
+			case 'sqlite3':
+				if ($inline && $this->return_statements)
 				{
-					$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
+					return $column_name . ' ' . $column_data['column_type_sql'];
 				}
+
+				$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
 			break;
 		}
 
@@ -1908,67 +1954,61 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
 					return $column_name;
 				}
 
-				if (version_compare(sqlite_libversion(), '3.0') == -1)
+				$recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name);
+				if (empty($recreate_queries))
 				{
-					$sql = "SELECT sql
-						FROM sqlite_master
-						WHERE type = 'table'
-							AND name = '{$table_name}'
-						ORDER BY type DESC, name;";
-					$result = $this->db->sql_query($sql);
-
-					if (!$result)
-					{
-						break;
-					}
+					break;
+				}
 
-					$row = $this->db->sql_fetchrow($result);
-					$this->db->sql_freeresult($result);
+				$statements[] = 'begin';
 
-					$statements[] = 'begin';
+				$sql_create_table = array_shift($recreate_queries);
 
-					// Create a backup table and populate it, destroy the existing one
-					$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
-					$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
-					$statements[] = 'DROP TABLE ' . $table_name;
+				// Create a backup table and populate it, destroy the existing one
+				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
+				$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
+				$statements[] = 'DROP TABLE ' . $table_name;
 
-					preg_match('#\((.*)\)#s', $row['sql'], $matches);
+				preg_match('#\((.*)\)#s', $sql_create_table, $matches);
 
-					$new_table_cols = trim($matches[1]);
-					$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
-					$column_list = array();
+				$new_table_cols = trim($matches[1]);
+				$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
+				$column_list = array();
 
-					foreach ($old_table_cols as $declaration)
+				foreach ($old_table_cols as $declaration)
+				{
+					$entities = preg_split('#\s+#', trim($declaration));
+					if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
 					{
-						$entities = preg_split('#\s+#', trim($declaration));
-						if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
-						{
-							continue;
-						}
-						$column_list[] = $entities[0];
+						continue;
 					}
+					$column_list[] = $entities[0];
+				}
 
-					$columns = implode(',', $column_list);
-
-					$new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
-
-					// create a new table and fill it up. destroy the temp one
-					$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
-					$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
-					$statements[] = 'DROP TABLE ' . $table_name . '_temp';
+				$columns = implode(',', $column_list);
 
-					$statements[] = 'commit';
-				}
-				else
+				$new_table_cols = trim(preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols));
+				if (substr($new_table_cols, -1) === ',')
 				{
-					$statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
+					// Remove the comma from the last entry again
+					$new_table_cols = substr($new_table_cols, 0, -1);
 				}
+
+				// create a new table and fill it up. destroy the temp one
+				$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
+				$statements = array_merge($statements, $recreate_queries);
+
+				$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
+				$statements[] = 'DROP TABLE ' . $table_name . '_temp';
+
+				$statements[] = 'commit';
 			break;
 		}
 
@@ -1998,6 +2038,7 @@ class tools
 			case 'oracle':
 			case 'postgres':
 			case 'sqlite':
+			case 'sqlite3':
 				$statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
 			break;
 		}
@@ -2104,35 +2145,29 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
 					return $column;
 				}
 
-				$sql = "SELECT sql
-					FROM sqlite_master
-					WHERE type = 'table'
-						AND name = '{$table_name}'
-					ORDER BY type DESC, name;";
-				$result = $this->db->sql_query($sql);
-
-				if (!$result)
+				$recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+				if (empty($recreate_queries))
 				{
 					break;
 				}
 
-				$row = $this->db->sql_fetchrow($result);
-				$this->db->sql_freeresult($result);
-
 				$statements[] = 'begin';
 
+				$sql_create_table = array_shift($recreate_queries);
+
 				// Create a backup table and populate it, destroy the existing one
-				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
+				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
 				$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 				$statements[] = 'DROP TABLE ' . $table_name;
 
-				preg_match('#\((.*)\)#s', $row['sql'], $matches);
+				preg_match('#\((.*)\)#s', $sql_create_table, $matches);
 
 				$new_table_cols = trim($matches[1]);
 				$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
@@ -2152,6 +2187,8 @@ class tools
 
 				// create a new table and fill it up. destroy the temp one
 				$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
+				$statements = array_merge($statements, $recreate_queries);
+
 				$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 				$statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
@@ -2182,6 +2219,7 @@ class tools
 			case 'postgres':
 			case 'oracle':
 			case 'sqlite':
+			case 'sqlite3':
 				$statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 			break;
 
@@ -2225,6 +2263,7 @@ class tools
 			case 'postgres':
 			case 'oracle':
 			case 'sqlite':
+			case 'sqlite3':
 				$statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 			break;
 
@@ -2316,6 +2355,7 @@ class tools
 				break;
 
 				case 'sqlite':
+				case 'sqlite3':
 					$sql = "PRAGMA index_info('" . $table_name . "');";
 					$col = 'name';
 				break;
@@ -2335,6 +2375,7 @@ class tools
 					case 'oracle':
 					case 'postgres':
 					case 'sqlite':
+					case 'sqlite3':
 						$row[$col] = substr($row[$col], strlen($table_name) + 1);
 					break;
 				}
@@ -2488,35 +2529,29 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
 					return $column_name . ' ' . $column_data['column_type_sql'];
 				}
 
-				$sql = "SELECT sql
-					FROM sqlite_master
-					WHERE type = 'table'
-						AND name = '{$table_name}'
-					ORDER BY type DESC, name;";
-				$result = $this->db->sql_query($sql);
-
-				if (!$result)
+				$recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+				if (empty($recreate_queries))
 				{
 					break;
 				}
 
-				$row = $this->db->sql_fetchrow($result);
-				$this->db->sql_freeresult($result);
-
 				$statements[] = 'begin';
 
+				$sql_create_table = array_shift($recreate_queries);
+
 				// Create a temp table and populate it, destroy the existing one
-				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
+				$statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
 				$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 				$statements[] = 'DROP TABLE ' . $table_name;
 
-				preg_match('#\((.*)\)#s', $row['sql'], $matches);
+				preg_match('#\((.*)\)#s', $sql_create_table, $matches);
 
 				$new_table_cols = trim($matches[1]);
 				$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
@@ -2534,8 +2569,10 @@ class tools
 
 				$columns = implode(',', $column_list);
 
-				// create a new table and fill it up. destroy the temp one
+				// Create a new table and fill it up. destroy the temp one
 				$statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
+				$statements = array_merge($statements, $recreate_queries);
+
 				$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 				$statements[] = 'DROP TABLE ' . $table_name . '_temp';
 
@@ -2701,4 +2738,75 @@ class tools
 
 		return $this->is_sql_server_2000;
 	}
+
+	/**
+	* Returns the Queries which are required to recreate a table including indexes
+	*
+	* @param string $table_name
+	* @param string $remove_column	When we drop a column, we remove the column
+	*								from all indexes. If the index has no other
+	*								column, we drop it completly.
+	* @return array
+	*/
+	protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '')
+	{
+		$queries = array();
+
+		$sql = "SELECT sql
+			FROM sqlite_master
+			WHERE type = 'table'
+				AND name = '{$table_name}'";
+		$result = $this->db->sql_query($sql);
+		$sql_create_table = $this->db->sql_fetchfield('sql');
+		$this->db->sql_freeresult($result);
+
+		if (!$sql_create_table)
+		{
+			return array();
+		}
+		$queries[] = $sql_create_table;
+
+		$sql = "SELECT sql
+			FROM sqlite_master
+			WHERE type = 'index'
+				AND tbl_name = '{$table_name}'";
+		$result = $this->db->sql_query($sql);
+		while ($sql_create_index = $this->db->sql_fetchfield('sql'))
+		{
+			if ($remove_column)
+			{
+				$match = array();
+				preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match);
+				if (!isset($match[1]))
+				{
+					continue;
+				}
+
+				// Find and remove $remove_column from the index
+				$columns = explode(', ', $match[1]);
+				$found_column = array_search($remove_column, $columns);
+				if ($found_column !== false)
+				{
+					unset($columns[$found_column]);
+
+					// If the column list is not empty add the index to the list
+					if (!empty($columns))
+					{
+						$queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index);
+					}
+				}
+				else
+				{
+					$queries[] = $sql_create_index;
+				}
+			}
+			else
+			{
+				$queries[] = $sql_create_index;
+			}
+		}
+		$this->db->sql_freeresult($result);
+
+		return $queries;
+	}
 }