[GRASS5] question about sqlite

GRASS GIS GRASS GIS developer
1

Radim,

Could you give me a quick overview of what it means to compile GRASS with
sqlite? Here are a couple questions I have.

1) Is it simply a driver that lets GRASS recognize a database/table created
by sqlite in an active sqlite session started in another process, like a
Postgresql table in an active Postgresql session?

2) Does it/can it active an sqlite session from within GRASS?

3) Are there other features that I'm not asking about that I should know?

Thanks
Michael
______________________________
Michael Barton, Professor of Anthropology
School of Human Evolution and Social Change
Arizona State University
Tempe, AZ 85287-2402
USA

voice: 480-965-6262; fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton

2

On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:

Radim,

Could you give me a quick overview of what it means to compile GRASS with
sqlite? Here are a couple questions I have.

1) Is it simply a driver that lets GRASS recognize a database/table created
by sqlite in an active sqlite session started in another process, like a
Postgresql table in an active Postgresql session?

2) Does it/can it active an sqlite session from within GRASS?

3) Are there other features that I'm not asking about that I should know?

The sqlite driver works like any other db driver (dbf, pg, mysql, odbc).
You can use an existing database or create a new one from GRASS.
Just set the driver and database file with db.connect and all new
attribute tables will be created in that database.

Radim

Thanks
Michael
______________________________
Michael Barton, Professor of Anthropology
School of Human Evolution and Social Change
Arizona State University
Tempe, AZ 85287-2402
USA

voice: 480-965-6262; fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton

3

Radim Blazek wrote:

On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:

Radim,

Could you give me a quick overview of what it means to compile GRASS with
sqlite? Here are a couple questions I have.

1) Is it simply a driver that lets GRASS recognize a database/table created
by sqlite in an active sqlite session started in another process, like a
Postgresql table in an active Postgresql session?

2) Does it/can it active an sqlite session from within GRASS?

3) Are there other features that I'm not asking about that I should know?
   
The sqlite driver works like any other db driver (dbf, pg, mysql, odbc).
You can use an existing database or create a new one from GRASS.
Just set the driver and database file with db.connect and all new
attribute tables will be created in that database.

I have updated the db.connect man/html page accordingly in CVS to
reflect the existance of sqlite.

Markus

4

Radim Blazek wrote:

> Could you give me a quick overview of what it means to compile GRASS with
> sqlite? Here are a couple questions I have.
>
> 1) Is it simply a driver that lets GRASS recognize a database/table created
> by sqlite in an active sqlite session started in another process, like a
> Postgresql table in an active Postgresql session?
>
> 2) Does it/can it active an sqlite session from within GRASS?
>
> 3) Are there other features that I'm not asking about that I should know?

The sqlite driver works like any other db driver (dbf, pg, mysql, odbc).

With the addition of SQLite, is there any reason to continue to
support the DBF driver?

--
Glynn Clements <glynn@gclements.plus.com>

5

DBF is a widely used, standard format--in spite of being old and klunky. It
can be imported or read by a wide variety of other programs (e.g., Open
Office, Excel, Access, Filemaker). It is also the standard table format for
ESRI, MapInfo, and Idrisi. It would be very inconvenient to drop it.

Michael
______________________________
Michael Barton, Professor of Anthropology
School of Human Evolution and Social Change
Arizona State University
Tempe, AZ 85287-2402
USA

voice: 480-965-6262; fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton

From: Glynn Clements <glynn@gclements.plus.com>
Date: Wed, 19 Oct 2005 17:56:04 +0100
To: Radim Blazek <radim.blazek@gmail.com>
Cc: Michael Barton <michael.barton@asu.edu>, grassdev <grass5@grass.itc.it>
Subject: Re: [GRASS5] Re: question about sqlite

Radim Blazek wrote:

Could you give me a quick overview of what it means to compile GRASS with
sqlite? Here are a couple questions I have.

1) Is it simply a driver that lets GRASS recognize a database/table created
by sqlite in an active sqlite session started in another process, like a
Postgresql table in an active Postgresql session?

