On Thu, Aug 30, 2018 at 11:10 AM Roger Binns <rog...@rogerbinns.com> wrote: > > 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.
One place I worked for had a proprietary ORM style interface that was intended to bridge the gap between an older legacy database and SQLite. It's upgrade process involved an in memory data structure that listed each upgrade step (add a column, move data, drop a column, create a table, etc) then for each step, read each row of data, write a custom insert statement, prepare, execute, finalize, discard, lather rinse repeat. It was very slow. Very very slow. I don't think it is possible to qualify it with sufficient verys to convey just how slow it was. I replaced it with a straight forward implementation that did a direct SQL statement migration from the old schema to the new schema. Begin a transaction, one statement per table, commit the transaction. We wound up going from as much as 48 hours to migrate a 10 GB or so DB to about 5 to 15 minutes (it's been a while, I don't remember exactly). If you have a simple schema and/or a small data set, the ORM migration approach might work well for you. If you have a complex schema and especially if you have a large data set, I'd encourage you to look into alternatives to an ORM approach. I don't think all ORMs would necessarily be as slow as the one I worked with was, but it was just the wrong tool for the job in that particular case. -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
