generate series - SQLite generate_series missing

Question

I'm trying to work with the SQLite CLI, and I can't get the generate_series function to work. I can simulate it with the recursive CTE, as suggested in the documentation, but I can't seem to get any of the examples in that link to work. Here's some output from my session:

sqlite> with recursive generate_series(value) as (
    select 1
    union all select value+1
    from generate_series
    where value+1<=3)
select value from generate_series;
1
2
3
sqlite> select value from generate_series;
Error: no such table: generate_series
sqlite> select value from generate_series(1,3,1);
Error: no such table: generate_series

It seems like the ext/misc/series.c extension is not actually being statically linked. I also don't know how to do that if I compile from scratch. Am I doing something wrong here?

Edit Until how to compile an extension into SQLite has a good answer, I don't think I'll be able to do what I want. The documentation is wrong: the extension is not build into the command line shell by default.

Answer 1

The following builds latest sqlite with dynamic library support, and compiles series extension. It also assumes debian-based linux distributive:

sudo apt build-dep sqlite3

mkdir sqlite-compilation
cd    sqlite-compilation

wget -O sqlite.tar.gz https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release

tar xzf sqlite.tar.gz

mkdir build
cd    build
  ../sqlite/configure
  make OPTS='-DSQLITE_ENABLE_LOAD_EXTENSION'
  ./sqlite3 -cmd 'pragma compile_options;' <<< .exit
cd -

cd sqlite/ext/misc
  gcc -g -O2 -shared -fPIC -I ../../../build -o series ./series.c
  ../../../build/sqlite3 <<< '
.load ./series
select value from generate_series(5,30,5);
.exit
  '
cd -

In result you will have:

build/sqlite3             # sqlite3 binary
sqlite/ext/misc/series.so # series extension

Also, if you want change the name of the extension object file (series.so), you need to edit its init function with a "generic" one:

sed -i 's/int sqlite3_series_init(/int sqlite3_extension_init(/' series.c
gcc -g -O2 -shared -fPIC -I ../../../build -o libSqlite3Series.so ./series.c
sqlite3 <<< '
.load ./libSqlite3Series
select value from generate_series(5,30,5);
.exit
'