I've just released Sqitch v0.20-TRIAL, the third testing release of Sqitch. Since last week, I've implemented add-step. So let's have a look-see at what all it can do. First, let's initialize a Sqitch project.

> mkdir myproj 
> cd myproj 
myproj> git init
Initialized empty Git repository in myproj/.git/
myproj> sqitch --engine pg init
Created sql/deploy
Created sql/revert
Created sql/test
Created ./sqitch.conf

Doesn't look like much, does it? Let's set the database name and look at the configuration:

myproj> sqitch config core.pg.db_name flipr_test
myproj> less sqitch.conf
[core]
    engine = pg
    # plan_file = sqitch.plan
    # sql_dir = sql
    # deploy_dir = sql/deploy
    # revert_dir = sql/revert
    # test_dir = sql/test
    # extension = sql
# [core "pg"]
    # db_name = 
    # client = psql
    # sqitch_schema = sqitch
    # password = 
    # port = 
    # host = 
    # username = 
[core "pg"]
    db_name = flipr_test

I've made an effort to make the default configuration file as useful as possible by including all the core and engine settings. Defaults are present, too, but commented-out. Some you'd probably never want to change in the local file, but might in your user file or in the system configuration file. Peruse the sqitch-config man page for all the Git-like awesomeness.s

So now we can add a step:

myproj> sqitch add-step user_roles
Created sql/deploy/user_roles.sql
Created sql/revert/user_roles.sql
Created sql/test/user_roles.sql

Wee! Again, doesn't look like much, I know. But in fact the generated scripts are created from Template::Tiny templates, and again, they can be overridden on a user or system basis. Have a look at the add-step man page for the details. Or just start with what's there: edit the generated scripts to deploy and revert your changes. Go crazy. The deploy script looks like this:

myproj> less sql/deploy/user_roles.sql 
-- Deploy user_roles

BEGIN;

-- XXX Add DDLs here.

COMMIT;

Next up, deployment. I think that will require that the plan interface be written, first. I'll be getting on that tomorrow.

A quick update on Sqitch. I started implementation about a couple of weeks ago. It’s coming a long a bit more slowly than I'd like, given that I need to give a presentation on it soon. But I did things a little differently than I usually do with project like this: I wrote documentation first. In addition to the basic docs I posted a couple weeks back, I’ve written a tutorial. I put quite a lot of time into it, studying the Git interface as I did so, to try to develop useful workflows. The nice thing about this it that it will not only serve as the foundation for my presentation (PHEW! Half the work done already!), but it also serves as a design specification.

So I've been diligently plugging away on it, and have uploaded a couple of trial releases to CPAN. So far, we have decent support for:

• sqitch help and sqitch help command. The latter only works for the implemented commands, of course.
• sqitch config, which is a near perfect duplication of git-config, thanks to the very useful Config::GitLike. It supports a local, project-specific config file, a user config file, and a system config file.
• sqitch init, which creates a new project by creating directories for the deploy, revert, and test scripts, and writes a project-specific config file. This file has options you specify in the call to sqitch (such as the database engine you plan to use), and all unmodified settings or settings set in user or system configuration are written out as comments.

So yeah, not a ton so far, but the foundations for how it all goes together are there, so it should take less time to develop other commands, all things being equal.

Next up:

• sqitch add-step, which will create deploy and revert scripts for a new step, based on simple templates.
• sqitch deploy, which is the big one. Initial support will be there for PostgreSQL and SQLite (and perhaps MySQL).

Interested in helping out?

• I'm going to need a parser for the plan file pretty soon. The interface will need an iterator to move back and forth in the file, as well as a way to write to the file, add steps to it, etc. The grammar is pretty simple, so anyone familiar with parsers and iterators could probably knock something out pretty quickly.

• The interface for testing needs some thinking through. I had been thinking that it could be something as simple as just diffing the output of a script file against an expected output file, at least to start. One could even use pgTAP or MyTAP in such scripts, although it might be a pain to get the output exactly right for varying environments. But maybe that doesn't matter for deployment, so much? Because it tends to be to a more controlled environment than your typical open-source library test suite, I mean.

