From 743d816631292a2081af4c5f7fc2fad2aff17c58 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Fri, 11 Apr 2014 17:08:01 +0200
Subject: [ticket/12012] Correctly drop default value constraints on MSSQL

We need to drop the default constraints of a column,
before being able to change their type or deleting them.

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 144 +++++++++++++++++++----------------------------
 1 file changed, 59 insertions(+), 85 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 3d480b7e1c..87c205c6c0 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -1846,49 +1846,7 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
-				$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
-				$result = $this->db->sql_query($sql);
-				$row = $this->db->sql_fetchrow($result);
-				$this->db->sql_freeresult($result);
-
-				// Remove default constraints
-				if ($row['mssql_version'][0] == '8')	// SQL Server 2000
-				{
-					// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
-					// Deprecated in SQL Server 2005
-					$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
-						SET @drop_default_name =
-							(SELECT so.name FROM sysobjects so
-							JOIN sysconstraints sc ON so.id = sc.constid
-							WHERE object_name(so.parent_obj) = '{$table_name}'
-								AND so.xtype = 'D'
-								AND sc.colid = (SELECT colid FROM syscolumns
-									WHERE id = object_id('{$table_name}')
-										AND name = '{$column_name}'))
-						IF @drop_default_name <> ''
-						BEGIN
-							SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
-							EXEC(@cmd)
-						END";
-				}
-				else
-				{
-					$sql = "SELECT dobj.name AS def_name
-					FROM sys.columns col 
-						LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
-					WHERE col.object_id = object_id('{$table_name}') 
-					AND col.name = '{$column_name}'
-					AND dobj.name IS NOT NULL";
-					$result = $this->db->sql_query($sql);
-					$row = $this->db->sql_fetchrow($result);
-					$this->db->sql_freeresult($result);
-
-					if ($row)
-					{
-						$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
-					}
-				}
-
+				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
 				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 			break;
 
@@ -2371,52 +2329,12 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
+				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
 				if (!empty($column_data['default']))
 				{
-					$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
-					$result = $this->db->sql_query($sql);
-					$row = $this->db->sql_fetchrow($result);
-					$this->db->sql_freeresult($result);
-
-					// Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
-					if ($row['mssql_version'][0] == '8')	// SQL Server 2000
-					{
-						$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
-							SET @drop_default_name =
-								(SELECT so.name FROM sysobjects so
-								JOIN sysconstraints sc ON so.id = sc.constid
-								WHERE object_name(so.parent_obj) = '{$table_name}'
-									AND so.xtype = 'D'
-									AND sc.colid = (SELECT colid FROM syscolumns
-										WHERE id = object_id('{$table_name}')
-											AND name = '{$column_name}'))
-							IF @drop_default_name <> ''
-							BEGIN
-								SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
-								EXEC(@cmd)
-							END
-							SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
-							EXEC(@cmd)";
-					}
-					else
-					{
-						$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
-							SET @drop_default_name =
-								(SELECT dobj.name FROM sys.columns col 
-									LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
-								WHERE col.object_id = object_id('{$table_name}') 
-								AND col.name = '{$column_name}'
-								AND dobj.name IS NOT NULL)
-							IF @drop_default_name <> ''
-							BEGIN
-								SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
-								EXEC(@cmd)
-							END
-							SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
-							EXEC(@cmd)";
-					}
+					$statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $this->db->sql_escape($column_data['default']) . ' FOR [' . $column_name . ']';
 				}
 			break;
 
@@ -2551,4 +2469,60 @@ class tools
 
 		return $this->_sql_run_sql($statements);
 	}