2) Does it/can it active an sqlite session from within GRASS?

3) Are there other features that I'm not asking about that I should know?

The sqlite driver works like any other db driver (dbf, pg, mysql, odbc).

With the addition of SQLite, is there any reason to continue to
support the DBF driver?

--
Glynn Clements <glynn@gclements.plus.com>

6

Indeed, it is important to support the format.

What I think Glynn had in mind is not accessing DBF files through a GRASS DBMI
driver but rather convert them to SQLite (and back, eventually) and not bother
anymore about parsers, SQL and the lot.

I'd say yes, let's build converters between DBF and sqlite3 files (and also
between txt/csv formats and sqlite3) and drop sqlp, since the equivalent in
SQLite is much better and powerful. As an aside, deprecate DBF and propose
sqlite3 as the default storage format, starting from (say) 6.2.

That would mean having GRASS depend on sqlite, of course.

-- Daniel Calvelo Aros

---------- Original Message -----------
From: Michael Barton <michael.barton@asu.edu>
To: Glynn Clements <glynn@gclements.plus.com>, Radim Blazek
<radim.blazek@gmail.com>
Cc: grassdev <grass5@grass.itc.it>
Sent: Wed, 19 Oct 2005 10:54:42 -0700
Subject: Re: [GRASS5] Re: question about sqlite

DBF is a widely used, standard format--in spite of being old and
klunky. It can be imported or read by a wide variety of other
programs (e.g., Open Office, Excel, Access, Filemaker). It is also
the standard table format for ESRI, MapInfo, and Idrisi. It would be
very inconvenient to drop it.

Michael
______________________________
Michael Barton, Professor of Anthropology
School of Human Evolution and Social Change
Arizona State University
Tempe, AZ 85287-2402
USA

voice: 480-965-6262; fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton

> From: Glynn Clements <glynn@gclements.plus.com>
> Date: Wed, 19 Oct 2005 17:56:04 +0100
> To: Radim Blazek <radim.blazek@gmail.com>
> Cc: Michael Barton <michael.barton@asu.edu>, grassdev <grass5@grass.itc.it>
> Subject: Re: [GRASS5] Re: question about sqlite
>
>
> Radim Blazek wrote:
>
>>> Could you give me a quick overview of what it means to compile GRASS with
>>> sqlite? Here are a couple questions I have.
>>>
>>> 1) Is it simply a driver that lets GRASS recognize a database/table created
>>> by sqlite in an active sqlite session started in another process, like a
>>> Postgresql table in an active Postgresql session?
>>>
>>> 2) Does it/can it active an sqlite session from within GRASS?
>>>
>>> 3) Are there other features that I'm not asking about that I should know?
>>
>> The sqlite driver works like any other db driver (dbf, pg, mysql, odbc).
>
> With the addition of SQLite, is there any reason to continue to
> support the DBF driver?
>
> --
> Glynn Clements <glynn@gclements.plus.com>

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

------- End of Original Message -------

7

From: Radim Blazek <radim.blazek@gmail.com>
Sent: Wed, 19 Oct 2005 10:22:09 +0200

On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:
> Radim,
>
> Could you give me a quick overview of what it means to compile GRASS with
> sqlite? Here are a couple questions I have.
>
> 1) Is it simply a driver that lets GRASS recognize a database/table created
> by sqlite in an active sqlite session started in another process, like a
> Postgresql table in an active Postgresql session?

Not quite. A SQLite "database" is a file, just like with DBF. There is no
client-server infrastructure for SQLite. SQLite is transactional, so you can
open the same file in two sessions, and there will be concurrent access,
mostly problem-free.

> 2) Does it/can it active an sqlite session from within GRASS?

What it does is access the indicated sqlite file using the SQLite language and
tranaction infrastructure, through the libsqlite library.

> 3) Are there other features that I'm not asking about that I should know?

The sqlite driver works like any other db driver (dbf, pg, mysql,
odbc). You can use an existing database or create a new one from GRASS.
Just set the driver and database file with db.connect and all new
attribute tables will be created in that database.

