Is it possible to compare 2 sqlite files and update

I have 2 sqlite files, one is older, another on开发者_开发技巧e 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

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.

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.

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.