[GRASS-user] Rename multiple sqlite columns at once

[Moritz Lennert]

On 05/11/08 19:36, Nikos Alexandris wrote:
> On Wed, 2008-11-05 at 17:33 +0100, Moritz Lennert wrote:
>> On 05/11/08 15:17, Nikos Alexandris wrote:
>>> On Tue, 2008-11-04 at 10:35 +0100, Paolo Craveri wrote:
>>>> Hello Nikos
>>>>
>>>>> The command
>>>>> v.db.renamecol map=test_rencol column="AREA","area"
>>>>>
>>>>> does not work. I assume that trying to use 'tr' for example wouldn't
>>>>> help (?). Or am I doing something wrong?
>>>>>
>>>> Try this:
>>>>
>>>>
>>>> ==============
>>>> #!/bin/sh
>>>> # # # in this example I use nc_spm_07 dataset
>>>> # # # I created a sqlite mapset
>>>> db.connect driver=sqlite \
>>>> 	database='$GISDBASE/$LOCATION_NAME/$MAPSET/my_sqlitedb.db'
>>>> db.connect -p
>>>> g.copy vect=hospitals,myhospitals --overwrite
>>>> v.db.connect myhospitals -p
>>>> db.columns myhospitals
>>>> #
>>>> # use awk, function: tolower
>>>> # echo "MINNIE" | awk '{print tolower($1)}'
>>>> # minnie
>>>> # ...and function  substr
>>>> #  echo "minnie_tmp"| awk '{print substr($1,1,length($1)-length("_tmp"))}'
>>>> # minnie
>>>>
>>>> #skip out duplicate column name error
>>>> # (no differences between lower and upper case)
>>>> #
>>>> #
>>>> key_field="cat"
>>>> suffix="_tmp"
>>>> #THIS MAY TAKE A WHILE...
>>>> for field in `db.columns myhospitals`; do
>>>> 	if [ "$field" != "$key_field" ]; then
>>>> 		tmpfield=`echo $field | awk '{print tolower($1)}'`
>>>> 		tmpfield=$tmpfield$suffix
>>>> 		v.db.renamecol map=myhospitals layer=1 column=$field,$tmpfield
>>>> 		lower_field=`echo $tmpfield $suffix| awk '{print
>>>> substr($1,1,length($1)-length($2))}'`
>>>> 		v.db.renamecol map=myhospitals layer=1 column=$tmpfield,$lower_field
>>>> 	fi
>>>> done
>>>>
>>>> db.columns myhospitals
>>>> =============================
>>>>
>>>>
>>>>
>>>> there are same better and more elegant solutions probably...
>>>>
>>>>
>>>> ciao
>>>>
>>>>
>>>> Paolo
>>> Ciao Paolo!
>>>
>>> Looks like a nice learning-example to me :-)
>>>
>>> It works!! But still I get some error/warning from "v.db.renamecol".
>>> Don't know, I changed it a bit... :
>>> ----
>>> #!/bin/sh
>>>
>>> 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
>>> ----
>>>
>>> The error is:
>>> Sorry <_tmp> is not a valid option
>> I think you might have to escape _tmp with a backslash, i.e. "\_tmp".
>>
>> Moritz
> 
> Hi Moritz, The DB-Wizard :-)

My suggestion did not concern any DB issues, but bash...

> 
> I 've replaced "_tmp" with "\_tmp" but no luck.
> ----
> DBMI-SQLite driver error:
> Error in sqlite3_prepare():
> unrecognized token: "\"
> 
> ERROR: Error while executing: 'ALTER TABLE test ADD COLUMN block_id\_tmp
>        INTEGER
>        '
> ERROR: Cannot continue (problem adding column).
> ERROR: Cannot continue (problem renaming column)
> Sorry <\_tmp> is not a valid option
> [...]
> ----
> 
> Using only "tmp" I get: "Sorry <tmp> is not a valid option"
> 
> So, the problem is somewhere else and not in the string (?)

I guess it doesn't work when you add it to a variable which you then use 
in concatenation.

Maybe you could just replace (in your original script)

tmpfield=$tmpfield$suffix

with

tmpfield=$tmpfield\_tmp

But I'm not sure that this is where the problem lies.

Maybe you could add -x after /bin/sh at the beginning of your script to 
debug and see where the problem is.

Or just do an 'echo $tmpcolumn' before the v.db.renamecol to see what 
the variable contains.

Moritz