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 - [email protected] + +- 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 L<Mojo::Pg/"abstract"> (usually an -L<SQL::Abstract> object) and execute it with L</"query">. You can also append a -callback to perform operations non-blocking. +L<SQL::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 L<Mojo::Pg/"abstract"> (usually an -L<SQL::Abstract> object) and execute it with L</"query">. You can also append a -callback to perform operations non-blocking. +L<SQL::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 L<SQL::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 L<Mojo::Pg/"abstract"> (usually an -L<SQL::Abstract> object) and execute it with L</"query">. You can also append a -callback to perform operations non-blocking. +L<SQL::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 L<SQL::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 L<Mojo::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 L<Mojo::Pg/"abstract"> (usually an -L<SQL::Abstract> object) and execute it with L</"query">. You can also append a -callback to perform operations non-blocking. +L<SQL::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); -L<SQL::Abstract> object used to generate CRUD queries for L<Mojo::Pg::Database>, -defaults to enabling C<array_datatypes> and setting C<name_sep> to C<.> and -C<quote_char> to C<">. +L<SQL::Abstract::Pg> object used to generate CRUD queries for +L<Mojo::Pg::Database>, defaults to enabling C<array_datatypes> and setting +C<name_sep> to C<.> and C<quote_char> to C<">. # Generate WHERE clause and bind values my($stmt, @bind) = $pg->abstract->where({foo => 'bar', baz => 'yada'}); @@ -533,6 +537,8 @@ =item * L<Mojo::Pg::Transaction> +=item * L<SQL::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 + +L<SQL::Abstract::Pg> extends L<SQL::Abstract> with a few PostgreSQL features +used by L<Mojo::Pg>. + +=head1 INSERT + + $abstract->insert($table, \@values || \%fieldvals, \%options); + +=head2 ON CONFLICT + +The C<on_conflict> 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 L<SQL::Abstract> you can now +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, {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<group_by> 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 + +L<SQL::Abstract::Pg> inherits all methods from L<SQL::Abstract>. + +=head1 SEE ALSO + +L<Mojo::Pg>, L<Mojolicious::Guides>, L<http://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();