Got something to add? Fork it!

A few years back, we spent six weeks in Rouen, France. I blogged about it a little and took a lot of photos. It was a terrific experience, and we always knew we wanted to do it again. So you can imagine our delight at being asked to swap houses with the same folks again. Last time it was late November to early January: cold, cold, cold. This time, though, it will be the middle of summer. We leave mid-June and will return in early August. Seven weeks of awesomeness. Can't wait.

Last weekend, I was talking to a friend who works for a moderately-sized regional newspaper. We were talking about distribution. The guys upstairs, he told me, feel very comfortable, complacent even, with their position. They’re not worried about their future because, as they say, “Hey, we’re not the New York Times, we’re not going to have those kind of distribution problems, because we cover regional news, and there’s still a need for good regional coverage.” I guess they think that people in their region don’t read local news on the internet or their mobile devices?

This is so incredibly wrong-headed it boggles the mind. This complacency will kill a perfectly good regional news source, all because the folks in charge are so blinkered that they cannot see that distribution is about to undergo a disruption not seen since…hell, I don’t know when.

I am a great believer in quality. There will always be a place for good writing, good editing, and solid reportage. We need organizations to employ journalists to investigate goings-on and report on them. There needs to be fact-checking, copy editing, compelling photography and illustration, and most of all, people who are willing to dig, to dig up the truth and tell stories that inform us, challenge us, and yes, entertain us.

Now, tell me, where in this characterization do you see any mention of forests of paper, barrels of inks, and warehouses of printing presses?

The printed press has struggled with the change to internet distribution over the last 15 years with very good reason: It’s difficult to make money. It didn’t help that so many of them gave away their content. But that change is nothing compared to the revolution that is the tablet computer, and especially the new iPad. This is a device you can take anywhere, and unlike your phone, is a pleasure to read. It’s as easy to take with you as a newspaper or magazine, but offers so much more. It’s here to stay. And it’s going to kill the printing press.1

Some disagree. An iPad does not offer the same pleasures as a newspaper: the texture of the pages, the scanability of the front page, the smell of the ink and the smudges it leaves on your fingers. No, there is nothing like a Sunday Times, a bagel, and a cup of coffee to laze away the morning. And when you’re done with the paper, the way it’s strewn about, the poorly refolded pages and crumpled edges of the most interesting sections offer satisfying remains of the experience. And then you recycle it.

Try that with your iPad. Don’t want to get cream cheese on it, or spill your coffee. Its scent does not bring back the memory of lazy Sundays, it doesn’t smudge your fingers, it doesn’t get crumpled or leave behind any of the detritus indicating a satisfying read. Just a smudged up screen, which won’t be nostalgic to anyone.

At the same time, you can’t perform a full text search of your newspaper. You can’t go back and read the article from last week because the recycling has been picked up. You can’t zoom in to a newspaper photo to look at things in greater detail. You can’t make text larger to relieve your aging eyes, or dig deeper to find out the story behind the photograph on the front page.

Look here, periodicals companies. This is important. It’s time realized that you are not in the printing business. You are in either the content business or the advertising business. You either sell your content to consumers in ways that are easy or enjoyable for them to access, or get your ads in front of as many eyeballs as possible (or both). The way to do that is not to run printing presses. Nor is it to squeeze the disadvantages of the printed page on devices. The way to do it is to provide the best experience possible. Decide what methods of distribution you want to use—print, web, tablet—and take advantage of the strengths and weaknesses of each to make things people want.

So yeah, keep printing, for now, to satisfy the aging population that needs it. Make the experience the best you can within the constraints of the printed page. But don’t force those same constraints into another distribution channel. You don’t try to make the printed page look and feel like a scrolling screen, do you? Nor should you make the iPad experience feel like the printed page. No, I can’t smell the ink in your iPad app, but if you took proper advantage of the device, tried to work within the confines of its limitations while exploiting is unique strengths, you could provide a compelling, unique experience.

