[GRASS-dev] SQLite as default DB in GRASS 6.3+

[twiens]

----- Original Message Follows -----
From: William Kyngesburye <woklist at kyngchaos.com>
To: Hamish <hamish_nospam at yahoo.com>
Cc: grass-dev at grass.itc.it
Subject: Re: [GRASS-dev] SQLite as default DB in GRASS 6.3+
Date: Wed, 23 Aug 2006 09:14:00 -0500

> The main thing I'm concerned about SQLite support is that
> I think by   default it stuffs all tables for all vector
> layers in a mapset into a   single DB file.
> 
> I think you can connect to different DB files before
> creating   vectors, thus separating them, but it would be
> nice to have something   more automatic, like what happens
> with DBF files.  Say a subfolder   for SQLite DBs, and
> connect to the folder, new vectors get their own   SQLite
> DB file generated automatically in that folder.
> 
> Maybe a bit confusing since you would have 'database'
> meaning the   folder and 'database' meaning the SQLite
> files in that folder,
> 
> A few problems with a single DB file:
> 
> - It could get HUGE.  I'm not worried so much about file
> system   limits, but it's a possibility.
> 
> - easy for corruption in the DB file to make all vectors
> in the   mapset useless.
> 
> - backups.  You can't backup a single vector, since you
> would be   backing up the attribute data for all vectors
> in the process.  And   incremental backups would be longer
> (especially if it's huge) - if a   single small vector
> changed (attributes) in a mapset, you still need   to
> backup the attribute DB for all vectors.

I don't know much about SQLite as I'm a PostgreSQL user, but
I don't think your concerns over single files are well
founded. Unless SQLite is a piece of crap, in which case we
shouldn't use it, corruption shouldn't be a concern. Second,
if you have so much attribute data, then it should probably
be handled in a real database anyway like PostgreSQL.
Further, if you run into system limits because of high
volumes of data you will run out of disk space no matter
what format you use. I would assume that probably SQLite is
probably more space efficient than dbf files, as dbf files
are not particularly efficient, but I don't know.

IMHO, creating multiple copies of everything essentially
defeats the purpose of having a database in the first place,
so there is no benefit to moving to SQLite if it is
implemented in the fashion you suggest.

Right now GRASS defaulting to dbf sets the database level
pretty low. AFAIK dbf will still be available once SQLite is
set as default, thus for the cases which you envision where
SQLite may present difficulties, users could choose
PostgreSQL or dbfs (although I can't possibly imagine why)
instead.

T
--
Trevor Wiens
twiens at interbaun.com

--
Trevor Wiens
twiens at interbaun.com