[ticket/9725] Add dummy indexes for Azure

SQL Azure requires a primary AKA clustered key on every table. This adds a dummy INT column to act as key, avoiding possible duplicate entries. so that these columns can support bigger (speak: huge) tables PHPBB3-9725
author: Henry Sudhof <kellanved@phpbb.com> 2012-04-12 18:39:40 +0200
committer: Patrick Webster <noxwizard@phpbb.com> 2014-03-01 21:03:25 -0600
commit: b993fa54a5d8902590d0d6a83bbe1c581d043f46 (patch)
tree: 0ee0fc0e4165c8dbe3e3c5ab9f927823f4c63f9c
parent: fd4f973440baf51b92de61cd067268f1881a9a30 (diff)
download: forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar
forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.gz
forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.bz2
forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.xz
forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.zip
2 files changed, 56 insertions, 56 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index 23ef2f6c74..b869c507e5 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -237,6 +237,19 @@ $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', '
 
 foreach ($supported_dbms as $dbms)
 {
+	$schema_data = get_schema_struct();
+	if ($dbms == 'mssql')
+	{
+		foreach ($schema_data as $table_name => $table_data)
+		{
+			if (!isset($table_data['PRIMARY_KEY']))
+			{
+				$schema_data[$table_name]['COLUMNS']['mssqlindex'] = array('UINT', NULL, 'auto_increment');
+				$schema_data[$table_name]['PRIMARY_KEY'] = 'mssqlindex';
+			}
+		}
+	}
+
 	$fp = fopen($schema_path . $dbms . '_schema.sql', 'wb');
 
 	$line = '';
@@ -863,9 +876,8 @@ function get_schema_struct()
 		'KEYS'			=> array(
 			'group_id'		=> array('INDEX', 'group_id'),
 			'auth_opt_id'	=> array('INDEX', 'auth_option_id'),
-			'auth_role_id'	=> array('INDEX', 'auth_role_id', 'auth_option_id'),
+			'auth_role_id'	=> array('INDEX', 'auth_role_id'),
 		),
-		'PRIMARY_KEY'	=> array('group_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'),
 	);
 
 	$schema_data['phpbb_acl_options'] = array(
@@ -922,7 +934,6 @@ function get_schema_struct()
 			'auth_option_id'	=> array('INDEX', 'auth_option_id'),
 			'auth_role_id'		=> array('INDEX', 'auth_role_id'),
 		),
-		'PRIMARY_KEY'	=> array('user_id', 'forum_id', 'auth_option_id', 'auth_role_id', 'auth_setting'),
 	);
 
 	$schema_data['phpbb_banlist'] = array(
@@ -1144,7 +1155,6 @@ function get_schema_struct()
 			'user_id'				=> array('INDEX', 'user_id'),
 			'notify_stat'			=> array('INDEX', 'notify_status'),
 		),
-		'PRIMARY_KEY'	=> array('forum_id', 'user_id'),
 	);
 
 	$schema_data['phpbb_groups'] = array(
@@ -1261,8 +1271,6 @@ function get_schema_struct()
 			'disp_idx'				=> array('INDEX', 'display_on_index'),
 			'forum_id'				=> array('INDEX', 'forum_id'),
 		),
-		'PRIMARY_KEY'	=> array('forum_id', 'user_id', 'group_id'),
-
 	);
 
 	$schema_data['phpbb_modules'] = array(
@@ -1298,7 +1306,6 @@ function get_schema_struct()
 			'poll_opt_id'			=> array('INDEX', 'poll_option_id'),
 			'topic_id'				=> array('INDEX', 'topic_id'),
 		),
-		'PRIMARY_KEY'	=> array('topic_id', 'poll_option_id'),
 	);
 
 	$schema_data['phpbb_poll_votes'] = array(
@@ -1313,7 +1320,6 @@ function get_schema_struct()
 			'vote_user_id'			=> array('INDEX', 'vote_user_id'),
 			'vote_user_ip'			=> array('INDEX', 'vote_user_ip'),
 		),
-		'PRIMARY_KEY'	=> array('topic_id', 'poll_option_id', 'vote_user_id'),
 	);
 
 	$schema_data['phpbb_posts'] = array(
@@ -1440,7 +1446,6 @@ function get_schema_struct()
 			'author_id'				=> array('INDEX', 'author_id'),
 			'usr_flder_id'			=> array('INDEX', array('user_id', 'folder_id')),
 		),
-'PRIMARY_KEY'	=> array('msg_id', 'user_id', 'author_id'),
 	);
 
 	$schema_data['phpbb_profile_fields'] = array(
@@ -1576,7 +1581,6 @@ function get_schema_struct()
 			'word_id'			=> array('INDEX', 'word_id'),
 			'post_id'			=> array('INDEX', 'post_id'),
 		),
-	'PRIMARY_KEY'	=> array('post_id', 'word_id'),
 	);
 
 	$schema_data['phpbb_sessions'] = array(
@@ -1692,7 +1696,6 @@ function get_schema_struct()
 			'tid'					=> array('INDEX', 'template_id'),
 			'tfn'					=> array('INDEX', 'template_filename'),
 		),
-		'PRIMARY_KEY'	=> array('template_id', 'template_filename'),
 	);
 
 	$schema_data['phpbb_styles_theme'] = array(
@@ -1822,7 +1825,6 @@ function get_schema_struct()
 			'user_id'			=> array('INDEX', 'user_id'),
 			'notify_stat'		=> array('INDEX', 'notify_status'),
 		),
-		'PRIMARY_KEY'	=> array('topic_id', 'user_id'),
 	);
 
 	$schema_data['phpbb_user_group'] = array(
@@ -1837,7 +1839,6 @@ function get_schema_struct()
 			'user_id'			=> array('INDEX', 'user_id'),
 			'group_leader'		=> array('INDEX', 'group_leader'),
 		),
-		'PRIMARY_KEY'	=> array('group_id', 'user_id'),
 	);
 
 	$schema_data['phpbb_users'] = array(
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 5397480280..b4b378147b 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -58,17 +58,14 @@ CREATE TABLE [phpbb_acl_groups] (
 	[forum_id] [int] DEFAULT (0) NOT NULL ,
 	[auth_option_id] [int] DEFAULT (0) NOT NULL ,
 	[auth_role_id] [int] DEFAULT (0) NOT NULL ,
-	[auth_setting] [int] DEFAULT (0) NOT NULL 
+	[auth_setting] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_acl_groups] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_acl_groups] PRIMARY KEY  CLUSTERED 
 	(
-		[group_id],
-		[forum_id],
-		[auth_option_id],
-		[auth_role_id],
-		[auth_setting]
+		[mssqlindex]
 	) 
 GO
 
@@ -158,17 +155,14 @@ CREATE TABLE [phpbb_acl_users] (
 	[forum_id] [int] DEFAULT (0) NOT NULL ,
 	[auth_option_id] [int] DEFAULT (0) NOT NULL ,
 	[auth_role_id] [int] DEFAULT (0) NOT NULL ,
-	[auth_setting] [int] DEFAULT (0) NOT NULL 
+	[auth_setting] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_acl_users] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_acl_users] PRIMARY KEY  CLUSTERED 
 	(
-		[user_id],
-		[forum_id],
-		[auth_option_id],
-		[auth_role_id],
-		[auth_setting]
+		[mssqlindex]
 	) 
 GO
 
