How to use tg-admin sql upgrade

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.

Published by

matt

My name is Matt Wilson and I live in Cleveland Heights, Ohio. I love random emails from strangers, so get in touch! matt@tplus1.com.

  • Glenn

    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 🙁

  • http://tplus1.com matt

    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.

  • greg

    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?

  • http://tplus1.com matt

    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.

  • http://chrisarndt.de/ Christopher Arndt

    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

  • http://tplus1.com matt

    Chris,

    I just created http://docs.turbogears.org/1.0/TgAdminSqlUpgrade with the text from this blog, and added a link to the 1.0/RoughDocs page.

    I saw the TgAdmin page, and maybe editors would want to consolidate mine into there, or at least add a link to this new page from there.

  • Craig

    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

  • http://tplus1.com matt

    Hi Craig, thanks for the tip. There’s now a wiki page on the TG site that is based on this article, so feel free to add your notes over there. This is the page.

  • http://www.uk-cheapest.co.uk Domain Names

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

  • http://www.cruiselinefans.com/ cheap cruises

    This posting is marvelous and what a fantastic research that you have done. It has helped me a lot. thank you very much.

  • EwingGouldingn77

    I subscribed to your blog when is the next post

    regards
    mike hlower
    ______________________________________________
    Italian Translation Service | Polywood lawn furniture | seattle divorce lawyer

  • http://www.wholesaleinc.net/ wholesale cell phone

    Thanks for sharing such wonderful experinence

  • http://www.mackaysatellite.com/ Satellite Phones Service

    When is the next post comming on this topic.

    regards
    CARLITA LORILEE
    ______________________________________________

  • http://knowbuckwheatpillow.com/ Bella

    Great tutorial. Absolutely fantastic!