Scarab commit: svn commit: r10153 - branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql

Author: dabbous
Date: 2006-06-07 14:08:43-0700
New Revision: 10153

Modified:
   branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql

Log:
changes in upgrade script due to incompatibilities with MySQL-4.0 fixed

Modified: branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql
Url: http://scarab.tigris.org/source/browse/scarab/branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql?view=diff&rev=10153&p1=branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql&p2=branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql&r1=10152&r2=10153
==============================================================================
--- branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql	(original)
+++ branches/release/b21/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql	2006-06-07 14:08:43-0700
@@ -16,6 +16,10 @@
 
 ALTER TABLE SCARAB_R_MODULE_USER_ATTRIBUTE ADD (INTERNAL_ATTRIBUTE VARCHAR(20) NULL);
 
+/*
+ * Drop index may fail, if it does not yet exist.
+ * The related error message can safely be ignored.
+ */
 DROP INDEX LIST_ID ON SCARAB_R_MODULE_USER_ATTRIBUTE;
 
 CREATE UNIQUE INDEX LIST_ID ON SCARAB_R_MODULE_USER_ATTRIBUTE (LIST_ID,MODULE_ID,USER_ID,ISSUE_TYPE_ID,ATTRIBUTE_ID,INTERNAL_ATTRIBUTE);
@@ -26,13 +30,25 @@
  *
  */
 
-ALTER TABLE SCARAB_ISSUE ADD COLUMN LAST_TRANS_ID BIGINT AFTER CREATED_TRANS_ID;
+CREATE TABLE SCARAB_ISSUE_BACKUP_b20_b21
+SELECT * FROM SCARAB_ISSUE;
+
+DROP TABLE SCARAB_ISSUE;
 
-ALTER TABLE SCARAB_ISSUE
-  ADD FOREIGN KEY (LAST_TRANS_ID) REFERENCES SCARAB_TRANSACTION (TRANSACTION_ID);
+CREATE TABLE SCARAB_ISSUE
+(
+    PRIMARY KEY(ISSUE_ID),
+    FOREIGN KEY (MODULE_ID) REFERENCES SCARAB_MODULE (MODULE_ID),
+    FOREIGN KEY (TYPE_ID) REFERENCES SCARAB_ISSUE_TYPE (ISSUE_TYPE_ID),
+    FOREIGN KEY (CREATED_TRANS_ID) REFERENCES SCARAB_TRANSACTION
+(TRANSACTION_ID),
+    INDEX IX_TYPE_MODULE (TYPE_ID, MODULE_ID),
+    INDEX IX_COUNT_PREFIX (ID_COUNT, ID_PREFIX)
+)
+SELECT i.ISSUE_ID, i.ID_PREFIX, i.ID_COUNT, i.ID_DOMAIN, i.TYPE_ID,
+i.MODULE_ID, i.CREATED_TRANS_ID,
+max(a.transaction_id) LAST_TRANS_ID, i.DELETED, i.MOVED
+FROM SCARAB_ISSUE_BACKUP_b20_b21 i LEFT OUTER JOIN SCARAB_ACTIVITY a ON
+(i.ISSUE_ID = a.ISSUE_ID)
+GROUP BY i.ISSUE_ID;
 
-UPDATE SCARAB_ISSUE i set i.LAST_TRANS_ID = ( 
-   SELECT MAX(a.TRANSACTION_ID)
-   FROM SCARAB_ACTIVITY a 
-   WHERE i.ISSUE_ID = a.ISSUE_ID 
-);