coocood/qbs — pull request #25 by iansmith (2013-08-27 17:30:11+00:00)
Title: Schema changes

coocood: Thanks for your work on Qbs! I'm getting a lot of use out of it.

However, I have a small feature request. Attached, you'll find a Pull Request
that I don't expect you to merge but rather to look at to see where I'm going.
The feature request I need is to support my go/dart based web UI framework and
the book I'm writing about it. I'd love to feature Qbs as my "database portion" and I
have it integrated with my framework already...

In addition to Create/Drop table on a Migration (see the pull request) I am suggesting
this feature: The ability to give an old and new version of a struct and have Qbs'
Migration object modify the table in the DB to handle the new version, given the
old version.

Here's how it would work:
```

//you must make struct that shows the database state before the migration, I'm
//calling it foo_old but the name of this struct should either be ignored or
//perhaps should have a prefix that is the same as the database table name.
//This is the origin state for my first example.
type Foo_old struct {
Id int64
F1 string
F2 int
}

//this is the database structure we *want* to change to (the destination). Again
//the name of the database table should be implied by this name.
type Foo struct {
Id int64
Bar_id int64
Other *Bar
F2 int
}

//This is a table that can be joined to the Foo struct above
type Bar struct {
Id int64
F1 string
}

var m *qbs.Migration

//This call should look at Foo_old and Foo (and now Bar, since it has a join
//relationship with Foo) and determine which columns to DROP and which columns
//to ADD to make Foo change to the new state. It should return an error like
//ChangeCantBeMade if it doesn't understand how to make the change. The last
//parameter, the boolean, indicates if the change is from Foo_old -> Foo or
//from Foo->Foo_old. Note that the table being affected is "foo" in the database
//either way.
m.AlterTable(&Foo_old{}, &Foo{}, true}

//Note that this must work in both directions so the change is reversible! Thus,
//running this code undoes whatever changes were made above. This reversibility
//is critical to the way that real production systems work because they must have
//a way to "roll back" to a previous version. The boolean
m.AlterTable(&Foo_old{}, &Foo, false)
```

Note about sqlite3: I know that sqlite3 doesn't have "DROP COLUMN". I'm ok with
the behavior of this feature on sqlite3 to be drop the table and re-create it
based on the new structure. Since sqlite3 is only used for development, not
production, I think this is ok. If you prefer, perhaps the sqlite3 implementation
could "panic" unless a specific environment variable is set to tell it that this
destructive behavior is ok?