+
+	/**
+	* Drop the default constraints of a column
+	*
+	* We need to drop the default constraints of a column,
+	* before being able to change their type or deleting them.
+	*
+	* @param string $table_name
+	* @param string $column_name
+	* @return array		Array with SQL statements
+	*/
+	protected function mssql_drop_default_constraints($table_name, $column_name)
+	{
+		$statements = array();
+		$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
+		$result = $this->db->sql_query($sql);
+		$mssql_server_properties = $this->db->sql_fetchrow($result);
+		$this->db->sql_freeresult($result);
+
+		// Remove default constraints
+		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		{
+			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
+			// Deprecated in SQL Server 2005
+			$sql = "SELECT so.name AS def_name FROM sysobjects so
+				JOIN sysconstraints sc ON so.id = sc.constid
+				WHERE object_name(so.parent_obj) = '{$table_name}'
+					AND so.xtype = 'D'
+					AND sc.colid = (SELECT colid FROM syscolumns
+						WHERE id = object_id('{$table_name}')
+							AND name = '{$column_name}')";
+			$result = $this->db->sql_query($sql);
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+			}
+			$this->db->sql_freeresult($result);
+		}
+		else
+		{
+			$sql = "SELECT dobj.name AS def_name
+				FROM sys.columns col
+					LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
+				WHERE col.object_id = object_id('{$table_name}')
+					AND col.name = '{$column_name}'
+					AND dobj.name IS NOT NULL";
+			$result = $this->db->sql_query($sql);
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+			}
+			$this->db->sql_freeresult($result);
+		}
+
+		return $statements;
+	}
 }
-- 
cgit v1.2.1


From 29ba06968d383dd63a869345352117d7ed82497b Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Sat, 12 Apr 2014 09:53:45 +0200
Subject: [ticket/12012] Fix query layout

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 87c205c6c0..a746099a14 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2493,7 +2493,8 @@ class tools
 		{
 			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 			// Deprecated in SQL Server 2005
-			$sql = "SELECT so.name AS def_name FROM sysobjects so
+			$sql = "SELECT so.name AS def_name
+				FROM sysobjects so
 				JOIN sysconstraints sc ON so.id = sc.constid
 				WHERE object_name(so.parent_obj) = '{$table_name}'
 					AND so.xtype = 'D'
-- 
cgit v1.2.1


From 9036cdaaa2d45664bca1a0e0cdeaaf2bd13c662a Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Sat, 12 Apr 2014 12:37:34 +0200
Subject: [ticket/12012] Drop and recreate indexes when changing a column on
 MSSQL

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 111 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 111 insertions(+)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index a746099a14..1299df51dd 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -1846,6 +1846,13 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
+				$indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
+
+				if (!empty($indexes))
+				{
+					trigger_error("Column '$column_name' on table '$table_name' is still part of an index and can not be removed: " . implode(', ', array_keys($indexes)), E_USER_ERROR);
+				}
+
 				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
 				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 			break;
@@ -2329,6 +2336,16 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
+				$indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
+
+				if (!empty($indexes))
+				{
+					foreach ($indexes as $index_name => $index_data)
+					{
+						$this->sql_index_drop($table_name, $index_name);
+					}
+				}
+
 				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
@@ -2336,6 +2353,15 @@ class tools
 				{
 					$statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $this->db->sql_escape($column_data['default']) . ' FOR [' . $column_name . ']';
 				}
+
+				if (!empty($indexes))
+				{
+					// Recreate indexes after we changed the column
+					foreach ($indexes as $index_name => $index_data)
+					{
+						$this->sql_create_index($table_name, $index_name, $index_data);
+					}
+				}
 			break;
 
 			case 'mysql_40':
@@ -2526,4 +2552,89 @@ class tools
 
 		return $statements;
 	}
