+ $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';

+ }

+ }

+ }

+

+ $line .= "\n)";// ON [PRIMARY]" . (($textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '') . "\n";

+ $line .= "\t)\n";

+ $line .= " [{$key_name}] ON [{$table_name}]([" . implode('], [', $key_data[1]) . "])\n";

+ if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')

+ {

+ if (!isset($table_data['PRIMARY_KEY']))

+ {

+ $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment');

+ $table_data['PRIMARY_KEY'] = 'mssqlindex';

+ }

+ }

+

+ $table_sql .= "\n);";

+ $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'] . ']';

+ }

+ }

+

+ $sql .= ')';

+ $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';

+ $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';

+ $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);

+

+ 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)";

+ }

+ $sql = 'SELECT @@VERSION AS mssql_version';

+ $result = $db->sql_query($sql);

+ $row = $db->sql_fetchrow($result);

+ $db->sql_freeresult($result);

+

+

+ if ($row)

+ {

+ // Azure stats are stored elsewhere

+ if (strpos($row['mssql_version'], 'SQL Azure') !== false)

+ {

+ $sql = 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize

+ FROM sys.dm_db_partition_stats';

+ }

+ }

+

+)GO

+ )

+CREATE INDEX [filetime] ON [phpbb_attachments]([filetime])

+CREATE INDEX [post_msg_id] ON [phpbb_attachments]([post_msg_id])

+CREATE INDEX [topic_id] ON [phpbb_attachments]([topic_id])

+CREATE INDEX [poster_id] ON [phpbb_attachments]([poster_id])

+CREATE INDEX [is_orphan] ON [phpbb_attachments]([is_orphan])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [group_id] ON [phpbb_acl_groups]([group_id])

+CREATE INDEX [auth_opt_id] ON [phpbb_acl_groups]([auth_option_id])

+CREATE INDEX [auth_role_id] ON [phpbb_acl_groups]([auth_role_id])

+)GO

+ )

+CREATE UNIQUE INDEX [auth_option] ON [phpbb_acl_options]([auth_option])

+)GO

+ )

+CREATE INDEX [role_type] ON [phpbb_acl_roles]([role_type])

+CREATE INDEX [role_order] ON [phpbb_acl_roles]([role_order])

+)GO

+ )

+CREATE INDEX [ath_op_id] ON [phpbb_acl_roles_data]([auth_option_id])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [user_id] ON [phpbb_acl_users]([user_id])

+CREATE INDEX [auth_option_id] ON [phpbb_acl_users]([auth_option_id])

+CREATE INDEX [auth_role_id] ON [phpbb_acl_users]([auth_role_id])

+)GO

+ )

+CREATE INDEX [ban_end] ON [phpbb_banlist]([ban_end])

+CREATE INDEX [ban_user] ON [phpbb_banlist]([ban_userid], [ban_exclude])

+CREATE INDEX [ban_email] ON [phpbb_banlist]([ban_email], [ban_exclude])

+CREATE INDEX [ban_ip] ON [phpbb_banlist]([ban_ip], [ban_exclude])

+)GO

+ )

+CREATE INDEX [display_on_post] ON [phpbb_bbcodes]([display_on_posting])

+)GO

+ )

+)GO

+ )

+CREATE INDEX [bot_active] ON [phpbb_bots]([bot_active])

+)GO

+ )

+CREATE INDEX [is_dynamic] ON [phpbb_config]([is_dynamic])

+)GO

+ )

+CREATE INDEX [confirm_type] ON [phpbb_confirm]([confirm_type])

+)GO

+ )

+)GO

+ )

+CREATE INDEX [save_time] ON [phpbb_drafts]([save_time])

+)GO

+ )

+)GO

+ )

+)GO

+ )

+CREATE INDEX [left_right_id] ON [phpbb_forums]([left_id], [right_id])

+CREATE INDEX [forum_lastpost_id] ON [phpbb_forums]([forum_last_post_id])

+)GO

+ )

+)GO

+ )

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [forum_id] ON [phpbb_forums_watch]([forum_id])

+CREATE INDEX [user_id] ON [phpbb_forums_watch]([user_id])