Radim

8

Michael Barton wrote:

DBF is a widely used, standard format--in spite of being old and klunky. It
can be imported or read by a wide variety of other programs (e.g., Open
Office, Excel, Access, Filemaker). It is also the standard table format for
ESRI, MapInfo, and Idrisi. It would be very inconvenient to drop it.

I wasn't suggesting dropping support for DBF as an *external* data
format, but for the DBF driver. IMHO, external DBF files should
imported with e.g. v.in.dbf, not manually inserted into the GRASS
database.

Daniel Calvelo Aros wrote:

What I think Glynn had in mind is not accessing DBF files through a
GRASS DBMI driver but rather convert them to SQLite (and back,
eventually) and not bother anymore about parsers, SQL and the lot.

That's exactly what I had in mind.

I'd say yes, let's build converters between DBF and sqlite3 files (and also
between txt/csv formats and sqlite3) and drop sqlp, since the equivalent in
SQLite is much better and powerful. As an aside, deprecate DBF and propose
sqlite3 as the default storage format, starting from (say) 6.2.

That would mean having GRASS depend on sqlite, of course.

Well, it would depend upon having at least one database backend,
whether that's SQLite, PostgreSQL, MySQL or ODBC.

AFAICT, the only advantage to the DBF driver is that we bundle it with
GRASS. SQLite does essentially the same thing (i.e. both are
server-less, zero-configuration DBMSs) but does a better job of it.

If it's essential to bundle at least one database backend, we could
always bundle SQLite (the code is public domain).

--
Glynn Clements <glynn@gclements.plus.com>

9

OK. I understand better what you are suggesting. As long as something like
v.in.db could still work with external dbf files, grass could simply link to
an external dbf file, and grass could output dbf files, this should work
fine. It would indeed be nice to have a richer set of SQL commands the
default in GRASS--and the possibility for a better attribute management
interface.

Michael
______________________________
Michael Barton, Professor of Anthropology
School of Human Evolution and Social Change
Arizona State University
Tempe, AZ 85287-2402
USA

voice: 480-965-6262; fax: 480-965-7671
www: http://www.public.asu.edu/~cmbarton

From: Glynn Clements <glynn@gclements.plus.com>
Date: Wed, 19 Oct 2005 20:13:19 +0100
To: <daniel.calvelo@minag.gob.pe>
Cc: Michael Barton <michael.barton@asu.edu>, Radim Blazek
<radim.blazek@gmail.com>, grassdev <grass5@grass.itc.it>
Subject: Re: [GRASS5] Re: question about sqlite

Michael Barton wrote:

DBF is a widely used, standard format--in spite of being old and klunky. It
can be imported or read by a wide variety of other programs (e.g., Open
Office, Excel, Access, Filemaker). It is also the standard table format for
ESRI, MapInfo, and Idrisi. It would be very inconvenient to drop it.

I wasn't suggesting dropping support for DBF as an *external* data
format, but for the DBF driver. IMHO, external DBF files should
imported with e.g. v.in.dbf, not manually inserted into the GRASS
database.

Daniel Calvelo Aros wrote:

What I think Glynn had in mind is not accessing DBF files through a
GRASS DBMI driver but rather convert them to SQLite (and back,
eventually) and not bother anymore about parsers, SQL and the lot.

That's exactly what I had in mind.

I'd say yes, let's build converters between DBF and sqlite3 files (and also
between txt/csv formats and sqlite3) and drop sqlp, since the equivalent in
SQLite is much better and powerful. As an aside, deprecate DBF and propose
sqlite3 as the default storage format, starting from (say) 6.2.

That would mean having GRASS depend on sqlite, of course.

Well, it would depend upon having at least one database backend,
whether that's SQLite, PostgreSQL, MySQL or ODBC.

AFAICT, the only advantage to the DBF driver is that we bundle it with
GRASS. SQLite does essentially the same thing (i.e. both are
server-less, zero-configuration DBMSs) but does a better job of it.

