Re: Test suite update for format conversion accuracy.

On Mon, May 20, 2013 at 8:12 AM, Daniele Varrazzo <
[email protected]> wrote:

> On Mon, May 20, 2013 at 12:51 PM, Vernon D. Cole <[email protected]>
> wrote:
> > Stuart:
> >
> > I have just uploaded a branch for your consideration for
> > https://code.launchpad.net/dbapi-compliance.  This version adds a second
> > field to the second test table (barflies) and loads that field with the
> > literal 'thi%s :may cau%(e)s? troub:1e'.  The value of this literal is a
> > trap for unwary format conversion routines which fail to notice that they
> > are re-formatting a literal value.
>
> You are requiring the sql string to be parsed, literal string
> identified and placeholders inside them not treated. This is not a
> DBAPI2 requirement.
>

Indeed, the DB API2 does not address parameter reformatting at all.  We are
just starting to play with that.  I am merely making sure that a reformat
operation does not step on the contents of an ANSI standard literal. And,
yes, when I am reformatting a string I do have to parse it.  How else would
I find my format markers?


>
> Without this requirement a driver can be implemented just passing the
> string to the client library, an extremely reasonable way to implement
> a client library wrapper. If you add it every driver will need to know
> the parsing rule of the server (and of its version). This is not a
> task as trivial as counting the quotes


As a matter of fact, counting quotes handles this problem quite nicely.
Actually, in my code I don't really count them.  I do a .split("'") [that
is using a single quote] and then only parse the odd-numbered tokens.  Then
do a "'".join().


> or applying a regexp: take a
> look at the Postgres literals grammar:
> <http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html>.
>
v v v v v v v v
 4.1.2.1. String Constants

A string constant in SQL is an arbitrary sequence of characters bounded by
single quotes ('), for example 'This is a string'. To include a
single-quote character within a string constant, write two adjacent single
quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a
double-quote character (").
^ ^ ^ ^ ^ ^

That's what I am using for this test: an arbitrary sequence of characters
bounded by single quotes.


> Of course PG developers are free to complicate that as much as they
> want: a client driver is expected to be agnostic and just forward the
> query.
>

Yes, that is the point: forward the query, not mess it up.


>
> I don't think any currently implemented driver behaves as you wish.
>

I happen to _know_ that at least _one_ does.  ( My code is GPL, feel free
to copy. I copied from yours yesterday morning.)


> Here is stdlib's sqlite3:
>
> In [1]: import sqlite3
>
> In [2]: cnn = sqlite3.connect(':memory:')
>
> In [3]: cur = cnn.cursor()
>
> In [4]: cur.execute("create table test (a text, b text)")
> Out[4]: <sqlite3.Cursor at 0x2a4d500>
>
> In [5]: cur.execute("insert into test values (?, ?)", ('hi', 'mum'))
> Out[5]: <sqlite3.Cursor at 0x2a4d500>
>
> In [6]: cur.execute("insert into test values (?, 'wat?')", ('hi'))
> ---------------------------------------------------------------------------
> ProgrammingError                          Traceback (most recent call last)
> /home/piro/src/qmarkpg/<ipython-input-6-b8a3105dc710> in <module>()
> ----> 1 cur.execute("insert into test values (?, 'wat?')", ('hi'))
>
> ProgrammingError: Incorrect number of bindings supplied. The current
> statement uses 1, and there are 2 supplied.
>
> That is, indeed, a programming error. "h" and "i" are two values. The line
should have another comma:
In [6]: cur.execute("insert into test values (?, 'wat?')", ('hi',))
I usually pass a single parameter in a list simply to avoid that pattern:
In [6]: cur.execute("insert into test values (?, 'wat?')", ['hi'])

(for extra fun, I don't think sqlite3 even contemplates the
> possibility to use ? into a literal and neither ?? nor \? appear to
> work, see <http://www.sqlite.org/lang_expr.html#varparam>)
>
> Here is what the document you linked says:
v v v v
4.1.2.1. String Constants

A string constant in SQL is an arbitrary sequence of characters bounded by
single quotes ('), for example 'This is a string'. To include a
single-quote character within a string constant, write two adjacent single
quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a
double-quote character (").

^ ^ ^ ^ ^

Nothing in there about avoiding question marks.

Now let's try it....

C:\Users\vernon\Projects\qmarkpg\tests>python
Python 2.7.4 (default, Apr  6 2013, 19:55:15) [MSC v.1500 64 bit (AMD64)]
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import adodbapi
>>> cnn = adodbapi.connect(
...
'%(prov_drv)s;Server=%(host)s;Database=%(database)s;uid=%(user)s;pwd=%(password)s;',
... 'adotestuser', '12345678', "25.223.161.222", 'adotest',
...   prov_drv='Provider=MSDASQL;Driver={PostgreSQL Unicode(x64)}')
>>> cnn.dbms_name
u'PostgreSQL'
>>> cnn.dbms_version
u'9.1.9'
>>> cur = cnn.cursor()
>>> cur.execute("create table test (a text, b text)")
>>> cur.paramstyle
'qmark'
>>> cur.execute("insert into test values (?, ?)", ('hi', 'mum'))
>>> cur.execute("insert into test values (?, 'wat?')", ('hi',))
>>> cur.paramstyle = 'format'
>>> cur.execute("insert into test values (%s, '%sure')", ('hello',))
>>> cnn.commit()
>>> cur.execute('select * from test')
>>> for row in cur:
...   print row
...
('hi', 'mum')
('hi', 'wat?')
('hello', '%sure')
>>>

Seems to me that counting quotes works.

--

Vernon

_______________________________________________
DB-SIG maillist  -  [email protected]
http://mail.python.org/mailman/listinfo/db-sig