On Aug 1, 2018, at 7:34 AM, Simon White <[email protected]> wrote:
>
> I would like to suggest the addition of the "If not exists" to the Add Column
> feature of SQLite.
I maintain an application that’s been through dozens of schema changes over its
nearly quarter century of life, so let me tell you what works for us: a DB
schema serial number.
Any time the DB schema changes, we bump the schema version number and modify a
small program we include with the software that upgrades the schema.
Each schema change is contained in a single function within this program, most
of which are just a simple CREATE or ALTER TABLE statement. A few are more
complex, moving data around or transforming it.
If you upgrade the software on a machine running DB schema 5 to with software
that requires DB schema 8, there are 3 steps that, if performed in order,
always result in you running DB schema 8. This program simply calls those
three functions in sequence based on the old schema number and the current
schema number.
We’ve so rarely needed to roll back to older schema versions that we’ve done it
by hand. If this happens to you often, you could code an inverse for each
upgrade step that lets you roll back each change.
We started out with a simple integer version number, starting with 1, but once
we started having multiple major versions in the wild with parallel development
on each major version branch, we’d occasionally have to upgrade the DB schema
in an older major version in cases where upgrading to the current major version
wasn’t possible.
That caused us to modify the DB schema version numbering scheme:
800 # first DB schema for software major version 8
801
802
etc.
900 # software version 9
901
etc.
In this system, we can say that schema 802 and 901 do the same thing for the
800 and 900 series, respectively, so that if a system is upgraded from 802 to
902 in a single step, the upgrade program knows to skip the step done in 901,
since it was already done in 802.
That’s as close as we’ve come to ever needing a fully-general tree-structured
DB schema versioning system.
We’ve never come close to defining over a hundred schema versions, but if you
think you will, then it’s easily handled by adding a digit. If you’re defining
over a thousand DB schema versions between major software versions, you
probably don’t understand change control. :)
Whenever we upgrade the software, the installer/package for the target runs a
post-installation script that runs this DB schema upgrading program. (e.g. The
%post script in RPM, InstallFinalize in WIX/MSI, etc.) If the schema upgrading
program runs to completion successfully, it updates the schema version number
and returns a success code, which tells the calling script that it can restart
the software.
Now the neat bit: SQLite already supports doing this with its user_version
feature:
https://sqlite.org/pragma.html#pragma_user_version
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
