v.distance -a with upload=to_attr and table= option: problems in table creation and value upload

[mlennert]

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance:

1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790.

2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

[mlennert]

Replying to mlennert:

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance:

1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790.

2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

How to reproduce bug in NC demo data set:

g.copy vect=hospitals@PERMANENT,myhosp
v.distance from=myhosp to=myhosp upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

The attached patch solves 1) & 2) for dbf and postgresql backends, but not 2) for SQLite. When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

In any case I find the attached patch useful for the backends where it works. It's against devel6, but should be straightforward to port to trunk.

Moritz

[mlennert]

patch allowing upload of to_attr data to newly created table

[mlennert]

Replying to mlennert:

When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

I can now confirm that this is a database locking issue. When doing

g.copy vect=myhosp,myhosp2
cp $GISDBASE/$MAPSET/sqlite.db ~/
v.db.connect -o myhosp2 driver=sqlite database=~/sqlite.db key=cat table=myhosp2
v.distance from=myhosp to=myhosp2 upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

it works, i.e distance_cancer contains values in the to_cancer column.

I would imagine that this kind of sqlite database locking will be a problem in other modules as well, so this probably needs a wider solution than just within this module. Anyone out there with enough experience with SQLite to attack this ? This is very important if SQLite is to become the default db backend...

Moritz

[neteler]

Potential answer to the SQLite locking problem:

On Tue, Jul 17, 2012 at 11:58 AM, Markus Metz <markus.metz.giswork@…> wrote:

"When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk." [0]

If another process is modifying files or just keeping files open in this directory while sqlite calls fsync(), opening the sqlite database will fail.

Markus M

[0] ​http://www.sqlite.org/lockingv3.html

[mlennert]

Replying to neteler:

Potential answer to the SQLite locking problem:

On Tue, Jul 17, 2012 at 11:58 AM, Markus Metz <markus.metz.giswork@…> wrote:

"When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk." [0]

If another process is modifying files or just keeping files open in this directory while sqlite calls fsync(), opening the sqlite database will fail.

Markus M

IIUC, MarkusM has fixed this with r52770. At least for me, in grass7, this seems to work. Probably should be backported, so leaving this open for now.

Moritz

[neteler]

Fixed in devbr6 and relbr64 in r53318 and r53319. Can the ticket be closed?

[mlennert]

Replying to neteler:

Fixed in devbr6 and relbr64 in r53318 and r53319. Can the ticket be closed?

Tested in both and it seems to work. Closing.

Thanks !