/[webpac]/branches/hidra/index_DBI_cache.pm
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /branches/hidra/index_DBI_cache.pm

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

trunk/index_DBI_cache.pm revision 140 by dpavlin, Thu Oct 30 00:10:09 2003 UTC branches/hidra/index_DBI_cache.pm revision 633 by dpavlin, Sun Jan 16 18:57:15 2005 UTC
# Line 10  package index_DBI; Line 10  package index_DBI;
10  use strict qw(vars);  use strict qw(vars);
11  use vars qw($Count);  use vars qw($Count);
12  use HTML::Entities;  use HTML::Entities;
13    use URI::Escape;
14    use locale;
15    
16  use DBI;  use DBI;
17    
# Line 42  sub new { Line 44  sub new {
44          my $user = shift || die "need dbi_user= in [global] section of configuration file";          my $user = shift || die "need dbi_user= in [global] section of configuration file";
45          my $passwd = shift || die "need dbi_passwd= in [global] section of configuration file";          my $passwd = shift || die "need dbi_passwd= in [global] section of configuration file";
46    
47            $self->{dbd} = $dbd;
48    
49          $self->{dbh} = DBI->connect("DBI:$dbd:$dsn",$user,$passwd) || die $DBI::errstr;          $self->{dbh} = DBI->connect("DBI:$dbd:$dsn",$user,$passwd) || die $DBI::errstr;
50          $Count++;          $Count++;
51    
52          $self->bench("connected to $dbd as $user");          $self->bench("connected to $dbd as $user");
53    
54            # force SQLite to support binary 0 in data (which shouldn't
55            # happend, but it did to me)
56            eval {
57                    no warnings 'all';
58                    $self->{dbh}->{sqlite_handle_binary_nulls} = 1;
59            };
60    
61          return $self;          return $self;
62  }  }
63    
# Line 58  sub delete_and_create { Line 69  sub delete_and_create {
69  #print "#### delete_and_create($field)\n";  #print "#### delete_and_create($field)\n";
70    
71          my $sql = "select count(*) from $field";          my $sql = "select count(*) from $field";
72          my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();          my $sth = $self->{dbh}->prepare($sql);
73  # FIX: this is not a good way to check if table exists!  # FIX: this is not a good way to check if table exists!
74          if ($sth->execute() && $sth->fetchrow_hashref) {          if ($sth && $sth->execute() && $sth->fetchrow_hashref) {
75                  my $sql = "drop table $field";                  my $sql = "drop table $field";
76                  my $sth = $self->{dbh}->do($sql) || die "SQL: $sql ".$self->{dbh}->errstr();                  my $sth = $self->{dbh}->do($sql) || warn "SQL: $sql - ".$sth->errstr();
77          }          }
78          $sql = "create table $field (          $sql = "create table $field (
79                          item varchar(255),                          item varchar(255),
80                            display text,
81                          count int,                          count int,
82                          ord int,                          ord int,
83                          primary key (item)                          primary key (item)
# Line 79  sub insert { Line 91  sub insert {
91    
92          my $field = shift;          my $field = shift;
93          my $index_data = shift || print STDERR "\$index->insert($field,NULL,...)";          my $index_data = shift || print STDERR "\$index->insert($field,NULL,...)";
94          my $ident = shift || '';        # e.g. library id          my $display = shift || $index_data;
95    
96          if (! $index_data) {          if (! $index_data) {
97                  print STDERR "\$index->insert() -- no value to insert\n";                  print STDERR "\$index->insert() -- no value to insert\n";
# Line 90  sub insert { Line 102  sub insert {
102    
103          #$sth_cache{$field."select"}->execute($index_data) || die "cache: $field select; ".$self->{dbh}->errstr();          #$sth_cache{$field."select"}->execute($index_data) || die "cache: $field select; ".$self->{dbh}->errstr();
104    
105          # XXX for some strange reason, it seems that some entries in my          $index_data =~ s#&(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#$1#gi;
         # database produce strings which start with null byte. I suspect  
         # this to be bug in OpenIsis 0.9.0.  
         # This should fix it..  
         $index_data =~ s/^[^\w]+//;  
         $index_data = substr($index_data,0,255);  
106    
107          my $uc = uc($index_data);          my $uc = uc($index_data);
108          if (! $c_table->{$field}->{$uc}) {          if (! $c_table->{$field}->{$uc}) {
109  #print stderr "in index: $index_data\n";  #print stderr "in index: $index_data\n";
110                  $c_table->{$field}->{$uc} = $index_data;                  $c_table->{$field}->{$uc} = $index_data;
111                    $c_table->{$field}->{$uc}->{display} = $display;
112                  $c_count->{$field}->{$uc} = 1;                  $c_count->{$field}->{$uc} = 1;
113          } else {          } else {
114                  $c_count->{$field}->{$uc}++;                  $c_count->{$field}->{$uc}++;
# Line 120  sub count { Line 128  sub count {
128    
129          my ($total) = $sth->fetchrow_array();          my ($total) = $sth->fetchrow_array();
130    
131          return $total;          # no results, count all
132            if (! $total) {
133                    my $sql = "select count(*) from $field";
134    
135                    my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
136                    $sth->execute() || die "sql: $sql; ".$self->{dbh}->errstr();
137                    $total = $sth->fetchrow_array();
138    
139            }
140    
141            return $total || 1;
142  }  }
143    
144    
# Line 135  sub fetch { Line 153  sub fetch {
153    
154          my @sql_args;          my @sql_args;
155    
156          my $sql = "select item,ord from $field";          my $sql = "select item,display,ord from $field";
157    
158          if ($where) {          if ($where) {
159                  my $sql2 = "select ord from $field where upper(item) like upper(?)||'%'";                  my $sql2 = "select ord from $field where upper(item) like upper(?)||'%'";
# Line 147  sub fetch { Line 165  sub fetch {
165                  } else {                  } else {
166                          # if no match is found when searching from beginning                          # if no match is found when searching from beginning
167                          # of word in index, try substring match anywhere                          # of word in index, try substring match anywhere
168                          $sql2 = "select ord from $field where upper(item) like '%'||upper(?)||'%'";                          $sql2 = "select ord from $field where upper(item) like '% '||upper(?)||'%'";
169                          $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();                          $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();
170                          $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();                          $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();
171                          if (my $row = $sth->fetchrow_hashref) {                          if (my $row = $sth->fetchrow_hashref) {
# Line 161  sub fetch { Line 179  sub fetch {
179          $sth->execute() || die "execute: $sql; ".$self->{dbh}->errstr();          $sth->execute() || die "execute: $sql; ".$self->{dbh}->errstr();
180          my @arr;          my @arr;
181          while (my $row = $sth->fetchrow_hashref) {          while (my $row = $sth->fetchrow_hashref) {
182                  $row->{item} = HTML::Entities::encode($row->{item},'<>&"');                  $row->{item} = HTML::Entities::encode($row->{item},' <>&"');
183                    $row->{display} = HTML::Entities::encode($row->{display},'<>&"');
184                    $row->{item} =~ s#&amp;(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#$1#gi;
185                    $row->{display} =~ s#&amp;(\w)(acute|cedil|circ|grave|ring|slash|tilde|uml);#&$1$2;#gi;
186                  push @arr,$row;                  push @arr,$row;
187          }          }
188          return @arr;          return @arr;
# Line 178  sub close { Line 199  sub close {
199    
200                  $self->{dbh}->begin_work || die $self->{dbh}->errstr();                  $self->{dbh}->begin_work || die $self->{dbh}->errstr();
201    
202                  $self->bench("Sorting ".$Table{$table}." items in $table");                  $self->bench("Sorting ".$Table{$table}." (with duplicates) items in $table");
203                  my @keys = sort keys %{$c_table->{$table}};                  my @keys = sort keys %{$c_table->{$table}};
204    
205                  $self->bench("Dumping data into $table");                  $self->bench("Dumping ".($#keys+1)." items into $table");
206                  my $sql = "insert into $table (ord,item,count) values (?,?,?)";                  my $sql = "insert into $table (ord,item,display,count) values (?,?,?,?)";
207                  my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();                  my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();
208    
209                  my $ord = 0;                  my $ord = 0;
210                  foreach my $key (@keys) {                  foreach my $key (@keys) {
211                          $sth->execute(++$ord,                          $sth->execute(++$ord,
212                                  $c_table->{$table}->{$key},                                  $c_table->{$table}->{$key},
213                                    $c_table->{$table}->{$key}->{display},
214                                  $c_count->{$table}->{$key}                                  $c_count->{$table}->{$key}
215                          );                          );
216                  }                  }
217    
218                  $self->{dbh}->commit || die $self->{dbh}->errstr();                  $self->{dbh}->commit || die $self->{dbh}->errstr();
219          }          }
220    
221            if ($self->{dbd} =~ m/(Pg|SQLite)/) {
222                    $self->{dbh}->do(qq{vacuum}) || warn "vacumming failed. It shouldn't if you are using PostgreSQL or SQLite: ".$self->{dbh}->errstr();
223            }
224    
225          $self->bench("disconnecting from database");          $self->bench("disconnecting from database");
226    
227          $self->{dbh}->disconnect;          $self->{dbh}->disconnect;
Legend:
Removed from v.140  
changed lines
  Added in v.633
  ViewVC Help
Powered by ViewVC 1.1.26