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

[Ivan Shmakov]

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

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

[...]

 >> Of course, any width limit specified via VARCHAR () is ignored by
 >> SQLite itself.

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

[...]

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

	Hm.  Now I see sqlite3_column_decltype () in the SQLite API
	reference?

[...]

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

	That's the point -- while it behaves its own way, it allows
	standards-compliant database schemata to be reused with little
	or no modification.  (Or, rather, it relies on de-facto
	standards, for that matter.)

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

	Yes.

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

	Shouldn't there be a separate value for the ``no size limit''
	case?