Native math function support for SQLite
(1) By anonymous on 2020-06-07 22:52:43 [link] [source]
Hello SQLite developers,
(This is a feature request)
I know that there is this "extension-functions.c" contribution available in the "Contributed files" page, but I think, nevertheless, that usual math functions (sqrt, exp, sin, cos, ..., PI constant, degree to radian conversion, ...) should be natively supported by SQLite.
I wanted to use SQLite this day to calculate some distance between GPS positions stored in rows of a big CSV file. I finally abandoned this solution because I couldn't do it except by compiling the previously mentioned contribution file (and I won't do that, because I don't want it to be done in a production environment).
For sure, there is many other products to help me to do that, but the lightness and portability of SQLite was great for my use case. I finally did my computation on a PostgreSQL instance, but I actually hadn't the need of all the features of a big RDBMS server for this.
In my opinion, in this already started data century, processing statistical computation or computation on geographic data will be more and more common. People doing this eventually won't have the possibility or the time to instantiate and maintain a database within a big RDBMS server, or to compile an external file. So maybe I'm not the only one to have the need of these math functions :-) .
Thank you for the attention you paid on this message, and thank you more if you decide to implement this feature one day ;-) .
(2) By Stephan Beal (stephan) on 2020-06-07 23:08:45 in reply to 1 [link] [source]
that usual math functions (sqrt, exp, sin, cos, ..., PI constant, degree to radian conversion, ...) should be natively supported by SQLite.
The problem with that is portability. The libmath routines are defined by C99 but not C89. Thus any build of sqlite which uses them could not be built on pure C89 platforms. (sqlite's use if long long is also not strictly C89-compatible, but that type is supported by essentially every compiler.)
(5) By Warren Young (wyoung) on 2020-06-08 11:37:27 in reply to 2 [source]
The libmath routines are defined by C99 but not C89.
Everything on this page not marked C99 is in C89.
Most of what you quoted is included, primarily excepting π, but you can get that at runtime with
acos(-1) or other identities. That in turn gets you degree to radian conversion: π/180°.
A better reason for SQLite to not include math: the size of the soft FP lib on systems without hard FP, like the ARM Cortex M0, machines where SQLite often makes the most sense of the restricted set of options available.
(3.1) By Larry Brasfield (LarryBrasfield) on 2020-06-08 07:45:31 edited from 3.0 in reply to 1 [link] [source]
I answer for 3-4 reasons which may be inferred from what follows.
The 'Lite' part of SQLite is part of what contributes to its ubiquity on smaller devices. Having the extension mechanism in place is a great solution to the tension between 'Lite' and full-featured. Of course, it could never be full-featured for every application because there is a practically infinite set of functions that might be useful across the large set of SQLite applications.
It is close to child's play for a developer to add functions to SQLite which, for a given build, act just as if they were 'natively supported'. Since you apparently are not a developer, (because you ask for somebody else to add what you could easily add yourself if you were), I suggest that you ask a developer friend or favor-debtor to look at the code, 'shell.c', that is included in the distributions and point out what parts to emulate so that your favored math functions can be added in a near-native way (as far as you will be able tell).
I wonder, when you opine that those transcendental math functions "should be natively supported by SQLite', whether you are aware that SQLite is used on machines which do not have hardware support for floating point arithmetic. Does that fact affect your "should" thinking?
Finally, in addition to some lecturing, a few tips: Take a look at SpatiaLite and see if that is not closer to what you seem to believe SQLite should be. However, if it is, be careful to not begrudge its departure from lightness relative to SQLite. Or, if you prefer the sqlite3 shell as it is, except with a few more math functions you value, consider putting something like
alias sqlite3_math=sqlite3 -cmd ".load sqlmath"
in your shell's startup script. Or, if you have a mess of customizations, (or favor a shell incapable of 'alias' or equivalent), put something like
.load sqlmath .load fileio .load noop .mode tabs
into a file named '.sqliterc' located in your home directory, to be read whenever you invoke the sqlite3 shell.
(4) By Gunter Hick (gunter_hick) on 2020-06-08 06:55:06 in reply to 1 [link] [source]
There is no necessity to "compile the contribution file in a production environment". It is quite easy and well documented how to compile a loadable extension in your development environment. The loadable extension is then loaded in the production environment, yielding access to the math functions. It is also easy and well documented how to compile an extension into sqlite in your development environment and have it available inthe production environment without loading an extension.
(6) By anonymous on 2020-06-08 17:42:42 in reply to 1 [link] [source]
I fully agree with the portability argument. That's one of the biggest strengths of your product. I understand you want to keep it and protect it.
I actually am an experienced developer. I'm trying to think with the deployment and the production in mind.
From a deployment point of view, I would personally prefer to use SQLite as a pure CLI tool which can be installed with the OS package manager, and not to have to deal with C files and their compilation...
From a security point of view, and with the understanding I have of it, it seems to me that deploying a module which is compiled from an external, unidentified and unversioned code file could be refused in a large or relatively secured environment.
SpatiaLite perfectly fit my needs, so I will use it.
Thank you for the tips and answers.
(7) By tom (younique) on 2020-06-08 20:07:07 in reply to 6 [link] [source]
From the portability point of view, at least basic math (such as Power and Sqrt) should be included in the core. I'm using the extension functions as a module and have views doing logarithmic calculations. I cannot give the database to anyone else without instructing him to use the math extensions module as well - which sometimes ain't easy, e.g. with PHP.
(8) By Chris F Carroll (chrisfcarroll) on 2020-11-05 09:37:23 in reply to 1 [link] [source]
Perhaps what's really wanted here is easily findable pre-compiled binaries for those platforms- mac and windows - where it is common even for developers to have never used a C compiler. I could put such things on a github repo, but I suppose the sqlite repo is the obvious place to look for them? That, at least, is what got me to this page.
(9) By jackie2 (jackie3) on 2022-05-09 11:18:40 in reply to 1 [link] [source]
Thanks for post