How to compile an extension into sqlite?

Question

I would like to compile an extension into sqlite for loading at runtime.

The file I am using is extension - functions.c from https://www.sqlite.org/contrib

I have been able to compile into a loadable module but I need to statically link it for loading at runtime (using shell.c to create an interface at run time)

I have read the manual on linking, but to be honest, it's a little bit beyond my scope of comprehension!

Could someone let me know what I need to do to compile please?

Answer 1

I found a way to compile sqlite3 from source code with additional functions provided by extension_functions.c.

Note:

At this time I show the quite dirty and easy way to compile sqlite with additional features because I haven't succeed to do that in right way.

But please remember that it would perhaps be much better to prepare a brand new part of amalgamation for adding custom features as @ngreen says above.
That's the designed way of sqlite itself.

1. Download the sqlite source code

https://www.sqlite.org/download.html

Choose amalgamation one, and better to use autoconf version.
For example, here is the download link of version 3.33.0.

https://www.sqlite.org/2020/sqlite-autoconf-3330000.tar.gz

curl -O https://www.sqlite.org/2020/sqlite-autoconf-3330000.tar.gz
tar -xzvf sqlite-autoconf-3330000.tar.gz
cd sqlite-autoconf-3330000

2. Download extension_functions.c

Listed at this url.

https://sqlite.org/contrib

Actual url:

https://sqlite.org/contrib/download/extension-functions.c?get=25

curl -o extension_functions.c https://sqlite.org/contrib/download/extension-functions.c?get=25

3. Configure compilation

We can specify the --prefix option to determine the destination of built stuffs.

./configure --prefix=/usr/local/sqlite/3.33.0

Other configuration time options can be specified as environment variables at this time.
Check https://www.sqlite.org/draft/compile.html for more details.

Here is an example to enable JSON and RTree Index features.

CPPFLAGS="-DSQLITE_ENABLE_JSON1=1 -DSQLITE_ENABLE_RTREE=1" ./configure --prefix=/usr/local/sqlite/3.33.0

And autoconf options can also be specified.

CPPFLAGS="-DSQLITE_ENABLE_JSON1=1 -DSQLITE_ENABLE_RTREE=1" ./configure --prefix=/usr/local/sqlite/3.33.0 --enable-dynamic-extensions

I couldn't find any documentation about these options at the official website, but found something in configure script itself.

Optional Features:
  --disable-option-checking  ignore unrecognized --enable/--with options
  --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
  --enable-silent-rules   less verbose build output (undo: "make V=1")
  --disable-silent-rules  verbose build output (undo: "make V=0")
  --disable-largefile     omit support for large files
  --enable-dependency-tracking
                          do not reject slow dependency extractors
  --disable-dependency-tracking
                          speeds up one-time build
  --enable-shared[=PKGS]  build shared libraries [default=yes]
  --enable-static[=PKGS]  build static libraries [default=yes]
  --enable-fast-install[=PKGS]
                          optimize for fast installation [default=yes]
  --disable-libtool-lock  avoid locking (might break parallel builds)
  --enable-editline       use BSD libedit
  --enable-readline       use readline
  --enable-threadsafe     build a thread-safe library [default=yes]
  --enable-dynamic-extensions
                          support loadable extensions [default=yes]
  --enable-fts4           include fts4 support [default=yes]
  --enable-fts3           include fts3 support [default=no]
  --enable-fts5           include fts5 support [default=yes]
  --enable-json1          include json1 support [default=yes]
  --enable-rtree          include rtree support [default=yes]
  --enable-session        enable the session extension [default=no]
  --enable-debug          build with debugging features enabled [default=no]
  --enable-static-shell   statically link libsqlite3 into shell tool
                          [default=yes]

FYI, Here is the default install script which is used in Homebrew. Maybe it would be useful to determine which option should be specified.

def install
  ENV.append "CPPFLAGS", "-DSQLITE_ENABLE_COLUMN_METADATA=1"
  # Default value of MAX_VARIABLE_NUMBER is 999 which is too low for many
  # applications. Set to 250000 (Same value used in Debian and Ubuntu).
  ENV.append "CPPFLAGS", "-DSQLITE_MAX_VARIABLE_NUMBER=250000"
  ENV.append "CPPFLAGS", "-DSQLITE_ENABLE_RTREE=1"
  ENV.append "CPPFLAGS", "-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS3_PARENTHESIS=1"
  ENV.append "CPPFLAGS", "-DSQLITE_ENABLE_JSON1=1"

  args = %W[
    --prefix=#{prefix}
    --disable-dependency-tracking
    --enable-dynamic-extensions
    --enable-readline
    --disable-editline
    --enable-session
  ]

  system "./configure", *args
  system "make", "install"
end

4. Remove confliction

Now we have to modify extension_functions.c to avoid conflicting against the source code of sqlite before compiling them together.

Open extension_functions.c and replace line 123 ~ 128 to a single line SQLITE_EXTENSION_INIT1.

#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#else
#include "sqlite3.h"
#endif

↓

SQLITE_EXTENSION_INIT1

5. Enable extension functions

We need to insert some line into shell.c to import and enable extension functions.

Open shell.c, search static void open_db and insert #include "extension_functions.c" at the line above.

#include "extension_functions.c"

static void open_db(ShellState *p, int openFlags){

Then search sqlite3_shathree_init(p->db, 0, 0); and insert sqlite3_extension_init(p->db, 0, 0); at the bottom of init funcs.

#endif
    sqlite3_fileio_init(p->db, 0, 0);
    sqlite3_shathree_init(p->db, 0, 0);
    sqlite3_completion_init(p->db, 0, 0);
    sqlite3_uint_init(p->db, 0, 0);
    sqlite3_decimal_init(p->db, 0, 0);
    sqlite3_ieee_init(p->db, 0, 0);
    sqlite3_extension_init(p->db, 0, 0);

6. Compile

Finally it's ready to compile sqlite including extension functions.

make install

It takes a while, and once done, distribution files will be generated at the destination which is specified at configuration time through --prefix option.

# Now we can use extension_functions without loading it manually.
$ /usr/local/sqlite/3.33.0/bin/sqlite3
sqlite> select cos(10);
-0.839071529076452

Answer 2

Q: "How to compile an extension into sqlite?"

A: That depends on the extension. To compile extension-functions.c referenced in the OP:

gcc -fPIC -shared extension-functions.c -o libsqlitefunctions.so -lm

(to remove the compilation warning see here)

Usage:

$ sqlite3
sqlite3> select cos(radians(45));
0.707106781186548
sqlite> .exit