johnnywang has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I'm in a situation where I have several database tables with identical structure (please don't ask me why), the application at runtime decides from which table to query. I'm using Class::DBI to map to the same perl classes. In my application code, I simply call the table() method before querying. This seems to be fine, except that if I query two tables with the same id, I got the same result. Is that a bug? or I'm doing something wrong?

Let me be more specific, say I have two tables Foo and Bar, each has the same columns: id, name. I have a standard perl object mapping to the table: 

package FooBar;

use strict;
use base 'Class::DBI';

FooBar->set_db('Main','dburl','user','password');
FooBar->table('Foo'); # Foo is the default table
FooBar->columns(Primary=> qw/id/ );
FooBar->columns(Other=> qw/name/ );

package main;
my $foo = FooBar->retrieve(1);
print $foo->name(), "\n";

FooBar->table('Bar'); #switch table
my $bar1 = FooBar->retrieve(1); # this give the same as $foo, it shoul
+dn't.
my $bar2 = FooBar->retrieve(2); # this is different

[download]
and if I skip the first query, then $bar1 and $bar2 are correct.

Replies are listed 'Best First'.
Re: Dynamically change tables for Class::DBI
by perrin (Chancellor) on Feb 18, 2005 at 21:25 UTC
    Well, this is really not supported at all by Class::DBI and is very likely to cause you problems all over the place. I would advise you to make separate classes. However, I can tell you how to fix this specific problem.

    Class::DBI keeps track of which objects you have in memory. See the "UNIQUENESS OF OBJECTS IN MEMORY" section of the docs for an explanation. You can either turn off this feature, or just remove the object from memory before trying to fetch it again. Here's an example: 

    my $foo = FooBar->retrieve(1);
print $foo->name(), "\n";
# remove the instance of FooBar with an ID of 1 from memory
undef $foo;

FooBar->table('Bar'); #switch table
my $bar1 = FooBar->retrieve(1);

    [download]
[reply]
[d/l]
      Thanks. In the real situation, I may not have access to $foo, i.e., some other part of the program may have made the query. If I call the FooBar->clear_object_index(), how much a performance hit is that? granted it is ugly.

      Update. further reading of the doc shows that the cache will be cleared if $foo is out of scope. That should be enough for me.

[reply]
        For more help on this, there is info on the wiki here.
[reply]

Back to Seekers of Perl Wisdom