Index: trunk/DataStoreServer/scripts/dsreg
===================================================================
--- trunk/DataStoreServer/scripts/dsreg	(revision 17885)
+++ trunk/DataStoreServer/scripts/dsreg	(revision 17895)
@@ -11,5 +11,4 @@
 use Digest::MD5::File qw( file_md5_hex );
 use DBI;
-#use DBD::mysql 4.005;  # needed to set this one day on ipp000
 use PS::IPP::Metadata::Config;
 use PS::IPP::Metadata::Stats;
@@ -35,10 +34,10 @@
 my ($ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7);
 
-my $linkfiles;      # if set, put links to files in datapath in the Data Store
+my $linkfiles;      # if set, put links to files in the Data Store. The actuall directory is $datapath
 my $copyfiles;      # if set, copy files from this directory to the Data Store fileset
 my $datapath;       # source for files required if $linkfiles or $copyfiles
 
-my $dbname;                     # Database name
-my $dsroot;
+my $dbname;         # Database name
+my $dsroot;         # root directory of the data store
 
 my $add;
@@ -46,5 +45,5 @@
 my $remove;
 
-my $verbosity = 1;
+my $verbose = 0;
 my $no_cleanup = 0; # if something goes wrong don't delete copied or linked files for debug
 
@@ -74,4 +73,5 @@
         'dbname=s'      =>      \$dbname,
         'dsroot=s'      =>      \$dsroot,
+        'verbose'       =>      \$verbose,
 ) or pod2usage(2);
 
@@ -82,5 +82,5 @@
 
 
-# will exit if neither or both -add and -del were specified
+# will exit if neither --add and --del is specified or if they both are specified
 $err .= "Must specify either --add or --del.\n"
     unless $add xor $del;
@@ -119,5 +119,5 @@
 }
 
-# bail if any of the above args were improper
+# bail if any errors above
 show_usage($err)
     if ($err);
@@ -126,6 +126,6 @@
     unless defined stat("$dsroot/$product/index.txt");
 
+$dbname      = metadataLookupStr($siteConfig, 'DBNAME') unless defined $dbname;
 my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
-$dbname      = metadataLookupStr($siteConfig, 'DBNAME') unless defined $dbname;
 my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
 my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
@@ -133,6 +133,10 @@
 
 my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
-
-my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+my %conn_attrs = (PrintError => 1, RaiseError => 1, AutoCommit => 0);
+
+my $dbh = DBI->connect_cached($dsn, $dbuser, $dbpass, \%conn_attrs)
+        or die "Cannot connect to DB server\n";
+
+print STDERR "dbh: $dbh\n" if $verbose;
 
 my $fileset_dir = "$dsroot/$product/$fileset";
@@ -143,6 +147,8 @@
     # delete fileset
     #
-    my $stmt = $dbh->prepare("SELECT prod_id, last_fs FROM dsProduct WHERE prod_name = \'$product\'");
-    $stmt->execute();
+
+    my $stmt = $dbh->prepare("SELECT prod_id, last_fs FROM dsProduct WHERE prod_name = ?");
+    $stmt->execute($product);
+
     my $prod_row = $stmt->fetchrow_hashref();
     my $prod_id = $prod_row->{prod_id};
@@ -152,8 +158,8 @@
         die("product $product not found\n");
     }
-    $stmt = $dbh->prepare("SELECT fileset_id,fileset_name FROM dsFileset WHERE fileset_name = '$fileset'" .
-                        " AND prod_id = $prod_id");
-
-    $stmt->execute();
+    $stmt = $dbh->prepare("SELECT fileset_id,fileset_name FROM dsFileset" .
+                                 " WHERE fileset_name = ? AND prod_id = $prod_id");
+
+    $stmt->execute($fileset);
     my $fs_row = $stmt->fetchrow_hashref();
 
@@ -162,4 +168,38 @@
         exit 1;
     }
