Original post is here: eklausmeier.goip.de
Task at hand: You want to compare two SQLite3 databases. What data has changed? What has changed in the schema?
SQLite3 provides a simple, but neat tool called sqldiff.
1$ sqldiff --help
2Usage: sqldiff [options] DB1 DB2
3Output SQL text that would transform DB1 into DB2.
4Options:
5 --changeset FILE Write a CHANGESET into FILE
6 -L|--lib LIBRARY Load an SQLite extension library
7 --primarykey Use schema-defined PRIMARY KEYs
8 --rbu Output SQL to create/populate RBU table(s)
9 --schema Show only differences in the schema
10 --summary Show only a summary of the differences
11 --table TAB Show only differences in table TAB
12 --transaction Show SQL output inside a transaction
13 --vtab Handle fts3, fts4, fts5 and rtree tables
14See https://sqlite.org/sqldiff.html for detailed explanation.
See sqldiff.exe: Database Difference Utility.
For example, to compare two J-Pilot SQLite3 databases goes like this:
1$ sqldiff jptables.db.2 jptables.db
2INSERT INTO Addr(rowid,Id,Category,Private,showPhone,Lastname,Firstname,Title,Company,PhoneLabel1,PhoneLabel2,PhoneLabel3,PhoneLabel4,PhoneLabel5,Phone1,Phone2,Phone3,Phone4,Phone5,Address,City,State,Zip,Country,Custom1,Custom2,Custom3,Custom4,Note,InsertDate,UpdateDate) VALUES(11167,15601,8,0,0,'Zal...','Ga...','Atlassian support','C...',0,1,2,3,4,'+420 ...',NULL,NULL,NULL,'ga...','R120, T02'||X'0a'
3||'Prague Branch',NULL,NULL,NULL,NULL,'ga...',NULL,NULL,NULL,NULL,'2023-03-30T14:37:21','2023-03-30T14:37:30');
4
5UPDATE Alarms SET UpToDate='2023-03-30T18:15' WHERE rowid=1;
6
7INSERT INTO Datebook(rowid,Id,Private,Timeless,"Begin","End",Alarm,Advance,AdvanceUnit,RepeatType,RepeatForever,RepeatEnd,RepeatFreq,RepeatDay,RepeatDaySu,RepeatDayMo,RepeatDayTu,RepeatDayWe,RepeatDayTh,RepeatDayFr,RepeatDaySa,Exceptions,Exception,Description,Note,InsertDate,UpdateDate) VALUES(60469,230423,0,0,'2023-03-29T09:00','2023-03-29T12:00',0,0,0,0,1,NULL,0,0,0,0,0,0,0,0,0,0,NULL,'CZU10: COOBJKID muß Null sein bei "Merge Party"',NULL,'2023-03-29T13:09:18',NULL);