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

Glynn Clements glynn at gclements.plus.com
Mon Jul 7 19:47:59 EDT 2008


Markus Neteler wrote:

> > It also means that, for VARCHAR columns, you cannot determine the
> > maximum width that can be stored in the column.
> 
> But doesn't v.in.ascii scan for column lengths in any case?

It does.

> Maybe make that switchable and use the extracted info?

If you don't use the columns= option, v.in.ascii uses the column
lengths determined from the data.

However, if you use the columns= option, it uses the answer to create
the table, then validates the table against the actual data.

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

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

But db/drivers/sqlite/create_table.c also needs to create the columns
with the correct type in the first place. At present, it "condenses"
the type down to one of SQLite's affinity types (e.g. VARCHAR columns
are created as TEXT, as are TIME and DATE).

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


More information about the grass-dev mailing list