Tuesday, August 2, 2011

Fixing sqlite compatibility issues

While testing the separation of database generation and the actual webpage, I had discovered that there was a weird bug in that SQLite cheerily opened the database but then complained that the database was invalid. I thought this was quite weird, and double-checked that the file had copied correctly: same size, correct permissions, file gives the same information (SQLite database, version 3). After a bit of thought, I found the problem:

[mozbuild@dm-dxr01 ~]$ sqlite3 -version
jcranmer@xochiquetazal ~ $ sqlite3 -version
3.7.7 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2

The databases were incompatible. So I did some investigation and I found an easy way to fix this:

jcranmer@xochiquetzal ~ $ sqlite3 -line '.dump' spidermonkey.sqlite > /tmp/statements.sql
[mozbuild@dm-dxr01 ~]$ sqlite3 -init /tmp/statements.sql spidermonkey.sqlite

That worked wonderfully. So if you ever need to fix a problem with SQLite-incompatible versions, that is how you dump a database to sqlite and import it again. While I'm on the topic, this is worth paying attention to:

-rw-r--r-- 1 jcranmer   jcranmer   27394048 Aug  2 17:13 spidermonkey.sqlite
-rw-r--r-- 1 jcranmer   jcranmer   27419572 Aug  2 17:17 statements.sql

The list of SQL statements is only 0.1% larger than the SQLite file. If I look at the older database, it's actually smaller than the database. Food for thought.


Neil Rashbrook said...
This comment has been removed by the author.
Neil Rashbrook said...

I once wanted to move a gigabyte database across ADSL. A zipped text dump crunched the database nicely. (Plus I can generate the text dump without taking the database offline.)

therube said...

Command line doesn't seem to work on Windows?

Had to use:

echo .dump | sqlite3 -line places.sqlite > statements.sql

> The list of SQL statements is only 0.1% larger than the SQLite file.

Believe that is more a "just happened to be the case".

For me 13 MB vs 20 MB. (sqlite3 places.sqlite vacuum, accomplished no size reduction.)