Is it possible to build sqlite extensions on Apple Silicon using clang?

I am attempting to build the percentile sqlite extension on an M1 laptop using the instructions on the page:

gcc -g -fPIC -shared percentile.c -o percentile.so

However, that results in the following errors from clang:

$ gcc -g -fPIC -shared percentile.c -o percentile.so
Undefined symbols for architecture arm64:
  "_sqlite3_aggregate_context", referenced from:
      _percentStep in percentile-a11598.o
      _percentFinal in percentile-a11598.o
  "_sqlite3_create_function", referenced from:
      _sqlite3_percentile_init in percentile-a11598.o
      _sqlite3_percentile_init in percentile-a11598.o
      _sqlite3_percentile_init in percentile-a11598.o
  "_sqlite3_free", referenced from:
      _percentStep in percentile-a11598.o
      _percentFinal in percentile-a11598.o
  "_sqlite3_realloc64", referenced from:
      _percentStep in percentile-a11598.o
  "_sqlite3_result_double", referenced from:
      _percentFinal in percentile-a11598.o
  "_sqlite3_result_error", referenced from:
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
  "_sqlite3_result_error_nomem", referenced from:
      _percentStep in percentile-a11598.o
  "_sqlite3_user_data", referenced from:
      _percentStep in percentile-a11598.o
  "_sqlite3_value_double", referenced from:
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
  "_sqlite3_value_numeric_type", referenced from:
      _percentStep in percentile-a11598.o
      _percentStep in percentile-a11598.o
  "_sqlite3_value_type", referenced from:
      _percentStep in percentile-a11598.o
ld: symbol(s) not found for architecture arm64
clang: error: linker command failed with exit code 1 (use -v to see invocation)

Is it possible to build sqlite extensions using clang, or do I need to get real GCC to make this work?

Solution

After much experimentation, I have got something that appears to work. The command you need is more like this as shared object libraries end with .dylib on macOS:

gcc -g -fPIC -dynamiclib -undefined suppress percentile.c -o percentile.dylib

although it does produce a warning ld: warning: -undefined suppress is deprecated which somebody cleverer than me (there are plenty about) may be able to assist with.

However, when you try to load it, it will fail if you use the Apple-supplied sqlite3 command in /usr/bin/sqlite3. You can tell which one you are using by running:

type sqlite3
sqlite3 is /usr/bin/sqlite3

When you try to load it, you get:

sqlite> .load percentile
Error: unknown command or invalid arguments:  "load". Enter ".help" for help

If you then check how Apple compiles sqlite3 with:

sqlite3 :memory: 'select * from pragma_compile_options()'

Output

ATOMIC_INTRINSICS=1
BUG_COMPATIBLE_20160819
CCCRYPT256
COMPILER=clang-15.0.0
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=2000
DEFAULT_CKPTFULLFSYNC
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=32768
DEFAULT_LOOKASIDE=1200,102
...
...
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=500000
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_UNFAIR
OMIT_AUTORESET
OMIT_LOAD_EXTENSION                  <--- OOPS, ".load" not built in
STMTJRNL_SPILL=131072
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=2
USE_URI

You can see that Apple doesn't permit the .load command. So, you need to either build the entire sqlite3 yourself (without the OMIT_LOAD_EXTENSION obviously), or use homebrew to install their executable:

brew install sqlite

Then you can run:

/opt/homebrew/Cellar/sqlite/3.46.0/bin/sqlite3 :memory: 'select * from pragma_compile_options()'

and you will see that the .load command is supported. Now you can do:

/opt/homebrew/Cellar/sqlite/3.46.0/bin/sqlite3
sqlite> .load percentile
sqlite>

Note that I have used /opt/homebrew/Cellar/sqlite/3.46.0/bin/sqlite3 whereas you should more properly use "$(brew --prefix)/Cellar/sqlite/3.46.0/bin/sqlite3" because that will work on Intel Macs (which use /usr/local/Cellar) as well as Apple Silicon (which use /opt/homebrew/Cellar).