# J-Pilot Plugin For SQLite Export


This post was automatically copied from [J-Pilot Plugin For SQLite Export](https://eklausmeier.goip.de/blog/2020/04-29-j-pilot-plugin-for-sqlite-export) on [eklausmeier.goip.de](https://eklausmeier.goip.de/blog).


In [SQL Datamodel For J-Pilot](https://eklausmeier.goip.de/blog/2020/04-27-sql-datamodel-for-j-pilot/) I described the [SQLite](https://sqlite.org/) datamodel. I wrote a [J-Pilot](http://jpilot.org/) plugin which can export the below entities and write them to an [SQLite](https://sqlite.org/) database file. The direction is one-way: from [J-Pilot](http://jpilot.org/) to [SQLite](https://sqlite.org/).

1. Address
1. Datebook
1. Memo
1. To-Do
1. Expense
1. 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](https://sqlite.org/cintro.html) with [`sqlite3_exec()`](https://sqlite.org/c3ref/exec.html), and [`sqlite3_prepare()`](https://sqlite.org/c3ref/prepare.html), [`sqlite3_bind()`](https://sqlite.org/c3ref/bind_blob.html), [`sqlite3_step()`](https://sqlite.org/c3ref/step.html), and finally [`sqlite3_finalize()`](https://sqlite.org/c3ref/finalize.html).

The general mechanics of a [J-Pilot](http://jpilot.org/) plugin are described by [Judd Montgomery](https://www.linkedin.com/in/juddmontgomery/), the author of [J-Pilot](http://jpilot.org/), in [this document](http://jpilot.org/documentation/plugin.html). I took the [`Expense/expense.c`](https://github.com/juddmon/jpilot/blob/master/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`
1. Export above mentioned entities
1. In debug mode you can use [J-Pilot](http://jpilot.org/)'s search to search in the [SQLite](https://sqlite.org/) database


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

Installation.

1. Compile single source code file [`jpsqlite.c`](https://github.com/eklausme/c/blob/master/jpsqlite.c)
1. Copy library (`.so` file) in plugin directory (`$HOME/.jpilot/plugins`)
1. Copy datamodel SQL file [`jptables.sql`](https://github.com/eklausme/c/blob/master/jptables.sql) into plugin directory


Compilation is with below command:
```bash
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](https://aur.archlinux.org/packages/pilot-link) header files and the [J-Pilot](http://jpilot.org/) ([AUR](https://aur.archlinux.org/packages/pilot-link)) 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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1626772113221/gBKy58eEc.png)

In debug mode and in debug mode only, the "[J-Pilot](http://jpilot.org/) search" also searches through all entities in the [SQLite](https://sqlite.org/) database.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1626772115559/wbUnqrLNb.png)

The long-term goal is that [SQLite](https://sqlite.org/) is the internal data structure for [J-Pilot](http://jpilot.org/), thereby abandoning the binary files entirely.