t+1: Matt Wilson’s blog

The tg-admin script that is bundled with turbogears is really helpful, but I had a hard time learning how to use it.

Before you read any more, you should know that this only works when you use SQLObject, not SQLAlchemy, for your ORM.

These are my notes on how I use tg-admin to upgrade an existing database.

• I have a production database that uses prod.cfg;
• I have a development database that uses dev.cfg;
• Neither databases have a sqlobject_db_version table initially, because I never payed attention to it yet.

The development database has a bunch of new columns, tables, and indexes that I want to add to the production database. For this example, I’ll pretend that all I want to do is add an index to a table.

First, I made sure that the dev database matches sqlobject classes:

tg-admin -c dev.cfg sql status


If those are out of sync, then do whatever you need to do to make sure your actual dev database matches your classes. Of course, tg-admin sql status is not perfect. For example, it overlooks missing indexes and constraints, at least with postgres.

Next, I recorded the state of the development database:

tg-admin -c dev.cfg sql record --force-db-version=2008-03-21

This will make a new table in the dev database called sqlobject_db_version. I am forcing it to have a value of today’s date (March 21st, 2008).

Now I connect to the production database and set a version on it with yesterday’s date:

tg-admin -c prod.cfg sql record --force-db-version=2008-03-20


Now I run this to try to upgrade the production database to match the development database:

tg-admin -c prod.cfg sql upgrade


Of course, that should fail, and I see some error message sort of like this:

$ tg-admin -c prod.cfg sql upgrade
Using database URI postgres://staffknex:staffknex@localhost/staffknex320
No way to upgrade from 2008-03-20 to 2008-03-21
(you need a 2008-03-20/upgrade_postgres_2008-03-21.sql script)


This is an example of a helpful error message. I need to write a script that will explain how to upgrade from yesterday’s version to today’s version.

That script will be really simple:

BEGIN;
CREATE UNIQUE INDEX majestic12 ON ufo_theorists (first_name, last_name);
END;


I suggest using BEGIN and END so that in case something goes wrong in the middle, your transaction will be rolled back automatically.

Now I can run this:

tg-admin -c prod.cfg sql upgrade

And my production database will be upgraded with the new index.

Now for some complaints:

• Why isn’t this advertised better? This is a really nice feature.
• You’re supposed to be able to specify the URI on the command-line with the –connection option, but I could never get it to work.
• I really wish that tg-admin sql status detected stuff like missing indexes and constraints. I use these things heavily.
• It would be nice to be able to mix python into the upgrade script, rather than just SQL. For example, I recently dropped a column that had both an employee’s first and last name, and separated this into two columns. I used SQL to make the new columns, then I used python to read data out of the old single column and write it to the two new columns. Then I used SQL again to drop the old column.

Like I said at the beginning, this is a really helpful script and I’m very grateful to whoever wrote it.

These are a few of the rules I try to follow in my design. So far, they’ve helped me out.

I aim to finish all interaction with the database before I get to the template layer.

This is non-trivial because it is so easy to forget that a method or an attribute will evaluate into a query. I use this rule because it lets me be certain about the number of interactions each page will have with the database.

I avoid branching (if-else clause) in my templates as much as possible.

I have a really hard time detangling code when I find a bunch of nested if statements. For all but the most trivial instances, I prefer to have a bunch of similar templates and then choose the best one. For example, instead of handling both a successful login and a failed login in a single template, I’ll make two different files and then choose the right one in my controller.

In practice, I have some really similar templates. But then I go back and strip out as much of the common code as possible and put those into widgets.

Any time I find a select() call in my controller, I consider making a new method in my model.

When I write something like this in a controller:

bluebirds = model.Bird.select(Bird.q.color == 'blue')


I usually come back later and put in something like this into the Bird class:

class Bird(SQLObject):
    color = UnicodeCol()
 
    @classmethod
    def by_color(cls, color)
       return cls.select(cls.q.color == color)


Now I have something that I can reuse. If I’m feeling whimsical I’ll use functools.partial to do something like this:

class Bird(SQLObject):
    color = UnicodeCol()
 
    def by_color(self, color):
        return self.select(self.q.color == color)
 
    redbirds = classmethod(partial(by_color, color='red'))
    bluebirds = classmethod(partial(by_color, color='blue'))


Sidenote: I couldn’t figure out how to use the @classmethod decorator in the second version of by_color because partial complained. Appararently, callable(some_class_method) returns False, and partial requires the first argument to be a callable.

Maybe a reader can explain to me what’s going on there…

I love SQLObject, but this is a rant about the tiny frustrations I face with it.

