add shortcut for "FOR UPDATE"

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

Hide file tree

Showing 4 changed files with 31 additions and 13 deletions.
diff --git a/Changes b/Changes
@@ -1,7 +1,9 @@
 
 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".
+    Mojo::Pg::Database with a shortcut for "ON CONFLICT DO NOTHING".
+  - Improved for option of select and select_p methods in Mojo::Pg::Database
+    with a shortcut for "FOR UPDATE".
 
 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
@@ -512,6 +512,9 @@ As well as some PostgreSQL specific extensions added by L<SQL::Abstract::Pg>.
   # "select * from some_table where foo = 23 group by foo, bar"
   $db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});
 
+  # "select * from some_table where id = 1 for update"
+  $db->select('some_table', '*', {id => 1}, {for => 'update'});
+
   # "select * from some_table where id = 1 for update skip locked"
   $db->select('some_table', '*', {id => 1}, {for => \'update skip locked'});
 

diff --git a/lib/SQL/Abstract/Pg.pm b/lib/SQL/Abstract/Pg.pm
@@ -21,9 +21,9 @@ sub _insert_returning {
     my ($conflict_sql, @conflict_bind);
     $self->_SWITCH_refkind(
       $conflict => {
-        SCALARREF => sub { $conflict_sql = $$conflict },
         ARRAYREFREF => sub { ($conflict_sql, @conflict_bind) = @$$conflict },
-        UNDEF => sub { $conflict_sql = $self->_sqlcase('do nothing') }
+        SCALARREF => sub { $conflict_sql = $$conflict },
+        UNDEF     => sub { $conflict_sql = $self->_sqlcase('do nothing') }
       }
     );
     $sql .= $self->_sqlcase(' on conflict ') . $conflict_sql;
@@ -56,10 +56,10 @@ sub _pg_parse {
     my $group_sql;
     $self->_SWITCH_refkind(
       $group => {
-        SCALARREF => sub { $group_sql = $$group },
         ARRAYREF => sub {
           $group_sql = join ', ', map { $self->_quote($_) } @$group;
-        }
+        },
+        SCALARREF => sub { $group_sql = $$group }
       }
     );
     $sql .= $self->_sqlcase(' group by ') . $group_sql;
@@ -86,6 +86,11 @@ sub _pg_parse {
     my $for_sql;
     $self->_SWITCH_refkind(
       $for => {
+        SCALAR => sub {
+          SQL::Abstract::puke('only the SCALAR value "update" is allowed')
+            unless $for eq 'update';
+          $for_sql = $self->_sqlcase('UPDATE');
+        },
         SCALARREF => sub { $for_sql .= $$for }
       }
     );
@@ -149,21 +154,21 @@ also pass a hash reference with various options. This includes C<order_by>,
 which takes the same values as the C<$order> argument.
 
   # "select * from some_table order by foo desc"
-  $abstract->select('some_table', undef, undef, {order_by => {-desc => 'foo'}});
+  $abstract->select('some_table', '*', undef, {order_by => {-desc => 'foo'}});
 
 =head2 LIMIT/OFFSET
 
 The C<limit> and C<offset> options can be used to generate C<SELECT> queries
 with C<LIMIT> and C<OFFSET> clauses.
 
   # "select * from some_table limit 10"
-  $abstract->select('some_table', undef, undef, {limit => 10});
+  $abstract->select('some_table', '*', undef, {limit => 10});
 
   # "select * from some_table offset 5"
-  $abstract->select('some_table', undef, undef, {offset => 5});
+  $abstract->select('some_table', '*', undef, {offset => 5});
 
   # "select * from some_table limit 10 offset 5"
-  $abstract->select('some_table', undef, undef, {limit => 10, offset => 5});
+  $abstract->select('some_table', '*', undef, {limit => 10, offset => 5});
 
 =head2 GROUP BY
 
@@ -172,18 +177,22 @@ C<GROUP BY> clauses. So far only 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, undef, {group_by => ['foo', 'bar']});
+  $abstract->select('some_table', '*', undef, {group_by => ['foo', 'bar']});
 
   # "select * from some_table group by foo, bar"
-  $abstract->select('some_table', undef, undef, {group_by => \'foo, bar'});
+  $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});
 
 =head2 FOR
 
 The C<for> option can be used to generate C<SELECT> queries with C<FOR> clauses.
-So far only scalar references to pass literal SQL are supported.
+So far only 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'});
 
   # "select * from some_table for update skip locked"
-  $abstract->select('some_table', undef, undef, {for => \'update skip locked'});
+  $abstract->select('some_table', '*, undef, {for => \'update skip locked'});
 
 =head1 METHODS
 

diff --git a/t/sql.t b/t/sql.t
@@ -73,10 +73,14 @@ eval { $abstract->select('foo', '*', undef, {group_by => {}}) };
 like $@, qr/HASHREF/, 'right error';
 
 # FOR
+@sql = $abstract->select('foo', '*', undef, {for => 'update'});
+is_deeply \@sql, ['SELECT * FROM "foo" FOR UPDATE'], 'right query';
 @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 => 'update skip locked'}) };
+like $@, qr/only the SCALAR value "update" is allowed/, 'right error';
 eval { $abstract->select('foo', '*', undef, {for => []}) };
 like $@, qr/ARRAYREF/, 'right error';