Luke Opperman | 12 Feb 18:14 2007

SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Bringing this subject up again, had someone done some work on unifying these, in
particular having SelectResults provide the fully constructed query to the DB
layer in the form of a sqlbuilder.Select?

My current desire is to be able to retrieve / replace the full query of a
SelectResults, and to be able to inspect SelectResults query components in the
same manner as sqlbuilder.Select objects.

- Luke

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
Oleg Broytmann | 12 Feb 18:24 2007
X-Face
Picon

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

On Mon, Feb 12, 2007 at 05:14:51PM +0000, Luke Opperman wrote:
> Bringing this subject up again, had someone done some work on unifying these, in
> particular having SelectResults provide the fully constructed query to the DB
> layer in the form of a sqlbuilder.Select?

   I have a desire to do that, but not much time...

> My current desire is to be able to retrieve / replace the full query of a
> SelectResults, and to be able to inspect SelectResults query components in the
> same manner as sqlbuilder.Select objects.

   Do you mean SelectResults.ops["orderBy"], ops["join"], ops["start"],
ops["end"], etc.?

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
Luke Opperman | 12 Feb 18:58 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Oleg Broytmann <phd <at> phd.pp.ru> writes:
> 
>    I have a desire to do that, but not much time...

Not swimming in time here, but this is looking like a promising approach to my
current critical work.

>    Do you mean SelectResults.ops["orderBy"], ops["join"], ops["start"],
> ops["end"], etc.?

Indeed, although I'd probably leave that interface alone at the moment and
instead provide a SelectResults.select (SelectResults.query?) that is the
sqlbuilder.Select, with it's .orderBy, .limit, etc. Adding to each's underlying
format where functionality is currently missing on one or the other.

But in the end I don't really intend to interact directly with the underlying
storage format in either case, because I can also see a quickly-approaching
desire to have matched interfaces for the SelectResults methods like filter,
reversed, accumulate*, etc. That is, part of my intention is to be able to write:

x.filter(Phone.q.id==PhoneCall.q.phoneID).orderBy(PhoneCall.q.callDate)

Not caring whether x is a Select or SelectResults, ie whether x is bound to
return a specific SQLObject class yet, etc. And then going the other way, asking
to get back X-type (SQLObject-based) records from this query:

X.select(query=x)

Or use it as an aliased join criteria:

(Continue reading)

Oleg Broytmann | 12 Feb 19:13 2007
X-Face
Picon

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

On Mon, Feb 12, 2007 at 05:58:13PM +0000, Luke Opperman wrote:
> x.filter(Phone.q.id==PhoneCall.q.phoneID).orderBy(PhoneCall.q.callDate)
> 
> Not caring whether x is a Select or SelectResults

   That would be hard to implement, I am afraid. Select() is (must be) much
more generic, supporting groupBy and many other features of SQL SELECT...

> I'm thinking that sqlbuilder.Table (or at least AliasTable) should also
> eventually get in on this fun, probably growing a .select method that returns a
> Select that can then be filter'ed etc.

   Why? sqlbuilder.Table is (was) an internal object used in q-magic, why
bring it to the front?

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
Luke Opperman | 12 Feb 20:37 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Oleg Broytmann <phd <at> phd.pp.ru> writes:

>    That would be hard to implement, I am afraid. Select() is (must be) much
> more generic, supporting groupBy and many other features of SQL SELECT...

Correct that Select by itself must be more generic, I wouldn't be opening up
direct select() kw access to groupBy on SelectResults. But it's that flexibility
that I'm looking to abuse and inject into SelectResults. :) Don't let the
following example color this discussion too much, as I think the general idea of
providing a common interface is useful even without going this particular route:

class PhoneWithTotalMinutes(SQLObject):
    'for the moment imagine this class is never directly queried/instantiated.'
    number = StringCol()
    minutes = IntCol()

x = Select([Phone.q.id, Phone.q.number, AS(func.SUM(PhoneCall.q.minutes),
'minutes')], where=Phone.q.id==PhoneCall.q.phoneID, groupBy=Phone.q.id)

PhoneWithTotalMinutes.select(query=x)

Using the fact that Iteraction.next/_SO_selectInit will accept any result tuple
that matches the columnList. (I'm ignoring the fact that this particular example
could be statically defined as a view on the db side, or even possibly
statically stored as sqlmeta.table, as currently I'm interested in representing
it on the python side in the pending state of being a malleable SelectResults.)

> > I'm thinking that sqlbuilder.Table (or at least AliasTable) should also
> > eventually get in on this fun, probably growing a .select method that
> > returns a Select that can then be filter'ed etc.
(Continue reading)

Luke Opperman | 13 Feb 22:05 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Ok, two things today:

1. Preview implementation of just the "use sqlbuilder.Select for queryForSelect"
part is in trac #291, but trac is now down. I've attached the patch (against svn
r2158) to this message too.

2. Found a fun problem with sqlbuilder.SQLJoin. Since Alias's don't have a
__sqlrepr__, SQLJoin.__init__ manually tries to construct the string
representation as AliasField.tablesUsedImmediate does. This causes problems with
the _fixTablesForJoin / Select with join logic where it's looking at "if
j.table1 in tables", because SQLJoin always puts "x AS y" and the AliasField
implementation (that ends up populating tables) is always "x y" (per
AliasField.as_string).

Any opposition to adding a __sqlrepr__/tablesUsedImmediate to Alias/AliasTable,
and having SQLJoin just sqlrepr it's components as otherwise expected?

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
Oleg Broytmann | 13 Feb 22:15 2007
X-Face
Picon

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

On Tue, Feb 13, 2007 at 09:05:04PM +0000, Luke Opperman wrote:
> but trac is now down

   I'll try to contact its admin...

> Any opposition to adding a __sqlrepr__/tablesUsedImmediate to Alias/AliasTable,
> and having SQLJoin just sqlrepr it's components as otherwise expected?

   IWB interesting to see the code.

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
Luke Opperman | 14 Feb 23:11 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Oleg Broytmann <phd <at> phd.pp.ru> writes:

>    IWB interesting to see the code.

Ok, put a slightly updated version on SF Patch #1653898

The description there is pretty complete, the relevant changes for the
Alias/Join part are below, but note the big caveat from the SF description:
the db argument to sqlrepr is currently (old & new code) lost when wrapping
Selects in Joins or Aliases.

I've only tested this on postgresql for the time being.

- Luke

 class AliasField(Field):
-    as_string = '' # set it to "AS" if your database requires it
-
-    def __init__(self, tableName, fieldName, alias):
+    def __init__(self, tableName, fieldName, alias, aliasTable):
         Field.__init__(self, tableName, fieldName)
         self.alias = alias
+        self.aliasTable = aliasTable

     def __sqlrepr__(self, db):
         return self.alias + "." + self.fieldName
-
+
     def tablesUsedImmediate(self):
-        return ["%s %s %s" % (self.tableName, self.as_string, self.alias)]
(Continue reading)

Luke Opperman | 14 Feb 23:24 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

I meant SF #1660094, clearly. :)

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
Oleg Broytmann | 16 Feb 17:30 2007
X-Face
Picon

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Hello!

On Wed, Feb 14, 2007 at 10:11:34PM +0000, Luke Opperman wrote:
> Ok, put a slightly updated version on SF Patch #1653898

   Wow! Though it is rather large it is a clever patch from a developer
with good understanding of deep SQLObject internals. Thank you for the job!
   I have applied it, and the entire test suite passed, Python 2.3 and 2.4,
Postgres and SQLite.
   A thing or two have drew my attention. First, I am a bit nervous about
global default_sqlrepr_db (sometimes I connect to two databases -
SQLite+Postgres, SQLite+MySQL); on the other hand even this solution is
better than simply passing None. Also test_aliases.py - test_1syntax could
be removed if there is no a way to fix it.

Oleg.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
Luke Opperman | 16 Feb 18:55 2007

Re: SelectResults / DBConnection.queryForSelect using sqlbuilder.Select

Oleg Broytmann <phd <at> phd.pp.ru> writes:

> 
> Hello!
> 
> On Wed, Feb 14, 2007 at 10:11:34PM +0000, Luke Opperman wrote:
> > Ok, put a slightly updated version on SF Patch #1653898
> 
>    Wow! Though it is rather large it is a clever patch from a developer
> with good understanding of deep SQLObject internals. Thank you for the job!
>    I have applied it, and the entire test suite passed, Python 2.3 and 2.4,
> Postgres and SQLite.
>    A thing or two have drew my attention. First, I am a bit nervous about
> global default_sqlrepr_db (sometimes I connect to two databases -
> SQLite+Postgres, SQLite+MySQL); on the other hand even this solution is
> better than simply passing None. Also test_aliases.py - test_1syntax could
> be removed if there is no a way to fix it.
> 
> Oleg.

Thanks, I do think the default_sqlrepr_db thing should be fixed in another way,
such as passing db to tablesUsedDict/tablesUsedImmediate. (I think that covers
all the existing cases of str-ification, which would be changed to sqlrepr's.)
This doesn't stop someone from converting to string rather than sqlrepr'ing in
their own code, but it wouldn't mysteriously happen out of reach in sqlbuilder's
code at least. :) Connecting to multiple databases seems to be a pretty common
use of SO, and more generally from a documentation / debugging perspective
having to set a module-level flag is not particularly obvious nor can it be
called out in the exception (not raised till the DB layer, ProgrammingError or
similar).
(Continue reading)


Gmane