dynamic creation of Class::DBI derived classes

[prev] [thread] [next] [Date index for 2004/12/07]

Hi,

I've only used Class::DBI the last few weeks, but it
already has increased my productivity tremendously.
Unfortunately the last few days I'm held up by it (and
my lack of skills?) because of the following:
Part of my database schema looks like this:
3 tables:
Item            (id, name, description)
ItemAttribute   (id, name, description, ... )
ItemData        (id, fk_item, fk_itemattribute, value,
start_date, end_date)

ItemData contains foreign keys to Item and
ItemAttribute and I've been able to get this working
with no problems using Many-to-Many mappings. This way
I can give Item attributes that are valid for a
specific period of time.

ItemAttribute has a few more columns that are the
cause of my problem. There is a 'is_reference' column,
if this is not NULL then the value in 'name' should be
interpreted as the name of another table that contains
more information on what possible values there are for
this attribute. Say there is a ItemAttribute row:
id   10,
name Contact,
is_reference 1

This would mean the thing stored in ItemData with
itemattribute 10 is the primary key of the table
'Contact' (which might look like: (id, first_name,
last_name) or something).

What I can do without problems is for every external
table I know I can create an extra class, but since
the application that I'm writing will be distributed
over quite a few machines, I don't want to update the
app itself everytime an extra attribute that refers to
an extra table is added.

So I'm looking for a way to dynamically create these
Class::DBI classes. Unfortunately the 'table' that is
set with:
package AuxTables;
__PACKAGE__->table($table);

is Class-data and not Instance-data (correct me if I'm
wrong, I've never received formal programming
training) and so this won't work.

I've also tried several other approaches:
* overload all CDBI methods like this:
 sub retrieve {
	my ($class,$table,@args) = @_;
	__PACKAGE__->table($table);
	__PACKAGE__->columns( All => MyDBI->get_columns(
$table ) );
	return $class->SUPER::retrieve(@args);
 }
couldn't get this to work, and is also very ugly.
Tried to find a single 'point of entry' where I had to
only rewrite 1 function, but couldn't find 1 single
function that had this property.
* use a DIY-object (stolen from CDBI wiki):
        my $sql = "SELECT * FROM " . $tablename ;
	my $dbh = $this->db_Main();
	my $sth = $dbh->prepare_cached($sql);
	my @results = AuxTables->sth_to_objects($sth);
couldn't get this to work, does only return the first
value from the specific auxilliary table.
* looked into Class::DBI::Factory
 seems to need a configuration, in advance (so not
based on data in tables itself), to be useful
* eval approaches
 won't work because 'package $class;' is invalid
syntax

Has anybody ever used a similar schema with CDBI
before and got this type of 'dynamic' subclasses to
work as beautifully as the rest of CDBI?
Anybody any tips on what else I could try to get this
working?

thanks,
E


	
		
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail