How to use tg-admin sql upgrade

Posted on by

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.

14 thoughts on “How to use tg-admin sql upgrade”

  1. Thanks for the info. I started playing with TG a year ago and been meaning to get back into it. Here’s a something play with.

    As a DBA by day, I’m glad to see you using constrainsts – I just wished they used them here at work 🙁

  2. Hey Glenn, thanks for the comment. Constraints and compound foreign keys are really good things, but I don’t see them used very often. It seems like a lot of programmers don’t realize they exist.

  3. hmm… I can’t get ‘tg-admin sql’ to recognize the ‘record’ or ‘upgrade’ options at all. It only lists ‘create’, ‘execute’, ‘list’, and ‘status’ as options. I’m running tg v1.0.4.2. Any idea why?

  4. Hi Greg,

    That sounds really bizarre!

    I suggest you paste the results of “tg-admin sql help” into an email and send it to the turbogears mailing list. I’ll take a look at it on there.

    For record and upgrade, the first time you use it, you gotta force the sqlobject_version_history table in.

  5. Hi Greg,

    you are probably using SQLAlchemy for your model. The “tg-admin sql” tool is really only a wrapper around sqlobject-admin. Naturally this only works for SQLObject model. For SQLAlchemy, “tg-admin sql” is only a very basic substitute for this integrated into TurboGears, because SQLAlchemy does not provide such a tool as sqlobject-admin.

    Feel free to file a feature request ticket for this on our Trac (http://trac.turbogears.org/newticket).

    @Matt Why this isn’t more widely advertised? Because you didn’t put the info on the wiki yet! 😉 Honestly, if you want to turn this into a page on our doc wiki (see http://docs.turbogears.org/DocHelp), you’d be more than welcome! Just put it there in whatever form you see fit and I’ll see that I adapt it to our wiki docs standards.

    Chris

  7. One thing that you might want to mention in this page somewhere is that all of the .sql files should be relative to the sql-history directory not the root of the application. It took me quite a while to figure that one out. Thanks for the info on this though.

    Craig

  9. # 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.

Comments are closed.