Because if you don’t, someone else will. The iPad in particular represents a gaping opportunity for disruption of your business, mainly because you fail to recognize that you are in the content business, not the distribution business. And upstart companies will start delivering well researched, edited, and fact-checked stories in a compelling format, with new approaches to interaction and engagement, in a way that people want. And they will be extremely successful. And profitable.

My recommendations for sane time zone management in PostgreSQL:

• Set timezone = 'UTC' in postgresq.conf. This makes UTC the default time zone for all connections.
• Use timestamp with time zone (aka timestamptz) and time with time zone (aka timetz). They store values as UTC, but convert them on selection to whatever your time zone setting is.
• Avoid timestamp without time zone (aka timestamp) and time without time zone (aka time). These columns do not know the time zone of a value, so different apps can insert values in different zones no one would ever know.
• Always specify a time zone when inserting into a timestamptz or timetz column. Unless the zone is UTC. But even then, append a "Z" to your value: it's more explicit, and will keep you sane.
• If you need to get timestamptz or timetz values in a zone other than UTC, use the AT TIME ZONE expression in your query. But be aware that the returned value will be a timestamp or time value, with no more time zone. Good for reporting and queries, bad for storage.
• If your app always needs data in some other time zone, have it SET timezone = 'UTC' on connection. All values then retrieved from the database will be in the configured time zone. The app should still include the time zone in values sent to the database.

The one exception to the rule preferring timestamptz and timetz is a special case: partitioning. When partitioning data on timestamps, you must not use timestamptz. Why? Because almost no expression involving timestamptz comparison is immutable. Use one in a WHERE clause, and constraint exclusion may well be ignored and all partitions scanned. This is usually something you want to avoid.

So in this one case and only in this one case, use a timestamp without time zone column, but always insert data in UTC. This will keep things consistent with the timestamptz columns you have everywhere else in your database. Unless your app changes the value of the timestamp GUC when it connects, it can just assume that everything is always UTC, and should always send updates as UTC.

After the Fireballing week before last, I put aside a bit of time to rejigger things. This blog now has a new home.

• Moved all content to a Linode virtual server. No more serving from my crappy old desktop system behind my Comcast connection. The VPS is kind of skimpy on the RAM, but seems fine for my basic needs.
• Still using Blosxom, but all content is statically-generated.
• Switched to Nginx. It's fast. Especially for a 100% static site.
• Search is gone. No one used it, anyway. That's what Duck Duck Go is for.
• Comments are gone, sort of. I removed the plugin for adding comments to posts. Existing comments are still shown, though.
• Added Disqus commenting. The upshot is that, for the first time in years, one can comment on any post at any time. No more closing comments after two weeks.
• Got rid of the "sociable" junk. No one needs hand-holding for sharing, and very few sharing sites are relevant anymore, anyway.

Oh, and I also moved strongrrl.com, kineticode.com, and my PGXN mirror to the Linode host. They're all static, too, so everything is nice and peppy.

So that's step 1. It's enough that I can get back to posting stuff and, on the off chance that I get Fireballed again, I think things will hold up (a simple ab test shows pretty good throughput at about 100 requests/second at a concurrency of 100). Over the next few months, I have other plans:

• Throw up a new kineticode.com. The company has actually shut down, so I need to put up a new page to direct interested parties elsewhere.
• Redesign Just a Theory. This design was okay in 2004, but never very forward-looking. I want to vastly simplify things. Just down to the bare essentials, really. Be prepared for more junk to disappear.
• Move to a new blog engine. Blosxom is okay, but finicky. There are a lot of steps to publishing a post, most of them involving SCP and SSH. I just want to write to a directory to do stuff, and support drafts and whatnot.

That last task is the one I'm least likely to find a lot of time to work on, though, as I am already overcommitted to numerous other things, and thinking of new stuff all the time. But I'd really like to make things much nicer for myself, so we'll see.

Back in January, I wrote three posts outlinining some ideas I had about a straight-forward, sane way of managing SQL change managment. The idea revolved around specifying scripts to deploy and revert in a plan file, and generating that plan file from VCS history. I still feel pretty good about the ideas there, and work has agreed to let me write it and open-source it. Here is the first step making it happen. I call it “Sqitch.”

Why “Sqitch”? Think of it as SQL changes with Git stuck in the middle. Of course I expect to support VCSs other than Git (probably Subversion and Mercurial, though I am not sure yet), but since Git is what I now have the most familiarity with, I thought it kind of fun to kind of reference a VCS in the name, if only obliquely.

This week, I started work on it. My first task is to outline a draft for the interface. Sqitch will be a command-line tool, primarily. The remainder of this post contains the documentation for the draft interface. Thoughts and feedback would be greatly appreciated, especially if you think I've overlooked anything! I do want to keep features pretty minimal for now, though, to build up a solid core to be built on later. But other than that, your criticism is greatly desired.

Next up, I will probably write a tutorial, just so I can make my way through some real-life(ish) examples and notice if I missed anything else. Besides, I'm going to need the tutorial myself! Watch for that next week.

Thanks!

Name

Sqitch - VCS-powered SQL change management

Synopsis

sqitch [<options>] <command> [<command-options>] [<args>]

Description

Sqitch is a VCS-aware SQL change management application. What makes it different from your typical migration-style approaches? A few things:

No opinions

Sqitch is not integrated with any framework, ORM, or platform. Rather, it is a standalone change management system with no opinions on your database or development choices.

Native scripting

Changes are implemented as scripts native to your selected database engine. Writing a PostgreSQL application? Write SQL scripts for psql. Writing a MySQL-backed app? Write SQL scripts for mysql.

VCS integration

Sqitch likes to use your VCS history to determine in what order to execute changes. No need to keep track of execution order, your VCS already tracks information sufficient for Sqitch to figure it out for you.

Dependency resolution

Deployment steps can declare dependencies on other deployment steps. This ensures proper order of execution, even when you've committed changes to your VCS out-of-order.

No numbering

Change deployment is managed either by maintaining a plan file or, more usefully, your VCS history. As such, there is no need to number your changes, although you can if you want. Sqitch does not care what you name your changes.

Packaging

Using your VCS history for deployment but need to ship a tarball or RPM? Easy, just have Sqitch read your VCS history and write out a plan file with your change scripts. Once deployed, Sqitch can use the plan file to deploy the changes in the proper order.

Reduced Duplication

If you're using a VCS to track your changes, you don't have to duplicate entire change scripts for simple changes. As long as the changes are idempotent, you can change your code directly, and Sqitch will know it needs to be updated.

Terminology

step

A named unit of change. A step name must be used in the file names of its corresponding deployment and a reversion scripts. It may also be used in a test script file name.

tag

A known deployment state with a list one or more steps that define the tag. A tag also implies that steps from previous tags in the plan have been applied. Think of it is a version number or VCS revision. A given point in the plan may have one or more tags.

state

The current state of the database. This is represented by the most recent tag or tags deployed. If the state of the database is the same as the most recent tag, then it is considered "up-to-date".

plan

A list of one or more tags and associated steps that define the order of deployment execution. Sqitch reads the plan to determine what steps to execute to change the database from one state to another. The plan may be represented by a "Plan File" or by VCS history.

deploy

The act of deploying database changes to reach a tagged deployment point. Sqitch reads the plan, checks the current state of the database, and applies all the steps necessary to change the state to the specified tag.

revert

The act of reverting database changes to reach an earlier tagged deployment point. Sqitch checks the current state of the database, reads the plan, and applies reversion scripts for all steps to return the state to an earlier tag.

Options

