[GRASS-dev] [GRASS GIS] #1631: v.db.join and CHARACTER type columns

[GRASS GIS]

#1631: v.db.join and CHARACTER type columns
---------------------------+------------------------------------------------
 Reporter:  micha          |       Owner:  grass-dev@…              
     Type:  defect         |      Status:  new                      
 Priority:  normal         |   Milestone:  6.4.3                    
Component:  Shell Scripts  |     Version:  6.4.2                    
 Keywords:                 |    Platform:  Unspecified              
      Cpu:  Unspecified    |  
---------------------------+------------------------------------------------
 The v.db.join script has a shortcoming in that it does not check the
 length of datatypes when listing the columns from the 'otable' data table.
 The script then creates new columns in the target vector with wrong data
 types.
 For example, if the 'otable' contains columns of type smallint, integer,
 bigint these all become integer ("int4") in the final table. Similarly,
 columns of type real and double are both created as double in the final
 join. I guess this is a limitation of the postgresql db driver (?)

 More serious is the case of character columns. Postgresql, by default,
 creates a CHARACTER column of size 1, unless the size is explicitly stated
 in the ADD COLUMN statement.

 ---Quote from the Postgresql Manual---[[BR]]
 ''
 The notations varchar(n) and char(n) are aliases for character varying(n)
 and character(n), respectively. character without length specifier is
 equivalent to character(1). If character varying is used without length
 specifier, the type accepts strings of any size. The latter is a
 PostgreSQL extension.''[[BR]]

 ---end quote---

 Since the v.db.join script (using v.db.addcol) creates the new column as
 type CHARACTER, it becomes length 1 in the joined table. This is almost
 always wrong.

 BTW, this problem does not occur with sqlite based tables since sqlite
 ignores the length of data types

 ---Quote from sqlite3 site----[[BR]]
 ''
 Note that numeric arguments in parentheses that following the type name
 (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any
 length restrictions (other than the large global SQLITE_MAX_LENGTH limit)
 on the length of strings, BLOBs or numeric values.''[[BR]]

 ---end quote---


 As a possible work around, here's a diff for the v.db.join script which
 collects both column type and column length, and uses both to send a more
 correct column definition to v.db.addcol.

 {{{

 [root at MS-SL6 scripts]# diff v.db.join.orig v.db.join
 107a108,109
 > # MS: Save column size for CHARACTER VARYING datatype
 > COLSIZES=`db.describe -c driver="$driver" database="$database"
 table="$GIS_OPT_OTABLE" | grep '^Column ' | cut -d':' -f4`
 111c113,126
 <   v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo
 $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
 ---
 >       coltype=`echo $COLTYPES | cut -d' ' -f$i`
 >       colsize=`echo $COLSIZES | cut -d' ' -f$i`
 >       case "$coltype" in
 >               CHARACTER)
 >                       coldef="VARCHAR($colsize)"
 >               ;;
 >               *)
 >                       coldef=`echo $COLTYPES | cut -d' ' -f$i | tr -s
 '_' ' '`
 >               ;;
 >       esac
 >
 > # v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col `echo
 $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
 > #     g.message -i "Adding column $col $coldef"
 >   v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" col="$col $coldef"

 }}}

-- 
Ticket URL: <http://trac.osgeo.org/grass/ticket/1631>
GRASS GIS <http://grass.osgeo.org>