2006-03-01

Views in SQLObject

I've been doing lots of turbogears hacking lately, and one of the things that i've found it necessery to do, is arbitarily complicated queries, without the help of the magic of SQLObject's query building syntax. So I present, the way to implement SQL Views as SQLObject abstracted objects. Views are database specific, and I have only ever tested with postgresql, YMMV, no warranty implied, etc.

This is not a real example, my use-case for a view had left-joins on 6 tables, not just one, but it's a good template to work from. Note that you always have to have an 'id' column come back, and it has to be a valid primary key for the view, otherwise SQLObjet magic blows up.

class SiteSearch(SQLObject):
    name_one = StringCol()
    name_two = StringCol()
    somebool = BoolCol()

    @classmethod
    def createTable(cls, ifNotExists=False, createJoinTables=True,
                    createIndexes=True, applyConstraints=True,
                    connection=None):
        conn = connection or cls._connection
        if ifNotExists and conn.tableExists(cls.sqlmeta.table):
            return

        sql, constraints = cls.createTableSQL()

        # Treat the view like a constraint, only create it
        # after all the other tables have been created.
        return [sql]

    @classmethod
    def createTableSQL(cls, createJoinTables=True, createIndexes=True,
                       connection=None):
        return """CREATE VIEW %s as (
        SELECT table_one.id as id,
               table_one.some_name as name_one,
               table_two.some_name as name_two,
               SOMEFUNC(table_one.foo + table_two.bar) as somebool
        FROM 
            table_one 
            LEFT JOIN table_two ON (table_one.foo_id = table_one.id)
        ) """ % (cls.sqlmeta.table,), []

I hope someone finds that useful. :)

2 comments:

Anonymous said...

Cool! What about posting this recipe on the TG trac?

http://trac.turbogears.org

Stephen Thorne said...

Good idea. It's there now: ViewsInSqlObject