add basic support for on_conflict option to insert and insert_p metho…

…ds in Mojo::Pg::Database
mojolicious · Jan 27, 2018 · aafdc0a · aafdc0a
1 parent a84b860
commit aafdc0a
Show file tree

Hide file tree

Showing 7 changed files with 101 additions and 19 deletions.
diff --git a/Changes b/Changes
@@ -1,6 +1,10 @@
 
 4.06  2018-01-27
+  - Added support for on_conflict option to insert and insert_p methods in
+    Mojo::Pg::Database.
   - Updated SQL::Abstract requirement to 1.84.
+  - Improved error messages generated by SQL::Abstract::Pg to be compatible with
+    SQL::Abstract.
 
 4.05  2018-01-26
   - Added support for for, group_by, limit, offset and order_by options to

diff --git a/Makefile.PL b/Makefile.PL
@@ -30,6 +30,6 @@ WriteMakefile(
     },
   },
   PREREQ_PM =>
-    {'DBD::Pg' => 3.005001, Mojolicious => '7.53', 'SQL::Abstract' => '1.84'},
+    {'DBD::Pg' => 3.005001, Mojolicious => '7.53', 'SQL::Abstract' => '1.85'},
   test => {TESTS => 't/*.t t/*/*.t'}
 );
diff --git a/lib/Mojo/Pg/Database.pm b/lib/Mojo/Pg/Database.pm
@@ -364,6 +364,9 @@ L<SQL::Abstract>.
   # "insert into some_table (foo) values ('bar') returning id, foo"
   $db->insert('some_table', {foo => 'bar'}, {returning => ['id', 'foo']});
 
+  # "insert into some_table (foo) values ('bar') on conflict do nothing"
+  $db->insert('some_table', {foo => 'bar'}, {on_conflict => \'do nothing'});
+
 =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
@@ -1,7 +1,34 @@
 package SQL::Abstract::Pg;
 use Mojo::Base 'SQL::Abstract';
 