+CREATE INDEX [notify_stat] ON [phpbb_forums_watch]([notify_status])

+)GO

+ )

+CREATE INDEX [group_legend_name] ON [phpbb_groups]([group_legend], [group_name])

+)GO

+ )

+CREATE INDEX [display_on_posting] ON [phpbb_icons]([display_on_posting])

+)GO

+ )

+CREATE INDEX [lang_iso] ON [phpbb_lang]([lang_iso])

+)GO

+ )

+CREATE INDEX [log_type] ON [phpbb_log]([log_type])

+CREATE INDEX [forum_id] ON [phpbb_log]([forum_id])

+CREATE INDEX [topic_id] ON [phpbb_log]([topic_id])

+CREATE INDEX [reportee_id] ON [phpbb_log]([reportee_id])

+CREATE INDEX [user_id] ON [phpbb_log]([user_id])

+ [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]

+ )

+CREATE INDEX [att_ip] ON [phpbb_login_attempts]([attempt_ip], [attempt_time])

+CREATE INDEX [att_for] ON [phpbb_login_attempts]([attempt_forwarded_for], [attempt_time])

+CREATE INDEX [att_time] ON [phpbb_login_attempts]([attempt_time])

+CREATE INDEX [user_id] ON [phpbb_login_attempts]([user_id])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [disp_idx] ON [phpbb_moderator_cache]([display_on_index])

+CREATE INDEX [forum_id] ON [phpbb_moderator_cache]([forum_id])

+)GO

+ )

+CREATE INDEX [left_right_id] ON [phpbb_modules]([left_id], [right_id])

+CREATE INDEX [module_enabled] ON [phpbb_modules]([module_enabled])

+CREATE INDEX [class_left_id] ON [phpbb_modules]([module_class], [left_id])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [poll_opt_id] ON [phpbb_poll_options]([poll_option_id])

+CREATE INDEX [topic_id] ON [phpbb_poll_options]([topic_id])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [topic_id] ON [phpbb_poll_votes]([topic_id])

+CREATE INDEX [vote_user_id] ON [phpbb_poll_votes]([vote_user_id])

+CREATE INDEX [vote_user_ip] ON [phpbb_poll_votes]([vote_user_ip])

+)GO

+ )

+CREATE INDEX [forum_id] ON [phpbb_posts]([forum_id])

+CREATE INDEX [topic_id] ON [phpbb_posts]([topic_id])

+CREATE INDEX [poster_ip] ON [phpbb_posts]([poster_ip])

+CREATE INDEX [poster_id] ON [phpbb_posts]([poster_id])

+CREATE INDEX [post_approved] ON [phpbb_posts]([post_approved])

+CREATE INDEX [post_username] ON [phpbb_posts]([post_username])

+CREATE INDEX [tid_post_time] ON [phpbb_posts]([topic_id], [post_time])

+)GO

+ )

+CREATE INDEX [author_ip] ON [phpbb_privmsgs]([author_ip])

+CREATE INDEX [message_time] ON [phpbb_privmsgs]([message_time])

+CREATE INDEX [author_id] ON [phpbb_privmsgs]([author_id])

+CREATE INDEX [root_level] ON [phpbb_privmsgs]([root_level])

+)GO

+ )

+CREATE INDEX [user_id] ON [phpbb_privmsgs_folder]([user_id])

+)GO

+ )

+CREATE INDEX [user_id] ON [phpbb_privmsgs_rules]([user_id])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [msg_id] ON [phpbb_privmsgs_to]([msg_id])

+CREATE INDEX [author_id] ON [phpbb_privmsgs_to]([author_id])

+CREATE INDEX [usr_flder_id] ON [phpbb_privmsgs_to]([user_id], [folder_id])

+)GO

+ )

+CREATE INDEX [fld_type] ON [phpbb_profile_fields]([field_type])

+CREATE INDEX [fld_ordr] ON [phpbb_profile_fields]([field_order])

+)GO

+ )

+)GO

+ )

+)GO

+ )

+)GO

+ )

+)GO

+ )

+CREATE INDEX [post_id] ON [phpbb_reports]([post_id])

