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 <kmedc...@dessus.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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 <kmedc...@dessus.com>
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>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 integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).
>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well. It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.
>---
>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
>_______________________________________________
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
