[GRASS-dev] implementing drop column into DBF driver
Markus Neteler
neteler at itc.it
Mon Dec 4 11:33:27 EST 2006
Hi,
I tried to implement drop column into DBF driver to get
rid of columns like 'cat_' when re-importing an exported map:
echo "ALTER TABLE mymap DROP COLUMN cat_" | db.execute
Attached the changes to lib/db/sqlp/ and /db/drivers/dbf/
The idea is to load the table internally in SQLP_DROP_COLUMN
(such as SQLP_ADD_COLUMN does) but to jump over the column
which we want to drop.
So I took load_table() and made it load_table_selective(),
most likely in an ugly way.
While ugly, it could work but apparently I never reach
SQLP_DROP_COLUMN. It bails out with
...
D3/3: sql: ALTER TABLE mymap DROP COLUMN cat_
D3/3: SQL statement parsed successfully: ALTER TABLE mymap DROP COLUMN cat_
...
D3/3: add_column(): tab = 11, type = 3, name = edg_id, width = 20, decimals = 6
D3/3: Doing SQL command <8> on DBF table... (see include/sqlp.h)
dbmi: Protocol error
D2/3: G__home home = /home/neteler
ERROR: Error while executing: "ALTER TABLE mymap DROP COLUMN cat_
"
I have no clue why. Help is welcome.
Thanks,
Markus
-------------- next part --------------
? include/config.h
? include/version.h
? include/winname.h
? include/Make/Grass.make
? include/Make/Platform.make
? lib/db/dbmi_base/OBJ.i686-pc-linux-gnu
? lib/db/dbmi_client/OBJ.i686-pc-linux-gnu
? lib/db/dbmi_driver/OBJ.i686-pc-linux-gnu
? lib/db/sqlp/OBJ.i686-pc-linux-gnu
? lib/db/sqlp/lex.yy.c
? lib/db/sqlp/y.output
? lib/db/sqlp/y.tab.c
? lib/db/sqlp/y.tab.h
? lib/db/stubs/OBJ.i686-pc-linux-gnu
Index: include/sqlp.h
===================================================================
RCS file: /home/grass/grassrepository/grass6/include/sqlp.h,v
retrieving revision 1.18
diff -u -r1.18 sqlp.h
--- include/sqlp.h 10 Oct 2006 08:21:34 -0000 1.18
+++ include/sqlp.h 4 Dec 2006 16:14:10 -0000
@@ -10,6 +10,7 @@
#define SQLP_UPDATE 5
#define SQLP_DELETE 6
#define SQLP_ADD_COLUMN 7
+#define SQLP_DROP_COLUMN 8
/* SQL OPERATORS */
/* Arithmetical */
Index: lib/db/sqlp/print.c
===================================================================
RCS file: /home/grass/grassrepository/grass6/lib/db/sqlp/print.c,v
retrieving revision 1.12
diff -u -r1.12 print.c
--- lib/db/sqlp/print.c 10 Oct 2006 08:19:34 -0000 1.12
+++ lib/db/sqlp/print.c 4 Dec 2006 16:14:10 -0000
@@ -75,6 +75,9 @@
case (SQLP_DROP):
fprintf( stderr, "DROP\n");
break;
+ case (SQLP_DROP_COLUMN):
+ fprintf( stderr, "DROP COLUMN\n");
+ break;
case (SQLP_INSERT):
fprintf( stderr, "INSERT\n");
break;
Index: lib/db/sqlp/yac.y
===================================================================
RCS file: /home/grass/grassrepository/grass6/lib/db/sqlp/yac.y,v
retrieving revision 1.24
diff -u -r1.24 yac.y
--- lib/db/sqlp/yac.y 10 Oct 2006 08:19:34 -0000 1.24
+++ lib/db/sqlp/yac.y 4 Dec 2006 16:14:11 -0000
@@ -104,6 +104,8 @@
y_alter:
ALTER TABLE y_table ADD COLUMN y_columndef { sqpCommand(SQLP_ADD_COLUMN); }
| ALTER TABLE y_table ADD y_columndef { sqpCommand(SQLP_ADD_COLUMN); }
+ | ALTER TABLE y_table DROP COLUMN y_column { sqpCommand(SQLP_DROP_COLUMN); }
+
;
y_create:
-------------- next part --------------
? db/drivers/dbf/OBJ.i686-pc-linux-gnu
Index: db/drivers/dbf/dbfexe.c
===================================================================
RCS file: /home/grass/grassrepository/grass6/db/drivers/dbf/dbfexe.c,v
retrieving revision 1.38
diff -u -r1.38 dbfexe.c
--- db/drivers/dbf/dbfexe.c 10 Oct 2006 08:20:25 -0000 1.38
+++ db/drivers/dbf/dbfexe.c 4 Dec 2006 16:25:39 -0000
@@ -70,7 +70,7 @@
}
G_free ( tmpsql) ;
- G_debug (3, "SQL statement parsed successfully" );
+ G_debug (3, "SQL statement parsed successfully: %s", sql );
/* sqpPrintStmt(st); */ /* debug output only */
@@ -91,7 +91,8 @@
}
if ((st->command == SQLP_DROP) || (st->command == SQLP_DELETE) ||
- (st->command == SQLP_INSERT) || (st->command == SQLP_UPDATE) || (st->command == SQLP_ADD_COLUMN)
+ (st->command == SQLP_INSERT) || (st->command == SQLP_UPDATE) ||
+ (st->command == SQLP_ADD_COLUMN) || (st->command == SQLP_DROP_COLUMN)
) {
if ( db.tables[tab].write == FALSE ) {
append_error( "Cannot modify table, don't have write permission for DBF file.\n");
@@ -102,7 +103,7 @@
/* find columns */
ncols = st->nCol;
if (st->command == SQLP_INSERT || st->command == SQLP_SELECT
- || st->command == SQLP_UPDATE) {
+ || st->command == SQLP_UPDATE || st->command == SQLP_DROP_COLUMN) {
if (ncols > 0) { /* colums were specified */
cols = (int *) G_malloc (ncols * sizeof(int));
for (i = 0; i < ncols; i++) {
@@ -140,6 +141,7 @@
}
/* do command */
+ G_debug(3,"Doing SQL command <%d> on DBF table... (see include/sqlp.h)", st->command);
switch (st->command) {
case (SQLP_ADD_COLUMN):
load_table(tab);
@@ -159,6 +161,15 @@
dbval->d = 0.0;
dbval->c = NULL;
dbval->is_null = 1;
+ }
+ db.tables[tab].updated = TRUE;
+ break;
+
+ case (SQLP_DROP_COLUMN):
+ ret = load_table_selective(tab, st->Col[0].s);
+ if ( ret == DB_FAILED ) {
+ append_error("Cannot delete column.\n");
+ return DB_FAILED;
}
db.tables[tab].updated = TRUE;
break;
Index: db/drivers/dbf/proto.h
===================================================================
RCS file: /home/grass/grassrepository/grass6/db/drivers/dbf/proto.h,v
retrieving revision 1.4
diff -u -r1.4 proto.h
--- db/drivers/dbf/proto.h 16 Mar 2004 13:06:33 -0000 1.4
+++ db/drivers/dbf/proto.h 4 Dec 2006 16:25:39 -0000
@@ -17,6 +17,7 @@
int free_table ( int );
int find_table ( char * );
int load_table_head( int );
+int load_table_selective ( int, char *);
int load_table( int );
int save_table( int );
int describe_table( int, int *, int, dbTable ** );
Index: db/drivers/dbf/table.c
===================================================================
RCS file: /home/grass/grassrepository/grass6/db/drivers/dbf/table.c,v
retrieving revision 1.18
diff -u -r1.18 table.c
--- db/drivers/dbf/table.c 9 Feb 2006 03:08:48 -0000 1.18
+++ db/drivers/dbf/table.c 4 Dec 2006 16:25:39 -0000
@@ -216,6 +216,78 @@
return DB_OK;
}
+/* loads table except for dropcol */
+int
+load_table_selective ( int t, char *dropcol)
+{
+ int i, j, ncols, nrows, dbfcol;
+ DBFHandle dbf;
+ char *buf;
+ ROW *rows;
+ VALUE *val;
+
+ G_debug ( 3, "load_table_selective(): tab = %d dropcol = %s", t, dropcol);
+
+ if ( db.tables[t].loaded == TRUE ) /*already loaded */
+ return DB_OK;
+
+ dbf = DBFOpen( db.tables[t].file, "r" );
+ if( dbf == NULL ) {
+ append_error("Cannot open dbf file.\n");
+ return DB_FAILED;
+ }
+
+ ncols = db.tables[t].ncols;
+ nrows = DBFGetRecordCount( dbf );
+ rows = db.tables[t].rows;
+ rows = (ROW *) G_malloc ( nrows * sizeof(ROW) );
+ db.tables[t].arows = nrows;
+
+ G_debug ( 2, " ncols = %d nrows = %d", ncols, nrows);
+
+ for( i = 0; i < nrows; i++ )
+ {
+ rows[i].alive = TRUE;
+ rows[i].values = (VALUE *) G_calloc ( ncols, sizeof (VALUE) );
+
+ for( j = 0; j < ncols; j++ )
+ {
+ /* jump over column to drop */
+ if ( G_strcasecmp( db.tables[t].cols[j].name, dropcol ) == 0 )
+ continue;
+
+ val = &(rows[i].values[j]);
+
+ dbfcol = j;
+
+ val->is_null = DBFIsAttributeNULL ( dbf, i, dbfcol );
+ if ( !(val->is_null) ) {
+ switch ( db.tables[t].cols[j].type )
+ {
+ case DBF_INT:
+ val->i = DBFReadIntegerAttribute( dbf, i, dbfcol );
+ break;
+ case DBF_CHAR:
+ buf = (char *) DBFReadStringAttribute( dbf, i, dbfcol );
+ save_string ( val, buf);
+ break;
+ case DBF_DOUBLE:
+ val->d = DBFReadDoubleAttribute( dbf, i, dbfcol );
+ break;
+ }
+ }
+ }
+ }
+
+ DBFClose ( dbf );
+
+ db.tables[t].rows = rows;
+ db.tables[t].nrows = nrows -1; /* one less */
+ db.tables[t].loaded = TRUE;
+
+ return DB_OK;
+}
+
int
save_table ( int t)
{
More information about the grass-dev
mailing list