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

[Stephen Waterbury]

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!

> On Jan 19, 2008 8:38 PM, Stephen Waterbury <waterbug at pangalactic.us> wrote:
>> Now for my questions:
>>
>> (1)  Collisions between SQL table/column names and SQL reserved
>> keywords are not permitted, as of course you are aware (ref., e.g.,
>> <http://www.postgresql.org/docs/8.1/static/sql-keywords-appendix.html>).
>>
> ...
>> Having now played with Storm and inspected the code, it seems that
>> Storm's way of dealing with this issue is to say "Don't do that!"
>> Is that correct?  That is of course a perfectly legitimate design
>> decision -- I just wanted confirmation so I know whether I need
>> to find my own way to work around it.
> 
> Storm automatically escapes SQL keywords that are being used as table
> names or column names. You shouldn't need to do anything special to
> get it to work. There's no need to actually change the names of your
> columns and tables, they just need to be quoted.

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.:
-----------------------------------------------------------------------
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)')
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File 
"/home/waterbug/src/Python/Storm/twisted-integration/storm/store.py", 
line 90, in execute
     return self._connection.execute(statement, params, noresult)
   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 242, in raw_execute
     raw_cursor.execute(statement)
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.  With my underscore-mapping, you can
still have a "Thing" class with an "authorization" attribute,
but the corresponding table will be "_thing" and it will have
columns "_id" and "_authorization" -- IOW, your choice of
attribute names is completely unrestricted by SQL reserved
keyword considerations.

As I say, my patch is a one-liner, so pretty trivial to
maintain!

>> (2)  My app is "interface-driven" rather than "class-driven" --
>> i.e., interfaces (I use zope.interface) are the primary containers
>> for application metadata, which then gets propagated to classes,
>> sql tables, user interfaces, and serialization methods from the
>> interfaces (which are themselves generated from external schema,
>> such as ontologies).  It's not much of an effort to create
>> Storm-style classes from the interfaces automatically --
>> I've got that working now.  Is the current plan for Storm
>> to continue to be "class-driven" as it is now?  Again, that's
>> a legitimate design decision -- I'm just looking for confirmation
>> before I continue with my work-around.
> 
> There are currently no plans to change this.

Thanks -- not a problem!

Cheers,
Steve