9th October 2022, 2 min read

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.

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.