[sqlite] Allow inclusion of generate_series function

[Digital Dog]

According to the online documentation (https://www.sqlite.org/series.html

),

generate_series is compiled into the command line shell. As it turns out,
this is not so. It is not even an option in the build system from what I
can tell. It would be nice to at least have a build flag to enable it.
I searched the list archives and noticed an email from late 2015 that
indicates that generate_series was never actually a part of the shell.
Apparently no one ever took up the task of making that happen. Would it be
okay if I put together a patch for this? I would prefer it to be enabled

by

default, but I can implement it either way.

Here it also doesn't work:

SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select * from generate_series(1,1000);
Error: no such table: generate_series

It's a pity because it's a very nice tally table and fast method of
generating sequential data, random data, intended number of rows etc. which
would be a useful addition in the shell one-liners or other sqlite scripts.
It's also useful for simple performance assessments.

I vote for GENERATE_SERIES to be included in official sqlite3 binary and
libraries.

Thanks in advance.

[Richard Hipp]

This post might be inappropriate. Click to display it.

[Digital Dog]

Post by Richard Hipp

Post by Digital Dog
I vote for GENERATE_SERIES to be included in official sqlite3 binary and
libraries.

We are under pressure to keep SQLite as small and compact as possible.
We cannot go adding every feature that everyone requests without the
size of the library growing out of control. Trade-offs have to be
made. We try to provide the ability for people who actually want lots
of features to include them in their own builds. But we do no think it
is appropriate to add things that grow the size of the library unless
there is a compelling need.

Okay - keeping the core library small - that makes sense.

But for the Command Line tools for general purpose OS like Windows, Linux,
BSD, macOS which you offer for download? I'm not so sure. It could contain
more features because there are no restrictions. Windows, macOS, typical
Linux server or desktop won't work without gigabytes of RAM. Mobile devices
are joining the club. I think the scenario can be completely reversed -
majority of platforms is capable enough that they can include almost all
features, and only some, embedded, esoteric, have real constraints and
require trimmed feature set. I think they need to be custom-built anyway.

Or maybe it is possible to offer different builds - minimal shell and
fully-packed shell on the download page?

[Nathan Green]

Post by Digital Dog

Post by Richard Hipp

Post by Digital Dog
I vote for GENERATE_SERIES to be included in official sqlite3 binary

and

Post by Richard Hipp

Post by Digital Dog
libraries.

We are under pressure to keep SQLite as small and compact as possible.
We cannot go adding every feature that everyone requests without the
size of the library growing out of control. Trade-offs have to be
made. We try to provide the ability for people who actually want lots
of features to include them in their own builds. But we do no think it
is appropriate to add things that grow the size of the library unless
there is a compelling need.

Okay - keeping the core library small - that makes sense.
But for the Command Line tools for general purpose OS like Windows, Linux,
BSD, macOS which you offer for download? I'm not so sure. It could contain
more features because there are no restrictions. Windows, macOS, typical
Linux server or desktop won't work without gigabytes of RAM. Mobile devices
are joining the club. I think the scenario can be completely reversed -
majority of platforms is capable enough that they can include almost all
features, and only some, embedded, esoteric, have real constraints and
require trimmed feature set. I think they need to be custom-built anyway.
Or maybe it is possible to offer different builds - minimal shell and
fully-packed shell on the download page?

Yes, generate_series can be simulated, but adding 5 extra lines to a query
is quite unpleasant. Making it an optional flag in the amalgamation build
has no impact on library size unless one takes the extra step to include
it. I've already put together a patch that makes this possible, I just
need to open a pull request I guess. I just don't want to put too much
time into it if it's going to be rejected out of hand.

--
Nathan

Post by Digital Dog
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

Post by Nathan Green

Post by Digital Dog

Post by Richard Hipp

Post by Digital Dog
I vote for GENERATE_SERIES to be included in official sqlite3
binary and libraries.

We are under pressure to keep SQLite as small and compact as
possible.
We cannot go adding every feature that everyone requests without
the size of the library growing out of control. Trade-offs have to be
made. We try to provide the ability for people who actually want
lots of features to include them in their own builds. But we do no
think it is appropriate to add things that grow the size of the library
unless there is a compelling need.

Okay - keeping the core library small - that makes sense.
But for the Command Line tools for general purpose OS like Windows,
Linux, BSD, macOS which you offer for download? I'm not so sure. It could
contain more features because there are no restrictions. Windows, macOS,
typical Linux server or desktop won't work without gigabytes of RAM. Mobile
devices are joining the club. I think the scenario can be completely
reversed - majority of platforms is capable enough that they can include
almost all features, and only some, embedded, esoteric, have real constraints
and require trimmed feature set. I think they need to be custom-built
anyway.
Or maybe it is possible to offer different builds - minimal shell
and fully-packed shell on the download page?

Yes, generate_series can be simulated, but adding 5 extra lines to a
query is quite unpleasant. Making it an optional flag in the amalgamation
build has no impact on library size unless one takes the extra step to
include it. I've already put together a patch that makes this possible, I
just need to open a pull request I guess. I just don't want to put too
much time into it if it's going to be rejected out of hand.

But, if you are going to have an extra compile time flag to enable it, then why not just include the extension when you compile the code yourself since that only requires appending the "extensions" you want included, a custom "init hook" to load them all, and one additional define at compile time to tell SQLite3 to run your additional "init hook" for every connection? And it works the same for adding "internal extensions" to both the shell and the core library?

I load a whole mess of extensions this way and have them initialized on every connection, compiled into both the shell and the SQLite3 engine itself. In fact, the most difficult thing to deal with is when extensions are loaded "internally" as part of shell.c but not loaded into the actual sqlite3.c amalgamation code itself since this means I have to find and disable the addition of the extension in shell.c and wrap a bunch of defines around it to disable it from compilation otherwise the optimizer gripes and complains about duplicate symbols and modules, so that I can add it where it belongs, in the sqlite3.c library, so that LTO and page-tuning work properly.

Why one would want a particular capability available in the shell that is not available when compiling the amalgamation code directly is beyond my ken. I realize that consistency is the hobgobblin of little minds, but why would one not want the same extensions available when compiling the amalgamation into "some other" application but have that extension built into the shell (which is merely yet another application)?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.