J-Pilot Plugin For SQLite Export

In SQL Datamodel For J-Pilot I described the SQLite datamodel. I wrote a J-Pilot plugin which can export the below entities and write them to an SQLite database file. The direction is one-way: from J-Pilot to SQLite.

  1. Address
  2. Datebook
  3. Memo
  4. To-Do
  5. Expense
  6. Various categories for above entities

Adding more entities is pretty easy. For example, if people need the Calendar Palm database exported, this can be implemented quickly. We use the usual SQLite API with sqlite3_exec(), and sqlite3_prepare(), sqlite3_bind(), sqlite3_step(), and finally sqlite3_finalize().

The general mechanics of a J-Pilot plugin are described by Judd Montgomery, the author of J-Pilot, in this document. I took the Expense/expense.c source code from the Expense plugin as a guide.

The plugin provides the following functionality:

  1. Create new database from scratch, it is called jptables.db
  2. Export above mentioned entities
  3. In debug mode you can use J-Pilot‘s search to search in the SQLite database

If you call jpilot -d then debug-mode is activated.

Installation.

  1. Compile single source code file jpsqlite.c
  2. Copy library (.so file) in plugin directory ($HOME/.jpilot/plugins)
  3. Copy datamodel SQL file jptables.sql in plugin directory

Compilation is with below command:

gcc `pkg-config -cflags-only-I gtk+-2.0` -I <J-Pilot src dir> -s -fPIC -shared jpsqlite.c -o libjpsqlite.so -lsqlite3

For this to work you need the Pilot-Link header files and the J-Pilot (AUR) source code at hand.

Running the plugin: go to the plugins menu by main-menu selection or function key (F7 in my case), then press SQL button. All previous data is completey erased in the database, then all data is written to database within a single transaction.

In debug mode and in debug mode only, the J-Pilot search also searches through all entities in the SQLite database.

The long-term goal is that SQLite is the internal data structure for J-Pilot, thereby abandoning the binary files entirely.

SQL Datamodel For J-Pilot

Currently J-Pilot stores its data in binary form which is compatible with the original Palm data format. See Palm File Format Specification. Reading these binary formats is not simple, see for example pdbrd.c. Portion of the internal structure looks something like this:

typedef struct {        // header of record list
        LocalID nextRecordListID;
        UInt16 numRecords;
        UInt16 firstEntry;
} RecordListType;

typedef struct {        // single element of record list
        LocalID localChunkID;   // offset, start of actual data
        UInt8 attributes;       // 1st nibble: ?/private, 2nd: this is the category
        UInt8 uniqueID[3];
} RecordEntryType;

typedef struct {
        UInt16 renamedCategories;
        char categoryLabels[16][16];
        UInt8 categoryUniqIDs[16];
        UInt16 lastUniqID_pad;  //UInt8 lastUniqID; UInt8 padding;
} AppInfoType;

In 2013 I proposed to use SQLite as internal data format instead, see Possible Enhancements to J-Pilot.

Below datamodel closely follows the field declaration in J-Pilot and Pilot-Link, e.g., /usr/include/pi-datebook.h or utils.h.

Continue reading