v.db.join and CHARACTER type columns

[micha]

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

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

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

[neteler]

Please regenerate the patch with "diff -u" from the main GRASS source code directory.

[micha]

New patch with -u

[micha]

Replying to neteler:

Please regenerate the patch with "diff -u" from the main GRASS source code directory.

Sorry, please try this one

[neteler]

As the application of the patch failed, can you please recreate the patch against GRASS 6.5.svn (develbranch_6)? Thanks.

[martinl]

Ticket retargeted after milestone closed