DBIx::Class - Define a table name at runtime

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

Brother monks, I seek your wisdom.

I am trying to write a DBIx::Class based script that runs queries on a table in a MySQL database where the name of the table is not known till runtime. The the table definition (the columns and their types) is known beforehand.

I thought I could just make use of the __PACKAGE__->table('some_name'); hook defined in DBIx::Class::Table, to set the name of the table at runtime, but that does not work. I wrote:

package MyCompany::Schema::LogEntry;
use base 'DBIx::Class::Core';
__PACKAGE__->add_columns(
    'id'          => { data_type=>'int',         is_auto_increment=>1 
+},
    # Other rows. (deleted)
);
__PACKAGE__->set_primary_key('id');
[download]

My main script looks like:

use Getopt::Long;
use MyCompany::Schema::LogEntry;

GetOptions( $opts,
    'help|h','man','verbose+', 'quiet',
    'dbic_dsn=s', 'dbic_user=s', 'dbic_password=s', 'db_table=s',
) or pod2usage(-verbose=>0, -message => "incorrect options");

# Change the log table to the real one.
MyCompany::Schema::LogEntry->table( $opts->{'db_table'} );

my $log_schema  = MyCompany::Schema->connect( @{$opts}{'dbic_dsn','dbi
+c_user','dbic_password'} )

my $matching_log_RS = $log_schema->resultset('LogEntry')->search({%sea
+rchTerms});

print $matching_log_RS->as_query();
[download]

I intended to call MyCompany::Schema::LogEntry->table() at runtime, but the code above would not compile. I got the error message:

DBIx::Class::Schema::throw_exception(): Can't locate object method "re
+sult_source_instance" via package "MyCompany::Schema::LogEntry" at /u
+sr/local/lib/perl/5.10.1/DBIx/Class/ResultSourceProxy.pm line 37.
[download]

I then thought, I could give the table a dummy name, and then change it later, so I added __PACKAGE__->table('__CHANGE_ME__'); to the source just before the add_columns call.

That still did not work, as internally DBIx::Class had stored the dummy table name, and not updated to the replacement name. The query returned by $matching_log_RS->as_query(); on the last line of my main program refers to the dummy table name rather than the updated one.

Can you offer any help or assistance?

Comment on DBIx::Class - Define a table name at runtime Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class - Define a table name at runtime by anonymized user 468275 (Curate) on Dec 02, 2010 at 12:00 UTC
DBIx::Class has a compile-time interface. The approach that comes to mind is a perl-perl wrapper main that generates the variable compile-time code at run-time and then calls the static code also by require that in turn requires the variable compile-time code. For example: Actual main program (outer wrapper): `#!/usr/bin/perl use strict; use warnings; print "enter some perl code: "; open my $r2h, '>require2.pl'; my $code = <>; print $r2h $code; close $r2h; require 'require1.pl';` [download] intended main program is require1.pl which can now load the variable code in require2.pl also by the require statement. Functionally main program require1.pl does not get compiled until require2.pl has been generated at run-time by the above artificial actual main program (wrapper). update: for multithreaded use, require1.pl has to be spawned rather than required and with e.g. a table name parameter and require2.pl will instead have a filename derived from the table name. It's all awkward, so you may also want to ask yourself if you really want to make a compile-time class behave outside its remit rather than choosing an alternative run-time-oriented DBI class. more update: correction: require1.pl should "use" not require the variable code require2.pl (now a misnomer) so that it is loaded at the compile time of require1.pl instead of its run-time. One world, one people	[reply] [d/l]

Replies are listed 'Best First'.

Re: DBIx::Class - Define a table name at runtime
by anonymized user 468275 (Curate) on Dec 02, 2010 at 12:00 UTC

Actual main program (outer wrapper):

#!/usr/bin/perl

use strict;

use warnings;

print "enter some perl code: ";

open my $r2h, '>require2.pl';
my $code = <>;
print $r2h $code;

close $r2h;

require 'require1.pl';
[download]

update: for multithreaded use, require1.pl has to be spawned rather than required and with e.g. a table name parameter and require2.pl will instead have a filename derived from the table name.

It's all awkward, so you may also want to ask yourself if you really want to make a compile-time class behave outside its remit rather than choosing an alternative run-time-oriented DBI class.

more update: correction: require1.pl should "use" not require the variable code require2.pl (now a misnomer) so that it is loaded at the compile time of require1.pl instead of its run-time.

One world, one people

[reply]
[d/l]