4 files changed, 282 insertions, 28 deletions
diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php
index bc6b027b1f..ddc633c6d1 100644
--- a/phpBB/includes/db/db_tools.php
+++ b/phpBB/includes/db/db_tools.php
@@ -347,6 +347,11 @@ class phpbb_db_tools
 		// holds the DDL for a column
 		$columns = $statements = array();
 
+		if ($this->sql_table_exists($table_name))
+		{
+			return $this->_sql_run_sql($statements);
+		}
+
 		// Begin transaction
 		$statements[] = 'begin';
 
@@ -580,6 +585,14 @@ class phpbb_db_tools
 		}
 
 		$statements = array();
+		$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)
+		{
+			$sqlite_data = array();
+			$sqlite = true;
+		}
 
 		// Change columns?
 		if (!empty($schema_changes['change_columns']))
@@ -589,16 +602,27 @@ class phpbb_db_tools
 				foreach ($columns as $column_name => $column_data)
 				{
 					// If the column exists we change it, else we add it ;)
-					if ($this->sql_column_exists($table, $column_name))
+					if ($column_exists = $this->sql_column_exists($table, $column_name))
 					{
-						$result = $this->sql_column_change($table, $column_name, $column_data);
+						$result = $this->sql_column_change($table, $column_name, $column_data, true);
 					}
 					else
 					{
-						$result = $this->sql_column_add($table, $column_name, $column_data);
+						$result = $this->sql_column_add($table, $column_name, $column_data, true);
 					}
 
-					if ($this->return_statements)
+					if ($sqlite)
+					{
+						if ($column_exists)
+						{
+							$sqlite_data[$table]['change_columns'][] = $result;
+						}
+						else
+						{
+							$sqlite_data[$table]['add_columns'][] = $result;
+						}
+					}
+					else if ($this->return_statements)
 					{
 						$statements = array_merge($statements, $result);
 					}
@@ -614,16 +638,27 @@ class phpbb_db_tools
 				foreach ($columns as $column_name => $column_data)
 				{
 					// Only add the column if it does not exist yet, else change it (to be consistent)
-					if ($this->sql_column_exists($table, $column_name))
+					if ($column_exists = $this->sql_column_exists($table, $column_name))
 					{
-						$result = $this->sql_column_change($table, $column_name, $column_data);
+						$result = $this->sql_column_change($table, $column_name, $column_data, true);
 					}
 					else
 					{
-						$result = $this->sql_column_add($table, $column_name, $column_data);
+						$result = $this->sql_column_add($table, $column_name, $column_data, true);
 					}
 
-					if ($this->return_statements)
+					if ($sqlite)
+					{
+						if ($column_exists)
+						{
+							$sqlite_data[$table]['change_columns'][] = $result;
+						}
+						else
+						{
+							$sqlite_data[$table]['add_columns'][] = $result;
+						}
+					}
+					else if ($this->return_statements)
 					{
 						$statements = array_merge($statements, $result);
 					}
@@ -658,9 +693,13 @@ class phpbb_db_tools
 					// Only remove the column if it exists...
 					if ($this->sql_column_exists($table, $column))
 					{
-						$result = $this->sql_column_remove($table, $column);
+						$result = $this->sql_column_remove($table, $column, true);
 
-						if ($this->return_statements)
+						if ($sqlite)
+						{
+							$sqlite_data[$table]['drop_columns'][] = $result;
+						}
+						else if ($this->return_statements)
 						{
 							$statements = array_merge($statements, $result);
 						}
@@ -674,9 +713,13 @@ class phpbb_db_tools
 		{
 			foreach ($schema_changes['add_primary_keys'] as $table => $columns)
 			{
-				$result = $this->sql_create_primary_key($table, $columns);
+				$result = $this->sql_create_primary_key($table, $columns, true);
 
-				if ($this->return_statements)
+				if ($sqlite)
+				{
+					$sqlite_data[$table]['primary_key'] = $result;
+				}
+				else if ($this->return_statements)
 				{
 					$statements = array_merge($statements, $result);
 				}
@@ -717,6 +760,147 @@ class phpbb_db_tools
 			}
 		}
 
+		if ($sqlite)
+		{
+			foreach ($sqlite_data as $table_name => $sql_schema_changes)
+			{
+				// Create temporary table with original data
+				$statements[] = 'begin';
+
+				$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)
+				{
+					continue;
+				}
+
+				$row = $this->db->sql_fetchrow($result);
+				$this->db->sql_freeresult($result);
+
+				// 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;
+
+				// Get the columns...
+				preg_match('#\((.*)\)#s', $row['sql'], $matches);
+
+				$plain_table_cols = trim($matches[1]);
+				$new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
+				$column_list = array();
+
+				foreach ($new_table_cols as $declaration)
+				{
+					$entities = preg_split('#\s+#', trim($declaration));
+					if ($entities[0] == 'PRIMARY')
+					{
+						continue;
+					}
+					$column_list[] = $entities[0];
+				}
+
+				// note down the primary key notation because sqlite only supports adding it to the end for the new table
+				$primary_key = false;
+				$_new_cols = array();
+
+				foreach ($new_table_cols as $key => $declaration)
+				{
+					$entities = preg_split('#\s+#', trim($declaration));
+					if ($entities[0] == 'PRIMARY')
+					{
+						$primary_key = $declaration;
+						continue;
+					}
+					$_new_cols[] = $declaration;
+				}
+
+				$new_table_cols = $_new_cols;
+
+				// First of all... change columns
+				if (!empty($sql_schema_changes['change_columns']))
+				{
+					foreach ($sql_schema_changes['change_columns'] as $column_sql)
+					{
+						foreach ($new_table_cols as $key => $declaration)
+						{
+							$entities = preg_split('#\s+#', trim($declaration));
+							if (strpos($column_sql, $entities[0] . ' ') === 0)
+							{
+								$new_table_cols[$key] = $column_sql;
+							}
+						}
+					}
+				}
+
+				if (!empty($sql_schema_changes['add_columns']))
+				{
+					foreach ($sql_schema_changes['add_columns'] as $column_sql)
+					{
+						$new_table_cols[] = $column_sql;
+					}
+				}
+
+				// Now drop them...
+				if (!empty($sql_schema_changes['drop_columns']))
+				{
+					foreach ($sql_schema_changes['drop_columns'] as $column_name)
+					{
+						// Remove from column list...
+						$new_column_list = array();
+						foreach ($column_list as $key => $value)
+						{
+							if ($value === $column_name)
+							{
+								continue;
+							}
+
+							$new_column_list[] = $value;
+						}
+
+						$column_list = $new_column_list;
+
+						// Remove from table...
+						$_new_cols = array();
+						foreach ($new_table_cols as $key => $declaration)
+						{
+							$entities = preg_split('#\s+#', trim($declaration));
+							if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
+							{
+								continue;
+							}
+							$_new_cols[] = $declaration;
+						}
+						$new_table_cols = $_new_cols;
+					}
+				}
+
+				// Primary key...
+				if (!empty($sql_schema_changes['primary_key']))
+				{
+					$new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
+				}
+				// Add a new one or the old primary key
+				else if ($primary_key !== false)
+				{
+					$new_table_cols[] = $primary_key;
+				}
+
+				$columns = implode(',', $column_list);
+
+				// create a new table and fill it up. destroy the temp one
+				$statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
+				$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
+				$statements[] = 'DROP TABLE ' . $table_name . '_temp';
+
+				$statements[] = 'commit';
+			}
+		}
+
 		if ($this->return_statements)
 		{
 			return $statements;
@@ -801,7 +985,7 @@ class phpbb_db_tools
 			case 'oracle':
 				$sql = "SELECT column_name
 					FROM user_tab_columns
-					WHERE table_name = '{$table}'";
+					WHERE LOWER(table_name) = '" . strtolower($table) . "'";
 				$result = $this->db->sql_query($sql);
 				while ($row = $this->db->sql_fetchrow($result))
 				{
@@ -819,7 +1003,7 @@ class phpbb_db_tools
 			case 'firebird':
 				$sql = "SELECT RDB\$FIELD_NAME as FNAME
 					FROM RDB\$RELATION_FIELDS
-					WHERE RDB\$RELATION_NAME = '{$table}'";
+					WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'";
 				$result = $this->db->sql_query($sql);
 				while ($row = $this->db->sql_fetchrow($result))
 				{
@@ -972,10 +1156,12 @@ class phpbb_db_tools
 		{
 			case 'firebird':
 				$sql .= " {$column_type} ";
+				$return_array['column_type_sql_type'] = " {$column_type} ";
 
 				if (!is_null($column_data[1]))
 				{
 					$sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
+					$return_array['column_type_sql_default'] = ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
 				}
 
 				$sql .= 'NOT NULL';
@@ -1129,7 +1315,7 @@ class phpbb_db_tools
 	/**
 	* Add new column
 	*/
-	function sql_column_add($table_name, $column_name, $column_data)
+	function sql_column_add($table_name, $column_name, $column_data, $inline = false)
 	{
 		$column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 		$statements = array();
@@ -1158,6 +1344,12 @@ class phpbb_db_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)
 				{
 					$sql = "SELECT sql
@@ -1222,7 +1414,7 @@ class phpbb_db_tools
 	/**
 	* Drop column
 	*/
-	function sql_column_remove($table_name, $column_name)
+	function sql_column_remove($table_name, $column_name, $inline = false)
 	{
 		$statements = array();
 
@@ -1250,6 +1442,12 @@ class phpbb_db_tools
 			break;
 
 			case 'sqlite':
+
+				if ($inline && $this->return_statements)
+				{
+					return $column_name;
+				}
+
 				if (version_compare(sqlite_libversion(), '3.0') == -1)
 				{
 					$sql = "SELECT sql
@@ -1292,7 +1490,7 @@ class phpbb_db_tools
 
 					$columns = implode(',', $column_list);
 
-					$new_table_cols = $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 . ');';
@@ -1347,6 +1545,11 @@ class phpbb_db_tools
 	{
 		$statements = array();
 
+		if (!$this->sql_table_exists($table_name))
+		{
+			return $this->_sql_run_sql($statements);
+		}
+
 		// the most basic operation, get rid of the table
 		$statements[] = 'DROP TABLE ' . $table_name;
 
@@ -1405,7 +1608,7 @@ class phpbb_db_tools
 	/**
 	* Add primary key
 	*/
-	function sql_create_primary_key($table_name, $column)
+	function sql_create_primary_key($table_name, $column, $inline = false)
 	{
 		$statements = array();
 
@@ -1432,6 +1635,12 @@ class phpbb_db_tools
 			break;
 
 			case 'sqlite':
+
+				if ($inline && $this->return_statements)
+				{
+					return $column;
+				}
+
 				$sql = "SELECT sql
 					FROM sqlite_master
 					WHERE type = 'table'
@@ -1639,7 +1848,7 @@ class phpbb_db_tools
 	/**
 	* Change column type (not name!)
 	*/
-	function sql_column_change($table_name, $column_name, $column_data)
+	function sql_column_change($table_name, $column_name, $column_data, $inline = false)
 	{
 		$column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 		$statements = array();
@@ -1648,7 +1857,15 @@ class phpbb_db_tools
 		{
 			case 'firebird':
 				// Change type...
-				$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql'];
+				if (!empty($column_data['column_type_sql_default']))
+				{
+					$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
+					$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" SET DEFAULT ' . ' ' . $column_data['column_type_sql_default'];
+				}
+				else
+				{
+					$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql'];
+				}
 			break;
 
 			case 'mssql':
@@ -1729,6 +1946,11 @@ class phpbb_db_tools
 
 			case 'sqlite':
 
+				if ($inline && $this->return_statements)
+				{
+					return $column_name . ' ' . $column_data['column_type_sql'];
+				}
+
 				$sql = "SELECT sql
 					FROM sqlite_master
 					WHERE type = 'table'
diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php
index b530a572da..a962696bb8 100644
--- a/phpBB/includes/db/dbal.php
+++ b/phpBB/includes/db/dbal.php
@@ -235,8 +235,8 @@ class dbal
 	*/
 	function sql_like_expression($expression)
 	{
-		$expression = str_replace(array('_', '%'), array("\_", "\%"), $expression);
-		$expression = str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
+		$expression = utf8_str_replace(array('_', '%'), array("\_", "\%"), $expression);
+		$expression = utf8_str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
 
 		return $this->_sql_like_expression('LIKE \'' . $this->sql_escape($expression) . '\'');
 	}
@@ -412,6 +412,24 @@ class dbal
 	}
 
 	/**
+	* Run binary AND operator on DB column.
+	* Results in sql statement: "{$column_name} & (1 << {$bit}) {$compare}"
+	*
+	* @param string $column_name The column name to use
+	* @param int $bit The value to use for the AND operator, will be converted to (1 << $bit). Is used by options, using the number schema... 0, 1, 2...29
+	* @param string $compare Any custom SQL code after the check (for example "= 0")
+	*/
+	function sql_bit_and($column_name, $bit, $compare = '')
+	{
+		if (method_exists($this, '_sql_bit_and'))
+		{
+			return $this->_sql_bit_and($column_name, $bit, $compare);
+		}
+
+		return $column_name . ' & ' . (1 << $bit) . (($compare) ? ' ' . $compare : '');
+	}
+
+	/**
 	* Run more than one insert statement.
 	*
 	* @param string $table table name to run the statements on
@@ -435,8 +453,7 @@ class dbal
 				// If by accident the sql array is only one-dimensional we build a normal insert statement
 				if (!is_array($_sql_ary))
 				{
-					$this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $sql_ary));
-					return true;
+					return $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $sql_ary));
 				}
 
 				$values = array();
@@ -447,7 +464,7 @@ class dbal
 				$ary[] = '(' . implode(', ', $values) . ')';
 			}
 
-			$this->sql_query('INSERT INTO ' . $table . ' ' . ' (' . implode(', ', array_keys($sql_ary[0])) . ') VALUES ' . implode(', ', $ary));
+			return $this->sql_query('INSERT INTO ' . $table . ' ' . ' (' . implode(', ', array_keys($sql_ary[0])) . ') VALUES ' . implode(', ', $ary));
 		}
 		else
 		{
@@ -458,7 +475,12 @@ class dbal
 					return false;
 				}
 
-				$this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary));
+				$result = $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary));
+
+				if (!$result)
+				{
+					return false;
+				}
 			}
 		}
 
diff --git a/phpBB/includes/db/firebird.php b/phpBB/includes/db/firebird.php
index 3b31942a8b..fb1ef44c55 100644
--- a/phpBB/includes/db/firebird.php
+++ b/phpBB/includes/db/firebird.php
@@ -20,7 +20,7 @@ include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
 
 /**
 * Firebird/Interbase Database Abstraction Layer
-* Minimum Requirement is Firebird 2.0
+* Minimum Requirement is Firebird 2.1
 * @package dbal
 */
 class dbal_firebird extends dbal
@@ -72,7 +72,7 @@ class dbal_firebird extends dbal
 			return @ibase_server_info($this->service_handle, IBASE_SVC_SERVER_VERSION);
 		}
 
-		return ($raw) ? '2.0' : 'Firebird/Interbase';
+		return ($raw) ? '2.1' : 'Firebird/Interbase';
 	}
 
 	/**
@@ -446,6 +446,11 @@ class dbal_firebird extends dbal
 		return $data;
 	}
 
+	function _sql_bit_and($column_name, $bit, $compare = '')
+	{
+		return 'BIN_AND(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : '');
+	}
+
 	/**
 	* return sql error array
 	* @access private
diff --git a/phpBB/includes/db/oracle.php b/phpBB/includes/db/oracle.php
index a140c4b676..10f4a1a7a7 100644
--- a/phpBB/includes/db/oracle.php
+++ b/phpBB/includes/db/oracle.php
@@ -568,6 +568,11 @@ class dbal_oracle extends dbal
 		return $data;
 	}
 
+	function _sql_bit_and($column_name, $bit, $compare = '')
+	{
+		return 'BITAND(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : '');
+	}
+
 	/**
 	* return sql error array
 	* @access private