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.

My asparagus has returned

I planted asparagus crowns last spring. Asparagus crowns look like dried out root clumps. At the end of last summer, I started finding little shoots around where I planted the crowns. Those shoots grew plants about three feet tall with lots of fine leaves. Very pretty.

I didn’t harvest anything last year. It takes several years for a planted crown to be ready. Here’s way more detail than most people want, from the Michigan Asparagus Advisory Board:

Do not harvest your asparagus the year you plant it or the year following planting. The asparagus plant needs to grow and establish a healthy crown and it will need all of its energy to do that. The third-year after transplanting we generally harvest the field for about two weeks.

Then winter came and all the plants died. After a few weeks, the plants looked like straw. Then on Sunday I found this shoot:

My asparagus is coming up

I found a few more shoots in other places. This is really exciting. Only one more year to go before my harvest!

See the whole gallery here.

My ten most-frequently-used shell commands

Here they are:
$ history|awk '{a[$2]++ } END{for(i in a){print a[i] " " i}}'|sort -rn|head
80 cd
59 svn
49 bzr
40 sudo
35 vi
32 nosetests
26 l
15 rfcomm
14 screen
14 c

l is an alias for ls and c is an alias for clear. rfcomm is how I connect to my mobile phone over a virtual serial port via bluetooth.

I’m happy that vi and nosetests are right next to each other. It looks like I’m pretty good about rerunning my test cases after editing.

I got the idea for this post from this guy.

My article is finally online

Introduction to Python Decorators is available for you to read after you fill out the annoying registration form.

I have a few ideas for the next article. Do any of these seem interesting?

  1. Demystify metaclasses: use metaclasses to add camel-cased aliases for underscored method names, show how to automatically make methods into properties, and build a crude ORM.
  2. Explore logging with python, ranging from writing out to local files to setting up a syslog-ng server. Show how to use logging config files and filters.
  3. Build a prototype inheritance system into python. I got really interested in prototype inheritance when I studied lua. Prototypes make it really easy to change class-based behaviors at run time.

Finally, the meaning behind the pirates-vs-ninjas debate became clear to me during a recent nitrous-oxide haze (no, not how you think; I was getting my teeth cleaned at the dentist). Anyhow, pirates and ninjas are symbols.

The ninja is a metaphor for the corporate employee. A ninja will get the job done or die trying. A ninja will kill everyone in his own family if he’s ordered to. A ninja has no sense of entitlement or dignity or flex time.

Meanwhile, the pirate is the entrepeneur, or maybe the upper-level executive. He has no sense of duty or honor. He seeks adventure and glory only. He’ll jump ship as soon as possible. He might even maroon his crew-mates on a desert island if it means he gets the treasure to himself.

Pirates love to hire ninjas because a ninja never disobeys. Ninjas love to kill pirates because they can pretend they’re killing their own pirate boss.

Flex Camp Cleveland was fun

Yesterday I attended Flex Camp Cleveland.

Slides from the presentation “Introduction to Object-Oriented Programming” are available here. Thanks to Kristopher Schultz for the link.

A few random notes and opinions mixed together:

  • Flex is a topic that attracted a lot of people! We had more than a hundred attendees, and at least drove from places like Columbus, Michigan, and Pittsburgh.
  • There’s a lot of technical talent in Ohio. I don’t believe that we have a technical shortage. Instead, we have a shortage of managers and entrepeneurs that know how to build technology-based businesses.
  • Flex addresses a lot of my personal frustrations with HTML. In particular, I’m thrilled to be able to add tags to the language itself.
  • Flex 3.0 is not just zero-cost, but truly open-source.
  • Search engine spiders may have difficulty indexing flex apps, but typically, you don’t want search engines linking into your app. You want them linking into your documentation and marketing text.
  • People say that there’s a difference between being knowledgeable about a subject and being able to teach that subject well. The same thing is true about public speaking. Speaking well is really hard to do. You can easily tell a professional from an amateur.
  • Air is now available on linux, in an alpha state. Hurray!
  • Flex offers server-initiated pushes out to clients. Flex uses a family of protocols to make this happen. Depending on network configuration, it uses anything from a true server-initiated push to client-side polling.
  • We all got flexcamp t-shirts, but they were all sized extra-large. Any reader that wants mine is welcome to it.
  • I’m a command-line snob and don’t like using GUIs to build user interfaces, but I was impressed by how powerful the designer view was in flex builder. I wasn’t impressed enough to pay for it though.

Neat code complexity tool.

David Stanek wrote a nice utility to measure code complexity. This post explains the details. Anyway, I downloaded his code and tried it out. I really like it:

$ cat matt.py
"""A few functions for use with pygenie."""
def f(x):
"Make an 8-way branch, 1 layer deep."
if x == 1: return 1
elif x == 2: return 2
elif x == 3: return 3
elif x == 4: return 4
elif x == 5: return 5
elif x == 6: return 6
elif x == 7: return 7
elif x == 8: return 8

def g(a, b, c):
"This function has 8 paths."
if a:
if b:
if c:
return 1 # a and b and c.
else:
return 2 # a and b and not c.
else:
if c:
return 3 # a and not b and c
else:
return 4 # a and not b and not c.
else:
if b:
if c:
return 5 # not a and b and c.
else:
return 6 # not a and not b and c.
else:
if c:
return 7 # not a and b and not c.
else:
return 8 # not a and not b and not c.

def h(x):
if x: return True
else: return False

And this is what happens when I run the code:

$ ./pygenie.py complexity matt.py
File: /home/matt/svn-checkouts/cyclic_complexity/matt.py
Type Name Complexity
--------------------
F f 9
F g 8

The functions f and g have a complexity exceeding 7, so they print out.

This might make a nice nose plugin.