/[webpac]/trunk/index_DBI.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

Annotation of /trunk/index_DBI.pm

Parent Directory Parent Directory | Revision Log Revision Log


Revision 11 - (hide annotations)
Wed Jan 22 20:24:32 2003 UTC (16 years, 10 months ago) by dpavlin
File size: 3399 byte(s)
display index (without pager for now)

1 dpavlin 10 #
2     # this file implements index functions using DBI
3     #
4    
5     package index_DBI;
6     use strict qw(vars);
7     use vars qw($Count);
8    
9     use DBI;
10    
11     my %Table; # index tables which where visited in this run
12    
13     sub new {
14     my $class = shift;
15     my $self = {};
16     bless($self, $class);
17    
18     # FIX: config params
19 dpavlin 11 $self->{dbh} = DBI->connect("DBI:Pg:dbname=webpac","dpavlin","") || die $DBI::errstr;
20 dpavlin 10 # begin transaction
21     $self->{dbh}->begin_work || die $self->{dbh}->errstr();
22    
23     $Count++;
24    
25     return $self;
26     }
27    
28     sub delete_and_create {
29     my $self = shift;
30    
31     my $field = shift;
32    
33     $self->{dbh}->commit;
34     $self->{dbh}->begin_work;
35    
36     my $sql = "select count(*) from $field";
37     my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
38     # FIX: this is not a good way to check if table exists!
39     if (1 || $sth->execute() && $sth->fetchrow_hashref) {
40     my $sql = "drop table $field";
41     # my $sth = $self->{dbh}->do($sql) || die "SQL: $sql ".$self->{dbh}->errstr();
42     }
43     $sql = "create table $field (
44     item varchar(255),
45     ident varchar(255),
46     count int,
47     primary key (item,ident)
48     )";
49     # $sth = $self->{dbh}->do($sql) || die "SQL: $sql ".$self->{dbh}->errstr();
50     $sth = $self->{dbh}->do($sql) || warn "SQL: $sql ".$self->{dbh}->errstr();
51    
52     $self->{dbh}->commit;
53     $self->{dbh}->begin_work;
54     }
55    
56     sub insert {
57     my $self = shift;
58    
59     my $field = shift;
60     my $index_data = shift;
61 dpavlin 11 my $ident = shift; # e.g. library id
62 dpavlin 10
63     if (! $index_data) {
64     print STDERR "\$index->insert() -- no value to insert\n";
65     return;
66     }
67    
68     if (! $Table{$field}) {
69     $self->delete_and_create($field);
70     }
71     $Table{$field}++;
72    
73     my $sql = "select item from $field where upper(item)=upper(?)";
74     my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
75 dpavlin 11 $sth->execute($index_data) || die "sql: $sql; ".$self->{dbh}->errstr();
76 dpavlin 10 if (! $sth->fetchrow_hashref) {
77     my $sql = "insert into $field (item,ident,count) values (?,?,?)";
78     my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
79 dpavlin 11 $sth->execute($index_data,$ident,1) || die "sql: $sql; ".$self->{dbh}->errstr();
80     #print stderr "in index: $index_data\n";
81 dpavlin 10 } else {
82     my $sql = "update $field set count = count + 1 where item = ? and ident = ?";
83     my $sth = $self->{dbh}->prepare($sql) || die $self->{dbh}->errstr();
84 dpavlin 11 $sth->execute($index_data,$ident) || die "sql: $sql; ".$self->{dbh}->errstr();
85 dpavlin 10 }
86     }
87    
88 dpavlin 11 sub check {
89     my $self = shift;
90    
91     my $field = shift;
92    
93     my $sql = "select count(*) from $field";
94     return $self->{dbh}->do($sql);
95     }
96    
97    
98     sub fetch {
99     my $self = shift;
100    
101     my $field = shift;
102     my $what = shift || 'item'; # 'item,ident'
103     my $where = shift;
104    
105     my @sql_args;
106    
107     my $sql = "select $what from $field";
108     if ($where) {
109     $sql .= " where upper(item) like upper(?)||'%'";
110     push @sql_args,$where;
111     }
112     $sql .= " order by item";
113    
114     my $sth = $self->{dbh}->prepare($sql) || die "sql: $sql; ".$self->{dbh}->errstr();
115     $sth->execute(@sql_args) || die "sql: $sql; ".$self->{dbh}->errstr();
116     my @arr;
117     while (my $row = $sth->fetchrow_hashref) {
118     push @arr,$row;
119     }
120     return @arr;
121     }
122    
123 dpavlin 10 sub close {
124     my $self = shift;
125    
126     if ($self->{dbh}) {
127     $self->{dbh}->commit || die $self->{dbh}->errstr();
128     $self->{dbh}->disconnect;
129     undef $self->{dbh};
130     }
131     }
132    
133     END {
134     $Count--;
135 dpavlin 11 print STDERR "index_DBI fatal error: \$index->close() not called... $Count references left!\n" if ($Count > 0);
136 dpavlin 10 # FIX: debug output
137 dpavlin 11 # print STDERR "usage\ttable\n";
138     # foreach (keys %Table) {
139     # print STDERR $Table{$_},"\t$_\n";
140     # }
141 dpavlin 10 }
142    
143     1;

Properties

Name Value
cvs2svn:cvs-rev 1.2

  ViewVC Help
Powered by ViewVC 1.1.26