+    $stmt->finish();
+
+    my $fileset_id = $fs_row->{fileset_id};
+
+    eval {
+        $dbh->do("DELETE from dsFile where fileset_id = $fileset_id");
+        $dbh->do("DELETE from dsFileset where fileset_id = $fileset_id");
+
+        if ($old_last_fs eq $fs_row->{fileset_name}) {
+            # print STDERR "deleting most recent fileset in $product\n";
+
+            $stmt = $dbh->prepare("SELECT fileset_name from dsFileset " .
+                        "WHERE prod_id = $prod_id ORDER BY reg_time DESC LIMIT 1");
+            $stmt->execute();
+            my $new_last_fs;
+            my $new_newest = $stmt->fetchrow_hashref();
+            if ($new_newest) {
+                $new_last_fs = $new_newest->{fileset_name};
+            } else {
+                $new_last_fs = "none";
+            }
+            $stmt->finish();
+            $dbh->do("UPDATE dsProduct SET last_fs = \'$new_last_fs\' WHERE prod_id = $prod_id");
+        }
+        $dbh->commit();
+    };
+    if ($@) { # an error occured
+        print STDERR "transaction failed, rolling back error was:\n$@\n";
+        eval {$dbh->rollback();};
+        cleanup();
+        exit 1;
+    }
+
+    $dbh->disconnect();
 
     if ($remove) {
@@ -170,25 +210,4 @@
         # zap the index script
         unlink("$index_script_name");
-    }
-
-    my $fileset_id = $fs_row->{fileset_id};
-
-    $dbh->do("DELETE from dsFile where fileset_id = $fileset_id");
-    $dbh->do("DELETE from dsFileset where fileset_id = $fileset_id");
-
-    if ($old_last_fs eq $fs_row->{fileset_name}) {
-        # print STDERR "deleting most recent fileset in $product\n";
-
-        $stmt = $dbh->prepare("SELECT fileset_name from dsFileset " .
-                    "WHERE prod_id = $prod_id ORDER BY reg_time DESC LIMIT 1");
-        $stmt->execute();
-        my $new_last_fs;
-        my $new_newest = $stmt->fetchrow_hashref();
-        if ($new_newest) {
-            $new_last_fs = $new_newest->{fileset_name};
-        } else {
-            $new_last_fs = "none";
-        }
-        $dbh->do("UPDATE dsProduct SET last_fs = \'$new_last_fs\' WHERE prod_id = $prod_id");
     }
     exit 0;
@@ -203,15 +222,15 @@
     }
 
-    my $stmt = $dbh->prepare("SELECT prod_id FROM dsProduct WHERE prod_name = \'$product\'");
-    $stmt->execute();
+    my $stmt = $dbh->prepare("SELECT prod_id FROM dsProduct WHERE prod_name = ?");
+    $stmt->execute($product);
     my $row = $stmt->fetchrow_hashref();
     my $prod_id = $row->{prod_id};
+    $stmt->finish();
 
     if (!$prod_id) {
         die("product $product not found");
     }
-    my $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = '$fileset'" .
-                        " AND prod_id = $prod_id");
-
+    my $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = ?" .
+                        " AND prod_id = $prod_id", undef, ($fileset));
     if ($count != 0E0) {
         print "Fileset '$fileset' already exists under $product.\n";
@@ -222,5 +241,4 @@
     # fileset_name = $fileset. So later we'll check again that only one fileset with the name exists
 
-    my $lineno = 0;
 
     my $in;
@@ -231,35 +249,30 @@
     }
 
+    my $lineno = 0;
     my @files;
     while (<$in>) {
+        chomp;
         $lineno++;
 
         my $filename;
         my $filetype;
-        my $ts_string = "";
-
-        chomp;
+
         my @fields = split /\|/;
         if (@fields < 2) {
-            die "malformed input line: $filelist line $lineno: $_\n";
+            die "malformed input line at $filelist line $lineno: $_\n";
         }
         $filename = shift @fields;
         $filetype = shift @fields;
 
-        #
-        # save any type specific fields provided in the string to be added to the
-        #
-        my $nfields = @fields;
-        my $i;
-        for ($i=0; $i < $nfields; $i++) {
-            $ts_string .= ", \'$fields[$i]\'";
-        }
-        for ($i=$nfields ; $i < 4; $i++) {
-            $ts_string .= ", NULL";
-        }
+        # make sure the length of the type specific columns is 8
+        while (@fields < 8) {
+            push @fields, undef;
+        }
+        my $ts_cols = [@fields];
+
         my $hashref = {
             'file'      => $filename,
             'type'      => $filetype,
-            'ts_string' => $ts_string
+            'ts_cols'   => $ts_cols,
         };
 
@@ -300,5 +313,4 @@
                 die("failed to remove $fileset_dir");
             }
-
             exit $PS_EXIT_UNKNOWN_ERROR;
         }
