Or you can store the version in a database table itself. Though I suppose the user_version pragma is kinda like a table: create table version(user_version); and then putting a single integer in there.
All the "good" applications ship with an upgrader from *any* version ever to the current version, as a bunch of stepwise modifications. Each subsequent version merely adds any needed changes from the version available. This allows any version to upgrade to the current. Crappier applications only include the upgrader from the previous interim beta patch release format/version to the current format/version, and you have to hunt all over the place to find every single version of the application to run one after each to upgrade stepwise to the current format/version. Needless to say, such vendors have their "user/administrator experience" tested once, then they are relegated forever to the corner full of steaming excrement and their product and company is never spoken to (or of, except with dire warnings to avoid at all costs) ever again. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Roger Binns >Sent: Thursday, 30 August, 2018 11:10 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Best practices for forward conversion of >database formats? > >On 30/08/18 09:51, Randall Smith wrote: >> is how to convert existing data from one DB format to another, >given some arbitrary set of changes in the database schema in the >interim. > >I use SQLite's user pragma. It starts at zero. > >https://sqlite.org/pragma.html#pragma_user_version > >My code ends up looking like this: > >if user_version==0: > CREATE TABLE IF NOT EXISTS events(key, time, message); > PRAGMA user_version=1; > >if user_version==1: > CREATE INDEX IF NOT EXISTS [events:message] ON events(message); > PRAGMA user_version=2; > >if user_version==2: > ALTER TABLE events ADD COLUMN severity; > PRAGMA user_version=3; > >This ensures that the currently running code will upgrade the schema >as >needed. Ensure the commands are wrapped in a transaction so they >either >completely happen or not. > >I am helped by having low complexity schemas. If yours are large you >could probably generate something like the above. Some ORM style >engines also have schema and data upgrade functionality. > >Roger _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
