[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

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

On Wed, Nov 19, 2014 at 9:05 AM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:

Do you mean for your specific problem or generally in GRASS ?

Ideally 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,

Means
- recompile SQLite locally, or
- add pysqlite support in GRASS GIS if it makes sense
- ...

or should switch backends.

So only the PG backend would do the job? Quite some effort to
calculate a logarithm :slight_smile:
Too bad that the SQLite backend doesn't come with the extended functions.

Markus

On 19/11/14 12:05, Markus Neteler wrote:

On Wed, Nov 19, 2014 at 9:05 AM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:

Do you mean for your specific problem or generally in GRASS ?

Ideally 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,

Means
- recompile SQLite locally, or

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

- add pysqlite support in GRASS GIS if it makes sense
- ...

or should switch backends.

So only the PG backend would do the job? Quite some effort to
calculate a logarithm :slight_smile:
Too bad that the SQLite backend doesn't come with the extended functions.

That's why it's called SQLite :wink:

Moritz

On Wed, Nov 19, 2014 at 1:13 PM, Moritz Lennert
<mlennert@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 :wink:

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

On Wed, Nov 19, 2014 at 12:59 PM, Markus Neteler <neteler@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.

This probably have to go the C code.

Hope that helps,
Vaclav

https://www.sqlite.org/c3ref/load_extension.html
https://www.sqlite.org/c3ref/enable_load_extension.html
https://www.sqlite.org/loadext.html

On 19/11/14 18:59, Markus Neteler wrote:

On Wed, Nov 19, 2014 at 1:13 PM, Moritz Lennert
<mlennert@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 :wink:

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

On 19/11/14 19:12, Vaclav Petras wrote:

On Wed, Nov 19, 2014 at 12:59 PM, Markus Neteler <neteler@osgeo.org
<mailto:neteler@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

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

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 <neteler@osgeo.org
<mailto:neteler@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);

Congrats, Moritz.

On Thu, Nov 20, 2014 at 9:22 AM, Moritz Lennert
<mlennert@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

Hi,

On Thu, Nov 20, 2014 at 10:53 AM, Markus Neteler <neteler@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

On Fri, Nov 28, 2014 at 11:18 AM, Markus Neteler <neteler@osgeo.org> wrote:

On Thu, Nov 20, 2014 at 10:53 AM, Markus Neteler <neteler@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

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.

On Sat, Dec 20, 2014 at 5:49 PM, Helmut Kudrnovsky <hellik@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

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.

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.