allow literal SQL with placeholders to be passed to on_conflict

mojolicious · Jan 27, 2018 · bdc3bd3 · bdc3bd3
1 parent aafdc0a
commit bdc3bd3
Show file tree

Hide file tree

Showing 4 changed files with 52 additions and 17 deletions.
diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -367,6 +367,12 @@ L<SQL::Abstract>.
   # "insert into some_table (foo) values ('bar') on conflict do nothing"
   $db->insert('some_table', {foo => 'bar'}, {on_conflict => \'do nothing'});
 
+  # "insert into some_table (foo) values ('bar')
+  #  on conflict (foo) do update set foo = 'baz'"
+  $db->insert('some_table', {foo => 'bar'}, {
+    on_conflict => \['(foo) do update set foo = ?', 'baz']
+  });
+
 =head2 insert_p
 
   my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -12,22 +12,25 @@ sub _insert_returning {
   my ($self, $options) = @_;
 
   delete $options->{returning} if $options->{_pg_returning};
-  my $sql = '';
 
   # ON CONFLICT
+  my $sql = '';
+  my @bind;
   if (defined(my $conflict = $options->{on_conflict})) {
+    my ($conflict_sql, @conflict_bind);
     $self->_SWITCH_refkind(
       $conflict => {
-        SCALARREF => sub {
-          $sql .= $self->_sqlcase(' on conflict ') . $$conflict;
-        }
+        SCALARREF => sub { $conflict_sql = $$conflict },
+        ARRAYREFREF => sub { ($conflict_sql, @conflict_bind) = @$$conflict }
       }
     );
+    $sql .= $self->_sqlcase(' on conflict ') . $conflict_sql;
+    push @bind, @conflict_bind;
   }
 
   $sql .= $self->SUPER::_insert_returning($options) if $options->{returning};
 
-  return $sql;
+  return $sql, @bind;
 }
 
 sub _order_by {
@@ -102,18 +105,28 @@ used by L<Mojo::Pg>.
 
 =head1 INSERT
 
+Additional C<INSERT> query features.
+
 =head2 ON CONFLICT
 
 The C<on_conflict> option can be used to generate C<INSERT> queries with
-C<ON CONFLICT> clauses. So far only scalar references to pass literal SQL are
-supported.
+C<ON CONFLICT> clauses. So far only scalar references to pass literal SQL and
+array reference references to pass literal SQL with bind values are supported.
 
   # "insert into some_table (foo) values ('bar') on conflict do nothing"
   $abstract->insert(
     'some_table', {foo => 'bar'}, {on_conflict => \'do nothing'});
 
+  # "insert into some_table (foo) values ('bar')
+  #  on conflict (foo) do update set foo = 'baz'"
+  $abstract->insert('some_table', {foo => 'bar'}, {
+    on_conflict => \['(foo) do update set foo = ?', 'baz']
+  });
+
 =head1 SELECT
 
+Additional C<SELECT> query features.
+
 =head2 ORDER BY
 
   $abstract->select($source, $fields, $where, $order);

diff --git a/t/crud.t b/t/crud.t
@@ -35,18 +35,23 @@ $db->insert(
   {id          => 1, name => 'foo'},
   {on_conflict => \'do nothing'}
 );
+$db->insert(
+  'crud_test',
+  {id          => 2,                               name => 'bar'},
+  {on_conflict => \['(id) do update set name = ?', 'baz']}
+);
 
 # Read
 is_deeply $db->select('crud_test')->hashes->to_array,
-  [{id => 1, name => 'foo'}, {id => 2, name => 'bar'}], 'right structure';
+  [{id => 1, name => 'foo'}, {id => 2, name => 'baz'}], 'right structure';
 is_deeply $db->select('crud_test', ['name'])->hashes->to_array,
-  [{name => 'foo'}, {name => 'bar'}], 'right structure';
+  [{name => 'foo'}, {name => 'baz'}], 'right structure';
 is_deeply $db->select('crud_test', ['name'], {name => 'foo'})->hashes->to_array,
   [{name => 'foo'}], 'right structure';
 is_deeply $db->select('crud_test', ['name'], undef, {-desc => 'id'})
-  ->hashes->to_array, [{name => 'bar'}, {name => 'foo'}], 'right structure';
+  ->hashes->to_array, [{name => 'baz'}, {name => 'foo'}], 'right structure';
 is_deeply $db->select('crud_test', undef, undef, {offset => 1})
-  ->hashes->to_array, [{id => 2, name => 'bar'}], 'right structure';
+  ->hashes->to_array, [{id => 2, name => 'baz'}], 'right structure';
 is_deeply $db->select('crud_test', undef, undef, {limit => 1})
   ->hashes->to_array, [{id => 1, name => 'foo'}], 'right structure';
 
@@ -55,25 +60,25 @@ my $result;
 my $delay = Mojo::IOLoop->delay(sub { $result = pop->hashes->to_array });
 $db->select('crud_test', $delay->begin);
 $delay->wait;
-is_deeply $result, [{id => 1, name => 'foo'}, {id => 2, name => 'bar'}],
+is_deeply $result, [{id => 1, name => 'foo'}, {id => 2, name => 'baz'}],
   'right structure';
 $result = undef;
 $delay = Mojo::IOLoop->delay(sub { $result = pop->hashes->to_array });
 $db->select('crud_test', undef, undef, {-desc => 'id'}, $delay->begin);
 $delay->wait;
-is_deeply $result, [{id => 2, name => 'bar'}, {id => 1, name => 'foo'}],
+is_deeply $result, [{id => 2, name => 'baz'}, {id => 1, name => 'foo'}],
   'right structure';
 
 # Update
-$db->update('crud_test', {name => 'baz'}, {name => 'foo'});
+$db->update('crud_test', {name => 'yada'}, {name => 'foo'});
 is_deeply $db->select('crud_test', undef, undef, {-asc => 'id'})
-  ->hashes->to_array, [{id => 1, name => 'baz'}, {id => 2, name => 'bar'}],
+  ->hashes->to_array, [{id => 1, name => 'yada'}, {id => 2, name => 'baz'}],
   'right structure';
 
 # Delete
-$db->delete('crud_test', {name => 'baz'});
+$db->delete('crud_test', {name => 'yada'});
 is_deeply $db->select('crud_test', undef, undef, {-asc => 'id'})
-  ->hashes->to_array, [{id => 2, name => 'bar'}], 'right structure';
+  ->hashes->to_array, [{id => 2, name => 'baz'}], 'right structure';
 $db->delete('crud_test');
 is_deeply $db->select('crud_test')->hashes->to_array, [], 'right structure';
 

diff --git a/t/sql.t b/t/sql.t
@@ -27,6 +27,17 @@ my $result = [
   'baz'
 ];
 is_deeply \@sql, $result, 'right query';
+@sql = $abstract->insert(
+  'foo',
+  {bar         => 'baz'},
+  {on_conflict => \['(foo) do update set foo = ?', 'yada']}
+);
+$result = [
+  'INSERT INTO "foo" ( "bar") VALUES ( ? )'
+    . ' ON CONFLICT (foo) do update set foo = ?',
+  'baz', 'yada'
+];
+is_deeply \@sql, $result, 'right query';
 
 # ON CONFLICT (unsupported value)
 eval { $abstract->insert('foo', {bar => 'baz'}, {on_conflict => []}) };