I try to restrict this 'blog to subject matter which annoys me, avoiding work, personal reminiscences and my over-indulged computing hobby.

Today I shall have been mainly annoyed about database reflection, in the sense of "automatically give me a web app which lets me view and update the contents of an SQL database". There are lots of solutions to this problem, but they partake of some of the following deficiencies:

• they also solve the problem of editing the database schema
• they are written in languages of exclusively epidemiological interest (e.g., PHP and other disease vectors such as Perl)
• they require a separate copy of the schema to reside outside the database
• they are written with the assumption that the database schema will be instantiated by the web app

Now leaving programmers in charge of the database schema does not scale, and many good programmers are afflicted by fear, ignorance and prejudice where SQL is concerned. In any case, larger organisations employ sysadmins to run production database servers and these DBAs are just not going to let some random Perl script scribble all over their database, if indeed it's allowed to run with any privileges at all.

So any off-the-shelf solution whose preference is to issue all those CREATE TABLE statements itself must be approached cautiously. The bad news is that for small-scale systems this is an acceptable means of prototyping, so this behaviour is endemic in web app frameworks (and indeed is regarded as so normal that some of the documentation refers to "legacy" databases as though it were undesirable that they should not be created by some web app). If you want to re-use any of these components, you may well find yourself stuck in the "update table requires alter table privilege" ghetto. The competent people who are writing the Create-Read-Update-Delete ("CRUD") frontend don't want to have to reimplement the whole stack, so they just give you access to whatever database tables the rest of the framework presents them.

... and these frameworks, being written in dynamic languages, instantiate database entities as objects or classes in their own right. Where the web app contains the authoritative copy of the schema, doubtless in some pidgin DDL, it's obviously trivial for a cargo cult programmer to cut-n-paste himself a few more database tables by imitating the the ritual incantations elsewhere in the models file.

I reproduce an example of this stuff below out of sheer sadism:

 
class UnbearableMovie(DeclarativeBase):
    __tablename__ = 'unbearable_movie'

    #{ Columns

    id = Column(Integer, primary_key=True)
    title = Column(Unicode(255), nullable=False)
    director = Column(Unicode(255), nullable=False)

    #} 

So once you've included that in your code, you do m = UnbearableMovie(name="Sex and the City") or whatever. This is no good if you don't know what the tables are called because someone else is deciding that or it might change, but to their credit the SQLAlchemy people have an autoLoad feature which inspects a database table for its column names. I couldn't find anything on the web which ran this on all database tables in Python, but did find people with the same problem. So here is a rough and very brittle solution:

class ReflectedTable(object):
    def __init__(self, **kwargs):
        for k, v in self.iteritems():
            setattr(self, k, v)

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

    global metadata

    DBSession.configure(bind=engine)

    def table_names():
        introspect = '''SELECT name FROM sqlite_master
                            WHERE type = 'table';'''
        for row in engine.execute(introspect):
            yield str(row[0])
    
    def do_reflect(name):
        class_name = name.title()

        reflected_table = Table(name, metadata, autoload=True, 
                                                autoload_with=engine)
        cls = type(class_name, (ReflectedTable,), {})
        mapper(cls, reflected_table)
        setattr(MY_APP_NAME.model.reflected, class_name, cls)

    for name in table_names():
        do_reflect(name)

Combined with the catwalk CRUD frontend, this is enough to get the necessary: a database whose schema changes are reflected in a web app without further hassle.

UPDATE: Someone may well have got there before me, and written SqlSoup, though I've not checked that that does what I want. Interestingly, the chap who wrote has written a "blow off steam" blog post just like this one to vent his frustrations.