search

fr-ser / grafana-sqlite-datasource

Grafana Plugin to enable SQLite as a Datasource
Apache License 2.0
124 stars 17 forks source link

add support for Built-In Mathematical SQL Functions #77

Closed rayures closed 2 years ago

rayures commented 2 years ago

per v3.35 sqlite has by default enabled Built-In Mathematical SQL Functions.

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

installed sqlite v.3.37 on my grafana host, tried a function, but these do not work. query does work when executing it directly in sqlite.

fr-ser commented 2 years ago

Can you provide an example query?

rayures commented 2 years ago

i.e: SELECT (100*POWER(1.01,365)) as balance FROM BALANCE

the 'power' is from the math functions

fr-ser commented 2 years ago

The latest release of the SQLite client that is used by the plugin seems to be on SQLite 3.28.0. Since I need to use the go client it does not look like I can do anything.

Currently latest release: https://github.com/mattn/go-sqlite3/releases/tag/v1.10.0

rayures commented 2 years ago

Isnt this the latest version?

https://pkg.go.dev/github.com/mattn/go-sqlite3?tab=versions

1.14.9

(Didn't look at the sqlite version in that version)

fr-ser commented 2 years ago

Yeah, you are right. It is just not shown as a Github release. The plugin currently uses this version: v1.14.4

I wouldn't expect significant upgrades to the underlying SQLite version between 1.14.9 and v1.14.4 but if you happen to find which version the latest is running I can try upgrading

rayures commented 2 years ago

Well, based on the code, it's currently at 3.36.0.

https://github.com/mattn/go-sqlite3/blob/master/sqlite3-binding.h

Line 127.

fr-ser commented 2 years ago

Neat. I'll test out the new version later and see if the math functions are included 👌🏻

fr-ser commented 2 years ago

I updated to the newer version of the go library and it still did not recognize the power function. I tried using the compile time option SQLITE_ENABLE_MATH_FUNCTIONS but I could not find how to pass this for the go client.

I tried using this extension, that was linked in the golang client docs: https://github.com/dinedal/go-sqlite3-extension-functions But this threw this error: ../../go/pkg/mod/github.com/dinedal/go-sqlite3-extension-functions/go@v0.0.0-20200202173959-679ee1ae1f0b/extension-functions.go:1:18: syntax error: unexpected -, expecting semicolon or newline

To be fair I am not really an expert regarding linking C libraries in go but I am sadly out of idea...

rayures commented 2 years ago

Hmm, to bad. Sort of hoped/expected it would just work with upgrading the sqlite / go package.

https://github.com/mattn/go-sqlite3/pull/926

fr-ser commented 2 years ago

Hmm, weird. They even mention the math functions in the version... I will check again later this week. Maybe I did something wrong during the go package upgrade...

fr-ser commented 2 years ago

I can't get it working... I opened an issue in the underlying library to get see what I am doing wrong... https://github.com/mattn/go-sqlite3/issues/990

rayures commented 2 years ago

Tnx for the update and all the time and work in it. I will also follow #990

fardeadok commented 2 years ago

I didn't understand: is it possible to use pow and sqrt or not? If not, when will it be fixed? I spent a couple of days trying to figure out why sqllite works differently in different applications!

stale[bot] commented 2 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

bearbin commented 2 years ago

There's still a PR pending in go-sqlite3 to add a build tag for maths functions - https://github.com/mattn/go-sqlite3/pull/1037

until then the functions won't work

fr-ser commented 2 years ago

The plugin is using a different sqlite library by now. The linked issue will not have any impact anymore, sadly.

The current library is https://pkg.go.dev/modernc.org/sqlite

rayures commented 2 years ago

Latest does have math functions enabled and they are working (at least the power function).

Just noticing that the grafana widget are loading much slower with latest sqlite Datasource plugin

bearbin commented 2 years ago

@fr-ser didn't see your message for a while, thanks for correcting me. Good to know that the math functions are now working :)

fr-ser commented 2 years ago

Latest does have math functions enabled and they are working (at least the power function).

Haha, I didn't even realize that myself. But with the new sqlite package provider it makes sense that some behavior changed 😅

Just noticing that the grafana widget are loading much slower with latest sqlite Datasource plugin

In case the slow down is noticable and consistent could you open a new issue and describe a bit more what you experience?