IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 17895


Ignore:
Timestamp:
Jun 3, 2008, 1:15:01 PM (18 years ago)
Author:
bills
Message:

Put delete under transaction. Don't interpolate with user provided values.
reconnect if the database connection goes away while processing Paul's
ridiculously long file lists. Various other improvements.

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/DataStoreServer/scripts/dsreg

    r17884 r17895  
    1111use Digest::MD5::File qw( file_md5_hex );
    1212use DBI;
    13 #use DBD::mysql 4.005;  # needed to set this one day on ipp000
    1413use PS::IPP::Metadata::Config;
    1514use PS::IPP::Metadata::Stats;
    … …  
    3534my ($ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7);
    3635
    37 my $linkfiles;      # if set, put links to files in datapath in the Data Store
     36my $linkfiles;      # if set, put links to files in the Data Store. The actuall directory is $datapath
    3837my $copyfiles;      # if set, copy files from this directory to the Data Store fileset
    3938my $datapath;       # source for files required if $linkfiles or $copyfiles
    4039
    41 my $dbname;                     # Database name
    42 my $dsroot;
     40my $dbname;         # Database name
     41my $dsroot;         # root directory of the data store
    4342
    4443my $add;
    … …  
    4645my $remove;
    4746
    48 my $verbosity = 1;
     47my $verbose = 0;
    4948my $no_cleanup = 0; # if something goes wrong don't delete copied or linked files for debug
    5049
    … …  
    7473        'dbname=s'      =>      \$dbname,
    7574        'dsroot=s'      =>      \$dsroot,
     75        'verbose'       =>      \$verbose,
    7676) or pod2usage(2);
    7777
    … …  
    8282
    8383
    84 # will exit if neither or both -add and -del were specified
     84# will exit if neither --add and --del is specified or if they both are specified
    8585$err .= "Must specify either --add or --del.\n"
    8686    unless $add xor $del;
    … …  
    119119}
    120120
    121 # bail if any of the above args were improper
     121# bail if any errors above
    122122show_usage($err)
    123123    if ($err);
    … …  
    126126    unless defined stat("$dsroot/$product/index.txt");
    127127
     128$dbname      = metadataLookupStr($siteConfig, 'DBNAME') unless defined $dbname;
    128129my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
    129 $dbname      = metadataLookupStr($siteConfig, 'DBNAME') unless defined $dbname;
    130130my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
    131131my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
    … …  
    133133
    134134my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
    135 
    136 my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
     135my %conn_attrs = (PrintError => 1, RaiseError => 1, AutoCommit => 0);
     136
     137my $dbh = DBI->connect_cached($dsn, $dbuser, $dbpass, \%conn_attrs)
     138        or die "Cannot connect to DB server\n";
     139
     140print STDERR "dbh: $dbh\n" if $verbose;
    137141
    138142my $fileset_dir = "$dsroot/$product/$fileset";
    … …  
    143147    # delete fileset
    144148    #
    145     my $stmt = $dbh->prepare("SELECT prod_id, last_fs FROM dsProduct WHERE prod_name = \'$product\'");
    146     $stmt->execute();
     149
     150    my $stmt = $dbh->prepare("SELECT prod_id, last_fs FROM dsProduct WHERE prod_name = ?");
     151    $stmt->execute($product);
     152
    147153    my $prod_row = $stmt->fetchrow_hashref();
    148154    my $prod_id = $prod_row->{prod_id};
    … …  
    152158        die("product $product not found\n");
    153159    }
    154     $stmt = $dbh->prepare("SELECT fileset_id,fileset_name FROM dsFileset WHERE fileset_name = '$fileset'" .
    155                         " AND prod_id = $prod_id");
    156 
    157     $stmt->execute();
     160    $stmt = $dbh->prepare("SELECT fileset_id,fileset_name FROM dsFileset" .
     161                                 " WHERE fileset_name = ? AND prod_id = $prod_id");
     162
     163    $stmt->execute($fileset);
    158164    my $fs_row = $stmt->fetchrow_hashref();
    159165
    … …  
    162168        exit 1;
    163169    }
     170    $stmt->finish();
     171
     172    my $fileset_id = $fs_row->{fileset_id};
     173
     174    eval {
     175        $dbh->do("DELETE from dsFile where fileset_id = $fileset_id");
     176        $dbh->do("DELETE from dsFileset where fileset_id = $fileset_id");
     177
     178        if ($old_last_fs eq $fs_row->{fileset_name}) {
     179            # print STDERR "deleting most recent fileset in $product\n";
     180
     181            $stmt = $dbh->prepare("SELECT fileset_name from dsFileset " .
     182                        "WHERE prod_id = $prod_id ORDER BY reg_time DESC LIMIT 1");
     183            $stmt->execute();
     184            my $new_last_fs;
     185            my $new_newest = $stmt->fetchrow_hashref();
     186            if ($new_newest) {
     187                $new_last_fs = $new_newest->{fileset_name};
     188            } else {
     189                $new_last_fs = "none";
     190            }
     191            $stmt->finish();
     192            $dbh->do("UPDATE dsProduct SET last_fs = \'$new_last_fs\' WHERE prod_id = $prod_id");
     193        }
     194        $dbh->commit();
     195    };
     196    if ($@) { # an error occured
     197        print STDERR "transaction failed, rolling back error was:\n$@\n";
     198        eval {$dbh->rollback();};
     199        cleanup();
     200        exit 1;
     201    }
     202
     203    $dbh->disconnect();
    164204
    165205    if ($remove) {
    … …  
    170210        # zap the index script
    171211        unlink("$index_script_name");
    172     }
    173 
    174     my $fileset_id = $fs_row->{fileset_id};
    175 
    176     $dbh->do("DELETE from dsFile where fileset_id = $fileset_id");
    177     $dbh->do("DELETE from dsFileset where fileset_id = $fileset_id");
    178 
    179     if ($old_last_fs eq $fs_row->{fileset_name}) {
    180         # print STDERR "deleting most recent fileset in $product\n";
    181 
    182         $stmt = $dbh->prepare("SELECT fileset_name from dsFileset " .
    183                     "WHERE prod_id = $prod_id ORDER BY reg_time DESC LIMIT 1");
    184         $stmt->execute();
    185         my $new_last_fs;
    186         my $new_newest = $stmt->fetchrow_hashref();
    187         if ($new_newest) {
    188             $new_last_fs = $new_newest->{fileset_name};
    189         } else {
    190             $new_last_fs = "none";
    191         }
    192         $dbh->do("UPDATE dsProduct SET last_fs = \'$new_last_fs\' WHERE prod_id = $prod_id");
    193212    }
    194213    exit 0;
    … …  
    203222    }
    204223
    205     my $stmt = $dbh->prepare("SELECT prod_id FROM dsProduct WHERE prod_name = \'$product\'");
    206     $stmt->execute();
     224    my $stmt = $dbh->prepare("SELECT prod_id FROM dsProduct WHERE prod_name = ?");
     225    $stmt->execute($product);
    207226    my $row = $stmt->fetchrow_hashref();
    208227    my $prod_id = $row->{prod_id};
     228    $stmt->finish();
    209229
    210230    if (!$prod_id) {
    211231        die("product $product not found");
    212232    }
    213     my $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = '$fileset'" .
    214                         " AND prod_id = $prod_id");
    215 
     233    my $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = ?" .
     234                        " AND prod_id = $prod_id", undef, ($fileset));
    216235    if ($count != 0E0) {
    217236        print "Fileset '$fileset' already exists under $product.\n";
    … …  
    222241    # fileset_name = $fileset. So later we'll check again that only one fileset with the name exists
    223242
    224     my $lineno = 0;
    225243
    226244    my $in;
    … …  
    231249    }
    232250
     251    my $lineno = 0;
    233252    my @files;
    234253    while (<$in>) {
     254        chomp;
    235255        $lineno++;
    236256
    237257        my $filename;
    238258        my $filetype;
    239         my $ts_string = "";
    240 
    241         chomp;
     259
    242260        my @fields = split /\|/;
    243261        if (@fields < 2) {
    244             die "malformed input line: $filelist line $lineno: $_\n";
     262            die "malformed input line at $filelist line $lineno: $_\n";
    245263        }
    246264        $filename = shift @fields;
    247265        $filetype = shift @fields;
    248266
    249         #
    250         # save any type specific fields provided in the string to be added to the
    251         #
    252         my $nfields = @fields;
    253         my $i;
    254         for ($i=0; $i < $nfields; $i++) {
    255             $ts_string .= ", \'$fields[$i]\'";
    256         }
    257         for ($i=$nfields ; $i < 4; $i++) {
    258             $ts_string .= ", NULL";
    259         }
     267        # make sure the length of the type specific columns is 8
     268        while (@fields < 8) {
     269            push @fields, undef;
     270        }
     271        my $ts_cols = [@fields];
     272
    260273        my $hashref = {
    261274            'file'      => $filename,
    262275            'type'      => $filetype,
    263             'ts_string' => $ts_string
     276            'ts_cols'   => $ts_cols,
    264277        };
    265278
    … …  
    300313                die("failed to remove $fileset_dir");
    301314            }
    302 
    303315            exit $PS_EXIT_UNKNOWN_ERROR;
    304316        }
    … …  
    322334
    323335
    324     # TODO: why do I do this before the database insert is complete?
    325     # create the cgi script index.txt by making a copy of its parent's
    326     if (!copy("$dsroot/$product/index.txt", $index_script_name)) {
    327         cleanup();
    328         die("failed to copy index script to file set");
     336    if (! $dbh->ping()) {
     337        # database connection has gone away, reconnect
     338        $dbh->disconnect();
     339        $dbh = DBI->connect_cached($dsn, $dbuser, $dbpass, \%conn_attrs)
     340            or die "Cannot connect to server\n";
     341        print STDERR "ping failed new dbh: $dbh\n" if $verbose;
    329342    }
    330343
    331344    eval {
    332         $dbh->{RaiseError} = 1; # raise exception if error occurs
    333         $dbh->{PrintError} = 1;
    334         $dbh->{AutoCommit} = 0;
    335 
    336         # note the resulting prodcolstr begins with a comma
    337         my $prodcolstr = make_prodcol_str($ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7);
    338 
    339345        my $qstring = "INSERT into dsFileset" .
    340346                " (prod_id, fileset_name, reg_time, type," .
    341347                " prod_col_0, prod_col_1, prod_col_2, prod_col_3, prod_col_4, prod_col_5, " .
    342348                " prod_col_6, prod_col_7)" .
    343                 " VALUES($prod_id, \'$fileset\', UTC_TIMESTAMP(), \'$fstype\' $prodcolstr)";
    344 
    345         $count = $dbh->do($qstring);
    346         if ($count == 0E0) {
    347             die("failed to insert $fileset");
    348         }
     349                " VALUES(?, ?, UTC_TIMESTAMP(), ?, ?, ?, ?, ?, ?, ?, ?, ?)";
     350
     351        $count = $dbh->do($qstring, undef, ($prod_id, $fileset, $fstype,
     352                            $ps0, $ps1, $ps2, $ps3, $ps4, $ps5, $ps6, $ps7));
     353
     354        die("failed to insert $fileset") if ($count == 0E0);
    349355
    350356        my $fileset_id = $dbh->last_insert_id(undef, undef, undef, undef);
    351357
    352358        # make sure that no-one got in and created a fileset with this name while we were busy
    353         $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = '$fileset'" .
    354                         " AND prod_id = $prod_id");
     359        $count = $dbh->do("SELECT fileset_id FROM dsFileset WHERE fileset_name = ?" .
     360                        " AND prod_id = $prod_id", undef, ($fileset));
    355361        if ($count > 1) {
    356362            die("Fileset '$fileset' already exists under $product.");
    … …  
    358364
    359365        foreach my $ref (@files) {
    360             # note: ts_string has a leading comma
    361             my $query = "INSERT INTO dsFile" .
    362                     " (fileset_id, file_id, file_name, bytes, md5sum, type, " .
    363                     " type_col_0, type_col_1, type_col_2, type_col_3)" .
    364 
    365                     " VALUES($fileset_id, 0, \'$ref->{file}\', $ref->{bytes}," .
    366                              " \'$ref->{md5sum}\', \'$ref->{type}\' $ref->{ts_string} )";
    367 
    368             $count = $dbh->do($query);
     366            my $query = "INSERT INTO dsFile" . 
     367                    " (fileset_id, file_id, file_name, bytes, md5sum, type," .
     368                    " type_col_0, type_col_1, type_col_2, type_col_3, type_col_4, type_col_5," .
     369                    " type_col_6, type_col_7)" .
     370                    " VALUES($fileset_id, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
     371
     372            my $aref = $ref->{ts_cols};
     373            my $do_args =  [($ref->{file}, $ref->{bytes}, $ref->{md5sum}, $ref->{type}), @$aref];
     374
     375            $count = $dbh->do($query, undef, @$do_args);
    369376
    370377            if ($count == 0E0) {
    371                 $dbh->rollback();
    372378                die("failed to insert $ref->{file} into $fileset");
    373379            }
    … …  
    377383                                . " WHERE prod_id = $prod_id");
    378384        if ($count == 0E0) {
    379             $dbh->rollback();
    380385            die("failed to update dsProduct $prod_id");
    381386        }
    382387
    383388        $dbh->commit();
     389
     390        # create the cgi script index.txt by making a copy of its parent's
     391        if (!copy("$dsroot/$product/index.txt", $index_script_name)) {
     392            die("failed to copy index script to file set");
     393        }
    384394    };
    385395    if ($@) { # an error occured
    386         print STDERR "transactionfailed, rolling back error was:\n$@\n";
     396        print STDERR "transaction failed, rolling back error was:\n$@\n";
    387397        eval {$dbh->rollback();};
    388398        cleanup();
    389399        exit 1;
    390400    }
     401
     402    print STDERR "calling disconnect before exit\n" if $verbose;
     403    $dbh->disconnect();
    391404
    392405    exit 0;
    … …  
    431444}
    432445
    433 sub make_prodcol_str {
    434 
    435     my @list = @_;
    436     my $string = "";
    437 
    438     foreach my $s (@list) {
    439         if ($s) {
    440             $string .= ", \'$s\'";
    441         } else {
    442             $string .= ", NULL";
    443         }
    444     }
    445 
    446     return $string;
    447 }
    448 
    449446sub cleanup {
    450447    if (!$no_cleanup && ($linkfiles or $copyfiles)) {
Note: See TracChangeset for help on using the changeset viewer.