extending DBD::SQLite functionalities

About one year ago I started my first project involving XS programming : this was because I needed specific collation sequences in SQLite databases. The basic SQLite implementation in C had support for user-defined collations, but at that time the Perl API in DBD::SQLite did not give access to it ... so I had to write the glue myself.

Learning XS was kind of fun : the C structures, the dozens of macros, the stack manipulation functions and the "mortalization" concept, all of this is pretty well documented, so once you have a real project to get motivated, you can enter that field without too much trouble.

Once done, I sent the patch to the DBD::Sqlite author, and got no answer, which was less fun. Fortunately; Audrey Tang incorporated the patch in her DBD::SQLite::Amalgamation version of the driver.

Nowadays the situation is very different, because Adam Kennedy has taken over maintenance of DBD::SQLite, and there are both an open source repository and a mailing list, and a reactive Kenichi Ishigaki who cares about release management, so it's much more pleasant to participate.

What I did recently was to study the SQLite API and add support for all other native functions that so far were not exposed to the Perl DBD::Sqlite driver --- not that I really need that at the moment, but just for completeness's sake. Such functions are, for example, hooks to be run whenever a commit, rollback or update occurs; this means for example that if your transaction does some stuff outside of the database -- like creating files or directories --, you can program a rollback hook that will 'undo' these changes in case of failure.

Another interesting functionality is another hook called set_authorizer, that can decide whether a given SQL statement is authorized or not on that database handle. This could be used for example to specify that only administrators are allowed to update or insert data, while any user can read.

If anybody is interested, that stuff is already available on CPAN in the current developer release of DBD::SQLite ... enjoy!