Thread: [SQLObject] Automatic joins and thoughts on object queries
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Shahms K. <sh...@sh...> - 2005-10-13 16:20:15
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Having heard a lot of noise around Microsoft's LINQ stuff for C# 3.0, I decided to take a look at it and the related spec for DLinq. I've been thinking a lot about mapping SQL queries to an OO model and thought it would be a good idea to look at a few other strategies for ideas. Like most things from Microsoft, it's over-engineered, inelegant and "heavy", but does have a few good ideas. More importantly, it got me thinking in new and different ways about queries ;-) Basically, DLinq consists of an ORM and the "LINQ" layer. The ORM isn't anything new or exciting (SQLObject feels significantly "cleaner" in a number of ways, at least to me), but separating the query and "storage" layers got me thinking that it might be possible to do something similar using SQLObject. If done properly, the same (or similar) queries could be used for extracting objects from SQL with SQLObject and any iterable with itertools. In order to do this the API itself would need to be distinct from SQLObject, but considering the audience, I'll describe my ideas in terms of the current SQLObject API for clarity. Currently, SQLObject.select() can be called in a number of ways, including taking arbitrary SQL for a where clause or using the '.q' object to build an expression. This flexibility is wonderful, but the '.q' object does present some difficulty with joins as one must manually specify the 'Child.q.parentID==Parent.q.id' parameter. It also means that using raw SQL is necessary for queries involving many-to-many joins, as the "table" is never exposed. It should be possible to simply say "Child.q.parent.name == 'somestring'" and have the '.q' object determine the join. Conversely, one should be able to say "Parent.q.children.name == 'somestring'" and have the reverse join work as well. Once this is done the many-to-many joins can also be used in query expressions. In order to maintain backwards compatibility with the current .q and provide enough flexibility to allow the same or similar methods to be applied to generic (or suitably decorated) iterators, I propose that the clause parameter of select() be extended to allow passing a callable taking a single 'query source' parameter and returning an expression. For example: Child.select(lambda c: c.parent.name == "somestring") Parent.select(lambda p: p.children.name == "somestring") There are a number of reasons for using this structure. 1. It allows for restricting the name space within the clause to reasonable attributes and parameters. 2. It allows for adding or overriding functions in the clause's name space such as: Child.select(lambda c: AND(c.parent.name == "somestring", c.id < 4)) 3. In combination with 2, it means the same or similar queries can be used on arbitrary iterables: query(iterable).where(lambda i: AND(i.value < 10, i.name == "foo")) Using the query() function above, the earlier selects could, in fact, look identical: query(Child).where(lambda i: AND(i.value < 10, i.name == "foo")) Except, in the SQLObject case, 'AND' would create the proper SQL query, whereas for the iterable, it would just return the boolean 'and' of the two arguments. In order for this to work, the clause functions would have to adhere to a certain subset of the language, largely identical to the current restrictions placed on lambda. I need to familiarize myself with the SQLObject code base in order to figure out how to automagically determine join parameters, but the majority of the functionality can likely be implemented replacing the functions global name space and calling it with the '.q' attribute. The beauty of the lambda-passing scheme is that things like grouping and ordering can be implemented in a similar fashion. I'm mostly posting this for comments. Please, let me know if I'm on crack ;-P I have a more extensive design in mind, but wanted to restrict this email to "SQLObject" related matters. - -- Shahms E. King <sh...@sh...> Multnomah ESD Public Key: http://shahms.mesd.k12.or.us/~sking/shahms.asc Fingerprint: 1612 054B CE92 8770 F1EA AB1B FEAB 3636 45B2 D75B -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFDTol8/qs2NkWy11sRAiWcAJ9O86ahwTQXt4I2kV4lY85PAH/jHQCfZ6pr yrHFvfok0CfQx6GLT1RxzVc= =zaHb -----END PGP SIGNATURE----- |
From: alejo a. <a_l...@ya...> - 2005-10-13 16:37:33
|
Hi, I have been working with SQLObject-0.61 and i need make some queries with sql operator distinct, when i look the documentation they put MyClass.select(...,distinct=True) but when i put that code i have the following error: TypeError: select() got an unexpected keyword argument 'distinct'. I think that the documentation is for SQLObject-0.7, and i need to work with SQLObject-0.61 thanks advice __________________________________ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ |
From: Oleg B. <ph...@ma...> - 2005-10-13 16:46:12
|
On Thu, Oct 13, 2005 at 09:37:15AM -0700, alejo alejo wrote: > I think that the documentation is for SQLObject-0.7, > and i need to work with SQLObject-0.61 Then you have to backport it yourself, I am afraid. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Ian B. <ia...@co...> - 2005-10-22 22:28:28
|
Shahms King wrote: > Currently, SQLObject.select() can be called in a number of ways, > including taking arbitrary SQL for a where clause or using the '.q' > object to build an expression. This flexibility is wonderful, but the > '.q' object does present some difficulty with joins as one must manually > specify the 'Child.q.parentID==Parent.q.id' parameter. It also means > that using raw SQL is necessary for queries involving many-to-many > joins, as the "table" is never exposed. It should be possible to simply > say "Child.q.parent.name == 'somestring'" and have the '.q' object > determine the join. Conversely, one should be able to say > "Parent.q.children.name == 'somestring'" and have the reverse join work > as well. Once this is done the many-to-many joins can also be used in > query expressions. SQLRelatedJoin addresses some of this, since you can do: aParent.children.filter(...) And SQLMultipleJoin offers some similar advantages. Potentially ForeignKey could also be extended in this way, so that 'Child.q.parent.name == "somestring"' could become (child.parent_id = parent.id AND parent.name = 'somestring') using a little cleverness (so that Child.q.parent would actually be a wrapper that would look like Parent.q, except add the join to the query that results). > In order to maintain backwards compatibility with the current .q and > provide enough flexibility to allow the same or similar methods to be > applied to generic (or suitably decorated) iterators, I propose that the > clause parameter of select() be extended to allow passing a callable > taking a single 'query source' parameter and returning an expression. > For example: > > Child.select(lambda c: c.parent.name == "somestring") > Parent.select(lambda p: p.children.name == "somestring") I think this is difficult, since it requires decompiling the expression. That's not impossible -- RuleDispatch (part of PEAK) does this (except using strings instead of lambdas), and with much the same purpose. But though possible, it doesn't seem very easy. And the time and scope of evaluation can be a little more confusing -- is the expression (partially) evaluated at the time .select() is called? Or when it is used? Closures handle some of this, but it can still lead to confusion. Though I suppose it is similar to LINQ in this way. I think most of what you want is possible without resorting to lambdas. I wouldn't really want to take on the task (at least myself) of doing that kind of decompiling, but if that functionality was available in a stable library it might be easier to approach. Even better if these kind of expressions were part Python itself. (If you were decompiling expressions, you also wouldn't need to use 'AND', you could use plain 'and'). -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
From: Shahms E. K. <sh...@sh...> - 2005-10-23 20:49:46
|
Ian Bicking wrote: > Shahms King wrote: >> >>Child.select(lambda c: c.parent.name == "somestring") >>Parent.select(lambda p: p.children.name == "somestring") > > > I think this is difficult, since it requires decompiling the expression. > That's not impossible -- RuleDispatch (part of PEAK) does this (except > using strings instead of lambdas), and with much the same purpose. But > though possible, it doesn't seem very easy. And the time and scope of > evaluation can be a little more confusing -- is the expression > (partially) evaluated at the time .select() is called? Or when it is > used? Closures handle some of this, but it can still lead to confusion. > Though I suppose it is similar to LINQ in this way. In the SQLObject case, the expression is evaluated (almost) immediately to yield the appropriate SQLExpression. However, if one were querying over generic iterables, it's concievable that the lambda would be used as the predicate for ifilter. It's basically a way of limiting and changing the expression's namespace such that it has access to one object that represents a single result returned from the underlying iterable. Whether it's a real result object or a SQLExpression should be transparent. > I think most of what you want is possible without resorting to lambdas. > I wouldn't really want to take on the task (at least myself) of doing > that kind of decompiling, but if that functionality was available in a > stable library it might be easier to approach. Even better if these > kind of expressions were part Python itself. (If you were decompiling > expressions, you also wouldn't need to use 'AND', you could use plain > 'and'). > See, I don't think it actually requires decompiling the expression (unless you want to use 'and' or make sure that the callable only contains a single expression), simply passing a "representative" object to the lambda function (basically, something like SQLObject.q) after overriding the function's globals: def select(cls, expr): if callable(expr): sqlglobals = {'AND': sqlbuilder.AND, ... } exprfun = new.function(expr.func_code, sqlglobals) expr = exprfun(cls.q) I admit that lambdas are a little bit awkward, but I couldn't come up with another way of preserving compatibility with the current select and paving the way for a generic "query" interface for SQLObject and generic iterators. --Shahms |