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

[Ivan Shmakov]

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

[...]

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

 > Er, right. ISTR that there are reasons why the SQLite driver doesn't
 > rely upon that much. However, I suspect that it will produce the same
 > result as parsing the string from sqlite_master.

	Most probably.

 > IIRC, the main problem is that sqlite3_column_decltype() only works
 > for actual columns, not expressions, subselects etc, but the code
 > in question has to be able to describe the format of rows returned
 > by arbitrary SELECT statements, not just tables.

 > So it falls back to sqlite3_column_type(), which returns the type
 > of the column's data. but that only works if you have a valid
 > row. If a select doesn't return any rows, you lose. Also,
 > sqlite3_column_type() only understands null, integer, float, text
 > and blob types.

	I wonder, how it's done for other RDBMS?  E. g., would there be
	a table, like:

CREATE TABLE foo (foo NUMERIC, bar TEXT);

	What would be the type of the only column of the query like:

SELECT foo || bar FROM foo;

	Or, the same question for the table like:

CREATE TABLE foo (foo VARCHAR (5), bar CHAR (5));

	It seems that the only sane type to be inferred from the
	expression is TEXT (for either case.)

	And I guess, sqlite3_column_type () will return either text or
	blob.

 > Apart from needing to fall-back to the data type for expressions,
 > another problem is that it coerces the decltype to one of SQLite's
 > limited set of types, then converts that to one of the DB_SQL_*
 > types, losing information in the process.

	That doesn't feel sound.

 > However, I don't know if it does this for a specific reason. I
 > suspect the only way to find out is to try it and see what breaks.

[...]