add shortcut for "DO NOTHING"

mojolicious · Jan 27, 2018 · 5a2994f · 5a2994f
1 parent d3c3fed
commit 5a2994f
Show file tree

Hide file tree

Showing 5 changed files with 22 additions and 15 deletions.
diff --git a/Changes b/Changes
@@ -1,5 +1,7 @@
 
-4.07  2018-01-28
+4.07  2018-01-27
+  - Improved on_conflict option of insert and insert_p methods in
+    Mojo::Pg::Database with a shortcut for "DO NOTHING".
 
 4.06  2018-01-27
   - Added support for on_conflict option to insert and insert_p methods in

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -367,7 +367,7 @@ L<SQL::Abstract>.
 As well as some PostgreSQL specific extensions added by L<SQL::Abstract::Pg>.
 
   # "insert into some_table (foo) values ('bar') on conflict do nothing"
-  $db->insert('some_table', {foo => 'bar'}, {on_conflict => \'do nothing'});
+  $db->insert('some_table', {foo => 'bar'}, {on_conflict => undef});
 
 Including operations commonly referred to as C<upsert>.
 

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -4,7 +4,7 @@ use Mojo::Base 'SQL::Abstract';
 sub insert {
   my ($self, $table, $data, $options) = @_;
   local @{$options}{qw(returning _pg_returning)} = (1, 1)
-    if defined $options->{on_conflict} && !$options->{returning};
+    if exists $options->{on_conflict} && !$options->{returning};
   return $self->SUPER::insert($table, $data, $options);
 }
 
@@ -16,12 +16,14 @@ sub _insert_returning {
   # ON CONFLICT
   my $sql = '';
   my @bind;
-  if (defined(my $conflict = $options->{on_conflict})) {
+  if (exists $options->{on_conflict}) {
+    my $conflict = $options->{on_conflict};
     my ($conflict_sql, @conflict_bind);
     $self->_SWITCH_refkind(
       $conflict => {
         SCALARREF => sub { $conflict_sql = $$conflict },
-        ARRAYREFREF => sub { ($conflict_sql, @conflict_bind) = @$$conflict }
+        ARRAYREFREF => sub { ($conflict_sql, @conflict_bind) = @$$conflict },
+        UNDEF => sub { $conflict_sql = $self->_sqlcase('do nothing') }
       }
     );
     $sql .= $self->_sqlcase(' on conflict ') . $conflict_sql;
@@ -117,12 +119,15 @@ 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 and
-array reference references to pass literal SQL with bind values are supported.
+C<ON CONFLICT> clauses. So far only C<undef> to pass C<DO NOTHING>, 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 t (a) values ('b') on conflict do nothing"
+  $abstract->insert('t', {a => 'b'}, {on_conflict => undef});
+
+  # "insert into t (a) values ('b') on conflict do nothing"
+  $abstract->insert('t', {a => 'b'}, {on_conflict => \'do nothing'});
 
 This includes operations commonly referred to as C<upsert>.
 

diff --git a/t/crud.t b/t/crud.t
@@ -30,11 +30,7 @@ is $db->insert('crud_test', {name => 'bar'}, {returning => 'id'})->hash->{id},
   2, 'right value';
 is_deeply $db->select('crud_test')->hashes->to_array,
   [{id => 1, name => 'foo'}, {id => 2, name => 'bar'}], 'right structure';
-$db->insert(
-  'crud_test',
-  {id          => 1, name => 'foo'},
-  {on_conflict => \'do nothing'}
-);
+$db->insert('crud_test', {id => 1, name => 'foo'}, {on_conflict => undef});
 $db->insert(
   'crud_test',
   {id          => 2,                               name => 'bar'},

diff --git a/t/sql.t b/t/sql.t
@@ -17,6 +17,10 @@ my @sql
 is_deeply \@sql,
   ['INSERT INTO "foo" ( "bar") VALUES ( ? ) ON CONFLICT do nothing', 'baz'],
   'right query';
+@sql = $abstract->insert('foo', {bar => 'baz'}, {on_conflict => undef});
+is_deeply \@sql,
+  ['INSERT INTO "foo" ( "bar") VALUES ( ? ) ON CONFLICT DO NOTHING', 'baz'],
+  'right query';
 @sql = $abstract->insert(
   'foo',
   {bar         => 'baz'},