First, this is a minor point. I don’t really care about database independence that much. Postgres has a lot of wonderful features: I never have to worry about choosing the table engine that will enforce foreign key constraints, I like creating indexes with function inside:

create unique index nodup_parent on category (org_id, parent_cat, lower(name));

and I really like how easy it is to write stored procedures. Anyway, since I know I’m going to use postgresql, I don’t want to be restricted to only the features that exist or can be emulated in every platform. I know all about sqlmeta and createSQL and use it plenty. But I don’t like how when I set a default value, sometimes it is set in the database table, and other times, it isn’t.

Anyway, in practice, the most dangerous part of using SQLObject is that it hypnotizes you into forgetting about the queries behind everything. Imagine you have employees, departments, and a join table between them. You can set this up in SQLObject like this:

class Employee(SQLobject):
    name = UnicodeCol(alternateID=True)
    departments = RelatedJoin('Department')
 
class Department(SQLObject):
    name = UnicodeCol(alternateID=True)
    employees = RelatedJoin('Employee')

You want to draw a grid that indicates whether each user is a member in every group, so you might dash off some code like this:

for emp in Employee.select():
    for d in Department.select():
        if d in emp.departments:
            print "yes!"
        else:
            print "no!"


In an ideal scenario, you can do this with three simple queries:

• You need a list of employees
• You need a list of departments
• You need the list of employee-department of associations.

People that talk about how you can use outer joins to cram all that into one query will be dropped into a bottomless pit. Besides, I profiled it, and three separate queries is often much cheaper.

Anyway, back to the point. SQLObject will only run a single query to get the employees and a separate single query to get all the departments. So that’s good.

However, the place where all hell breaks loose is that if clause in the middle. If we have three employees and four departments, this statement

if d in emp.departments:

executes a dozen times. That’s unavoidable. The problem is that each time it executes, SQLObject runs a query like:

select department_id from department_employee where employee_id = (whatever);

Every time you say “is this particular department in this employee’s list of departments?” SQLObject grabs the full list of departments for that employee. So, if you ask about 10 different departments, you will run the exact same query ten times. Sure, the database is likely to cache the results of the query for you, but it is still very wasteful.

With just a few employees and a few departments, that’s not so bad. Eventually, though, as the number of employees and departments grow, the cost of that code grows at N², which is just geek slang for sucky.

So, in conclusion, this may sound like a rant, but it really isnt. SQLObject is great. But it isn’t magic. It’s a great scaffolding system. But now I find that I’m rewriting a fair portion of code in order to reduce the database costs.

Aside: when I started paying attention to the queries generated by SQLObject, I found it really useful to edit postgresql.conf and enable log_min_duration_statement. Then every query and its cost will be logged for you. This is really useful stuff. It’s helped me to relax about doing a lot of things that I used to think were really bad.

The /visit/api.py file in the 1.0.4b3 tag of turbogears has this function, starting on line 177:

        def encode_utf8(params):
            '''
            will recursively encode to utf-8 all values in a dictionnary
            '''
            res = dict()
            for k, v in params.items():
                if type(v) is dict:
                    res[k] = encode_utf8(v)
 
                else:
                    res[k] = v.encode('utf-8')
 
            return res


If you have a query string like ?a=1&a=2, then params has a key u’a’ that points to a list that contains u’1′ and u’2′. And encode isn’t defined for lists, so . . .

Fortunately, the /visit/api.py file in the branches/1.0 branch already has a fix for this problem, so I ran setup.py develop in my checkout directory and was back in business.

I lost so much time today figuring this out because I kept looking for the bug in my code, rather than in the framework itself. Also, the code works fine as long as the query string doesn’t have more than one value for the same key.

While I’m on the soapbox, I really wish that testutil.py would change this function:

    def tearDown(self):
        database.rollback_all()
        for item in self._get_soClasses():
            if isinstance(item, types.TypeType) and issubclass(item,
                sqlobject.SQLObject) and item != sqlobject.SQLObject \
                and item != InheritableSQLObject:
                item.dropTable(ifExists=True)

to something sort of like this instead:

    def tearDown(self):
        database.rollback_all()
        import copy # Probably don't actually import here, but this is just for illustration.
        x = copy.copy(self.__get_soClasses())  # store a copy of the list.
        x.reverse() # Now reverse it.
        for item in x: # Iterate the reversed copy.
            if isinstance(item, types.TypeType) and issubclass(item,
                sqlobject.SQLObject) and item != sqlobject.SQLObject \
                and item != InheritableSQLObject:
                item.dropTable(ifExists=True)


The whole point of using self.__get_soClasses is that it looks for a list that defines the order to follow when creating tables. You can define soClasses in your model to make sure that your independent tables are created before your dependent tables.

