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
_______________________________________________
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest
