On Aug 1, 2018, at 7:34 AM, Simon White <simonwh...@dciphercomputing.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users