SQLite生成系列缺失

wz3gfoph 于 2022-11-24 发布在 SQLite

关注(0)|答案(2)|浏览(133)

我正在尝试使用SQLite CLI，但无法使用generate_series函数。我可以使用文档中建议的递归CTE来模拟它，但似乎无法使用该链接中的任何示例。以下是我的会话的一些输出：

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

看起来ext/misc/series.c扩展实际上并没有被静态链接。如果我从头编译，我也不知道该怎么做。我在这里做错了什么吗？

- 编辑**除非how to compile an extension into SQLite有一个好的答案，否则我不认为我可以做我想做的事情。文档是错误的：默认情况下，扩展不会内置到命令行shell中。

sqlite

来源：https://stackoverflow.com/questions/52954732/sqlite-generate-series-missing

2条答案

按热度按时间

i1icjdpr1#

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
'

赞(0）回复(0）举报 2022-11-24

inkz8wg92#

GENERATE_SERIES表值函数终于在2020-12-01在version 3.34.0中被添加到SQLite3 CLI中。祝生成愉快！

赞(0）回复(0）举报 2022-11-24