@@ -513,14 +507,14 @@ GO
 CREATE TABLE [phpbb_forums_watch] (
 	[forum_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
-	[notify_status] [int] DEFAULT (0) NOT NULL 
+	[notify_status] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_forums_watch] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_forums_watch] PRIMARY KEY  CLUSTERED 
 	(
-		[forum_id],
-		[user_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -667,9 +661,17 @@ CREATE TABLE [phpbb_login_attempts] (
 	[attempt_time] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
 	[username] [varchar] (255) DEFAULT (0) NOT NULL ,
-	[username_clean] [varchar] (255) DEFAULT (0) NOT NULL 
+	[username_clean] [varchar] (255) DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
+ALTER TABLE [phpbb_login_attempts] WITH NOCHECK ADD 
+	CONSTRAINT [PK_phpbb_login_attempts] PRIMARY KEY  CLUSTERED 
+	(
+		[mssqlindex]
+	) 
+GO
+
 CREATE  INDEX [att_ip] ON [phpbb_login_attempts]([attempt_ip], [attempt_time]) 
 GO
 
@@ -692,15 +694,14 @@ CREATE TABLE [phpbb_moderator_cache] (
 	[username] [varchar] (255) DEFAULT ('') NOT NULL ,
 	[group_id] [int] DEFAULT (0) NOT NULL ,
 	[group_name] [varchar] (255) DEFAULT ('') NOT NULL ,
-	[display_on_index] [int] DEFAULT (1) NOT NULL 
+	[display_on_index] [int] DEFAULT (1) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_moderator_cache] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_moderator_cache] PRIMARY KEY  CLUSTERED 
 	(
-		[forum_id],
-		[user_id],
-		[group_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -752,14 +753,14 @@ CREATE TABLE [phpbb_poll_options] (
 	[poll_option_id] [int] DEFAULT (0) NOT NULL ,
 	[topic_id] [int] DEFAULT (0) NOT NULL ,
 	[poll_option_text] [varchar] (4000) DEFAULT ('') NOT NULL ,
-	[poll_option_total] [int] DEFAULT (0) NOT NULL 
+	[poll_option_total] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_poll_options] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_poll_options] PRIMARY KEY  CLUSTERED 
 	(
-		[topic_id],
-		[poll_option_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -777,15 +778,14 @@ CREATE TABLE [phpbb_poll_votes] (
 	[topic_id] [int] DEFAULT (0) NOT NULL ,
 	[poll_option_id] [int] DEFAULT (0) NOT NULL ,
 	[vote_user_id] [int] DEFAULT (0) NOT NULL ,
-	[vote_user_ip] [varchar] (40) DEFAULT ('') NOT NULL 
+	[vote_user_ip] [varchar] (40) DEFAULT ('') NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_poll_votes] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_poll_votes] PRIMARY KEY  CLUSTERED 
 	(
-		[topic_id],
-		[poll_option_id],
-		[vote_user_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -968,15 +968,14 @@ CREATE TABLE [phpbb_privmsgs_to] (
 	[pm_replied] [int] DEFAULT (0) NOT NULL ,
 	[pm_marked] [int] DEFAULT (0) NOT NULL ,
 	[pm_forwarded] [int] DEFAULT (0) NOT NULL ,
-	[folder_id] [int] DEFAULT (0) NOT NULL 
+	[folder_id] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_privmsgs_to] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_privmsgs_to] PRIMARY KEY  CLUSTERED 
 	(
-		[msg_id],
-		[user_id],
-		[author_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -1199,14 +1198,14 @@ GO
 CREATE TABLE [phpbb_search_wordmatch] (
 	[post_id] [int] DEFAULT (0) NOT NULL ,
 	[word_id] [int] DEFAULT (0) NOT NULL ,
-	[title_match] [int] DEFAULT (0) NOT NULL 
+	[title_match] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_search_wordmatch] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_search_wordmatch] PRIMARY KEY  CLUSTERED 
 	(
-		[post_id],
-		[word_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -1387,14 +1386,14 @@ CREATE TABLE [phpbb_styles_template_data] (
 	[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 
+	[template_data] [text] DEFAULT ('') NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_styles_template_data] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_styles_template_data] PRIMARY KEY  CLUSTERED 
 	(
-		[template_id],
-		[template_filename]
+		[mssqlindex]
 	) 
 GO
 
@@ -1589,14 +1588,14 @@ GO
 CREATE TABLE [phpbb_topics_watch] (
 	[topic_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
-	[notify_status] [int] DEFAULT (0) NOT NULL 
+	[notify_status] [int] DEFAULT (0) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_topics_watch] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_topics_watch] PRIMARY KEY  CLUSTERED 
 	(
-		[topic_id],
-		[user_id]
+		[mssqlindex]
 	) 
 GO
 
@@ -1617,14 +1616,14 @@ CREATE TABLE [phpbb_user_group] (
 	[group_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
 	[group_leader] [int] DEFAULT (0) NOT NULL ,
-	[user_pending] [int] DEFAULT (1) NOT NULL 
+	[user_pending] [int] DEFAULT (1) NOT NULL ,
+	[mssqlindex] [int] IDENTITY (1, 1) NOT NULL 
 )GO
 
 ALTER TABLE [phpbb_user_group] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_user_group] PRIMARY KEY  CLUSTERED 
 	(
-		[group_id],
-		[user_id]
+		[mssqlindex]
 	) 
 GO
author	Henry Sudhof <kellanved@phpbb.com>	2012-04-12 18:39:40 +0200
committer	Patrick Webster <noxwizard@phpbb.com>	2014-03-01 21:03:25 -0600
commit	b993fa54a5d8902590d0d6a83bbe1c581d043f46 (patch)
tree	0ee0fc0e4165c8dbe3e3c5ab9f927823f4c63f9c
parent	fd4f973440baf51b92de61cd067268f1881a9a30 (diff)
download	forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.gz forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.bz2 forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.tar.xz forums-b993fa54a5d8902590d0d6a83bbe1c581d043f46.zip