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 <[EMAIL PROTECTED]> _______________________________________________ grass-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/grass-dev
