[storm] Thanks, and questions: SQL reserved keywords; "interface-driven" apps

[Stephen Waterbury]

Christopher Armstrong wrote:
> On Jan 20, 2008 1:26 PM, Stephen Waterbury <waterbug at pangalactic.us> wrote:
>> Christopher Armstrong wrote:
>>> Hi Steve!
>> Hi Chris!
>>
>> I saw that you were involved in Storm, which is part of the
>> reason I decided to try it (in fact, I'm using your twisted-integration
>> branch, though I haven't tried it out with twisted yet) -- at least I
>> know that its twisted support will be good!
> 
> Glad to hear my influence is strong and far-reaching. But fyi, that
> branch is Thomas Hervé's :)

Indeed, not 1 but 2 twisted minions are involved -- so much
the better!  :)

>> Actually, that wasn't exactly the problem I was referring to:
>> SQL won't even let you *create* a column that is a *reserved*
>> keyword (note that SQL has reserved keywords and non-reserved
>> keywords -- the reserved ones are the ones you have to avoid).
>> E.g.:
> ...
>>  >>> store.execute('CREATE TABLE thing '
>> ...               '(id INTEGER PRIMARY KEY, '
>> ...               ' authorization TEXT)')
>> Traceback (most recent call last):
> ...
>> psycopg2.ProgrammingError: syntax error at or near "authorization"
>> LINE 1: CREATE TABLE thing (id INTEGER PRIMARY KEY,  authorization T...
>>                                                       ^
>> -----------------------------------------------------------------------
>>
>> This is of course not Storm's fault at all:  "authorization" is an
>> SQL reserved keyword, so you can't have a column named
>> "authorization", period.
> 
> Actually,
> 
> CREATE TABLE foo ("authorization" INTEGER);
> 
> works. If you create your schema with properly quoted column names,
> you should be able to use storm with those column names and not have
> any problems.

True -- thanks for the SQL lesson, I didn't know about that!
However, what about this?
-----------------------------------------------------------------------
waterbug at pangalactic:~$ python
Python 2.5.1 (r251:54863, Oct  5 2007, 13:36:32)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from storm.locals import *
>>> class Thing(object):
...     __storm_table__ = 'thing'
...     id = Int(primary=True)
...     authorization = Unicode()
...
>>> db = create_database('postgres:stormtest')
>>> store = Store(db)
>>> store.execute('CREATE TABLE thing '
...               '(id INTEGER PRIMARY KEY, '
...               ' "authorization" TEXT)')
<storm.databases.postgres.PostgresResult object at 0x833aa0c>
>>> thing = Thing()
>>> thing.authorization = u'moi'
>>> store.add(thing)
<__main__.Thing object at 0x833f0ec>
>>> store.commit()
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/store.py",
line 102, in commit
     self.flush()
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/store.py",
line 436, in flush
     self._flush_one(obj_info)
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/store.py",
line 470, in _flush_one
     result = self._connection.execute(expr)
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/database.py",
line 182, in execute
     raw_cursor = self.raw_execute(statement, params)
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/databases/postgres.py", 

line 173, in raw_execute
     return Connection.raw_execute(self, statement, params)
   File
"/home/waterbug/src/Python/Storm/twisted-integration/storm/database.py",
line 247, in raw_execute
     raw_cursor.execute(statement, params)
psycopg2.ProgrammingError: syntax error at or near "authorization"
LINE 1: INSERT INTO thing (authorization) VALUES ('moi')
                            ^
-----------------------------------------------------------------------

Store.add doesn't seem to do the right thing.

Steve