+CREATE INDEX [pm_id] ON [phpbb_reports]([pm_id])

+)GO

+ )

+)GO

+ )

+)GO

+ )

+CREATE UNIQUE INDEX [wrd_txt] ON [phpbb_search_wordlist]([word_text])

+CREATE INDEX [wrd_cnt] ON [phpbb_search_wordlist]([word_count])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE UNIQUE INDEX [unq_mtch] ON [phpbb_search_wordmatch]([word_id], [post_id], [title_match])

+CREATE INDEX [word_id] ON [phpbb_search_wordmatch]([word_id])

+CREATE INDEX [post_id] ON [phpbb_search_wordmatch]([post_id])

+)GO

+ )

+CREATE INDEX [session_time] ON [phpbb_sessions]([session_time])

+CREATE INDEX [session_user_id] ON [phpbb_sessions]([session_user_id])

+CREATE INDEX [session_fid] ON [phpbb_sessions]([session_forum_id])

+)GO

+ )

+CREATE INDEX [last_login] ON [phpbb_sessions_keys]([last_login])

+)GO

+ )

+)GO

+ )

+CREATE INDEX [display_on_post] ON [phpbb_smilies]([display_on_posting])

+)GO

+ )

+CREATE UNIQUE INDEX [style_name] ON [phpbb_styles]([style_name])

+CREATE INDEX [template_id] ON [phpbb_styles]([template_id])

+CREATE INDEX [theme_id] ON [phpbb_styles]([theme_id])

+CREATE INDEX [imageset_id] ON [phpbb_styles]([imageset_id])

+)GO

+ )

+CREATE UNIQUE INDEX [tmplte_nm] ON [phpbb_styles_template]([template_name])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [tid] ON [phpbb_styles_template_data]([template_id])

+CREATE INDEX [tfn] ON [phpbb_styles_template_data]([template_filename])

+)GO

+ )

+CREATE UNIQUE INDEX [theme_name] ON [phpbb_styles_theme]([theme_name])

+)GO

+ )

+CREATE UNIQUE INDEX [imgset_nm] ON [phpbb_styles_imageset]([imageset_name])

+)GO

+ )

+CREATE INDEX [i_d] ON [phpbb_styles_imageset_data]([imageset_id])

+)GO

+ )

+CREATE INDEX [forum_id] ON [phpbb_topics]([forum_id])

+CREATE INDEX [forum_id_type] ON [phpbb_topics]([forum_id], [topic_type])

+CREATE INDEX [last_post_time] ON [phpbb_topics]([topic_last_post_time])

+CREATE INDEX [topic_approved] ON [phpbb_topics]([topic_approved])

+CREATE INDEX [forum_appr_last] ON [phpbb_topics]([forum_id], [topic_approved], [topic_last_post_id])

+CREATE INDEX [fid_time_moved] ON [phpbb_topics]([forum_id], [topic_last_post_time], [topic_moved_id])

+)GO

+ )

+CREATE INDEX [topic_id] ON [phpbb_topics_track]([topic_id])

+CREATE INDEX [forum_id] ON [phpbb_topics_track]([forum_id])

+)GO

+ )

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [topic_id] ON [phpbb_topics_watch]([topic_id])

+CREATE INDEX [user_id] ON [phpbb_topics_watch]([user_id])

+CREATE INDEX [notify_stat] ON [phpbb_topics_watch]([notify_status])

+ [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

+ (

+ [mssqlindex]

+ )

+CREATE INDEX [group_id] ON [phpbb_user_group]([group_id])

+CREATE INDEX [user_id] ON [phpbb_user_group]([user_id])

+CREATE INDEX [group_leader] ON [phpbb_user_group]([group_leader])

+)GO

+ )

+CREATE INDEX [user_birthday] ON [phpbb_users]([user_birthday])

+CREATE INDEX [user_email_hash] ON [phpbb_users]([user_email_hash])

+CREATE INDEX [user_type] ON [phpbb_users]([user_type])

+CREATE UNIQUE INDEX [username_clean] ON [phpbb_users]([username_clean])

+)GO

+ )

+)GO

+ )

+)GO

+ )