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 <h...@scigames.at> To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> 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(<default>,<function>[,<args>]) The implementation will then check if the named function is available; If so, prepare, execute and return the result of "SELECT <function>(<args>)" If not, just return <default>. 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 <sqlite-users@mailinglists.sqlite.org> 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 <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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ 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
