From 198b992dcef0a0a7099eb3db6185d567b58b6e5a Mon Sep 17 00:00:00 2001
From: Nathaniel Guse <nathaniel.guse@gmail.com>
Date: Sun, 28 Apr 2013 22:53:05 -0500
Subject: [ticket/11413] Schema changes and migration file

Notifications tables are dropped because phpBB currently does not have
any way to make the necessary changes to the DB schema (and no release
has yet been made with these changes).

This will fix the following bugs:
PHPBB3-11411
PHPBB3-11413
PHPBB3-11414
PHPBB3-11416
PHPBB3-11420

PHPBB3-11413
---
 phpBB/install/schemas/mssql_schema.sql | 15 +++++++++------
 1 file changed, 9 insertions(+), 6 deletions(-)

(limited to 'phpBB/install/schemas/mssql_schema.sql')

diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 3530f9cd25..e869cbd1b5 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -793,7 +793,8 @@ GO
 	Table: 'phpbb_notification_types'
 */
 CREATE TABLE [phpbb_notification_types] (
-	[notification_type] [varchar] (255) DEFAULT ('') NOT NULL ,
+	[notification_type_id] [int] IDENTITY (1, 1) NOT NULL ,
+	[notification_type_name] [varchar] (255) DEFAULT ('') NOT NULL ,
 	[notification_type_enabled] [int] DEFAULT (1) NOT NULL 
 ) ON [PRIMARY]
 GO
@@ -801,18 +802,20 @@ GO
 ALTER TABLE [phpbb_notification_types] WITH NOCHECK ADD 
 	CONSTRAINT [PK_phpbb_notification_types] PRIMARY KEY  CLUSTERED 
 	(
-		[notification_type],
-		[notification_type_enabled]
+		[notification_type_id]
 	)  ON [PRIMARY] 
 GO
 
+CREATE  UNIQUE  INDEX [type] ON [phpbb_notification_types]([notification_type_name]) ON [PRIMARY]
+GO
+
 
 /*
 	Table: 'phpbb_notifications'
 */
 CREATE TABLE [phpbb_notifications] (
 	[notification_id] [int] IDENTITY (1, 1) NOT NULL ,
-	[item_type] [varchar] (255) DEFAULT ('') NOT NULL ,
+	[notification_type_id] [int] DEFAULT (0) NOT NULL ,
 	[item_id] [int] DEFAULT (0) NOT NULL ,
 	[item_parent_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
@@ -829,7 +832,7 @@ ALTER TABLE [phpbb_notifications] WITH NOCHECK ADD
 	)  ON [PRIMARY] 
 GO
 
-CREATE  INDEX [item_ident] ON [phpbb_notifications]([item_type], [item_id]) ON [PRIMARY]
+CREATE  INDEX [item_ident] ON [phpbb_notifications]([notification_type_id], [item_id]) ON [PRIMARY]
 GO
 
 CREATE  INDEX [user] ON [phpbb_notifications]([user_id], [notification_read]) ON [PRIMARY]
@@ -1588,7 +1591,7 @@ GO
 	Table: 'phpbb_user_notifications'
 */
 CREATE TABLE [phpbb_user_notifications] (
-	[item_type] [varchar] (255) DEFAULT ('') NOT NULL ,
+	[notification_type_id] [int] DEFAULT (0) NOT NULL ,
 	[item_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
 	[method] [varchar] (255) DEFAULT ('') NOT NULL ,
-- 
cgit v1.2.1


From 33287a73609a99f33f3d0718fceaf72e39d5283e Mon Sep 17 00:00:00 2001
From: Nathaniel Guse <nathaniel.guse@gmail.com>
Date: Mon, 29 Apr 2013 21:22:07 -0500
Subject: [ticket/11413] Undo editing the user_notifications table

item_type is not equivalent to notification_type_name, it can be a generic
string (typically used to be able to subscribe to multiple notification
types while only subscribing to one item

PHPBB3-11413
---
 phpBB/install/schemas/mssql_schema.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'phpBB/install/schemas/mssql_schema.sql')

diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index e869cbd1b5..8465dc4d72 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -1591,7 +1591,7 @@ GO
 	Table: 'phpbb_user_notifications'
 */
 CREATE TABLE [phpbb_user_notifications] (
-	[notification_type_id] [int] DEFAULT (0) NOT NULL ,
+	[item_type] [varchar] (255) DEFAULT ('') NOT NULL ,
 	[item_id] [int] DEFAULT (0) NOT NULL ,
 	[user_id] [int] DEFAULT (0) NOT NULL ,
 	[method] [varchar] (255) DEFAULT ('') NOT NULL ,
-- 
cgit v1.2.1

