Recently we ran into the need to load an extension for SQLite using PHP's PDO database abstraction. Unfortunately, this is not supported out of the box. Eventually we found a solution involving FFI and Z-Engine. In this blog post I will take you on a tour of our solution and the black magic behind it.
The problem
At Moxio we make extensive use of geospatial database functions. Whether it's for infrastructural construction projects or rule management for the living environment, a lot of objects are bound to a specific geographic location and need to be queried as such. In production we run MariaDB as a database engine, which has geospatial support baked in. For integration tests however we use the lightweight SQLite database engine, which does not come with geospatial functions out of the box. The SpatiaLite extension for SQLite can be used for this.
Currently we use a homemade database abstraction layer, which uses the SQLite3 PHP extension for communicating with SQLite. This PHP extension allows loading SQLite extensions such as SpatiaLite using the SQLite3::loadExtension method. So far all is well.
At the moment we're looking at switching our own database abstraction layer for the Doctrine DBAL. It simply doesn't make sense anymore to maintain our own. We'd rather use a battle-tested open source solution and try to contribute back some of our code if possible, so that everybody benefits.
The problem is that the Doctrine DBAL implementation is not based on PHP's SQLite3 extension, but basically a wrapper around PDO, which is already a database abstraction layer. It therefore contains few SQLite-specific features. There are three sqlite-prefixed functions in PDO, but the possibility to load an SQLite extension is not one of them. This is registered in the PHP issue tracker and there's an implementation PR on GitHub, but that one didn't make it into PHP 7.4. So, no Doctrine DBAL for us?
Exploring solutions
Naturally, we started thinking of solutions. Even if PHP didn't expose the functionality to load extensions, maybe we could still call it directly through the SQLite C API. The Foreign Function Interface (FFI) introduced in PHP 7.4 would be a good candidate to do this. However, if we look at the relevant function declaration in the C API we notice a problem:
int sqlite3_load_extension(
sqlite3 *db, /* Load the extension into this database connection */
const char *zFile, /* Name of the shared library containing extension */
const char *zProc, /* Entry point. Derived from zFile if 0 */
char **pzErrMsg /* Put error message here if not 0 */
);
The sqlite3_load_extension requires a pointer to the database connection we want to load the extension into, which we don't have in PHP. We must find some trick to obtain it from the PDO object.
Luckily, through trips to phpCE 2018 and Bulgaria PHP 2019 I've come to know Alexander Lisachenko and his work on Z-Engine. Z-Engine is a PHP library that uses FFI to hook into the Zend Engine, providing access to PHP's internal structures. Maybe this would allow obtaining the internal sqlite3 pointer behind a PDO object? Indeed, after reaching out Twitter, Alexander confirmed that this would probably be possible using Z-Engine.
Hence I set out to try a solution based on Z-Engine, but soon another challenge presented itself. I had hoped that the PDO object would have some sort of direct reference (something like a private variable) to the internal SQLite connection. This turned out not to be the case. Instead, the PDO implementation uses a common C trick where the PDO object and its internal structures are aligned next to eachother in memory. In the C code they use a function php_pdo_dbh_fetch_inner to shift in memory between the PDO object and the internal struct. We cannot use this function from FFI hoewever, since it is inlined and thus not exposed as a separate function to the outside world. I reached out to Alexander once again, and he and Nikita Popov pointed me to a trick involving handler->offset to obtain the correct memory offset to implement the memory shift myself.
Putting it all together
This proved to be the missing puzzle piece. It still took some time to get used to working with PHP FFI, but eventually I got a working solution. First we need to obtain a C pointer to the relevant PDO object using Z-Engine:
use ZEngine\Core;
use ZEngine\Reflection\ReflectionValue;
Core::init();
$pdo_refl_value = new ReflectionValue($pdo);
$pdo_obj_pointer = $pdo_refl_value->getRawObject();
We then initialize an FFI instance to traverse the relevant structures of the PDO-SQLite extension. To keep the header definitions brief, we omit all parts of C structs after the properties we're interested in. Also, we replace pointers to properties before the ones we want (and the sqlite3 pointer itself for now) by void pointers. This allows us to omit the headers for their real types, and doesn't matter since a pointer takes the same amount of memory regardless of what it points to. Also, we had to add a struct keyword in some places to please the PHP FFI parser.
$pdo_sqlite_ffi = \FFI::cdef(<<<CDEF
/* From https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L432 */
struct _pdo_dbh_t {
/* replaced pdo_dbh_methods* by void* */
const void *methods;
void *driver_data;
/* omitted rest of struct */
};
/* From https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L510 */
struct _pdo_dbh_object_t {
/* had to insert struct keyword here */
struct _pdo_dbh_t *inner;
/* omitted `zend_object std` */
};
/* Adapted from https://github.com/php/php-src/blob/cfc704ea83c56970a72756f7d4fe464885445b5e/ext/pdo_sqlite/php_pdo_sqlite_int.h#L55 */
struct pdo_sqlite_db_handle {
/* replaced sqlite3* by void* */
void *db;
/* omitted rest of struct */
};
CDEF, "pdo_sqlite.so");
With that FFI instance we can now perform the memory shift to get the PDO internal data structure corresponding to the exposed PDO object. We use the handle->offset trick to obtain the correct memory offset for the shift:
// Following https://github.com/php/php-src/blob/d1764ca33018f1f2e4a05926c879c67ad4aa8da5/ext/pdo/php_pdo_driver.h#L520
$offset = $pdo_obj_pointer->handlers->offset;
$pdo_dbh_object_pointer = $pdo_sqlite_ffi->cast("struct _pdo_dbh_object_t*", $pdo_sqlite_ffi->cast("char*", $pdo_obj_pointer) - $offset);
$pdo_dbh_pointer = $pdo_dbh_object_pointer[0]->inner;
The driver-specific handles are in the driver_data property. In case of the PDO SQLite driver this points to a pdo_sqlite_db_handle object, which contains the raw SQLite connection in its db property. We obtain a void pointer to that connection as follows:
// Following https://github.com/php/php-src/pull/3368/files#diff-eb26679695f7db289366ef6b03ee25daR729
$pdo_sqlite_db_handle_pointer = $pdo_sqlite_ffi->cast("struct pdo_sqlite_db_handle*", $pdo_dbh_pointer[0]->driver_data);
$sqlite3_void_pointer = $pdo_sqlite_db_handle_pointer[0]->db;
Now we set up an FFI object for communicating with the SQLite C API (or at least the parts of it we want to use):
$sqlite3_ffi = \FFI::cdef(<<<CDEF
/* From https://github.com/sqlite/sqlite/blob/278b0517d88d4150830a4ee2c628a55da40d186d/src/sqlite.h.in#L249 */
typedef struct sqlite3 sqlite3;
/* From https://github.com/sqlite/sqlite/blob/278b0517d88d4150830a4ee2c628a55da40d186d/src/sqlite.h.in#L6581 */
int sqlite3_load_extension(
sqlite3 *db, /* Load the extension into this database connection */
const char *zFile, /* Name of the shared library containing extension */
const char *zProc, /* Entry point. Derived from zFile if 0 */
char **pzErrMsg /* Put error message here if not 0 */
);
CDEF, "sqlite3.so");
We can then cast our void pointer to an actual sqlite3 pointer and use it to call functions on the SQLite C API:
$sqlite3_pointer = $sqlite3_ffi->cast("struct sqlite3*", $sqlite3_void_pointer);
$sqlite3_ffi->sqlite3_load_extension($sqlite3_pointer, "mod_spatialite.so", null, null);
And voila, our extension is loaded!
Wrapping up
To make this solution easy to use, we published it as a Composer package. It hides all underlying Z-Engine and FFI logic and allows loading SQLite extensions through a simple API. We aim to grow this library to also provide access to other SQLite API's that are otherwise not available in PHP. If there are other SQLite features that you'd like to use with PHP, feel free to file a feature request or create a PR!
Finally, please note that Z-Engine should not be considered stable before version 1.0.0. Use it at your own risk.
Top comments (0)