[Interest] Use SQLite math functions (-DSQLITE_ENABLE_MATH_FUNCTIONS) like sin and cos?

[Till Oliver Knoll]

Dear all,

What is the least complicated way to enable the math functions that come 
with SQLite, when it is compiled with the -DSQLITE_ENABLE_MATH_FUNCTIONS 
switch?

My empirical experiments with the binary Qt 5.15.2 (open source) release 
(but also some tests with the lastest Qt 6.4 binary release, for either 
Windows or macOS) suggest that the SQLite plugin that ships with Qt does 
not enable those "built in mathematical functions", as described here:

https://www.sqlite.org/lang_mathfunc.html

So do I:

  * Recompile the Qt SQLite plugin, with the desired
    -DSQLITE_ENABLE_MATH_FUNCTIONS flag?
      o Disadvantages (?):
          + Need to download Qt source code
          + Probably complicates the build process... (I have "build
            actions" on github.com that currently "only" need to install
            the binary Qt distribution)
          + Feels a bit "overkill"
  * Try to get the "SQLite DB handle" and add my own sin(), cos() and
    power() functions?
      o As described here:
        https://forum.qt.io/topic/71210/sqlite-user-define-functions/3
          + Essentially "QSqlDatabase::driver->handle()", some voodoo
            casting and using sqlite3_create_function()
      o Advantages:
          + Probably can be achieved using the "stock" SQLite plugin
            that ships with Qt, "programatically"
      o Disadvantages (?):
          + Still need to get hold of some SQLite header (for
            sqlite_create_function etc.)
          + Fragile? What if the header mismatches (in the future) with
            the shipped Qt SQLite plugin?
              # Feels "hacky"...
  * Any other "trick" to add those "math extensions" to an existing Qt
    SQLite plugin?

The second option seems more attractive than the first, but nevertheless 
a bit "fragile" (and I have to add functions that would essentially 
"already be in SQLite" (if enabled & compiled)), so feels like 
"re-inventing the wheel"...

Some more details: specifically I am trying to execute a query like:

*select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) * 
cos(radians( 48.0 )), 2) dist ****from   location l;*

(Eventually I want to find locations given by their latitude and 
longitude that are within a certain distance of my given location: 
https://jonisalonen.com/2014/computing-distance-between-coordinates-can-be-simple-and-fast/)

However when compiled into my Qt 5.15.2 application with some current 
"test code" like:.

QSqlQueryquery1;

query1.setForwardOnly(true);

query1.prepare(

"selectpower(l.latitude-48.0,2)+power((l.longitude-8.0)*cos(radians(48.0)),2)dist"

"from location l;"

);

constboolsuccess=query1.exec();
     if(success){
	...
}else{
qDebug()<<"SQL error:"<<query1.lastError().text()<<"- error code:"<<query1.lastError().nativeErrorCode();
}

I get:

SQL error: "No query Unable to fetch row" - error code: ""

When executing the equivalent query in the Qt example app "Qt SQL 
Browser" (this time compiled with Qt 6.4) (which provides a Movies and 
Names table):

select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) * 
cos(radians( 48.0 )), 2) dist from movies l;

then I get a more meaningful (and expected) error message:

"no such function: power Unable to exectute statement"

Conclusion: "No math support in the shipped SQLite plugin, neither in Qt 
5.15.2 (Windows nor macOS) nor Qt 6.4 (again tried both Windows and macOS).

Now when I execute my first query in the app "DB Browser for SQLite 
(https://sqlitebrowser.org/), against my own database schema (with a 
"location" table) again:

select power(l.latitude - 48.0, 2) + power((l.longitude - 8.0) * 
cos(radians( 48.0 )), 2) dist from location l;

I intially also get "no such function: power". However DB Browser for 
SQLite provides the possibility to add "extensions", such as (on macOS);

/Applications/DB Browser for SQLite.app/Contents/Extensions/math.dylib

(To be registered via Preferences or loaded via Tool / Load Extension...)

Now with this "math" extension loaded (I assume this is some DB Browser 
for SQLite specific plugin / extension) my query works as expected (even 
with the somewhat older SQLite 3.35.x). So this goes to illustrate that 
my query is not completely wrong (syntactically)...

So any ideas how to "enable math support" in either the shipped Qt 
SQLite plugin, and/or how to (re-)compile that plugin with the least 
effort, or any other tricks how to make use of "sin, cos, power and 
friends" in SQLite are very welcome. Interestingly I did not find any 
such questions in the Qt interest forums, and some stackoverflow answers 
seem indeed to suggest to use sqlite3_create_function()

Thanks,

Oliver
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20221024/31b3041b/attachment.htm>