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.

Did you build that module and load it into the client? https://www.sqlite.org/loadext.html — Shawn, Oct 23 '18 at 17:38
According to the link, it's statically compiled into the CLI, which is what I want. If that's not happening, then I need to know what build option to use to make that happen. I've built from source using homebrew on macOS and abuild on Alpine Linux and neither platform does what the documentation says. — ngreen, Oct 23 '18 at 18:08
Huh. That's interesting. And wrong. That extension isn't included in shell.c. You have to build it as a loadable extension and `.load` it. — Shawn, Oct 23 '18 at 18:12
I was able to edit `shell.c.in` and create my own amalgamation, which caused the code, including `rc = sqlite3_create_module(db, "generate_series", &seriesModule, 0);` to be included in the resulting `shell.c` file. Still doesn't seem to work. — ngreen, Oct 23 '18 at 19:45
I found the place where static extensions are initialized and using that allowed the change to work. — ngreen, Oct 23 '18 at 21:31

score 2 · Answer 1 · answered Oct 24 '22 at 00:35

2

The GENERATE_SERIES table-valued function has been finally added to SQLite3 CLI in version 3.34.0 on 2020-12-01. Happy generating!

answered Oct 24 '22 at 00:35

Endrju

2,354
16
23

Adobe · Answer 2 · 2020-08-17T07:05:27.443

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
'

SQLite generate_series missing

2 Answers2

Linked