Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Markus Neteler wrote
On Sat, Dec 20, 2014 at 5:49 PM, Helmut Kudrnovsky lt;
hellik@
gt; wrote:
Markus Neteler wrote
Works.
Submitted to trunk in r63238. Please test (tomorrow) also on Windows
if possible (the name will be something like libsqlitefunctions.dll
which you need to provide to v.db.select).
any idea where to find/to get libsqlitefunctions.dll for testing?
Some folks suggest to compile it (one-liner):
http://stackoverflow.com/a/20984485/452464
Markus
libsqlitefunctions.dll now self compiled and tested:
---
v.db.update --verbose map=myprecip@user1 layer=1 column=logjuly
query_column=log(jul)
sqliteextra=C:\OSGeo4Wdev\src\sqliteextension\libsqlitefunctions.dll
SQL: SELECT
load_extension('C:\OSGeo4Wdev\src\sqliteextension\libsqlitefunctions.dll');
UPDATE myprecip SET logjuly=log(jul)
(Sun Dec 28 10:38:51 2014) Befehl ausgeführt (1 Sek)
---
v.report map=myprecip@user1 option=coor
cat|station|lat|long|elev|jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|annual|logjuly|x|y|z
1|310090|35.39917|-80.19944|185.928|110.998|92.456|122.682|84.582|107.696|111.252|132.842|104.902|113.03|90.424|83.82|83.82|1236.98|4.88916045210132|500657.105808135|183600.883438444|0.0
2|310184|35.20139|-83.83861|533.0952|182.118|154.686|177.038|131.572|136.398|140.716|127|139.7|113.03|89.154|135.382|152.908|1678.94|4.84418708645859|169208.722850323|171737.46864344|0.0
[...]
---
nice, seems to work.
should we add a note to the manual where extension-functions.c could be
downloaded (https://www.sqlite.org/contrib) and how it is compiled (on
windows)? or somewhere in the wiki?
-
best regards
Helmut
--
View this message in context:
http://osgeo-org.1560.x6.nabble.com/How-to-calculate-log-in-v-db-update-with-SQLite-backend-tp5173670p5179336.html
Sent from the Grass - Dev mailing list archive at Nabble.com.
___
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
should we add a note to the manual where extension-functions.c could be downloaded (https://www.sqlite.org/contrib) and how it is compiled (on windows)? or somewhere in the wiki? wiki entry added: http://grasswiki.osgeo.org/wiki/Build_SQLite_extension_on_windows - best regards Helmut -- View this message in context: http://osgeo-org.1560.x6.nabble.com/How-to-calculate-log-in-v-db-update-with-SQLite-backend-tp5173670p5179350.html Sent from the Grass - Dev mailing list archive at Nabble.com. ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On Sat, Dec 20, 2014 at 5:49 PM, Helmut Kudrnovsky hel...@web.de wrote: Markus Neteler wrote Works. Submitted to trunk in r63238. Please test (tomorrow) also on Windows if possible (the name will be something like libsqlitefunctions.dll which you need to provide to v.db.select). any idea where to find/to get libsqlitefunctions.dll for testing? Some folks suggest to compile it (one-liner): http://stackoverflow.com/a/20984485/452464 Markus ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Markus Neteler wrote Works. Submitted to trunk in r63238. Please test (tomorrow) also on Windows if possible (the name will be something like libsqlitefunctions.dll which you need to provide to v.db.select). any idea where to find/to get libsqlitefunctions.dll for testing? - best regards Helmut -- View this message in context: http://osgeo-org.1560.x6.nabble.com/How-to-calculate-log-in-v-db-update-with-SQLite-backend-tp5173670p5178691.html Sent from the Grass - Dev mailing list archive at Nabble.com. ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On Fri, Nov 28, 2014 at 11:18 AM, Markus Neteler nete...@osgeo.org wrote: On Thu, Nov 20, 2014 at 10:53 AM, Markus Neteler nete...@osgeo.org wrote: ... Trying with my locally modified v.db.update GRASS 7.1.svn (nc_spm_08_grass7):~ g.copy vect=precip_30ynormals,myprecip_30ynormals GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.addcolumn myprecip_30ynormals column=logjuly double precision GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.update myprecip_30ynormals column=logjuly qcolumn=log(jul) sqliteextra=/home/neteler/software/sqlite_extensions/libsqlitefunctions.so GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.select myprecip_30ynormals columns=jul,logjuly jul|logjuly 132.842|4.88916045210132 127|4.84418708645859 124.206|4.82194147751127 104.648|4.65060233738593 98.298|4.58800368106618 ... Works. Submitted to trunk in r63238. Please test (tomorrow) also on Windows if possible (the name will be something like libsqlitefunctions.dll which you need to provide to v.db.select). Can I backport it? Markus ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Hi, On Thu, Nov 20, 2014 at 10:53 AM, Markus Neteler nete...@osgeo.org wrote: ... Trying with my locally modified v.db.update GRASS 7.1.svn (nc_spm_08_grass7):~ g.copy vect=precip_30ynormals,myprecip_30ynormals GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.addcolumn myprecip_30ynormals column=logjuly double precision GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.update myprecip_30ynormals column=logjuly qcolumn=log(jul) sqliteextra=/home/neteler/software/sqlite_extensions/libsqlitefunctions.so GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.select myprecip_30ynormals columns=jul,logjuly jul|logjuly 132.842|4.88916045210132 127|4.84418708645859 124.206|4.82194147751127 104.648|4.65060233738593 98.298|4.58800368106618 ... Works. Submitted to trunk in r63238. Please test (tomorrow) also on Windows if possible (the name will be something like libsqlitefunctions.dll which you need to provide to v.db.select). Markus ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On 20/11/14 08:46, Moritz Lennert wrote:
On 19/11/14 19:12, Vaclav Petras wrote:
On Wed, Nov 19, 2014 at 12:59 PM, Markus Neteler nete...@osgeo.org
mailto:nete...@osgeo.org wrote:
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
ERROR: Error while executing: 'SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so')'
Traceback (most recent call last):
...
Process ended with non-zero return code 1. See errors in the (error)
output.
No idea what's disliked here in:
SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');
UPDATE meuse_voronoi SET logzinc=log(zinc);
Any hints?
Loading user defined function is not considered completely safe, so it
is disabled by default. I think this is not an issue for GRASS GIS.
You have to enable it somehow. It seems that enable_load_extension() is
the way.
Right.
But this depends on the installation, i.e. how sqlite3 was compiled.
Here in Debian Testing I do not need to activate anything, so I assume
that it's activated by default.
However, I'm not sure if sqlite needs to be compiled with this option by
default. I think we could activate it in the sqlite driver.
Below is a proposal after a very superficial reading of the docs and
code, so no guarantees (and I cannot really test here since it seems
enabled by default).
However, this means that we enable this automatically for each sqlite db
opened by GRASS...
Moritz
Index: db/drivers/sqlite/db.c
===
--- db/drivers/sqlite/db.c (révision 62792)
+++ db/drivers/sqlite/db.c (copie de travail)
@@ -110,6 +110,9 @@
return DB_FAILED;
}
+/* enable loading of extensions */
+sqlite3_enable_load_extension(sqlite, 1);
+
/* set the sqlite busy handler */
sqlite3_busy_handler(sqlite, sqlite_busy_callback, NULL);
___
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Congrats, Moritz. On Thu, Nov 20, 2014 at 9:22 AM, Moritz Lennert mlenn...@club.worldonline.be wrote: Below is a proposal after a very superficial reading of the docs and code, so no guarantees (and I cannot really test here since it seems enabled by default). However, this means that we enable this automatically for each sqlite db opened by GRASS... Maybe an issue, maybe not. The sqlite3 cmd line software has it enabled, too. Index: db/drivers/sqlite/db.c === --- db/drivers/sqlite/db.c (révision 62792) +++ db/drivers/sqlite/db.c (copie de travail) @@ -110,6 +110,9 @@ return DB_FAILED; } +/* enable loading of extensions */ +sqlite3_enable_load_extension(sqlite, 1); + /* set the sqlite busy handler */ sqlite3_busy_handler(sqlite, sqlite_busy_callback, NULL); Trying with my locally modified v.db.update GRASS 7.1.svn (nc_spm_08_grass7):~ g.copy vect=precip_30ynormals,myprecip_30ynormals GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.addcolumn myprecip_30ynormals column=logjuly double precision GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.update myprecip_30ynormals column=logjuly qcolumn=log(jul) sqliteextra=/home/neteler/software/sqlite_extensions/libsqlitefunctions.so GRASS 7.1.svn (nc_spm_08_grass7):~ v.db.select myprecip_30ynormals columns=jul,logjuly jul|logjuly 132.842|4.88916045210132 127|4.84418708645859 124.206|4.82194147751127 104.648|4.65060233738593 98.298|4.58800368106618 ... Works. Markus ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On 18/11/14 22:34, Markus Neteler wrote: Hi, playing around with the Meuse dataset about soil contamination I attempted to calculate that right away but...: v.db.update meuse_voronoi column=logzinc qcolumn=log(zinc) DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log ERROR: Error while executing: 'UPDATE meuse_voronoi SET logzinc=log(zinc)' After some online research I found that I would need to tune my local SQLite installation with https://sqlite.org/contrib/download/extension-functions.c extension-functions.c (50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07 Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism. Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile. Alternative: use pysqlite and define an own function. Which way to go? Do you mean for your specific problem or generally in GRASS ? I don't think that we should start creating (and maintaining) our own version of functions for specific backends. Users that need them should either use the options provided by the backend, in this case the extension functions, or should switch backends. Moritz ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On Wed, Nov 19, 2014 at 1:13 PM, Moritz Lennert
mlenn...@club.worldonline.be wrote:
...
No, since version 3.6 you can compile the extension as a shared library and
then activate it:
$ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 test.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter .help for usage hints.
sqlite create table test (value int);
sqlite insert into test VALUES (1), (159), (257);
sqlite select value, log(value) from test;
Error: no such function: log
sqlite SELECT load_extension('/home/mlennert/SRC/libsqlitefunctions.so');
sqlite select value, log(value) from test;1|0.0
159|5.06890420222023
257|5.54907608489522
Thanks, now I have all with a Makefile to fetch and compile
libsqlitefunctions.so.
...
Too bad that the SQLite backend doesn't come with the extended functions.
That's why it's called SQLite ;-)
Good point.
So I have modified v.db.update.py to allow the user to offer locally
libsqlitefunctions.so.
Effectively it executes the line
SELECT load_extension('/path/to/libsqlitefunctions.so');
prior to the existing UPDATE command.
Theoretially nice, but it still ends up with an error:
GRASS 7.1.svn (meuse):~ v.db.update meuse_voronoi column=logzinc
qcolumn=log(zinc)
sqliteextra=~/software/sqlite_extensions/libsqlitefunctions.so
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
ERROR: Error while executing: 'SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so')'
Traceback (most recent call last):
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/scripts/v.db.update,
line 123, in module
sys.exit(main())
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/scripts/v.db.update,
line 114, in main
grass.write_command('db.execute', input = '-', database =
database, driver = driver, stdin = cmd)
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/etc/python/grass/script/core.py,
line 483, in write_command
return handle_errors(returncode, returncode, args, kwargs)
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/etc/python/grass/script/core.py,
line 308, in handle_errors
returncode=returncode)
grass.exceptions.CalledModuleError: Module run None ['db.execute',
'input=-', stdin=SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');\nUPDATE
meuse_voronoi SET logzinc=log(zinc);\n, 'driver=sqlite',
'database=/home/neteler/grassdata/meuse/user1/sqlite/sqlite.db'] ended
with error
Process ended with non-zero return code 1. See errors in the (error) output.
No idea what's disliked here in:
SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');
UPDATE meuse_voronoi SET logzinc=log(zinc);
Any hints?
Markus
___
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On 19/11/14 18:59, Markus Neteler wrote:
On Wed, Nov 19, 2014 at 1:13 PM, Moritz Lennert
mlenn...@club.worldonline.be wrote:
...
No, since version 3.6 you can compile the extension as a shared library and
then activate it:
$ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
$ sqlite3 test.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter .help for usage hints.
sqlite create table test (value int);
sqlite insert into test VALUES (1), (159), (257);
sqlite select value, log(value) from test;
Error: no such function: log
sqlite SELECT load_extension('/home/mlennert/SRC/libsqlitefunctions.so');
sqlite select value, log(value) from test;1|0.0
159|5.06890420222023
257|5.54907608489522
Thanks, now I have all with a Makefile to fetch and compile
libsqlitefunctions.so.
...
Too bad that the SQLite backend doesn't come with the extended functions.
That's why it's called SQLite ;-)
Good point.
So I have modified v.db.update.py to allow the user to offer locally
libsqlitefunctions.so.
Effectively it executes the line
SELECT load_extension('/path/to/libsqlitefunctions.so');
prior to the existing UPDATE command.
Theoretially nice, but it still ends up with an error:
GRASS 7.1.svn (meuse):~ v.db.update meuse_voronoi column=logzinc
qcolumn=log(zinc)
sqliteextra=~/software/sqlite_extensions/libsqlitefunctions.so
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
ERROR: Error while executing: 'SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so')'
Traceback (most recent call last):
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/scripts/v.db.update,
line 123, in module
sys.exit(main())
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/scripts/v.db.update,
line 114, in main
grass.write_command('db.execute', input = '-', database =
database, driver = driver, stdin = cmd)
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/etc/python/grass/script/core.py,
line 483, in write_command
return handle_errors(returncode, returncode, args, kwargs)
File
/home/neteler/software/grass71/dist.x86_64-unknown-linux-gnu/etc/python/grass/script/core.py,
line 308, in handle_errors
returncode=returncode)
grass.exceptions.CalledModuleError: Module run None ['db.execute',
'input=-', stdin=SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');\nUPDATE
meuse_voronoi SET logzinc=log(zinc);\n, 'driver=sqlite',
'database=/home/neteler/grassdata/meuse/user1/sqlite/sqlite.db'] ended
with error
Process ended with non-zero return code 1. See errors in the (error) output.
No idea what's disliked here in:
SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');
UPDATE meuse_voronoi SET logzinc=log(zinc);
Have you tried firing up sqlite3 on your command line and just issuing
the first select load_extension() on its own ?
Moritz
___
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
On 19/11/14 19:12, Vaclav Petras wrote:
On Wed, Nov 19, 2014 at 12:59 PM, Markus Neteler nete...@osgeo.org
mailto:nete...@osgeo.org wrote:
DBMI-SQLite driver error:
Error in sqlite3_step():
not authorized
ERROR: Error while executing: 'SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so')'
Traceback (most recent call last):
...
Process ended with non-zero return code 1. See errors in the (error)
output.
No idea what's disliked here in:
SELECT
load_extension('/home/neteler/software/sqlite_extensions/libsqlitefunctions.so');
UPDATE meuse_voronoi SET logzinc=log(zinc);
Any hints?
Loading user defined function is not considered completely safe, so it
is disabled by default. I think this is not an issue for GRASS GIS.
You have to enable it somehow. It seems that enable_load_extension() is
the way.
Right.
But this depends on the installation, i.e. how sqlite3 was compiled.
Here in Debian Testing I do not need to activate anything, so I assume
that it's activated by default.
Moritz
___
grass-dev mailing list
grass-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev
Re: [GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Markus Neteler wrote: Hi, playing around with the Meuse dataset about soil contamination I attempted to calculate that right away but...: v.db.update meuse_voronoi column=logzinc qcolumn=log(zinc) DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log ERROR: Error while executing: 'UPDATE meuse_voronoi SET logzinc=log(zinc)' Maybe a new module v.db.mapcalc or similar could be useful? This module could offer the functions of r.mapcalc if applicable, and would work on attributes. Sometimes a new attribute needs to be calculated from existing attributes with a more elaborate mathematical formula. Markus M ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
[GRASS-dev] How to calculate log() in v.db.update with SQLite backend?
Hi, playing around with the Meuse dataset about soil contamination I attempted to calculate that right away but...: v.db.update meuse_voronoi column=logzinc qcolumn=log(zinc) DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log DBMI-SQLite driver error: Error in sqlite3_prepare(): no such function: log ERROR: Error while executing: 'UPDATE meuse_voronoi SET logzinc=log(zinc)' After some online research I found that I would need to tune my local SQLite installation with https://sqlite.org/contrib/download/extension-functions.c extension-functions.c (50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07 Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism. Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile. Alternative: use pysqlite and define an own function. Which way to go? thanks Markus ___ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev
