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

[Moritz Lennert]

On 19/11/14 18:59, Markus Neteler wrote:
> 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);

Have you tried firing up sqlite3 on your command line and just issuing 
the first select load_extension() on its own ?

Moritz