+
+	/**
+	* Get a list with existing indexes for the column
+	*
+	* @param string $table_name
+	* @param string $column_name
+	* @return array		Array with Index name => columns
+	*/
+	protected function mssql_get_existing_indexes($table_name, $column_name)
+	{
+		$existing_indexes = array();
+		$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
+		$result = $this->db->sql_query($sql);
+		$mssql_server_properties = $this->db->sql_fetchrow($result);
+		$this->db->sql_freeresult($result);
+
+		// Remove default constraints
+		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		{
+			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
+			// Deprecated in SQL Server 2005
+			/**
+			* @todo Fix for SQL Server 2000
+			$sql = "SELECT so.name AS def_name
+				FROM sysobjects so
+				JOIN sysconstraints sc ON so.id = sc.constid
+				WHERE object_name(so.parent_obj) = '{$table_name}'
+					AND so.xtype = 'D'
+					AND sc.colid = (SELECT colid FROM syscolumns
+						WHERE id = object_id('{$table_name}')
+							AND name = '{$column_name}')";
+			$result = $this->db->sql_query($sql);
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+			}
+			$this->db->sql_freeresult($result);
+			*/
+		}
+		else
+		{
+			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name
+				FROM sys.indexes ix
+				INNER JOIN sys.index_columns ixc
+					ON ixc.object_id = ix.object_id
+						AND ixc.index_id = ix.index_id
+				INNER JOIN sys.columns cols
+					ON cols.column_id = ixc.column_id
+					AND cols.object_id = ix.object_id
+				WHERE ix.object_id = object_id('{$table_name}')
+					AND cols.name = '{$column_name}'";
+			$result = $this->db->sql_query($sql);
+			$existing_indexes = array();
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$existing_indexes[$row['phpbb_index_name']] = array();
+			}
+			$this->db->sql_freeresult($result);
+
+			if (empty($existing_indexes))
+			{
+				return array();
+			}
+
+			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
+				FROM sys.indexes ix
+				INNER JOIN sys.index_columns ixc
+					ON ixc.object_id = ix.object_id
+						AND ixc.index_id = ix.index_id
+				INNER JOIN sys.columns cols
+					ON cols.column_id = ixc.column_id
+						AND cols.object_id = ix.object_id
+				WHERE ix.object_id = object_id('{$table_name}')
+					AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
+			$result = $this->db->sql_query($sql);
+
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
+			}
+			$this->db->sql_freeresult($result);
+		}
+
+		return $existing_indexes;
+	}
 }
-- 
cgit v1.2.1


From e2784d01cee227a56f2ad7bcaee4b796dbf6dde6 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Sat, 12 Apr 2014 12:42:32 +0200
Subject: [ticket/12012] Fix tools::mssql_get_existing_indexes() for SQL Server
 2000

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 50 ++++++++++++++++++++++++++++++++++--------------
 1 file changed, 36 insertions(+), 14 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 1299df51dd..c2285bcf80 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2568,28 +2568,50 @@ class tools
 		$mssql_server_properties = $this->db->sql_fetchrow($result);
 		$this->db->sql_freeresult($result);
 
