Changeset 20994

Timestamp:

Dec 15, 2008, 1:05:11 PM (17 years ago)

Author:

bills

Message:

update schema changes to handle the migration of all diff input/template
combinations

Location:

trunk

Files:

• dbconfig/changes.txt (modified) (2 diffs)
• tools/collapse_diffinputs.pl (modified) (3 diffs)

trunk/dbconfig/changes.txt

@@ -742 +742 @@
 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;
@@ -758 +747 @@
 ALTER TABLE diffInputSkyfile ADD COLUMN warp2 BIGINT AFTER stack1, ADD KEY(warp2);
 ALTER TABLE diffInputSkyfile CHANGE COLUMN stack_id stack2 BIGINT AFTER warp2;
+
+-- 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 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);

trunk/tools/collapse_diffinputs.pl

@@ -5 +5 @@
 # From version 1.1.46 of the schema to 1.1.47
 #
-# Assumes that exp_id has been added to diffRun, but no other changes
-# have been made
-#
+# Assumes that the changes up to the diffInputSkyfile columns have been
+# made up to the place where it says that this script should be run
 
 use strict;
@@ -27 +26 @@
 
 # find all exposures in summitExp
-my $query = "SELECT * FROM diffInputSkyfile where template = 1";
+# my $query = "SELECT * FROM diffInputSkyfile where template = 1";
+
+my $query = "
+select
+    diff_id,
+    warp1,
+    stack1,
+    warp2,
+    stack2
+    FROM
+        (select
+            diff_id,
+            warp1 as warp1,
+            stack2 as stack1
+        from diffInputSkyfile
+        WHERE template = 0
+        ) as inputs
+    JOIN
+        (select
+            diff_id,
+            warp1 as warp2,
+            stack2 as stack2
+        from diffInputSkyfile
+        WHERE template = 1
+        ) as templates
+    USING (diff_id)
+";
 
 my $stmt = $dbh->prepare($query);
 $stmt->execute();
 
-my $sub_query .= "(SELECT exp_id FROM diffInputSkyfile JOIN warpRun USING(warp_id) join fakeRun using(fake_id) join camRun using(cam_id) join chipRun using(chip_id) where diff_id = ? )";
+
+my $sub_query .= "(SELECT exp_id FROM diffInputSkyfile JOIN warpRun on diffInputSkyfile.warp1 = warpRun.warp_id join fakeRun using(fake_id) join camRun using(cam_id) join chipRun using(chip_id) where diff_id = ? )";
 
 my $exp_query = "UPDATE diffRun SET exp_id = $sub_query where diff_id = ?";
@@ -38 +64 @@
 while (my $ref = $stmt->fetchrow_hashref()) {
     my $diff_id = $ref->{diff_id};
-    my $stack_id = $ref->{stack_id};
+    my $warp1 = $ref->{warp1};
+    my $warp2 = $ref->{warp2};
+    my $stack1 = $ref->{stack1};
+    my $stack2 = $ref->{stack2};
 
-    # copy the stack_id to the row with template = 0
-    $dbh->do("UPDATE diffInputSkyfile SET stack_id = $stack_id where diff_id = $diff_id AND template = 0");
+    # reorganizing columns puts warp1 and stack 1 correctly for template.
+    # set all of the stack and warp fields in the template = 0 input
+    # (the template = 1 row will be deleted)
+    if ($warp1 and $stack2) {
+        # usual case warp - stack
+        $dbh->do("UPDATE diffInputSkyfile SET stack2 = $stack2  where diff_id = $diff_id AND template = 0");
+    } elsif ($stack1 and $stack2) {
+        # stack - stack
+        $dbh->do("UPDATE diffInputSkyfile SET stack1 = $stack1, stack2 = $stack2  where diff_id = $diff_id AND template = 0");
+    } elsif ($warp1 and $warp2) {
+        # warp - warp
+        $dbh->do("UPDATE diffInputSkyfile SET warp2 = $warp2  where diff_id = $diff_id AND template = 0");
+    } elsif ($stack1 and $warp2) {
+        # stack - warp
+        $dbh->do("UPDATE diffInputSkyfile SET stack1 = $stack1, warp2 = $warp2  where diff_id = $diff_id AND template = 0");
+    } else {
+        die "unexpected input set for $diff_id";
+    }
 
-    # set the exp_id for the diffRun
     #
-    my $rows = $dbh->do($exp_query, undef, $diff_id, $diff_id);
-    die "failed to update diffRun.exp_id for $diff_id" if ($rows != 1);
+    # set the exp_id for the diffRun if the input is a warp
+    #
+    if ($warp1) {
+        my $rows = $dbh->do($exp_query, undef, $diff_id, $diff_id);
+        die "failed to update diffRun.exp_id for $diff_id" if ($rows != 1);
+    }
 }