Re: [SQLObject] Re: DB API

[Ian B. <ia...@co...>]

Oleg Broytmann wrote:
> On Mon, Nov 22, 2004 at 03:14:41PM +0300, Oleg Broytmann wrote:
> 
>>   Why use sqlStringReplace and _[lL]ikeQuoted?...
>>   ...use format/pyformat/etc paramstyle from DB API 2.
> 
>    But this, probably, requires some architectural changes in the
> SQLObject.
>    Well, what do you think of this?

I generally thing this is the right thing to do.  There are a couple 
reasons it wasn't that way originally:

* I wanted to be able to override SQL representation, with what is now 
__sqlrepr__.
* Constructing SQL from several sources is difficult when you have to 
worry about the order of parameters.
* There's no standard paramstyle.

Going backwards through the list...

The standard paramstyle probably isn't that big a deal.  %s is an easy 
implementation (just do query % ('?', '?', ...) or whatever); pyformat 
doesn't imply SQL parsing in the way other formats (like ?) do; you have 
to escape any %'s.  The other way would be the split strings.  I think 
named and numeric both imply SQL parsing, and as such are too difficult. 
  When this last came up on DB-SIG, I gave several examples where 
parsing would be necessary:
   http://mail.python.org/pipermail/db-sig/2004-August/004165.html

And it is even worse than that, because aspects of the parsing are 
database specific (like string quoting), and we're back to where we 
started, but with more code.


The first two problems could be solved with a nice abstraction.  I'd 
like something like __sqlrepr__, but it could return the abstraction 
instead of a text SQL literal.  Maybe this query abstraction would 
contain the list of parameters, and a list of strings (where parameters 
go inbetween the strings).  There'd be a method to convert the query 
object to a SQL string with the appropriate paramstyle, and a list of 
parameters.  The query objects would also support concatenation. 
__sqlrepr__ would take a bit of thought, but if the method was present 
it should allow a parameter to insert new text and new parameters. 
E.g., a Point object might look like:

class Point:
   def __init__(self, x, y):
       self.x, self.y = x, y
   def __sqlrepr__(self):
       return ('(', ', ', ')'), (self.x, self.y)
       # or...?
       return '(%s, %s)', (self.x, self.y)

The first is hard to read, the second requires a standard simplistic 
paramstyle (like pyformat).  Potentially both could be supported.

All of this could be SQLObject-neutral.  I don't think it would be quite 
as intuitive as string substitution, but hopefully it could get close. 
Since people are frequently proposing these sorts of libraries (often 
under the guise of DB API 3), maybe it's all much harder than I realize. 
    But maybe

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org