Index: trunk/dbconfig/changes.txt
===================================================================
--- trunk/dbconfig/changes.txt	(revision 20855)
+++ trunk/dbconfig/changes.txt	(revision 20973)
@@ -717,2 +717,76 @@
 alter table magicDSRun add column cam_id BIGINT after stage_id;
 
+--  Version: 1.1.47
+
+-- WARNING WARNING WARNING
+-- Updating a database which has populated diffRun's requires editing
+-- the contents of the tables in a way that cannot be expresed here.
+-- The script tools/collapsdiffinputs.pl does the appropriate edits
+-- and should be run at the spot indicated below
+
+-- fix order of workdir to match pxadmin_create_tables.sql
+ALTER TABLE diffRun CHANGE COLUMN workdir workdir VARCHAR(255) AFTER state;
+
+-- add exposure id to diffRun
+ALTER TABLE diffRun ADD COLUMN exp_id BIGINT;
+ALTER TABLE diffRun ADD FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id);
+ALTER TABLE diffRun DROP COLUMN skycell_id;
+
+
+-- drop exiting foreign key constraints
+ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_1;
+ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_2;
+ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_3;
+
+ALTER TABLE diffInputSkyfile DROP COLUMN kind;
+ALTER TABLE diffInputSkyfile CHANGE COLUMN skycell_id skycell_id VARCHAR(64) AFTER diff_id;
+
+-- WARNING WARNING
+-- If you have existing diff runs they must be converted
+
+-- run the script collapse_diffinputs.pl here.
+--        collapse_diffinputs.pl dbname dbuser dbpass dbhost
+
+DELETE FROM diffInputSkyfile where template = 1;
+
+ALTER TABLE diffInputSkyfile DROP PRIMARY KEY;
+ALTER TABLE diffInputSkyfile ADD PRIMARY KEY (diff_id, skycell_id);
+ALTER TABLE diffInputSkyfile DROP COLUMN template;
+
+ALTER TABLE diffInputSkyfile CHANGE COLUMN warp_id warp1 BIGINT;
+ALTER TABLE diffInputSkyfile ADD COLUMN stack1 BIGINT AFTER warp1, ADD KEY(stack1);
+ALTER TABLE diffInputSkyfile ADD COLUMN warp2 BIGINT AFTER stack1, ADD KEY(warp2);
+ALTER TABLE diffInputSkyfile CHANGE COLUMN stack_id stack2 BIGINT AFTER warp2;
+ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id);
+
+# need to check these
+ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);
+ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);
+
+ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack1) REFERENCES stackRun(stack_id);
+ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack2) REFERENCES stackRun(stack_id); 
+
+-- add unique ids for each image and flags that indicate whether the image has been
+-- magic destreaked
+ALTER TABLE diffSkyfile ADD COLUMN skycell_id VARCHAR(64) AFTER diff_id;
+ALTER TABLE diffSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY (diff_id, skycell_id);
+ALTER TABLE diffSkyfile ADD COLUMN diff_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(diff_image_id);
+ALTER TABLE diffSkyfile ADD COLUMN magicked TINYINT;
+
+ALTER TABLE warpRun DROP COLUMN magiced;
+
+ALTER TABLE warpSkyfile ADD COLUMN warp_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(warp_image_id);
+ALTER TABLE warpSkyfile ADD COLUMN magicked TINYINT;
+
+ALTER TABLE chipProcessedImfile ADD COLUMN chip_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(chip_image_id);
+ALTER TABLE chipProcessedImfile ADD COLUMN magicked TINYINT;
+
+ALTER TABLE rawImfile ADD COLUMN raw_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(raw_image_id);
+ALTER TABLE rawImfile ADD COLUMN magicked TINYINT;
+
+-- magic adaptations to diff reorginization
+ALTER TABLE magicRun ADD COLUMN diff_id BIGINT AFTER exp_id;
+ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id);
+ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id);
+ALTER TABLE magicInputSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY(magic_id, diff_id, node);
+
Index: trunk/dbconfig/chip.md
===================================================================
--- trunk/dbconfig/chip.md	(revision 20855)
+++ trunk/dbconfig/chip.md	(revision 20973)
@@ -81,4 +81,6 @@
     path_base       STR     255
     fault           S16     0       # Key NOT NULL
+    chip_image_id   S64     0       # Key AUTO_INCREMENT
+    magicked        BOOL    f
 END
 
Index: trunk/dbconfig/config.md
===================================================================
--- trunk/dbconfig/config.md	(revision 20855)
+++ trunk/dbconfig/config.md	(revision 20973)
@@ -2,4 +2,4 @@
     pkg_name        STR     ippdb
     pkg_namespace   STR     ippdb
-    pkg_version     STR     1.1.46
+    pkg_version     STR     1.1.47
 END
