Re: sqlstring -- a library to build a SELECT statement
- From: "grunar@xxxxxxxxx" <grunar@xxxxxxxxx>
- Date: 20 Oct 2005 08:47:13 -0700
> The big operator question will be: how will "and" and "or" be
> implemented? This is always a sticking point because of Python's
> short-circuiting behaviors regarding them (the resultant bytecode will
> include a JUMP).
I'm using the Boolean | and & operators for logical groups, eg (a | b |
(b & c)). This might seem ugly to pureists, but solves all of the
short-circuit issues. It does require the user to use excessive
parentheses, becuase | evaluates before ==. Another option is to use
functions-- AND(EQ(a, 1), OR(IN(B,(1,2,3)))) -- But I find this hard to
read. But mixing the two is sometimes clean: EQ(a,1) & LT(b,2). But
having too many ways of doing things doesn't seem very pythonic.
>
> An alternative is to stuff the representation into a string, which can
> then be parsed however one likes.
>
> For Dejavu (http://projects.amor.org/dejavu), I didn't do either
> one--instead I used lambdas to express the where clause, so that:
>
> f = logic.Expression(lambda x: ('Rick' in x.Name) or
> (x.Birthdate == datetime.date(1970, 1, 1)))
> units = sandbox.recall(Person, f)
>
> might produce, in the bowels of the ORM:
>
> "SELECT * FROM [Person] WHERE [Person].[Name] Like '%Rick%' or
> [Person].[Birthdate] = #1/1/1970#"
>
> Note that the tablename is provided in a separate step. The translation
> is based on the codewalk.py and logic.py modules, which are in the
> public domain if you want to use any part of them. See
> http://projects.amor.org/dejavu/svn/trunk/
This is a very elegant solution, so much so that I almost didn't go
down the path of sqlstring. Having support for lambda expressions is
still an option, though I wanted to try object operator
overloading/methods first--too see if I could avoid the Bytecode issue.
>
> > 2. How to best add further sql function support? Adding magic
> > callable objects to columns came to mind, but this has it's own set
> > of issues. I'm leaning towards a magic object in the sqlstring
> > module. For example:
> >
> > sqlstring.F.substring(0, 4, person.first_name)
> >
> > would result in: substring(0, 4, person.first_name). the F object
> > could be put in the local scope for short-hand.
>
> This is a hard problem, since your sqlstring module doesn't control the
> result sets, and so can't provide fallback mechanisms if a given
> database does not support a given function (or operator, or minute
> detail of how a function or operator works; for example, LIKE is
> case-insensitive in MS SQL Server but case-sensitive in PostgreSQL). If
> you're going to use subclasses to handle "database-specific overwrites"
> (below), then you'll probably want to stick such functions in that base
> class (and override them in subclasses), as well.
Good point. These things should be able to be "intercepted" in the
database specific modules, so the library has a documented way
functions should be used (ANSI if applicable), but database specific
overwrites allow us to deal with issues or hacks (to emulate a
function) in databases.
> See the Adapter and SQLDecompiler classes in
> http://projects.amor.org/dejavu/svn/trunk/storage/db.py (and the
> store*.py modules) for some examples of using subclassing to produce
> database-specific syntax. There, it's one Adapter class per supported
> DB-type; you might consider keeping the Expression objects themselves
> free from SQL, and transform the Expressions to SQL in a separate
> class, which you could then subclass.
Thanks. Your approach here had already inspired me, I'll take a look
at it again. Pulling the SQL out of the Expression objects is double
sided, but might be a way to cleanly support db syntax nuances. I'll
keep you posted.
Runar
.
- References:
- sqlstring -- a library to build a SELECT statement
- From: grunar@xxxxxxxxx
- Re: sqlstring -- a library to build a SELECT statement
- From: fumanchu
- sqlstring -- a library to build a SELECT statement
- Prev by Date: Re: __getattr__, __setattr__
- Next by Date: Re: Popularity of blogging tools used by python programmers
- Previous by thread: Re: sqlstring -- a library to build a SELECT statement
- Next by thread: Re: sqlstring -- a library to build a SELECT statement
- Index(es):
Relevant Pages
|
