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

Ivan Shmakov ivan at theory.asu.ru
Thu Jul 3 10:56:47 EDT 2008


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

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

	E. g.:

$ sqlite3 /tmp/foo.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE foo (bar VARCHAR (3));
sqlite> INSERT INTO foo VALUES ('bar baz');
sqlite> SELECT * FROM foo;
bar baz
sqlite> .schema
CREATE TABLE foo (bar VARCHAR (3));
sqlite> SELECT * FROM 'sqlite_master';
table|foo|foo|2|CREATE TABLE foo (bar VARCHAR (3))
sqlite> 

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

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

	I. e., by means of parsing SQL?

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

	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.

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

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

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



More information about the grass-dev mailing list