Index: trunk/dbconfig/diff.md
===================================================================
--- trunk/dbconfig/diff.md	(revision 20855)
+++ trunk/dbconfig/diff.md	(revision 20973)
@@ -1,14 +1,14 @@
-# $Id: diff.md,v 1.14 2008-10-11 02:33:40 price Exp $
+# $Id: diff.md,v 1.15 2008-12-13 20:17:34 bills Exp $
 
 diffRun METADATA
     diff_id     S64         0       # Primary Key AUTO_INCREMENT
     state       STR         64      # Key
+    workdir     STR         255
     label       STR         64      # Key
     reduction   STR         64      # Reduction class
-    workdir     STR         255
     dvodb       STR         255
     registered  TAI         NULL
-    skycell_id  STR         64      # Key
     tess_id     STR         64      # Key
+    exp_id      S64         0       # fkey(exp_id) ref rawExp(exp_id)
 END
 
@@ -17,18 +17,17 @@
 #
 
-# only ever 2 per run - one template / one not
 diffInputSkyfile METADATA
     diff_id     S64         0       # Primary Key fkey(diff_id) ref diffRun(diff_id)
-    template    BOOL        f       # Primary Key
-    stack_id    S64         0       # fkey(stack_id) ref stackSumSkyfile(stack_id) 
-    warp_id     S64         0       # fkey(warp_id, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
-    skycell_id  STR         64      # Key
+    skycell_id  STR         64      # Primary Key
+    warp1       S64         0       # fkey(warp1, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
+    stack1      S64         0       # fkey(stack1) ref stackSumSkyfile(stack_id) 
+    warp2       S64         0       # fkey(warp2, skycell_id, tess_id) ref warpSkyfile(warp_id, skycell_id, tess_id)
+    stack2      S64         0       # fkey(stack2) ref stackSumSkyfile(stack_id) 
     tess_id     STR         64      # Key
-# either a input or a template
-    kind        STR         64      # Key 
 END
 
 diffSkyfile METADATA
     diff_id      S64        0       # Primary Key fkey(diff_id) ref diffRun(diff_id)
+    skycell_id   STR        64      # 
     uri          STR        255
     path_base    STR        255
@@ -53,3 +52,5 @@
     good_frac    F32        0.0     # Key
     fault        S16        0       # Key
+    diff_image_id S64       0       # Key NOT NULL AUTO_INCREMENT
+    magicked     BOOL       f
 END
Index: trunk/dbconfig/magic.md
===================================================================
--- trunk/dbconfig/magic.md	(revision 20855)
+++ trunk/dbconfig/magic.md	(revision 20973)
@@ -1,3 +1,3 @@
-# $Id: magic.md,v 1.12 2008-11-26 03:21:13 bills Exp $
+# $Id: magic.md,v 1.13 2008-12-13 20:17:34 bills Exp $
 
 ### Fault in magicRun indicates that the processing tree failed
@@ -5,4 +5,5 @@
     magic_id    S64         0       # Primary Key AUTO_INCREMENT
     exp_id      S64         0       # Key
+    diff_id     S64         0       # Key
     state       STR         64      # Key
     workdir     STR         255
Index: trunk/dbconfig/tasks.md
===================================================================
--- trunk/dbconfig/tasks.md	(revision 20855)
+++ trunk/dbconfig/tasks.md	(revision 20973)
@@ -1,3 +1,3 @@
-# $Id: tasks.md,v 1.158 2008-11-09 23:51:01 price Exp $
+# $Id: tasks.md,v 1.159 2008-12-13 20:17:34 bills Exp $
 
 # this table records all exposure ID ever seen from the summit
@@ -239,3 +239,4 @@
     fault       S16         0       # Key NOT NULL
     epoch       UTC         0001-01-01T00:00:00Z
-END
+    magicked    BOOL        f
+END
Index: trunk/dbconfig/warp.md
===================================================================
--- trunk/dbconfig/warp.md	(revision 20855)
+++ trunk/dbconfig/warp.md	(revision 20973)
@@ -1,3 +1,3 @@
-# $Id: warp.md,v 1.19 2008-10-07 00:01:28 price Exp $
+# $Id: warp.md,v 1.20 2008-12-13 20:17:34 bills Exp $
 
 #
@@ -11,5 +11,5 @@
 warpRun METADATA
     warp_id     S64         0       # Primary Key AUTO_INCREMENT
-    fake_id      S64         0       # Key INDEX(warp_id, fake_id) fkey(fake_id) ref camProcessedExp(fake_id)
+    fake_id     S64         0       # Key INDEX(warp_id, fake_id) fkey(fake_id) ref camProcessedExp(fake_id)
     mode        STR         64      # Key
     state       STR         64      # Key
@@ -22,5 +22,4 @@
     registered  TAI         NULL
 # if magic is T then look for the exp_id in the magic output tables
-    magiced     BOOL        f       # Key
 END
 
@@ -52,4 +51,6 @@
     ignored        BOOL     f       # Key
     fault          S16      0       # Key
+    warp_image_id  S64      0       # Key AUTO_INCREMENT
+    magicked       BOOL     f
 END