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

[Glynn Clements]

Markus Neteler wrote:

> >> when using the SQLite driver, it cuts the "alternatename" field at
> >> 256 chars due to a hardcoded limitation in describe.c. I don't
> >> remember why not varchar() length is used there.
> >
> > SQLite doesn't have VARCHAR. If you create a VARCHAR column, the
> > driver translates it to TEXT. The size you specify when the table is
> > created isn't stored anywhere.
> 
> This is not clear to me:
> - I run v.in.ascii and define that I want varchar(4000).
> - this is sent to DBMI which calls the SQLite driver
> - in the SQLite driver 255 is hardcoded for TEXT and the
>   "alternatename" field is cut
> - hardcoding something much longer isn't convenient due to
>   troubles when moving table to other DBMS
> 
> so far understood.
> 
> -> question: any chance to remove the hardcoded 255 limit
>    and use instead the length I defined in the v.in.ascii call?

Not easily.

>    Or is that size information eliminated in the communication
>    between v.in.ascii and SQLite driver?

Clients communicate that information to the driver, but the SQLite
driver ignores it, as there's no way to communicate it to SQLite
itself. So far as SQLite is concerned, columns are either "text",
"integer" or "real". There's no way to specify a limit on the width of
a column, and thus no way to query it.

In theory, the driver could scan the entire table and determine the
widest element. But that would be slow for large tables, and it could
also confuse clients trying to add data to an existing table.

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