8 files changed, 74 insertions, 46 deletions
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index aca3b9f1c4..eb31c05fc9 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -401,7 +401,7 @@ $database_update_info = array(
 				'template_id'		=> array('USINT', NULL, 'auto_increment'),
 			),
 			STYLES_TEMPLATE_DATA_TABLE	=> array(
-				'template_id'		=> array('USINT', NULL, 'auto_increment'),
+				'template_id'		=> array('USINT', 0),
 			),
 			STYLES_THEME_TABLE			=> array(
 				'theme_id'			=> array('USINT', NULL, 'auto_increment'),
@@ -1297,7 +1297,6 @@ if (version_compare($current_version, '3.0.RC4', '<='))
 	$update_auto_increment = array(
 		STYLES_TABLE				=> 'style_id',
 		STYLES_TEMPLATE_TABLE		=> 'template_id',
-		STYLES_TEMPLATE_DATA_TABLE	=> 'template_id',
 		STYLES_THEME_TABLE			=> 'theme_id',
 		STYLES_IMAGESET_TABLE		=> 'imageset_id'
 	);
@@ -1413,22 +1412,76 @@ if (version_compare($current_version, '3.0.RC4', '<='))
 
 		$no_updates = false;
 	}
-	else if ($map_dbms == 'sqlite')
+	else if ($map_dbms == 'postgres')
 	{
 		foreach ($update_auto_increment as $auto_table_name => $auto_column_name)
 		{
-			sql_column_change($dbms, $auto_table_name, $auto_column_name, array('USINT', NULL, 'auto_increment'));
+			$sql = "SELECT SETVAL('" . $auto_table_name . "_seq',(select case when max({$auto_column_name})>0 then max({$auto_column_name})+1 else 1 end from " . $auto_table_name . '));';
+			_sql($sql, $errored, $error_ary);
 		}
 
-		$no_updates = false;
+		$sql = 'DROP SEQUENCE ' . STYLES_TEMPLATE_DATA_TABLE . '_seq';
+		_sql($sql, $errored, $error_ary);
 	}
-	else if ($map_dbms == 'postgres')
+	else if ($map_dbms == 'firebird')
 	{
-		foreach ($update_auto_increment as $auto_table_name => $auto_column_name)
+		$sql = 'DROP TRIGGER t_' . STYLES_TEMPLATE_DATA_TABLE;
+		_sql($sql, $errored, $error_ary);
+
+		$sql = 'DROP GENERATOR ' . STYLES_TEMPLATE_DATA_TABLE . '_gen';
+		_sql($sql, $errored, $error_ary);
+	}
+	else if ($map_dbms == 'oracle')
+	{
+		$sql = 'DROP TRIGGER t_' . STYLES_TEMPLATE_DATA_TABLE;
+		_sql($sql, $errored, $error_ary);
+
+		$sql = 'DROP SEQUENCE ' . STYLES_TEMPLATE_DATA_TABLE . '_seq';
+		_sql($sql, $errored, $error_ary);
+	}
+	else if ($map_dbms == 'mssql')
+	{
+		// we use transactions because we need to have a working DB at the end of all of this
+		$db->sql_transaction('begin');
+
+		$sql = 'SELECT *
+			FROM ' . STYLES_TEMPLATE_TABLE;
+		$result = _sql($sql, $errored, $error_ary);
+		$old_style_rows = array();
+		while ($row = $db->sql_fetchrow($result))
 		{
-			$sql = "SELECT SETVAL('" . $auto_table_name . "_seq',(select case when max({$auto_column_name})>0 then max({$auto_column_name})+1 else 1 end from " . $auto_table_name . '));';
-			_sql($sql, $errored, $error_ary);
+			$old_style_rows[] = $row;
+		}
+		$db->sql_freeresult($result);
+
+		// death to the table, it is evil!
+		$sql = 'DROP TABLE ' . STYLES_TEMPLATE_DATA_TABLE;
+		_sql($sql, $errored, $error_ary);
+
+		// the table of awesomeness, praise be to it (or something)
+		$sql = 'CREATE TABLE [' . STYLES_TEMPLATE_DATA_TABLE . "] (
+			[template_id] [int] DEFAULT (0) NOT NULL ,
+			[template_filename] [varchar] (100) DEFAULT ('') NOT NULL ,
+			[template_included] [varchar] (8000) DEFAULT ('') NOT NULL ,
+			[template_mtime] [int] DEFAULT (0) NOT NULL ,
+			[template_data] [text] DEFAULT ('') NOT NULL 
+		) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
+		_sql($sql, $errored, $error_ary);
+
+		// index? index
+		$sql = 'CREATE  INDEX [tid] ON [' . STYLES_TEMPLATE_DATA_TABLE . ']([template_id]) ON [PRIMARY]';
+		_sql($sql, $errored, $error_ary);
+
+		// yet another index
+		$sql = 'CREATE  INDEX [tfn] ON [' . STYLES_TEMPLATE_DATA_TABLE . ']([template_filename]) ON [PRIMARY]';
+		_sql($sql, $errored, $error_ary);
+
+		foreach ($old_style_rows as $return_row)
+		{
+			_sql('INSERT INTO ' . STYLES_TEMPLATE_DATA_TABLE . '(' . implode(', ', array_keys($return_row)) . ') VALUES (' . implode(', ', $return_row) . ')');
 		}
