Just a Theory

I recently discovered a mildly annoying bug in Sqitch, the Git-inspired database schema change management app I’ve been working on for the past year. One of its key features is the ability to “rework” changes. For example, if you have a change that defines a function change_password(), and discover sometime after release that it has a bug (maybe the hashing algorithm is too weak), you can “rework” it – essentially modify it in place – and save some headaches. Check out the “In Place Changes” section of the (PostgreSQL, SQLite, Oracle, or MySQL (coming soon) tutorials for detailed examples of how it works.

The bug was about what happens when one adds a new change that depends on a reworked change, but just specifies it by name, such as change_password:

sqitch add meow --requires change_password

This added the change fine, but at deploy time, Sqitch complained that there were multiple instances of a change in the database. Of course, that’s true, because change_password will have been deployed twice: once for the original version, and the second time for the reworked version. This was inconsistent with how it looked up changes in the plan, where it would just return the first instance of a change in the plan. So I changed it so that dependency lookups in the database also return the first instance of the change. I believe this makes sense, because if you require change_password, without specifying which instance you want, you probably want any instance, starting with the earliest.

But what if you actually need to require a specific instance of a reworked change? Let’s say your plan looks like this:

users
widgets
change_pass
sleep
@v1.0

work_stuff
change_pass [change_pass@v1.0]

The third change is change_pass, and it has been reworked in the sixth change (requiring the previous version, as of the @v1.0 tag). If you want to require any instance of change_pass, you specify it as in the previous example. But what if there were changes in the reworked version that you require? You might try to require it as-of the symbolic tag @HEAD:

sqitch add meow --requires change_password@HEAD

This means, “Require the last instance of change_password in the plan.” And that would work…until you reworked it again, then it would be updated to point at the newer instance. Sqitch will choke on that, because you can’t require changes that appear later in the plan.

So what we have to do instead is add a new tag after the second instance of change_pass:

sqitch tag rehash

Now the plan will look like this:

users
widgets
change_pass
sleep
@v1.0

work_stuff
change_pass [change_pass@v1.0]
@rehash

Now we can identify exactly the instance we need by specifying that tag:

sqitch add meow --requires change_password@rehash

Meaning “The instance of change_password as of @rehash.” If what you really needed was the first version, you can specify the tag that follows it:

sqitch add meow --requires change_password@v1.0

Which, since it is the first instance is the same as specifying no tag at all. But if there were, say, four instances of change_pass, you can see how it might be important to use tags to specify specific instances for dependencies.

For what it’s worth, this is how to get around the original bug referenced above: just specify which instance of the change to require by using a tag that follows that instance, and the error should go away.

I was traveling last week, and knowing I would be offline a fair bit, not to mention seriously jet-lagged, I put my hacking efforts into getting MySQL support into Sqitch. I merged it in yesterday; check out the tutorial if you’re interested in it. I expect to release v0.980 with the MySQL support in a couple of weeks; testing and feedback would most appreciated.

There is a caveat, though: it requires MySQL v5.6.4. So if you’re stuck with an older MySQL, it won’t work. There are two reasons to require v5.6.4:

• The microsecond precision support in DATETIME values, added in v5.6.4. This makes it much easier to keep things in the proper order (deployments usually take less than a second).
• The SIGNAL functionality, introduced in v5.5. This allows the schema to mock a check constraint in the Sqitch database, as well as make it much easier to write verify tests (as described in the tutorial and figured out on StackOverflow).

But if you can afford to take advantage of a relatively modern MySQL, give it a shot!

The next release also makes a backwards-incompatible change to the SQLite engine: the default Sqitch database is no longer $db_dir/$db_name-sqitch.$suffix, but $db_dir/sqitch.$suffix. In other words, if you were deploying to a db named /var/db/myapp.db, Sqitch previously kept its metadata in /var/db/myapp-sqitch.db, but now will keep it in /var/db/sqitch.db. This is to make it more like the other engines (MySQL defaults to a database named “sqitch”, and Postgres and Oracle default to a schema named “sqitch”).

It’s also useful if you use the SQLite ATTACHDATABASE command to manage multiple database files in a single project. In that case, you will want to use the same metadata file for all the databases. Keep them all in the same directory with the same suffix and you get just that with the default sqitch database.

If you’d like it to have a different name, use sqitch config core.sqlite.sqitch_db $name to configure it. This will be useful if you don’t want to use the same Sqitch database to manage multiple databases, or if you do, but they live in different directories.

I haven’t released this change yet, and I am not a big-time SQLite user. So if this makes no sense, please comment on this issue. It’ll be a couple of weeks before I release v0.980, so there is time to reverse if if there’s consensus that it’s a bad idea.

But given another idea I’ve had, I suspect it will be okay. The idea is to expand on the concept of a Sqitch “target” by giving it its own command and configuration settings. Basically, it would be sort of like Git remotes: use URIs to specify database connection and parameter info (such as the sqitch database name for SQLite). These can be passed to database-touching commands, such as deploy, revert, log, and the like. They can also be given names and stored in the configuration file. The upshot is that it would enable invocations such as

sqitch deploy production
sqitch log qa
sqitch status pg://localhost/flipr?sqitch_schema=meta

See the GitHub issue for a fuller description of this feature. I’m certain that this would be useful at work, as we have a limited number of databases that we deploy each Sqitch project to, and it’s more of a PITA for my co-workers to remember to use different values for the --db-host, --db-user, --db-name and friends options. The project itself would just store the named list of relevant deployment targets.

And it alleviates the issue of specifying a different Sqitch database on SQLite or MySQL, as one can just create a named target that specifies it in the URI.

Not sure when I will get to this feature, though. I think it would be great to have, and maybe iovation would want me to spend some time on it in the next couple of months. But it might also be a great place for someone else to get started adding functionality to Sqitch.

Oh, and before I forget: it looks like Sqitch might soon get CUBRID support, too, thanks to Ștefan Suciu. Stay tuned!

Just a quick post to announce that I’ve set up a Sqitch Google Group. I’ve been getting a lot more email about it lately, and a fair bit of it should be shared more generally. So if you’re interested in Sqitch, sign up! (Don’t like web forums? Me neither. Feel free to subscribe by email, instead.)

I gave a tutorial at PGCon a couple weeks back, entitled “Agile Database Development with Git, Sqitch, and pgTAP.” It went well, I think. The Keynote document and an exported PDF have been posted on PGCon.org, and also uploaded to Speaker Deck. And embedded below, too. Want to follow along? Clone the tutorial Git repository and follow along. Here’s the teaser:

Hi, I’m David. I like to write database apps. Just as much as I like to write web apps. (Maybe more!) How? Not by relying on bolted-on, half-baked database integration tools like migrations, I’ll tell you that!. Instead, I make extensive use of best-of-breed tools for source control (Git), database unit testing (pgTAP), and database change management and deployment (Sqitch). If you’d like to get as much pleasure out of database development as you do application development, join me for this tutorial. We’ll develop a sample application using the processes and tools I’ve come to depend on, and you’ll find out whether they might work for you. Either way, I promise it will at least be an amusing use of your time.

I found myself with a little unexpected time at work recently, and since we use Oracle (for a few more months), I decided to port Sqitch. Last night, I released v0.970 with full support for Oracle. I did the development against an 11.2 VirtualBox VM, though I think it should work on 10g, as well.

Sqitch is available from the usual locations. For Oracle support, you’ll need the Instant Client, including SQL*Plus. Make sure you have $ORACLE_HOM set and you’ll be ready to install. Via CPAN, it’s

cpan install App::Sqitch DBD::Oracle

Via Homebrew:

brew tap theory/sqitch
brew install sqitch-oracle

Via ActiveState PPM, install ActivePerl, then run:

ppm install App-Sqitch DBD-Oracle

There are a few other minor tweaks and fixed in this release; check the release notes for details.

Want more? I will be giving a half-day tutorial, entitled “Agile Database Development,” on database development with Git, Sqitch, and pgTAP at on May 22 PGCon 2013 in Ottawa, Ontario. Come on up!

I got an invitation to write on Medium a couple weeks ago. I have been pondering some more philosophical posts lately, so I thought I’d try posting there. My first post, “Misguided Delusion,” tries to pull apart the the false dichotomy between faith and rationality. Yeah, really. That kind of thinking is a throwback to a previous career path, but one that has, of course, always stuck with me. And I am very happy with how the post turned out.

It remains to be seen whether or not I write more stuff like that. It’s rewarding, but time-consuming.

Arles from the Arena by Salva Barbera (CC BY 2.0)

We spent seven weeks last summer in Northern France. Man it was an awesome time. None of us wanted to leave! Well, almost true. The thing about Normandy is that the weather is very much like Portland—except that Juneuary lasts through July and August. We were so desperate for sun that we spent a week in Barcelona.

This summer will be different. Much warmer. No, not Portland, but two months in Arles, in Provence. Yes, we are once again doing a home exchange, this time in the city in which Vincent Van Gogh famously spent his final years. Nice, warm, Mediterranean weather.

We can’t wait.