Original post is here: eklausmeier.goip.de
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.
1$ ls -l .jpilot
2total 20308
3-rw-r--r-- 1 klm klm 8750 Sep 12 16:20 jpilotcss.default
4-rw------- 1 klm klm 0 Mar 27 2021 jpilot.plugins
5-rw------- 1 klm klm 7217152 Dec 3 15:23 jptables.db
6drwxr-xr-x 2 klm klm 4096 Nov 26 18:33 old/
7drwxr-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
1jpilot -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:
1if (glob_sqlite) jpsqlite_DatebookSEL(&cel,NULL,1);
2else 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.
[mermaid] %%{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" [/mermaid]
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.
|
|
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
structlike so:
1struct {
2 int app, rc_file_write, date_time_flag, child_pid, sqlite;
3} glob;