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

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](https://github.com/sqlite/sqlite) 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]

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]

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