, 1 min read
SQLite3 sqldiff command
Original post is here eklausmeier.goip.de/blog/2023/03-30-sqlite3-sqldiff-command.
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
.
$ sqldiff --help
Usage: sqldiff [options] DB1 DB2
Output SQL text that would transform DB1 into DB2.
Options:
--changeset FILE Write a CHANGESET into FILE
-L|--lib LIBRARY Load an SQLite extension library
--primarykey Use schema-defined PRIMARY KEYs
--rbu Output SQL to create/populate RBU table(s)
--schema Show only differences in the schema
--summary Show only a summary of the differences
--table TAB Show only differences in table TAB
--transaction Show SQL output inside a transaction
--vtab Handle fts3, fts4, fts5 and rtree tables
See 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:
$ sqldiff jptables.db.2 jptables.db
INSERT 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'
||'Prague Branch',NULL,NULL,NULL,NULL,'ga...',NULL,NULL,NULL,NULL,'2023-03-30T14:37:21','2023-03-30T14:37:30');
UPDATE Alarms SET UpToDate='2023-03-30T18:15' WHERE rowid=1;
INSERT 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);