perform loaddata.pl updates in a transaction per table, to speed it up

diff --git a/site/cgi-bin/modules/BSE/DB.pm b/site/cgi-bin/modules/BSE/DB.pm
index 8071ae4..b627664 100644 (file)
--- a/site/cgi-bin/modules/BSE/DB.pm
+++ b/site/cgi-bin/modules/BSE/DB.pm
@@ -5,7 +5,7 @@ use Carp qw(croak);
 use Carp qw/confess/;
 use DBI::Const::GetInfoType;
 
-our $VERSION = "1.002";
+our $VERSION = "1.003";
 
 my $single;
 
@@ -258,8 +258,16 @@ sub auto_commit {
   $self->dbh->{AutoCommit} = $value;
 }
 
+sub begin_work {
+  my ($self) = @_;
+
+  $self = BSE::DB->single unless ref $self;
+
+  $self->dbh->begin_work;
+}
+
 sub commit {
-  my ($self, $value) = @_;
+  my ($self) = @_;
 
   $self = BSE::DB->single unless ref $self;
 
@@ -277,19 +285,20 @@ sub rollback {
 sub do_txn {
   my ($self, $code) = @_;
 
-  $self->auto_commit(0);
   eval {
+    $self->begin_work;
     $code->();
     $self->commit;
-    $self->auto_commit(1);
     1;
-  }
-    and return 1;
-  my $error = $@;
+  } or do {
+    my $error = $@;
+
+    $self->rollback;
+    $self->auto_commit(1);
+    die $error;
+  };
 
-  $self->rollback;
-  $self->auto_commit(1);
-  die $error;
+  return 1;
 }
 
 1;

diff --git a/site/util/loaddata.pl b/site/util/loaddata.pl
index d4d886a..c677ade 100644 (file)
--- a/site/util/loaddata.pl
+++ b/site/util/loaddata.pl
@@ -16,14 +16,16 @@ my $datadir = shift
   or die "Usage: $0 directoryname\n";
 
 # this is pretty rough, but good enough for now
-my $dbh = BSE::DB::single->dbh
+my $db = BSE::DB::single
   or die "Cannot connect to database: ",DBI->errstr;
 
+my $dbh = $db->dbh;
+
 my $dbuser = BSE::DB->dbuser;
 
 my %tables;
-opendir DATADIR, $datadir or die "Cannot open '$datadir' directory: $!";
-while (my $inname = readdir DATADIR) {
+opendir my $dh, $datadir or die "Cannot open '$datadir' directory: $!";
+while (my $inname = readdir $dh) {
   (my $table_name = $inname) =~ s/\.data$//
     or next;
 
@@ -32,41 +34,44 @@ while (my $inname = readdir DATADIR) {
   my @pkey = $dbh->primary_key(undef, $dbuser, $table_name);
   unless (@pkey) {
     # look for a file with it
-    open PKEY, "< $datadir/$table_name.pkey"
+    my $pkey_filename = "$datadir/$table_name.pkey";
+    open my $pkeyfh, "<", $pkey_filename
       or die "No primary key info found for $table_name";
-    @pkey = <PKEY>;
+    @pkey = <$pkeyfh>;
     chomp @pkey;
     @pkey = grep /\S/, @pkey;
-    close PKEY;
+    @pkey
+      or die "No primary key data found in $pkey_filename\n";
+    close $pkeyfh;
   }
 
   my $del_sql = "delete from $table_name where "
     . join(" and ", map "$_ = ?", @pkey);
 
-  open DATA, "< $datadir/$inname"
+  open my $fh, "<", "$datadir/$inname"
     or die "Cannot open $datadir/$inname: $!";
 
-  my $datafile = DevHelp::LoaderData->new(\*DATA)
+  my $datafile = DevHelp::LoaderData->new($fh)
     or die;
 
-  while (my $row = $datafile->read) {
-    #for my $pkey_col (@pkey) {
-    #  unless (exists $row->{$pkey_col}) {
-    #  die "Missing value for $pkey_col in record ending $. of $inname\n";
-    #  }
-    #}
-    defined($dbh->do($del_sql, {}, @{$row}{@pkey}))
-      or die "Error deleting old record: ", DBI->errstr;
-
-    my $add_sql = "insert into $table_name(" .
-      join(",", map $dbh->quote_identifier($_), keys %$row) . ") values (".
-       join(",", ("?") x keys %$row) . ")";
-    defined($dbh->do($add_sql, {}, values %$row))
-      or die "Error adding new record ($add_sql): ", DBI->errstr;
-  }
-  
-  close DATA;
+  $db->do_txn
+    (
+     sub {
+       while (my $row = $datafile->read) {
+        defined($dbh->do($del_sql, {}, @{$row}{@pkey}))
+          or die "Error deleting old record: ", DBI->errstr;
+
+        my $add_sql = "insert into $table_name(" .
+          join(",", map $dbh->quote_identifier($_), keys %$row) . ") values (".
+            join(",", ("?") x keys %$row) . ")";
+        defined($dbh->do($add_sql, {}, values %$row))
+          or die "Error adding new record ($add_sql): ", DBI->errstr;
+       }
+     }
+    );
+
+  close $fh;
 }
-close DATADIR;
+close $dh;
 
 $dbh->disconnect;