Well, when it comes time to destroy all your tables, you should destroy the dependent tables first.

I posted this about a month ago to the turbogears trunk mailing list already.

Sidenote — if you’re one of the people that are selflessly donating your time to working on turbogears, please don’t take my rants here personally. I’m really grateful that other people are building tools and giving them away, so that I can make a living.

I just put HTML code into my data model. I have a list-of-objects page. Each object is an instance of an object defined in my data model, derived from a row in a database. Each object needs a pretty link drawn that object’s detailed-view page. So I added a property on my object:
class Message(SQLObject):
    def _get_view(self):
        "Draw a link to the view page for this message."
        return cElementTree.XML("""<a href="/details/%d">VIEW</a>""" % self.id)
    # Lots of other stuff snipped out.

This is now what my kid template looks like:

        <tr py:for="m in messages">
            <td py:for="col in columns" py:content="getattr(m, col)">MESSAGE STUFF</td>


I pass in messages and columns; messages is a list of objects and columns is a tuple of strings that map to attributes or properties, like “view”.

I’m happy with this decision. I know I could have manipulated the messages or created some new classes in my controller, but I couldn’t really see any advantage. This way works.

I just don’t want anyone else doing this

I bought the TurboGears book about two weeks ago, and I have been working through it. I like the book in general, but I agree with the reviewers on Amazon that complain about the number of errors. I can’t think of another programming book that I’ve read with this many errors.

All of the errors I noticed are little glitchy typographical errors, rather than incorrect theory. The authors really do a good job of illustrating the MVC approach to web design, so I’m glad I bought it.

Anyway, this page lists mistakes found after publication, and the community of readers seems to be doing a good job of helping each other out.

I think I might have found another tiny error. This code appears at the bottom of page 109:

class ProjectFields(widgets.WidgetsList):
    title = TextField(label="project", validator=validators.NotEmpty())
    client_revenue = widgets.TextField(validator=validators.Number())
    project_form = widgets.TableForm(fields=ProjectFields(), action="save_project_test")


I don’t see the point in using both TextField and widgets.TextField. But more importantly, I think the indentation is wrong in the last line. I don’t think project_form is supposed to be an attribute of the ProjectField class.

I think the code should look more like this:


class ProjectFields(widgets.WidgetsList):
    title = widgets.TextField(label="project", validator=validators.NotEmpty())
    client_revenue = widgets.TextField(validator=validators.Number())
 
# Moved outside the class.
project_form = widgets.TableForm(fields=ProjectFields(), action="save_project_test")


But maybe I’m missing something. I posted to the TurboGears Book mailing list, so hopefully I’ll find out.

BeContrary.com is a very clever idea for a site. This debate on going dutch illustrates how the site works. And this is a good discussion of different styles of python templates.

The site’s author, Will McGugan, wrote up a blog post describing his experience with turbogears here. He says he chose TurboGears partially because he had already worked with CherryPy and really liked it. Will made this remark after talking about SQLObject:

Incidently, I don’t like the way that Python ORMs specify the database schema as Python code. Since the schema doesn’t change when the application is running, I would prefer a simple xml definition that would be used by the ORM to dynamically build classes for each table that could be derived from in the model code.

I like this idea, but instead of writing XML, I would prefer to write SQL and have python parse that to build classes.

I wanted to translate a(1).b(2)into the TurboGears URL /a/1/b/2. A browser making a request for /a/1/b/2 would trigger that code.

This page explains how to do it. You build a single default method that catches everything and then does introspection to figure out where to send the request.

It works fine, but it isn’t nearly as obvious or concise as the regular-expression approach I’ve seen in rails and Django.

It shouldn’t be so hard to set up turbogears on ubuntu Feisty Fox. The easy_install-2.4 turbogears approach crashes when trying to install Cheetah.

So, here’s a list of everything I had to do:

• I installed the Ubuntu package for Cheetah: sudo apt-get install python-cheetah
• Then I could run the easy install: sudo easy_install-2.4 turbogears
• I edited /etc/apt/sources.list and added multiverse to the list of available packages. In other words, I changed this section:

deb http://archive.ubuntu.com/ubuntu/ feisty universe
deb-src http://archive.ubuntu.com/ubuntu/ feisty universe

to look like this:

deb http://archive.ubuntu.com/ubuntu/ feisty universe multiverse
deb-src http://archive.ubuntu.com/ubuntu/ feisty universe multiverse

• Then I installed the python profiler package: sudo apt-get install python-profiler.
• I tried installing pysqlite2 through easy_install, but that blew up, so I installed ubuntu package for pysqlite2 instead: sudo apt-get install python-pysqlite2.

Now everything works.