re-wrote module to use in-memory sorting

[webpac] / index_DBI_cache.pm

diff --git a/index_DBI_cache.pm b/index_DBI_cache.pm
index 97dd913..ec3c038 100644 (file)
--- a/index_DBI_cache.pm
+++ b/index_DBI_cache.pm
@@ -2,6 +2,9 @@
 # this file implements index functions using DBI
 # and huge amounts of memory for cache speedup
 #
+# this version doesn't support ident (which sould be location in
+# library). But, that functionality is not used anyway...
+#
 
 package index_DBI;
 use strict qw(vars);
@@ -18,7 +21,16 @@ my $c_table;
 my $c_count;
 
 # bench time
-my $t = time();
+my $bench_time = time();
+
+sub bench {
+       my $self = shift;
+       my $msg = shift;
+
+       print STDERR "last operation took ",time()-$bench_time," seconds...\n";
+       $bench_time=time();
+       print STDERR "$msg\n";
+}
 
 sub new {
        my $class = shift;
@@ -31,11 +43,10 @@ sub new {
        my $passwd = shift || die "need dbi_passwd= in [global] section of configuration file";
 
        $self->{dbh} = DBI->connect("DBI:$dbd:$dsn",$user,$passwd) || die $DBI::errstr;
-       # begin transaction
-       $self->{dbh}->begin_work || die $self->{dbh}->errstr();
-
        $Count++;
 
+       $self->bench("connected to $dbd as $user");
+
        return $self;
 }
 
@@ -46,30 +57,21 @@ sub delete_and_create {
 
 #print "#### delete_and_create($field)\n";
 
-       $self->{dbh}->commit;
-
        my $sql = "select count(*) from $field";
        my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
 # FIX: this is not a good way to check if table exists!
        if ($sth->execute() && $sth->fetchrow_hashref) {
                my $sql = "drop table $field";
-               $self->{dbh}->begin_work;
                my $sth = $self->{dbh}->do($sql) || die "SQL: $sql ".$self->{dbh}->errstr();
-               $self->{dbh}->commit;
        }
        $sql = "create table $field (
                        item varchar(255),
-                       ident varchar(255),
                        count int,
                        ord int,
-                       primary key (item,ident)
+                       primary key (item)
                )";
 
-       $self->{dbh}->begin_work;
-       $sth = $self->{dbh}->do($sql); # || warn "SQL: $sql ".$self->{dbh}->errstr();
-       $self->{dbh}->commit;
-
-       $self->{dbh}->begin_work;
+       $sth = $self->{dbh}->do($sql) || warn "SQL: $sql ".$self->{dbh}->errstr();
 }
 
 sub insert {
@@ -84,18 +86,6 @@ sub insert {
                return;
        }
 
-       if (! $Table{$field}) {
-               $self->delete_and_create($field);
-
-               my $sql = "select item from $field where upper(item)=upper(?)";
-               $sth_cache{$field."select"} = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
-
-               $sql = "insert into $field (item,ident,count) values (?,?,?)";
-               $sth_cache{$field."insert"} = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
-
-               $sql = "update $field set count = count + 1 where item = ? and ident = ?";
-               $sth_cache{$field."update"} = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
-       }
        $Table{$field}++;
 
        #$sth_cache{$field."select"}->execute($index_data) || die "cache: $field select; ".$self->{dbh}->errstr();
@@ -108,13 +98,12 @@ sub insert {
        $index_data = substr($index_data,0,255);
 
        my $uc = uc($index_data);
-       if (! $c_table->{$field}->{$ident}->{$uc}) {
-               $sth_cache{$field."insert"}->execute($index_data,$ident,0) || warn "cache: $field insert ($index_data,$ident); ".$self->{dbh}->errstr();
+       if (! $c_table->{$field}->{$uc}) {
 #print stderr "in index: $index_data\n";
-               $c_table->{$field}->{$ident}->{$uc} = $index_data;
-               $c_count->{$field}->{$ident}->{$uc} = 1;
+               $c_table->{$field}->{$uc} = $index_data;
+               $c_count->{$field}->{$uc} = 1;
        } else {
-               $c_count->{$field}->{$ident}->{$uc}++;
+               $c_count->{$field}->{$uc}++;
        }
 }
 
@@ -172,49 +161,35 @@ sub fetch {
 sub close {
        my $self = shift;
 
+       return if (! $self->{dbh});
 
-       # re-create ord column (sorted order) in table
-       sub create_ord {
-
-               my $table = shift;
+       foreach my $table (keys %Table) {
+               $self->bench("Crating table $table");
+               $self->delete_and_create($table);
 
                $self->{dbh}->begin_work || die $self->{dbh}->errstr();
 
-               my $sql = "select oid from $table order by upper(item)";
+               $self->bench("Sorting ".$Table{$table}." items in $table");
+               my @keys = sort keys %{$c_table->{$table}};
+
+               $self->bench("Dumping data into $table");
+               my $sql = "insert into $table (ord,item,count) values (?,?,?)";
                my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();
-               $sql = "update $table set ord=? where oid=?";
-               my $sth_update = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();
-               $sth->execute() || die "sql: $sql; ".$self->{dbh}->errstr();
-               my $ord = 1;
-               while (my $row = $sth->fetchrow_hashref) {
-                       $sth_update->execute($ord++,$row->{oid});
+
+               my $ord = 0;
+               foreach my $key (@keys) {
+                       $sth->execute($ord++,
+                               $c_table->{$table}->{$key},
+                               $c_count->{$table}->{$key}
+                       );
                }
 
                $self->{dbh}->commit || die $self->{dbh}->errstr();
        }
-       #--- end of sub
-
-       if ($self->{dbh}) {
+       $self->bench("disconnecting from database");
 
-               # commit
-               $self->{dbh}->commit || die $self->{dbh}->errstr();
-
-               foreach my $table (keys %Table) {
-# FIX
-print STDERR "last operation took ",time()-$t," seconds...\n";
-$t=time();
-print STDERR "creating ord for $table...\n";
-                       create_ord($table);
-                       undef $sth_cache{$table."select"};
-                       undef $sth_cache{$table."insert"};
-                       undef $sth_cache{$table."update"};
-# XXX
-#              $sth_cache{$field."update"}->execute($index_data,$ident) || die "cache: $field update; ".$self->{dbh}->errstr();
-               }
-
-               $self->{dbh}->disconnect;
-               undef $self->{dbh};
-       }
+       $self->{dbh}->disconnect;
+       undef $self->{dbh};
 }
 
 END {