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 <ryansmit...@gmail.com> To: sqlite-users@mailinglists.sqlite.org <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 <h...@scigames.at> > To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> > 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 <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 > _______________________________________________ > 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
