/[webpac]/trunk2/lib/WebPAC/Index.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 /trunk2/lib/WebPAC/Index.pm

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

trunk/index_DBI_cache.pm revision 127 by dpavlin, Thu Sep 4 14:56:41 2003 UTC trunk2/lib/WebPAC/Index.pm revision 354 by dpavlin, Wed Jun 16 11:31:42 2004 UTC
# Line 6  Line 6 
6  # library). But, that functionality is not used anyway...  # library). But, that functionality is not used anyway...
7  #  #
8    
9  package index_DBI;  package WebPAC::Index;
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    use Carp;
16    
17  use DBI;  use DBI;
18    
# Line 34  sub bench { Line 37  sub bench {
37    
38  sub new {  sub new {
39          my $class = shift;          my $class = shift;
40          my $self = {};          my $self = {@_};
41          bless($self, $class);          bless($self, $class);
42    
43          my $dbd = shift || die "need dbi_dbd= in [global] section of configuration file";          croak "need dbd" if (! $self->{dbd});
44          my $dsn = shift || die "need dbi_dsn= in [global] section of configuration file";          croak "need dsn" if (! $self->{dsn});
45          my $user = shift || die "need dbi_user= in [global] section of configuration file";          croak "need user" if (! $self->{user});
46          my $passwd = shift || die "need dbi_passwd= in [global] section of configuration file";          croak "need passwd" if (! $self->{passwd});
47    
48          $self->{dbh} = DBI->connect("DBI:$dbd:$dsn",$user,$passwd) || die $DBI::errstr;          $self->{dbh} = DBI->connect("DBI:$self->{dbd}:$self->{dsn}",$self->{user},$self->{passwd}) || die $DBI::errstr;
49          $Count++;          $Count++;
50    
51          $self->bench("connected to $dbd as $user");          $self->bench("connected to $self->{dbd} as $self->{user}");
52    
53            # force SQLite to support binary 0 in data (which shouldn't
54            # happend, but it did to me)
55            eval {
56                    no warnings 'all';
57                    $self->{dbh}->{sqlite_handle_binary_nulls} = 1;
58            };
59    
60          return $self;          return $self;
61  }  }
# Line 62  sub delete_and_create { Line 72  sub delete_and_create {
72  # FIX: this is not a good way to check if table exists!  # FIX: this is not a good way to check if table exists!
73          if ($sth->execute() && $sth->fetchrow_hashref) {          if ($sth->execute() && $sth->fetchrow_hashref) {
74                  my $sql = "drop table $field";                  my $sql = "drop table $field";
75                  my $sth = $self->{dbh}->do($sql) || die "SQL: $sql ".$self->{dbh}->errstr();                  my $sth = $self->{dbh}->do($sql) || warn "SQL: $sql - ".$sth->errstr();
76          }          }
77          $sql = "create table $field (          $sql = "create table $field (
78                          item varchar(255),                          item varchar(255),
79                            display text,
80                          count int,                          count int,
81                          ord int,                          ord int,
82                          primary key (item)                          primary key (item)
# Line 79  sub insert { Line 90  sub insert {
90    
91          my $field = shift;          my $field = shift;
92          my $index_data = shift || print STDERR "\$index->insert($field,NULL,...)";          my $index_data = shift || print STDERR "\$index->insert($field,NULL,...)";
93          my $ident = shift || '';        # e.g. library id          my $display = shift || $index_data;
94    
95          if (! $index_data) {          if (! $index_data) {
96                  print STDERR "\$index->insert() -- no value to insert\n";                  print STDERR "\$index->insert() -- no value to insert\n";
# Line 101  sub insert { Line 112  sub insert {
112          if (! $c_table->{$field}->{$uc}) {          if (! $c_table->{$field}->{$uc}) {
113  #print stderr "in index: $index_data\n";  #print stderr "in index: $index_data\n";
114                  $c_table->{$field}->{$uc} = $index_data;                  $c_table->{$field}->{$uc} = $index_data;
115                    $c_table->{$field}->{$uc}->{display} = $display;
116                  $c_count->{$field}->{$uc} = 1;                  $c_count->{$field}->{$uc} = 1;
117          } else {          } else {
118                  $c_count->{$field}->{$uc}++;                  $c_count->{$field}->{$uc}++;
119          }          }
120  }  }
121    
122  sub check {  sub count {
123          my $self = shift;          my $self = shift;
124    
125          my $field = shift;          my $field = shift;
126            my $where = shift;
127    
128          my $sql = "select count(*) from $field";          my $sql = "select count(*) from $field where upper(item) like upper(?)||'%'";
129    
130          my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();          my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
131          $sth->execute() || die "sql: $sql; ".$self->{dbh}->errstr();          $sth->execute($where) || die "sql: $sql; ".$self->{dbh}->errstr();
132    
133          my ($total) = $sth->fetchrow_array();          my ($total) = $sth->fetchrow_array();
134    
135          return $total;          # no results, count all
136            if (! $total) {
137                    my $sql = "select count(*) from $field";
138    
139                    my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
140                    $sth->execute() || die "sql: $sql; ".$self->{dbh}->errstr();
141                    $total = $sth->fetchrow_array();
142    
143            }
144    
145            return $total || 1;
146  }  }
147    
148    
# Line 127  sub fetch { Line 150  sub fetch {
150          my $self = shift;          my $self = shift;
151    
152          my $field = shift;          my $field = shift;
         my $what = shift || 'item';     # 'item,ident'  
153          my $where = shift;          my $where = shift;
154    
155          my $from_ord = shift || 0;          my $from_ord = shift || 0;
# Line 135  sub fetch { Line 157  sub fetch {
157    
158          my @sql_args;          my @sql_args;
159    
160          my $sql = "select $what,ord from $field";          my $sql = "select item,display,ord from $field";
161    
162          if ($where) {          if ($where) {
163                  my $sql2 = "select ord from $field where upper($what) like upper(?)||'%'";                  my $sql2 = "select ord from $field where upper(item) like upper(?)||'%'";
164                  my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();                  my $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();
165    
166                  $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();                  $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();
# Line 147  sub fetch { Line 169  sub fetch {
169                  } else {                  } else {
170                          # if no match is found when searching from beginning                          # if no match is found when searching from beginning
171                          # of word in index, try substring match anywhere                          # of word in index, try substring match anywhere
172                          $sql2 = "select ord from $field where upper($what) like '%'||upper(?)||'%'";                          $sql2 = "select ord from $field where upper(item) like '% '||upper(?)||'%'";
173                          $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();                          $sth = $self->{dbh}->prepare($sql2) || die "sql2: $sql2; ".$self->{dbh}->errstr();
174                          $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();                          $sth->execute($where) || die "sql2: $sql2; ".$self->{dbh}->errstr();
175                          if (my $row = $sth->fetchrow_hashref) {                          if (my $row = $sth->fetchrow_hashref) {
# Line 161  sub fetch { Line 183  sub fetch {
183          $sth->execute() || die "execute: $sql; ".$self->{dbh}->errstr();          $sth->execute() || die "execute: $sql; ".$self->{dbh}->errstr();
184          my @arr;          my @arr;
185          while (my $row = $sth->fetchrow_hashref) {          while (my $row = $sth->fetchrow_hashref) {
186                  $row->{item} = HTML::Entities::encode($row->{item},'<>&"');                  $row->{item} = HTML::Entities::encode($row->{item},' <>&"');
187                    $row->{display} = HTML::Entities::encode($row->{display},'<>&"');
188                  push @arr,$row;                  push @arr,$row;
189          }          }
190          return @arr;          return @arr;
# Line 178  sub close { Line 201  sub close {
201    
202                  $self->{dbh}->begin_work || die $self->{dbh}->errstr();                  $self->{dbh}->begin_work || die $self->{dbh}->errstr();
203    
204                  $self->bench("Sorting ".$Table{$table}." items in $table");                  $self->bench("Sorting ".$Table{$table}." (with duplicates) items in $table");
205                  my @keys = sort keys %{$c_table->{$table}};                  my @keys = sort keys %{$c_table->{$table}};
206    
207                  $self->bench("Dumping data into $table");                  $self->bench("Dumping ".($#keys+1)." items into $table");
208                  my $sql = "insert into $table (ord,item,count) values (?,?,?)";                  my $sql = "insert into $table (ord,item,display,count) values (?,?,?,?)";
209                  my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();                  my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();
210    
211                  my $ord = 0;                  my $ord = 0;
212                  foreach my $key (@keys) {                  foreach my $key (@keys) {
213                          $sth->execute(++$ord,                          $sth->execute(++$ord,
214                                  $c_table->{$table}->{$key},                                  $c_table->{$table}->{$key},
215                                    $c_table->{$table}->{$key}->{display},
216                                  $c_count->{$table}->{$key}                                  $c_count->{$table}->{$key}
217                          );                          );
218                  }                  }
219    
220                  $self->{dbh}->commit || die $self->{dbh}->errstr();                  $self->{dbh}->commit || die $self->{dbh}->errstr();
221          }          }
222    
223            if ($self->{dbd} =~ m/(Pg|SQLite)/) {
224                    $self->{dbh}->do(qq{vacuum}) || warn "vacumming failed. It shouldn't if you are using PostgreSQL or SQLite: ".$self->{dbh}->errstr();
225            }
226    
227          $self->bench("disconnecting from database");          $self->bench("disconnecting from database");
228    
229          $self->{dbh}->disconnect;          $self->{dbh}->disconnect;

Legend:
Removed from v.127  
changed lines
  Added in v.354

  ViewVC Help
Powered by ViewVC 1.1.26