Scarab commit: svn commit: r10143 - trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql

Author: jorgeuriarte
Date: 2006-06-05 09:19:01-0700
New Revision: 10143

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

Log:
Applied patch to B21 migration process in mysql. Provided by Steve James (fixing my error ;-) ). Thanks\!

Modified: trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql
Url: http://scarab.tigris.org/source/browse/scarab/trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql?view=diff&rev=10143&p1=trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql&p2=trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql&r1=10142&r2=10143
==============================================================================
--- trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql	(original)
+++ trunk/src/sql/mysql/upgrade/mysql-upgrade-1.0b20-1.0b21-3.sql	2006-06-05 09:19:01-0700
@@ -24,17 +24,15 @@
  * Add new column to SCARAB_ISSUE with LAST_TRANS_ID, setting its value to
  * the older activity ID related to every issue
  *
- * A backup table (SCARAB_ISSUE_BACKUP_b20_b21) is created
- *
  */
 
-CREATE TABLE SCARAB_ISSUE_BACKUP_b20_b21
-SELECT * FROM SCARAB_ISSUE;
+ALTER TABLE SCARAB_ISSUE ADD COLUMN LAST_TRANS_ID BIGINT AFTER CREATED_TRANS_ID;
 
-DROP TABLE SCARAB_ISSUE;
+ALTER TABLE SCARAB_ISSUE
+  ADD FOREIGN KEY (LAST_TRANS_ID) REFERENCES SCARAB_TRANSACTION (TRANSACTION_ID);
 
-CREATE TABLE SCARAB_ISSUE
-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 
+);