SQLAlchemy tidbits from reflecting an existing schema

gistfile1.txt

Goal: Transform a raw SQL file into SQLAlchemy models and functions

caveats: I started from zero - never have created models from scratch with SQLAlchemy before. 

= Initial reflection
http://code.google.com/p/sqlautocode/

tl;dr: Could use some work, but saved me a bit of time.

Did a good job of: 
* Capturing majority of the base tables and mostly did well with Declarative

Buglets/stuff that could be implemented: 
* Concept of plurals is pretty off (Changed 'Status' to 'Statu')
* Didn't capture server-side defaults
* Didn't add 'autoincrement=False' on columns that didn't have sequences associated
* Didn't name sequences to match existing schema
* Mixed declarative tables with "the other way" -- this might be by design, need to dig a bit
* No concept of server-side user defined functions
* No concept of aggregates
* No concept of custom types
* No concept of domains
* No CITEXT (but this is really a SQLAlchemy problem), and custom types in general were problematic

Stuff I should learn better: 
* How to specify custom types so that sqlautocode could use them
* Quick script for comparing before/after databases (diffs suck!), check_postgres.pl probably could do most of this

= Creating Views
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views
Related example:
http://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy

= Escaping %
This was a duh, but documented here so I don't forget:
http://stackoverflow.com/questions/8657508/strange-sqlalchemy-error-message-typeerror-dict-object-does-not-support-inde

= Declaring functions

I hacked this in with statements like: 
@event.listens_for(UptimeLevel.__table__, "before_create")
def to_major_version(target, connection, **kw):
    to_major_version = """
...
"""
    connection.execute(to_major_version)

Pretty easy to script/search-replace in raw SQL to create these. I may regret this later... but for now, I can't see why this is wrong.