If it's essential to bundle at least one database backend, we could
always bundle SQLite (the code is public domain).

--
Glynn Clements <glynn@gclements.plus.com>

10

Daniel,

Thanks for your message. You have answered questions I have tried to ask
on the grasslist recently but didn't know how to, being a woose in DB stuff.

My 0,01 PLN: if sqlite improves the v.* and db.* modules speed, as weel
as provides functionality to drop/rename/change type of columns without
all the complexity of Postgresql I, a simple user, am all my hands and
legs for making it a default DB backend in Grass 6.1.

Thanks Radim for your effort on implementing sqlite in Grass, and BTW for all the recent wonders you have contributed to Qgis-Grass
interoperability.

Maciek

Daniel Calvelo Aros napisał(a):

From: Radim Blazek <radim.blazek@gmail.com>
Sent: Wed, 19 Oct 2005 10:22:09 +0200

On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:

Radim,

Could you give me a quick overview of what it means to compile GRASS with
sqlite? Here are a couple questions I have.

1) Is it simply a driver that lets GRASS recognize a database/table created
by sqlite in an active sqlite session started in another process, like a
Postgresql table in an active Postgresql session?

Not quite. A SQLite "database" is a file, just like with DBF. There is no
client-server infrastructure for SQLite. SQLite is transactional, so you can
open the same file in two sessions, and there will be concurrent access,
mostly problem-free.

2) Does it/can it active an sqlite session from within GRASS?

What it does is access the indicated sqlite file using the SQLite language and
tranaction infrastructure, through the libsqlite library.

3) Are there other features that I'm not asking about that I should know?

The sqlite driver works like any other db driver (dbf, pg, mysql,
odbc). You can use an existing database or create a new one from GRASS.
Just set the driver and database file with db.connect and all new
attribute tables will be created in that database.

Radim

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

--------------------
W polskim Internecie są setki milionów stron. My przekazujemy Tobie tylko najlepsze z nich!
http://katalog.epf.pl/

11

You're welcome :slight_smile:

BTW, for beginning to work in SQL (and understand a lot about relational
databases), sqlite is truly great. You need only one executable file and
you're set. Along with any SQL tutorial, it should get you experimenting with
SQL in minimal time. And the code is beautifully written and commented.
Really, it's one of the nicest sourcebases around.

About speed, I'm pretty sure that SQLite *can* be much faster than our DBF
driver, given proper index management. I haven't looked in detail at Radim's
work, so can't really comment on that.

About flexibility, the SQL subset handled by SQLite is very large, and the
limitiations (field name length, number of fields,...) inherent to DBF are
gone. You don't have the geometric/GIS features of PostgreSQL+PostGIS, but as
far as RDB handling, you might find yourself willing to delve into SQL proper.

As for the best way to interface with GRASS, I don't have a strong opinion.
For sure we have to extend v.in.db and db.copy. I mostly use db.execute, which
becomes somewhat superfluous for SQLite since we might bundle the sqlite CLI
front-end if we bundle the library. v.db.* is a nice way of GRASSifying (and
foremost integrating) the DB back-ends, but I can't come up with a more GRASSy
interface, lest we begin experimenting with d.m from where Michael gave us the
field list.

Anyway, I think that if we have consensus about a DBF->SQLite transition we
should set up a roadmap.

Daniel.

-- Daniel Calvelo Aros

---------- Original Message -----------
From: Maciek Sieczka <werchowyna@epf.pl>
To: daniel.calvelo@minag.gob.pe
Cc: Radim Blazek <radim.blazek@gmail.com>, GRASS dev list <grass5@grass.itc.it>
Sent: Wed, 19 Oct 2005 21:38:46 +0200
Subject: Re: [GRASS5] Re: question about sqlite

Daniel,

Thanks for your message. You have answered questions I have tried to
ask on the grasslist recently but didn't know how to, being a woose
in DB stuff.

My 0,01 PLN: if sqlite improves the v.* and db.* modules speed, as weel
as provides functionality to drop/rename/change type of columns without
all the complexity of Postgresql I, a simple user, am all my hands
and legs for making it a default DB backend in Grass 6.1.

