Best practices for forward conversion of database formats?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Best practices for forward conversion of database formats?

Randall Smith
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.  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?

Thanks in advance for whatever you can suggest.

Randall.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

Roger Binns
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (201 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

Simon Slavin-3
In reply to this post by Randall Smith
On 30 Aug 2018, at 5:51pm, Randall Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

Scott Robison-2
In reply to this post by Roger Binns
On Thu, Aug 30, 2018 at 11:10 AM Roger Binns <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

Keith Medcalf
In reply to this post by Roger Binns

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-
>[hidden email]] On Behalf Of Roger Binns
>Sent: Thursday, 30 August, 2018 11:10
>To: [hidden email]
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

Ling, Andy
In reply to this post by Randall Smith
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: Best practices for forward conversion of database formats?

dmp
In reply to this post by Randall Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users