How to compile an extension from scratch? The CSV virtual table in particular
I'm trying to get the CSV virtual table working as per example on https://www.sqlite.org/csv.html:
.load ./csv CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); SELECT * FROM t1;
But I don't understand the compilation process. I'm on MacOS and installed
sqlite3 with Homebrew. Now I've got just one file
csv.c from the repo mirror on GitHub and try to compile it in an arbitrary directory as described on https://www.sqlite.org/loadext.html:
% gcc -g -fPIC -dynamiclib csv.c -o csv.dylib csv.c:643:27: error: use of undeclared identifier 'SQLITE_VTAB_DIRECTONLY' sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY); ^ 1 error generated.
Makefile.in does not contain much traces of
A user on StackOverflow followed a different path and didn't succeed either: https://stackoverflow.com/questions/59969377/how-to-compile-sqlite3-extension-csv-virtual-table
What steps should I follow from the very beginning to get
.load ./csv working?
Try adding "
-I." to the gcc command-line (which really should be "clang" on Mac, but they have "gcc" as an alias). The -I. will tell the compiler to pick up the
include files from the local directory rather than from the system. The
SQLITE_VTAB_DIRECTONLY macro is a recent addition and probably has not
made it into whatever version is currently installed on your Mac.
It works! Thank you very much!
Here's the full script for compiling the CSV extension from scratch (based on https://www.sqlite.org/loadext.html and https://github.com/sqlite/sqlite/blob/master/README.md):
wget https://www.sqlite.org/src/tarball/sqlite.tar.gz tar xzf sqlite.tar.gz mkdir bld cd bld ../sqlite/configure make gcc -g -I. -fPIC -dynamiclib ../sqlite/ext/misc/csv.c -o csv.dylib
And testing it:
echo -e 'col_text,col_int\napples,3\noranges,5' > sample.csv ./sqlite3 '' '.load csv' 'CREATE VIRTUAL TABLE temp.t1 USING csv(filename="sample.csv");' 'SELECT * FROM t1;'