@@ -322,35 +334,29 @@
 
 
-    # TODO: why do I do this before the database insert is complete?
-    # create the cgi script index.txt by making a copy of its parent's
-    if (!copy("$dsroot/$product/index.txt", $index_script_name)) {
-        cleanup();
-        die("failed to copy index script to file set");
+    if (! $dbh->ping()) {
+        # database connection has gone away, reconnect
+        $dbh->disconnect();
+        $dbh = DBI->connect_cached($dsn, $dbuser, $dbpass, \%conn_attrs)
+            or die "Cannot connect to server\n";
+        print STDERR "ping failed new dbh: $dbh\n" if $verbose;
     }
 
     eval {
-        $dbh->{RaiseError} = 1; # raise exception if error occurs
-        $dbh->{PrintError} = 1;
-        $dbh->{AutoCommit} = 0;
-
-        # note the resulting prodcolstr begins with a comma
-        my $prodcolstr = make_prodcol_str($ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7);
-
         my $qstring = "INSERT into dsFileset" .
                 " (prod_id, fileset_name, reg_time, type," .
                 " prod_col_0, prod_col_1, prod_col_2, prod_col_3, prod_col_4, prod_col_5, " .
                 " prod_col_6, prod_col_7)" .
-                " VALUES($prod_id, \'$fileset\', UTC_TIMESTAMP(), \'$fstype\' $prodcolstr)";
-
-        $count = $dbh->do($qstring);
-        if ($count == 0E0) {
-            die("failed to insert $fileset");
-        }
+                " VALUES(?, ?, UTC_TIMESTAMP(), ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+
+        $count = $dbh->do($qstring, undef, ($prod_id, $fileset, $fstype, 
+                            $ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7));
+
+        die("failed to insert $fileset") if ($count == 0E0);
 
         my $fileset_id = $dbh->last_insert_id(undef, undef, undef, undef);
 
         # make sure that no-one got in and created a fileset with this name while we were busy
-        $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = '$fileset'" .
-                        " AND prod_id = $prod_id");
+        $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = ?" .
+                        " AND prod_id = $prod_id", undef, ($fileset));
         if ($count > 1) {
             die("Fileset '$fileset' already exists under $product.");
@@ -358,16 +364,16 @@
 
         foreach my $ref (@files) {
-            # note: ts_string has a leading comma
-            my $query = "INSERT INTO dsFile" .
-                    " (fileset_id, file_id, file_name, bytes, md5sum, type, " .
-                    " type_col_0, type_col_1, type_col_2, type_col_3)" .
-
-                    " VALUES($fileset_id, 0, \'$ref->{file}\', $ref->{bytes}," .
-                             " \'$ref->{md5sum}\', \'$ref->{type}\' $ref->{ts_string} )";
-
-            $count = $dbh->do($query);
+            my $query = "INSERT INTO dsFile" .  
+                    " (fileset_id, file_id, file_name, bytes, md5sum, type," .
+                    " type_col_0, type_col_1, type_col_2, type_col_3, type_col_4, type_col_5," .
+                    " type_col_6, type_col_7)" .
+                    " VALUES($fileset_id, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+
+            my $aref = $ref->{ts_cols};
+            my $do_args =  [($ref->{file}, $ref->{bytes}, $ref->{md5sum}, $ref->{type}), @$aref];
+
+            $count = $dbh->do($query, undef, @$do_args);
 
             if ($count == 0E0) {
-                $dbh->rollback();
                 die("failed to insert $ref->{file} into $fileset");
             }
@@ -377,16 +383,23 @@
                                 . " WHERE prod_id = $prod_id");
         if ($count == 0E0) {
-            $dbh->rollback();
             die("failed to update dsProduct $prod_id");
         }
 
         $dbh->commit();
+
+        # create the cgi script index.txt by making a copy of its parent's
+        if (!copy("$dsroot/$product/index.txt", $index_script_name)) {
+            die("failed to copy index script to file set");
+        }
     };
     if ($@) { # an error occured
-        print STDERR "transactionfailed, rolling back error was:\n$@\n";
+        print STDERR "transaction failed, rolling back error was:\n$@\n";
         eval {$dbh->rollback();};
         cleanup();
         exit 1;
     }
+
+    print STDERR "calling disconnect before exit\n" if $verbose;
+    $dbh->disconnect();
 
     exit 0;
@@ -431,20 +444,4 @@
 }
 
-sub make_prodcol_str {
-
-    my @list = @_;
-    my $string = "";
-
-    foreach my $s (@list) {
-        if ($s) {
-            $string .= ", \'$s\'";
-        } else {
-            $string .= ", NULL";
-        }
-    }
-
-    return $string;
-}
-
 sub cleanup {
     if (!$no_cleanup && ($linkfiles or $copyfiles)) {