Click here to Skip to main content
16,017,351 members
Articles / Desktop Programming / Windows Forms

SQLite Compare Utility

Rate me:
Please Sign up or sign in to vote.
4.89/5 (68 votes)
21 Feb 2015LGPL35 min read 299.8K   37.5K   132   80
Utility for comparing two SQLite database files for both structure and data

SQLite Compare Screenshot

Introduction

SQLite Compare is a small and fast utility that can compare two SQLite database files for schema and data differences.

It was born out of my frustration of finding a decent DB comparison tool for SQLite database files.

Initially, I tried to sell it as a commercial product. After some time, I realized that this is not the course I'd like to pursue and thus I've decided to release it as open source to the SQLite community.

I hope it will be useful for your purposes.

Using the Code

The SQLite Compare utility is composed of the following C# projects:

  • SQLiteParser project - Responsible to parse SQLite SQL schema text and produce an abstract syntax tree of the entire database schema. This is mandatory if we want to be able to compare the schema differences of two different SQLite databases.
  • DiffControl project - Contains a Windows-Forms DIFF control that I've developed so that I can provide the user with the ability to view differences in SQL schema between two SQLite databases and to modify these schemas. It can be useful in other contexts as well so it merited a project of its own. The control itself was written by me, but the diff engine itself was taken from another project in CodeProject.
  • FastGrid project - During the development of the data differences view, I came to the conclusion that the standard C# data grid view control does not have the ability to work with millions of rows. Even when working in VirtualMode, it stores tons of information in memory which makes it impractical for displaying large amounts of information. For this purpose, I developed a very lightweight control (WindowsForms) that can support this fast enough.
  • Liron.Windows.Forms project - Contains the multi-panel control I've developed. I wrote a separate article about this in CodeProject (search for multi panel). Basically - it provides the ability to author multiple pages in a single design time control (like working with a tab control, but without the tabs...)
  • AutomaticUpdates - This project was responsible to check if there is a newer software version in the web site and download it if necessary. It is now obsolete, but a quick reading of the code can reveal what I did and may be useful to you if you intend to support live updates in your application.
  • UndoRedo - A small library I wrote to support UNDO/REDO operations. May be useful in other applications as well.
  • Be.Windows.Forms.HexBox - A very nice control I've downloaded from the internet which supports Hex editing. I used it for editing BLOB field values.
  • SQLiteTurbo - the main application code. All the various forms are here.
  • Misc libraries I've downloaded from open source authors: Garden Point Parser Generator SDK (used to build the SQLite parser code), Log4NET (logging), Puzzle Syntax Box (displaying change scripts)

Since I don't have much time and the code is reasonably documented, I've decided to skip on the explanations of the utility's internal structure and jump right into the instructions of how to use it.

Basically - in order to compare two SQLite database files, you need to click the "Compare..." button. This will open up the "Comparison Details" dialog in which you'll fill in the paths to both SQLite database files and choose the comparison mode:

  • Compare schema only- For comparing only SQL schema differences. This will perform very quickly but will not compare any data.
  • Compare schema and data - For comparing both SQL schema differences and all table data rows. Depending on the databases - this can be a lengthy operation..

Once the comparison is completed - the utility will display a table with all the differences that were found and allow the user to drill down and show the specific differences:

SQLite Comparison Results

When drilling down to a specific DB object, we'll get the detailed difference. For example:

Table Difference Results

At this point, you can copy differences between the two databases and the utility will automatically update the relevant database schema.

In case you need to compare the data rows of the two tables - you can click the "Compare data" button and you'll get the following tab:

Table Difference Results

This tab provides you with the ability to view the data differences between the rows and to copy differences from one table to the other.

Points of Interest

The Gardens Point Parser Generator project proved invaluable resource when building the SQLite parser. In my opinion, it's one of the best SDKs in C# for building parsers and lexical analyzers.

FastGrid and DiffControl projects were very fun to develop. They proved that it is not too difficult to build even complex seeming controls in a short amount of time and are a testament to the fantastic work done by Microsoft and the open source community. Specifically - the Diff engine algorithm was contributed by another CodeProject member.

