, 2 min read
Renumbering J-Pilot SQLite Data
Original post is here eklausmeier.goip.de/blog/2022/10-09-renumbering-j-pilot-sqlite-data.
The J-Pilot SQLite plugin stores data in SQLite with Id's stemming from J-Pilot and your various Palm devices over time. These Id's are not necessarily consecutive and jump wildly. Below SQL can be used to renumber them. The approach is to move the Id's to a high range, then copy that range to your desired range sorted according your wishes. Finally deleting the initially moved range.
My data had below Id's.
Table | Addr | Datebook | ToDo | Memo | Expense |
---|---|---|---|---|---|
Start | 1 | 1 | 2 | 1 | 7499777 |
End | 12716046 | 16321734 | 15155777 | 13177815 | 7499778 |
#recs | 5553 | 29929 | 89 | 184 | 2 |
1. Addr. Renumbering Address starting at 100k. In case you have data violating foreign key constraints, you have to correct them in J-Pilot or xxd
, or specify:
PRAGMA foreign_keys = false;
Now, move (=update), copy (=insert), and delete.
update Addr set Id=Id+5000000;
insert into Addr
select 10000+row_number() over (order by Category, Company, Lastname, Firstname, Title, Phone1,Phone2,Phone3,Phone4,Phone5,Note,Id) as 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 from Addr;
delete from Addr where Id >= 5000000;
2. Datebook. Renumbering Datebook starting at 200k.
update Datebook set Id=Id+5000000;
insert into Datebook
select 200000+row_number() over (order by Begin,End,Description,Note,Id) as 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 from Datebook;
delete from Datebook where Id >= 5000000;
3. ToDo. Renumbering ToDo starting at 300k.
update ToDo set Id=Id+5000000;
insert into ToDo
select 300000+row_number() over (order by Category, Private, Due, Priority,Description,Note,Id) as Id,
Category, Private,Indefinite,Due,Priority,Complete,Description,
Note,InsertDate,UpdateDate from ToDo;
delete from ToDo where Id >= 5000000;
4. Memo. Renumbering Memo starting at 400k.
update Memo set Id=Id+5000000;
insert into Memo
select 400000+row_number() over (order by Category, Text, Private, Id),
Category, Private, Text, InsertDate, UpdateDate from Memo;
delete from Memo where Id >= 5000000;
5. Expense. Renumbering Expense starting at 500k.
update Expense set Id=Id+5000000;
insert into Expense
select 500000+row_number() over (order by Id),
Category, Date, Type, Payment, Currency, Amount, Vendor, City, Attendees,
Note, InsertDate, UpdateDate from Expense;
delete from Expense where Id >= 5000000;
6. Running as a single script. To run above statements in a single script, put above statements into a file, enclose it with
BEGIN TRANSACTION;
...
END TRANSACTION;
then run
sqlite3 jptables.db ".read jpilot_renum.sql"
It is a good idea to also run
vacuum;
to reduce ("vacuum") file-size of the database file, which temporarily containted twice the volume.