GRASS Programmer's Manual
6.4.1(2011)
|
00001 00015 #include <stdlib.h> 00016 #include <string.h> 00017 #include <grass/dbmi.h> 00018 #include <grass/glocale.h> 00019 #include "macros.h" 00020 00021 static int cmp(const void *pa, const void *pb) 00022 { 00023 int *p1 = (int *)pa; 00024 int *p2 = (int *)pb; 00025 00026 if (*p1 < *p2) 00027 return -1; 00028 if (*p1 > *p2) 00029 return 1; 00030 return 0; 00031 } 00032 00058 int db__copy_table(const char *from_drvname, const char *from_dbname, 00059 const char *from_tblname, const char *to_drvname, 00060 const char *to_dbname, const char *to_tblname, 00061 const char *where, const char *select, const char *selcol, 00062 int *ivals, int nvals) 00063 { 00064 int col, ncols, sqltype, ctype, more, selcol_found; 00065 char buf[1000]; 00066 int *ivalues; 00067 dbHandle from_handle, to_handle; 00068 dbString tblname, sql; 00069 dbString value_string; 00070 dbString *tblnames; 00071 dbTable *table, *out_table; 00072 dbCursor cursor; 00073 dbColumn *column; 00074 dbValue *value; 00075 const char *colname; 00076 dbDriver *from_driver, *to_driver; 00077 int count, i; 00078 00079 G_debug(3, "db_copy_table():\n from driver = %s, db = %s, table = %s\n" 00080 " to driver = %s, db = %s, table = %s, where = %s, select = %s", 00081 from_drvname, from_dbname, from_tblname, to_drvname, to_dbname, 00082 to_tblname, where, select); 00083 00084 db_init_handle(&from_handle); 00085 db_init_handle(&to_handle); 00086 db_init_string(&tblname); 00087 db_init_string(&sql); 00088 db_init_string(&value_string); 00089 00090 /* Make a copy of input values and sort it */ 00091 if (ivals) { 00092 ivalues = (int *)G_malloc(nvals * sizeof(int)); 00093 memcpy(ivalues, ivals, nvals * sizeof(int)); 00094 qsort((void *)ivalues, nvals, sizeof(int), cmp); 00095 } 00096 00097 /* Open input driver and database */ 00098 from_driver = db_start_driver(from_drvname); 00099 if (from_driver == NULL) { 00100 G_warning(_("Unable to start driver <%s>"), from_drvname); 00101 return DB_FAILED; 00102 } 00103 db_set_handle(&from_handle, from_dbname, NULL); 00104 if (db_open_database(from_driver, &from_handle) != DB_OK) { 00105 G_warning(_("Unable to open database <%s> by driver <%s>"), 00106 from_drvname, from_dbname); 00107 db_close_database_shutdown_driver(from_driver); 00108 return DB_FAILED; 00109 } 00110 00111 /* Open output driver and database */ 00112 if (strcmp(from_drvname, to_drvname) == 0 00113 && strcmp(from_dbname, to_dbname) == 0) { 00114 G_debug(3, "Use the same driver"); 00115 to_driver = from_driver; 00116 } 00117 else { 00118 to_driver = db_start_driver(to_drvname); 00119 if (to_driver == NULL) { 00120 G_warning(_("Unable to start driver <%s>"), to_drvname); 00121 db_close_database_shutdown_driver(from_driver); 00122 return DB_FAILED; 00123 } 00124 db_set_handle(&to_handle, to_dbname, NULL); 00125 if (db_open_database(to_driver, &to_handle) != DB_OK) { 00126 G_warning(_("Unable to open database <%s> by driver <%s>"), 00127 to_drvname, to_dbname); 00128 db_close_database_shutdown_driver(to_driver); 00129 if (from_driver != to_driver) { 00130 db_close_database_shutdown_driver(from_driver); 00131 } 00132 return DB_FAILED; 00133 } 00134 } 00135 00136 db_begin_transaction(to_driver); 00137 00138 /* Because in SQLite3 an opened cursor is no more valid 00139 if 'schema' is modified (create table), we have to open 00140 cursor twice */ 00141 00142 /* test if the table exists */ 00143 if (db_list_tables(to_driver, &tblnames, &count, 0) != DB_OK) { 00144 G_warning(_("Unable to get list tables in database <%s>"), 00145 to_dbname); 00146 db_close_database_shutdown_driver(to_driver); 00147 if (from_driver != to_driver) 00148 db_close_database_shutdown_driver(from_driver); 00149 00150 return DB_FAILED; 00151 } 00152 00153 for (i = 0; i < count; i++) { 00154 const char *tblname = db_get_string(&tblnames[i]); 00155 00156 if (strcmp(to_tblname, tblname) == 0) { 00157 G_warning(_("Table <%s> already exists in database <%s>"), 00158 to_tblname, to_dbname); 00159 db_close_database_shutdown_driver(to_driver); 00160 if (from_driver != to_driver) 00161 db_close_database_shutdown_driver(from_driver); 00162 00163 return DB_FAILED; 00164 } 00165 } 00166 00167 /* Create new table */ 00168 /* Open cursor for data structure */ 00169 if (select) { 00170 db_set_string(&sql, select); 00171 00172 /* TODO!: cannot use this because it will not work if a query 00173 * ends with 'group by' for example */ 00174 /* 00175 tmp = strdup ( select ); 00176 G_tolcase ( tmp ); 00177 00178 if ( !strstr( tmp,"where") ) 00179 { 00180 db_append_string ( &sql, " where 0 = 1"); 00181 } 00182 else 00183 { 00184 db_append_string ( &sql, " and 0 = 1"); 00185 } 00186 00187 free (tmp); 00188 */ 00189 } 00190 else { 00191 db_set_string(&sql, "select * from "); 00192 db_append_string(&sql, from_tblname); 00193 db_append_string(&sql, " where 0 = 1"); /* to get no data */ 00194 } 00195 00196 G_debug(3, db_get_string(&sql)); 00197 if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) != 00198 DB_OK) { 00199 G_warning(_("Unable to open select cursor: '%s'"), 00200 db_get_string(&sql)); 00201 db_close_database_shutdown_driver(to_driver); 00202 if (from_driver != to_driver) { 00203 db_close_database_shutdown_driver(from_driver); 00204 } 00205 return DB_FAILED; 00206 } 00207 G_debug(3, "Select cursor opened"); 00208 00209 table = db_get_cursor_table(&cursor); 00210 ncols = db_get_table_number_of_columns(table); 00211 G_debug(3, "ncols = %d", ncols); 00212 00213 out_table = db_alloc_table(ncols); 00214 db_set_table_name(out_table, to_tblname); 00215 00216 selcol_found = 0; 00217 for (col = 0; col < ncols; col++) { 00218 dbColumn *out_column; 00219 00220 column = db_get_table_column(table, col); 00221 colname = db_get_column_name(column); 00222 sqltype = db_get_column_sqltype(column); 00223 ctype = db_sqltype_to_Ctype(sqltype); 00224 00225 G_debug(3, "%s (%s)", colname, db_sqltype_name(sqltype)); 00226 00227 out_column = db_get_table_column(out_table, col); 00228 00229 if (selcol && G_strcasecmp(colname, selcol) == 0) { 00230 if (ctype != DB_C_TYPE_INT) 00231 G_fatal_error(_("Column <%s> is not integer"), 00232 colname); 00233 selcol_found = 1; 00234 } 00235 00236 db_set_column_name(out_column, db_get_column_name(column)); 00237 db_set_column_description(out_column, 00238 db_get_column_description(column)); 00239 db_set_column_sqltype(out_column, db_get_column_sqltype(column)); 00240 db_set_column_length(out_column, db_get_column_length(column)); 00241 db_set_column_precision(out_column, db_get_column_precision(column)); 00242 db_set_column_scale(out_column, db_get_column_scale(column)); 00243 } 00244 00245 db_close_cursor(&cursor); 00246 00247 if (selcol && !selcol_found) 00248 G_fatal_error(_("Column <%s> not found"), selcol); 00249 00250 if (db_create_table(to_driver, out_table) != DB_OK) { 00251 G_warning(_("Unable to create table <%s>"), 00252 to_tblname); 00253 db_close_database_shutdown_driver(to_driver); 00254 if (from_driver != to_driver) { 00255 db_close_database_shutdown_driver(from_driver); 00256 } 00257 return DB_FAILED; 00258 } 00259 00260 /* Open cursor with data */ 00261 if (select) { 00262 db_set_string(&sql, select); 00263 } 00264 else { 00265 db_set_string(&sql, "select * from "); 00266 db_append_string(&sql, from_tblname); 00267 if (where) { 00268 db_append_string(&sql, " where "); 00269 db_append_string(&sql, where); 00270 } 00271 } 00272 00273 G_debug(3, db_get_string(&sql)); 00274 if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) != 00275 DB_OK) { 00276 G_warning(_("Unable to open select cursor: '%s'"), 00277 db_get_string(&sql)); 00278 db_close_database_shutdown_driver(to_driver); 00279 if (from_driver != to_driver) { 00280 db_close_database_shutdown_driver(from_driver); 00281 } 00282 return DB_FAILED; 00283 } 00284 G_debug(3, "Select cursor opened"); 00285 00286 table = db_get_cursor_table(&cursor); 00287 ncols = db_get_table_number_of_columns(table); 00288 G_debug(3, "ncols = %d", ncols); 00289 00290 /* Copy all rows */ 00291 while (1) { 00292 int select; 00293 00294 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK) { 00295 G_warning(_("Unable to fetch data from table <%s>"), 00296 from_tblname); 00297 db_close_cursor(&cursor); 00298 db_close_database_shutdown_driver(to_driver); 00299 if (from_driver != to_driver) { 00300 db_close_database_shutdown_driver(from_driver); 00301 } 00302 return DB_FAILED; 00303 } 00304 if (!more) 00305 break; 00306 00307 sprintf(buf, "insert into %s values ( ", to_tblname); 00308 db_set_string(&sql, buf); 00309 select = 1; 00310 for (col = 0; col < ncols; col++) { 00311 column = db_get_table_column(table, col); 00312 colname = db_get_column_name(column); 00313 sqltype = db_get_column_sqltype(column); 00314 ctype = db_sqltype_to_Ctype(sqltype); 00315 value = db_get_column_value(column); 00316 00317 if (selcol && G_strcasecmp(colname, selcol) == 0) { 00318 if (db_test_value_isnull(value)) 00319 continue; 00320 if (!bsearch(&(value->i), ivalues, nvals, sizeof(int), cmp)) { 00321 select = 0; 00322 break; 00323 } 00324 } 00325 if (col > 0) 00326 db_append_string(&sql, ", "); 00327 db_convert_value_to_string(value, sqltype, &value_string); 00328 switch (ctype) { 00329 case DB_C_TYPE_STRING: 00330 case DB_C_TYPE_DATETIME: 00331 if (db_test_value_isnull(value)) { 00332 db_append_string(&sql, "null"); 00333 } 00334 else { 00335 db_double_quote_string(&value_string); 00336 db_append_string(&sql, "'"); 00337 db_append_string(&sql, db_get_string(&value_string)); 00338 db_append_string(&sql, "'"); 00339 } 00340 break; 00341 case DB_C_TYPE_INT: 00342 case DB_C_TYPE_DOUBLE: 00343 if (db_test_value_isnull(value)) { 00344 db_append_string(&sql, "null"); 00345 } 00346 else { 00347 db_append_string(&sql, db_get_string(&value_string)); 00348 } 00349 break; 00350 default: 00351 G_warning(_("Unknown column type (column <%s>)"), 00352 colname); 00353 db_close_cursor(&cursor); 00354 db_close_database_shutdown_driver(to_driver); 00355 if (from_driver != to_driver) { 00356 db_close_database_shutdown_driver(from_driver); 00357 } 00358 return DB_FAILED; 00359 } 00360 } 00361 if (!select) 00362 continue; 00363 db_append_string(&sql, ")"); 00364 G_debug(3, db_get_string(&sql)); 00365 if (db_execute_immediate(to_driver, &sql) != DB_OK) { 00366 G_warning("Unable to insert new record: '%s'", 00367 db_get_string(&sql)); 00368 db_close_cursor(&cursor); 00369 db_close_database_shutdown_driver(to_driver); 00370 if (from_driver != to_driver) { 00371 db_close_database_shutdown_driver(from_driver); 00372 } 00373 return DB_FAILED; 00374 } 00375 } 00376 if (selcol) 00377 G_free(ivalues); 00378 G_debug(3, "Table copy OK"); 00379 00380 db_close_cursor(&cursor); 00381 db_commit_transaction(to_driver); 00382 db_close_database_shutdown_driver(to_driver); 00383 if (from_driver != to_driver) { 00384 db_close_database_shutdown_driver(from_driver); 00385 } 00386 00387 return DB_OK; 00388 } 00389 00403 int db_copy_table(const char *from_drvname, const char *from_dbname, 00404 const char *from_tblname, const char *to_drvname, 00405 const char *to_dbname, const char *to_tblname) 00406 { 00407 return db__copy_table(from_drvname, from_dbname, from_tblname, 00408 to_drvname, to_dbname, to_tblname, 00409 NULL, NULL, NULL, NULL, 0); 00410 } 00411 00426 int db_copy_table_where(const char *from_drvname, const char *from_dbname, 00427 const char *from_tblname, const char *to_drvname, 00428 const char *to_dbname, const char *to_tblname, 00429 const char *where) 00430 { 00431 return db__copy_table(from_drvname, from_dbname, from_tblname, 00432 to_drvname, to_dbname, to_tblname, 00433 where, NULL, NULL, NULL, 0); 00434 } 00435 00450 int db_copy_table_select(const char *from_drvname, const char *from_dbname, 00451 const char *from_tblname, const char *to_drvname, 00452 const char *to_dbname, const char *to_tblname, 00453 const char *select) 00454 { 00455 return db__copy_table(from_drvname, from_dbname, from_tblname, 00456 to_drvname, to_dbname, to_tblname, 00457 NULL, select, NULL, NULL, 0); 00458 } 00459 00476 int db_copy_table_by_ints(const char *from_drvname, const char *from_dbname, 00477 const char *from_tblname, const char *to_drvname, 00478 const char *to_dbname, const char *to_tblname, 00479 const char *selcol, int *ivals, int nvals) 00480 { 00481 return db__copy_table(from_drvname, from_dbname, from_tblname, 00482 to_drvname, to_dbname, to_tblname, 00483 NULL, NULL, selcol, ivals, nvals); 00484 }