Friday, July 4, 2014

project : Perl virtual tables for DBD::SQLite

During the year I have more and more management tasks and less time for programming. So for the holidays I wanted a change and decided to engage into a really "hardcore programming"  project, namely to add support for Perl virtual tables within the DBD::SQLite database driver. SQLite has this notion of "virtual tables" which look like regular tables but are implemented through callback routines. This project  implies some C programming, using Perl XS API, and the delicate part is to design some appropriate glue between SQLite's notion of "object-oriented", through extensible C structures and callbacks, and Perl's object-oriented features.

At the beginning I wasn't even sure if such a project would be feasible, but now it is slowly taking shape and I'm pretty confident that it will eventually reach something usable. The concept is quite similar to Perl's tied variables, where a published API is reused for accessing many different kinds of data; except that here the published API is SQL instead of hashes or array operators. As a result, we could have virtual tables bound to the filesystem, to the Win32::Registry, to some configuration data, or any other accessible resource. This will open a new field for lots of creative ideas.

My main motivation for doing this work is to be able to build a framework for collections of documents, using SQLite for the fulltext index, and using the filesystem for storing document content : this will be a much more powerful replacement for my very old File::Tabular::Web::Attachment::Indexed module. That module is still heavily used at Geneva courts of law, but now we have 10 years of data, and the old architecture is clearly showing its limits.

For the virtual tables project I need some test cases, so if anybody has ideas about Perl-accessible data to be published as an SQL table, I'm interested.


  1. Did my previous comment got lost? uff, I seems am having a dull morning!!!

    SQLite::VirtualTable is available on CPAN, it is mature and last time I looked it did almost anything doable with SQLite virtual tables.

    A long time ago DBD::SQLite authors and me were talking about integrating the virtual tables support there, but in the end we never did it.

    1. @Salvador : great, thanks for the heads up, I'm eager to have a look into this module, this seems to be just what I needed.

  2. I always thought that accessing tabular data from Wikipedia pages (like those found on "Comparison of X" pages) from SQLite would be neat!

  3. You may also find my 2009 CPAN module Set::Relation useful to solve your problem, if you just want to do what SQL does with Perl data structures.

    By the way, posting to this forum seems to completely break on the Safari browser; clicking Publish/Preview simply makes the form content vanish; I'm trying this time on Firefox.