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

Glynn Clements glynn at gclements.plus.com
Thu Jul 3 13:47:46 EDT 2008


Ivan Shmakov wrote:

>  >>> 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.
> 
> 	Not exactly.  While I don't know of the GRASS SQLite driver
> 	specifics, the CREATE TABLE commands are preserved by SQLite:
> 
> --cut--
>     The exact text of each CREATE TABLE statement is stored in the
>     sqlite_master table.  Every time the database is opened, all CREATE
>     TABLE statements are read from the sqlite_master table and used to
>     regenerate SQLite's internal representation of the table layout.  If
>     the original command was a CREATE TABLE AS then then an equivalent
>     CREATE TABLE statement is synthesized and store in sqlite_master in
>     place of the original command.  The text of CREATE TEMPORARY TABLE
>     statements are stored in the sqlite_temp_master table.
> --cut--

Interesting. But not particularly useful unless we add our own SQL
parser to the SQLite driver. Well, it should only need to handle
CREATE TABLE statements, but it would still need to understand exactly
the syntax which SQLite supports (and the table might have been
created by a future version).

OTOH, that essentially rules out the idea of having the SQLite driver
store its own metadata. That would only work with tables created by
the DBMI driver, and given that limitation, we could add a parser for
the data from sqlite_master which just supports the syntax used by the
SQLite driver.

IOW, change db/drivers/sqlite/describe.c to parse the sqlite_master
table according to the syntax used by db/drivers/sqlite/create_table.c.

>  > 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.
> 
> 	Does it mean that the GRASS SQLite driver doesn't try to put the
> 	column width into the CREATE TABLE statement?  As shown in the
> 	example above, an arbitrary width limit may be passed to SQLite.

The SQLite driver doesn't use VARCHAR at all; see
db/drivers/sqlite/create_table.c.

But even if it did, it doesn't attempt to retrieve any information
from the sqlite_master table.

> 	IIUC, the very reason to allow SQL type specifications to be
> 	used in CREATE TABLE was the interoperability with other SQL
> 	implementations.

SQLite allows them, but essentially ignores them (other than the
information it puts into sqlite_master). If you create a VARCHAR(10)
column, it will happily allow you to store a 1000-character string in
that column, and to retrieve it without truncation.

> 	But then, don't the major SQL RDBMS support the TEXT type?  It
> 	has unlimited width and thus implies no incompatibility with
> 	SQLite.

They support the TEXT type, but the DBMI seems to assume fixed sizes,
e.g. the aforementioned problem with the PostgreSQL driver failing to
report the size for TEXT fields.

The MySQL driver simply reports the length reported by MySQL; I have
no idea what that will be for TEXT columns.

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


More information about the grass-dev mailing list