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

[Markus Metz]

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


> 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