-p --plan-file  FILE    Path to a deployment plan file.
-e --engine     ENGINE  Database engine.
-c --client     PATH    Path to the engine command-line client.
-d --db-name    NAME    Database name.
-u --username   USER    Database user name.
-h --host       HOST    Database server host name.
-n --port       PORT    Database server port number.
   --sql-dir    DIR     Path to directory with deploy and revert scripts.
   --deploy-dir DIR     Path to directory with SQL deployment scripts.
   --revert-dir DIR     Path to directory with SQL reversion scripts.
   --test-dir   DIR     Path to directory with SQL test scripts.
   --extension  EXT     SQL script file name extension.
   --dry-run            Execute command without making any changes.
-v --verbose            Increment verbosity.
-V --version            Print the version number and exit.
-H --help               Print a usage statement and exit.
-M --man                Print the complete documentation and exit.

Options Details

-p

--plan-file

sqitch --plan-file plan.conf
sqitch -p sql/deploy.conf

Path to the deployment plan file. Defaults to ./sqitch.plan. If this file is not present, Sqitch will attempt to read from VCS files. If no supported VCS system is in place, an exception will be thrown. See "Plan File" for a description of its structure.

-e

--engine

sqitch --engine pg
sqitch -e sqlite

The database engine to use. Supported engines include:

• pg - PostgreSQL

• mysql - MySQL

• sqlite - SQLite

-c

--client

sqitch --client /usr/local/pgsql/bin/psql
sqitch -c /usr/bin/sqlite3

Path to the command-line client for the database engine. Defaults to a client in the current path named appropriately for the specified engine.

-d

--db-name

Name of the database. For some engines, such as PostgreSQL and MySQL, the database must already exist. For others, such as SQLite, the database will be automatically created on first connect.

-u

--user

--username

User name to use when connecting to the database. Does not apply to all engines.

-h

--host

Host name to use when connecting to the database. Does not apply to all engines.

-n

--port

Port number to connect to. Does not apply to all engines.

--sql-dir

sqitch --sql-dir migrations/

Path to directory containing deployment, reversion, and test SQL scripts. It should contain subdirectories named deploy, revert, and (optionally) test. These may be overridden by --deploy-dir, --revert-dir, and --test-dir. Defaults to ./sql.

--deploy-dir

sqitch --deploy-dir db/up

Path to a directory containing SQL deployment scripts. Overrides the value implied by --sql-dir.

--revert-dir

sqitch --revert-dir db/up

Path to a directory containing SQL reversion scripts. Overrides the value implied by --sql-dir.

--test-dir

sqitch --test-dir db/t

Path to a directory containing SQL test scripts. Overrides the value implied by --sql-dir.

--extension

sqitch --extension ddl

The file name extension on deployment, reversion, and test SQL scripts. Defaults to sql.

--dry-run

sqitch --dry-run

Execute the Sqitch command without making any actual changes. This allows you to see what Sqitch would actually do, without doing it. Implies a verbosity level of 1; add extra --verboses for greater verbosity.

-v

--verbose

sqitch --verbose -v

A value between 0 and 3 specifying how verbose Sqitch should be. The default is 0, meaning that Sqitch will be silent. A value of 1 causes Sqitch to output some information about what it's doing, while 2 and 3 each cause greater verbosity.

-H

--help

sqitch --help
sqitch -H

Outputs a brief description of the options supported by sqitch and exits.

-M

--man

sqitch --man
sqitch -M

Outputs this documentation and exits.

-V

--version

sqitch --version
sqitch -V

Outputs the program name and version and exits.

Sqitch Commands

init

Initialize the database and create deployment script directories if they do not already exist.

status

Output information about the current status of the deployment, including a list of tags, deployments, and dates in chronological order. If any deploy scripts are not currently deployed, they will be listed separately.

check

Sanity check the deployment scripts. Checks include:

• Make sure all deployment scripts have complementary reversion scripts.

• Make sure no deployment script appears more than once in the plan file.

deploy

Deploy changes. Configuration properties may be specified under the [deploy] section of the configuration file, or via sqitch config:

sqitch config deploy.$property $value

Options and configuration properties:

--to

Tag to deploy up to. Defaults to the latest tag or to the VCS HEAD commit. Property name: deploy.to.

revert