Thanks Radim for your effort on implementing sqlite in Grass, and
BTW for all the recent wonders you have contributed to Qgis-Grass

interoperability.

Maciek

[UTF-8?]> Daniel Calvelo Aros napisał(a):

> From: Radim Blazek <radim.blazek@gmail.com>
> Sent: Wed, 19 Oct 2005 10:22:09 +0200
>
>>On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:
>>
>>>Radim,
>>>
>>>Could you give me a quick overview of what it means to compile GRASS with
>>>sqlite? Here are a couple questions I have.
>>>
>>>1) Is it simply a driver that lets GRASS recognize a database/table created
>>>by sqlite in an active sqlite session started in another process, like a
>>>Postgresql table in an active Postgresql session?
>
>
> Not quite. A SQLite "database" is a file, just like with DBF. There is no
> client-server infrastructure for SQLite. SQLite is transactional, so you can
> open the same file in two sessions, and there will be concurrent access,
> mostly problem-free.
>
>
>>>2) Does it/can it active an sqlite session from within GRASS?
>
>
> What it does is access the indicated sqlite file using the SQLite language and
> tranaction infrastructure, through the libsqlite library.
>
>
>>>3) Are there other features that I'm not asking about that I should know?
>>
>>The sqlite driver works like any other db driver (dbf, pg, mysql,
>> odbc). You can use an existing database or create a new one from GRASS.
>>Just set the driver and database file with db.connect and all new
>>attribute tables will be created in that database.
>>
>>Radim
>
>
> _______________________________________________
> grass5 mailing list
> grass5@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass5
>
>

--------------------

[UTF-8?]> W polskim Internecie są setki milionów stron. My przekazujemy

Tobie tylko najlepsze z nich! http://katalog.epf.pl/

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

------- End of Original Message -------

12

AFAICT, the only advantage to the DBF driver is that we bundle it with
GRASS. SQLite does essentially the same thing (i.e. both are
server-less, zero-configuration DBMSs) but does a better job of it.

If it's essential to bundle at least one database backend, we could
always bundle SQLite (the code is public domain).

While I think it is essential that we provide something that "works out
of the box", we do rely on e.g. GDAL as an external package without too
many hickups. If maintaining our own copy of SQLite within the GRASS
source is going to be anything like what maintaining our own copy of
GDAL/PROJ4 within GRASS 5 was like, I'd be a bit worried.

The nice thing about DBF is that it is simple, well known and widely
used, and ain't about to change anytime soon. Is SQLite mature and
stable enough both on its own and as part of grass for us to depend on
with some level of comfort? (as GDAL is) ie let's avoid moving targets.

Note I am not making technical arguments against SQLite, as I'm not
qualified to comment on that. Just being conservative.

Hamish

13

Am Mittwoch, 19. Oktober 2005 22:11 schrieb Daniel Calvelo Aros:

You're welcome :slight_smile:

Hi,

BTW, for beginning to work in SQL (and understand a lot about relational
databases), sqlite is truly great. You need only one executable file and
you're set. Along with any SQL tutorial, it should get you experimenting
with SQL in minimal time. And the code is beautifully written and
commented. Really, it's one of the nicest sourcebases around.

About speed, I'm pretty sure that SQLite *can* be much faster than our DBF
driver, given proper index management. I haven't looked in detail at
Radim's work, so can't really comment on that.

From my experience especially the import using v.in.ogr to sqlite.db is very
slow, but I do not have much experience with sqlite. As Maciek mentioned,
this would have to be improved.

About flexibility, the SQL subset handled by SQLite is very large, and the
limitiations (field name length, number of fields,...) inherent to DBF are
gone. You don't have the geometric/GIS features of PostgreSQL+PostGIS, but
as far as RDB handling, you might find yourself willing to delve into SQL
proper.

