1

I have 2 sqlite files, one is older, another one has some new data compare to the first one. Is it possible to compare 2 sqlite files with any single command and do update if there is any difference?

Welcome any comment

Thanks

menjaraz
  • 7,551
  • 4
  • 41
  • 81
arachide
  • 8,006
  • 18
  • 71
  • 134
  • I think what you want is binary diffing. Look at [this answer](http://stackoverflow.com/questions/4580368/how-can-i-diff-2-sqlite-files) – bpercevic Feb 11 '15 at 20:18

3 Answers3

2

There is no single command. But there are some SQLite DB comparision tools. And you can achieve the similar on your own. I see two basic ways.

SQL way. Open the X database, then attach to Y database. Use the SELECT ... FROM X.sqlite_master to get the table list of X database. Walk through the list and execute the query:

SELECT * FROM x.tab
EXCEPT 
SELECT * FROM y.tab
UNION ALL
SELECT * FROM y.tab
EXCEPT
SELECT * FROM x.tab

That will return you the table content difference for each table. Now for each row returned by this query you can perform REPLACE SQL command.

Tool way. Rather funny, but the approach may be used too for manual sync. Use the SQLite.exe tool. Open the X database in it and perform .output and .dump command. Repeat the same for X database. Use the tool like Beyond Compare to compare both outputs.

da-soft
  • 7,670
  • 28
  • 36
  • Thanks for writing the "SQL way" code. But in practice, I guess this will be slow for big tables, because all data will be retrieved from both tables multiple times (at least four times), then compared column by column, using default collation. See http://www.sqlite.org/lang_select.html – Arnaud Bouchez Jan 15 '11 at 13:52
1

There is no direct command for such a feature.

You could use a trigger, then make a separate list containing a "journal" of all modifications (INSERT/UPDATE/DELETE) made to the database, then run it against the old.

But if you don't have any possibility to install such a trigger, you'll have to read all file rows, then test if the new row is the same inside the old table, and make an UPDATE/INSERT/DELETE.

The 2nd solution will be dead slow: you'll have to read all new content, then search for every row content in the old table.

So for a fast solution, I only see three possibilities:

  1. Copy the new file in place of the old file. It will be fast and safe. Then run a VACCUM save space. But the new file will be locked during the copy.
  2. Make a binary diff of the two files. It could be faster in some cases. But the new file will also be locked during the diff.
  3. If you don't want to have the new file locked, make a backup of the new table into a new file. See the SQLite online Backup API page. But if someone write to the new table, the backup will restart from the beginning... so it's not perfect either!

For a fast binary diff, there is a Delphi optimized unit (also FOSS) in our source code repository.

Community
  • 1
  • 1
Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
0

You could count the number of rows to determine whether or not they are different, however, it is non-trivial to decide how to update the "older one". Since you have access to the two files, the easiest method, imo, if you just want to make the old table the same as the new one would be:

1) Count the rows. If old < new then: 2) truncate the old table 3) Select all the rows in the new table and insert them into the old table.

Brandon
  • 668
  • 8
  • 22
  • If you only handle the row count change, you won't handle the row content modification. If any UPDATE statement has been run, you'll loose the changes. So IMHO not a good solution. – Arnaud Bouchez Jan 14 '11 at 06:45