[GRASS-dev] Re: new v.in.geonames: problems with UTF-8 Unicode text

[Ivan Shmakov]

>>>>> Glynn Clements <glynn at gclements.plus.com> writes:

[...]

 > So, the problem arises because v.in.geonames tell v.in.ascii to use
 > varchar(4000) for the "alternatename" column, but once the table
 > has been created the SQLite driver reports the column as
 > varchar(255).

 > Essentially, db/drivers/sqlite/describe.c needs to do a better job
 > of parsing the result from sqlite3_column_decltype().

 > At present, it parses the decltype to an SQLite affinity type:

 > int aff = SQLITE_FLOAT;
  
 >	...
  
 > if ( strstr(lc,"int") )
 > {
 >     aff = SQLITE_INTEGER;
 > }
 > else if ( strstr(lc,"char") || strstr(lc,"clob")
 >           || strstr(lc,"text") || strstr(lc,"date") )
 > {
 >     aff = SQLITE_TEXT;
 > }
 > else if ( strstr(lc,"blob") )
 > {
 >     aff = SQLITE_BLOB;
 > }

 > then converts the affinity type to a DBMI type.

 > [I'm not sure why the default is float.]

--cut: http://www.sqlite.org/datatype3.html--
2.1 Determination Of Column Affinity

   The type affinity of a column is determined by the declared type of
   the column, according to the following rules:
    1. If the datatype contains the string "INT" then it is assigned
       INTEGER affinity.
    2. If the datatype of the column contains any of the strings "CHAR",
       "CLOB", or "TEXT" then that column has TEXT affinity. Notice that
       the type VARCHAR contains the string "CHAR" and is thus assigned
       TEXT affinity.
    3. If the datatype for a column contains the string "BLOB" or if no
       datatype is specified then the column has affinity NONE.
    4. If the datatype for a column contains any of the strings "REAL",
       "FLOA", or "DOUB" then the column has REAL affinity
    5. Otherwise, the affinity is NUMERIC.
--cut: http://www.sqlite.org/datatype3.html--

 > What it should do (IMHO) is to parse the decltype directly to a
 > DBMI type (including parsing VARCHAR() types, to determine the
 > size). It should only use the affinity type if there is no decltype
 > (which occurs when a column in a SELECT statement is an expression
 > rather than a column reference).

	For the text data fields, it may make sense to report TEXT
	instead of the declared type (provided that the callers do
	support that type.)

[...]