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 are 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.

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

  1. currently no HotSync with your Palm device
  2. no "Calendar", just "Datebook" -- main difference being the first having categories
  3. no contact (i.e., version 2 address), which has 39 fields, while version 1 (a.k.a. address) having 20 fields
  4. 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:

  1. Better performance: Reading and writing from an SQLite database is often faster than reading and writing individual files from disk.
  2. Reduced complexity.
  3. 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.
  4. Reliability: Content can be updated continuously and atomically so that little or no work is lost in a power failure or crash.
  5. 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 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:

  1. PWA (progressive web app) making the use of an app-store unneccessary. The Google Android weather app is a prominent example of a PWA.
  2. WebAssembly based app using current J-Pilot source.
  3. 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:

  1. 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".
  2. 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;