Scarab commit: svn commit: r10133 - trunk/src/sql/postgresql/upgrade

Author: pledbrook
Date: 2006-06-02 02:15:25-0700
New Revision: 10133

Added:
   trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-1.sql   (contents, props changed)
   trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-2.sql   (contents, props changed)
   trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-3.sql   (contents, props changed)

Log:
Adding b20-b21 upgrade scripts for PostgreSQL.

Added: trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-1.sql
Url: http://scarab.tigris.org/source/browse/scarab/trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-1.sql?view=auto&rev=10133
==============================================================================
--- (empty file)
+++ trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-1.sql	2006-06-02 02:15:25-0700
@@ -0,0 +1,33 @@
+---
+--- b20-b21 Migration.
+---
+--- Creates new fields to allow for the deletion of issues
+--- Creates new permissions for adding comments and deleting issues
+---
+ 
+---
+--- Adds the field to describe ActivityType for every activity record.
+---
+ALTER TABLE SCARAB_ACTIVITY ADD ACTIVITY_TYPE varchar(30) null;
+
+---
+--- Adds the field to mark an issue as 'moved'. The 'deleted' will now be
+--- used just for the deletions.
+---
+ALTER TABLE SCARAB_ISSUE ADD MOVED INT2;
+
+---
+--- Move the 'deleted' values into 'moved' field.
+---
+update SCARAB_ISSUE SET MOVED=DELETED;
+
+---
+--- Reset the 'deleted' issue.
+---
+update SCARAB_ISSUE SET DELETED=0;
+
+---
+--- Insert new permissions (delete & comment)
+---
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME) VALUES (21, 'Issue | Comment');
+INSERT INTO TURBINE_PERMISSION (PERMISSION_ID, PERMISSION_NAME) VALUES (20, 'Issue | Delete');

Added: trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-2.sql
Url: http://scarab.tigris.org/source/browse/scarab/trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-2.sql?view=auto&rev=10133
==============================================================================
--- (empty file)
+++ trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-2.sql	2006-06-02 02:15:25-0700
@@ -0,0 +1,54 @@
+---
+--- b20-b21 Migration.
+---
+--- Creates new tables for the notification system.
+---
+
+---
+--- SCARAB_NOTIFICATION_STATUS
+---
+CREATE TABLE SCARAB_NOTIFICATION_STATUS
+(
+    ACTIVITY_ID INT8 NOT NULL,
+    CREATOR_ID INTEGER NOT NULL,
+    RECEIVER_ID INTEGER NOT NULL,
+    STATUS INTEGER NOT NULL,
+    CREATION_DATE DATE,
+    CHANGE_DATE DATE,
+    COMMENT VARCHAR(255),
+    PRIMARY KEY(ACTIVITY_ID,CREATOR_ID,RECEIVER_ID),
+    FOREIGN KEY (CREATOR_ID) REFERENCES TURBINE_USER (USER_ID),
+    FOREIGN KEY (RECEIVER_ID) REFERENCES TURBINE_USER (USER_ID),
+    FOREIGN KEY (ACTIVITY_ID) REFERENCES SCARAB_ACTIVITY (ACTIVITY_ID)
+);
+CREATE INDEX IX_NOTIF_STATUS ON SCARAB_NOTIFICATION_STATUS (STATUS);
+
+---
+--- SCARAB_NOTIFICATION_FILTER
+---
+CREATE TABLE SCARAB_NOTIFICATION_FILTER
+(
+    MODULE_ID INTEGER NOT NULL,
+    USER_ID INTEGER NOT NULL,
+    ACTIVITY_TYPE VARCHAR(30) NOT NULL,
+    MANAGER_ID INTEGER default 0 NOT NULL,
+    FILTER_STATE INT2 default 0 NOT NULL,
+    SEND_SELF INT2 default 0 NOT NULL,
+    SEND_FAILURES INT2 default 0 NOT NULL,
+    PRIMARY KEY(MODULE_ID,USER_ID,ACTIVITY_TYPE,MANAGER_ID),
+    FOREIGN KEY (USER_ID) REFERENCES TURBINE_USER (USER_ID),
+    FOREIGN KEY (MODULE_ID) REFERENCES SCARAB_MODULE (MODULE_ID)
+);
+
+---
+--- SCARAB_DEFAULT_NOTIFICATION_FILTER
+---
+CREATE TABLE SCARAB_DEFAULT_NOTIFICATION_FILTER (
+  MODULE_ID INTEGER NOT NULL,
+  ROLE_ID INTEGER NOT NULL,
+  ACTIVITY_TYPE varchar(30) NOT NULL,
+  MANAGER_ID INTEGER NOT NULL,
+  FILTER_STATE INT2 NOT NULL default '0',
+  MODIFYABlE INT2 NOT NULL default '1',
+  PRIMARY KEY  (MODULE_ID,ROLE_ID,ACTIVITY_TYPE,MANAGER_ID)
+);

Added: trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-3.sql
Url: http://scarab.tigris.org/source/browse/scarab/trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-3.sql?view=auto&rev=10133
==============================================================================
--- (empty file)
+++ trunk/src/sql/postgresql/upgrade/postgresql-upgrade-1.0b20-1.0b21-3.sql	2006-06-02 02:15:25-0700
@@ -0,0 +1,33 @@
+---
+--- b20-b21 Migration.
+---
+--- SCB1565: Include 'last modified' and 'creation' information in search results
+---  - Modifications on SCARAB_R_MODULE_USER_ATTRIBUTE
+---  - Dump and recreate SCARAB_ISSUE with the new field (LAST_TRANS_ID) loaded
+---
+
+---
+--- SCB1565: Creation and Last Modification date and users in Query Results
+---
+--- - Add new column to SCARAB_R_MODULE_USER_ATTRIBUTE and modify the unique constraint
+---
+ALTER TABLE SCARAB_R_MODULE_USER_ATTRIBUTE ADD INTERNAL_ATTRIBUTE VARCHAR(20) NULL;
+
+ALTER TABLE SCARAB_R_MODULE_USER_ATTRIBUTE DROP CONSTRAINT SCARAB_R_MODULE_USER_ATTRIBUTE_U_1;
+
+CREATE UNIQUE INDEX SCARAB_R_MODULE_USER_ATTRIBUTE_U_1 ON SCARAB_R_MODULE_USER_ATTRIBUTE (LIST_ID,MODULE_ID,USER_ID,ISSUE_TYPE_ID,ATTRIBUTE_ID,INTERNAL_ATTRIBUTE);
+
+---
+--- Add new column to SCARAB_ISSUE with LAST_TRANS_ID, setting its value to
+--- the older activity ID related to every issue
+---
+ALTER TABLE SCARAB_ISSUE ADD COLUMN LAST_TRANS_ID INT8;
+
+ALTER TABLE SCARAB_ISSUE
+  ADD FOREIGN KEY (LAST_TRANS_ID) REFERENCES SCARAB_TRANSACTION (TRANSACTION_ID);
+
+UPDATE SCARAB_ISSUE SET LAST_TRANS_ID = ( 
+   SELECT MAX(activity.TRANSACTION_ID)
+   FROM SCARAB_ACTIVITY activity 
+   WHERE scarab_issue.ISSUE_ID = activity.ISSUE_ID 
+);