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

[Markus Metz]

On Mon, Dec 12, 2011 at 5:29 PM, Dylan Beaudette
<debeaudette at ucdavis.edu> wrote:
> 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?
>
Yes, please file a ticket since this should be fixed generically and
not be forgotten...

Markus M

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