[GRASS-user] Rename multiple sqlite columns at once

GRASS GRASS user
1

Is there (hopefully?) any way to translate for example all Caps to lower
case letters in all column names of a vector maps attribute tavble
(stored in sqlite.db)?

Any way to apply similar techniques like with common GNU text utilities
(i.e. tr, sed, cut)?

Kind regards, Nikos

2

On Mon, 2008-11-03 at 16:58 +0100, Nikos Alexandris wrote:

Is there (hopefully?) any way to translate for example all Caps to lower
case letters in all column names of a vector maps attribute tavble
(stored in sqlite.db)?

Any way to apply similar techniques like with common GNU text utilities
(i.e. tr, sed, cut)?

Kind regards, 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?

3

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

4

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

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

Grazie mille, Nikos

5

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

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

6

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

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

7

On Wed, 2008-11-05 at 19:36 +0100, 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:

[...]

I tried another example using "cut" this time.

----
#!/bin/sh

CAT="cat"
PREFIX="tmp_"
for COLUMN in `db.columns test`; do
        if [ "$COLUMN" != "$CAT" ]; then
                PRETEMP=`echo $COLUMN | awk '{print tolower($1)}'`
                TEMP=$PREFIX$PRETEMP
                v.db.renamecol map=test layer=1 column=$COLUMN,$TEMP
                lower_column=`echo $TEMP | cut -d"_" -f2`
                v.db.renamecol map=test layer=1 column=$TEMP,
$lower_column
        fi
done

db.columns test
----

It works (final columns are renamed/reported all with lower case letters
by db.columns) BUT it produces lots of errors? Is this a bug? Below the
details:

#initial test vector map
db.columns test

block_id
cat
AREA
PERIMETER
RAS2X2_
RAS2X2_ID
RAS2X2_NR
block
STAND
BEFL_DATUM
BEFL_JAHR
QUALITAET

# running the "cut" script
sh ./caps_2_lower_cut.sh

DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: block

ERROR: Error while executing: 'ALTER TABLE test ADD COLUMN block INTEGER
       '
ERROR: Cannot continue (problem adding column).
ERROR: Cannot continue (problem renaming column)
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: ras2x2

ERROR: Error while executing: 'ALTER TABLE test ADD COLUMN ras2x2 DOUBLE
       PRECISION
       '
ERROR: Cannot continue (problem adding column).
ERROR: Cannot continue (problem renaming column)
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: ras2x2

ERROR: Error while executing: 'ALTER TABLE test ADD COLUMN ras2x2
       varchar(1)
       '
ERROR: Cannot continue (problem adding column).
ERROR: Cannot continue (problem renaming column)
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: unable to parse decltype: CHARACTER INTEGER
WARNING: SQLite driver: column 'tmp_block', 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 'tmp_block', SQLite type 1 is not
supported
ERROR: Column <tmp_block> not found in table
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: befl

ERROR: Error while executing: 'ALTER TABLE test ADD COLUMN befl INTEGER
       '
ERROR: Cannot continue (problem adding column).
ERROR: Cannot continue (problem renaming column)
cat
tmp_block_id
area
perimeter
ras2x2
tmp_ras2x2_id
tmp_ras2x2_nr
stand
befl
tmp_befl_jahr
qualitaet

8

On Wed, 2008-11-05 at 19:51 +0100, Nikos Alexandris wrote:

On Wed, 2008-11-05 at 19:36 +0100, 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:
[...]

[...]

Well, it does not work after all the way it should. Normally since my
column names have a "_" character. I am working on it... :slight_smile:

9

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

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

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

10

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

--
Paolo

11

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.

12

On 06/11/08 17:37, Nikos Alexandris wrote:

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))}'`

[...]

# output
block_id\_tmp

I think this is because of the quotes:

tempcolumn="$tempcolumn""\_tmp"

But actually, trying out your entire original script, I don't see where the problem is. The attached version seems to work perfectly for me (just slightly simplified from your original version):

What exactly did not work when you tried this ?

Moritz

(attachments)

change_case.sh (413 Bytes)

13

On Thu, 2008-11-06 at 19:59 +0100, Moritz Lennert wrote:

[...]

>
> # output
> block_id\_tmp

I think this is because of the quotes:

tempcolumn="$tempcolumn""\_tmp"

But actually, trying out your entire original script, I don't see
where
the problem is. The attached version seems to work perfectly for me
(just slightly simplified from your original version):

What exactly did not work when you tried this ?

Moritz

Moritz,
thank you for your time.

1. I *corrected* the double quotes and re-run the script. [Script +
output attached]

2. I tried your latest script (the one you attached in the previous
message). It works but I still get those column-type-related WARNINGS.
[Script + output attached]

Kind Regards, Nikos

(attachments)

caps_2_lower_moritzx2.output (6.98 KB)
change_case.output (1.32 KB)

14

On 06/11/08 20:33, Nikos Alexandris wrote:

On Thu, 2008-11-06 at 19:59 +0100, Moritz Lennert wrote:

[...]

# output
block_id\_tmp

I think this is because of the quotes:

tempcolumn="$tempcolumn""\_tmp"

But actually, trying out your entire original script, I don't see
where the problem is. The attached version seems to work perfectly for me (just slightly simplified from your original version):

What exactly did not work when you tried this ?

Moritz

Moritz,
thank you for your time.

1. I *corrected* the double quotes and re-run the script. [Script +
output attached]

You still use quotes:
tempcolumn="$tempcolumn\_tmp"

IIUC, the quotes will let the slash appear literally. So, just leave out the quotes as follows:

tempcolumn=$tempcolumn\_tmp

