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

[Markus Metz]

On Fri, Dec 2, 2011 at 5:36 AM, Dylan Beaudette
<dylan.beaudette at gmail.com> wrote:
> 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.
>
Ah, I see. The truncation to length 1 probably occurs whenever sqlite
CHARACTER fields are exported to another db backend that needs
something like VARCHAR(50). I guess this needs to be fixed in lib/db
or db/drivers rather than fixing on module level, unfortunately.

Markus M

> 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