SQLite backend vector maps

From GRASS-Wiki
Revision as of 15:52, 3 April 2017 by Neteler (talk | contribs) (Extra tricks for power users)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Separate SQLite database files for vector maps

 IMPORTANT: Extra tricks for power users!

Since SQLite does not support concurrent write access to its DB file (reference), parallelized vector map processing in a single mapset is hampered ("Busy SQLite" warnings).

As a solution, instead of storing the vector attributes in a single SQLite database file in each mapset, individual SQLite files are to be created on a per-map basis. Simply running the next command will do the job to activate this behaviour. This can be run right after mapset creation:

 db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db'

Most (all?) of the v.db.* commands work with vector map specific database files. The db.* commands seemed work as well, if the database path is provided at the command line.

Background info

Remarks by Sören Gebbert:

I skipped the sqlite directory in the vector map path ($MAP/sqlite/sqlite.db -> $MAP/sqlite.db), since this directory must be created when a new vector is opened, hence the vector-open-new functions must be modified to support a separate sqlite directory. With the simple solution the directory fsync() behavior of sqlite will force now all vector map specific files to be flushed to disc. This may be a performance problem.

Hints (by Markus Metz):

SQLite might not open the (new) database at all if it can not fsync() the directory. That will not result in performance penalty but in a fatal error, therefore it is a good idea to keep the sqlite db in a separate folder.

The GRASS "sqlite" driver attempts to create the sqlite directory (db__driver_open_database() in trunk/db/driver/sqlite/db.c) if the directory does not exist, therefore Vect_open_new*() should not need any modification.


Thread on GRASS-dev mailing list on the topic (source of the information in this article)