-		// Remove default constraints
 		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
 		{
 			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 			// Deprecated in SQL Server 2005
-			/**
-			* @todo Fix for SQL Server 2000
-			$sql = "SELECT so.name AS def_name
-				FROM sysobjects so
-				JOIN sysconstraints sc ON so.id = sc.constid
-				WHERE object_name(so.parent_obj) = '{$table_name}'
-					AND so.xtype = 'D'
-					AND sc.colid = (SELECT colid FROM syscolumns
-						WHERE id = object_id('{$table_name}')
-							AND name = '{$column_name}')";
+			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name
+				FROM sysindexes ix
+				INNER JOIN sysindexkeys ixc
+					ON ixc.id = ix.id
+						AND ixc.indid = ix.indid
+				INNER JOIN syscolumns cols
+					ON cols.colid = ixc.colid
+						AND cols.id = ix.id
+				WHERE ix.id = object_id('{$table_name}')
+					AND cols.name = '{$column_name}'";
 			$result = $this->db->sql_query($sql);
+			$existing_indexes = array();
 			while ($row = $this->db->sql_fetchrow($result))
 			{
-				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+				$existing_indexes[$row['phpbb_index_name']] = array();
+			}
+			$this->db->sql_freeresult($result);
+
+			if (empty($existing_indexes))
+			{
+				return array();
+			}
+
+			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
+				FROM sysindexes ix
+				INNER JOIN sysindexkeys ixc
+					ON ixc.id = ix.id
+						AND ixc.indid = ix.indid
+				INNER JOIN syscolumns cols
+					ON cols.colid = ixc.colid
+						AND cols.id = ix.id
+				WHERE ix.id = object_id('{$table_name}')
+					AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
+			$result = $this->db->sql_query($sql);
+
+			while ($row = $this->db->sql_fetchrow($result))
+			{
+				$existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
 			}
 			$this->db->sql_freeresult($result);
-			*/
 		}
 		else
 		{
@@ -2600,7 +2622,7 @@ class tools
 						AND ixc.index_id = ix.index_id
 				INNER JOIN sys.columns cols
 					ON cols.column_id = ixc.column_id
-					AND cols.object_id = ix.object_id
+						AND cols.object_id = ix.object_id
 				WHERE ix.object_id = object_id('{$table_name}')
 					AND cols.name = '{$column_name}'";
 			$result = $this->db->sql_query($sql);
-- 
cgit v1.2.1


From 190b4282df5c5c81123b1ed371eeada385831e99 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Sun, 13 Apr 2014 16:43:57 +0200
Subject: [ticket/12012] Return SQL statements for index drop/create

Otherwise we recreate the index before changing the column

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 20 +++++++++++++++++---
 1 file changed, 17 insertions(+), 3 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index c2285bcf80..113059900a 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2336,21 +2336,32 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
+				// We need the data here
+				$old_return_statements = $this->return_statements;
+				$this->return_statements = true;
+
 				$indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
 
+				// Drop any indexes
 				if (!empty($indexes))
 				{
 					foreach ($indexes as $index_name => $index_data)
 					{
-						$this->sql_index_drop($table_name, $index_name);
+						$result = $this->sql_index_drop($table_name, $index_name);
+						$statements = array_merge($statements, $result);
 					}
 				}
 
-				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
+				// Drop default value constraint
+				$result = $this->mssql_drop_default_constraints($table_name, $column_name);
+				$statements = array_merge($statements, $result);
+
+				// Change the column
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
 				if (!empty($column_data['default']))
 				{
+					// Add new default value constraint
 					$statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $this->db->sql_escape($column_data['default']) . ' FOR [' . $column_name . ']';
 				}
 
@@ -2359,9 +2370,12 @@ class tools
 					// Recreate indexes after we changed the column
 					foreach ($indexes as $index_name => $index_data)
 					{
-						$this->sql_create_index($table_name, $index_name, $index_data);
+						$result = $this->sql_create_index($table_name, $index_name, $index_data);
+						$statements = array_merge($statements, $result);
 					}
 				}
+
+				$this->return_statements = $old_return_statements;
 			break;
 
 			case 'mysql_40':
-- 
cgit v1.2.1


From 7dc163f2b7f483e4abb46015c0e41b47cddfd757 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Mon, 14 Apr 2014 17:21:53 +0200
Subject: [ticket/12012] Drop and recreate indexes when removing columns

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 35 +++++++++++++++++++++++++++++++++--
 1 file changed, 33 insertions(+), 2 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 113059900a..bc505010ba 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -1846,15 +1846,46 @@ class tools
 
 			case 'mssql':
 			case 'mssqlnative':
+				// We need the data here
+				$old_return_statements = $this->return_statements;
+				$this->return_statements = true;
+
 				$indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
 
+				// Drop any indexes
+				$recreate_indexes = array();
 				if (!empty($indexes))
 				{
-					trigger_error("Column '$column_name' on table '$table_name' is still part of an index and can not be removed: " . implode(', ', array_keys($indexes)), E_USER_ERROR);
+					foreach ($indexes as $index_name => $index_data)
+					{
+						$result = $this->sql_index_drop($table_name, $index_name);
+						$statements = array_merge($statements, $result);
+						if (sizeof($index_data) > 1)
+						{
+							// Remove this column from the index and recreate it
+							$recreate_indexes[$index_name] = array_diff($index_data, array($column_name));
+						}
+					}
 				}
 
-				$statements = $this->mssql_drop_default_constraints($table_name, $column_name);
+				// Drop default value constraint
+				$result = $this->mssql_drop_default_constraints($table_name, $column_name);
+				$statements = array_merge($statements, $result);
+
+				// Remove the column
 				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
