[GRASS-user] Rename multiple sqlite columns at once

[Nikos Alexandris]

On Thu, 2008-11-13 at 10:28 +0100, Moritz Lennert wrote:
> On 07/11/08 01:47, Nikos Alexandris wrote:
> > On Fri, 2008-11-07 at 01:38 +0100, Moritz Lennert wrote:
[...]

> Sorry, for not coming back on this earlier, but I think I found it now. 
> The error (which also appears in other drivers, at least dbf), is due to 
> the following line in v.db.renamecol:
> 
> oldcoltype="`db.describe -c table=$table database=$database\
> 		driver=$driver | grep $oldcol | cut -d':' -f3`"
> 
> If you have two columns with the name of the first a subset of the name 
> of the second (e.g. DATA and DATA2, or in the nc_spm_06 dataset's 
> comm_colleges map, CC_ and CC_NAME and CCL_ and CCL_ID), the grep in the 
> above line will find both lines and result in an invalid oldcoltype:
> 
> GRASS 6.4.svn (nc_spm_06):~ > db.describe -c table=test | grep CC_ 
> Column 7: CC_:CHARACTER:8
> Column 8: CC_NAME:CHARACTER:30
> 
> GRASS 6.4.svn (nc_spm_06):~ > db.describe -c table=test | grep CC_ | 
> cut -d':' -f3
> CHARACTER
> CHARACTER
> 
> GRASS 6.4.svn (nc_spm_06):~ > oldcoltype="`db.describe -c table=test | 
> grep CC_ |  cut -d':' -f3`"
> GRASS 6.4.svn (nc_spm_06):~ > echo $oldcoltype
> CHARACTER CHARACTER
> 
> Which then obviously leads to an error as 'CHARACTER CHARACTER' is not a 
> valid column type.
> 
> Can you confirm that this is the case for you as well ?
> And actually this is not only a warning issue, but the column is lost 
> because of this !

Well, not exactly. Columns are renamed in the end (even if some names
are "subsets" of other column names) but it looses a column which was
already in lower case!? See example below (using sqlite):

# some table
db.describe befliegung_copy -c

ncols: 12
nrows: 361
Column 1: block_id:INTEGER:20
Column 2: cat:INTEGER:20
Column 3: AREA:DOUBLE PRECISION:20
Column 4: PERIMETER:DOUBLE PRECISION:20
Column 5: RAS2X2_:DOUBLE PRECISION:20
Column 6: RAS2X2_ID:DOUBLE PRECISION:20
Column 7: RAS2X2_NR:CHARACTER:13
Column 8: block:CHARACTER:40
Column 9: STAND:CHARACTER:16
Column 10: BEFL_DATUM:CHARACTER:16
Column 11: BEFL_JAHR:INTEGER:20
Column 12: QUALITAET:CHARACTER:16

# using you script change_case.sh (added some echo's)
sh ./change_case.sh befliegung_copy

*Original* column name is: block_id
*Temporary* set to: block_id_tmp
New column name will be: block_id
*** Column block_id renamed to block_id :-) ***

*Original* column name is: AREA
*Temporary* set to: AREA_tmp
New column name will be: area
*** Column AREA renamed to area :-) ***

*Original* column name is: PERIMETER
*Temporary* set to: PERIMETER_tmp
New column name will be: perimeter
*** Column PERIMETER renamed to perimeter :-) ***

*Original* column name is: RAS2X2_
*Temporary* set to: RAS2X2__tmp
New column name will be: ras2x2_
*** Column RAS2X2_ renamed to ras2x2_ :-) ***

*Original* column name is: RAS2X2_ID
*Temporary* set to: RAS2X2_ID_tmp
New column name will be: ras2x2_id
*** Column RAS2X2_ID renamed to ras2x2_id :-) ***

*Original* column name is: RAS2X2_NR
*Temporary* set to: RAS2X2_NR_tmp
New column name will be: ras2x2_nr
*** Column RAS2X2_NR renamed to ras2x2_nr :-) ***

*Original* column name is: block
*Temporary* set to: block_tmp
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: column 'block_tmp', SQLite type 1 is not
supported
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: column 'block_tmp', SQLite type 1 is not
supported
New column name will be: block
ERROR: Column <block_tmp> not found in table
*** Column block renamed to block :-) ***

*Original* column name is: STAND
*Temporary* set to: STAND_tmp
New column name will be: stand
*** Column STAND renamed to stand :-) ***

*Original* column name is: BEFL_DATUM
*Temporary* set to: BEFL_DATUM_tmp
New column name will be: befl_datum
*** Column BEFL_DATUM renamed to befl_datum :-) ***

*Original* column name is: BEFL_JAHR
*Temporary* set to: BEFL_JAHR_tmp
New column name will be: befl_jahr
*** Column BEFL_JAHR renamed to befl_jahr :-) ***

*Original* column name is: QUALITAET
*Temporary* set to: QUALITAET_tmp
New column name will be: qualitaet
*** Column QUALITAET renamed to qualitaet :-) ***

Result is...
cat
block_id
area
perimeter
ras2x2_
ras2x2_id
ras2x2_nr
stand
befl_datum
befl_jahr
qualitaet

# initial column "block" got lost :-(

> I don't have the time right now to try to figure out a solution, but you 
> can either give it a try yourself, or file a bug report, and I'll get 
> back to it when I have time.
> 
> Moritz

Hope this helps.
Regards, Nikos