Re: Allow inclusion of generate_series function

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Digital Dog
On Thu, 25 Oct 2018 09:32:05 -0700 Nathan Green wrote:

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Richard Hipp-3
On 12/4/18, Digital Dog <[hidden email]> wrote:
>
> I vote for GENERATE_SERIES to be included in official sqlite3 binary and
> libraries.

That seems unlikely, since you can accomplish the same thing using a
recursive common table expression.

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.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Digital Dog
On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp <[hidden email]> wrote:

>
> On 12/4/18, Digital Dog <[hidden email]> wrote:
> >
> > 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?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Nathan Green
On Tue, Dec 4, 2018 at 11:25 AM Digital Dog <[hidden email]> wrote:

> On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp <[hidden email]> wrote:
> >
> > On 12/4/18, Digital Dog <[hidden email]> wrote:
> > >
> > > 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.

--
Nathan

> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Keith Medcalf

On Thursday, 6 December, 2018 08:23, Nathan Green <[hidden email]> wrote:

>On Tue, Dec 4, 2018 at 11:25 AM Digital Dog <[hidden email]> wrote:
>> On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp <[hidden email]> wrote:
>> > On 12/4/18, Digital Dog <[hidden email]> wrote:

>> > > 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.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

Digital Dog
On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf <[hidden email]> wrote:

>
> 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.


The issue is not limited to generate_series (although that one seems to be
pain point for many users). The issue is that all sqlite downloads offered
on the project's home page could contain more extensions by default,
because they target capable platforms. Only some platforms these days are
restricted. Projects using these platforms require custom builds of sqlite
anyway.

Why I want fully-fledged sqlite shell? To be able to easily juggle data
straight from the OS shell, using one-liners, pipelines or bash scripts
without having to compile/enable/whatever my own sqlite shell. To just be
able to download sqlite3 executable and start working.

By offering sqlite shell with most (or all) extensions enabled, the authors
would increase out-of-the-box data processing abilities for all users who
decide to download and just use the shell command. The ability to download
and immediately start working with a really capable shell. Without risk of
bringing malware from other downloads. Without the hassle of rebuilding it
every time I need a new version. Without having to remember one or two
years later upon returning to a stuff that need changes that there was a
custom built sqlite shell there. Consistent across machines, because it was
just downloaded from official download site.

Yes, this is one of possible ways of thinking about consistency. I could
reverse your deduction - why should I want the feature parity between
sqlite embedded in an industrial device and sqlite running in Linux shell
script that gathers data from such devices, processes them differently than
the devices, aggregates them, compresses them and sends somewhere - that's
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)?
>

I will not comment on that one. It's not the level of discussion this
mailing list expects.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users