Revert changes. Configuration properties may be specified under the [revert] section of the configuration file, or via sqitch config:

sqitch config revert.$property $value

Options and configuration properties:

--to

Tag to revert to. Defaults to reverting all changes. Property name: revert.to.

test

Test changes. All SQL scripts in --test-dir will be run. [XXX Not sure whether to have subdirectories for tests and expected output and to diff them, or to use some other approach.]

config

Set configuration options. By default, the options will be written to the local configuration file, sqitch.ini. Options:

--get

Get the value for a given key. Returns error code 1.

--unset

Remove the line matching the key from config file.

--list

List all variables set in config file.

--global

For writing options: write to global ~/.sqitch/config.ini file rather than the local sqitch.ini.

For reading options: read only from global ~/.sqitch/config.ini rather than from all available files.

--system

For writing options: write to system-wide $prefix/etc/sqitch.ini file rather than the local sqitch.ini.

For reading options: read only from system-wide $prefix/etc/sqitch.ini rather than from all available files.

--config-file

Use the given config file.

package

Package up all deployment and reversion scripts and write out a plan file. Configuration properties may be specified under the [package] section of the configuration file, or via sqitch config package.$property $value command. Options and configuration properties:

--from

Tag to start the plan from. All tags and steps prior to that tag will not be included in the plan, and their change scripts Will be omitted from the package directory. Useful if you've rejiggered your deployment steps to start from a point later in your VCS history than the beginning of time. Property name: package.from.

--to

Tag with which to end the plan. No steps or tags after that tag will be included in the plan, and their change scripts will be omitted from the package directory. Property name: package.to.

--tags-only

Write the plan file with deployment targets listed under VCS tags, rather than individual commits. Property name: package.tags_only.

--destdir

Specify a destination directory. The plan file and deploy, revert, and test directories will be written to it. Defaults to "package". Property name: package.destdir.

Configuration

Sqitch configuration information is stored in standard INI files. The # and ; characters begin comments to the end of line, blank lines are ignored.

The file consists of sections and properties. A section begins with the name of the section in square brackets and continues until the next section begins. Section names are not case sensitive. Only alphanumeric characters, - and . are allowed in section names. Each property must belong to some section, which means that there must be a section header before the first setting of a property.

All the other lines (and the remainder of the line after the section header) are recognized as setting properties, in the form name = value. Leading and trailing whitespace in a property value is discarded. Internal whitespace within a property value is retained verbatim.

All sections are named for commands except for one, named "core", which contains core configuration properties.

Here's an example of a configuration file that might be useful checked into a VCS for a project that deploys to PostgreSQL and stores its deployment scripts with the extension ddl under the migrations directory. It also wants packages to be created in the directory _build/sql, and to deploy starting with the "gamma" tag:

[core]
    engine    = pg
    db        = widgetopolis
    sql_dir   = migrations
    extension = ddl

[revert]
    to        = gamma

[package]
    from      = gamma
    tags_only = yes
    dest_dir  = _build/sql

Core Properties

This is the list of core variables, which much appear under the [core] section. See the documentation for individual commands for their configuration options.

plan_file

The plan file to use. Defaults to sqitch.ini or, if that does not exist, uses the VCS history, if available.

engine

The database engine to use. Supported engines include:

• pg - PostgreSQL

• mysql - MySQL

• sqlite - SQLite

client

Path to the command-line client for the database engine. Defaults to a client in the current path named appropriately for the specified engine.

db_name

Name of the database.

username

User name to use when connecting to the database. Does not apply to all engines.

password

Password to use when connecting to the database. Does not apply to all engines.

host

Host name to use when connecting to the database. Does not apply to all engines.

port

Port number to connect to. Does not apply to all engines.

sql_dir

Path to directory containing deployment, reversion, and test SQL scripts. It should contain subdirectories named deploy, revert, and (optionally) test. These may be overridden by deploy_dir, revert_dir, and test_dir. Defaults to ./sql.

deploy_dir

Path to a directory containing SQL deployment scripts. Overrides the value implied by sql_dir.

