how to update sqlite3 schema with data migration

Question

I have a possibly peculiar data migration problem:

I have an existing and populated SQLite3 database.
I do receive a new schema for a (hopefully compatible) database.

Result should be a new database, built according to new schema, containing as much as possible of the old database content.

Given limitations in both SQLite3 ALTER statement and our workflow it is safe to assume:

normal case will be new columns are added to end of table.
added columns (fields) will either have a default or can be left NULL.
rarely some table will be added.
very rarely some table or column may be dropped.
no table/column renaming will happen.
no column reshuffling will happen.

NOTE: if the new schema is not compatible with the old one (i.e.: any of the above assumptions does not hold true) it's accepted to fail badly.

I tried this script (old database is data.sql3 and new schema is data.schema):

mkdir tmp cd tmp #compute old DB schema sqlite3 ../data.sql3 .schema >old_s #purge new schema for any initialization... grep -v ^INSERT ../data.schema >data.schema #... create a dew, empty DB... sqlite3 new.sql3 <data.schema #... and compute a standard schema #(this is done to avoid typing differences) sqlite3 new.sql3 .schema >new_s #iff the schemas are different if ! diff -q old_s new_s then #save old DB mv ../data.sql3 . #dump contents sqlite3 data.sql3 .dump >old_d #expunge all statements needed to recreate DB/Tables #new_d contains only INSERT statements grep -v -f old_s old_d >new_d #add old DB content to new DB sqlite3 new.sql3 <new_d #move new DB in place mv new.sql3 ../data.sql3 fi cd ..

This works to detect changes, but fails to repopulate the new database because .dump does not contain column names and thus insertion fails (missing value).

What I'm looking for is some way to force sqlite3 DB .dump to output INSERT statements containing all field names (normally it relies on position) or, it that's not possible, some way to tell sqlite3 DB <new_d to consider any undefined field as null or default (without failing).

Any other way to achieve the same result (without requiring knowledge of what, exactly, has been modified) would be equally welcome.

I'd say no, there isn't an easy or automatic way to solve this. For every DB version you'll have to set up a migration script and adapt the queried rows from the old data to the new schema. — Murphy
– Murphy, Commented Jun 12, 2016 at 8:49
@Murphy: I vas not clear enough. I am concerned just with database migration. The software using it already uses the full column names (i.e.: no "select * from" or positional queries). Also "position" question is moth anyways because SQLite3 only allows to add columns at end. What I need is to be able to import a dump with less field than required "simply" adding empty fields as required without bailing out. — ZioByte
– ZioByte, Commented Jun 13, 2016 at 7:31
Follow this rabbit hole: github.com/praeclarum/sqlite-net/wiki/AutomaticMigrations — CAD bloke
– CAD bloke, Commented Dec 17, 2017 at 22:32

Murphy · Accepted Answer · 2016-06-13 08:30:24Z

To be able to insert/import dumps with less columns into a table you can provide default values for the new, appended columns, or simply enable them to be set to NULL. The constraint clause is the same for CREATE TABLE and ALTER TABLE:

http://www.sqlite.org/syntax/column-constraint.html

-- newColumn is set to a default value if not provided with INSERT alter table myTable add column newColumn INTEGER NOT NULL default 0; -- newColumn may be NULL, which is the default if not provided with INSERT alter table myTable add column newColumn INTEGER; -- It is also valid to combine NULL and DEFAULT constraints alter table myTable add column newColumn INTEGER default 0;

Note that in order for the INSERT statement to work with the new columns it must provide the column names.

Let me rephrase, then: how do I force SQLite3 to ".dump" including column names? My problem is I want to dump a DB with less columns and import into a different one which has some added column, setting the excess columns to NULL/default as specified by whoever "enhanced" the DB. What I have available is: the old DB and the schema for the new DB. I can safely assume the two schemas differ only due to added tables (ok to leave them empty) or because of added columns (to be filled by default/null values as defined in the schema).
@ZioByte You better rephrase your question and provide the necessary details so we can see what your real problem is: stackoverflow.com/help/how-to-ask

Collectives™ on Stack Overflow

how to update sqlite3 schema with data migration

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related