[GRASS-dev] single vs. multiple sqlite db files [was v.db.calc]

[Moritz Lennert]

On 11/05/09 22:59, Dylan Beaudette wrote:
> On Monday 11 May 2009, C Michael Barton wrote:
>>
>> You can have multiple tables in an SQLite database. These are simply
>> imported or created. So joins would be possible between any table in the
>> database. The question is whether to have one SQLite database (with one or
>> more tables, depending on one's need) for each vector file OR to have a
>> single SQLite for ALL vector files in a mapset.
>>
>> The latter has the greatest possibility of making any kind of join between
>> any table used with any vector file in a mapset. However, it breaks the
>> portability we now have with respect to vectors in which you can grab the
>> vector folder from the mapset and copy/move/send it to any other equivalent
>> location with all parts intact (vector GIS files, indexes,  and attribute
>> tables).
>>
>> So the issue is whether the ability to make any kind of join between any
>> table within a mapset (does not apply to between mapset joins as proposed
>> currently) is more important than maintaining easy vector portability OR is
>> portability more important than making joins between the tables of
>> different vectors within a mapset? 


I would plead for solid modules allowing to export and import GRASS data 
between GRASS installations (e.g. something like the r.pack/r.unpack 
modules). IMHO, moving around data should not depend on the location of 
the database or the choice of database backend (i.e. how do you handle 
above export with a PostgreSQL backend ?). We need to povide a mecanism 
which allows users to do that while respecting the unwritten law that 
they should not have to do anything at all inside their GISDBASE.


> Sure. As long as there is a documented approach to adjusting this behavior. I 
> think that apart from joins, the table / file locking issue may be more 
> important... If a long-running process is causing the mapset-wide SQLite db 
> to be locked, then vector-specific database files would make more sense.

I agree that locking can be a serious issue for sqlite. I didn't think 
of this before, but AFAIK, sqlite can only provide file-based locking as 
it is a single-file database [1]. With this in mind, I might actually 
change my mind and plead for one db per vector, although I agree with 
Dylan that sharing data across vector file attributs tables happens 
regularly for me.

I guess that sqlite is still more in the same line of solutions as dbf, 
only that if provides a much larger set of possibilities, but that if 
you want to do any serious use of db functionalities you will in any 
case have to turn to PostgreSQL of MySQL...

Moritz

[1] http://www.sqlite.org/lockingv3.html