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/postgres_schema.sql | 14 +++++++++-----
 1 file changed, 9 insertions(+), 5 deletions(-)

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

diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index 6dc507b46d..cd6de434b5 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -623,12 +623,16 @@ CREATE INDEX phpbb_modules_class_left_id ON phpbb_modules (module_class, left_id
 /*
 	Table: 'phpbb_notification_types'
 */
+CREATE SEQUENCE phpbb_notification_types_seq;
+
 CREATE TABLE phpbb_notification_types (
-	notification_type varchar(255) DEFAULT '' NOT NULL,
+	notification_type_id INT2 DEFAULT nextval('phpbb_notification_types_seq'),
+	notification_type_name varchar(255) DEFAULT '' NOT NULL,
 	notification_type_enabled INT2 DEFAULT '1' NOT NULL CHECK (notification_type_enabled >= 0),
-	PRIMARY KEY (notification_type, notification_type_enabled)
+	PRIMARY KEY (notification_type_id)
 );
 
+CREATE UNIQUE INDEX phpbb_notification_types_type ON phpbb_notification_types (notification_type_name);
 
 /*
 	Table: 'phpbb_notifications'
@@ -637,7 +641,7 @@ CREATE SEQUENCE phpbb_notifications_seq;
 
 CREATE TABLE phpbb_notifications (
 	notification_id INT4 DEFAULT nextval('phpbb_notifications_seq'),
-	item_type varchar(255) DEFAULT '' NOT NULL,
+	notification_type_id INT2 DEFAULT '0' NOT NULL CHECK (notification_type_id >= 0),
 	item_id INT4 DEFAULT '0' NOT NULL CHECK (item_id >= 0),
 	item_parent_id INT4 DEFAULT '0' NOT NULL CHECK (item_parent_id >= 0),
 	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
@@ -647,7 +651,7 @@ CREATE TABLE phpbb_notifications (
 	PRIMARY KEY (notification_id)
 );
 
-CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications (item_type, item_id);
+CREATE INDEX phpbb_notifications_item_ident ON phpbb_notifications (notification_type_id, item_id);
 CREATE INDEX phpbb_notifications_user ON phpbb_notifications (user_id, notification_read);
 
 /*
@@ -1171,7 +1175,7 @@ CREATE INDEX phpbb_topics_watch_notify_stat ON phpbb_topics_watch (notify_status
 	Table: 'phpbb_user_notifications'
 */
 CREATE TABLE phpbb_user_notifications (
-	item_type varchar(255) DEFAULT '' NOT NULL,
+	notification_type_id INT2 DEFAULT '0' NOT NULL CHECK (notification_type_id >= 0),
 	item_id INT4 DEFAULT '0' NOT NULL CHECK (item_id >= 0),
 	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
 	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/postgres_schema.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

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

diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index cd6de434b5..fea5700167 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -1175,7 +1175,7 @@ CREATE INDEX phpbb_topics_watch_notify_stat ON phpbb_topics_watch (notify_status
 	Table: 'phpbb_user_notifications'
 */
 CREATE TABLE phpbb_user_notifications (
-	notification_type_id INT2 DEFAULT '0' NOT NULL CHECK (notification_type_id >= 0),
+	item_type varchar(255) DEFAULT '' NOT NULL,
 	item_id INT4 DEFAULT '0' NOT NULL CHECK (item_id >= 0),
 	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
 	method varchar(255) DEFAULT '' NOT NULL,
-- 
cgit v1.2.1

