[GRASS-dev] string madness

[Glynn Clements]

Markus Neteler wrote:

> > 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.

But will presumably fail if a table uses some other unknown type.

If affinity_type() is retained, it really should default to text. 
AFAICT, it should always be possible to obtain a value as text, but
not all values can be converted to float. The problem is that we would
then need a list of types which should be treated as float.

> > 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.

Having just read the SQLite documentation, sqlite3_column_type() only
returns a valid result if the cursor is valid (i.e. if you have
performed a query and obtained results). This is consistent with
SQLite's behaviour of typing the actual values rather than the columns
which contain them.

That is presumably why affinity_type is used in place of
sqlite3_column_type().

OTOH, sqlite3_column_decltype() only returns a valid result if the
result column directly corresponds to a table column. If the result
column is an expression, it returns NULL.

That may be why affinity_type() defaults to SQLITE_FLOAT; most
expressions will probably have a numeric result. However,
affinity_type() isn't called if decltype == NULL, so this is probably
misguided.

This is all rather problematic for db__driver_describe_table(), which
has to determine the format before any data has actually been
retrieved. And all data for a column must have the same type, so
db__driver_fetch() can't look at the actual type of the data.

In which case, the best that can be achieved would be option #2 above,
but we need a list of known floating-point types. From the PostgreSQL
documentation, I get decimal, numeric, real and double precision.

Beyond that, I'm not sure that describe.c should be bothering about
SQLITE_* types at all. It seems more logical to just associate a
DB_SQL_TYPE_* type with each column, and have fetch.c reference that
directly.

In the case where decltype == NULL and sqlite3_column_type() is
actually called, I suspect that it will always return 0 (corresponding
to an sqltype of DB_SQL_TYPE_UNKNOWN) as describe_table() will
normally (always?) be called before any rows have been read.

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