[GRASS-dev] string madness

[Markus Neteler]

On Sat, Mar 17, 2007 at 09:15:46PM +0000, Glynn Clements wrote:
> Markus Neteler wrote:
> 
> > I debugged db.select but then bailed out when reaching XDR.
> > I am not sure how to attach to a child process (I am using 'ddd').
> > Maybe some notes could be added to
> > http://grass.gdf-hannover.de/wiki/GRASS_Debugging
> > (to avoid future questions) - or I can add notes once I know
> > how to do it.
> 
> I haven't used DDD in years.

[ I am just a poor geographer :-) ]

> In gdb, you use "attach <pid>" to attach
> to an existing process.

OK, AFAIK I can enter gdb commands in 'ddd', too.
 
> > > To fix this, the SQLite driver would need to check the column type
> > 
> > That's easy, in my local version it was already there.
> > Now in CVS.
> > 
> > > reported to the client (sqltype) rather than the type used within
> > > SQLite (litetype), and parse the textual form into the value->t field
> > > (of type dbDateTime).
> > 
> > I have added this in CVS now, inspired by the Postgresql driver.
> > It still doesn't work, sigh. Still no date output in db.select.
> 
> 	switch ( litetype ) {
> 	    case SQLITE_TEXT:
> 		if (sqltype == 6 ) { /* date string */
> 
> I don't know where that 6 comes from; it should probably be
> DB_SQL_TYPE_DATE (which is 9).

I have no clue. Here debug output (the map was imported with v.in.ogr
from Postgresql where the column is of "date" type):

g.gisenv set=DEBUG=3
echo "SELECT * from zecche_BL2001_gis WHERE comune='Zoldo Alto' ORDER BY datacens" | db.select
...
D3/3: name = '$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db'
D3/3: tokens[0] = $GISDBASE
D3/3:    -> /ssi0/ssi/neteler/grassdata
D3/3: tokens[1] = $LOCATION_NAME
D3/3:    -> belluno_GBovest
D3/3: tokens[2] = $MAPSET
D3/3:    -> zecche_sqlite
D3/3: tokens[3] = sqlite.db
D2/3: name2 = '/ssi0/ssi/neteler/grassdata/belluno_GBovest/zecche_sqlite/sqlite.db'
D3/3: Escaped SQL: SELECT * from zecche_BL2001_gis WHERE comune='Zoldo Alto' ORDER BY datacens

D3/3: describe_table()
D3/3: ncols = 34
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 2
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 1
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: nkcols = 34
D3/3: litetype = 1
D2/3: col: cat, nkcols 0, litetype : 1, sqltype 3
D3/3: litetype = 1
D2/3: col: x_gid, nkcols 1, litetype : 1, sqltype 3
D3/3: litetype = 1
D2/3: col: adulti, nkcols 2, litetype : 1, sqltype 3
D3/3: litetype = 1
D2/3: col: altitudine, nkcols 3, litetype : 1, sqltype 3
D3/3: litetype = 3
D2/3: col: bab_fixed, nkcols 4, litetype : 3, sqltype 13
D3/3: litetype = 3
D2/3: col: babesia, nkcols 5, litetype : 3, sqltype 13
D3/3: litetype = 3
D2/3: col: bor_fixed, nkcols 6, litetype : 3, sqltype 13
D3/3: litetype = 3
D2/3: col: borrelia, nkcols 7, litetype : 3, sqltype 13
D3/3: litetype = 1
D2/3: col: cat_, nkcols 8, litetype : 1, sqltype 3
D3/3: litetype = 1
D2/3: col: classe, nkcols 9, litetype : 1, sqltype 3
D3/3: litetype = 1
D2/3: col: codice, nkcols 10, litetype : 1, sqltype 3
D3/3: litetype = 3
D2/3: col: comune, nkcols 11, litetype : 3, sqltype 13
D3/3: litetype = 3
D2/3: col: condatm, nkcols 12, litetype : 3, sqltype 13
D3/3: litetype = 2
D2/3: col: datacens, nkcols 13, litetype : 2, sqltype 6
D3/3: litetype = 3
D2/3: col: datains, nkcols 14, litetype : 3, sqltype 13
D3/3: litetype = 3
...

"datacens" is the column in question.

> However, having looked at this some more, I don't see how you can have
> a "date" column in an SQLite database.

I cannot. It is a string column.

> The SQLite driver's version of
> db__driver_create_table() just creates a "text" column, with no
> indication that the column actually contains dates. Unless *something*
> is storing the fact that the column contains dates, the value should
> simply be treated as text.

Right. That's why I am confused - the sscanf parsing should do the job.

> What does db.describe have to say about the table?

db.describe zecche_BL2001_gis
...
column:datacens
description:
type:DOUBLE PRECISION
len:99999
scale:0
precision:0
default:
nullok:yes
select:?
update:?

I assume that it is now 'DOUBLE PRECISION' since this should be the
fallback if type detection fails (in GRASS!?).

If I open the table in sqlite3, it works, so the table seems to
be fine:

sqlite3 sqlite.db
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE zecche_BL2001_gis (cat integer, x_gid integer, adulti integer, altitudine integer, bab_fixed varchar ( 1 ), babesia varchar ( 80 ), bor_fixed varchar ( 1 ), borrelia varchar ( 80 ), cat_ integer, classe integer, codice integer, comune varchar ( 80 ), condatm varchar ( 80 ), datacens date, datains varchar ( 80 ), datamod varchar ( 80 ), ehr_fixed varchar ( 1 ), ehrlichia varchar ( 80 ), est integer, fauna varchar ( 80 ), gid integer, larve integer, latitude integer, localita varchar ( 80 ), longitude integer, ninfe integer, nord integer, nsito varchar ( 80 ), nzecche integer, ric_fixed varchar ( 1 ), rickettsia varchar ( 80 ), rilevatore varchar ( 80 ), utente varchar ( 80 ), vegetazion varchar ( 80 ));
CREATE UNIQUE INDEX zecche_BL2001_gis_cat on zecche_BL2001_gis ( cat );
sqlite> select typeof(datacens) from zecche_BL2001_gis;
text
text
text
...
sqlite> select datacens from zecche_BL2001_gis;
2001-03-28
2001-04-03
2001-08-20
...

Markus