Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
2019 WISH LIST --- - A list of libraries that are known to be successfully added to SQLite. - Have a base instance, select some check-boxes, press SUBMIT, something somewhere somehow generates your .DLL, .SO, etc. so that you have everything you want built in - A million dollars - More Pepsi [Check] - No one to tell me that the first and second option are not feasible. It's just a wish list. ;) On Thu, May 23, 2019 at 6:28 PM R Smith wrote: > > And to be clear, I don't think the Math lib would specifically be bloat, > though it's an unneeded piece of added weight for my needs, but I will > accept it happily if it makes most other people happy. But then, if we > start with the math lib, what is next? Sure enough someone will come up > with a very valid next bit to be added, and a next. I understand that > Richard's decision on inclusion is not beyond reproach, but whose would be? > > Put another way, let's say we do add some libraries automatically, > pushing the weight up a nice bit, but they are not the libs you wanted, > would you be happy and consoled? If not, how do you expect anyone else > to be happy with your choice being the chosen implemented? Would you > then rather go back to how it was before? > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
On May 23, 2019, at 4:28 PM, R Smith wrote: > > it is very easy to add things to the base distro, but extremely hard to > impossible to ever take it away again, which means one should only ever "add" > with great caution. Easy fix: -DSQLITE_OMIT_EXTENDED_MATH_LIBRARY Include it by default, but let those on resource-constrained platforms yank it back out. I think the biggest obstacle here is this: *which* math library do we use? If SQLite’s math is based on the platform math library (e.g. libm.so) then it may substantially change behavior across platforms, which goes against one of the SQLite guarantees: your DB file behaves the same way everywhere. If it’s a custom math library, that’ll take a lot of work to get right, and once done, then it’s “bloat” relative to the platform math library. That can in turn be solved with more ifdefs (-DSQLITE_PLATFORM_MATH) but the way off-by-default options are usually handled in SQLite is that they aren’t checked as part of the test suites, so there are option combinations that don’t build a working library, much less give one that’s had all of the options cross-tested against each other. I understand why that’s the case: the alternative is a combinatoric explosion where the number of test configurations is 2^N * M where N is the number of Boolean options and M is the number of the *non*-Boolean option combinations that need to be tested. 16 Boolean options is 64k runs of the test suite to cross-check every option against all possible combinations of the other 15 options! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
On 2019/05/23 1:26 PM, J. King wrote: On May 23, 2019 6:46:52 a.m. EDT, R Smith wrote: This is SQLite. Perhaps some of us could collaborate on a fork called SQLbloat //.. I find this a little condescending. There's a lot of reasons to like SQLite, and the aspect that sways me more than others is not "lite", but "zeroconf". I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not practical, and in the case of PHP, loadable extensions are disabled by default. I as a programmer am at the mercy of what distributions package---which is often SQLite in its default configuration, so a less-lite-but-still-zeroconf SQLessLite as the default configuration would be extremely valuable to me. At the same time, those working in tiny systems still have tons of compile-time options to keep things lean. In short, I'm skeptical that the choices Hwaci have made about what to exclude are necessarily beyond reproach or discussion. Derogatory references to "SQLbloat" really don't further the cause of honest discussion. This is a misunderstanding of my intent, much as the OP misunderstood that I don't consider a function that could confirm the presence of another function to be bloat, I quite welcome that, and I most certainly do not think any decision is beyond reproach. The entire bloat argument is towards the extent of the libraries included automatically. And to be clear, I don't think the Math lib would specifically be bloat, though it's an unneeded piece of added weight for my needs, but I will accept it happily if it makes most other people happy. But then, if we start with the math lib, what is next? Sure enough someone will come up with a very valid next bit to be added, and a next. I understand that Richard's decision on inclusion is not beyond reproach, but whose would be? Put another way, let's say we do add some libraries automatically, pushing the weight up a nice bit, but they are not the libs you wanted, would you be happy and consoled? If not, how do you expect anyone else to be happy with your choice being the chosen implemented? Would you then rather go back to how it was before? Lastly, it is very easy to add things to the base distro, but extremely hard to impossible to ever take it away again, which means one should only ever "add" with great caution. Everybody's needs are different and it is impossible to satisfy all, so I maintain that providing the base SQL functions and having the options for added functionality relegated to every user's personal choice (with multiple ways of achieving it no less) is a good solution. I do however think that having the function-list pragma in the base distro is needed. Understanding dependency shortcomings would then be as easy as a quick query, which is especially useful where SQLite is used through wrappers. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
J. King, on Thursday, May 23, 2019 07:26 AM, wrote... >On May 23, 2019 6:46:52 a.m. EDT, R Smith wrote: > >>This is SQLite. Perhaps some of us could collaborate on a fork called >>SQLbloat and put out standard libs/code/precompileds for versions of >>sqlite with everything - bbq sauce and all, for when you don't need >>Lite >>- then you can specify that the queries are for SQLbloat and they will >>run out the box directly and correctly. Linux distro wars coming to an > >I find this a little condescending. There's a lot of reasons to like > SQLite, and the aspect that sways me more than others is not "lite", but > "zeroconf". I also feel the same way as Mr. King, but, I understand the "bloating" idea. And so, I am happy to have the liteness. But, do I pray every day for a little bloat-ness from SQLite for my own greed? Yes, indeed. >I mainly use SQLite in PHP and Tcl, so using my own SQLite library is > often not practical, and in the case of PHP, loadable extensions are I have been using D and SQlite since 2006. I have prepared tools for myself, work, friends, church using these two tools and the freeware libraries that I can find. They work perfectly, but I want more. :-) However, I am thrill to continue to use SQLite the way it is. But, again, do I pray for more? Yes. :-) Thanks for the chance to be part of this discussion. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Thank you for bringing topic back to original intent. Clearly much is missing from the latest SQL standard. Debates aside, the URL is equally lacking. Yes, "own" extensions solve much of my needs. On Thu, May 23, 2019, 7:26 AM J. King wrote: > On May 23, 2019 6:46:52 a.m. EDT, R Smith wrote: > > >This is SQLite. Perhaps some of us could collaborate on a fork called > >SQLbloat and put out standard libs/code/precompileds for versions of > >sqlite with everything - bbq sauce and all, for when you don't need > >Lite > >- then you can specify that the queries are for SQLbloat and they will > >run out the box directly and correctly. Linux distro wars coming to an > > I find this a little condescending. There's a lot of reasons to like > SQLite, and the aspect that sways me more than others is not "lite", but > "zeroconf". > > I mainly use SQLite in PHP and Tcl, so using my own SQLite library is > often not practical, and in the case of PHP, loadable extensions are > disabled by default. I as a programmer am at the mercy of what > distributions package---which is often SQLite in its default configuration, > so a less-lite-but-still-zeroconf SQLessLite as the default configuration > would be extremely valuable to me. At the same time, those working in tiny > systems still have tons of compile-time options to keep things lean. > > In short, I'm skeptical that the choices Hwaci have made about what to > exclude are necessarily beyond reproach or discussion. Derogatory > references to "SQLbloat" really don't further the cause of honest > discussion. > > > -- > J. King > ___ > 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] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
On May 23, 2019 6:46:52 a.m. EDT, R Smith wrote: >This is SQLite. Perhaps some of us could collaborate on a fork called >SQLbloat and put out standard libs/code/precompileds for versions of >sqlite with everything - bbq sauce and all, for when you don't need >Lite >- then you can specify that the queries are for SQLbloat and they will >run out the box directly and correctly. Linux distro wars coming to an I find this a little condescending. There's a lot of reasons to like SQLite, and the aspect that sways me more than others is not "lite", but "zeroconf". I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not practical, and in the case of PHP, loadable extensions are disabled by default. I as a programmer am at the mercy of what distributions package---which is often SQLite in its default configuration, so a less-lite-but-still-zeroconf SQLessLite as the default configuration would be extremely valuable to me. At the same time, those working in tiny systems still have tons of compile-time options to keep things lean. In short, I'm skeptical that the choices Hwaci have made about what to exclude are necessarily beyond reproach or discussion. Derogatory references to "SQLbloat" really don't further the cause of honest discussion. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
This ain't quite helpful. Sqlite also supports FTS3/4/5 which I would
personally consider as bloat. So what is the actual "bloat" in supporting a
function that can just check whether a function exists or not. That would
probably add only some bytes to the library.
- Original Message -
From: R Smith
To: sqlite-users@mailinglists.sqlite.org
Sent: Thursday, May 23, 2019, 12:46:52
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Ok, but you can't have it both ways. Either you want to write generic
use-everywhere SQL, in which case you need to stick to the generic
included-everywhere functions,
OR
You want to write specialized specific queries that use either your own
or other UDF's, in which case you must be able to load your own (or
other's) add-on libraries.
If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which
returns the function result, unless it errors out, in which case it
returns Zero, is good programming, then you are making a first-principle
mistake. Because now you add a layer of complexity to the end-system.
The result is Zero - Is this an error, or is this actually the result?
Extra checks needed.
Errors should cause errors so that everyone know it's an error.
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of
thinking and a mistake often made by novice GUI programmers who value
user-experience over truth.
Else what you are asking for is to have your bread buttered on both
sides, which isn't feasible, and which isn't done in any RDBMS engine
(though admittedly the big ones do include most general math functions
as standard, but then they don't need to sometimes run on embedded devices).
This is SQLite. Perhaps some of us could collaborate on a fork called
SQLbloat and put out standard libs/code/precompileds for versions of
sqlite with everything - bbq sauce and all, for when you don't need Lite
- then you can specify that the queries are for SQLbloat and they will
run out the box directly and correctly. Linux distro wars coming to an
SQLite DB near you soon!
Cheers,
Ryan
On 2019/05/23 12:19 PM, Thomas Kurz wrote:
> That doesn't make any difference. Then I could use the extensions-functions.c
> loadable module as well. My database has to work equally well no matter what
> dll and/or extension is used or not.
> Regardless of my application, the problem stays the same when you open the
> database in any other viewer that doesn't have the extension.
> - Original Message -
> From: Hick Gunter
> To: 'SQLite mailing list'
> Sent: Thursday, May 23, 2019, 11:58:10
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
> Put your function into a loadable extension and load it during application
> startup so that it is always available to your code. This does not require
> checking the sqlite3.dll
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 11:41
> An: SQLite mailing list
> Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not
> Implement
> I want to define VIEWs that work equally well regardless of whether a default
> sqlite3.dll or a custom build with built-in extension-functions.c is used.
> Defining my own function as an extension module which checks that case would
> additionally require checking whether a default sqlite3.dll or a custom build
> is used? That doesn't make any sense to me?!?
> - Original Message -
> From: Hick Gunter
> To: 'SQLite mailing list'
> Sent: Thursday, May 23, 2019, 10:02:30
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
> Just write a function that takes a function name, a default value and an
> unspecified number of arguments.
> function_present(,[,])
> The implementation will then check if the named function is available; If so,
> prepare, execute and return the result of "SELECT ()"
> If not, just return .
> This is probably going to make your queries run blindingly slow...
> BTW, what is your use case?
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 08:58
> An: SQLite mailing list
> Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
> Ok, thank you for that hint. But it is still very unconvenient. How can I
> define a view based on your suggestion? I want to have something like
> CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
> - Original Message -
> From: Keith Medcalf
> To: SQLite mailing list
> Sent: Thursday, May 23, 2019, 07:1
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Ok, but you can't have it both ways. Either you want to write generic
use-everywhere SQL, in which case you need to stick to the generic
included-everywhere functions,
OR
You want to write specialized specific queries that use either your own
or other UDF's, in which case you must be able to load your own (or
other's) add-on libraries.
If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which
returns the function result, unless it errors out, in which case it
returns Zero, is good programming, then you are making a first-principle
mistake. Because now you add a layer of complexity to the end-system.
The result is Zero - Is this an error, or is this actually the result?
Extra checks needed.
Errors should cause errors so that everyone know it's an error.
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of
thinking and a mistake often made by novice GUI programmers who value
user-experience over truth.
Else what you are asking for is to have your bread buttered on both
sides, which isn't feasible, and which isn't done in any RDBMS engine
(though admittedly the big ones do include most general math functions
as standard, but then they don't need to sometimes run on embedded devices).
This is SQLite. Perhaps some of us could collaborate on a fork called
SQLbloat and put out standard libs/code/precompileds for versions of
sqlite with everything - bbq sauce and all, for when you don't need Lite
- then you can specify that the queries are for SQLbloat and they will
run out the box directly and correctly. Linux distro wars coming to an
SQLite DB near you soon!
Cheers,
Ryan
On 2019/05/23 12:19 PM, Thomas Kurz wrote:
That doesn't make any difference. Then I could use the extensions-functions.c
loadable module as well. My database has to work equally well no matter what
dll and/or extension is used or not.
Regardless of my application, the problem stays the same when you open the
database in any other viewer that doesn't have the extension.
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Put your function into a loadable extension and load it during application
startup so that it is always available to your code. This does not require
checking the sqlite3.dll
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
I want to define VIEWs that work equally well regardless of whether a default
sqlite3.dll or a custom build with built-in extension-functions.c is used.
Defining my own function as an extension module which checks that case would
additionally require checking whether a default sqlite3.dll or a custom build
is used? That doesn't make any sense to me?!?
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Just write a function that takes a function name, a default value and an
unspecified number of arguments.
function_present(,[,])
The implementation will then check if the named function is available; If so, prepare, execute and
return the result of "SELECT ()"
If not, just return .
This is probably going to make your queries run blindingly slow...
BTW, what is your use case?
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
Ok, thank you for that hint. But it is still very unconvenient. How can I
define a view based on your suggestion? I want to have something like
CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement
select name from pragma_function_list where name == 'M_Pi' collate nocase;
returns the name of the function if it exists. See pragma function_list
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
-Original Message-
From: sqlite-users [mailto:sqlite-users-
boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
Sent: Wednesday, 22 May, 2019 22:19
To: SQLite mailing list
Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
I agree in that not every math function can be included by default.
My problem, however, is that I cannot know whether a user uses my
s
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
That doesn't make any difference. Then I could use the extensions-functions.c
loadable module as well. My database has to work equally well no matter what
dll and/or extension is used or not.
Regardless of my application, the problem stays the same when you open the
database in any other viewer that doesn't have the extension.
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Put your function into a loadable extension and load it during application
startup so that it is always available to your code. This does not require
checking the sqlite3.dll
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
I want to define VIEWs that work equally well regardless of whether a default
sqlite3.dll or a custom build with built-in extension-functions.c is used.
Defining my own function as an extension module which checks that case would
additionally require checking whether a default sqlite3.dll or a custom build
is used? That doesn't make any sense to me?!?
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Just write a function that takes a function name, a default value and an
unspecified number of arguments.
function_present(,[,])
The implementation will then check if the named function is available; If so,
prepare, execute and return the result of "SELECT ()"
If not, just return .
This is probably going to make your queries run blindingly slow...
BTW, what is your use case?
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
Ok, thank you for that hint. But it is still very unconvenient. How can I
define a view based on your suggestion? I want to have something like
CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement
select name from pragma_function_list where name == 'M_Pi' collate nocase;
returns the name of the function if it exists. See pragma function_list
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM
>bar
>- Original Message -
>From: Keith Medcalf
>To: SQLite mailing list
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>Traditionally "math library" functions provided by the various language
>runtimes were not included becase this would introduce dependancies on
>a "math library". While this is available on *most* platforms, it is
>not available on *all* platforms which SQLite3 can be compiled for out
>of the box. Furthermore the implementation of some transcendentals may
>be intrinsic on some CPU's and require huge amounts of library code on
>others. Statistical functions are not included because, well, they
>require complex implementations to get right.
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5 rounding
>rather than half-even rounding), the average function is rather simple
>in implementation and suffers from trivally triggered sources of
>computational error (it uses sum/count rather than
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Put your function into a loadable extension and load it during application
startup so that it is always available to your code. This does not require
checking the sqlite3.dll
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
I want to define VIEWs that work equally well regardless of whether a default
sqlite3.dll or a custom build with built-in extension-functions.c is used.
Defining my own function as an extension module which checks that case would
additionally require checking whether a default sqlite3.dll or a custom build
is used? That doesn't make any sense to me?!?
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Just write a function that takes a function name, a default value and an
unspecified number of arguments.
function_present(,[,])
The implementation will then check if the named function is available; If so,
prepare, execute and return the result of "SELECT ()"
If not, just return .
This is probably going to make your queries run blindingly slow...
BTW, what is your use case?
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
Ok, thank you for that hint. But it is still very unconvenient. How can I
define a view based on your suggestion? I want to have something like
CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement
select name from pragma_function_list where name == 'M_Pi' collate nocase;
returns the name of the function if it exists. See pragma function_list
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM
>bar
>- Original Message -
>From: Keith Medcalf
>To: SQLite mailing list
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>Traditionally "math library" functions provided by the various language
>runtimes were not included becase this would introduce dependancies on
>a "math library". While this is available on *most* platforms, it is
>not available on *all* platforms which SQLite3 can be compiled for out
>of the box. Furthermore the implementation of some transcendentals may
>be intrinsic on some CPU's and require huge amounts of library code on
>others. Statistical functions are not included because, well, they
>require complex implementations to get right.
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5 rounding
>rather than half-even rounding), the average function is rather simple
>in implementation and suffers from trivally triggered sources of
>computational error (it uses sum/count rather than successive
>approximation to the mean), and many other limitations exist in the
>builtin implementations of many functions).
>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs. For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distribut
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
I want to define VIEWs that work equally well regardless of whether a default
sqlite3.dll or a custom build with built-in extension-functions.c is used.
Defining my own function as an extension module which checks that case would
additionally require checking whether a default sqlite3.dll or a custom build
is used? That doesn't make any sense to me?!?
- Original Message -
From: Hick Gunter
To: 'SQLite mailing list'
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Just write a function that takes a function name, a default value and an
unspecified number of arguments.
function_present(,[,])
The implementation will then check if the named function is available;
If so, prepare, execute and return the result of "SELECT ()"
If not, just return .
This is probably going to make your queries run blindingly slow...
BTW, what is your use case?
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
Ok, thank you for that hint. But it is still very unconvenient. How can I
define a view based on your suggestion? I want to have something like
CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement
select name from pragma_function_list where name == 'M_Pi' collate nocase;
returns the name of the function if it exists. See pragma function_list
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar
>- Original Message -
>From: Keith Medcalf
>To: SQLite mailing list
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library". While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box. Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others. Statistical functions are not
>included because, well, they require complex implementations to get
>right.
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).
>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs. For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the who
Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement
Just write a function that takes a function name, a default value and an
unspecified number of arguments.
function_present(,[,])
The implementation will then check if the named function is available;
If so, prepare, execute and return the result of "SELECT ()"
If not, just return .
This is probably going to make your queries run blindingly slow...
BTW, what is your use case?
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement
Ok, thank you for that hint. But it is still very unconvenient. How can I
define a view based on your suggestion? I want to have something like
CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement
select name from pragma_function_list where name == 'M_Pi' collate nocase;
returns the name of the function if it exists. See pragma function_list
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar
>- Original Message -
>From: Keith Medcalf
>To: SQLite mailing list
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com wrote:
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library". While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box. Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others. Statistical functions are not
>included because, well, they require complex implementations to get
>right.
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).
>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs. For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.
>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite". There are other drawbacks as well. For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.
>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrat
