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

GRASS GIS GRASS GIS developer
1

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

(attachments)

v.db.dropcolumn.diff (1.08 KB)

2

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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

3

On Thu, Dec 1, 2011 at 1:25 AM, Markus Metz
<markus.metz.giswork@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@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.

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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

4

On Fri, Dec 2, 2011 at 5:36 AM, Dylan Beaudette
<dylan.beaudette@gmail.com> wrote:

On Thu, Dec 1, 2011 at 1:25 AM, Markus Metz
<markus.metz.giswork@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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

5

On Friday, December 02, 2011, Markus Metz wrote:

On Fri, Dec 2, 2011 at 5:36 AM, Dylan Beaudette
<dylan.beaudette@gmail.com> wrote:
> On Thu, Dec 1, 2011 at 1:25 AM, Markus Metz
> <markus.metz.giswork@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@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@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/

6

On Mon, Dec 12, 2011 at 5:29 PM, Dylan Beaudette
<debeaudette@ucdavis.edu> wrote:

On Friday, December 02, 2011, Markus Metz wrote:

On Fri, Dec 2, 2011 at 5:36 AM, Dylan Beaudette
<dylan.beaudette@gmail.com> wrote:
> On Thu, Dec 1, 2011 at 1:25 AM, Markus Metz
> <markus.metz.giswork@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@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@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/

7

On Wed, Nov 30, 2011 at 10:24 PM, Dylan Beaudette
<debeaudette@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-dev

8

On Sun, Dec 18, 2011 at 12:07 PM, Markus Metz
<markus.metz.giswork@googlemail.com> wrote:

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

Great for catching this. If it helps, I vote for backporting to 6.4 to
let it be in the upcoming 6.4.2 release.

Markus

9

On Sunday, December 18, 2011, Markus Neteler wrote:

On Sun, Dec 18, 2011 at 12:07 PM, Markus Metz
<markus.metz.giswork@googlemail.com> wrote:
> On Wed, Nov 30, 2011 at 10:24 PM, Dylan Beaudette
> <debeaudette@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.

Great for catching this. If it helps, I vote for backporting to 6.4 to
let it be in the upcoming 6.4.2 release.

Markus

Thanks for making those fixes, much appreciated!

Dylan

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

10

On Mon, Dec 19, 2011 at 6:13 AM, Markus Neteler <neteler@osgeo.org> wrote:

On Sun, Dec 18, 2011 at 12:07 PM, Markus Metz
<markus.metz.giswork@googlemail.com> wrote:

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

Great for catching this. If it helps, I vote for backporting to 6.4 to
let it be in the upcoming 6.4.2 release.

Done in r49862.

Markus M