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

[Dylan Beaudette]

On Friday, December 02, 2011, Markus Metz wrote:
> 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


OK. Should I still file a related ticket?

Thanks,
Dylan


> > 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
> 


-- 
Dylan E. Beaudette
USDA-NRCS Soil Scientist
California Soil Resource Lab
http://casoilresource.lawr.ucdavis.edu/