SQLite Forum

Latest trunk/extensions & UDF's on tap
Login

Latest trunk/extensions & UDF's on tap

(1.1) By curmudgeon on 2021-02-22 10:24:38 edited from 1.0 [source]

For a while now I've been wishing that, at the click of a button, I could have the latest trunk & extensions as well as all my own functions automatically available to all connections (CLI as well as in my own code). I've recently started usung linux (ubuntu 20.04 via WSL2) and to that end I've written a bash script which is shown below in the hope that it might demistify the process for other wee diddy programmers like myself. I'd also like to thank the forum gurus for their help in getting to this stage (too many of them to mention but Keih Medcalf played a starring role in the core_init stuff) and ask them to cast a critical eye.

1) Create the directory you want to store the sqlite repository in. I chose ~/sqlite.


2) If you want any extensions &/or UDF's to be automatically available to all your connections and want to avoid creating libraries you'll need a core_init function (you can call it something else provided you replace core_init with your own name in the 2 files below). I've written a bare bones example creating 2 simple UDF's and including 2 extensions to illustrate the process.

file : core_init.c (it must be saved in your chosen sqlite directory)

--------------------------------
void mult2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2*sqlite3_value_int(values[0]));
}

void plus2(sqlite3_context *ctx,int nargs,sqlite3_value **values)
{
    sqlite3_result_int(ctx,2+sqlite3_value_int(values[0]));
}

#define TXTREP SQLITE_UTF8 | SQLITE_DETERMINISTIC

int sqlite3_myfcts_init(sqlite3 *db,char **pzErrMsg,const sqlite3_api_routines *pApi)
{
    return sqlite3_create_function_v2(db,"mult2",1,TXTREP,0,&mult2,0,0,0) +
    sqlite3_create_function_v2(db,"plus2",1,TXTREP,0,&plus2,0,0,0);
}

#include "eval.c"
#include "carray.c"

int core_init(const char* dummy)
{
	int nErr = sqlite3_auto_extension((void(*)())sqlite3_myfcts_init);
	nErr += sqlite3_auto_extension((void(*)())sqlite3_eval_init);
#ifndef SQLITE_OMIT_VIRTUALTABLE
	nErr += sqlite3_auto_extension((void(*)())sqlite3_carray_init);
#endif
	return nErr ? SQLITE_ERROR : SQLITE_OK;
}
--------------------------------


3) Create and save the following bash script (I called it upd8) in your sqlite directory.

--------------------------------
#!/bin/bash
cd "$(dirname $(realpath "$0"))"

echo "installing dependencies ..."
sudo apt uninstall sqlite3 -y > /dev/null 2>&1 # uninstall sqlite3 if it was installed with sudo apt
set -e # exit if an error occurs
sudo apt install fossil tcl-dev libreadline-dev -y # if you manually install any of these then omit from list
echo "updating system ..."
sudo apt update && sudo apt upgrade -y # take opportunity to update everything else not sqlite related
echo

if [[ -f sqlite.fossil ]]; then
    echo "updating repository ..."
    fossil update trunk > /dev/null
else
    echo "cloning sqlite repository ..." 
    fossil clone https://www.sqlite.org/src/sqlite sqlite.fossil
    fossil open sqlite.fossil > /dev/null
fi

echo "configuring sqlite ..."
{ configure; make; } > /dev/null
echo "creating tools ..."
make sqlite3_analyzer sqldiff > /dev/null
# make test # uncomment this line if you want sqlite tests run 
if [[ -f core_init.c ]]; then
    echo "integrating core_init.c ..."
    cat core_init.c >> sqlite3.c
    gcc -Os -I. -I./ext/misc \
    -DSQLITE_THREADSAFE=0 \
    -DSQLITE_EXTRA_INIT=core_init -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_FTS5 \
    -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE \
    -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_READLINE -DHAVE_USLEEP \
    sqlite3.c shell.c -o sqlite3 -ldl -lreadline -lncurses -lz -lm > /dev/null 
    # if you omit DSQLITE_THREADSAFE=0 add -pthread to library list
 fi
--------------------------------
 
4) On the command line type 'chmod +x upd8' to make the script executable.

5) Add your sqlite directory to the system PATH. If you're using WSL like myself you'll have to do it by editing (I had to create the file) /etc/profile.d/myenvvars.sh as WSL doesn't seem to pick up on the /etc/environment path (there's a thread on github about this problem that has went unresolved for years). My myenvvars.sh shows the following

tom@sp4:~$ cat /etc/profile.d/myenvvars.sh
#!/bin/bash
export PATH=~/sqlite:$PATH

6) Run 'upd8' in bash. The tail of my output shows

configuring sqlite ...
ar: `u' modifier ignored since `D' is the default (see `U')
ar: `u' modifier ignored since `D' is the default (see `U')
creating tools ...
integrating core_init.c ...
tom@sp4:~$ 
The warnings appear to be harmless.

7) Now test with sqlite3

tom@sp4:~$ sqlite3
SQLite version 3.35.0 2021-02-21 01:19:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select eval('select 2+3'), mult2(8), plus2(4);
5|16|6
sqlite> .exit
tom@sp4:~$

Edit: Amended upd8 so it uninstalls sqlite3 if already installed using sudo apt.