+
+		$db->sql_transaction('commit');
 	}
 }
 
@@ -1882,7 +1935,11 @@ function prepare_column_data($dbms, $column_data, $table_name, $column_name)
 
 			// In Oracle empty strings ('') are treated as NULL.
 			// Therefore in oracle we allow NULL's for all DEFAULT '' entries
-			$sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
+			// Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
+			if (preg_match('/number/i', $column_type))
+			{
+				$sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
+			}
 		break;
 
 		case 'postgres':
diff --git a/phpBB/install/schemas/firebird_schema.sql b/phpBB/install/schemas/firebird_schema.sql
index d0f9cf88d1..38ef13c412 100644
--- a/phpBB/install/schemas/firebird_schema.sql
+++ b/phpBB/install/schemas/firebird_schema.sql
@@ -1093,7 +1093,7 @@ END;;
 
 # Table: 'phpbb_styles_template_data'
 CREATE TABLE phpbb_styles_template_data (
-	template_id INTEGER NOT NULL,
+	template_id INTEGER DEFAULT 0 NOT NULL,
 	template_filename VARCHAR(100) CHARACTER SET NONE DEFAULT '' NOT NULL,
 	template_included BLOB SUB_TYPE TEXT CHARACTER SET NONE DEFAULT '' NOT NULL,
 	template_mtime INTEGER DEFAULT 0 NOT NULL,
@@ -1103,17 +1103,6 @@ CREATE TABLE phpbb_styles_template_data (
 CREATE INDEX phpbb_styles_template_data_tid ON phpbb_styles_template_data(template_id);;
 CREATE INDEX phpbb_styles_template_data_tfn ON phpbb_styles_template_data(template_filename);;
 
-CREATE GENERATOR phpbb_styles_template_data_gen;;
-SET GENERATOR phpbb_styles_template_data_gen TO 0;;
-
-CREATE TRIGGER t_phpbb_styles_template_data FOR phpbb_styles_template_data
-BEFORE INSERT
-AS
-BEGIN
-	NEW.template_id = GEN_ID(phpbb_styles_template_data_gen, 1);
-END;;
-
-
 # Table: 'phpbb_styles_theme'
 CREATE TABLE phpbb_styles_theme (
 	theme_id INTEGER NOT NULL,
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 7dd9c4e924..37e8f66b2a 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -1309,7 +1309,7 @@ GO
 	Table: 'phpbb_styles_template_data'
 */
 CREATE TABLE [phpbb_styles_template_data] (
-	[template_id] [int] IDENTITY (1, 1) NOT NULL ,
+	[template_id] [int] DEFAULT (0) NOT NULL ,
 	[template_filename] [varchar] (100) DEFAULT ('') NOT NULL ,
 	[template_included] [varchar] (8000) DEFAULT ('') NOT NULL ,
 	[template_mtime] [int] DEFAULT (0) NOT NULL ,
diff --git a/phpBB/install/schemas/mysql_40_schema.sql b/phpBB/install/schemas/mysql_40_schema.sql
index e2dc0718e7..a1450cacee 100644
--- a/phpBB/install/schemas/mysql_40_schema.sql
+++ b/phpBB/install/schemas/mysql_40_schema.sql
@@ -743,7 +743,7 @@ CREATE TABLE phpbb_styles_template (
 
 # Table: 'phpbb_styles_template_data'
 CREATE TABLE phpbb_styles_template_data (
-	template_id smallint(4) UNSIGNED NOT NULL auto_increment,
+	template_id smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
 	template_filename varbinary(100) DEFAULT '' NOT NULL,
 	template_included blob NOT NULL,
 	template_mtime int(11) UNSIGNED DEFAULT '0' NOT NULL,
diff --git a/phpBB/install/schemas/mysql_41_schema.sql b/phpBB/install/schemas/mysql_41_schema.sql
index 022c3ecbfe..4cee662be6 100644
--- a/phpBB/install/schemas/mysql_41_schema.sql
+++ b/phpBB/install/schemas/mysql_41_schema.sql
@@ -743,7 +743,7 @@ CREATE TABLE phpbb_styles_template (
 
 # Table: 'phpbb_styles_template_data'
 CREATE TABLE phpbb_styles_template_data (
-	template_id smallint(4) UNSIGNED NOT NULL auto_increment,
+	template_id smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
 	template_filename varchar(100) DEFAULT '' NOT NULL,
 	template_included text NOT NULL,
 	template_mtime int(11) UNSIGNED DEFAULT '0' NOT NULL,
diff --git a/phpBB/install/schemas/oracle_schema.sql b/phpBB/install/schemas/oracle_schema.sql
index 7dad10dd67..aabc00f0e2 100644
--- a/phpBB/install/schemas/oracle_schema.sql
+++ b/phpBB/install/schemas/oracle_schema.sql
@@ -1453,7 +1453,7 @@ END;
 	Table: 'phpbb_styles_template_data'
 */
 CREATE TABLE phpbb_styles_template_data (
-	template_id number(4) NOT NULL,
+	template_id number(4) DEFAULT '0' NOT NULL,
 	template_filename varchar2(100) DEFAULT '' ,
 	template_included clob DEFAULT '' ,
 	template_mtime number(11) DEFAULT '0' NOT NULL,
@@ -1466,22 +1466,6 @@ CREATE INDEX phpbb_styles_template_data_tid ON phpbb_styles_template_data (templ
 CREATE INDEX phpbb_styles_template_data_tfn ON phpbb_styles_template_data (template_filename)
 /
 
-CREATE SEQUENCE phpbb_styles_template_data_seq
-/
-
-CREATE OR REPLACE TRIGGER t_phpbb_styles_template_data
-BEFORE INSERT ON phpbb_styles_template_data
-FOR EACH ROW WHEN (
-	new.template_id IS NULL OR new.template_id = 0
-)
-BEGIN
-	SELECT phpbb_styles_template_data_seq.nextval
-	INTO :new.template_id
-	FROM dual;
-END;
-/
-
-
 /*
 	Table: 'phpbb_styles_theme'
 */
diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index f9b0557d8a..5d4c6f477b 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -977,10 +977,8 @@ CREATE UNIQUE INDEX phpbb_styles_template_tmplte_nm ON phpbb_styles_template (te
 /*
 	Table: 'phpbb_styles_template_data'
 */
-CREATE SEQUENCE phpbb_styles_template_data_seq;
-
 CREATE TABLE phpbb_styles_template_data (
-	template_id INT2 DEFAULT nextval('phpbb_styles_template_data_seq'),
+	template_id INT2 DEFAULT '0' NOT NULL CHECK (template_id >= 0),
 	template_filename varchar(100) DEFAULT '' NOT NULL,
 	template_included varchar(8000) DEFAULT '' NOT NULL,
 	template_mtime INT4 DEFAULT '0' NOT NULL CHECK (template_mtime >= 0),
diff --git a/phpBB/install/schemas/sqlite_schema.sql b/phpBB/install/schemas/sqlite_schema.sql
index 90847e9d31..546cfb0321 100644
--- a/phpBB/install/schemas/sqlite_schema.sql
+++ b/phpBB/install/schemas/sqlite_schema.sql
@@ -717,7 +717,7 @@ CREATE UNIQUE INDEX phpbb_styles_template_tmplte_nm ON phpbb_styles_template (te
 
 # Table: 'phpbb_styles_template_data'
 CREATE TABLE phpbb_styles_template_data (
-	template_id INTEGER PRIMARY KEY NOT NULL ,
+	template_id INTEGER UNSIGNED NOT NULL DEFAULT '0',
 	template_filename varchar(100) NOT NULL DEFAULT '',
 	template_included text(65535) NOT NULL DEFAULT '',
 	template_mtime INTEGER UNSIGNED NOT NULL DEFAULT '0',