[DB-SIG] PROPOSAL: Portable Argment Format

[Jim Fulton]

I'd like to lobby for a portable argument format for the DBI
interface.  While this *does* require parsing SQL, this is
not really all that hard and I think the benefits are well
worth the effort. I volunteer to provide a utility to assist 
with this.

Here's what I think the format needs to do:

  - Not interfere with SQL.  That is, it must be unabiguous
    to find parameters in SQL.  

  - Support optional argument names, which may be given
    a positional interpretation.

  - Capture type information, to make type explicit.

Maybe this is all that's needed.

I propose the following format:

     :(name)code

where : signals a parameter and code is a type code.

Valid type codes are:

    c, b, B, h, H, i, I, l, L, f, d, and s -- 
       as defined in the struct module

    t -- Date/Time
    $ -- Money (???)
    r -- Binary data (raw/blob) gotten from a string
    others...???

Note that, after some consideration, I decided to use ':'
rather than '%' to signal a parameter, because:

  - % has a meaning in string substitution that is
    too similar and not similar enough to sql parameters.
    I don't want to worry about conflicting or confused
    type codes.

  - One might want to use string formating to generate
    sql containing parameter references (without wanting to 
    escape %s).

  - I was too lazy to try to determin if % could be
    confused with some SQL syntax element and figured
    that : was OK because it was used by Oracle. :-)

I'm not wed to ':'. '?' would be OK too.

The name is optional and defaults to a generated name, 
_x, where x is the index of the argument in the
unnamed arguments.  For example:

     select * from spam 
     where foo=:s and bar=:d

is equivalent to:

     select * from spam 
     where foo=:(_0)s and bar=:(_1)d

Note that positional arguments could be given explicitly
and intermixed with non-positional arguments, as in:

     select * from spam 
     where foo=:(_1)s and bar=:(bar)d and baz=:i

is equivalent to:

     select * from spam 
     where foo=:(_1)s and bar=:(bar)d and baz=:(_0)i

Note that arguments may be repeated as in:

     select * from spam
     where x1 > :(minx)i and x2 > :(minx)i and
           w == :s and a == :(_1)i and b < :(_1)i

When used with ODBC, this would be converted to:

     select * from spam
     where x1 > ? and x2 > ? and
           w == ? and a == ? and b < ?

and two of the arguments would have to bound twice.

I propose that non-positional arguments be assigned positions
according to their order of appearence, with positional arguments
ordered before non-positional arguments.  So in the example above, 
the arguments and their positions would be:

    _0   at position 0,
    _1   at position 1, and
    minx at position 2.

I propose that arguments be treated in a similar fashion to 
Python function arguments, allowing either positional or 
non-positional actual arguments.  For example, the signature 
of the above example would be "_0, _1, minx".

Someone could pass the parameters like this:

  sql=("select * from spam "
       "where x1 > :(minx)i and x2 > :(minx)i and "
       "    w == :s and a == :(_1)i and b < :(_1)i")

  c.execute(sql, 'eggs', 10, 20)
  x.execute(sql, 'eggs', 10, minx=20)
  x.execute(sql, _0=eggs, minx=20, _1=10)

I think that this proposal hase a number of advantages over 
the current scheme:

  - It provides greater portability.

  - It provides some measure of type safety.

  - It supports more user-friendly calling mechanism 
    (e.g. keyword arguments)

  - It is more powerful than some database-specific 
    mechanisms.  For example, ODBC's mechanism forces
    parameters that are *used* more than once to be bound 
    more than once.

The only real downside is that module developers may have 
a bit more work to do.  I'll volunteer to reduce the work
required by providing a utility that parses an sql statement
and returns a new SQL statement that uses database-specific
format and that provides information needed to bind parameters.

Jim

--
Jim Fulton           mailto:jim@digicool.com
Technical Director   (888) 344-4332              Python Powered!
Digital Creations    http://www.digicool.com     http://www.python.org

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.