[GRASS-dev] How to calculate log() in v.db.update with SQLite backend?

[Markus Neteler]

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