[GRASS-user] Rename multiple sqlite columns at once

[Nikos Alexandris]

On Thu, 2008-11-06 at 11:18 +0100, Paolo Craveri wrote:
> ciao to all
> 
> pay attention to spaces between column name in v.db.renamecol: no
> spaces before and after comma.
> 
> Command substitution: backticks → $(....)  not substantial
> differences; the second form is easier to read and write; it is also
> nestable (we are all used to `    ` form, but $() is better (IMHO))
> 
> This should work (probably):
> 
> ================================
> db.columns test
> 
> key_column="cat"
> suffix="_tmp"
> for column in `db.columns test`; do
>        if [ "$column" != "$key_column" ]; then
>                tempcolumn=$(echo $column | awk '{print tolower($1)}')
>                tempcolumn=$tempcolumn$suffix
>                v.db.renamecol map=test layer=1 column=$column,$tempcolumn
>                lower_column=$(echo $tempcolumn $suffix| awk '{print
> substr($1,1,length($1)-length($2))}')
>                v.db.renamecol map=test layer=1 column=$tempcolumn,$lower_column
>        fi
> done
> db.columns test
> 
> ===================
> 
> ciao

Ciao Paolo & Moritz!
Thank you for your assistance.


@Moritz: I tried it but still not clean.
# cat caps_2_lower_moritzx.sh -n
     1	#!/bin/sh -x
     2	
     3	key_column="cat"
     4	suffix="\_tmp"
     5	for column in `db.columns test`; do
     6	        if [ "$column" != "$key_column" ]; then
     7	                tempcolumn=`echo $column | awk '{print
tolower($1)}'`
     8	                tempcolumn="$tempcolumn""\_tmp"
     9			echo $tempcolumn
    10	                v.db.renamecol map=befliegung_copy column=
$column,$tempcolumn
    11			lower_column=`echo $tempcolumn $suffix | awk '{print
    12	substr($1,1,length($1)-length($2))}'`
    13	                v.db.renamecol map=test layer=1 column=
$tempcolumn,$lower_column
    14	        fi
    15	done
    16	
    17	db.columns test

# output
block_id\_tmp
ERROR: Column <block_id> not found in table
Sorry <\_tmp> is not a valid option

## and the "Sorry..." message is repeated along with other stuff.


@Paolo:

I still get the "Sorry <_tmp> is not a valid option" message and the
final column names have the "_tmp" suffix! I did some testing but
nothing really clean came out.

Since I can't work it out I attempted this time with the "sed" utility.
Now it works with some warnings (relates with the column types?)!!

It looks like that:

     1   #! /bin/sh -x
     2	db.columns test
     3	
     4	key_column="cat"
     5	suffix="_tmp"
     6	for column in `db.columns test`; do
     7		if [ "$column" != "$key_column" ]; then
     8			tempcolumn=$(echo $column | awk '{print tolower($1)}')
     9			tempcolumn=$tempcolumn$suffix
    10			echo $tempcolumn
    11			v.db.renamecol map=test layer=1 column=$column,$tempcolumn
    12			lower_column=`echo $tempcolumn | sed 's:'$suffix'::'`
    13			v.db.renamecol map=test layer=1 column=$tempcolumn,
$lower_column
    14		fi
    15	
    16	done
    17	db.columns test

----
# Executing this script gives
sh ./caps_2_lower_sed.sh

block_id
cat
AREA
PERIMETER
RAS2X2_
RAS2X2_ID
RAS2X2_NR
block
STAND
BEFL_DATUM
BEFL_JAHR
QUALITAET
block_id_tmp
area_tmp
perimeter_tmp
ras2x2__tmp
ras2x2_id_tmp
ras2x2_nr_tmp
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
ERROR: Column <block_tmp> not found in table
stand_tmp
befl_datum_tmp
befl_jahr_tmp
qualitaet_tmp
cat
block_id
area
perimeter
ras2x2_
ras2x2_id
ras2x2_nr
stand
befl_datum
befl_jahr
qualitaet
----

I have checked the original vector map and all column types are grass- &
sqlite-acceptable I think (?)...

# db.describe
db.describe -c befliegung

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

But according to sqlitebrowser, the same attribute table has all "type"
entries written in lower case (that is: integer, double precision) and
instead of CHARACTER (or character) it is varchar(*somenumber here...*).

Question: why db.describe reports different column types?? I have
checked the db connection and it is linked to the correct sqlite.db
file.