Introducing SQLite Diff
Why you need this handy little application

by David Simpson

SQLite Diff is a cross-platform graphical comparison tool for SQLite database files developed by .com Solutions Inc, comparing both the schema and the data between any two SQLite database files. Schema results include comparisons of tables/columns, triggers, indexes and views. Changed SQL code including trigger SQL, index creation SQL, view creation SQL code and changed data records are displayed with colorized highlighting to make it easier to see the differences between the compared files.

Why might you need a SQLite comparison tool?

Revolution's built-in revDB functionality supports the use of SQLite database files within Revolution projects. SQLite can be used as a fast and lightweight database engine useful for storing application preferences or even as the main data store for single-user applications.

Some tasks where you could find SQLite Diff to be helpful include:
•You need to read undocumented (or poorly documented) SQLite database files created by popular applications including:
Photoshop Lightroom, Adobe AIR, Skype, FireFox, Apple Mail, Safari, Aperture, Solaris 10, and Bento by FileMaker.
SQLite Diff is optimized for reverse-engineering complex SQLite schemas, enabling you to see the changes within the SQLite database file as it is modified by an application.

• You might have made undocumented changes to your own SQLite database files and you need to figure out what you changed weeks or months ago.

• Troubleshooting your own application - which previously worked but now has unexpected errors. Did you change your schema or data within the database in such as way as to cause an unexpected program error? You can compare a previous known working SQLite database with the current one to quickly find answers to these questions in a few seconds.

SQLite Schema Comparison

Comparing two SQLite database files is as simple as dragging and dropping the files onto SQLite Diff and pressing the Compare button. The schemas of each file are compared and the results are displayed in a few seconds. Clicking each object (Table, Trigger, View or Index) instantly displays the detailed results for the selected object.

For objects other than tables (i.e. Triggers, Views, or Indexes), SQLite Diff displays the SQL code used to create the object, and displays the changed SQL code as colorized text. In this screenshot of the LayoutName5 trigger SQL code, you can see that the text "WHEN New Layout Name is NULL" was newly added text within the 2nd version of the trigger.

SQLite Data Comparison

SQLite Diff uses the SQLite ROWID value to compare each record within matching tables between each of the database files.

The tables list contains summary info displaying the difference count and number of records found in each source database file.

Clicking on an individual table displays the added/deleted/changed records as a comparison between the first database (DB1) and the second database file (DB2).

SQLite Diff Case Study - Comparing FileMaker Bento SQLite Database Files

SQLite Diff was originally written for the purpose of analyzing Bento SQLite database files. The Bento SQLite schema is not publicly documented, so I had to analyze the files myself in order to perform Bento to FileMaker Pro migrations with FmPro Migrator Developer Edition. As an additional benefit to Revolution developers, Bento forms can now be converted directly into Revolution cards/stacks.

After writing SQLite Diff, it was a rather straightforward process to compare before and after copies of Bento backup files for the purpose of understanding the schema. For instance, one of the first tasks I did was to create a backup of the Bento database, then I added a new Library to Bento named Classes. I created another backup of the Bento SQLite database and then compared them.

Within this screenshot of the data comparison results for the gn_node table, you can see that a new library was created and two join tables were also created along with this new library. There is a primary key column named gnpk and its value is 1096 for this new library. All of the related database objects for this table are referenced with this same gnpk value throughout the database. This is vital info to have, because I use this value to work my way through the rest of the tables in the schema in order to find all of the related fields, field types, forms, columns and form elements.

Scrolling horizontally through the data comparison results for the gn_node table, I can see that the gn_label column contains the user-defined name for the new library, and I use this name as the name for the new table I create within the FileMaker database. This is a lot more meaningful than the gnguid value of Library533FF2FD-1F6 seen in the previous screenshot.

Another table of interest is the gn_field table shown below.

Each field has gn_typeName and gn_label columns so that I know the type of data stored within the field along with the user-defined name for the field. There is also a gn_library column (beyond the edge of this screenshot) containing the value 1096, which matches the gnpk value for the new library. This value tells me that that these fields belong to the new library which was just created.

This is just a sample of the type of work I did to figure out the Bento database schema. There are other tables of interest like gn_form, gn_form_column, gn_decrativeControlInfo, all of which required some work in order to figure out how they were used within the schema. But the basic idea is the same:

1) Create a reference level backup of the database file.
2) Make a small change to the database.
3) Backup the database file again - and use SQLite Diff to find the changes which have taken place.

So to summarize, SQLite Diff can be a valuable tool if you need to perform an "impossible" task, like figuring out an undocumented SQLite database schema. It can also be a useful way to solve embarrassing problems like losing your existing documentation or forgetting to create documentation for ad hoc schema changes you have made to your SQLite database files.

You can get this neat little application through our store, here, for only $25.