Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare two SQLite databases on Linux

Tags:

linux

sql

sqlite

Using Linux, I want to compare two SQLite databases that have the same schema. There will be just a few differences.

Is there a tool that would output these differences? Preferably output them to the command line, so that I can grep/sed them.

SQLite uses SQL, so a general SQL tool might also do.

like image 310
Nicolas Raoul Avatar asked Jan 19 '10 12:01

Nicolas Raoul


People also ask

How can I compare two SQLite databases?

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.

How check SQLite database in Linux?

If you are using Linux or a Mac, open a terminal window instead a command prompt. Open a command prompt (cmd.exe) and 'cd' to the folder location of the SQL_SAFI. sqlite database file. run the command 'sqlite3' This should open the SQLite shell and present a screen similar to that below.

What is Sqldiff?

The sqldiff binary is a command-line utility program that displays the differences between SQLite databases. The usual output is an SQL script that will transform database1. sqlite (the "source" database) into database2. sqlite (the "destination" database).

How connect to SQLite in Linux?

SQLite is generally not included on Linux systems by default. RazorSQL can create a new SQLite database by going to the Connections -> Add Connection Profile menu option, selecting SQLite as the database type, and then on the next screen, entering the location for the new SQLite database file, etc.


1 Answers

Please have a look at the SQLite Release 3.8.10 which was released on May 7, 2015. This release for the first time contains the sqldiff.exe utility program for computing the differences between two SQLite database files. Most likely this program will also be part of future releases.

The sqldiff.exe command-line line tool should work for all supported operating systems and offers several switches for altering its output behavior. Example usage:

sqldiff [options] database1.sqlite database2.sqlite 

If no options are specified, then the output of sqldiff.exe is SQL statements that will transform database1.sqlite (the "source" database) into database2.sqlite (the "destination" database).

However, there are also certain limitations. For example, the sqldiff.exe utility (at least currently) does not display differences in TRIGGERs, VIEWs, or virtual tables.


Sample command and output

I took a simple key-value store database (db1.sqlite) and made a copy of it (db2.sqlite). I then inserted one key-value pair into db2.sqlite. After that I ran the following command:

sqldiff db1.sqlite db2.sqlite 

and got the following output:

INSERT INTO my_table(rowid,"key",value) VALUES(1,'D:\Test\Test.txt',x'aabbccdd');
UPDATE my_table_size SET counter=1 WHERE rowid=1;

The table my_table_size was automatically updated by a TRIGGER after the key-value pair was inserted to my_table. I then ran sqldiff.exe again, but this time with with db2.sqlite as first argument and db1.sqlite as second argument:

sqldiff db2.sqlite db1.sqlite 

and got the following output:

DELETE FROM my_table WHERE rowid=1;
UPDATE my_table_size SET counter=0 WHERE rowid=1;


sqldiff download links

Since SQLite version 3.10.2 which was released on January 20, 2016, the 32-bit binaries for sqldiff can be directly downloaded from the SQLite Download Page. They can be found in the sqlite tools archives for the corresponding operating systems (see the Precompiled Binaries sections). For example, here are the links to the sqlite tools archives of version 3.36.0:

  • SQLite tools for Linux
  • SQLite tools for OS X
  • SQLite tools for Windows

For SQLite versions prior to version 3.10.2, the SQLite website hosts 32-bit binaries for sqldiff, but does not link to them. Here are the links to sqldiff of version 3.8.10:

  • sqldiff for Linux
  • sqldiff for OS X
  • sqldiff for Windows

If you need 64-bit binaries, then you have to download the raw sources and compile them by yourself. (The file sqldiff.c is located in the tool sub-directory of the archive containing the sources.)

like image 170
honk Avatar answered Sep 17 '22 23:09

honk