[SQL-CVS] [ sqlobject-Bugs-2865410 ] createTable Fails When Table is Named "Transaction"

[SourceForge.net <no...@so...>]

Bugs item #2865410, was opened at 2009-09-24 04:45
Message generated for change (Comment added) made by phd
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=2865410&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: SQLObject release (specify)
Status: Closed
Resolution: Invalid
Priority: 5
Private: No
Submitted By: Brendan Doms (bdoms)
Assigned to: Oleg Broytman (phd)
Summary: createTable Fails When Table is Named "Transaction"

Initial Comment:
Bare bones, just try this (I'm working off of the easy_install version on Ubuntu, which is currently 0.11.0):


from sqlobject import SQLObject, connectionForURI, sqlhub

db_file = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'finance.db')

connection = connectionForURI('sqlite:' + db_file)

sqlhub.processConnection = connection

class Transaction(SQLObject):
    pass

Transaction.createTable()

If the table is named anything else (as far as I can find), then it works fine. But as it is, this generates the following error:

  File "/usr/local/lib/python2.6/dist-packages/SQLObject-0.11.0-py2.6.egg/sqlobject/sqlite/sqliteconnection.py", line 183, in _executeRetry
    raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: near "transaction": syntax error

Not very helpful right? Either this error should be changed to point out the fact that "Transaction" is invalid as a table name, or the preferred solution would be to eliminate the error altogether and allow this table name. Considering that the import list contains no * and nothing called "Transaction" I see no reason why SQLObject's name space should interfere here.

----------------------------------------------------------------------

>Comment By: Oleg Broytman (phd)
Date: 2010-04-26 01:24

Message:
jakmak, you can do this in SQLite, but you will have problems later dealing
with in in SQLObject.

----------------------------------------------------------------------

Comment By: jakimak (jakmak)
Date: 2010-04-26 01:05

Message:
just came accross this. 

Using sqlite3, you can simply put the keyword into quotes, then it works
fine. 
This could also apply for SQLObject, but didn't test it.

e.g. 
CREATE TABLE "transaction" (id INTEGER PRIMARY KEY);
or 
CREATE TABLE "transaction" ("transaction" INTEGER PRIMARY KEY);
and 
DROP TABLE "transaction";



----------------------------------------------------------------------

Comment By: Brendan Doms (bdoms)
Date: 2009-09-26 01:25

Message:
Ah, nice job getting to the root of the issue.

I guess I just assumed that because SQLObject is an abstraction layer it
would take the pain away from dealing with databases and so none of
SQLite's (or any other database) reserved keywords would be in conflict. My
bad.

Since that's not the case, I would still recommend making the error
message more clear here if possible. Specifically mentioning the reserved
keyword thing would have helped me track down the bug faster.

----------------------------------------------------------------------

Comment By: Oleg Broytman (phd)
Date: 2009-09-25 19:04

Message:
If you add '?debug=1' to DB URI you find the following SQLObject debugging
output:

 1/QueryR  :  CREATE TABLE transaction (
    id INTEGER PRIMARY KEY
)

This is where the error came from - from SQLite, not from SQLObject!
SQLObject's namespace has nothing to do here.

Traceback (most recent call last):
[skip]
    raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: near "transaction": syntax error

Let's test it without SQLObject:

$  sqlite3 test
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE transaction (id INTEGER PRIMARY KEY);
SQL error: near "transaction": syntax error

SQLite (like any other DB) doesn't allow keywords to be used as names.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=2865410&group_id=74338