Index: /trunk/tools/collapse_diffinputs.pl
===================================================================
--- /trunk/tools/collapse_diffinputs.pl	(revision 20974)
+++ /trunk/tools/collapse_diffinputs.pl	(revision 20974)
@@ -0,0 +1,69 @@
+#!/usr/bin/env perl
+#
+# 
+# script to convert existing diffRun and diffInputSkyfiles rows 
+# 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
+#
+
+use strict;
+use warnings;
+
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+
+
+use DBI;
+
+my $dbname = shift;
+my $dbuser = shift;
+my $dbpass = shift;
+my $dbhost = shift;
+die "usage: $0 dbname dbuser dbpassword dbhost\n" unless $dbname and $dbuser and $dbpass and $dbhost;
+
+my $dbh  = getDBHandle($dbname, $dbuser, $dbpass, $dbhost);
+
+# find all exposures in summitExp
+my $query = "SELECT * FROM diffInputSkyfile where template = 1";
+
+my $stmt = $dbh->prepare($query);
+$stmt->execute();
+
+my $sub_query .= "(SELECT exp_id FROM diffInputSkyfile JOIN USING(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 = ?";
+
+while (my $ref = $stmt->fetchrow_hashref()) {
+    my $diff_id = $ref->{diff_id};
+    my $stack_id = $ref->{stack_id};
+
+    # 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");
+
+    # 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);
+}
+
+exit 0;
+
+
+sub getDBHandle {
+    my $dbname   = shift;
+    my $dbuser   = shift;
+    my $dbpass   = shift;
+    my $dbserver = shift;
+
+    die "database environment not set up" unless defined($dbserver) and defined($dbuser)
+        and defined($dbpass) and defined($dbname);
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+
+    return $dbh;
+}
+