[Dbix-class] Getting INSERT INTO ... SELECT support in DBIx::Class

[Jose Luis Martinez]

Hi!

     Recently we migrated a project from DBI raw prepare() and execute() 
to DBIC, and had to do some "naughty stuff" to retain compatibility, but 
now that release time is not pressing I'd like to clean it up, put 
things in the right places, and contribute new functionalities back to DBIC.

     One of the naughty bits that the app did, and that I had to work 
around without DBIC was INSERT INTO table (...) [SELECT STATEMENT].

     I already have a first approach, but I need some guidance to fit 
the right bits into the right places, and see how to fit this into the 
DBIC API correctly.

     The final objective is to add a behaviour to the insert (maybe this 
isn't the right spot) method so that you can then call 
$table->insert($rs) on it, and you get an INSERT INTO table_name 
(table_columns) SELECT SELECT_GENERATED_BY_$rs. This is lots faster that 
looping over $rs, and inserting rows one by one.

     The (working) proof of concept code that I have is:

# $options is a hashref that can be passed:
#  -  ignore => 1 to generate a INSERT IGNORE INTO statement.
#  -  mapping => [] array ref of column names to insert into.
#                   defaults to all columns of the table
sub insert_poc {
   my ($self, $rs, $options) = @_;

   # Get the SQL for the RS we are inserting. Is this sane?
   my ($sql, @params) = @{ ${$rs->as_query()} };

   # Ignore option
   my $ignore = ($options->{ignore}==1)?'IGNORE':'';
   my $mapping;
   if (ref($options->{mapping}) eq 'ARRAY'){
     $mapping = [ @{$options->{mapping}} ];
   } else {
     $mapping = [ $self->result_source->columns() ];
   }

   my $fields = join ',', @$mapping;
   my $table = $self->result_source->from;

   my $isql = "INSERT $ignore INTO $table ($fields) " . $sql;

   my $dbh = $self->result_source->schema->storage->dbh();
   my $sth = $dbh->prepare($isql);

   # maybe this is even insaner...
   $sth->execute(map { $_->[1] } @params);
   $sth->finish;
}

     But to get this up to standards, I would need some community review 
/ thoughts, and some guidance as to put what where. (Patch 
SQL::Abstract? Is the insert method the right / expected place to put 
this functionality? More use cases?)

Thanks in advance,

Jose Luis Martinez
jlmartinez at capside.com