sqlite foreign data wrapper

Last week, one of my customers asked me during a training why there is no foreign data wrapper for sqlite (actually, you can have one with multicorn, but there's no native one). I have no idea why but I was happy to learn that no sqlite FDW already existed. I wanted to write one for quite some time now, and it appeared to be the perfect idea.

So, in the evening, I started working on one. I took as foundation the blackhole foreign data wrapper, written by Andrew Dunstan. It helped a lot to start quickly. I found a bit surprising that it didn't include #ifdef for some functions and hooks, so that it could be compatible with 9.1 and 9.2. I added them in a patch that you can find here if you need them. Otherwise, you can simply delete some parts of the blackhole code.

After that, I tried to find how to open and close a sqlite database, and how to read a table in it. They have a really nice example in their "Quick start document" and "Introduction To The SQLite C/C++ Interface".

I wanted something really simple to start with. Almost two years ago, at pgconf.eu, I went to see Dave Page's talk on FDW (PostgreSQL at the center of your dataverse). So I already knew that you mainly need three functions to read a table: BeginForeignScan, IterateForeignScan, EndForeignScan. The first one has to open the connection (if it's not opened yet). The second one will be executed as many times as there are rows to grab. On its first execution, it must launch the query and get the first row. On every other iteration, it will grab a new row. And the third function helps cleaning memory. So I started to include the sqlite tutorial code in the blackhole FDW. And it worked great. I had to write some other functions, specifically the handler and the validator, but,in an hour, I had something working. Of course, it wasn't pretty. The database filename was written in the code, with no option to change it. The query executed remotely was also written in the code, which means you couldn't change the tablename without recompiling.

So I started to look at options for the foreign server and the foreign table. Most of the code was taken from Dave Page's mysql_fdw code. I quickly had a foreign data wrapper allowing two options: database (to change the database file), and table (to target a specific remote table).

The whole code is available on the sqllite_fdw github repository. I'll try to add the missing features as soon as possible. And when I'll have something working and tested, I'll post it on pgxn.


1. Le mardi, juin 11 2013, 10:02 par will

Thanks for making this !

I had to transfer some data from SQLite to Postgres last week, and couldn't find a SQLite FDW so I had to write a sed script to import a dump. Being able to use sqlite_fdw instead in the future will be much better.

Plus, as there are already MySQL and Postgres foreign data wrappers, this will now make it very easy to use Postgres to integrate data kept in any of the three main open source databases.

What other features are you planning to include ?

2. Le mardi, juin 11 2013, 22:16 par Guillaume Lelarge

You're welcome.

Right now, a user can select from a table. The planer knows the estimated number of rows it will get back. And the user can have the EXPLAIN QUERY PLAN from the sqlite database. That's not much, but it's a start.

I plan to include all the features available in the FDW from PostgreSQL 9.1 to PostgreSQL 9.3. Next one will probably be to have a writable FDW, and then handling push-downs (pushing the WHERE clause to the sqlite database so that we don't transfer the whole table to PostgreSQL if we only need one row).

I'm really interested to hear from tests. This for sure is to be used on tests servers, not production ones. But still, bugreports and enhancement requests are welcome :)

Ajouter un commentaire

Les commentaires peuvent être formatés en utilisant une syntaxe wiki simplifiée.

Fil des commentaires de ce billet