revert_dir

Path to a directory containing SQL reversion scripts. Overrides the value implied by sql_dir.

test_dir

Path to a directory containing SQL test scripts. Overrides the value implied by sql_dir.

extension

The file name extension on deployment, reversion, and test SQL scripts. Defaults to sql.

Plan File

A plan file describes the deployment tags and scripts to be run against a database. In general, if you use a VCS, you probably won't need a plan file, since your VCS history should be able to provide all the information necessary to derive a deployment plan. However, if you really do need to maintain a plan file by hand, or just want to better understand the file as output by the package command, read on.

Format

The contents of the plan file are plain text encoded as UTF-8. It is divided up into sections that denote deployment states. Each state has a bracketed, space-delimited list of one or more tags to identify it, followed by any number of deployment steps. Here's an example of a plan file with a single state and a single step:

[alpha]
users_table

The state has one tag, named "alpha", and one step, named "users_table". A state may of course have many steps. Here's an expansion:

[root alpha]
users_table
insert_user
update_user
delete_user

This state has two tags, "root" and "alpha", and four steps, "users_table", "insert_user", "update_user", and "delete_user".

Most plans will have multiple states. Here's a longer example with three states:

[root alpha]
users_table
insert_user
update_user
delete_user

[beta]
widgets_table
list_widgets

[gamma]
ftw

Using this plan, to deploy to the "beta" tag, the "root"/"alpha" state steps must be deployed, as must the "beta" steps. To then deploy to the "gamma" tag, the "ftw" step must be deployed. If you then choose to revert to the "alpha" tag, then the "gamma" step ("ftw") and all of the "beta" steps will be reverted in reverse order.

Using this model, steps cannot be repeated between states. One can repeat them, however, if the contents for a file in a given tag can be retrieved from a VCS. An example:

[alpha]
users_table

[beta]
add_widget
widgets_table

[gamma]
add_user

[44ba615b7813531f0acb6810cbf679791fe57bf2]
widgets_created_at

[HEAD epsilon master]
add_widget

This example is derived from a Git log history. Note that the "add_widget" step is repeated under the state tagged "beta" and under the last state. Sqitch will notice the repetition when it parses this file, and then, if it is applying all changes, will fetch the version of the file as of the "beta" tag and apply it at that step, and then, when it gets to the last tag, retrieve the deployment file as of its tags and apply it. This works in reverse, as well, as long as the changes in this file are always idempotent.

Grammar

Here is the EBNF Grammar for the plan file:

plan-file   = { <state> | <empty-line> | <comment> }* ;

state       = <tags> <steps> ;

tags        = "[" <taglist> "]" <line-ending> ;
taglist     = <name> | <name> <white-space> <taglist> ;

steps       = { <step> | <empty-line> | <line-ending> }* ;
step        = <name> <line-ending> ;

empty-line  = [ <white-space> ] <line-ending> ;
line-ending = [ <comment> ] <EOL> ;
comment     = [ <white-space> ] "#" [ <string> ] ;

name        = ? non-white space characters ? ;
white-space = ? white space characters ? ;
string      = ? non-EOL characters ? ;

See Also

The original design for Sqitch was sketched out in a number of blog posts:

• Simple SQL Change Management

• VCS-Enabled SQL Change Management

• SQL Change Management Sans Duplication

Other tools that do database change management include:

Rails migrations

Numbered migrations for Ruby on Rails.

Module::Build::DB

Numbered changes in pure SQL, integrated with Perl's Module::Build build system. Does not support reversion.

DBIx::Migration

Numbered migrations in pure SQL.

Versioning

PostgreSQL-specific dependency-tracking solution by depesz.

Author

David E. Wheeler <david@justatheory.com>

License

Copyright (c) 2012 iovation Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

