Hello community,
here is the log from the commit of package perl-Mojo-Pg for openSUSE:Factory checked in at 2018-02-03 15:42:23
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Comparing /work/SRC/openSUSE:Factory/perl-Mojo-Pg (Old)
and /work/SRC/openSUSE:Factory/.perl-Mojo-Pg.new (New)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Package is "perl-Mojo-Pg"
Sat Feb 3 15:42:23 2018 rev:11 rq:571366 version:4.08
Changes:
--------
--- /work/SRC/openSUSE:Factory/perl-Mojo-Pg/perl-Mojo-Pg.changes 2018-01-26 13:39:08.326305707 +0100
+++ /work/SRC/openSUSE:Factory/.perl-Mojo-Pg.new/perl-Mojo-Pg.changes 2018-02-03 15:42:25.601583722 +0100
@@ -1,0 +2,35 @@
+Wed Jan 31 12:44:52 UTC 2018 - coolo@suse.com
+
+- updated to 4.08
+ see /usr/share/doc/packages/perl-Mojo-Pg/Changes
+
+ 4.08 2018-01-29
+ - Improved on_conflict option of insert and insert_p methods in
+ Mojo::Pg::Database with a shortcut for simple conflict targets.
+
+ 4.07 2018-01-28
+ - Added support for "JOIN" to select and select_p methods in
+ Mojo::Pg::Database.
+ - Added support for field aliases to select and select_p methods in
+ Mojo::Pg::Database.
+ - Added support for having option to select and select_p methods in
+ Mojo::Pg::Database.
+ - Improved on_conflict option of insert and insert_p methods in
+ 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".
+
+ 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
+ select and select_p methods in Mojo::Pg::Database.
+ - Added module SQL::Abstract::Pg.
+
+-------------------------------------------------------------------
Old:
----
Mojo-Pg-4.04.tar.gz
New:
----
Mojo-Pg-4.08.tar.gz
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Other differences:
------------------
++++++ perl-Mojo-Pg.spec ++++++
--- /var/tmp/diff_new_pack.Moz5JA/_old 2018-02-03 15:42:26.129559063 +0100
+++ /var/tmp/diff_new_pack.Moz5JA/_new 2018-02-03 15:42:26.133558876 +0100
@@ -1,7 +1,7 @@
#
# spec file for package perl-Mojo-Pg
#
-# Copyright (c) 2017 SUSE LINUX GmbH, Nuernberg, Germany.
+# Copyright (c) 2018 SUSE LINUX GmbH, Nuernberg, Germany.
#
# All modifications and additions to the file contributed by third parties
# remain the property of their copyright owners, unless otherwise agreed
@@ -17,7 +17,7 @@
Name: perl-Mojo-Pg
-Version: 4.04
+Version: 4.08
Release: 0
%define cpan_name Mojo-Pg
Summary: Mojolicious ♥ PostgreSQL
@@ -32,10 +32,10 @@
BuildRequires: perl-macros
BuildRequires: perl(DBD::Pg) >= 3.005001
BuildRequires: perl(Mojolicious) >= 7.53
-BuildRequires: perl(SQL::Abstract) >= 1.81
+BuildRequires: perl(SQL::Abstract) >= 1.85
Requires: perl(DBD::Pg) >= 3.005001
Requires: perl(Mojolicious) >= 7.53
-Requires: perl(SQL::Abstract) >= 1.81
+Requires: perl(SQL::Abstract) >= 1.85
%{perl_requires}
%description
++++++ Mojo-Pg-4.04.tar.gz -> Mojo-Pg-4.08.tar.gz ++++++
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/Changes new/Mojo-Pg-4.08/Changes
--- old/Mojo-Pg-4.04/Changes 2017-12-16 16:24:58.000000000 +0100
+++ new/Mojo-Pg-4.08/Changes 2018-01-29 18:59:30.000000000 +0100
@@ -1,4 +1,33 @@
+4.08 2018-01-29
+ - Improved on_conflict option of insert and insert_p methods in
+ Mojo::Pg::Database with a shortcut for simple conflict targets.
+
+4.07 2018-01-28
+ - Added support for "JOIN" to select and select_p methods in
+ Mojo::Pg::Database.
+ - Added support for field aliases to select and select_p methods in
+ Mojo::Pg::Database.
+ - Added support for having option to select and select_p methods in
+ Mojo::Pg::Database.
+ - Improved on_conflict option of insert and insert_p methods in
+ 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".
+
+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
+ select and select_p methods in Mojo::Pg::Database.
+ - Added module SQL::Abstract::Pg.
+
4.04 2017-12-16
- Added db attribute to Mojo::Pg::Results.
- Added sql_for method to Mojo::Pg::Migrations.
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/MANIFEST new/Mojo-Pg-4.08/MANIFEST
--- old/Mojo-Pg-4.04/MANIFEST 2017-12-17 19:00:55.000000000 +0100
+++ new/Mojo-Pg-4.08/MANIFEST 2018-01-29 19:29:36.000000000 +0100
@@ -19,6 +19,7 @@
lib/Mojo/Pg/PubSub.pm
lib/Mojo/Pg/Results.pm
lib/Mojo/Pg/Transaction.pm
+lib/SQL/Abstract/Pg.pm
LICENSE
Makefile.PL
MANIFEST This list of files
@@ -34,5 +35,6 @@
t/pod_coverage.t
t/pubsub.t
t/results.t
+t/sql.t
META.yml Module YAML meta-data (added by MakeMaker)
META.json Module JSON meta-data (added by MakeMaker)
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/META.json new/Mojo-Pg-4.08/META.json
--- old/Mojo-Pg-4.04/META.json 2017-12-17 19:00:55.000000000 +0100
+++ new/Mojo-Pg-4.08/META.json 2018-01-29 19:29:36.000000000 +0100
@@ -36,7 +36,7 @@
"requires" : {
"DBD::Pg" : "3.005001",
"Mojolicious" : "7.53",
- "SQL::Abstract" : "1.81",
+ "SQL::Abstract" : "1.85",
"perl" : "5.010001"
}
}
@@ -57,6 +57,6 @@
},
"x_IRC" : "irc://irc.perl.org/#mojo"
},
- "version" : "4.04",
- "x_serialization_backend" : "JSON::PP version 2.97000"
+ "version" : "4.08",
+ "x_serialization_backend" : "JSON::PP version 2.97001"
}
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/META.yml new/Mojo-Pg-4.08/META.yml
--- old/Mojo-Pg-4.04/META.yml 2017-12-17 19:00:55.000000000 +0100
+++ new/Mojo-Pg-4.08/META.yml 2018-01-29 19:29:36.000000000 +0100
@@ -22,7 +22,7 @@
requires:
DBD::Pg: '3.005001'
Mojolicious: '7.53'
- SQL::Abstract: '1.81'
+ SQL::Abstract: '1.85'
perl: '5.010001'
resources:
IRC: irc://irc.perl.org/#mojo
@@ -30,5 +30,5 @@
homepage: http://mojolicious.org
license: http://www.opensource.org/licenses/artistic-license-2.0
repository: https://github.com/kraih/mojo-pg.git
-version: '4.04'
+version: '4.08'
x_serialization_backend: 'CPAN::Meta::YAML version 0.018'
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/Makefile.PL new/Mojo-Pg-4.08/Makefile.PL
--- old/Mojo-Pg-4.04/Makefile.PL 2017-11-04 16:20:03.000000000 +0100
+++ new/Mojo-Pg-4.08/Makefile.PL 2018-01-27 13:36:58.000000000 +0100
@@ -30,6 +30,6 @@
},
},
PREREQ_PM =>
- {'DBD::Pg' => 3.005001, Mojolicious => '7.53', 'SQL::Abstract' => '1.81'},
+ {'DBD::Pg' => 3.005001, Mojolicious => '7.53', 'SQL::Abstract' => '1.85'},
test => {TESTS => 't/*.t t/*/*.t'}
);
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/examples/blog/lib/Blog/Model/Posts.pm new/Mojo-Pg-4.08/examples/blog/lib/Blog/Model/Posts.pm
--- old/Mojo-Pg-4.04/examples/blog/lib/Blog/Model/Posts.pm 2017-12-14 19:43:40.000000000 +0100
+++ new/Mojo-Pg-4.08/examples/blog/lib/Blog/Model/Posts.pm 2018-01-26 19:33:07.000000000 +0100
@@ -12,7 +12,7 @@
sub find {
my ($self, $id) = @_;
- return $self->pg->db->select('posts', undef, {id => $id})->hash;
+ return $self->pg->db->select('posts', '*', {id => $id})->hash;
}
sub remove {
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/lib/Mojo/Pg/Database.pm new/Mojo-Pg-4.08/lib/Mojo/Pg/Database.pm
--- old/Mojo-Pg-4.04/lib/Mojo/Pg/Database.pm 2017-12-16 16:23:31.000000000 +0100
+++ new/Mojo-Pg-4.08/lib/Mojo/Pg/Database.pm 2018-01-29 19:25:20.000000000 +0100
@@ -279,8 +279,8 @@
my $results = $db->delete($table, \%where, \%options);
Generate a C<DELETE> statement with LMojo::Pg/"abstract" (usually an
-LSQL::Abstract object) and execute it with L"query">. You can also append a
-callback to perform operations non-blocking.
+LSQL::Abstract::Pg object) and execute it with L"query">. You can also
+append a callback to perform operations non-blocking.
$db->delete(some_table => sub {
my ($db, $err, $results) = @_;
@@ -340,8 +340,8 @@
my $results = $db->insert($table, \@values || \%fieldvals, \%options);
Generate an C<INSERT> statement with LMojo::Pg/"abstract" (usually an
-LSQL::Abstract object) and execute it with L"query">. You can also append a
-callback to perform operations non-blocking.
+LSQL::Abstract::Pg object) and execute it with L"query">. You can also
+append a callback to perform operations non-blocking.
$db->insert(some_table => {foo => 'bar'} => sub {
my ($db, $err, $results) = @_;
@@ -364,6 +364,16 @@
# "insert into some_table (foo) values ('bar') returning id, foo"
$db->insert('some_table', {foo => 'bar'}, {returning => ['id', 'foo']});
+As well as some PostgreSQL specific extensions added by LSQL::Abstract::Pg.
+
+ # "insert into some_table (foo) values ('bar') on conflict do nothing"
+ $db->insert('some_table', {foo => 'bar'}, {on_conflict => undef});
+
+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 => {a => 'c'}]});
+
=head2 insert_p
my $promise = $db->insert_p($table, \@values || \%fieldvals, \%options);
@@ -463,11 +473,11 @@
=head2 select
- my $results = $db->select($source, $fields, $where, $order);
+ my $results = $db->select($source, $fields, $where, \%options);
Generate a C<SELECT> statement with LMojo::Pg/"abstract" (usually an
-LSQL::Abstract object) and execute it with L"query">. You can also append a
-callback to perform operations non-blocking.
+LSQL::Abstract::Pg object) and execute it with L"query">. You can also
+append a callback to perform operations non-blocking.
$db->select(some_table => ['foo'] => {bar => 'yada'} => sub {
my ($db, $err, $results) = @_;
@@ -487,15 +497,46 @@
# "select * from some_table where foo = 'bar'"
$db->select('some_table', undef, {foo => 'bar'});
- # "select * from some_table where foo = 'bar' order by id desc"
- $db->select('some_table', undef, {foo => 'bar'}, {-desc => 'id'});
-
# "select * from some_table where foo like '%test%'"
$db->select('some_table', undef, {foo => {-like => '%test%'}});
+As well as some PostgreSQL specific extensions added by LSQL::Abstract::Pg.
+
+ # "select * from foo join bar on (bar.foo_id = foo.id)"
+ $db->select(['foo', ['bar', foo_id => 'id']]);
+
+ # "select * from foo left join bar on (bar.foo_id = foo.id)"
+ $db->select(['foo', [-left => 'bar', foo_id => 'id']]);
+
+ # "select foo as bar from some_table"
+ $db->select('some_table', [[foo => 'bar']]);
+
+ # "select extract(epoch from foo) as foo, bar from some_table"
+ $db->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);
+
+Including a new last argument to pass many new options.
+
+ # "select * from some_table where foo = 'bar' order by id desc"
+ $db->select('some_table', '*', {foo => 'bar'}, {order_by => {-desc => 'id'}});
+
+ # "select * from some_table limit 10 offset 20"
+ $db->select('some_table', '*', undef, {limit => 10, offset => 20});
+
+ # "select * from some_table where foo = 23 group by foo, bar"
+ $db->select('some_table', '*', {foo => 23}, {group_by => ['foo', 'bar']});
+
+ # "select * from t where a = 'b' group by c having d = 'e'"
+ $db->select('t', '*', {a => 'b'}, {group_by => ['c'], having => {d => 'e'}});
+
+ # "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'});
+
=head2 select_p
- my $promise = $db->select_p($source, $fields, $where, $order);
+ my $promise = $db->select_p($source, $fields, $where, \%options);
Same as L"select">, but performs all operations non-blocking and returns a
LMojo::Promise object instead of accepting a callback.
@@ -530,8 +571,8 @@
my $results = $db->update($table, \%fieldvals, \%where, \%options);
Generate an C<UPDATE> statement with LMojo::Pg/"abstract" (usually an
-LSQL::Abstract object) and execute it with L"query">. You can also append a
-callback to perform operations non-blocking.
+LSQL::Abstract::Pg object) and execute it with L"query">. You can also
+append a callback to perform operations non-blocking.
$db->update(some_table => {foo => 'baz'} => {foo => 'bar'} => sub {
my ($db, $err, $results) = @_;
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/lib/Mojo/Pg/PubSub.pm new/Mojo-Pg-4.08/lib/Mojo/Pg/PubSub.pm
--- old/Mojo-Pg-4.04/lib/Mojo/Pg/PubSub.pm 2017-12-14 19:43:42.000000000 +0100
+++ new/Mojo-Pg-4.08/lib/Mojo/Pg/PubSub.pm 2018-01-27 02:47:09.000000000 +0100
@@ -46,7 +46,8 @@
notification => sub {
my ($db, $name, $pid, $payload) = @_;
$payload = eval { from_json $payload } if $self->{json}{$name};
- for my $cb (@{$self->{chans}{$name}}) { $self->$cb($payload) }
+ my @cbs = @{$self->{chans}{$name}};
+ for my $cb (@cbs) { $self->$cb($payload) }
}
);
$db->once(close => sub { $self->{pg} and $self->_db if delete $self->{db} });
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/lib/Mojo/Pg.pm new/Mojo-Pg-4.08/lib/Mojo/Pg.pm
--- old/Mojo-Pg-4.04/lib/Mojo/Pg.pm 2017-12-14 19:43:41.000000000 +0100
+++ new/Mojo-Pg-4.08/lib/Mojo/Pg.pm 2018-01-28 20:04:51.000000000 +0100
@@ -8,10 +8,14 @@
use Mojo::Pg::PubSub;
use Mojo::URL;
use Scalar::Util qw(blessed weaken);
-use SQL::Abstract;
+use SQL::Abstract::Pg;
has abstract => sub {
- SQL::Abstract->new(array_datatypes => 1, name_sep => '.', quote_char => '"');
+ SQL::Abstract::Pg->new(
+ array_datatypes => 1,
+ name_sep => '.',
+ quote_char => '"'
+ );
};
has [qw(auto_migrate parent search_path)];
has database_class => 'Mojo::Pg::Database';
@@ -38,7 +42,7 @@
return $pubsub;
};
-our $VERSION = '4.04';
+our $VERSION = '4.08';
sub db { $_[0]->database_class->new(dbh => $_[0]->_prepare, pg => $_[0]) }
@@ -329,11 +333,11 @@
=head2 abstract
my $abstract = $pg->abstract;
- $pg = $pg->abstract(SQL::Abstract->new);
+ $pg = $pg->abstract(SQL::Abstract::Pg->new);
-LSQL::Abstract object used to generate CRUD queries for LMojo::Pg::Database,
-defaults to enabling C and setting C to C<.> and
-C to C<">.
+LSQL::Abstract::Pg object used to generate CRUD queries for
+LMojo::Pg::Database, defaults to enabling C and setting
+C to C<.> and C to C<">.
# Generate WHERE clause and bind values
my($stmt, @bind) = $pg->abstract->where({foo => 'bar', baz => 'yada'});
@@ -533,6 +537,8 @@
=item * LMojo::Pg::Transaction
+=item * LSQL::Abstract::Pg
+
=back
=head1 AUTHOR
@@ -553,13 +559,15 @@
Hernan Lopes
+Peter Rabbitson
+
William Lindley
=back
=head1 COPYRIGHT AND LICENSE
-Copyright (C) 2014-2017, Sebastian Riedel and others.
+Copyright (C) 2014-2018, Sebastian Riedel and others.
This program is free software, you can redistribute it and/or modify it under
the terms of the Artistic License version 2.0.
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/lib/SQL/Abstract/Pg.pm new/Mojo-Pg-4.08/lib/SQL/Abstract/Pg.pm
--- old/Mojo-Pg-4.04/lib/SQL/Abstract/Pg.pm 1970-01-01 01:00:00.000000000 +0100
+++ new/Mojo-Pg-4.08/lib/SQL/Abstract/Pg.pm 2018-01-29 19:28:37.000000000 +0100
@@ -0,0 +1,314 @@
+package SQL::Abstract::Pg;
+use Mojo::Base 'SQL::Abstract';
+
+BEGIN { *puke = \&SQL::Abstract::puke }
+
+sub insert {
+ my ($self, $table, $data, $options) = @_;
+ local @{$options}{qw(returning _pg_returning)} = (1, 1)
+ if exists $options->{on_conflict} && !$options->{returning};
+ return $self->SUPER::insert($table, $data, $options);
+}
+
+sub select {
+ my ($self, $table, $fields, @args) = @_;
+
+ if (ref $fields eq 'ARRAY') {
+ my @fields;
+ for my $field (@$fields) {
+ if (ref $field eq 'ARRAY') {
+ puke 'field alias must be in the form [$name => $alias]' if @$field < 2;
+ push @fields,
+ $self->_quote($field->[0])
+ . $self->_sqlcase(' as ')
+ . $self->_quote($field->[1]);
+ }
+ elsif (ref $field eq 'SCALAR') { push @fields, $$field }
+ else { push @fields, $self->_quote($field) }
+ }
+ $fields = join ', ', @fields;
+ }
+
+ return $self->SUPER::select($table, $fields, @args);
+}
+
+sub _insert_returning {
+ my ($self, $options) = @_;
+
+ delete $options->{returning} if $options->{_pg_returning};
+
+ # ON CONFLICT
+ my $sql = '';
+ my @bind;
+ if (exists $options->{on_conflict}) {
+ my $conflict = $options->{on_conflict};
+ my ($conflict_sql, @conflict_bind);
+ $self->_SWITCH_refkind(
+ $conflict => {
+ ARRAYREF => sub {
+ my ($target, $set) = @$conflict;
+ puke 'on_conflict value must be in the form [$target, \%set]'
+ unless ref $set eq 'HASH';
+
+ $conflict_sql = '(' . $self->_quote($target) . ')';
+ $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') }
+ }
+ );
+ $sql .= $self->_sqlcase(' on conflict ') . $conflict_sql;
+ push @bind, @conflict_bind;
+ }
+
+ $sql .= $self->SUPER::_insert_returning($options) if $options->{returning};
+
+ return $sql, @bind;
+}
+
+sub _order_by {
+ my ($self, $options) = @_;
+
+ # Legacy
+ return $self->SUPER::_order_by($options)
+ if ref $options ne 'HASH'
+ or grep {/^-(?:desc|asc)/i} keys %$options;
+
+ # GROUP BY
+ my $sql = '';
+ my @bind;
+ if (defined(my $group = $options->{group_by})) {
+ my $group_sql;
+ $self->_SWITCH_refkind(
+ $group => {
+ ARRAYREF => sub {
+ $group_sql = join ', ', map { $self->_quote($_) } @$group;
+ },
+ SCALARREF => sub { $group_sql = $$group }
+ }
+ );
+ $sql .= $self->_sqlcase(' group by ') . $group_sql;
+ }
+
+ # HAVING
+ if (defined(my $having = $options->{having})) {
+ my ($having_sql, @having_bind) = $self->_recurse_where($having);
+ $sql .= $self->_sqlcase(' having ') . $having_sql;
+ push @bind, @having_bind;
+ }
+
+ # ORDER BY
+ $sql .= $self->_order_by($options->{order_by})
+ if defined $options->{order_by};
+
+ # LIMIT
+ if (defined $options->{limit}) {
+ $sql .= $self->_sqlcase(' limit ') . '?';
+ push @bind, $options->{limit};
+ }
+
+ # OFFSET
+ if (defined $options->{offset}) {
+ $sql .= $self->_sqlcase(' offset ') . '?';
+ push @bind, $options->{offset};
+ }
+
+ # FOR
+ if (defined(my $for = $options->{for})) {
+ my $for_sql;
+ $self->_SWITCH_refkind(
+ $for => {
+ SCALAR => sub {
+ puke qq{for value "$for" is not allowed} unless $for eq 'update';
+ $for_sql = $self->_sqlcase('UPDATE');
+ },
+ SCALARREF => sub { $for_sql .= $$for }
+ }
+ );
+ $sql .= $self->_sqlcase(' for ') . $for_sql;
+ }
+
+ return $sql, @bind;
+}
+
+sub _table {
+ my ($self, $table) = @_;
+
+ return $self->SUPER::_table($table) unless ref $table eq 'ARRAY';
+
+ my (@table, @join);
+ for my $t (@$table) {
+ if (ref $t eq 'ARRAY') { push @join, $t }
+ else { push @table, $t }
+ }
+
+ $table = $self->SUPER::_table(\@table);
+ my $sep = $self->{name_sep} // '';
+ for my $join (@join) {
+ puke 'join must be in the form [$table, $fk => $pk]' if @$join < 3;
+ my $type = @$join > 3 ? shift @$join : '';
+ my ($name, $fk, $pk) = @$join;
+ $table
+ .= $self->_sqlcase($type =~ /^-(.+)$/ ? " $1 join " : ' join ')
+ . $self->_quote($name)
+ . $self->_sqlcase(' on ') . '('
+ . $self->_quote(index($fk, $sep) > 0 ? $fk : "$name.$fk") . ' = '
+ . $self->_quote(index($pk, $sep) > 0 ? $pk : "$table[0].$pk") . ')';
+ }
+
+ return $table;
+}
+
+1;
+
+=encoding utf8
+
+=head1 NAME
+
+SQL::Abstract::Pg - PostgreSQL
+
+=head1 SYNOPSIS
+
+ use SQL::Abstract::Pg;
+
+ my $abstract = SQL::Abstract::Pg->new;
+ say $abstract->select('some_table');
+
+=head1 DESCRIPTION
+
+LSQL::Abstract::Pg extends LSQL::Abstract with a few PostgreSQL features
+used by LMojo::Pg.
+
+=head1 INSERT
+
+ $abstract->insert($table, \@values || \%fieldvals, \%options);
+
+=head2 ON CONFLICT
+
+The C option can be used to generate C<INSERT> queries with
+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.
+
+ # "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>.
+
+ # "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']});
+
+=head1 SELECT
+
+ $abstract->select($source, $fields, $where, $order);
+ $abstract->select($source, $fields, $where, \%options);
+
+=head2 AS
+
+The C<$fields> argument now also accepts array references containing array
+references with field names and aliases, as well as array references containing
+scalar references to pass literal SQL.
+
+ # "select foo as bar from some_table"
+ $abstract->select('some_table', [[foo => 'bar']]);
+
+ # "select foo, bar as baz, yada from some_table"
+ $abstract->select('some_table', ['foo', [bar => 'baz'], 'yada']);
+
+ # "select extract(epoch from foo) as foo, bar from some_table"
+ $abstract->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);
+
+=head2 JOIN
+
+The C<$source> argument now also accepts array references containing not only
+table names, but also array references with tables to generate C<JOIN> clauses
+for.
+
+ # "select * from foo join bar on (bar.foo_id = foo.id)"
+ $abstract->select(['foo', ['bar', foo_id => 'id']]);
+
+ # "select * from foo join bar on (foo.id = bar.foo_id)"
+ $abstract->select(['foo', ['bar', 'foo.id' => 'bar.foo_id']]);
+
+ # "select * from a join b on (b.a_id = a.id) join c on (c.a_id = a.id)"
+ $abstract->select(['a', ['b', a_id => 'id'], ['c', a_id => 'id']]);
+
+ # "select * from foo left join bar on (bar.foo_id = foo.id)"
+ $abstract->select(['foo', [-left => 'bar', foo_id => 'id']]);
+
+=head2 ORDER BY
+
+Alternatively to the C<$order> argument accepted by LSQL::Abstract you can now
+also pass a hash reference with various options. This includes C,
+which takes the same values as the C<$order> argument.
+
+ # "select * from some_table order by foo desc"
+ $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, {limit => 10});
+
+ # "select * from some_table offset 5"
+ $abstract->select('some_table', '*', undef, {offset => 5});
+
+ # "select * from some_table limit 10 offset 5"
+ $abstract->select('some_table', '*', undef, {limit => 10, offset => 5});
+
+=head2 GROUP BY
+
+The C option can be used to generate C<SELECT> queries with
+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']});
+
+ # "select * from some_table group by foo, bar"
+ $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});
+
+=head2 HAVING
+
+The C<having> option can be used to generate C<SELECT> queries with C<HAVING>
+clauses, which takes the same values as the C<$where> argument.
+
+ # "select * from t group by a having b = 'c'"
+ $abstract->select('t', '*', undef, {group_by => ['a'], having => {b => 'c'}});
+
+=head2 FOR
+
+The C<for> option can be used to generate C<SELECT> queries with C<FOR> clauses.
+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'});
+
+ # "select * from some_table for update skip locked"
+ $abstract->select('some_table', '*', undef, {for => \'update skip locked'});
+
+=head1 METHODS
+
+LSQL::Abstract::Pg inherits all methods from LSQL::Abstract.
+
+=head1 SEE ALSO
+
+LMojo::Pg, LMojolicious::Guides, Lhttp://mojolicious.org.
+
+=cut
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/t/crud.t new/Mojo-Pg-4.08/t/crud.t
--- old/Mojo-Pg-4.04/t/crud.t 2017-11-02 19:47:07.000000000 +0100
+++ new/Mojo-Pg-4.08/t/crud.t 2018-01-29 19:24:45.000000000 +0100
@@ -30,41 +30,51 @@
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 => undef});
+$db->insert(
+ 'crud_test',
+ {id => 2, name => 'bar'},
+ {on_conflict => [id => {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 => 'baz'}], 'right structure';
+is_deeply $db->select('crud_test', undef, undef, {limit => 1})
+ ->hashes->to_array, [{id => 1, name => 'foo'}], 'right structure';
# Non-blocking read
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';
@@ -107,7 +117,7 @@
$result = undef;
my $first = $pg->db->query_p("select * from crud_test where name = 'promise'");
my $second = $pg->db->query_p("select * from crud_test where name = 'promise'");
-$first->all($second)->then(
+Mojo::Promise->all($first, $second)->then(
sub {
my ($first, $second) = @_;
$result = [$first->[0]->hash, $second->[0]->hash];
@@ -132,6 +142,27 @@
$db->dollar_only->query_p('does_not_exist')->catch(sub { $fail = shift })->wait;
like $fail, qr/does_not_exist/, 'right error';
+# Join
+$db->query(
+ 'create table if not exists crud_test4 (
+ id serial primary key,
+ test1 text
+ )'
+);
+$db->query(
+ 'create table if not exists crud_test5 (
+ id serial primary key,
+ test2 text
+ )'
+);
+$db->insert('crud_test4', {test1 => 'hello'});
+$db->insert('crud_test5', {test2 => 'world'});
+is_deeply $db->select(['crud_test4', ['crud_test5', id => 'id']],
+ ['crud_test4.id', 'test1', 'test2', ['crud_test4.test1' => 'test3']])
+ ->hashes->to_array,
+ [{id => 1, test1 => 'hello', test2 => 'world', test3 => 'hello'}],
+ 'right structure';
+
# Clean up once we are done
$pg->db->query('drop schema mojo_crud_test cascade');
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/t/pod_coverage.t new/Mojo-Pg-4.08/t/pod_coverage.t
--- old/Mojo-Pg-4.04/t/pod_coverage.t 2017-06-24 15:01:30.000000000 +0200
+++ new/Mojo-Pg-4.08/t/pod_coverage.t 2018-01-28 15:13:40.000000000 +0100
@@ -7,4 +7,8 @@
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', 'puke', 'select']},);
+pod_coverage_ok($_, $RULES{$_} || {}) for all_modules();
+
+done_testing();
diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/Mojo-Pg-4.04/t/sql.t new/Mojo-Pg-4.08/t/sql.t
--- old/Mojo-Pg-4.04/t/sql.t 1970-01-01 01:00:00.000000000 +0100
+++ new/Mojo-Pg-4.08/t/sql.t 2018-01-29 19:24:41.000000000 +0100
@@ -0,0 +1,169 @@
+use Mojo::Base -strict;
+
+use Test::More;
+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';
+is_deeply [$abstract->select(['foo', 'bar', 'baz'])],
+ ['SELECT * FROM "foo", "bar", "baz"'], '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 => undef});
+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';
+@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';
+@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/on_conflict value must be in the form \[\$target, \\\%set\]/,
+ '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'});
+is_deeply \@sql,
+ ['SELECT * FROM "foo" WHERE ( "bar" = ? ) ORDER BY "yada" DESC', 'baz'],
+ 'right query';
+@sql = $abstract->select('foo', '*', {bar => 'baz'},
+ {order_by => {-desc => 'yada'}});
+is_deeply \@sql,
+ ['SELECT * FROM "foo" WHERE ( "bar" = ? ) ORDER BY "yada" DESC', 'baz'],
+ 'right query';
+
+# LIMIT/OFFSET
+@sql = $abstract->select('foo', '*', undef, {limit => 10, offset => 5});
+is_deeply \@sql, ['SELECT * FROM "foo" LIMIT ? OFFSET ?', 10, 5], 'right query';
+
+# GROUP BY
+@sql = $abstract->select('foo', '*', undef, {group_by => \'bar, baz'});
+is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY bar, baz'], 'right query';
+@sql = $abstract->select('foo', '*', undef, {group_by => ['bar', 'baz']});
+is_deeply \@sql, ['SELECT * FROM "foo" GROUP BY "bar", "baz"'], 'right query';
+
+# HAVING
+@sql = $abstract->select('foo', '*', undef,
+ {group_by => ['bar'], having => {baz => 'yada'}});
+is_deeply \@sql,
+ ['SELECT * FROM "foo" GROUP BY "bar" HAVING "baz" = ?', 'yada'],
+ 'right query';
+@sql = $abstract->select(
+ 'foo', '*',
+ {bar => {'>' => 'baz'}},
+ {group_by => ['bar'], having => {baz => {'<' => 'bar'}}}
+);
+$result = [
+ 'SELECT * FROM "foo" WHERE ( "bar" > ? ) GROUP BY "bar" HAVING "baz" < ?',
+ 'baz', 'bar'
+];
+is_deeply \@sql, $result, 'right query';
+
+# GROUP BY (unsupported value)
+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/for value "update skip locked" is not allowed/, 'right error';
+eval { $abstract->select('foo', '*', undef, {for => []}) };
+like $@, qr/ARRAYREF/, 'right error';
+
+# AS
+@sql = $abstract->select('foo', ['bar', [bar => 'baz'], 'yada']);
+is_deeply \@sql, ['SELECT "bar", "bar" AS "baz", "yada" FROM "foo"'],
+ 'right query';
+@sql = $abstract->select('foo',
+ ['bar', \'extract(epoch from baz) as baz', 'yada']);
+is_deeply \@sql,
+ ['SELECT "bar", extract(epoch from baz) as baz, "yada" FROM "foo"'],
+ 'right query';
+
+# AS (unsupported value)
+eval { $abstract->select('foo', [[]]) };
+like $@, qr/field alias must be in the form \[\$name => \$alias\]/,
+ 'right error';
+
+# JOIN
+@sql = $abstract->select(['foo', ['bar', foo_id => 'id']]);
+is_deeply \@sql,
+ ['SELECT * FROM "foo" JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'],
+ 'right query';
+@sql = $abstract->select(['foo', ['bar', 'foo.id' => 'bar.foo_id']]);
+is_deeply \@sql,
+ ['SELECT * FROM "foo" JOIN "bar" ON ("foo"."id" = "bar"."foo_id")'],
+ 'right query';
+@sql = $abstract->select(
+ ['foo', ['bar', foo_id => 'id'], ['baz', foo_id => 'id']]);
+$result
+ = [ 'SELECT * FROM "foo"'
+ . ' JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'
+ . ' JOIN "baz" ON ("baz"."foo_id" = "foo"."id")'
+ ];
+is_deeply \@sql, $result, 'right query';
+@sql = $abstract->select(['foo', [-left => 'bar', foo_id => 'id']]);
+is_deeply \@sql,
+ ['SELECT * FROM "foo" LEFT JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'],
+ 'right query';
+@sql = $abstract->select(['foo', [-right => 'bar', foo_id => 'id']]);
+is_deeply \@sql,
+ ['SELECT * FROM "foo" RIGHT JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'],
+ 'right query';
+@sql = $abstract->select(['foo', [-inner => 'bar', foo_id => 'id']]);
+is_deeply \@sql,
+ ['SELECT * FROM "foo" INNER JOIN "bar" ON ("bar"."foo_id" = "foo"."id")'],
+ 'right query';
+
+# JOIN (unsupported value)
+eval { $abstract->select(['foo', []]) };
+like $@, qr/join must be in the form \[\$table, \$fk => \$pk\]/, 'right error';
+
+done_testing();