As for the best way to interface with GRASS, I don't have a strong opinion.
For sure we have to extend v.in.db and db.copy. I mostly use db.execute,
which becomes somewhat superfluous for SQLite since we might bundle the
sqlite CLI front-end if we bundle the library. v.db.* is a nice way of
GRASSifying (and foremost integrating) the DB back-ends, but I can't come
up with a more GRASSy interface, lest we begin experimenting with d.m from
where Michael gave us the field list.

Anyway, I think that if we have consensus about a DBF->SQLite transition we
should set up a roadmap.

I agree that it wouldn't be a good idea to drop the DBF driver. It is really
used everywhere and is support by many editors, but it is good to have sqlite
as a better alternative already now.

Daniel.

thanks,
Otto

---------- Original Message -----------
From: Maciek Sieczka <werchowyna@epf.pl>
To: daniel.calvelo@minag.gob.pe
Cc: Radim Blazek <radim.blazek@gmail.com>, GRASS dev list
<grass5@grass.itc.it> Sent: Wed, 19 Oct 2005 21:38:46 +0200
Subject: Re: [GRASS5] Re: question about sqlite

> Daniel,
>
> Thanks for your message. You have answered questions I have tried to
> ask on the grasslist recently but didn't know how to, being a woose
> in DB stuff.
>
> My 0,01 PLN: if sqlite improves the v.* and db.* modules speed, as weel
> as provides functionality to drop/rename/change type of columns without
> all the complexity of Postgresql I, a simple user, am all my hands
> and legs for making it a default DB backend in Grass 6.1.
>
> Thanks Radim for your effort on implementing sqlite in Grass, and
> BTW for all the recent wonders you have contributed to Qgis-Grass

interoperability.

> Maciek

[UTF-8?]> Daniel Calvelo Aros napisał(a):
> > From: Radim Blazek <radim.blazek@gmail.com>
> > Sent: Wed, 19 Oct 2005 10:22:09 +0200
> >
> >>On 10/18/05, Michael Barton <michael.barton@asu.edu> wrote:
> >>>Radim,
> >>>
> >>>Could you give me a quick overview of what it means to compile GRASS
> >>> with sqlite? Here are a couple questions I have.
> >>>
> >>>1) Is it simply a driver that lets GRASS recognize a database/table
> >>> created by sqlite in an active sqlite session started in another
> >>> process, like a Postgresql table in an active Postgresql session?
> >
> > Not quite. A SQLite "database" is a file, just like with DBF. There is
> > no client-server infrastructure for SQLite. SQLite is transactional, so
> > you can open the same file in two sessions, and there will be
> > concurrent access, mostly problem-free.
> >
> >>>2) Does it/can it active an sqlite session from within GRASS?
> >
> > What it does is access the indicated sqlite file using the SQLite
> > language and tranaction infrastructure, through the libsqlite library.
> >
> >>>3) Are there other features that I'm not asking about that I should
> >>> know?
> >>
> >>The sqlite driver works like any other db driver (dbf, pg, mysql,
> >> odbc). You can use an existing database or create a new one from
> >> GRASS. Just set the driver and database file with db.connect and all
> >> new attribute tables will be created in that database.
> >>
> >>Radim
> >
> > _______________________________________________
> > grass5 mailing list
> > grass5@grass.itc.it
> > http://grass.itc.it/mailman/listinfo/grass5
>
> --------------------

[UTF-8?]> W polskim Internecie są setki milionów stron. My przekazujemy

> Tobie tylko najlepsze z nich! http://katalog.epf.pl/
>
> _______________________________________________
> grass5 mailing list
> grass5@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grass5

------- End of Original Message -------

_______________________________________________
grass5 mailing list
grass5@grass.itc.it
http://grass.itc.it/mailman/listinfo/grass5

14

On 10/19/05, Glynn Clements <glynn@gclements.plus.com> wrote:

With the addition of SQLite, is there any reason to continue to
support the DBF driver?

We don't have to add new features to dbf driver but we must keep that
in the distribution. Many datasets exist with tables in dbf format.
In any case, in major release number line (6.x) we can only add new features,
not remove them.

Radim