14 Sep, 2020
What would be nice is if we could support loadable modules in SQLite in Lua for Apache on the Raspberry Pi so that we can add in new functions.
The default build of apr_dbd_sqlite doesn't support this, but we can easily enough re-compile it so that it does.
To avoid installing loads of extra software we won't need, we'll set up an isolated chroot and do the build in there. We can delete it afterwards.
For background information on chroots and approaches, see these:
Let's get started:
sudo apt install -y binutils debootstrap libsqlite3-mod-spatialite
mkdir -p /tmp/chroot/buster
mkdir -p /tmp/chroot/cache
sudo debootstrap --keyring=/usr/share/keyrings/raspbian-archive-keyring.gpg --cache-dir=/tmp/chroot/cache buster
/tmp/chroot/buster http://raspbian.raspberrypi.org/raspbian/
sudo chroot /tmp/chroot/buster
cat << EOF > /etc/apt/sources.list.d/source.list
deb-src http://raspbian.raspberrypi.org/raspbian/ buster main contrib non-free rpi
EOF
apt update
apt install -y dpkg-dev
apt install -y locales
dpkg-reconfigure locales
apt build-dep -y libaprutil1-dbd-sqlite3
Now we have everything we need, let's make a change:
export APACHEVER="1.6.1"
cd /tmp/
apt-get source -y libaprutil1-dbd-sqlite3="$APACHEVER"
cd "apr-util-$APACHEVER"
cp dbd/apr_dbd_sqlite3.c dbd/apr_dbd_sqlite3.c.orig
cat << EOF > dbd/apr_dbd_sqlite3.patch
--- dbd/apr_dbd_sqlite3.c.orig 2020-08-18 10:40:12.490478890 +0000
+++ dbd/apr_dbd_sqlite3.c 2020-08-18 10:40:59.600120671 +0000
@@ -828,6 +828,15 @@
sqlite3_close(conn);
return NULL;
}
+ /* Allow loadable extensions */
+ sqlres = sqlite3_enable_load_extension(conn, 1);
+ if (sqlres != SQLITE_OK) {
+ if (error) {
+ *error = apr_pstrdup(pool, sqlite3_errmsg(conn));
+ }
+ sqlite3_close(conn);
+ return NULL;
+ }
/* should we register rand or power functions to the sqlite VM? */
sql = apr_pcalloc(pool, sizeof(*sql));
sql->conn = conn;
EOF
patch -p0 < dbd/apr_dbd_sqlite3.patch
# You would have to put the patch in debian/patches if building properly, and build like this:
# env -i PATH=/usr/bin:/bin SHELL=/bin/sh dpkg-buildpackage -uc -us </dev/null
# But we can just build for local use like this:
dpkg-buildpackage -b
# Also, if you look at debian/rules you can see how the package is built. This should give you enough hints to build manually if you wanted to.
# In this case you could just have done:
# ./configure --with-apr=/usr --with-sqlite3
# make
# Then copied the .so from dbd/.libs
Now exit the chroot:
exit
Back in your main environment install the package you created:
sudo dpkg -i /tmp/chroot/buster/tmp/libaprutil1-dbd-sqlite3_1.6.1-4_armhf.deb
sudo apt install -f
Put it on hold so that it doesn't get upgraded when you run apt-get upgrade:
sudo apt-mark hold libaprutil1-dbd-sqlite3
Now we can install spatialite and load the module as part of the DBD setup.
sudo apt install -y libsqlite3-mod-spatialite
If you've followed Apache with SQLite then you can add now an SQL initialisation statement to /etc/apache2/conf-available/demo.conf that loads spatialite:
DBDInitSQL "SELECT load_extension('mod_spatialite');"
Unfortunately, you can't use any of those functions in a DBDPrepareSQL statement, because it seems the prepare statement is sometimes run before the init.
As a workaround you can build the loading of the library into the driver itself.
After line 840 add:
/* Load mod_spatialite */
sqlres = sqlite3_load_extension(conn, "mod_spatialite", 0, error);
if (sqlres != SQLITE_OK) {
if (error) {
*error = apr_pstrdup(pool, sqlite3_errmsg(conn));
}
sqlite3_close(conn);
return NULL;
}
You'll then need to re-compile, re-install the .deb and restart Apache.
Now you can change your prepared statements to use spatial functions:
DBDPrepareSQL "SELECT Dimension(GeomFromText('POINT(10 20)'));"
Just reload Apache to test:
sudo systemctl reload apache2
For debugging, install sqlite3 and inspect the databases directly.
sudo apt install -y sqlite3
Be the first to comment.
Copyright James Gardner 1996-2020 All Rights Reserved. Admin.