4th December 2022, 10 min read

SQLite Storage for J-Pilot

I am using J-Pilot since more than 20 years. I am using it on a daily basis. I do no longer use any Palm device. I had owned a Palm III, a Palm V, and a Tungsten, but they all failed at some point in time, and are no longer usable. Therefore, I do no HotSync between PC and Palm device. I use J-Pilot as a stand-alone application. But I do copy my datebook, and address data to my Android devices, see Importing Calendar Data to Google-Calendar and J-Pilot Data on Android Phone: Contacts.

My first Palm device, the Palm III, was a gift from my brother, who bought them at the SAPPHIRE conference in the US. Before using this device, I kept my addresses in a MS-Word file, which I printed out on paper every few weeks, whenever too many changes were written on the previous paper. When I started with the Palm III, I was still using MS Windows. Therefore, I used the original Palm Windows application, which looks pretty similar to J-Pilot, well, no wonder. Starting in 2001 I began using Linux, and J-Pilot was one of my first applications in regular use.

Around ten years ago I proposed to store the J-Pilot data in SQLite, see Possible Enhancements to J-Pilot, instead of using the internal pdb/pc3-format. Two years ago I devised a datamodel for this, see SQL Datamodel For J-Pilot. I implemented a J-Pilot plugin, which stores J-Pilot data in SQLite, see J-Pilot Plugin For SQLite Export. This plugin is working like an export mechanism, i.e., once you press a button, all data from the internal format is stored in SQLite — but J-Pilot itself still uses the internal pdb/pc3-format.

Now I have implemented all the necessary changes within J-Pilot, so that J-Pilot no longer uses the internal pdb/pc3-format, but rather reads and writes all its data from/to SQLite. No pdb/pc3-files are used anymore. Also, the alarm file and the preference file is stored within SQLite. Only jpilot.log, jpilotcss.default, and jpilot.plugins remain as separate files. The rest is stored in jptables.db, a SQLite3 file.

$ ls -l .jpilot
total 20308
-rw-r--r--  1 klm klm    8750 Sep 12 16:20 jpilotcss.default
-rw-------  1 klm klm       0 Mar 27  2021 jpilot.plugins
-rw-------  1 klm klm 7217152 Dec  3 15:23 jptables.db
drwxr-xr-x  2 klm klm    4096 Nov 26 18:33 old/
drwxr-xr-x  2 klm klm    4096 Nov 26 17:16 plugins/

I also fixed the issues mentioned in Crashing J-Pilot. Another null-pointer exceptions in ToDo's was also fixed along the way.

I am productive since 26-Nov-2022, i.e., I am only using the new version based on SQLite.

1. Code changes. Main program jpilot.c now uses getopt() so that options can be merged without given separate dashes for each individual option. For example, you can now start jpilot like so

jpilot -Sdr

I added opions -S for SQLite storage and option -r to suppress permanent saving into preference file. I noticed that J-Pilot constantly writes into jpilot.rc. In many cases this is not necessary, or just superflous. Therefore this additional option -r. When you start jpilot without -S it will behave as before, i.e., everything will be saved in pdb/pc3-files.

The other executables were not changed to use getopt(), as they lose their importance, when data is stored in SQLite. For example, jpilot-dump is no longer required, when data can easily be accessed via SQLite. Same with jpilot-merge. jpilot-dump reads pdb/pc3-files and produces text files. jpilot-merge combines pdb and pc3 into a single pdb file.

Overall the changes in the existing code basis are very few. Essentially they look like this:

if (glob_sqlite) jpsqlite_DatebookSEL(&cel,NULL,1);
else get_days_calendar_events2(&cel, NULL, 2, 2, 1, CATEGORY_ALL, NULL);

All interaction with SQLite is in libsqlite.c.

As proposed in Possible Enhancements to J-Pilot the search routine no longer makes unnecessary malloc() and free() calls, i.e., jp_strstr() now uses strcasestr().

Serious development started in October and finished in November 2022. In November I tried to check each functionality in J-Pilot at least once, so that it still works with SQLite. I made quite intensive use of gdb, using Marc Heisenko's cheat-sheet. The debug-option in J-Pilot, -d, also proved to be very valuable to see which functions are involved.

%%{init: { 'gitGraph':{'mainBranchName':'master'} } }%% gitGraph commit id: "ccb225a" commit id: "6aaded5" branch feature-gtk3 commit id: "9ba0ce5" commit id: "0a8989b" tag: "2.0.1" commit id: "..." commit id: "200d954" type: HIGHLIGHT commit id: "7ec8251" commit id: "73f4179" checkout master commit id: "5170c25" commit id: "8c43c5b"

I started development at Git commit 200d954 in the feature-gtk3 branch, but incorporated the changes contained in the two later commits. Though, they lead to a warning during compilation.

Overview of lines of code.

