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

[Till Oliver Knoll]

Am 27.10.22 um 12:46 schrieb Benjamin B (BBenj):

Hello,

Not sure if it was mentioned, but you can also copy the QSQLite 
plugin, change its name and build it with SQLite and all the options 
you need. Then use it as a Qt SQL plug-in and load it instead of the 
default one.


Thank you for that suggestion, that seems to be indeed a viable option 
(besides linking with the "amalgamation code" and adding the required 
functions via sqlite3_create_function - and probably a bit more 
reliable, too, with respect to "version matching").


In the future the binary distribution of the upcoming Qt 6.5 will enable 
the "math" (and other) functions as well in the Qt SQlite plugin.


Cheers, Oliver

___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 26.10.22 um 19:35 schrieb Mike Chinander:
I think it would be nice to have these math functions generally 
available in queries, but I would like to point out that in your 
example code, the angle you are taking the cosine of is a constant 
rather than a value retrieved from the DB.


Uhm... yeah ;)

Maybe I should have used the well-known value "42" to make it more 
obvious that this query was merely meant for "illustrative purposes" ;) 
It is not my "real-world query", to be clear.


Cheers, Oliver

P.S. But indeed, I used this query to quickly test the existence of 
"math functions" via "DB Browser for SQLite". The used "hard-coded" 
coordinates are *very* roughly where I live ;)


___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 26.10.22 um 18:23 schrieb Thiago Macieira:

On Wednesday, 26 October 2022 07:29:15 PDT Till Oliver Knoll wrote:

I have "github actions" that make it very easy to install a
given "stock binary Qt release" on the desired integration platform

Then please ask whoever is providing those binaries to you to enable the math
functions.


I did. "Ask and you shall receive":

https://bugreports.qt.io/browse/QTBUG-108016

:)

Qt 6.5 will provide a Qt SQLite plugin with math (and other) functions 
enabled.


Cheers, Oliver

___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Benjamin B (BBenj)]

Hello,

Not sure if it was mentioned, but you can also copy the QSQLite plugin, change 
its name and build it with SQLite and all the options you need. Then use it as 
a Qt SQL plug-in and load it instead of the default one. It’s documented 
somewhere (creating a custom sql plugin).

I did that in the past to add custom functions in sqlite, among other things. 

Beware of licensing conflicts, maybe? My use of it never extended beyond my own 
so I didn’t bother at the time. If it’s OSS/GPL I guess it’s fine.

Benjamin 

> Le 26 oct. 2022 à 16:01, Till Oliver Knoll  a 
> écrit :
> 
> 
> Am 25.10.22 um 18:40 schrieb Christian Ehrlicher:
>>> Am 24.10.2022 um 19:58 schrieb 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?
 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"
>>> 
>>> From my pov this is the correct way to add new functionality to a Qt 
>>> plugin. Even for github you can install your binary Qt distribution and 
>>> then replace the plugin with your own.
>>> 
>> Yes, but that would imply that I had a recompiled Qt SQLite plugin in the 
>> first place, and that I would then also "check-in" somehow into my source 
>> repository / include into the "github action build step". Something I'd like 
>> to avoid, if possible.
>> 
>> Also, I am also trying to avoid to recompile Qt on my desktop(s): it is a 
>> simple "one man open source app", and I try to keep the development process 
>> (done in my spare time) as simple as possible, for the time being. Or in 
>> other words: it's been more than a decade ago since I compiled Qt from 
>> source (what was that, perhaps Qt 4.x?), but that was in a "professional 
>> environment" ;) I don't even have all the required SKDs installed, 
>> expecially not on Windows (the target platform - because the given "flight 
>> simulator" runs there, too - however I mostly develop on macOS ;))
>> 
>> In short: I am looking for a solution that - if possible - avoids having to 
>> (re-)compile Qt from source. For no other reason other than "I am lazy" ;)
>> 
>> Are there any disadvantages when the math functions are enabled? Esp. 
>> regarding to speed?
>> 
> I can't tell for sure, since I do have little experience with SQLite apart 
> from using it, but I would not expect any speed issues when enabling those 
> "math functions".
> 
> Certainly https://www.sqlite.org/lang_mathfunc.html does not mention anything 
> like this. But yes, the code size would grow by some. So if the goal of the 
> Qt binary distribution was "minimal size" (which I don't believe is a 
> particular goal - developers that /really/ care about size recompile Qt 
> anyway with their own custom options, I would expect) then one could argue 
> that all external libraries would also be compiled with a "minimal feature 
> set". But again, I hope that the "omission" of the 
> -DSQLITE_ENABLE_MATH_FUNCTIONS wasn't a concious choice.
> 
> But perhaps someone else with more experience with SQLite could shed some 
> light on this or has some experience why enabling this "math option" by 
> default could be a bad choice?
> 
> 
> 
>> Feel free to open a bug report so we can discuss if this flag can be added 
>> by default.
>> 
> I just created a suggestion report here, if anyone wants to contribute 
> information about those "math functions":
> 
> https://bugreports.qt.io/browse/QTBUG-108016
> 
> 
> 
> Thanks for your reply!
> 
>   Oliver
___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 26.10.22 um 16:01 schrieb Till Oliver Knoll:


