SQLite Forum

How to compile an extension from scratch? The CSV virtual table in particular

How to compile an extension from scratch? The CSV virtual table in particular

(1) By anonymous on 2020-05-16 12:20:20 [source]

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');

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 csv.c.

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?

(2) By Richard Hipp (drh) on 2020-05-16 16:51:31 in reply to 1 [link] [source]

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.

(3) By anonymous on 2020-05-17 11:13:09 in reply to 2 [link] [source]

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
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;'