[GRASS-dev] string madness

[Markus Neteler]

On Sat, Mar 17, 2007 at 10:48:33PM +0000, Glynn Clements wrote:
> 
> Markus Neteler wrote:
> 
> > 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:
> 
> This patch is entirely inappropriate. You're adding a hack to fix a
> specific broken table.

Accepted that my fix is broken, but the table isn't:

sqlite3 sqlite.db
sqlite> select typeof(datacens) from zecche_BL2001_gis;
text
...

sqlite> select datacens from zecche_BL2001_gis;
2001-03-28
...

It is the GRASS driver which doesn't work as needed.

> Instead, you need to figure out why the table
> is getting created wrong in the first place. Once that's fixed, you
> don't need this hack.

The table is created correctly. Keeping the column type
helps us once the user wants to convert back from SQLite
to something else.
Note that SQLite is special about types:

sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table t (id bogus, data trento, created default current_date);
sqlite> insert into t (id, data) values (1, 'test');
sqlite> insert into t (id, data) values (2, 'more');
sqlite> select created from t;
2007-03-18
2007-03-18
sqlite> select * from t;
1|test|2007-03-18
2|more|2007-03-18
sqlite> select typeof(id), typeof(data), typeof(current_date) from t;
integer|text|text
integer|text|text
sqlite> 

-> "bogus" and "trento" type are accepted without problems, so is "date".
It just checks what comes in and assigns the type.

I admit that I am not much expert here.

> > Submitted to CVS.
> 
> And about to be reverted.

OK.

Markus