[GRASS-user] Error in sqlite3_step()

[Moritz Lennert]

On 03/04/09 11:34, Craig Leat wrote:
> I have a problem with v.db.dropcol, it's changing the length of columns.
> 
> db.describe -c table=SBC1 driver=sqlite
> database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
> ncols: 6
> nrows: 197842
> Column 1: cat:INTEGER:20
> Column 2: CLASS_NAME:CHARACTER:1
> Column 3: LM:CHARACTER:1
> Column 4: WARD:INTEGER:20
> Column 5: building:CHARACTER:20
> Column 6: LM_name:CHARACTER:20
> 
> v.db.dropcol map=SBC1 col=CLASS_NAME
> 
> db.describe -c table=SBC1 driver=sqlite
> database=/home/craig/GIS/grassdata/uMDM_ll/craig/sqlite.db
> ncols: 5
> nrows: 197842
> Column 1: cat:INTEGER:20
> Column 2: LM:CHARACTER:1
> Column 3: WARD:INTEGER:20
> Column 4: building:CHARACTER:1
> Column 5: LM_name:CHARACTER:1
> 
> building and LM_name have changed from length=20 to length=1

Don't have time to look into this now, but it must be a problem in the 
special sqlite implementation in that script:

if [ "$driver" = "sqlite" ] ; then
         #echo "Using special trick for SQLite"
         # http://www.sqlite.org/faq.html#q13
         v.info --q -c map=$GIS_OPT_MAP layer=$GIS_OPT_LAYER | cut -d'|' 
-f1,2 | grep -v "|${col}$" > "$TMP.coldesc"
         # need to revert order:
         cat "$TMP.coldesc" | cut -d'|' -f1 > "$TMP.coltypes"
         cat "$TMP.coldesc" | cut -d'|' -f2 > "$TMP.colnames"
         COLDEF=`paste -d' ' "$TMP.colnames" "$TMP.coltypes" | tr '\n' 
',' | sed 's+,$++g'`
         COLNAMES=`cat "$TMP.colnames" | tr '\n' ',' | sed 's+,$++g'`
echo "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE ${table}_backup(${COLDEF});
INSERT INTO ${table}_backup SELECT ${COLNAMES} FROM ${table};
DROP TABLE ${table};
CREATE TABLE ${table}(${COLDEF});
INSERT INTO ${table} SELECT ${COLNAMES} FROM ${table}_backup;
DROP TABLE ${table}_backup;
COMMIT;" > "$TMP"
db.execute input="$TMP"

You could try the individual steps and see where the problem comes from.


> I'll test with Spearfish and update GRASS before reporting a bug, but
> this may take some time. First I have to finish this job with a broken
> v.db.dropcol. It's time to play with sqlitebrowser...

You can also try this:
https://addons.mozilla.org/en-US/firefox/addon/5817

Moritz