LOC	words	chars	C file
526	1577	15486	./address.c
4740	13369	196368	./address_gui.c
1082	2825	31590	./alarms.c
633	1800	18635	./calendar.c
1020	3080	37367	./category.c
803	2277	22486	./contact.c
94	545	3599	./cp1250.c
1273	3599	35669	./dat.c
890	3082	27454	./datebook.c
5936	16826	235733	./datebook_gui.c
387	1035	13964	./dialer.c
554	1594	13309	./dialer/jpilot-dial.c
2195	6110	77753	./Expense/expense.c
369	982	12981	./export_gui.c
86	148	2494	./font.c
4873	38304	245635	./icons/icons.c
436	1211	14841	./import_gui.c
477	1278	16768	./install_gui.c
278	814	9608	./install_user.c
244	1017	7150	./japanese.c
76	307	2467	./jp-contact.c
2242	6554	76522	./jpilot.c
1916	5521	71820	./jpilot-dump.c
317	1020	9877	./jpilot-merge.c
282	982	9037	./jpilot-sync.c
859	2483	30071	./jpsqlite/jpsqlite.c
2903	8331	101505	./KeyRing/keyring.c
950	2500	25866	./libplugin.c
2018	6174	86382	./libsqlite.c
215	715	6094	./log.c
386	1079	10269	./memo.c
2049	5748	72251	./memo_gui.c
544	1608	18430	./monthview_gui.c
329	1053	9129	./otherconv.c
367	1086	10509	./password.c
105	332	2875	./pidfile.c
445	1331	13382	./plugins.c
916	2613	26913	./prefs.c
1012	2898	38779	./prefs_gui.c
1340	3507	40224	./print.c
355	975	12070	./print_gui.c
906	4400	37995	./print_headers.c
453	649	32340	./print_logo.c
383	1172	15482	./restore_gui.c
127	319	4695	./russian.c
741	1935	26316	./search_gui.c
3862	12436	128907	./sync.c
118	401	3545	./SyncTime/synctime.c
437	1542	12280	./task.c
520	1446	13885	./todo.c
2769	7564	106979	./todo_gui.c
3838	11945	120301	./utils.c
403	1170	13742	./weekview_gui.c
61079	203269	2229829	total

LOC	words	chars	header file
95	428	3527	./address.h
30	194	1328	./alarms.h
65	287	2633	./calendar.h
205	944	5724	./config.h
30	175	1156	./cp1250.h
142	662	4833	./datebook.h
53	239	2047	./export.h
265	1207	9470	./gettext.h
38	185	1283	./i18n.h
278	814	5882	./icons/appl_menu_icons.h
215	695	4607	./icons/list_mini_icons.h
101	388	2828	./icons/lock_icons.h
28	146	1063	./install_user.h
30	170	1202	./japanese.h
24	156	1110	./jpilot.h
59	269	2073	./jp-pi-contact.h
379	1541	11324	./libplugin.h
53	310	2889	./libsqlite.h
51	258	1935	./log.h
39	190	1492	./memo.h
39	224	1524	./otherconv.h
51	251	1702	./password.h
27	152	1076	./pidfile.h
92	384	2797	./pi-task.h
89	312	2819	./plugins.h
23	143	1011	./prefs_gui.h
187	754	6367	./prefs.h
77	318	2847	./print.h
28	166	1216	./print_headers.h
22	154	1029	./print_logo.h
26	152	1062	./restore.h
29	164	1166	./russian.h
49	225	1890	./stock_buttons.h
59	231	1902	./sync.h
68	253	2359	./todo.h
592	2267	18441	./utils.h
3638	15408	117614	total

libsqlite.c and libsqlite.h are entirely new. libjpsqlite.c is the plugin, which I wrote two years ago, see J-Pilot Plugin For SQLite Export.

2. Limitations. The current implementation with storing all data in SQLite has some limitations:

currently no HotSync with your Palm device
no "Calendar", just "Datebook" — main difference being the first having categories
no contact (i.e., version 2 address), which has 39 fields, while version 1 (a.k.a. address) having 20 fields
no undelete as all insertions, updates and deletions happen directly on the SQLite database

I investigated whether I should also implement "Calendar" and "Contact v2", but saw no compelling reason for me, so left that. If others raise their voice for that being included, I will surely reconsider.

3. File format. SQLite as file format has a number of advantages over the previous internal format stored in pdb/pc3 files. Below points are reproduced from SQLite As An Application File Format:

Better performance: Reading and writing from an SQLite database is often faster than reading and writing individual files from disk.

Reduced complexity.

Portability: The application file is portable across all operating systems, 32-bit and 64-bit and big- and little-endian architectures. I.e., SQLite can be read and written on Intel/AMD and ARM.

Reliability: Content can be updated continuously and atomically so that little or no work is lost in a power failure or crash.

Accessibility: SQLite database content can be viewed using a wide variety third-party tools. SQLite database files are recommended by the US Library of Congress as a storage format for long-term preservation of digital content.

4. Moving from pdb/pc3 to SQLite. I had written a J-Pilot plugin, which stores the internal format in SQLite format. This plugin is now part of the J-Pilot source-tree, similar to the Expense plugin. This allows an easy migration from old to new format. That way you can get warm with the new format, but still using the pdb/pc3 storage format. I used this approach for more than two years.

The SQLite plugin and the sole storage in SQLite are two entirely separate things. One can use one without the other. I.e., one can use the SQLite plugin without actually storing the entire data in SQLite, and vice versa, once you have stored all your data in SQLite, then there is no real need for the plugin anymore.

5. Outlook. SQLite is the native data storage format for Android apps. Therefore, the next step would be to implement an Android app to continue to use the SQLite data format. The HotSync mechanism was outlined in SQL Datamodel For J-Pilot. Currently I am still unsure what route to follow:

PWA (progressive web app) making the use of an app-store unneccessary. The Google Android weather app is a prominent example of a PWA.
WebAssembly based app using current J-Pilot source.
App programmed in Flutter using the Dart programming language. Google Wallet (a.k.a. Google Pay) is a prominent example of an app written in Flutter/Dart.

All routes would provide the J-Pilot functionality on Android and iOS.

The source code of J-Pilot is easy to read and understand. In particular, some longer functions contain all the processing required, instead of calling millions of small, but otherwise useless mini-functions. Nevertheless, some possible improvements:

J-Pilot uses automake to generate the final Makefile, which is quite arcane. A possible alternative would be Ninja, or use a plain Makefile. Though, there is this saying, "if it ain't broke, don't fix it".
Definition of global variables should be concentrated in a single C file, possibly within a struct like so:

struct {
    int app, rc_file_write, date_time_flag, child_pid, sqlite;
} glob;