On Wed, 9 Mar 2005 18:17:53 +0200, Ksenia Marasanova
<ksenia.marasanova@...> wrote:
> Carlos,
>
> thank you for your reaction,
:-)
If you have the time, browse through the SQLObject mail list archives,
and you'll see how many questions resort around modelling *exactly*
this kind of stuff: Persons, Adresses, Roles, Groups, etc. Time for a
cookbook. It's a shame that I'm depending on dial-up now most of the
time, so I really cant stay online long enough to discuss it...
> On Wed, 9 Mar 2005 11:11:21 -0300, Carlos Ribeiro <carribeiro@...> wrote:
> > My first attempt used many-to-many relationships between peoples &
> > groups of people. I found my code confusing and hard to manage. Adding
> > people to groups & groups to people was easy but other operations
> > seemed harder to implement. SQLObject model makes some things easy,
> > but others (for example, complex queries) may be incredibly hard to
> > code (in the sense that it's not trivial or any easier to read than
> > the equivalent SQL query), and are in fact better handled by 'pure'
> > SQL queries embedded in the code.
>
> Well in fact complex queries is the only thing that is really
> bothering me, the rest IMHO works just fine. But when embedding SQL
> queries in the code you don't want to give up all other nice things
> SQLObject has. So I think it would be very handy if one can pass plain
> SQL strings that will be used just "as is" in functions (like it is
> already possible in a simple select):
>
> Person.select(OR('some complex query goes here...', Person.q.age > 21))
>
> Forgive me if this is already possible with some magic from
> sqlbuilder; I couldn't find it :(
It may be possible indeed, but I don't know *how* do to it.
[WARNING!]
<RANT ON>
IMHO, SQLObject lacks now a good wrapper for a query object. MS Access
(I apologize for mentioning this name here :-) does it. Here's a quick
draft of a possible interface (pseudo code!):
class Person(SQLObject):
name = StringCol()
age = IntCol()
address = ForeignKey('Address')
class Address(SQLObject):
street = StringCol()
city = StringCol()
persons = RelatedJoin('Person')
class MyQuery(SQLObjectQuery):
name = QueryCol('[Address.person.name]')
street = QueryCol('[Address.street]')
_where = '[Address.person.name] > %(age)'
_params = {'age': 18}
There are several ideas thrown together here:
- The basic idea here is that the MyQuery object would act pretty much
like a 'Query' does on Access, or even Delphi. The query only exposes
the columns that the programmer deems necessary. The 'from' clause is
not necessary, at least in this case, as it can be inferred
automatically.
- Also, columns are referred to as strings, using their Python names,
instead of the magic objects that SQLObject currently exposes. I
believe this avoids a great deal of problems and adds no complexity to
the design (on the opposite, it's clear & readable).
- The where clause itself is also a plain string. _params is a dict
with the substitutable parameters for the query.
- Instead of providing the joins explicitly in the query, the
programmer can provide the 'fully qualified' [Address.person.name]
column name. SQLObject can traverse this definition (Address ->
Person, via the relationship), and detect the correct from table list
& join type.
The same trick can also be used for a simpler interface for generic
queries over a table:
resultSet = Address.Query('[Address.Person.name] > 18')
or even:
sql = '''
select [Address.Person.name], [Address.street]
from [Address]
where [Address.Person.name] > 18
'''
resultSet = RunQuery(sql)
Well, that's a rant... but maybe we can get something usable out of it.
</RANT OFF>
--
Carlos Ribeiro
Consultoria em Projetos
blog: http://rascunhosrotos.blogspot.com
blog: http://pythonnotes.blogspot.com
mail: carribeiro@...
mail: carribeiro@...
|