[GRASS-dev] string madness

[Markus Neteler]

On Sat, Mar 17, 2007 at 11:58:25PM +0000, Glynn Clements wrote:
> Wolf Bergenheim wrote:
...
> > So in other words this might really be an issue... perhaps we should
> > treat everything which is not numeric as a string when dealing with SQLite?
> 
> Yes. SQLite's db__driver_create_table() already does this if you
> create the table that way.
> 
> I think that the problem is affinity_type (in describe.c):
> 
> 	int affinity_type ( const char *declared )
...
> But shouldn't get_column_info be using sqlite3_column_type() to get
> SQLite's idea of the column type rather than trying to guess based
> upon the declaration type? Oh; it already does that, but only if
> there's no decltype:

This is where I got trapped and thought to fix it:

>     decltype = sqlite3_column_decltype ( statement, col );
>     if ( decltype ) 
>     {
> 	G_debug ( 4, "decltype = %s", decltype );
> 	*litetype = affinity_type ( decltype );
>     }
>     else
>     {
> 	G_debug ( 4, "this is not a table column" );
> 	
> 	/* If there are no results it gives 0 */ 
> 	*litetype = sqlite3_column_type ( statement, col );
>     }
> 
> Potential fixes (in descending order of preference):
> 
> 1. Discard affinity_type() and always use sqlite3_column_type() for
> the litetype.
> 
> 2. Change affinity_type() to default to text (but then we need a list
> of decltypes which should be treated as SQLITE_FLOAT).

This would be probably against the philosophy of SQLite
(btw, their lists are full of people complaining about the
bad/absent "date" support).

> 3. Add strstr(lc, "date") to the SQLITE_TEXT case in affinity_type().

Your related patch for (3) does the job:

--- describe.c  17 Mar 2007 22:50:43 -0000      1.6
+++ describe.c  18 Mar 2007 00:01:17 -0000      1.7
@@ -251,7 +251,7 @@
         aff = SQLITE_INTEGER;
     }
     else if ( strstr(lc,"char") || strstr(lc,"clob")
-              || strstr(lc,"text") )
+              || strstr(lc,"text") || strstr(lc,"date") )
     {
         aff = SQLITE_TEXT;
     }

Tested successfully with my table.
 
> An additional improvement would be to modify get_column_info() to take
> the decltype into account when determining the sqltype, rather than
> only using the litetype. That would allow dates to be returned as
> dates rather than as text. But I strongly feel that the litetype
> should be what sqlite3_column_type() says it is, not what it "should"
> be.

That might be sufficient rather than guessing around.

Markus