[Dbix-class] Select From Dual (Again)

[Richard Cox]

Hi, I've played around a bit more now and am loving HashRefInflator but will
prob stick to doing $obj->get_column('sysdate');

More than happy to contribute so here's a brief recipe for selecting from dual,
feel free to remove the Note: if people think it's patronizing ;)

Cheers
Rick

=head3 select from dual

Note: If you're using Oracles magical dual table don't B<ever> do anything
other than a select, if you CRUD on your dual table you *will* break your
database and your dba will most likely
C<revoke all privileges except create session from your_ass>

Make a table class as you would for any other table

  package MyAppDB::Dual;
  use strict;
  use warnings;
  use base 'DBIx::Class';
  __PACKAGE__->load_components("Core");
  __PACKAGE__->table("Dual");
  __PACKAGE__->add_columns(
    "dummy",
    { data_type => "VARCHAR2", is_nullable => 0, size => 1 },
  );

Once you've loaded your table class select from it using C<select>
and C<as> instead of C<columns>

  my $rs = $schema->resultset('Dual')->search(undef,
    { select => [ 'sydate' ],
      as     => [ 'now' ]
    },
  );

All you have to do now is be careful how you access your resultset, the below
will not work because there is no column called 'now' in the Dual table class

  while (my $dual = $rs->next) {
    print $dual->now."\n";
  }
  Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl
line 23.

You could of course use 'dummy' in C<as> instead of 'now', or C<add_columns> to
your Dual class for whatever you wanted to select from dual, but that's just
silly, instead use C<get_column>

  while (my $dual = $rs->next) {
    print $dual->get_column('now')."\n";
  }

Or use C<cursor>

  my $cursor = $rs->cursor;
  while (my @vals = $cursor->next) {
    print $vals[0]."\n";
  }
    
Or use L<DBIx::Class::ResultClass::HashRefInflator>

  $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
  while ( my $dual = $rs->next ) {
    print $dual->{now}."\n";
  }

Here are some example C<select> conditions to illustrate the different syntax
you could use for doing stuff like 
C<oracles.heavily(nested(functions_can('take', 'lots'), OF), 'args')>

  # get a sequence value
  select => [ 'A_SEQ.nextval' ],

  # get create table sql
  select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ],

  # get a random num between 0 and 100
  select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ],

  # what year is it?
  select => [ { 'extract' => [ \'year from sysdate' ] } ],

  # do some math
  select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}],

  # which day of the week were you born on?
  select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'"
]}, "'day'"]}],

  # select 16 rows from dual
  select   => [ "'hello'" ],
  as       => [ 'world' ],
  group_by => [ 'cube( 1, 2, 3, 4 )' ],