• Set up a simple Blosxom-powered blog on the advice of a friend who wrote it
• Never bother to make it anything other than a CGI script that crawls your file system on every request
• Add a bunch of plugins for various doodads and useless doohickies, also executing a bunch of crap on every request
• Put it on your eight year old Dell Dimension running Ubuntu Linux
• In your basement
• On your residential Comcast connection
• Let many years go by, giving it very little attention
• Write something interesting
• Laugh about your complete inability to connect to the box from work while Daring Fireball melts your connection

I have more to say on the topic of iPad magazines, but I'm using the meager tuits I have to first make some infrastructural changes.

Hey look everybody, I wrote today's Perl Advent Calendar post, Less Tedium, More Transactions. Go read it!

I've started using TestFlight to release DesignScene betas to testers. The documentation is thin, so I had to futz a bit, but fortunately it's a pretty simple app, so once I figured out that I just needed to stick to one "Team", I was off and running. And let me tell you, TestFlight is a far easier way to distribute betas than the convoluted methods suggested by Apple. Much more beta user-friendly.

For us developers, the TestFlight SDK is particularly handy. Add it to your TestFlight-distributed project and get crash reports and remote logging, ask your testers for feedback, and other cool stuff. I've only just started using it, but the immediate diagnostic feedback has already proved invaluable.

Getting the TestFlight SDK to work is dead simple, but it's not supported in App Store distributions. So I wanted to set things up so that it would always be included in beta releases and never in production releases. Getting to that point took a couple of days of futzing, as it's not explicitly supposed by Xcode's UI. The solution I came up with, thanks to this StackOverflow post, is to:

• Add a "Beta" configuration to complement the default "Release" and "Debug" configurations
• Add a preprocessor macro to allow conditional use of the TestFlight SDK
• Use the EXCLUDED_SOURCE_FILE_NAMES setting to exclude the TestFlight library from "Release" builds

That last step makes me a bit nervous, but EXCLUDED_SOURCE_FILE_NAMES, while undocumented, seems to be reasonably well known. At any rate, I could find no better way to tie the inclusion of a library to a specific configuration, so I'm going with it. Better solutions welcome.

At any rate, here's the step-by-step for Xcode 4.2:

• Download the TestFlight SDK and unpack it.
• Drag it into your project. Make sure that "Copy items into destination group's folder" is checked, as is "Create groups for any added folders". Include it in all relevant targets.

• Create a "Beta" configuration:

  • Click on the app name in the navigator, then on the project name and then the info tab.
  • Under "Configurations", click the plus sign and select "Duplicate "Release" Configuration"
  • Type "Beta" to name the new configuration.

  You should end up with something like this:

  

• Create configuration marcos:

  

  • Still in the project settings, go to the "Build Settings" tab.
  • Search for "preprocessor macros".
  • Double-click the value section next to the "Preprocessor Macros" label, hit the + button, and enter CONFIGURATION_$(CONFIGURATION).

  You should end up with a window like the above. Once you close it, you should see the macros names for each individual configuration, shown here:

  

• Add the EXCLUDED_SOURCE_FILE_NAMES build setting.

  • Still in the "Build Settings" tab, click the "Add Build Setting" button in the lower-left corner and select "Add User-Defined Setting".
  • Input EXCLUDED_SOURCE_FILE_NAMES as the name of the setting.
  • Open the reveal triangle next to the setting name.
  • Double-click to the right of "Release".
  • Enter *libTestFlight.a as the value.

  You should end up with the value *libTestFlight.a only for the "Release" configuration, as shown here:

  

• Go ahead and use the TestFlight SDK:

  • In your app delegate, add #include "testFlight.h"

  • In -application:didFinishLaunchingWithOptions:, just before returning, add these lines:

    #ifdef CONFIGURATION_Beta
        [TestFlight takeOff:@"Insert your Team Token here"];
    #endif

Now, when you build or archive with the "Beta" target, the TestFlight SDK will be included and log sessions. But when you build with the "Release" target, TestFlight will neither be bundled or referenced in the app. You can include it anywhere, though, and use any of its features, as long as you do so only within a #ifdef CONFIGURATION_Beta block. Check out the complete SDK docs for details. Then, get your beta on!