-use Carp 'croak';
+sub insert {
+  my ($self, $table, $data, $options) = @_;
+  local @{$options}{qw(returning _pg_returning)} = (1, 1)
+    if defined $options->{on_conflict} && !$options->{returning};
+  return $self->SUPER::insert($table, $data, $options);
+}
+
+sub _insert_returning {
+  my ($self, $options) = @_;
+
+  delete $options->{returning} if $options->{_pg_returning};
+  my $sql = '';
+
+  # ON CONFLICT
+  if (defined(my $conflict = $options->{on_conflict})) {
+    $self->_SWITCH_refkind(
+      $conflict => {
+        SCALARREF => sub {
+          $sql .= $self->_sqlcase(' on conflict ') . $$conflict;
+        }
+      }
+    );
+  }
+
+  $sql .= $self->SUPER::_insert_returning($options) if $options->{returning};
+
+  return $sql;
+}
 
 sub _order_by {
   my ($self, $arg) = @_;
@@ -11,25 +38,26 @@ sub _order_by {
     if ref $arg ne 'HASH'
     or grep {/^-(?:desc|asc)/i} keys %$arg;
 
-  return $self->_parse($arg);
+  return $self->_pg_parse($arg);
 }
 
-sub _parse {
+sub _pg_parse {
   my ($self, $options) = @_;
 
   # GROUP BY
   my $sql = '';
   my @bind;
-  if (defined $options->{group_by}) {
-    croak qq{Unsupported group_by value "$options->{group_by}"}
-      unless ref $options->{group_by} eq 'SCALAR';
-    $sql .= $self->_sqlcase(' group by ') . ${$options->{group_by}};
+  if (defined(my $group = $options->{group_by})) {
+    $self->_SWITCH_refkind(
+      $group => {
+        SCALARREF => sub { $sql .= $self->_sqlcase(' group by ') . $$group }
+      }
+    );
   }
 
   # ORDER BY
-  if (defined $options->{order_by}) {
-    $sql .= $self->_order_by($options->{order_by});
-  }
+  $sql .= $self->_order_by($options->{order_by})
+    if defined $options->{order_by};
 
   # LIMIT
   if (defined $options->{limit}) {
@@ -44,10 +72,12 @@ sub _parse {
   }
 
   # FOR
-  if (defined $options->{for}) {
-    croak qq{Unsupported for value "$options->{for}"}
-      unless ref $options->{for} eq 'SCALAR';
-    $sql .= $self->_sqlcase(' for ') . ${$options->{for}};
+  if (defined(my $for = $options->{for})) {
+    $self->_SWITCH_refkind(
+      $for => {
+        SCALARREF => sub { $sql .= $self->_sqlcase(' for ') . $$for }
+      }
+    );
   }
 
   return $sql, @bind;
@@ -70,6 +100,20 @@ SQL::Abstract::Pg - PostgreSQL Magic
 L<SQL::Abstract::Pg> extends L<SQL::Abstract> with a few PostgreSQL features
 used by L<Mojo::Pg>.
 
+=head1 INSERT
+
+=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.
+
+  # "insert into some_table (foo) values ('bar') on conflict do nothing"
+  $abstract->insert(
+    'some_table', {foo => 'bar'}, {on_conflict => \'do nothing'});
+
+=head1 SELECT
+
 =head2 ORDER BY
 
   $abstract->select($source, $fields, $where, $order);

diff --git a/t/crud.t b/t/crud.t
@@ -30,6 +30,11 @@ 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'}
+);
 
 # Read
 is_deeply $db->select('crud_test')->hashes->to_array,

diff --git a/t/pod_coverage.t b/t/pod_coverage.t
@@ -7,4 +7,7 @@ plan skip_all => 'set TEST_POD to enable this test (developer only!)'
 plan skip_all => 'Test::Pod::Coverage 1.04+ required for this test!'
   unless eval 'use Test::Pod::Coverage 1.04; 1';
 
-all_pod_coverage_ok();
+my %RULES = ('SQL::Abstract::Pg' => {also_private => ['insert']},);
+pod_coverage_ok($_, $RULES{$_} || {}) for all_modules();
+
+done_testing();
diff --git a/t/sql.t b/t/sql.t
@@ -6,11 +6,34 @@ use Mojo::Pg;
 # Basics
 my $pg       = Mojo::Pg->new;
 my $abstract = $pg->abstract;
+is_deeply [$abstract->insert('foo', {bar => 'baz'})],
+  ['INSERT INTO "foo" ( "bar") VALUES ( ? )', 'baz'], 'right query';
 is_deeply [$abstract->select('foo', '*')], ['SELECT * FROM "foo"'],
   'right query';
 
+# ON CONFLICT
+my @sql
+  = $abstract->insert('foo', {bar => 'baz'}, {on_conflict => \'do nothing'});
+is_deeply \@sql,
+  ['INSERT INTO "foo" ( "bar") VALUES ( ? ) ON CONFLICT do nothing', 'baz'],
+  'right query';
+@sql = $abstract->insert(
+  'foo',
+  {bar         => 'baz'},
+  {on_conflict => \'do nothing', returning => '*'}
+);
+my $result = [
+  'INSERT INTO "foo" ( "bar") VALUES ( ? ) ON CONFLICT do nothing RETURNING *',
+  'baz'
+];
+is_deeply \@sql, $result, 'right query';
+
+# ON CONFLICT (unsupported value)
+eval { $abstract->insert('foo', {bar => 'baz'}, {on_conflict => []}) };
+like $@, qr/ARRAYREF/, 'right error';
+
 # ORDER BY
-my @sql = $abstract->select('foo', '*', {bar => 'baz'}, {-desc => 'yada'});
+@sql = $abstract->select('foo', '*', {bar => 'baz'}, {-desc => 'yada'});
 is_deeply \@sql,
   ['SELECT * FROM "foo" WHERE ( "bar" = ? ) ORDER BY "yada" DESC', 'baz'],
   'right query';
@@ -30,14 +53,14 @@ is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY bar, baz'], 'right query';
 
 # GROUP BY (unsupported value)
 eval { $abstract->select('foo', '*', undef, {group_by => []}) };
-like $@, qr/Unsupported group_by value "ARRAY/, 'right error';
+like $@, qr/ARRAYREF/, 'right error';
 
 # FOR
 @sql = $abstract->select('foo', '*', undef, {for => \'update skip locked'});
 is_deeply \@sql, ['SELECT * FROM "foo" FOR update skip locked'], 'right query';
 
 # FOR (unsupported value)
 eval { $abstract->select('foo', '*', undef, {for => []}) };
-like $@, qr/Unsupported for value "ARRAY/, 'right error';
+like $@, qr/ARRAYREF/, 'right error';
 
 done_testing();