+
+				if (!empty($recreate_indexes))
+				{
+					// Recreate indexes after we removed the column
+					foreach ($recreate_indexes as $index_name => $index_data)
+					{
+						$result = $this->sql_create_index($table_name, $index_name, $index_data);
+						$statements = array_merge($statements, $result);
+					}
+				}
+
+				$this->return_statements = $old_return_statements;
 			break;
 
 			case 'mysql_40':
-- 
cgit v1.2.1


From 0a953ddb1505087f77eab1d2ee96033f99a08294 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Sun, 20 Apr 2014 12:54:19 +0200
Subject: [ticket/12012] Remove duplicated code (only the $sql are different)

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 101 ++++++++++++++++++++---------------------------
 1 file changed, 42 insertions(+), 59 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index bc505010ba..0e3ab59da0 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2572,12 +2572,6 @@ class tools
 					AND sc.colid = (SELECT colid FROM syscolumns
 						WHERE id = object_id('{$table_name}')
 							AND name = '{$column_name}')";
-			$result = $this->db->sql_query($sql);
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
-			}
-			$this->db->sql_freeresult($result);
 		}
 		else
 		{
@@ -2587,14 +2581,15 @@ class tools
 				WHERE col.object_id = object_id('{$table_name}')
 					AND col.name = '{$column_name}'
 					AND dobj.name IS NOT NULL";
-			$result = $this->db->sql_query($sql);
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
-			}
-			$this->db->sql_freeresult($result);
 		}
 
+		$result = $this->db->sql_query($sql);
+		while ($row = $this->db->sql_fetchrow($result))
+		{
+			$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+		}
+		$this->db->sql_freeresult($result);
+
 		return $statements;
 	}
 
@@ -2627,36 +2622,6 @@ class tools
 						AND cols.id = ix.id
 				WHERE ix.id = object_id('{$table_name}')
 					AND cols.name = '{$column_name}'";
-			$result = $this->db->sql_query($sql);
-			$existing_indexes = array();
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$existing_indexes[$row['phpbb_index_name']] = array();
-			}
-			$this->db->sql_freeresult($result);
-
-			if (empty($existing_indexes))
-			{
-				return array();
-			}
-
-			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
-				FROM sysindexes ix
-				INNER JOIN sysindexkeys ixc
-					ON ixc.id = ix.id
-						AND ixc.indid = ix.indid
-				INNER JOIN syscolumns cols
-					ON cols.colid = ixc.colid
-						AND cols.id = ix.id
-				WHERE ix.id = object_id('{$table_name}')
-					AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
-			$result = $this->db->sql_query($sql);
-
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
-			}
-			$this->db->sql_freeresult($result);
 		}
 		else
 		{
@@ -2670,19 +2635,36 @@ class tools
 						AND cols.object_id = ix.object_id
 				WHERE ix.object_id = object_id('{$table_name}')
 					AND cols.name = '{$column_name}'";
-			$result = $this->db->sql_query($sql);
-			$existing_indexes = array();
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$existing_indexes[$row['phpbb_index_name']] = array();
-			}
-			$this->db->sql_freeresult($result);
+		}
 
-			if (empty($existing_indexes))
-			{
-				return array();
-			}
+		$result = $this->db->sql_query($sql);
+		$existing_indexes = array();
+		while ($row = $this->db->sql_fetchrow($result))
+		{
+			$existing_indexes[$row['phpbb_index_name']] = array();
+		}
+		$this->db->sql_freeresult($result);
+
+		if (empty($existing_indexes))
+		{
+			return array();
+		}
 
+		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		{
+			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
+				FROM sysindexes ix
+				INNER JOIN sysindexkeys ixc
+					ON ixc.id = ix.id
+						AND ixc.indid = ix.indid
+				INNER JOIN syscolumns cols
+					ON cols.colid = ixc.colid
+						AND cols.id = ix.id
+				WHERE ix.id = object_id('{$table_name}')
+					AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
+		}
+		else
+		{
 			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
 				FROM sys.indexes ix
 				INNER JOIN sys.index_columns ixc
@@ -2693,14 +2675,15 @@ class tools
 						AND cols.object_id = ix.object_id
 				WHERE ix.object_id = object_id('{$table_name}')
 					AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
-			$result = $this->db->sql_query($sql);
+		}
 