I just created a suggestion report here, if anyone wants to contribute 
information about those "math functions":


https://bugreports.qt.io/browse/QTBUG-108016


https://codereview.qt-project.org/c/qt/qtbase/+/440378


That was... quick :)

Thanks!

___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Thiago Macieira]

On Wednesday, 26 October 2022 07:29:15 PDT Till Oliver Knoll wrote:
> I have "github actions" that make it very easy to install a
> given "stock binary Qt release" on the desired integration platform

Then please ask whoever is providing those binaries to you to enable the math 
functions. Preferably also pre-build SQLite as a system library and include 
THAT as a separate file too, so you can replace as needed.

-- 
Thiago Macieira - thiago.macieira (AT) intel.com
  Cloud Software Architect - Intel DCAI Cloud Engineering



___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 25.10.22 um 21:49 schrieb Thiago Macieira:

On Monday, 24 October 2022 10:58:56 PDT Till Oliver Knoll wrote:

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?

Solution #1: compile Qt with -system-sqlite (and provide said system sqlite).


Recompiling Qt is something that I try to avoid for now - especially 
given that I have "github actions" that make it very easy to install a 
given "stock binary Qt release" on the desired integration platform 
(that I currently only use for "code analysis", but not actually 
building any binaries - "nightly builds" - meant for distribution).


Or in other words: I am trying to keep my "required build setup" as 
simple as possible.


So compiling the "SQLite amalgation" sources (sqlite3.h, sqlite3.c) 
together with my app and use the *sqlite3_create_function* to add the 
desired "sin, cos and pow" etc. functions seems to be "the lesser evil" 
- albeit also way more fragile (keyword: "SQLite versions need to 
match") than properly recompiling Qt / the Qt SQLite plugin with my own 
desired flags.



This applies to ANY use of SQLite, whether you need the math functions or not.


I am not quite sure whether I understand this statement: what exactly 
does apply to "any use of SQLite"? For instance I can perfectly use the 
provided stock binary Qt SQLite plugin on all platforms (for me: 
Windows, macOS, Linux), without having to recompile any Qt related 
source code - for as long as I am not using math functions, of course. 
So why exactly do you say "whether you need the math functions or not"?


Best,

  Oliver

P.S. On a personal note: Hello Thiago! If there is something "more 
constant than constexpr in C++", then it is Thiago replying to all sorts 
of (broad) topics here on the list (you probably don't remember me, and 
that's okay - it's been a while since I have been actively participated 
on this list ;))
___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 25.10.22 um 18:40 schrieb Christian Ehrlicher:

Am 24.10.2022 um 19:58 schrieb 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"



From my pov this is the correct way to add new functionality to a Qt 
plugin. Even for github you can install your binary Qt distribution 
and then replace the plugin with your own.


Yes, but that would imply that I had a recompiled Qt SQLite plugin in 
the first place, and that I would then also "check-in" somehow into my 
source repository / include into the "github action build step". 
Something I'd like to avoid, if possible.


Also, I am also trying to avoid to recompile Qt on my desktop(s): it is 
a simple "one man open source app", and I try to keep the development 
process (done in my spare time) as simple as possible, for the time 
being. Or in other words: it's been more than a decade ago since I 
compiled Qt from source (what was that, perhaps Qt 4.x?), but that was 
in a "professional environment" ;) I don't even have all the required 
SKDs installed, expecially not on Windows (the target platform - because 
the given "flight simulator" runs there, too - however I mostly develop 
on macOS ;))


In short: I am looking for a solution that - if possible - avoids having 
to (re-)compile Qt from source. For no other reason other than "I am 
lazy" ;)


