Ok, want to let you all in on something magical that made my weekend. This is
an extension to my earlier thread on using SQLBuilder more consistently in
SQLObject.
On Saturday I expanded and refactored slightly on my last efforts, including
cleaning up the reaching into DBConnection._SO_selectOne/_SO_selectOneAlt.
Combined with the earlier work on SelectResults (and hence SQL*Joins), this
gives us built-in SQLBuilder support for all read operations. This is attached
to the previous SF patch #1660094 as "sresults-selectOne-20060217.diff".
On Sunday I started work on the next piece, turns out the magic is not so
magic once we've come this far, and many things end up "just working".
The goal: SQLObject-defined views, in particular derived columns, in
as-close-to sqlobject style and able to be freely composed. Let me just quote
from test_views.py:
class PhoneNumber(SQLObject):
number = StringCol()
calls = SQLMultipleJoin('PhoneCall')
incoming = SQLMultipleJoin('PhoneCall', joinColumn='toID')
class PhoneCall(SQLObject):
phoneNumber = ForeignKey('PhoneNumber')
to = ForeignKey('PhoneNumber')
minutes = IntCol()
class ViewPhone(ViewSQLObject):
class sqlmeta:
idName = PhoneNumber.q.id
clause = PhoneCall.q.phoneNumberID==PhoneNumber.q.id
minutes = IntCol(dbName=func.SUM(PhoneCall.q.minutes))
numberOfCalls = IntCol(dbName=func.COUNT(PhoneCall.q.phoneNumberID))
number = StringCol(dbName=PhoneNumber.q.number)
phoneNumber = ForeignKey('PhoneNumber', dbName=PhoneNumber.q.id)
calls = SQLMultipleJoin('PhoneCall', joinColumn='phoneNumberID')
vCalls = SQLMultipleJoin('ViewPhoneCall', joinColumn='phoneNumberID')
class ViewPhoneMore(ViewSQLObject):
''' View on top of view '''
class sqlmeta:
idName = ViewPhone.q.id
clause = ViewPhone.q.id == PhoneCall.q.toID
number = StringCol(dbName=ViewPhone.q.number)
timesCalled = IntCol(dbName=func.COUNT(PhoneCall.q.toID))
minutesCalled = IntCol(dbName=func.SUM(PhoneCall.q.minutes))
This is attached to the SF patch as "sresults-views-20070219-2.diff" (a
cumulative patch including all of the sqlbuilder, selectOne, etc stuff, plus
views and several new tests).
The one other addition here is sqlbuilder.ImportProxy, because of course I'd
like to deal nicely with the circular import issues when classes are in
different files, and because requiring SQLBuilder expressions in column
definitions greatly frustrates that issue. This uses the classregistry to
delay .q access until the subsequent classes are imported. So if I were
defining one of the above views in a separate file, I could instead do:
from sqlbuilder import ImportProxy #etc
VP = ImportProxy('ViewPhone')
PC = ImportProxy('PhoneCall')
class ViewPhoneMore(ViewSQLObject):
class sqlmeta:
idName = VP.q.id
clause = VP.q.id == PC.q.toID
number = StringCol(dbName=VP.q.number)
timesCalled = IntCol(dbName=func.COUNT(PC.q.toID))
minutesCalled = IntCol(dbName=func.SUM(PC.q.minutes))
I've actually had this lying on my shelf for several months, it is entirely
independent from the rest of this work, so don't think it only applies to these
new View classes, use it wherever you're constructing SQLBuilder expressions in
another class (I run into it all the time when I'm defining a _get_ override to
a join that wants to be filtered by some other SQLObject fields).
Ok, so I'll be putting together some documentation on this shortly, and
further testing/coding for any edge cases I encounter. I will probably also
expand the SQLBuilder-ization to join retrieval. Beyond that, can't yet
say where this leads. Please, any commentary is greatly desired, from naming
changes to ideas for further expansion to frightening code screams to ??
- Luke
P.S. Yes, I've only tested this on PostgreSQL, which already supports views. I
have my reasons. :) But I'm doubly curious whether this works on any of the
DBs that don't support views. I however will not have any time to devote to
such things for a while as our world revolves around postgres.
-------------------------------------------------------------------------
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
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
