[GRASS-dev] nasty v.db.dropcolumn and v.db.join bug in GRASS7 + sqlite

[Dylan Beaudette]

Just noticed a nasty bug when using v.db.dropcolumn and v.db.join with a 
sqlite back-end. This seems to happen whenever a table is modified using the 
'coltypes' as reported by the GRASS-DB API:

Here are the coltypes reported from a vector newly imported

------------------------------------------------------------------------------------------------------------------------------------------------------
cat INTEGER, OBJECTID INTEGER, Shape_Leng DOUBLE PRECISION, Shape_Area DOUBLE 
PRECISION, musym CHARACTER, comment CHARACTER, acres DOUBLE PRECISION, 
confidence CHARACTER
------------------------------------------------------------------------------------------------------------------------------------------------------

As v.db.dropcolumn or v.db.join proceed with these coltypes, all "CHARACTER" 
fields are truncated to a length of 1... here is the SQL created by 
v.db.dropcolumn:

------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE linework_backup(cat INTEGER, OBJECTID INTEGER, 
Shape_Leng DOUBLE PRECISION, Shape_Area DOUBLE PRECISION, musym CHARACTER, 
comment CHARACTER, acres DOUBLE PRECISION, confidence CHARACTER);
INSERT INTO linework_backup SELECT cat, OBJECTID, Shape_Leng, Shape_Area, 
musym, comment, acres, confidence FROM linework;
DROP TABLE linework;
CREATE TABLE linework(cat INTEGER, OBJECTID INTEGER, Shape_Leng DOUBLE 
PRECISION, Shape_Area DOUBLE PRECISION, musym CHARACTER, comment CHARACTER, 
acres DOUBLE PRECISION, confidence CHARACTER);
INSERT INTO linework SELECT cat, OBJECTID, Shape_Leng, Shape_Area, musym, 
comment, acres, confidence FROM linework_backup;
CREATE UNIQUE INDEX linework_cat ON linework (cat );
DROP TABLE linework_backup;
COMMIT
------------------------------------------------------------------------------------------------------------------------------------------------------

Somehow, the GRASS-DB API needs to figure out when to use VARCHAR, and when to 
use CHARACTER. Or, the field length needs to be given to sqlite.

Hacking v.db.dropcolumn to include the field lengths seems to work:

coltypes.append("%s %s(%s)" % (f[0], f[1], f[2]))

however... db.describe breaks with the following errors on integer / double 
columns:

WARNING: SQLite driver: unable to parse decltype: INTEGER(20)
WARNING: SQLite driver: unable to parse decltype: INTEGER(20)
WARNING: SQLite driver: unable to parse decltype: DOUBLE PRECISION(20)
WARNING: SQLite driver: unable to parse decltype: DOUBLE PRECISION(20)
WARNING: SQLite driver: unable to parse decltype: DOUBLE PRECISION(20)
WARNING: SQLite driver: unable to parse decltype: INTEGER(20)


.... so it seems that the field length should be included, but only for non-
numeric column types.

attached is a patch for v.db.dropcolumn, with some testing on sqlite. I do not 
think that this will function properly with other back-ends and there are 
significant differences between CHARACTER(50) and VARCHAR(50). Perhaps further 
planning is needed...

Dylan




-- 
Dylan E. Beaudette
USDA-NRCS Soil Scientist
California Soil Resource Lab
http://casoilresource.lawr.ucdavis.edu/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v.db.dropcolumn.diff
Type: text/x-patch
Size: 1106 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/grass-dev/attachments/20111130/3153b645/v.db.dropcolumn.bin