Thread: [SQLObject] sqlbulder, sresults, views - update
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Luke O. <lu...@me...> - 2007-03-28 15:10:48
|
After some time away, a chance to cleanup my changes to SelectResults and SQLBuilder and submit them to all of you. Those changes fall into the following areas: 1. SelectResults use SQLBuilder Select queries. 2. SQLBuilder Select supports rest of SelectResults options (reversed, distinct, etc) 3. SQLBuilder tablesUsedDict handles sqlrepr'able objects. 4. SQLBuilder Joins handle SQLExpression tables (not just str/SQLObject/Alias) and properly sqlrepr. 5. Addition of SQLBuilder ImportProxy. 6. Addition of SelectResults.throughTo. 7. Addition of ViewSQLObject. All of these except #7 is at: http://svn.colorstudy.com/SQLObject/branches/sqlbuilder-sresults/ And the whole deal is at: http://svn.colorstudy.com/SQLObject/branches/sqlbuilder-views/ The reason I'm separating out ViewSQLObject from the rest is simply that I haven't become fully comfortable with the SQL-generation logic. It's tested, it works, but from a scalability perspective I haven't had the time to address some fundamental issues with pruning unneeded derivations (mostly becomes an issue for views with many complex aggregates). Also, caching of intermediate sqlrepr'ed strings will need to be addressed. (In testing I used this branch with views-on-top-of-views-on-top-of-views and complex aggregates over views, which leads to 2000-line SQL statements and >20sec just to process sqlrepr - caching the sqlrepr results of each sub-expression reduces that to <0.5sec sqlrepr time but I'm not happy with that code at all, and while the resulting sql produces correct results it's often excessive for its generality). I'll skip #s 1,2,4 as self-explanatory or internal. #3 is an API change, although probably not for anything the vast majority of people have had need to extend: SQLExpression.tablesUsedDict (and sqlbuilder.tablesUsedDict utility function) take a db argument like sqlrepr does, ensuring that SQLExpressions can be used as tables and that they are not prematurely string-ified. #3 is also intended to fix the "TypeError:unhashable instance using sqlbuilder.Table" bug (SF#1642143) that was recently added - the accepted patch has the issue that it is not sqlrepr-safe (there is a series of tests added with my changes for trying to check that SQLExpressions that require sqlrepr with a specific database aren't str'ed too early when wrapped in a table or alias or join etc). #5 is an addition that allows you to ignore the circular import issues with referring to SQLObject classes in other files - it uses the classregistry as the string class names for FK/Joins do, but specifically intended for SQLBuilder expressions. This is required for defining View columns since they're at import time, probably just a convenience otherwise, but one that I've used for a while to simplify "I need to import objects X,Y,Z in this _get_ method rather than at the top of my file to build a filter expression". See tests/test_sqlbuilder_importproxy.py #6 SelectResults.throughTo allows you to traverse your relationships (FK/Join) via SQL, avoiding the intermediate objects. Additionally, it's a simple mechanism for pre-caching/eager-loading of later FK relationships (ie, going to loop over a select of somePeople and ask for aPerson.group, first call list(somePeople.throughTo.group) to preload those related groups and use 2 db queries instead of N+1). I'm open to alternate names to "throughTo", none have really seemed obvious or descriptive. See tests/test_select_through.py - Luke |
From: Oleg B. <ph...@ph...> - 2007-03-28 15:59:05
|
Hello! On Wed, Mar 28, 2007 at 03:09:39PM +0000, Luke Opperman wrote: > 1. SelectResults use SQLBuilder Select queries. > 2. SQLBuilder Select supports rest of SelectResults options (reversed, distinct, > etc) > 3. SQLBuilder tablesUsedDict handles sqlrepr'able objects. > 4. SQLBuilder Joins handle SQLExpression tables (not just str/SQLObject/Alias) > and properly sqlrepr. > 5. Addition of SQLBuilder ImportProxy. > 6. Addition of SelectResults.throughTo. > 7. Addition of ViewSQLObject. Amazing job! Thank you! > http://svn.colorstudy.com/SQLObject/branches/sqlbuilder-sresults/ > http://svn.colorstudy.com/SQLObject/branches/sqlbuilder-views/ I had been following your commits with one eye. Now I am going to look at the entire results. > #3 is also intended to fix the "TypeError:unhashable instance using > sqlbuilder.Table" bug (SF#1642143) that was recently added Just yesterday I applied the patch http://sourceforge.net/tracker/index.php?func=detail&aid=1673013&group_id=74338&atid=540674 It has to be reverted for your changes to be merged in. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Luke O. <lu...@me...> - 2007-03-28 16:10:51
|
Oleg Broytmann <phd <at> phd.pp.ru> writes: > > #3 is also intended to fix the "TypeError:unhashable instance using > > sqlbuilder.Table" bug (SF#1642143) that was recently added > > Just yesterday I applied the patch > http://sourceforge.net/tracker/index.php?func=detail&aid=1673013&group_id=74338&atid=540674 > It has to be reverted for your changes to be merged in. Right, I was actually responding specifically to that patch, the sqlbuilder-sresults branch was created from trunk after that change was applied so already contains the revert/merge-resolution with that patch, so no effort for you there. - Luke |