Re: [sqlite] Best practices for forward conversion of database formats?
Randall wrote: ~ ~ > Any application that involves a "persistent" database, i.e., one where the data is > long-lived and expected to exist and evolve over time, sooner or later has the issue > of moving customers from a V1 database to a V2 database. Obviously at least one > technical issue (there are probably others) 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. > Are there accepted or best practices in the industry for handling this in general, > or with SQLite in particular? Can anyone who has implemented this make useful > suggestions? Or are there published resources I am missing? Hello, I should suppose pretense with I'm wholly ignorant to such a process, but even has such may have some wisdom. The only reason I'm replying because I have been working on I guess an ORM, data converter it seems. One, it seems data is best preserved in a standard raw format such as CSV. SQL statements from one database do not translate to another databases well and perhaps of the same database v1 to v2. Two, with changes to schema it seems the simple way to go would be export data in a query fashion, to match the new schema. Leave out columns not needed and a default for new. Three, this in hindsight, but data should be always be in it simplest form as possible, Integer, Char, etc. The most intolerant data is temporal information. For these ALWAYS use a SQL or a common appropriate standard. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best practices for forward conversion of database formats?
> This is not strictly a SQLite question, though in my case it is. > > Any application that involves a "persistent" database, i.e., one where the > data is long-lived and expected to exist and evolve over time, sooner or later > has the issue of moving customers from a V1 database to a V2 database. Several have given examples of holding a schema version number in the database and having your application know how to upgrade from one to the next. I have used similar mechanisms, but one thing I would say. If you have to maintain older versions of your app, make a jump in the schema version when you switch major version of you app. For example, if you have version 1.5 of your app and you move to version 2.0, then you need to make a version 1.6 for some reason and it needs a schema change. If you jumped to say, schema version 100 for app 2.0, it is much easier to add a new schema version for 1.6 that can then be handled by 2.x Also, one system I worked on used SQL script files to make the database changes. e.g. There would be a script that updated from schema 2 to 3. To go with this there would also be a script that downgraded from schema 3 to 2. These scripts where left by the installer, so if you needed to install an earlier version of code, it could find the "downgrade" scripts and run them without the earlier version of code having to know how to downgrade. HTH Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best practices for forward conversion of database formats?
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
Re: [sqlite] Best practices for forward conversion of database formats?
On Thu, Aug 30, 2018 at 11:10 AM Roger Binns 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
Re: [sqlite] Best practices for forward conversion of database formats?
On 30 Aug 2018, at 5:51pm, Randall Smith wrote: > Are there accepted or best practices in the industry for handling this in > general, or with SQLite in particular? Can anyone who has implemented this > make useful suggestions? Or are there published resources I am missing? Roger has provided an example of upgrading databases within SQLite, where your existing database is in SQLite, and you intend to continue working with SQLite. However, a truly paranoid organisation might think SQLite might fail entirely. It would institute a requirement that it be easy to export your data from SQLite in a form readable by other systems. SQLite allows this using its precompiled binary tool. This allows you to turn any SQLite database into a text file containing the SQL commands required to reconstruct that database. The text can be immediately processed by any other program which understands SQL, or it can be processed by any text-processing program to massage the format into an alternative format some other SQL system might prefer. Unless you feel that nothing that understands SQL will survive, this should satisfy such a requirement. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