The SQLite database libraries and the .NET provider are amazingly versatile and robust pieces of code. The work done by Dr. Richard Hipp (SQLite C libraries) and Robert Simpson (C# SQLite Provider) is simply fantastic!

IMPORTANT NOTE: Due to the way the .net sqlite provider works - comparison will not work correctly for GUID type columns. Note that GUID columns are actually stored as TEXT so there is no benefit for doing this anyway.

Bottom line - use VARCHAR field instead of GUID field if you want comparisons to work correctly.

History

  • 3rd July, 2011: Version 2.5 - Initial version (open source)
  • 5th July, 2011: Fixed the Setup.nsi file to use the correct files in the Release folder
  • 6th July, 2011: Fixed problem with 64 bit systems (changed build configuration to use x86 settings)

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
Software Developer
Israel Israel
My name is Liron Levi and I'm developing software for fun & profit for 15 years already.

Comments and Discussions

 
QuestionSQLiteCompare Pin
Member 163284824-Aug-24 18:22
Member 163284824-Aug-24 18:22 
QuestionCan't Install on Windows 11 Pin
rwmorey11-Dec-23 5:19
rwmorey11-Dec-23 5:19 
AnswerRe: Can't Install on Windows 11 Pin
Springers18-Feb-24 1:47
Springers18-Feb-24 1:47 
QuestionSQLite Compare does not support Schema REGEXP Pin
Member 1512614922-Sep-21 20:06
Member 1512614922-Sep-21 20:06 
QuestionFloat type values are not compared correctly Pin
Member 145175081-Jul-19 6:03
Member 145175081-Jul-19 6:03 
QuestionUnable to see the copied information Pin
davidprince24-Jan-19 1:24
davidprince24-Jan-19 1:24 
SuggestionWonderful tool, I love it Pin
Member 1157919323-Aug-18 10:32
Member 1157919323-Aug-18 10:32 
BugError message during comparison: "Input string was not in a correct format" Pin
Ivan Kochurkin7-Apr-18 2:14
Ivan Kochurkin7-Apr-18 2:14 
PraiseIt works very well for me. Many thanks! Pin
Ngoc Minh Huynh5-Feb-17 22:29
Ngoc Minh Huynh5-Feb-17 22:29 
QuestionGetting Error- The file are not .db or they are encrypted. Pin
Member 1259217319-Jun-16 9:39
Member 1259217319-Jun-16 9:39 
AnswerRe: Getting Error- The file are not .db or they are encrypted. Pin
Member 940275221-Aug-16 7:04
Member 940275221-Aug-16 7:04 
GeneralRe: Getting Error- The file are not .db or they are encrypted. Pin
Member 1222998327-Dec-16 14:20
Member 1222998327-Dec-16 14:20 
GeneralRe: Getting Error- The file are not .db or they are encrypted. Pin
John M. Drescher24-Mar-18 4:32
John M. Drescher24-Mar-18 4:32 
GeneralRe: Getting Error- The file are not .db or they are encrypted. Pin
Member 968360829-Jul-18 2:31
Member 968360829-Jul-18 2:31 
GeneralRe: Getting Error- The file are not .db or they are encrypted. Pin
John M. Drescher29-Jul-18 5:05
John M. Drescher29-Jul-18 5:05 
QuestionMy vote of 5 Pin
B.Seshu Babu3-May-16 21:55
B.Seshu Babu3-May-16 21:55 
QuestionMy vote of 5 Pin
Greg Ellis26-Apr-16 8:54
Greg Ellis26-Apr-16 8:54 
QuestionGreat Tool, diff cell content? Pin
Elessar81-Oct-15 3:35
Elessar81-Oct-15 3:35 
QuestionDebugged "invalid sql string" Pin
Angel Eyes21-Sep-15 22:43
Angel Eyes21-Sep-15 22:43 
AnswerRe: Debugged "invalid sql string" Pin
Win32nipuh26-Mar-17 3:07
professionalWin32nipuh26-Mar-17 3:07 
GeneralRe: Debugged "invalid sql string" Pin
Member 137019531-Mar-18 2:19
Member 137019531-Mar-18 2:19 
NewsSQLite's own sqldiff utility program Pin
khb23-Aug-15 1:34
khb23-Aug-15 1:34 
SuggestionCompare based on other column Pin
Member 1178507022-Jun-15 21:14
Member 1178507022-Jun-15 21:14 
BugDATETIME UPDATE BUG? Pin
Mik-ha-El24-May-15 21:36
Mik-ha-El24-May-15 21:36 
GeneralRe: DATETIME UPDATE BUG? Pin
John M. Drescher4-Oct-17 12:51
John M. Drescher4-Oct-17 12:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.