Are there any disadvantages when the math functions are enabled? Esp. 
regarding to speed?


I can't tell for sure, since I do have little experience with SQLite 
apart from using it, but I would not expect any speed issues when 
enabling those "math functions".


Certainly https://www.sqlite.org/lang_mathfunc.html does not mention 
anything like this. But yes, the code size would grow by some. So if the 
goal of the Qt binary distribution was "minimal size" (which I don't 
believe is a particular goal - developers that /really/ care about size 
recompile Qt anyway with their own custom options, I would expect) then 
one could argue that all external libraries would also be compiled with 
a "minimal feature set". But again, I hope that the "omission" of the 
-DSQLITE_ENABLE_MATH_FUNCTIONS 
 wasn't a 
concious choice.


But perhaps someone else with more experience with SQLite could shed 
some light on this or has some experience why enabling this "math 
option" by default could be a bad choice?



Feel free to open a bug report so we can discuss if this flag can be 
added by default.


I just created a suggestion report here, if anyone wants to contribute 
information about those "math functions":


https://bugreports.qt.io/browse/QTBUG-108016


Thanks for your reply!

  Oliver
___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Till Oliver Knoll]

Am 25.10.22 um 05:24 schrieb Tony Rietwyk:


Hi Oliver,

Can't answer your broader question, but I suspect the reason you are 
seeing "No query Unable to fetch row" - error code: "" is because you 
are not checking for an error after the prepare.


Oh my! I wasn't even aware that a call to QSqlQuery::prepare would 
already validate the given query! And just for my own embarassment I 
also noticed that I am even ignoring the boolean return value of that 
method. And while I do compile my application with various compilers on 
Windows, macOS and Linux and have various "Clang-Tidy and Clazy" and 
some other "modern/core C++" checks enabled, none is complaining so far 
about this "ignored return statement" so far (will go and see whether 
other checks - if enabled - will warn about this. I am pretty sure that 
there must be /some/ code validator test that checks for ignored return 
values).


(In my little defense: I am not executing any user-provided SQL. All SQL 
in my application is hand-crafted and "hardcoded" SQL, and any failure 
would indicate a "programming error" anyway, that hopefully would be 
detected during manual testing - I am talking about a "one man open 
source app", so this is acceptable for my given situation ;))


For the record, this is also (of course, as usually in an excellent 
manner) documented here, so for the record:


https://doc.qt.io/qt-6/qsqlquery.html#prepare

Even noting that "Some databases choose to delay preparing a query until 
it is executed the first time. In this case, preparing a syntactically 
wrong query succeeds, but every consecutive exec 
() will fail."


The fact that the "prepare" already validates the SQL is actually good 
news, because it could help me to "test at runtime" whether the given 
SQLite plugin would support sin(), cos() etc., and if not I could fall 
back to some simpler "distance formula".


So thank you so much to making me aware that I should - at least - also 
check the result of the prepare() statement (even if it is "hardcoded 
and tested SQL"). ;)


Cheers, Oliver
___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Thiago Macieira]

On Monday, 24 October 2022 10:58:56 PDT Till Oliver Knoll wrote:
> 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?

Solution #1: compile Qt with -system-sqlite (and provide said system sqlite).

This applies to ANY use of SQLite, whether you need the math functions or not.

-- 
Thiago Macieira - thiago.macieira (AT) intel.com
  Cloud Software Architect - Intel DCAI Cloud Engineering



___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Christian Ehrlicher]

Am 24.10.2022 um 19:58 schrieb 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"



From my pov this is the correct way to add new functionality to a Qt
plugin. Even for github you can install your binary Qt distribution and
then replace the plugin with your own.

Are there any disadvantages when the math functions are enabled? Esp.
regarding to speed?

Feel free to open a bug report so we can discuss if this flag can be
added by default.


Christian
___
Interest mailing list
Interest@qt-project.org
https://lists.qt-project.org/listinfo/interest

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

[Tony Rietwyk]

Hi Oliver,

Can't answer your broader question, but I suspect the reason you are 
seeing "No query Unable to fetch row" - error code: "" is because you 
are not checking for an error after the prepare. Hopefully, you will 
then the expected 'no such function' error and can act on it accordingly.


Regards,

Tony

On 25/10/2022 4:58 am, Till Oliver Knoll wrote:


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:"

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

[Till Oliver Knoll]

Am 24.10.22 um 19:58 schrieb Till Oliver Knoll:


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:"