[GRASS-user] Rename multiple sqlite columns at once

[Nikos Alexandris]

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 :-)

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 (?)

Kind regards, Nikos