However, as you say your original script (in my modified form) actually worked, it just spit out a series of warnings. So it might be more interesting to check upon these warnings. I only saw these with the sqlite driver, not with dbf or postgresql, so we should try to find out what happens with that driver.

Moritz

2. I tried your latest script (the one you attached in the previous
message). It works but I still get those column-type-related WARNINGS.
[Script + output attached]

Kind Regards, Nikos

------------------------------------------------------------------------

# I *corrected* the tempcolumn="$tempcolumn""\_tmp"
## Actually it didn't work in first place this is why I experimented a bit by adding the double quotes!

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 when running the above
sh ./caps_2_lower_moritzx.sh > caps_2_lower_moritzx2

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

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <AREA> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <PERIMETER> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <RAS2X2_> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <RAS2X2_ID> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <RAS2X2_NR> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <block> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <STAND> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <BEFL_DATUM> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <BEFL_JAHR> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
ERROR: Column <QUALITAET> not found in table
Sorry <_tmp> is not a valid option

Description:
Renames a column in the attribute table connected to a given vector map.

Keywords:
vector, database, attribute table

Usage:
v.db.renamecol map=name [layer=value] column=string[,string,...]
   [--verbose] [--quiet]

Flags:
--v Verbose module output
--q Quiet module output

Parameters:
     map Vector map for which to rename attribute column
   layer Layer where to rename column
           default: 1
  column Old and new name of the column (old,new)
[Raster MASK present]

------------------------------------------------------------------------

# Moritz's script...
cat change_case.sh -n

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

# running the above works but I still get some WARNINGS... ??
sh ./change_case.sh

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: column 'block_tmp', SQLite type 1 is not supported
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

15

On Fri, 2008-11-07 at 01:38 +0100, Moritz Lennert wrote:

You still use quotes:
tempcolumn="$tempcolumn\_tmp"

IIUC, the quotes will let the slash appear literally. So, just leave
out
the quotes as follows:

tempcolumn=$tempcolumn\_tmp

However, as you say your original script (in my modified form)
actually
worked, it just spit out a series of warnings. So it might be more
interesting to check upon these warnings. I only saw these with the
sqlite driver, not with dbf or postgresql, so we should try to find
out
what happens with that driver.

Moritz

Oh, sorry Moritz. Now I see. I am at your service :slight_smile: Tell me what/how
to test?

At a glance:

* I have a shapefile with more than 30 columns (the test shapefile is
just a test).

* I imported in GRASS (using sqlite backend).

* sqlitebrowser reports "correct" column types (integere, double
precision, varchar)

* db.describe reports columnt type with CAPS and CHARACTER instead of
varchar

16

Moritz Lennert wrote:

>> I think this is because of the quotes:
>>
>> tempcolumn="$tempcolumn""\_tmp"
>>
>> But actually, trying out your entire original script, I don't see
>> where
>> the problem is. The attached version seems to work perfectly for me
>> (just slightly simplified from your original version):
>>
>> What exactly did not work when you tried this ?
>>
>> Moritz
>
> Moritz,
> thank you for your time.
>
> 1. I *corrected* the double quotes and re-run the script. [Script +
> output attached]

You still use quotes:
tempcolumn="$tempcolumn\_tmp"

IIUC, the quotes will let the slash appear literally. So, just leave out
the quotes as follows:

tempcolumn=$tempcolumn\_tmp

If you just want to prevent the underscore from being treated as part
of the variable name, use braces:

  tempcolumn=${tempcolumn}_tmp

or, if the variable's value may contain spaces::

  tempcolumn="${tempcolumn}_tmp"

--
Glynn Clements <glynn@gclements.plus.com>

17

On Fri, 2008-11-07 at 06:39 +0000, Glynn Clements wrote:

Moritz Lennert wrote:

[...]

> You still use quotes:
> tempcolumn="$tempcolumn\_tmp"
>
> IIUC, the quotes will let the slash appear literally. So, just leave out
> the quotes as follows:
>
> tempcolumn=$tempcolumn\_tmp

If you just want to prevent the underscore from being treated as part
of the variable name, use braces:

  tempcolumn=${tempcolumn}_tmp

or, if the variable's value may contain spaces::

  tempcolumn="${tempcolumn}_tmp"

Thanks Glynn. I suppose this way it's safer.

18

On 07/11/08 01:47, Nikos Alexandris wrote:

On Fri, 2008-11-07 at 01:38 +0100, Moritz Lennert wrote:

You still use quotes:
tempcolumn="$tempcolumn\_tmp"

IIUC, the quotes will let the slash appear literally. So, just leave
out the quotes as follows:

tempcolumn=$tempcolumn\_tmp

However, as you say your original script (in my modified form)
actually worked, it just spit out a series of warnings. So it might be more interesting to check upon these warnings. I only saw these with the sqlite driver, not with dbf or postgresql, so we should try to find
out what happens with that driver.

Moritz

Oh, sorry Moritz. Now I see. I am at your service :slight_smile: Tell me what/how
to test?

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

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 !

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

19

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 :slight_smile: ***

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

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

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

*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 :slight_smile: ***

*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 :slight_smile: ***

*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 :slight_smile: ***

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

*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 :slight_smile: ***

*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 :slight_smile: ***

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

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

# initial column "block" got lost :frowning:

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

20

Hi,

I haven't really been following this thread (or know much about it), but
I spotted this tip on the gdal site, maybe it helps:

http://www.gdal.org/ogr/drv_gpx.html
# Use of -sql option to remap field names to the ones allowed by the GPX schema !

ogr2ogr -f GPX output.gpx input.shp -sql "SELECT field1 AS name, field2 AS desc FROM source_layer"

Hamish