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

[Markus Metz]

On Wed, Nov 30, 2011 at 10:24 PM, Dylan Beaudette
<debeaudette at ucdavis.edu> 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:

Please try trunk r49802.

The bug was in the sqlite driver who assumes a default column length
of 1 which is then adjusted according to the declared column type. A
declared column type CHARACTER without length info slipped through
with length = 1, although it is really a variable width TEXT field
which gets a dummy width of 1000 for clients which don't understand
variable-size fields.

Markus M

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