make upserts look nice

mojolicious · Jan 27, 2018 · cd07e1e · cd07e1e
1 parent 702d049
commit cd07e1e
Show file tree

Hide file tree

Showing 5 changed files with 45 additions and 12 deletions.
diff --git a/Changes b/Changes
@@ -1,7 +1,8 @@
 
 4.07  2018-01-27
   - Improved on_conflict option of insert and insert_p methods in
-    Mojo::Pg::Database with a shortcut for "ON CONFLICT DO NOTHING".
+    Mojo::Pg::Database with shortcuts for "ON CONFLICT DO UPDATE SET" and
+    "ON CONFLICT DO NOTHING".
   - Improved for option of select and select_p methods in Mojo::Pg::Database
     with a shortcut for "FOR UPDATE".
 

diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -372,8 +372,7 @@ As well as some PostgreSQL specific extensions added by L<SQL::Abstract::Pg>.
 Including operations commonly referred to as C<upsert>.
 
   # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
-  $db->insert(
-    't', {a => 'b'}, {on_conflict => \['(a) do update set a = ?', 'c']});
+  $db->insert('t', {a => 'b'}, {on_conflict => [['a'], {a => 'c'}]);
 
 =head2 insert_p
 

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -21,6 +21,20 @@ sub _insert_returning {
     my ($conflict_sql, @conflict_bind);
     $self->_SWITCH_refkind(
       $conflict => {
+        ARRAYREF => sub {
+          my ($fields, $set) = @$conflict;
+          SQL::Abstract::puke(qq{ARRAYREF value "$fields" not allowed})
+            unless ref $fields eq 'ARRAY';
+          SQL::Abstract::puke(qq{ARRAYREF value "$set" not allowed})
+            unless ref $set eq 'HASH';
+
+          $conflict_sql
+            = '(' . join(', ', map { $self->_quote($_) } @$fields) . ')';
+          $conflict_sql .= $self->_sqlcase(' do update set ');
+          my ($set_sql, @set_bind) = $self->_update_set_values($set);
+          $conflict_sql .= $set_sql;
+          push @conflict_bind, @set_bind;
+        },
         ARRAYREFREF => sub { ($conflict_sql, @conflict_bind) = @$$conflict },
         SCALARREF => sub { $conflict_sql = $$conflict },
         UNDEF     => sub { $conflict_sql = $self->_sqlcase('do nothing') }
@@ -124,7 +138,8 @@ 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 C<undef> to pass C<DO NOTHING>, scalar
+C<ON CONFLICT> clauses. So far C<undef> to pass C<DO NOTHING>, array references
+to pass C<DO UPDATE> with conflict targets and a C<SET> expression, scalar
 references to pass literal SQL and array reference references to pass literal
 SQL with bind values are supported.
 
@@ -136,6 +151,9 @@ SQL with bind values are supported.
 
 This includes operations commonly referred to as C<upsert>.
 
+  # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
+  $abstract->insert('t', {a => 'b'}, {on_conflict => [['a'], {a => 'c'}]);
+
   # "insert into t (a) values ('b') on conflict (a) do update set a = 'c'"
   $abstract->insert(
     't', {a => 'b'}, {on_conflict => \['(a) do update set a = ?', 'c']});
@@ -173,8 +191,8 @@ with C<LIMIT> and C<OFFSET> clauses.
 =head2 GROUP BY
 
 The C<group_by> option can be used to generate C<SELECT> queries with
-C<GROUP BY> clauses. So far only array references to pass a list of fields and
-scalar references to pass literal SQL are supported.
+C<GROUP BY> clauses. So far array references to pass a list of fields and scalar
+references to pass literal SQL are supported.
 
   # "select * from some_table group by foo, bar"
   $abstract->select('some_table', '*', undef, {group_by => ['foo', 'bar']});
@@ -185,8 +203,8 @@ scalar references to pass literal SQL are supported.
 =head2 FOR
 
 The C<for> option can be used to generate C<SELECT> queries with C<FOR> clauses.
-So far only the scalar value C<update> to pass C<UPDATE> and scalar references
-to pass literal SQL are supported.
+So far the scalar value C<update> to pass C<UPDATE> and scalar references to
+pass literal SQL are supported.
 
   # "select * from some_table for update"
   $abstract->select('some_table', '*', undef, {for => 'update'});

diff --git a/t/crud.t b/t/crud.t
@@ -33,8 +33,8 @@ is_deeply $db->select('crud_test')->hashes->to_array,
 $db->insert('crud_test', {id => 1, name => 'foo'}, {on_conflict => undef});
 $db->insert(
   'crud_test',
-  {id          => 2,                               name => 'bar'},
-  {on_conflict => \['(id) do update set name = ?', 'baz']}
+  {id          => 2,       name  => 'bar'},
+  {on_conflict => [['id'], {name => 'baz'}]}
 );
 
 # Read

diff --git a/t/sql.t b/t/sql.t
@@ -42,10 +42,25 @@ $result = [
   'baz', 'yada'
 ];
 is_deeply \@sql, $result, 'right query';
+@sql = $abstract->insert(
+  'foo',
+  {bar         => 'baz'},
+  {on_conflict => [['foo'], {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 => []}) };
-like $@, qr/ARRAYREF/, 'right error';
+eval { $abstract->insert('foo', {bar => 'baz'}, {on_conflict => [{}]}) };
+like $@, qr/ARRAYREF value "HASH/, 'right error';
+eval { $abstract->insert('foo', {bar => 'baz'}, {on_conflict => [[], []]}) };
+like $@, qr/ARRAYREF value "ARRAY/, 'right error';
+eval { $abstract->insert('foo', {bar => 'baz'}, {on_conflict => {}}) };
+like $@, qr/HASHREF/, 'right error';
 
 # ORDER BY
 @sql = $abstract->select('foo', '*', {bar => 'baz'}, {-desc => 'yada'});