[Dbix-class] Select From Dual (Again)

[Richard Cox]

Greetings all, a colleague of mine raised this last year and I've finally got
round to playing with ways to do stuff like "select sysdate from dual" in DBIC.

Consensus at the time seemed to be just use raw sql. That's cool but I use dual
a lot, mainly for getting a job_id from a sequence but also for funkier stuff
like date formatting...

select initcap(TO_CHAR(SYSDATE, 'DY MON DD YYYY HH24:MI:SS')) from dual
# Thu Nov 08 2007 16:44:47

and getting ddl

select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') from dual
# create table schema.table_name ...blah blah

So, first up some tips for anyone interested, to do the above, get yourself a
Dual Table class

package MyAppDB::Dual;
use strict;
use warnings;
use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("Dual");
__PACKAGE__->add_columns( "dummy");

1;

and of course load it, then in a nearly bit of code I can do

# get a sequence val
my $rs = $schema->resultset('Dual')->search(undef,
   { select => [ \'A_SEQ.nextval' ],
     as     => [ 'dummy' ]
   }
);
print_dummy(); # 480 or whatever

# use an oracle function
$rs = $schema->resultset('Dual')->search(undef,
   { select => [ { initcap => '\'foo\'' } ],
     as     => [ 'dummy' ]
   },
);
print_dummy(); # Foo

# funky date
$rs = $schema->resultset('Dual')->search(undef,
   { select => [ { initcap => {'to_char' => [  \'sysdate' =>
                                              '\'DY MON DD YYYY HH24:MI:SS\''
                                            ]
                              }
                 }
               ],
     as     => [ 'dummy' ]
   },
);
print_dummy(); # Fri Nov 09 2007 13:58:33

# ddl - I know about deploy and stuff but this gets you oracles weird storage
# clauses as well like pctfree an all that, and you can get it as xml with
# .get_xml

$rs = $schema->resultset('Dual')->search(undef,
   { select => [ { 'dbms_metadata.get_ddl' => ['\'TABLE\'', '\'TABLE_NAME\'' ]}
               ],
     as     => [ 'dummy' ]
   },
);
print_dummy(); # ctas

sub print_dummy {
   while (my $thing = $rs->next) {
       print $thing->dummy."\n";
   }
}

So all that's cool but I hit a roadblock if I want to select more than one
column at a time because we only have one 'dummy' column in our table class so
it gets autoviv'd

my @rs = $schema->resultset('Dual')->search(undef,
   { select => [ \'sysdate', {initcap => '\'bar\''} ],
     as     => [ 'dummy', 'dummy' ]
   },
);

for my $col (@rs) {
   print $col->dummy."\n"; # Bar
}

To get round this I've slung together an 'auto column' component which
overrides get_column

package MyApp::AutoColTest;
use strict;
use warnings;
use base 'DBIx::Class';

# temporarily add a column to 'Dual' class
sub get_column {
   my ( $self, $column ) = @_;
   my $value;
   if (! $self->has_column($column)) {
         $self->add_column($column => {} );
         $value = $self->next::method($column);
         # otherwise we'd end up with _lots_ of fake columns over time?
         $self->remove_column($column);
   }
   else {
        $value = $self->next::method($column);
   }
   return $value;
}
1;

# use it
package MyAppDB::Dual;
__PACKAGE__->load_components( "+MyApp::AutoColTest", "Core");

So now I (seem to be able to) put whatever I want in as...

my @cols = qw( my_date my_text);
my @dual = $schema->resultset('Dual')->search(undef,
   { select => [ \'sysdate', {initcap => '\'bar\''} ],
     as     => \@cols
   },
);
for my $col (@dual) {
   print $col->get_column($cols[0])." : ".$col->get_column($cols[1])."\n";
}
# 09-NOV-07 : Bar

Problem is of course this only work if you use get_column...

So what the consensus? is this a sane thing to do? I realize this will conflict
with other components overriding get_column and goes against msts advice:
"The DBIC approach is that anything that goes through get/set_column should be
a real database column (or at least for read-only resultset column), always."

but then the fake col in Dual is 'real' at least to the oracle optimizer I
guess? Any other ideas how to do a similar / better thing?

Cheers for reading, flame proof trousers are on ;)
Rick