Re: [sqlite] Allow inclusion of generate_series function
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 wrote: > On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Thursday, 6 December, 2018 08:23, Nathan Green wrote: >On Tue, Dec 4, 2018 at 11:25 AM Digital Dog wrote: >> On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: >> > On 12/4/18, Digital Dog 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Tue, Dec 4, 2018 at 11:25 AM Digital Dog wrote: > On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: > > > > On 12/4/18, Digital Dog 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: > > On 12/4/18, Digital Dog 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On 12/4/18, Digital Dog 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Allow inclusion of generate_series function
Hi, 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. Thanks, Nathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
