1 package Squirrel::Table;
11 my $dh = BSE::DB->single;
16 # no caching is performed if this is zero
17 my $cache_timeout = 2; # seconds
19 # cache of loaded tables
20 # this prevents us from reloading the table so often
21 # key is the table class name, value is a hash ref with two keys:
22 # table - the table object
23 # when - time value when the object was created.
24 # Table::add() invalidates the cache for the given class
28 my ($class, $nocache) = @_;
30 return $cache{$class}{table}
32 && exists $cache{$class}
33 && defined $cache{$class}{time}
34 && $cache{$class}{time}+$cache_timeout >= time;
36 my $sth = $dh->stmt($class)
37 or confess "No $class member in DatabaseHandle";
39 or confess "Cannot execute $class handle from DatabaseHandle:",DBI->errstr;
43 my $rowClass = $class->rowClass;
44 (my $reqName = $rowClass) =~ s!::!/!g;
45 require $reqName.".pm";
46 while (my $row = $sth->fetchrow_arrayref) {
47 my $item = $rowClass->new(@$row);
48 $coll{$item->{pkey}} = $item;
53 my $result = bless { ptr=>-1, coll=>\%coll, order=>\@order }, $class;
56 $cache{$class}{table} = $result;
57 $cache{$class}{when} = time;
66 ++$self->{ptr} >= @{$self->{order}};
71 return $self->{order}[$self->{ptr}];
75 my ($self, @values) = @_;
77 my $class = ref($self) || $self;
78 my $key = "$class getByPkey ".join("\x01", @values);
79 my $desc = "getByPkey valuesquery";
81 #print STDERR "Checking for $desc\n";
82 if (exists $query_cache{$key}) {
83 # print STDERR "Found query getBy @query\n";
84 return $query_cache{$key};
90 $result = $self->{coll}{join "", @values};
93 # try to get row by key
94 my $rowClass = $self->rowClass;
95 (my $reqName = $rowClass) =~ s!::!/!g;
96 require $reqName . ".pm";
97 my $member = "get${rowClass}ByPkey";
98 my $sth = $dh->stmt_noerror($member);
100 my @cols = ( $rowClass->primary );
102 @vals{@cols} = @values;
103 $sth ||= $self->_getBy_sth($member, \@cols, \%vals);
106 or confess "No $member in BSE::DB";
107 $sth->execute(@values)
108 or confess "Cannot execute $member handle from DatabaseHandle:", DBI->errstr;
109 # should only be one row
110 if (my $row = $sth->fetchrow_arrayref) {
111 $result = $rowClass->new(@$row);
118 $query_cache{$key} = $result if $cache_queries;
124 my ($self, @data) = @_;
126 (my $rowRequire = $self->rowClass) =~ s!::!/!g;
127 require $rowRequire.".pm";
128 my $item = $self->rowClass->new(undef, @data);
130 # if called as an instance method
132 delete $cache{ref $self};
133 $self->{coll}{$item->{pkey}} = $item;
134 push(@{$self->{order}}, $item);
137 delete $cache{ref $self};
143 # get all values in a particular column
145 my ($self, $column) = @_;
146 my @values = map { $_->{$column} } @{$self->{order}};
148 return wantarray ? @values : \@values;
152 my ($self, $value) = @_;
154 $cache_queries = $value;
162 my ($self, @query) = @_;
167 or confess "Odd number of arguments supplied to getBy()";
169 my $class = ref($self) || $self;
170 my $key = "$class getBy ".join("\x01", @query);
171 my $desc = "getBy @query";
172 if ($cache_queries) {
173 #print STDERR "Checking for $desc\n";
174 if (my $entry = $query_cache{$key}) {
175 # print STDERR "Found query getBy @query\n";
176 return wantarray ? @$entry : $entry->[0];
180 while (my ($col, $val) = splice(@query, 0, 2)) {
186 if (ref($self) && UNIVERSAL::isa($self, __PACKAGE__)) {
187 # this is an object with the rows already loaded
188 for my $row (@{$self->{order}}) {
191 map ref $row->{$_} ? $row->{$_}->getPkey : $row->{$_}, @cols;
192 push @results, $row if @cols == grep $comp{$_} eq $vals{$_}, @cols;
196 # ask the database directly
197 my $rowClass = $self->rowClass;
198 (my $reqName = $rowClass) =~ s!::!/!g;
199 require $reqName . ".pm";
200 my $member = "get${rowClass}By".join("And", map "\u$_", @cols);
201 my $sth = $dh->stmt_noerror($member);
202 $sth ||= $self->_getBy_sth($member, \@cols, \%vals);
204 or confess "No $member in BSE::DB";
205 $sth->execute(@vals{@cols})
206 or confess "Cannot execute $member from BSE::DB: ",DBI->errstr;
207 while (my $row = $sth->fetchrow_arrayref) {
208 push(@results, $rowClass->new(@$row));
212 if ($cache_queries) {
213 #print STDERR "Saving $desc\n";
214 $query_cache{$key} = \@results;
217 return wantarray ? @results : $results[0];
221 my ($self, $name, $cols, $vals) = @_;
223 my $bases = $self->rowClass->bases;
225 and confess "No statement $name found and cannot generate";
227 my @db_cols = $self->rowClass->db_columns;
228 my @code_cols = $self->rowClass->columns;
230 @map{@code_cols} = @db_cols;
233 for my $col (@$cols) {
234 my $db_col = $map{$col}
235 or confess "Cannot generate $name: unknown column $col";
236 # this doesn't handle null, but that should use a "special"
237 push @conds, "$db_col = ?";
240 my $sql = "select " . join(",", @db_cols) .
241 " from " . $self->rowClass->table .
242 " where " . join(" and ", @conds);
244 my $sth = $dh->{dbh}->prepare($sql)
245 or confess "Cannot prepare generated $sql: ", $dh->{dbh}->errstr;
251 my ($self, $cols, %find) = @_;
253 my @db_cols = $self->rowClass->db_columns;
254 my @code_cols = $self->rowClass->columns;
256 @map{@code_cols} = @db_cols;
260 for my $col (keys %find) {
261 my $db_col = $map{$col}
262 or confess "Cannot generate query: unknown column $col";
263 # this doesn't handle null, but that should use a "special"
264 push @conds, "$db_col = ?";
265 push @args, $find{$col};
267 my @result_cols = map $map{$_}, @$cols;
269 my $sql = "select " . join(",", @result_cols) .
270 " from " . $self->rowClass->table .
271 " where " . join(" and ", @conds);
273 my $sth = $dh->{dbh}->prepare($sql)
274 or confess "Cannot prepare generated $sql: ", $dh->{dbh}->errstr;
277 or confess "Cannot execute $sql: ",$dh->{dbh}->errstr;
280 while (my $row = $sth->fetchrow_arrayref) {
282 @row{@$cols} = @$row;
286 return wantarray ? @rows : \@rows;
290 my ($self, $name, @args) = @_;
292 my $class = ref($self) || $self;
293 my $key = "$class getSpecial $name ".join("\x01", @args);
294 my $desc = "getSpecial $name @args";
295 if ($cache_queries) {
296 #print STDERR "Checking for $desc\n";
297 if (my $entry = $query_cache{$key}) {
298 # print STDERR "Found query getBy @query\n";
299 return wantarray ? @$entry : $entry;
303 my $rowClass = $self->rowClass;
304 my $sqlname = $class . "." . $name;
305 my $sth = $dh->stmt($sqlname)
306 or confess "No $sqlname in database object";
308 or confess "Cannot execute $sqlname: ", $sth->errstr;
310 while (my $row = $sth->fetchrow_arrayref) {
311 push(@results, $rowClass->new(@$row));
314 if ($cache_queries) {
315 #print STDERR "Saving $desc\n";
316 $query_cache{$key} = \@results;
319 wantarray ? @results : \@results;
323 my ($self, $name, @args) = @_;
325 my $class = ref $self ? ref $self : $self;
326 my $sqlname = $class . "." . $name;
327 my $sth = $dh->stmt($sqlname)
328 or confess "No $sqlname in database object";
330 or confess "Cannot execute $sqlname: ", $sth->errstr;
335 # a list of all rows in select order
340 $self = $self->new();
343 return @{$self->{order}};
347 my ($self, $columns, $query, $opts) = @_;
349 $dh->generate_query($self->rowClass, $columns, $query, $opts);
353 my ($self, %values) = @_;
355 my @cols = $self->rowClass->columns;
356 my %defaults = $self->rowClass->defaults;
357 shift @cols; # presumably the generated private key
359 for my $col (@cols) {
361 # a defined test is inappropriate here, the caller might want to
362 # set a column to null.
363 if (exists $values{$col}) {
364 $value = delete $values{$col};
366 elsif (exists $defaults{$col}) {
367 $value = $defaults{$col};
370 confess "No value or default supplied for $col";
372 push @values, $value;
375 and confess "Extra values ", join(",", keys %values), " supplied to ${self}->make()";
377 return $self->add(@values);
386 Base class for tables.
390 This needs more documentation.
392 =head1 IMPLEMENT IN THE BASE
398 Returns the name of the class implementing the rows for this class.
402 =head1 IMPLEMENT IN DatabaseHandle
406 Some methods can be used as both class and instance methods.
408 In these cases when used as a class method they ask the database
409 directly for the information. This requires that appropriate keys be
410 defined in the DatabaseHandle object.
412 In the examples SomeTable is used as the name of the table class
413 derived from Squirell::Table.
417 =item $table = SomeTable->new
419 Loads the contents of the table into memory.
421 =item until ($table->EOF) { ... }
423 Bumps the index into the table, returns TRUE if we've passed the end
426 =item $row = $table->getNext
428 Gets the currently indexed item in the table.
430 =item $row = $table->getByPkey(@values)
432 =item $row = SomeTable->getByPkey(@values)
434 Retrieves the specified row from the database.
436 For the class method version to work you must have a statement handle
437 in the DatabaseHandle object called get${rowClass}ByPkey.
439 =item $row = $table->add(@data)
441 =item $row = SomeTable->add(@data)
443 Adds a row to the table. @data must contain all except the primary key.
445 =item @rows = $table->getAll($column)
447 Returns a list containing that column for each row in the table.
449 Returns an array ref if called in a scalar context.
451 =item @rows = $table->getBy($column, $value)
453 =item @rows = SomeTable->getBy($column, $value)
455 Returns any rows where the given column has that value.
457 Returns the first element of the list if called in scalar context
458 (though it still retrieves the whole lot.)
460 For the class method form to work the DatabaseHandle object must have
461 a member "get${rowClass}By\u$column".