-			while ($row = $this->db->sql_fetchrow($result))
-			{
-				$existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
-			}
-			$this->db->sql_freeresult($result);
+		$result = $this->db->sql_query($sql);
+
+		while ($row = $this->db->sql_fetchrow($result))
+		{
+			$existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
 		}
+		$this->db->sql_freeresult($result);
 
 		return $existing_indexes;
 	}
-- 
cgit v1.2.1


From bbc2e6c7b29603ab311828b34f3f55d0a44f0d7f Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Thu, 24 Apr 2014 14:53:33 +0200
Subject: [ticket/12012] Move MS SQL server comparison into a method

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 46 ++++++++++++++++++++++++++++------------------
 1 file changed, 28 insertions(+), 18 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 0e3ab59da0..9cd3e64d20 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -1869,7 +1869,7 @@ class tools
 				}
 
 				// Drop default value constraint
-				$result = $this->mssql_drop_default_constraints($table_name, $column_name);
+				$result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
 				$statements = array_merge($statements, $result);
 
 				// Remove the column
@@ -2384,7 +2384,7 @@ class tools
 				}
 
 				// Drop default value constraint
-				$result = $this->mssql_drop_default_constraints($table_name, $column_name);
+				$result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
 				$statements = array_merge($statements, $result);
 
 				// Change the column
@@ -2542,7 +2542,7 @@ class tools
 	}
 
 	/**
-	* Drop the default constraints of a column
+	* Get queries to drop the default constraints of a column
 	*
 	* We need to drop the default constraints of a column,
 	* before being able to change their type or deleting them.
@@ -2551,16 +2551,10 @@ class tools
 	* @param string $column_name
 	* @return array		Array with SQL statements
 	*/
