Renumbering J-Pilot SQLite Data

Original post is here: eklausmeier.goip.de

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:

1PRAGMA foreign_keys = false;

Now, move (=update), copy (=insert), and delete.

1update Addr set Id=Id+5000000;
2insert into Addr
3	select 10000+row_number() over (order by Category, Company, Lastname, Firstname, Title, Phone1,Phone2,Phone3,Phone4,Phone5,Note,Id) as Id,
4	Category, Private, showPhone, Lastname, Firstname, Title, Company,
5	PhoneLabel1, PhoneLabel2, PhoneLabel3, PhoneLabel4, PhoneLabel5,
6	Phone1, Phone2, Phone3, Phone4, Phone5,
7	Address, City, State, Zip, Country, Custom1, Custom2, Custom3, Custom4,
8	Note, InsertDate, UpdateDate from Addr;
9delete from Addr where Id >= 5000000;

2. Datebook. Renumbering Datebook starting at 200k.

1update Datebook set Id=Id+5000000;
2insert into Datebook
3	select 200000+row_number() over (order by Begin,End,Description,Note,Id) as Id,Private,Timeless,
4	Begin,End,Alarm,Advance,AdvanceUnit,RepeatType,RepeatForever,RepeatEnd,RepeatFreq,
5	RepeatDay,RepeatDaySu,RepeatDayMo,RepeatDayTu,RepeatDayWe,RepeatDayTh,RepeatDayFr,RepeatDaySa,
6	Exceptions, Exception, Description,Note,InsertDate,UpdateDate from Datebook;
7delete from Datebook where Id >= 5000000;

3. ToDo. Renumbering ToDo starting at 300k.

1update ToDo set Id=Id+5000000;
2insert into ToDo
3	select 300000+row_number() over (order by Category, Private, Due, Priority,Description,Note,Id) as Id,
4	Category, Private,Indefinite,Due,Priority,Complete,Description,
5	Note,InsertDate,UpdateDate from ToDo;
6delete from ToDo where Id >= 5000000;

4. Memo. Renumbering Memo starting at 400k.

1update Memo set Id=Id+5000000;
2insert into Memo
3	select 400000+row_number() over (order by Category, Text, Private, Id),
4	Category, Private, Text, InsertDate, UpdateDate from Memo;
5delete from Memo where Id >= 5000000;

5. Expense. Renumbering Expense starting at 500k.

1update Expense set Id=Id+5000000;
2insert into Expense
3	select 500000+row_number() over (order by Id),
4	Category, Date, Type, Payment, Currency, Amount, Vendor, City, Attendees,
5	Note, InsertDate, UpdateDate from Expense;
6delete 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

1BEGIN TRANSACTION;
2
3...
4
5END TRANSACTION;

then run

1sqlite3 jptables.db ".read jpilot_renum.sql"

It is a good idea to also run

1vacuum;

to reduce ("vacuum") file-size of the database file, which temporarily containted twice the volume.