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

[Dylan Beaudette]

On Thu, Dec 1, 2011 at 1:25 AM, Markus Metz
<markus.metz.giswork at googlemail.com> wrote:
> Dylan Beaudette wrote:
>> 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...
>
> I can not reproduce this in the sqlite mapset of nc_spm_08:
>
> g.copy vect=boundary_county at PERMANENT,my_boundary_county
> v.build map=my_boundary_county
> # make sure this is really sqlite
> v.db.connect -g map=my_boundary_county layer=1
>
> v.db.select -v map=my_boundary_county layer=1 where='cat = 1'
>
> v.db.dropcolumn map=my_boundary_county layer=1 columns=CO_WIKIPED
>
> v.db.select -v map=my_boundary_county layer=1 where='cat = 1'
>
> --> output looks fine, CHARACTER fields are not truncated
>
> ??
>
> Markus M
>

Thanks for looking into this Markus-- I noticed this as well. Take a
look at the output from db.describe: there maybe some CHARACTER fields
with length of 1. This doesn't seem to affect any aspect of how these
data are used within GRASS. However, when exported with v.out.ogr, it
seems that the fields are truncated.

Dylan


>
>> 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/
>>
>> _______________________________________________
>> grass-dev mailing list
>> grass-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/grass-dev