Re: Allow inclusion of generate_series function

classic Classic list List threaded Threaded
7 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
Reply | Threaded
Open this post in threaded view
|

Re: Allow inclusion of generate_series function

sub sk79
Hi,

I think both your requirements and Dr Hipp's point-of-view are valid if we
continue to see SQLite as just a tool. Yes, SQLite is an amazing tool for
all the things it does and especially for a great design which almost never
stands in the way of user customization and extension.
However, SQLite's user base has grown so much that it should not be seen
just as a tool but also as a business with multiple use-cases to which it
is applied.

The question of which features to enable by default to please everyone is
not answerable if one continues to see it as a tool.
However, if seen as a business, there may be a solution possible. The
canonical way businesses approach this is to rely on its ecosystem  -
partners, resellers, app-stores etc. Such an approach is a win-win-win for
core-tool-builder (by helping keep focus on key-features), customers
(getting niche features without breaking a sweat) and partners. But this
approach needs a willingness to nurture the ecosystem by providing , as
needed, tech support, access to paying customer lists with niche
requirements, funding, guidance etc - possibly in exchange for a cut and
strong control over ecosystem tool's testing, metrics etc - in short, a
Partner Program.

I built one such SQLite ecosystem solution called StepSqlite - an advanced
PL/SQL compiler  which is backward compatible with subset of Oracle-TM
PL/SQL. It is full of features which will blow your mind - one of the
features, relevant here, is automatic detection and setting of SQLite
pragmas and extensions  based on user code. You simply write PL/SQL-like
code for DB access and StepSqlite customizes a SQLite library baked with
your code(translated to C++) and make it available to you as a shared
library for download which can be used in your app(s) - which in turn can
be written in any major client language and for any major OS without any
bridge-layer (No JDBC-ODBC etc needed). I designed it specifically to
target requirements like your's:

> 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 ...
> Consistent across machines...
>

There are several such power features in StepSqlite (Pls. see this Google
doc link for a list of StepSqlite features:
https://docs.google.com/document/d/1037VTdEhuGYi8D6vVQ2KOD86fn9-qt4WVYVos9lpBqE/edit?usp=sharing)
- I even  added window-functions one year before SQLite core - and IMHO, is
still a better value than core-version (because it works on older SQLite
versions using just CTE , does not add complexity to core, has more
included window-functions and is dead-easy to write custom ones. for ex,

                                FUNCTION MyAvg(val float) RETURN float
ANALYTIC is sum(val)/count(val);

Needless to say, IMO, StepSqlite has a great value proposition but I am
having a hard time funding it myself and reaching a critical mass of
customers.
If SQLite had a partner program I could bring it to market much faster and
it would be a win-win-win instead of loose-loose-loose (SQLite core gets
bulky loosing its key USP)-(users get upset because the required features
take a lot of hand-customization)-(well-wishing potential partners like me
languish).

And I am just one such ecosystem solution of many: there are other great
ones: for ex, bedrockdb, litereplica etc which would probably benefit from
a well defined SQLite partner program.

In any case, that would be my go-to solution for the issue at hand: SQLite
partner program.

Best Regards,
S Kashikar



On Tue, Dec 11, 2018 at 6:47 AM Digital Dog <[hidden email]> wrote:

> On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf <[hidden email]> wrote:
>
> The issue is not limited to generate_series (although that one seems to be
> pain point for many users). ... Projects using these platforms require
> custom builds of sqlite
> anyway.
>
> 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.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users