-	protected function mssql_drop_default_constraints($table_name, $column_name)
+	protected function mssql_get_drop_default_constraints_queries($table_name, $column_name)
 	{
 		$statements = array();
-		$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
-		$result = $this->db->sql_query($sql);
-		$mssql_server_properties = $this->db->sql_fetchrow($result);
-		$this->db->sql_freeresult($result);
-
-		// Remove default constraints
-		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		if ($this->mssql_is_sql_server_2000())
 		{
 			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 			// Deprecated in SQL Server 2005
@@ -2603,12 +2597,7 @@ class tools
 	protected function mssql_get_existing_indexes($table_name, $column_name)
 	{
 		$existing_indexes = array();
-		$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
-		$result = $this->db->sql_query($sql);
-		$mssql_server_properties = $this->db->sql_fetchrow($result);
-		$this->db->sql_freeresult($result);
-
-		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		if ($this->mssql_is_sql_server_2000())
 		{
 			// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 			// Deprecated in SQL Server 2005
@@ -2650,7 +2639,7 @@ class tools
 			return array();
 		}
 
-		if ($mssql_server_properties['mssql_version'][0] == '8')	// SQL Server 2000
+		if ($this->mssql_is_sql_server_2000())
 		{
 			$sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
 				FROM sysindexes ix
@@ -2687,4 +2676,25 @@ class tools
 
 		return $existing_indexes;
 	}
+
+	protected $is_sql_server_2000;
+
+	/**
+	* Is the used MS SQL Server a SQL Server 2000?
+	*
+	* @return bool
+	*/
+	protected function mssql_is_sql_server_2000()
+	{
+		if ($this->is_sql_server_2000 === null)
+		{
+			$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
+			$result = $this->db->sql_query($sql);
+			$properties = $this->db->sql_fetchrow($result);
+			$this->db->sql_freeresult($result);
+			$this->is_sql_server_2000 = $properties['mssql_version'][0] == '8';
+		}
+
+		return $this->is_sql_server_2000;
+	}
 }
-- 
cgit v1.2.1


From d5ea4906ca3a85a278518cc91189176174422bde Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Thu, 24 Apr 2014 22:57:35 +0200
Subject: [ticket/12012] Move property to the top

PHPBB3-12012
---
 phpBB/phpbb/db/tools.php | 8 ++++++--
 1 file changed, 6 insertions(+), 2 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 9cd3e64d20..2b0132075b 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -33,6 +33,12 @@ class tools
 	*/
 	var $dbms_type_map = array();
 
+	/**
+	* Is the used MS SQL Server a SQL Server 2000?
+	* @var bool
+	*/
+	protected $is_sql_server_2000;
+
 	/**
 	* Get the column types for every database we support
 	*
@@ -2677,8 +2683,6 @@ class tools
 		return $existing_indexes;
 	}
 
-	protected $is_sql_server_2000;
-
 	/**
 	* Is the used MS SQL Server a SQL Server 2000?
 	*
-- 
cgit v1.2.1


From b39b0369aa0ac6853bde0504259166f570beb983 Mon Sep 17 00:00:00 2001
From: Patrick Webster <noxwizard@phpbb.com>
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/tools.php | 146 ++++++++++++++++++++++++++++++++---------------
 1 file changed, 99 insertions(+), 47 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 2b0132075b..ddd1ca5957 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,13 +1820,14 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
 					return $column_name . ' ' . $column_data['column_type_sql'];
 				}
 
-				if (version_compare(sqlite_libversion(), '3.0') == -1)
+				if (version_compare($this->db->sql_server_info(true), '3.0') == -1)
 				{
 					$sql = "SELECT sql
 						FROM sqlite_master
@@ -1829,7 +1880,7 @@ class tools
 				}
 				else
 				{
-					$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
+					$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
 				}
 			break;
 		}
@@ -1908,67 +1959,61 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
 					return $column_name;
 				}
 
-				if (version_compare(sqlite_libversion(), '3.0') == -1)
-				{
-					$sql = "SELECT sql
-						FROM sqlite_master
-						WHERE type = 'table'
-							AND name = '{$table_name}'
-						ORDER BY type DESC, name;";
-					$result = $this->db->sql_query($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)
-					{
-						break;
-					}
+				if (!$result)
+				{
+					break;
+				}
 
-					$row = $this->db->sql_fetchrow($result);
-					$this->db->sql_freeresult($result);
+				$row = $this->db->sql_fetchrow($result);
+				$this->db->sql_freeresult($result);
 
-					$statements[] = 'begin';
+				$statements[] = 'begin';
 
-					// 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', $row['sql']);
+				$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
+				$statements[] = 'DROP TABLE ' . $table_name;
 
-					preg_match('#\((.*)\)#s', $row['sql'], $matches);
+				preg_match('#\((.*)\)#s', $row['sql'], $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);
+				$columns = implode(',', $column_list);
 
-					$new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
+				$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';
+				// 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';
 
-					$statements[] = 'commit';
-				}
-				else
-				{
-					$statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
-				}
+				$statements[] = 'commit';
 			break;
 		}
 
@@ -1998,6 +2043,7 @@ class tools
 			case 'oracle':
 			case 'postgres':
 			case 'sqlite':
+			case' sqlite3':
 				$statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
 			break;
 		}
@@ -2104,6 +2150,7 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
@@ -2182,6 +2229,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 +2273,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 +2365,7 @@ class tools
 				break;
 
 				case 'sqlite':
+				case 'sqlite3':
 					$sql = "PRAGMA index_info('" . $table_name . "');";
 					$col = 'name';
 				break;
@@ -2335,6 +2385,7 @@ class tools
 					case 'oracle':
 					case 'postgres':
 					case 'sqlite':
+					case 'sqlite3':
 						$row[$col] = substr($row[$col], strlen($table_name) + 1);
 					break;
 				}
@@ -2488,6 +2539,7 @@ class tools
 			break;
 
 			case 'sqlite':
+			case 'sqlite3':
 
 				if ($inline && $this->return_statements)
 				{
-- 
cgit v1.2.1


From 07042e484e34115a89985fcd7841c75b1b6135a9 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Mon, 28 Apr 2014 21:47:55 +0200
Subject: [feature/sqlite3] Remove trailing comma from column list

PHPBB3-9728
---
 phpBB/phpbb/db/tools.php | 7 ++++++-
 1 file changed, 6 insertions(+), 1 deletion(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index ddd1ca5957..a462b2fdc7 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2006,7 +2006,12 @@ class tools
 
 				$columns = implode(',', $column_list);
 
-				$new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
+				$new_table_cols = trim(preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols));
+				if (substr($new_table_cols, -1) === ',')
+				{
+					// 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 . ');';
-- 
cgit v1.2.1


From b5267d97f43213269b7b3ae1c153d911cebda385 Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Tue, 29 Apr 2014 14:11:29 +0200
Subject: [feature/sqlite3] Fix sql_index_drop() for sqlite3

PHPBB3-9728
---
 phpBB/phpbb/db/tools.php | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index a462b2fdc7..9b205de7ea 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2048,7 +2048,7 @@ class tools
 			case 'oracle':
 			case 'postgres':
 			case 'sqlite':
-			case' sqlite3':
+			case 'sqlite3':
 				$statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
 			break;
 		}
-- 
cgit v1.2.1


From fd37f6361496bf6c6d0133a0c9a66e7d921d0c7b Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Tue, 29 Apr 2014 15:20:29 +0200
Subject: [feature/sqlite3] Correctly recreate indexes when recreating a table

PHPBB3-9728
---
 phpBB/phpbb/db/tools.php | 217 +++++++++++++++++++++++++++++------------------
 1 file changed, 135 insertions(+), 82 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 9b205de7ea..addb57ac0a 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -1820,68 +1820,63 @@ class tools
 			break;
 
 			case 'sqlite':
-			case 'sqlite3':
-
 				if ($inline && $this->return_statements)
 				{
 					return $column_name . ' ' . $column_data['column_type_sql'];
 				}
 
-				if (version_compare($this->db->sql_server_info(true), '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;
 		}
 
@@ -1966,29 +1961,22 @@ class tools
 					return $column_name;
 				}
 
-				$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, $column_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);
@@ -2015,6 +2003,8 @@ class tools
 
 				// 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';
 
@@ -2162,29 +2152,22 @@ class tools
 					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);
@@ -2204,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';
 
@@ -2551,29 +2536,22 @@ class tools
 					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);
@@ -2591,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';
 
@@ -2758,4 +2738,77 @@ 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}'
+			ORDER BY type DESC, 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}'
+			ORDER BY type DESC, 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;
+	}
 }
-- 
cgit v1.2.1


From ae3586869a7a85109cc5e38eccc862af1f09fa3c Mon Sep 17 00:00:00 2001
From: Joas Schilling <nickvergessen@gmx.de>
Date: Fri, 2 May 2014 11:10:03 +0200
Subject: [feature/sqlite3] Remove unneeded ORDER BY type from sqlite_master
 queries

PHPBB3-9728
---
 phpBB/phpbb/db/tools.php | 6 ++----
 1 file changed, 2 insertions(+), 4 deletions(-)

(limited to 'phpBB/phpbb/db/tools.php')

diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index addb57ac0a..a983ed91b5 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -2755,8 +2755,7 @@ class tools
 		$sql = "SELECT sql
 			FROM sqlite_master
 			WHERE type = 'table'
-				AND name = '{$table_name}'
-			ORDER BY type DESC, name;";
+				AND name = '{$table_name}'";
 		$result = $this->db->sql_query($sql);
 		$sql_create_table = $this->db->sql_fetchfield('sql');
 		$this->db->sql_freeresult($result);
@@ -2770,8 +2769,7 @@ class tools
 		$sql = "SELECT sql
 			FROM sqlite_master
 			WHERE type = 'index'
-				AND tbl_name = '{$table_name}'
-			ORDER BY type DESC, name;";
+				AND tbl_name = '{$table_name}'";
 		$result = $this->db->sql_query($sql);
 		while ($sql_create_index = $this->db->sql_fetchfield('sql'))
 		{
-- 
cgit v1.2.1