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
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
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
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
00139
00140
00141
00142
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
00168
00169 if (select) {
00170 db_set_string(&sql, select);
00171
00172
00173
00174
00175
00176
00177
00178
00179
00180
00181
00182
00183
00184
00185
00186
00187
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");
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
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
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 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 }