RE: [Dbix-class] SQL::Abstract 1.x subqueries patch
-Message d'origine-
De : Zbigniew Lukasiak [mailto:[EMAIL PROTECTED]
Envoyé : mercredi, 5. novembre 2008 14:58
À : DBIx::Class user and developer list
Objet : Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
I've started testing it with the latest DBIC.
Here is a test I propose:
Index: t/02where.t
===
--- t/02where.t (wersja 5054)
+++ t/02where.t (kopia robocza)
@@ -175,6 +175,18 @@
stmt = WHERE ( (bar ? AND bar ?) AND foo IN (?, ?) ),
bind = [44, 55, 22, 33],
},
+{
+where = {
+ '-and' = [
+ {},
+ {
+ 'me.id' = '1'
+ }
+ ]
+},
+stmt = WHERE ( ( me.id = ? ) ),
+bind = [ 1 ],
+},
);
Currently this is translated into WHERE ( 0 AND ( me.id = ? ) ) and
that causes t/relationship_doesnt_exist.t in DBIC fail. I've tested
that the 1.24 does ignore that spurious clause (it is added in the
'single' method in ResultSet).
Hi,
Thanks for the bug report; fixed in revision 5055 (with your test added).
Cheers, Laurent Dami
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
One thing that I am really missing from SQL::A is a way to specify an
arbitrary SQL fragment together with bindings and have it interpolated
into the rest of the query and bindings. In DBIC with
'search_literal' method and 'where' attribute you can add arbitrary
SQL - but only at the beginning or the end of the query and frequently
it gets the bindings in the wrong order. In standard SQL::A there are
many tricks you can do - but for example clause like:
? REGEXP keyword
cannot be normally represented in SQL::A ( you can try tricks like {
$value = { '' = '' },... } - but there is always something wrong in
the resulting query).
Long time ago I've patched my SQL::A version and added a rule that
double reference to array was resulting in $array[0] being
interpolated into the query and the rest into the bindings array.
This is in a way extending the convention that reference to a scalar
is interpolated as literary SQL. I did not used it for long - and it
is lost now - but I can recreate it if such a hack could be accepted
into the main SQL::A tree.
On Tue, Nov 4, 2008 at 12:03 AM, BUCHMULLER Norbert [EMAIL PROTECTED] wrote:
On Mon, 3 Nov 2008 12:28:40 + Ash Berlin [EMAIL PROTECTED]
wrote:
Yeah, I think the best plan of attack is to move as much out of
DBIC::SQL::A as possible, and then bump the dep on DBIC to SQL::A
1.50.
Matters got bit more complicated: some bits should be merged into
SQL::Abstract::Limit instead. I'll try to contact the
SQL::Abstract::Limit maintainer (Dave Baird) and ask if he has the time
to review/apply patches to SQL::A::L and coordinate a public release with
us.
norbi
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
--
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
RE: [Dbix-class] SQL::Abstract 1.x subqueries patch
-Message d'origine-
De : Zbigniew Lukasiak [mailto:[EMAIL PROTECTED]
Envoyé : mardi, 4. novembre 2008 09:25
À : DBIx::Class user and developer list
Objet : Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
One thing that I am really missing from SQL::A is a way to specify an
arbitrary SQL fragment together with bindings and have it interpolated
into the rest of the query and bindings. In DBIC with
'search_literal' method and 'where' attribute you can add arbitrary
SQL - but only at the beginning or the end of the query and frequently
it gets the bindings in the wrong order. In standard SQL::A there are
many tricks you can do - but for example clause like:
? REGEXP keyword
cannot be normally represented in SQL::A ( you can try tricks like {
$value = { '' = '' },... } - but there is always something wrong in
the resulting query).
Same problem with many other clauses like 'MATCH .. AGAINST ..', etc.
Long time ago I've patched my SQL::A version and added a rule that
double reference to array was resulting in $array[0] being
interpolated into the query and the rest into the bindings array.
This is in a way extending the convention that reference to a scalar
is interpolated as literary SQL. I did not used it for long - and it
is lost now - but I can recreate it if such a hack could be accepted
into the main SQL::A tree.
SQLA1.50 implements that convention : \[$sql, @bind] is interpreted
as literal SQL. There are a couple of other extensions -- have a look
at the LCHANGES section.
Laurent Dami
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On Tue, 4 Nov 2008 09:24:48 +0100 Zbigniew Lukasiak [EMAIL PROTECTED] wrote: One thing that I am really missing from SQL::A is a way to specify an arbitrary SQL fragment together with bindings and have it interpolated into the rest of the query and bindings. Yeah, that exactly was the feature I asked for (and implemented Just Another Patch for it:-) in my original post (the guy who wrote t/07subqueries.t in 1.50_RC called them 'subqueries' so I kept that naming in my post). Fortunately it's already implemented in 1.50_RC, so only making the new SQLA work with DBIC is left. (And it uses the interface you described.) norbi ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On 3 Nov 2008, at 12:28, Ash Berlin wrote: Yeah, I think the best plan of attack is to move as much out of DBIC::SQL::A as possible, and then bump the dep on DBIC to SQL::A 1.50. If you are going to make changes to DBIC, can you please so on a branch (assuming you have commit access, if not ask mst for it) -ash I should have also said that the user facing aspect (i.e. where conditions et al.) needs to stay the same or at lease be backwards compatible. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On 3 Nov 2008, at 11:46, BUCHMULLER Norbert wrote: On Fri, 31 Oct 2008 23:48:56 +0100 Peter Rabbitson [EMAIL PROTECTED] wrote: There is a mostly-working-1.x-rc at http://dev.catalyst.perl.org/repos/bast/SQL-Abstract/1.x/branches/1.50_RC . Patches should be made against it afaik, although it is very possible this particular bit has been already implemented. OK, I didn't know about this RC (I just checked 1.x/trunk and 2.000 trunk). Also the branch suffers from major shortage of volunteers. It passes its own tests but fails reather hard if subjected to the test suite of dbix-class (i.e. added to PERL5LIB before running make test on the dbic trunk). So if you can spend some cycles on it, it would be rather fantastic. I've started to investigate fix some bugs, but it seems that some of them require modifications to DBIC as well. What's the policy (plan) here? Can I modify DBIC in such a way that breaks compatibility with older SQLA versions, or should I attempt to make 1.50 behave the same as prior versions instead (so that DBIC can work with both an older SQLA or 1.50)? You know there's an adaptor module DBIC::SQL::Abstract defined in lib/DBIx/Class/Storage/DBI.pm that contains mostly code that should go into SQL::Abstract - in fact a comment at the beginning confirms that it is just a temp hack. Now this module calls private, undocumented methods of SQLA, and some of those are either gone or behave differently. The cleanest solution would be to migrate to SQLA those part of the code that are useful in general, and add some more public methods to SQLA where necessary (eg. Ccondition(\%where) that only returns the condition, witout the 'WHERE' word prepended to it). But that will break compatibility with older SQLA versions for granted. I'm going to take care of the conversion of DBIC test cases to use SQL::Abstract::Test. I didn't know about the existence of this piece of canned magic :-), but I like it very much. norbi Yeah, I think the best plan of attack is to move as much out of DBIC::SQL::A as possible, and then bump the dep on DBIC to SQL::A 1.50. If you are going to make changes to DBIC, can you please so on a branch (assuming you have commit access, if not ask mst for it) -ash ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On Fri, 31 Oct 2008 23:48:56 +0100 Peter Rabbitson [EMAIL PROTECTED] wrote: There is a mostly-working-1.x-rc at http://dev.catalyst.perl.org/repos/bast/SQL-Abstract/1.x/branches/1.50_RC. Patches should be made against it afaik, although it is very possible this particular bit has been already implemented. OK, I didn't know about this RC (I just checked 1.x/trunk and 2.000 trunk). Also the branch suffers from major shortage of volunteers. It passes its own tests but fails reather hard if subjected to the test suite of dbix-class (i.e. added to PERL5LIB before running make test on the dbic trunk). So if you can spend some cycles on it, it would be rather fantastic. I've started to investigate fix some bugs, but it seems that some of them require modifications to DBIC as well. What's the policy (plan) here? Can I modify DBIC in such a way that breaks compatibility with older SQLA versions, or should I attempt to make 1.50 behave the same as prior versions instead (so that DBIC can work with both an older SQLA or 1.50)? You know there's an adaptor module DBIC::SQL::Abstract defined in lib/DBIx/Class/Storage/DBI.pm that contains mostly code that should go into SQL::Abstract - in fact a comment at the beginning confirms that it is just a temp hack. Now this module calls private, undocumented methods of SQLA, and some of those are either gone or behave differently. The cleanest solution would be to migrate to SQLA those part of the code that are useful in general, and add some more public methods to SQLA where necessary (eg. Ccondition(\%where) that only returns the condition, witout the 'WHERE' word prepended to it). But that will break compatibility with older SQLA versions for granted. I'm going to take care of the conversion of DBIC test cases to use SQL::Abstract::Test. I didn't know about the existence of this piece of canned magic :-), but I like it very much. norbi ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On Mon, 3 Nov 2008 12:32:24 + Ash Berlin [EMAIL PROTECTED] wrote: Yeah, I think the best plan of attack is to move as much out of DBIC::SQL::A as possible, and then bump the dep on DBIC to SQL::A 1.50. Green light, cool. I'll do my best to clean it up. If you are going to make changes to DBIC, can you please so on a branch (assuming you have commit access, if not ask mst for it) Thanks, I'll ask Matt for commit access. I should have also said that the user facing aspect (i.e. where conditions et al.) needs to stay the same or at lease be backwards compatible. Of course. :-) Anyways, I'll probably discuss it here or on ICQ when it will be clear for me what interface changes it will require exactly. norbi ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On Mon, 3 Nov 2008 12:28:40 + Ash Berlin [EMAIL PROTECTED] wrote: Yeah, I think the best plan of attack is to move as much out of DBIC::SQL::A as possible, and then bump the dep on DBIC to SQL::A 1.50. Matters got bit more complicated: some bits should be merged into SQL::Abstract::Limit instead. I'll try to contact the SQL::Abstract::Limit maintainer (Dave Baird) and ask if he has the time to review/apply patches to SQL::A::L and coordinate a public release with us. norbi ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
RE: [Dbix-class] SQL::Abstract 1.x subqueries patch
Hi, I confirm that subqueries are implemented in the 1.50RC branch (have a look at the LCHANGES section). Concerning tests : SQLA 1.50 often generates less parentheses than previous versions. So the output is equivalent in semantics, but not in surface syntax; as a result, the applications will most probably continue to work , but the tests do fail because the best they can do is to check the resulting SQL syntax. It would be quite difficult and rather pointless to try to generate exactly the same parentheses as before; so instead, there is a test module SQL::Abstract::Test that implements a test function is_same_sql_bind(..) that compares SQL statements on abstract level, ignoring unnecessary parentheses. As an example, attached is a a test rewrite for SQL::Abstract::Limit . Yes it would be great if somebody could invest some time in testing it against DBIx-Class. Best regards, Laurent Dami -Message d'origine- De : Peter Rabbitson [mailto:[EMAIL PROTECTED] Envoyé : vendredi, 31. octobre 2008 23:49 À : Class user and developer list Objet : Re: [Dbix-class] SQL::Abstract 1.x subqueries patch BUCHMULLER Norbert wrote: Hi, do you still accept patches for SQL::Abstract 1.x, or should I wait for 2.0 to come out instead? (BTW, what's the proposed time frame for that?) There is a mostly-working-1.x-rc at http://dev.catalyst.perl.org/repos/bast/SQL-Abstract/1.x/branch es/1.50_RC. Patches should be made against it afaik, although it is very possible this particular bit has been already implemented. Also the branch suffers from major shortage of volunteers. It passes its own tests but fails reather hard if subjected to the test suite of dbix-class (i.e. added to PERL5LIB before running make test on the dbic trunk). So if you can spend some cycles on it, it would be rather fantastic. Cheers ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk 01.sql.t Description: 01.sql.t ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] SQL::Abstract 1.x subqueries patch
Hi,
do you still accept patches for SQL::Abstract 1.x, or should I wait for
2.0 to come out instead? (BTW, what's the proposed time frame for that?)
I rather miss the subquery functionality from 1.x to be able to write SQL
stored procedure calls on the right-hand side of WHERE conditions like
this:
{
foo = \[mangle_name(?), $name]
}
With 1.x this is the best workaround I could devise (assuming DBIC):
{
foo = \(
sprintf '= mangle_name(%s)',
$schema-storage-dbh-quote($name)
)
}
And I don't like it. :-)
The attached patch adds subquery support to the 1.x trunk. (Includes the
corresponding tests taken adapted from 2.000 trunk.)
norbiIndex: t/07subqueries.t
===
--- t/07subqueries.t (revision 0)
+++ t/07subqueries.t (revision 0)
@@ -0,0 +1,90 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use Test::More;
+
+use FindBin;
+use lib $FindBin::Bin;
+
+plan tests = 10;
+
+use SQL::Abstract;
+
+my $sql = SQL::Abstract-new;
+
+my (@tests, $sub_stmt, @sub_bind, $where);
+
+#1
+($sub_stmt, @sub_bind) = (SELECT c1 FROM t1 WHERE c2 ? AND c3 LIKE ?,
+ 100, foo%);
+$where = {
+foo = 1234,
+bar = \[IN ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 ? AND c3 LIKE ?) AND foo = ? ),
+ bind = [100, foo%, 1234],
+};
+
+#2
+($sub_stmt, @sub_bind)
+ = $sql-select(t1, c1, {c2 = { = 100},
+ c3 = {-like = foo%}});
+$where = {
+foo = 1234,
+bar = \[ ALL ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( bar ALL (SELECT c1 FROM t1 WHERE ( c2 ? AND c3 LIKE ? )) AND foo = ? ),
+ bind = [100, foo%, 1234],
+};
+
+#3
+($sub_stmt, @sub_bind)
+ = $sql-select(t1, *, {c1 = 1, c2 = \ t0.c0});
+$where = {
+foo = 1234,
+-nest = \[EXISTS ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 t0.c0 )) AND foo = ? ),
+ bind = [1, 1234],
+};
+
+#4
+$where = {
+-nest = \[MATCH (col1, col2) AGAINST (?) = apples],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( MATCH (col1, col2) AGAINST (?) ),
+ bind = [apples],
+};
+
+
+#5
+($sub_stmt, @sub_bind)
+ = $sql-where({age = [{ = 10}, { = 20}]});
+$sub_stmt =~ s/^ where //i; # don't want WHERE in the subclause
+$where = {
+lname = {-like = '%son%'},
+-nest = \[NOT ( $sub_stmt ) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( NOT ( ( ( ( age ? ) OR ( age ? ) ) ) ) AND lname LIKE ? ),
+ bind = [10, 20, '%son%'],
+};
+
+
+
+for (@tests) {
+
+ my($stmt, @bind) = $sql-where($_-{where}, $_-{order});
+ is($stmt, $_-{stmt});
+ is_deeply([EMAIL PROTECTED], $_-{bind});
+}
Index: lib/SQL/Abstract.pm
===
--- lib/SQL/Abstract.pm (revision 5033)
+++ lib/SQL/Abstract.pm (working copy)
@@ -732,7 +732,13 @@
my $v = $where-{$k};
my $label = $self-_quote($k);
-if ($k =~ /^-(\D+)/) {
+if (ref $v eq 'REF' ref $$v eq 'ARRAY') {
+# literal SQL with bind values
+my ($stmt, @bind) = @$$v;
+$self-_debug(ARRAYREF($k) means literal SQL with bind values: [ $stmt @bind ]);
+push @sqlf, ($k ne -nest) ? $label $stmt : $stmt;
+push @sqlv, @bind;
+} elsif ($k =~ /^-(\D+)/) {
# special nesting, like -and, -or, -nest, so shift over
my $subjoin = $self-_modlogic($1);
$self-_debug(OP(-$1) means special logic ($subjoin), recursing...);
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
On Fri, 31 Oct 2008 20:30:54 +0100 BUCHMULLER Norbert
[EMAIL PROTECTED] wrote:
The attached patch adds subquery support to the 1.x trunk. (Includes the
corresponding tests taken adapted from 2.000 trunk.)
Oops, I forgot the documentation. Here goes the patch again.
norbi
Index: t/07subqueries.t
===
--- t/07subqueries.t (revision 0)
+++ t/07subqueries.t (revision 0)
@@ -0,0 +1,90 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use Test::More;
+
+use FindBin;
+use lib $FindBin::Bin;
+
+plan tests = 10;
+
+use SQL::Abstract;
+
+my $sql = SQL::Abstract-new;
+
+my (@tests, $sub_stmt, @sub_bind, $where);
+
+#1
+($sub_stmt, @sub_bind) = (SELECT c1 FROM t1 WHERE c2 ? AND c3 LIKE ?,
+ 100, foo%);
+$where = {
+foo = 1234,
+bar = \[IN ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( bar IN (SELECT c1 FROM t1 WHERE c2 ? AND c3 LIKE ?) AND foo = ? ),
+ bind = [100, foo%, 1234],
+};
+
+#2
+($sub_stmt, @sub_bind)
+ = $sql-select(t1, c1, {c2 = { = 100},
+ c3 = {-like = foo%}});
+$where = {
+foo = 1234,
+bar = \[ ALL ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( bar ALL (SELECT c1 FROM t1 WHERE ( c2 ? AND c3 LIKE ? )) AND foo = ? ),
+ bind = [100, foo%, 1234],
+};
+
+#3
+($sub_stmt, @sub_bind)
+ = $sql-select(t1, *, {c1 = 1, c2 = \ t0.c0});
+$where = {
+foo = 1234,
+-nest = \[EXISTS ($sub_stmt) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( EXISTS (SELECT * FROM t1 WHERE ( c1 = ? AND c2 t0.c0 )) AND foo = ? ),
+ bind = [1, 1234],
+};
+
+#4
+$where = {
+-nest = \[MATCH (col1, col2) AGAINST (?) = apples],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( MATCH (col1, col2) AGAINST (?) ),
+ bind = [apples],
+};
+
+
+#5
+($sub_stmt, @sub_bind)
+ = $sql-where({age = [{ = 10}, { = 20}]});
+$sub_stmt =~ s/^ where //i; # don't want WHERE in the subclause
+$where = {
+lname = {-like = '%son%'},
+-nest = \[NOT ( $sub_stmt ) = @sub_bind],
+ };
+push @tests, {
+ where = $where,
+ stmt = WHERE ( NOT ( ( ( ( age ? ) OR ( age ? ) ) ) ) AND lname LIKE ? ),
+ bind = [10, 20, '%son%'],
+};
+
+
+
+for (@tests) {
+
+ my($stmt, @bind) = $sql-where($_-{where}, $_-{order});
+ is($stmt, $_-{stmt});
+ is_deeply([EMAIL PROTECTED], $_-{bind});
+}
Index: lib/SQL/Abstract.pm
===
--- lib/SQL/Abstract.pm (revision 5033)
+++ lib/SQL/Abstract.pm (working copy)
@@ -732,7 +732,13 @@
my $v = $where-{$k};
my $label = $self-_quote($k);
-if ($k =~ /^-(\D+)/) {
+if (ref $v eq 'REF' ref $$v eq 'ARRAY') {
+# literal SQL with bind values
+my ($stmt, @bind) = @$$v;
+$self-_debug(ARRAYREF($k) means literal SQL with bind values: [ $stmt @bind ]);
+push @sqlf, ($k ne -nest) ? $label $stmt : $stmt;
+push @sqlv, @bind;
+} elsif ($k =~ /^-(\D+)/) {
# special nesting, like -and, -or, -nest, so shift over
my $subjoin = $self-_modlogic($1);
$self-_debug(OP(-$1) means special logic ($subjoin), recursing...);
@@ -1273,6 +1279,19 @@
TMTOWTDI.
+You can also pass a literal SQL chunk together with bind values as a reference
+to an arrayref:
+
+my %where = (
+priority = { '', 2 },
+requestor = \[toupper(?), 'mendel']
+);
+
+This would create:
+
+$stmt = WHERE priority ? AND requestor = toupper(?);
+@bind = ('2', 'mendel');
+
These pages could go on for a while, since the nesting of the data
structures this module can handle are pretty much unlimited (the
module implements the CWHERE expansion as a recursive function
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] SQL::Abstract 1.x subqueries patch
BUCHMULLER Norbert wrote: Hi, do you still accept patches for SQL::Abstract 1.x, or should I wait for 2.0 to come out instead? (BTW, what's the proposed time frame for that?) There is a mostly-working-1.x-rc at http://dev.catalyst.perl.org/repos/bast/SQL-Abstract/1.x/branches/1.50_RC. Patches should be made against it afaik, although it is very possible this particular bit has been already implemented. Also the branch suffers from major shortage of volunteers. It passes its own tests but fails reather hard if subjected to the test suite of dbix-class (i.e. added to PERL5LIB before running make test on the dbic trunk). So if you can spend some cycles on it, it would be rather fantastic. Cheers ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
