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 <sqlite-users@mailinglists.sqlite.org> 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 <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 ___________________________________________ 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
