Bulk Insert support?

[KnowZero]

It would be nice to support multiple inserts in a single statement, especially non-blocking.

Maybe something like this?

my @values = [ [ 'cat', 1 ], [ 'dog', 2 ] ];
 $abstract->insert( 'pets' => {  -values=>\@values }  );

my @keys = [ 'name', 'rank' ];
 $abstract->insert( 'pets' => { -keys=> \@keys,  -values=>\@values }  );

my @keyvalues = [ { name=>'cat', rank=>1 }, { name=>'dog', rank=>2 }  ];
 $abstract->insert( 'pets' => {  -keyvalues=>\@keyvalues }  );

Then backend something like this?

sub _insert_HASHREF { 
  my ($self, $data) = @_;

if ( ref $$data{'-keyvalues'} eq 'ARRAY' ) {
$$data{'-keys'}=[ sort keys %{ $$data{'-keyvalues'}[0] } ];
$$data{'-values'} = [ map {   
    my $keyvalues=$_;
    [ map {  $$keyvalues{ $_ }  } @{ $$data{'-keys'} }  ]   
    } @{ $$data{'-keyvalues'} } ];
}
   
   return $self->SUPER::_insert_HASHREF($data) if ref $$data{'-values'} ne 'ARRAY';
 


my @fields = @{$$data{'-keys'}};
 

   my ( @all_bind, @all_values);
  foreach my $rec (@{ $$data{'-values'} }) { 
      my @values;
    foreach my $value (@$rec) {

      my ($values, @bind) = $self->_insert_value(undef, $value);
      push @values, $values;
      push @all_bind, @bind;
    }

    push @all_values, \@values;
  }
  my $sql = ( exists $$data{'-keys'} ? "( ".join(", ", @fields )." ) ":'' ).
              $self->_sqlcase('values').
              join(',', map{ " ( ".join(", ", @$_)." )" } @all_values  );

  return ($sql, @all_bind);

}

[kraih]

Yes, i would like to see bulk inserts too. But getting the code right is not so easy.

[KnowZero]

Is there a problem with my approach of overwriting _insert_HASHREF?

or is it better to make a seperate bulk_insert function maybe?

[mrdvt92]

I added bulk insert support to DBIx::Array you are more than welcome to see how that was implemented. https://metacpan.org/pod/DBIx::Array#METHODS-(Write)-Bulk-SQL

[marghidanu]

This would be a nice thing to have.

[kraih]

Note that SQL::Abstract::Pg is now a separate project. https://github.com/mojolicious/sql-abstract-pg

[marghidanu]

I found a workaround! Since SQL::Abstract::Pg is a subclass of SQL::Abstract, all the plugins for SQL::Abstract will work directly. I stumbled upon SQL::Abstract::Plugin::InsertMulti and made a simple test:

package My::Model {
    use Mojo::Base -base, -signatures;

    use SQL::Abstract::Plugin::InsertMulti;

    has 'pg';

    sub insert_multi_p ( $self, $table, $data, $opts = {} ) {
        my ( $stmt, @bind ) =
          $self->pg()->abstract()->insert_multi( $table, $data, $opts );

        return $self->pg()->db()->query_p( $stmt, @bind );
    }
};

1;

So far, it works well. I hope it solves the problem for everyone else.

[akarelas]

I created this function which you might want to check, until SQL::Abstract::Pg implements it in the correct way.

It seems to work for "normal-looking" databases (ie ones that don't have parenthesis symbols, for example, as part of table names and field names, etc).

It fully supports the third argument of Mojo::Pg's insert (returning, on_conflict, etc). Also has auto-split of a large array into smaller ones (in that case, a list of multiple Mojo::Pg::Result objects will be returned since many inserts will happen).

Use like this:

my @resultsets = await $db->insert_multi_p(
    \300, # this is optional, splits the array
    'table_name',
    [
        # four new rows here:
        { a => 1 },
        { b => 2 },
        { a => 3, b => \'3 + 1' },
        { c => 5 },
    ],
    { on_conflict => ... },
);

use List::AllUtils qw/ natatime uniq /;
use Tie::IxHash;

async sub insert_multi_p ($db, @args) {
    # first argument can optionally be: \100 (to insert 100 rows at a time)
    if (eval { no warnings; $args[0]->$* eq int $args[0]->$* }) {
        my $how_many_at_a_time = (shift @args)->$*;

        my ($table, $array_of_kv_s, @rest) = @args;
        my $it = natatime $how_many_at_a_time, @$array_of_kv_s;
        my @results;
        while (my @bunch_of_kv_s = $it->()) {
            push @results, await $db->insert_multi_p(
                $table,
                \@bunch_of_kv_s,
                @rest,
            );
            await sleep_p(0);
        }
        return @results;
    }

    my ($table, $array_of_kv_s, @rest) = @args;
    my $abstract = $db->pg->abstract;

    @$array_of_kv_s or return ();

    my @all_keys = sort(uniq(map {keys %$_} @$array_of_kv_s));

    my $only_start_str;
    my @all_bind;
    my @values_strings;
    foreach my $_kv (@$array_of_kv_s) {
        my $kv = { %$_kv };
        foreach my $key (@all_keys) {
            exists $kv->{$key} or $kv->{$key} = \'DEFAULT';
        }

        my @sorted_kv = map {( $_ => $kv->{$_} )} @all_keys;
        tie my %sorted_kv, 'Tie::IxHash', @sorted_kv;

        my ($sql, @bind) = $abstract->insert($table, \%sorted_kv);
        my ($keys_str, $values_str) = $sql =~ /
            ^ \s* (
                INSERT \s+ INTO \s+ \S+? \s+
                \( (?:\s*\S+\s*,)* \s* \S+ \s* \) \s+
                VALUES
            ) \s+
            ( \( .+? \) )
            \s* \z
        /xi;
        $keys_str and $values_str or die "invalid sql";
        $only_start_str //= $keys_str;
        $keys_str eq $only_start_str or die 'problem with Tie::IxHash and Mojo::Pg';
        push @values_strings, $values_str;
        push @all_bind, @bind;
    }

    my ($dummy_sql, @rest_bind) = $abstract->insert(
        'aaa',
        { a => \'NULL' },
        @rest,
    );
    my $rest_sql = $dummy_sql =~ s/^.*?\bVALUES\s+\(.*?\)//r;
    push @all_bind, @rest_bind;

    my $final_sql = "$only_start_str "
        . join(', ', @values_strings)
        . " $rest_sql";

    return $db->query_p($final_sql, @all_bind);
}