[GRASS-dev] string madness

[Markus Neteler]

On Sat, Mar 17, 2007 at 09:59:12PM +0000, Glynn Clements wrote:
> 
> Markus Neteler wrote:
> 
> > g.gisenv set=DEBUG=3
> > echo "SELECT * from zecche_BL2001_gis WHERE comune='Zoldo Alto' ORDER BY datacens" | db.select
> 
> > D2/3: col: datacens, nkcols 13, litetype : 2, sqltype 6
> 
> > "datacens" is the column in question.
> 
> 	http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/sqlite.h.in&v=1.198
> says:
> 	#define SQLITE_INTEGER  1
> 	#define SQLITE_FLOAT    2
> 	/* #define SQLITE_TEXT  3  // See below */
> 	#define SQLITE_BLOB     4
> 	#define SQLITE_NULL     5

A few lines down it says:

	/*
	** SQLite version 2 defines SQLITE_TEXT differently.  To allow both
	** version 2 and version 3 to be included, undefine them both if a
	** conflict is seen.  Define SQLITE3_TEXT to be the version 3 value.
	*/
	#ifdef SQLITE_TEXT
	# undef SQLITE_TEXT
	#else
	# define SQLITE_TEXT     3
	#endif
	#define SQLITE3_TEXT     3

A SQLITE3_TEXT seems to be there. (?)
 
> IOW, litetype == SQLITE_FLOAT, sqltype == DB_SQL_TYPE_DOUBLE_PRECISION.

Yes, the table description is going wrong in GRASS.

> If DBMI thinks that the column contains a float, trying to parse
> YYYY-MM-DD as a float is going to fail.

Right. Some analysis:

echo "SELECT * from zecche_BL2001_gis WHERE comune='Zoldo Alto' ORDER BY datacens" | db.select
...
D3/3: litetype = 3
D3/3: litetype = 3
D3/3: litetype = 2  <--- ZAP
D3/3: litetype = 3
...
D3/3: litetype = 3
D2/3: col: condatm, nkcols 12, litetype : 3, sqltype 13
D3/3: litetype = 2                                   <--- ZAP
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: col 10, litetype 1, sqltype 3: val = '87'
D3/3: col 11, litetype 3, sqltype 13: val = 'Zoldo Alto'
D3/3: col 12, litetype 3, sqltype 13: val = 'soleggiato'
D3/3: col 13, litetype 3, sqltype 6: val = '2001-08-01'  <-- BETTER
D3/3: sqlite fetched date: 2001-08-01
D3/3: col 14, litetype 3, sqltype 13: val = ''


describe.c: db_set_column_host_type(column, litetype);

in fetch.c, I had changed:
        litetype  = db_get_column_host_type(column);
to
        litetype = sqlite3_column_type ( c->statement, col );
to obtain
        D3/3: col 13, litetype 3, sqltype 6: val = '2001-08-01'
which makes the SWITCH statement in fetch.c work.

But in describe.c the function
        db_set_column_host_type(column, litetype);
is still used which reports litetype 2. Not sure how to fix this one.

With DEBUG=4 I get output from get_column_info() in describe.c

...
D4/4: decltype = varchar ( 80 )
D3/4: litetype = 3
D2/4: col: condatm, nkcols 12, litetype : 3, sqltype 13
D4/4: decltype = date			<--- from PostgreSQL originally
D3/4: litetype = 2			<--- should be 3 ideally
D2/4: col: datacens, nkcols 13, litetype : 2, sqltype 6   <-- the problem
D4/4: decltype = varchar ( 80 )
D3/4: litetype = 3
D2/4: col: datains, nkcols 14, litetype : 3, sqltype 13
D4/4: decltype = varchar ( 80 )
D3/4: litetype = 3
...

#### some minutes later ###########

I GOT IT!

this patch:
diff -u -r1.4 describe.c
--- describe.c  4 Jul 2006 09:24:14 -0000       1.4
+++ describe.c  17 Mar 2007 22:34:08 -0000
@@ -199,7 +199,11 @@
        *litetype = sqlite3_column_type ( statement, col );
     }

-    G_debug ( 3, "litetype = %d", *litetype );
+    /* Date support hack */
+    if ( strcmp(decltype, "date") == 0 ) {
+       *litetype = SQLITE_TEXT;
+       G_debug ( 4, " date found, new litetype = %d", *litetype );
+    }

     switch ( *litetype) {
        case SQLITE_INTEGER:

does the job:

D4/4: decltype = varchar ( 80 )
D2/4: col: condatm, nkcols 12, litetype : 3, sqltype 13
D4/4: decltype = date
D4/4:  date found, new litetype = 3
D2/4: col: datacens, nkcols 13, litetype : 3, sqltype 13
D4/4: decltype = varchar ( 80 )
D2/4: col: datains, nkcols 14, litetype : 3, sqltype 13

# TEST
echo "SELECT * from zecche_BL2001_gis WHERE comune='Zoldo Alto' ORDER BY datacens" | db.select
cat|x_gid|adulti|altitudine|bab_fixed|babesia|bor_fixed|borrelia|cat_|classe|codice|comune|condatm|datacens|datains|datamod|ehr_fixed|ehrlichia|est|fauna|gid|larve|latitude|localita|longitude|ninfe|nord|nsito|nzecche|ric_fixed|rickettsia|rilevatore|utente|vegetazion
2|86|0|1250||f|n|n|114|24|9|Zoldo Alto|soleggiato|2001-04-03|||n|n|2296670|cervo, capriolo|85|0|46|Le Vare|12|0|5136625|24p|0||t||ULSSBL|formazioni di conifere d'alta quota

-> 2001-04-03 is finally there...

That was tough :-)

Thanks, Glynn, for you support and for getting me into the right direction!
Submitted to CVS.

Markus