Thread: [SQLObject] SQLBuilder & Views
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Luke O. <lu...@me...> - 2007-02-20 02:56:16
|
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. |
From: Oleg B. <ph...@ph...> - 2007-02-20 09:32:38
|
On Tue, Feb 20, 2007 at 02:55:31AM +0000, Luke Opperman wrote: > 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. Thank you, I will look into it. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Johan D. <jd...@as...> - 2007-02-20 13:14:38
Attachments:
viewable.py
view.py
|
Luke Opperman skrev: > 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. I wrote something similar a couple of weeks ago, I didn't submit it to the mailing list because it was not in my opinion clean enough to be submitted upstream. The idea behind it is to avoid SQL views and instead define your own composite objects which uses columns from different tables. SQLBuilder works so you can access ViewableSubclass.q.column which will be translated to the real query defined in the columns dictionary. GROUP BY is done magically behind you if a function is used. I plan to add a white-list of aggregators so you can use non-aggregate functions at some point (where GROUP BY makes less sense) It does not implement any kind of caching yet, so a new object will always be returned. I plan to solve this once it is a real problem to me. I'm attaching the implementation (viewable.py) and an example (view.py) I'm still using an old version of SQLObject, based on a fairly old 0.7 SVN revision, so it might not work with current trunk. Johan |
From: Luke O. <lu...@me...> - 2007-02-20 14:47:49
|
Johan Dahlin <jdahlin <at> async.com.br> writes: > I wrote something similar a couple of weeks ago, I didn't submit it to > the mailing list because it was not in my opinion clean enough to be > submitted upstream. Indeed, I make no specific claims that my code is ready to be released into the wild yet. :) Thanks for sharing your code too. > The idea behind it is to avoid SQL views and instead define your own > composite objects which uses columns from different tables. > SQLBuilder works so you can access ViewableSubclass.q.column which will > be translated to the real query defined in the columns dictionary. > GROUP BY is done magically behind you if a function is used. I plan > to add a white-list of aggregators so you can use non-aggregate > functions at some point (where GROUP BY makes less sense) Impressive, we're on much the same page - my patch also does the auto groupBy / joins for aggregates (with the same "any SQLCall is treated as aggregate" limitation), column query definition is the same (although mine are defined in full Col objects), and we're definitely both trying to solve the same problem. Mine is currently missing dynamic schema changes (addColumn/delColumn etc) and any way to provide a more complex base restriction than a simple clause can handle (ie, your "joins" attr). Also, it appears you're using InheritableSQLObject, I have no idea whether mine works with that. (Based on the tests I don't think this patch breaks Inheritable, I just don't know if Views over Inheritables works. But if you're interested in trying this out, I'd love to know what breaks.) > It does not implement any kind of caching yet, so a new object will > always be returned. I plan to solve this once it is a real problem to me. I'd tried approaches similar to yours in the past, the real impetus making this feasible for me now (with the sqlbuilder changes prior) is precisely that ViewSQLObject is a minor subclass over SQLObject and uses all the same Cols etc - so immediately all of SQLObject's cache/cacheValues, column type conversion, _get_X shortcuts, etc are functional. ForeignKeys, Join relationships, etc as well, just as you're used to in your SQLObject classes, including View->SO, SO->View, and View->View. - Luke |