This and especially portability is why I argued--
unsuccessfully--
? I thought the question was still up in the air for g7 and
for now it was user choosable by the way you constructed the
db.connect string.
for a separate sqlite DB for each vector file, even if it is
inefficient.
is it? only when doing joins? if so, how common a use case is
that? (no idea how well sqlite handles fseeks() to jump right to
the data it needs, or if it has to read in the whole file)
I'm mildly in favour of per-map dbs as I've often seen 600mb
vector datasets on 32bit machines, and 2-4gb limit of the
filesystem doesn't seem so far away if those get set to be
cumulative. But I am fairly ignorant of DB issues and sqlite
implementations in particular, so am not really familiar with
what gains you might see from the monolithic approach.
How can you change the default by changing the db.connect string? I'd like to do it.
I don't think it is all that difficult connecting tables across different vector objects, but agree (and said at the time) that it is probably a pretty rare occurrence in reality.
Michael
______________________________
C. Michael Barton
Director, Center for Social Dynamics & Complexity
Professor of Anthropology, School of Human Evolution & Social Change
Arizona State University
Tempe, AZ 85287-2402
USA
On Feb 14, 2013, at 2:59 PM, Hamish <hamish_b@yahoo.com>
wrote:
Michael wrote:
This and especially portability is why I argued--
unsuccessfully--
? I thought the question was still up in the air for g7 and
for now it was user choosable by the way you constructed the
db.connect string.
for a separate sqlite DB for each vector file, even if it is
inefficient.
is it? only when doing joins? if so, how common a use case is
that? (no idea how well sqlite handles fseeks() to jump right to
the data it needs, or if it has to read in the whole file)
I'm mildly in favour of per-map dbs as I've often seen 600mb
vector datasets on 32bit machines, and 2-4gb limit of the
filesystem doesn't seem so far away if those get set to be
cumulative. But I am fairly ignorant of DB issues and sqlite
implementations in particular, so am not really familiar with
what gains you might see from the monolithic approach.
> I thought the question was still up in the air for g7 and
> for now it was user choosable by the way you constructed the
> db.connect string.
Michael:
How can you change the default by
changing the db.connect string? I'd like to do it.
I don't think it is all that difficult connecting tables
across different vector objects, but agree (and said at the
time) that it is probably a pretty rare occurrence in
reality.
hmm, I thought it did emulate the dbf way if you only speficied
the directory, but just testing it looks like it doesn't:
[trunk]
cd $MAPSET
mkdir sqlite
db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/'
echo "1|2|abc" | v.in.ascii in=- out=testmap
...
DBMI-SQLite driver error:
Unable to open database: unable to open database file
we definitely should make that option work for people who
want/need that though.
Is there a reason to collect them in a common $MAPSET/dbf/
or $MAPSET/sqlite/ dir instead of under $MAPSET/vector/$MAPNAME/sqlite.db ? (and would you want separate sqlite db files for
each db layer, or for a single map keep all of the layer
connections using the sqlite driver all in the same sqlite file?)
Note v.pack(.py) demos the method to make a $MAPSET/vector/
$MAPNAME/db.sqlite per-map extract.
On Thu, Feb 14, 2013 at 11:26 PM, Hamish <hamish_b@yahoo.com> wrote:
Hamish wrote:
> I thought the question was still up in the air for g7 and
> for now it was user choosable by the way you constructed the
> db.connect string.
Michael:
How can you change the default by
changing the db.connect string? I'd like to do it.
I don't think it is all that difficult connecting tables
across different vector objects, but agree (and said at the
time) that it is probably a pretty rare occurrence in
reality.
hmm, I thought it did emulate the dbf way if you only speficied
the directory, but just testing it looks like it doesn't:
[trunk]
cd $MAPSET
mkdir sqlite
db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/'
echo "1|2|abc" | v.in.ascii in=- out=testmap
...
DBMI-SQLite driver error:
Unable to open database: unable to open database file
we definitely should make that option work for people who
want/need that though.
Is there a reason to collect them in a common $MAPSET/dbf/
or $MAPSET/sqlite/ dir instead of under $MAPSET/vector/$MAPNAME/sqlite.db ? (and would you want separate sqlite db files for
each db layer, or for a single map keep all of the layer
connections using the sqlite driver all in the same sqlite file?)
I guess the reason for one common sqlite database is the ability to do
joins. In GRASS 6.4.1- it was possible to have a separate sqlite db
for each vector in $MAPSET/vector/$MAP/sqlite.db. I have no idea why
this option has been disabled. The VAR file can be
In GRASS 6.4.1- it was possible to have a separate sqlite db
for each vector in $MAPSET/vector/$MAP/sqlite.db. I have no
idea why this option has been disabled. The VAR file can be
Should we revert the changes that make it now impossible to
have a separate sqlite db for each vector?
AFAIK it's still possible to have a separate sqlite db for each
vector if you use v.db.connect (see v.pack.py), it's just that
you can't tell db.connect to make that the default for all new
maps.
perhaps if [v.]db.connect understood to parse '$MAP' you could
have a portable
database='$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db'
string saved in the VAR file? (and so, by default but user-
alterable, all sqlite layers for that map saved in the same file)
If the $MAP variable is available, I’d like to see the database names as ‘$GISDBASE/$LOCATION_NAME/$MAPSET/vector/sqlite/$MAP.sqlite’ and I think you’re suggestion to put all layers for a vector into one sqlite file does make sense. THere are definitely situations where you want to join between different layers within the same vector. Having both in the same sqlite makes that easier.
···
On 15/02/2013 01:42, Hamish wrote:
Markus Metz wrote:
In GRASS 6.4.1- it was possible to have a separate sqlite db
for each vector in $MAPSET/vector/$MAP/sqlite.db. I have no
idea why this option has been disabled. The VAR file can be
DB_DRIVER: sqlite
DB_DATABASE:
$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db
Should we revert the changes that make it now impossible to
have a separate sqlite db for each vector?
AFAIK it's still possible to have a separate sqlite db for each
vector if you use v.db.connect (see v.pack.py), it's just that
you can't tell db.connect to make that the default for all new
maps.
perhaps if [v.]db.connect understood to parse '$MAP' you could
have a portable
database='$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db'
string saved in the VAR file? (and so, by default but user-
alterable, all sqlite layers for that map saved in the same file)
"db.sqlite" or "sqlite.db" for the filename?
Hamish
_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[http://lists.osgeo.org/mailman/listinfo/grass-user](http://lists.osgeo.org/mailman/listinfo/grass-user)
This mail was received via Mail-SeCure System.
On Fri, Feb 15, 2013 at 8:21 AM, Micha Silver <micha@arava.co.il> wrote:
On 15/02/2013 01:42, Hamish wrote:
Markus Metz wrote:
In GRASS 6.4.1- it was possible to have a separate sqlite db
for each vector in $MAPSET/vector/$MAP/sqlite.db. I have no
idea why this option has been disabled. The VAR file can be
Should we revert the changes that make it now impossible to
have a separate sqlite db for each vector?
AFAIK it's still possible to have a separate sqlite db for each
vector if you use v.db.connect (see v.pack.py), it's just that
you can't tell db.connect to make that the default for all new
maps.
perhaps if [v.]db.connect understood to parse '$MAP' you could
have a portable
database='$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db'
string saved in the VAR file? (and so, by default but user-
alterable, all sqlite layers for that map saved in the same file)
"db.sqlite" or "sqlite.db" for the filename?
If the $MAP variable is available, I'd like to see the database names as
'$GISDBASE/$LOCATION_NAME/$MAPSET/vector/sqlite/$MAP.sqlite'
This could cause problems with SQLite directory locking because of
[0]. Therefore I would prefer to restore the previous behaviour of
allowing '$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db
and I think you're suggestion to put all layers for a vector into one sqlite
file does make sense. THere are definitely situations where you want to join
between different layers within the same vector. Having both in the same
sqlite makes that easier.
I'm not sure how to pull it off now given the current structure, but IMHO, the ideal organization would be to have 1 sqlite DB for each vector file--so a single DB for all layers of a single vector.
That makes vector files portable. I'm not sure if joins across layer tables are needed because the layer structure is an inherent join.
Michael
____________________
C. Michael Barton
Director, Center for Social Dynamics & Complexity
Professor of Anthropology, School of Human Evolution & Social Change
Arizona State University
On Feb 15, 2013, at 9:29 AM, Markus Metz <markus.metz.giswork@gmail.com>
wrote:
On Fri, Feb 15, 2013 at 8:21 AM, Micha Silver <micha@arava.co.il> wrote:
On 15/02/2013 01:42, Hamish wrote:
Markus Metz wrote:
In GRASS 6.4.1- it was possible to have a separate sqlite db
for each vector in $MAPSET/vector/$MAP/sqlite.db. I have no
idea why this option has been disabled. The VAR file can be
Should we revert the changes that make it now impossible to
have a separate sqlite db for each vector?
AFAIK it's still possible to have a separate sqlite db for each
vector if you use v.db.connect (see v.pack.py), it's just that
you can't tell db.connect to make that the default for all new
maps.
perhaps if [v.]db.connect understood to parse '$MAP' you could
have a portable
database='$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db'
string saved in the VAR file? (and so, by default but user-
alterable, all sqlite layers for that map saved in the same file)
"db.sqlite" or "sqlite.db" for the filename?
If the $MAP variable is available, I'd like to see the database names as
'$GISDBASE/$LOCATION_NAME/$MAPSET/vector/sqlite/$MAP.sqlite'
This could cause problems with SQLite directory locking because of
[0]. Therefore I would prefer to restore the previous behaviour of
allowing '$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db
and I think you're suggestion to put all layers for a vector into one sqlite
file does make sense. THere are definitely situations where you want to join
between different layers within the same vector. Having both in the same
sqlite makes that easier.
Could you review again what the problem with sqlite3 directory
locking was? In that url I see the rollback file with the name
of the sqlite.db file + "-journal", but not what would lock
some other .db file in the same dir. Is it the fsync()?
Therefore I would prefer to restore the previous behaviour
of allowing
'$GISDBASE/$LOCATION_NAME/$MAPSET/vector/$MAP/sqlite.db
AFAIK that is still allowed, the question is how to make it
(optionally) the user's default, instead of having to run
v.db.connect each time? Perhaps the only missing piece is adding
the parsing of "$MAP" into [v.]db.connect so you don't have to
tailor it per map &/or the link survives a g.rename of the map.
