00001 #include <stdlib.h>
00002 #include <string.h>
00003 #include <grass/dbmi.h>
00004 #include "macros.h"
00005
00006 static int cmp ( const void *pa, const void *pb)
00007 {
00008 int *p1 = (int *) pa;
00009 int *p2 = (int *) pb;
00010
00011 if( *p1 < *p2 ) return -1;
00012 if( *p1 > *p2 ) return 1;
00013 return 0;
00014 }
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029 int
00030 db__copy_table ( char *from_drvname, char *from_dbname, char *from_tblname,
00031 char *to_drvname, char *to_dbname, char *to_tblname,
00032 char *where, char *select,
00033 char *selcol, int *ivals, int nvals)
00034 {
00035 int col, ncols, sqltype, ctype, more, selcol_found;
00036 char buf[1000];
00037 int *ivalues;
00038 dbHandle from_handle, to_handle;
00039 dbString tblname, sql;
00040 dbString value_string;
00041 dbString *tblnames;
00042 dbTable *table, *out_table;
00043 dbCursor cursor;
00044 dbColumn *column;
00045 dbValue *value;
00046 char *colname;
00047 dbDriver *from_driver, *to_driver;
00048 int count, i;
00049
00050 G_debug ( 3, "db_copy_table():\n from driver = %s, db = %s, table = %s\n"
00051 " to driver = %s, db = %s, table = %s, where = %s, select = %s",
00052 from_drvname, from_dbname, from_tblname, to_drvname, to_dbname, to_tblname, where, select);
00053
00054 db_init_handle (&from_handle);
00055 db_init_handle (&to_handle);
00056 db_init_string (&tblname);
00057 db_init_string (&sql);
00058 db_init_string (&value_string);
00059
00060
00061 if ( ivals ) {
00062 ivalues = (int*) G_malloc ( nvals * sizeof(int) );
00063 memcpy ( ivalues, ivals, nvals * sizeof(int) );
00064 qsort( (void *)ivalues, nvals, sizeof(int), cmp);
00065 }
00066
00067
00068 from_driver = db_start_driver(from_drvname);
00069 if ( from_driver == NULL) {
00070 G_warning ( "Cannot open driver '%s'", from_drvname);
00071 return DB_FAILED;
00072 }
00073 db_set_handle (&from_handle, from_dbname, NULL);
00074 if (db_open_database(from_driver, &from_handle) != DB_OK) {
00075 G_warning ( "Cannot open database '%s'", from_dbname);
00076 db_close_database_shutdown_driver(from_driver);
00077 return DB_FAILED;
00078 }
00079
00080
00081 if ( strcmp(from_drvname,to_drvname)==0
00082 && strcmp(from_dbname,to_dbname)==0 )
00083 {
00084 G_debug ( 3, "Use the same driver" );
00085 to_driver = from_driver;
00086 }
00087 else
00088 {
00089 to_driver = db_start_driver(to_drvname);
00090 if ( to_driver == NULL) {
00091 G_warning ( "Cannot open driver '%s'", to_drvname);
00092 db_close_database_shutdown_driver(from_driver);
00093 return DB_FAILED;
00094 }
00095 db_set_handle (&to_handle, to_dbname, NULL);
00096 if (db_open_database(to_driver, &to_handle) != DB_OK) {
00097 G_warning ( "Cannot open database '%s'", to_dbname);
00098 db_close_database_shutdown_driver(to_driver);
00099 if ( from_driver != to_driver )
00100 {
00101 db_close_database_shutdown_driver(from_driver);
00102 }
00103 return DB_FAILED;
00104 }
00105 }
00106
00107 db_begin_transaction ( to_driver );
00108
00109
00110
00111
00112
00113
00114 if (db_list_tables(to_driver, &tblnames, &count, 0) != DB_OK) {
00115 G_warning("Cannot list tables in database '%s'", to_dbname);
00116 db_close_database_shutdown_driver(to_driver);
00117 if (from_driver != to_driver)
00118 db_close_database_shutdown_driver(from_driver);
00119
00120 return DB_FAILED;
00121 }
00122
00123 for (i = 0; i < count; i++) {
00124 char *tblname = db_get_string(&tblnames[i]);
00125
00126 if (strcmp(to_tblname, tblname) == 0) {
00127 G_warning("Table '%s' already exists", 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
00137
00138 if ( select ) {
00139 db_set_string ( &sql, select );
00140
00141
00142
00143
00144
00145
00146
00147
00148
00149
00150
00151
00152
00153
00154
00155
00156
00157
00158 } else {
00159 db_set_string ( &sql, "select * from ");
00160 db_append_string ( &sql, from_tblname);
00161 db_append_string ( &sql, " where 0 = 1");
00162 }
00163
00164 G_debug ( 3, db_get_string(&sql) );
00165 if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) != DB_OK) {
00166 G_warning ( "Cannot open select cursor: '%s'", db_get_string(&sql) );
00167 db_close_database_shutdown_driver(to_driver);
00168 if ( from_driver != to_driver )
00169 {
00170 db_close_database_shutdown_driver(from_driver);
00171 }
00172 return DB_FAILED;
00173 }
00174 G_debug ( 3, "Select cursor opened" );
00175
00176 table = db_get_cursor_table (&cursor);
00177 ncols = db_get_table_number_of_columns(table);
00178 G_debug ( 3, "ncols = %d", ncols );
00179
00180 out_table = db_alloc_table ( ncols );
00181 db_set_table_name ( out_table, to_tblname );
00182
00183 selcol_found = 0;
00184 for ( col = 0; col < ncols; col++ ) {
00185 dbColumn *out_column;
00186
00187 column = db_get_table_column (table, col);
00188 colname = db_get_column_name (column);
00189 sqltype = db_get_column_sqltype (column);
00190 ctype = db_sqltype_to_Ctype ( sqltype );
00191
00192 G_debug ( 3, "%s (%s)", colname, db_sqltype_name(sqltype) );
00193
00194 out_column = db_get_table_column (out_table, col);
00195
00196 if ( selcol && G_strcasecmp ( colname, selcol) == 0 ) {
00197 if ( ctype != DB_C_TYPE_INT )
00198 G_fatal_error ("Column '%s' is not integer", colname);
00199 selcol_found = 1;
00200 }
00201
00202 db_set_column_name ( out_column, db_get_column_name ( column ) );
00203 db_set_column_description ( out_column, db_get_column_description ( column ) );
00204 db_set_column_sqltype ( out_column, db_get_column_sqltype ( column ) );
00205 db_set_column_length ( out_column, db_get_column_length ( column ) );
00206 db_set_column_precision ( out_column, db_get_column_precision ( column ) );
00207 db_set_column_scale ( out_column, db_get_column_scale ( column ) );
00208 }
00209
00210 db_close_cursor(&cursor);
00211
00212 if ( selcol && !selcol_found)
00213 G_fatal_error ("Column '%s' not found", selcol);
00214
00215 if ( db_create_table ( to_driver, out_table ) != DB_OK ) {
00216 G_warning ( "Cannot create new table" );
00217 db_close_database_shutdown_driver(to_driver);
00218 if ( from_driver != to_driver )
00219 {
00220 db_close_database_shutdown_driver(from_driver);
00221 }
00222 return DB_FAILED;
00223 }
00224
00225
00226 if ( select ) {
00227 db_set_string ( &sql, select );
00228 } else {
00229 db_set_string ( &sql, "select * from ");
00230 db_append_string ( &sql, from_tblname);
00231 if ( where ) {
00232 db_append_string ( &sql, " where ");
00233 db_append_string ( &sql, where);
00234 }
00235 }
00236
00237 G_debug ( 3, db_get_string(&sql) );
00238 if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) != DB_OK) {
00239 G_warning ( "Cannot open select cursor: '%s'", db_get_string(&sql) );
00240 db_close_database_shutdown_driver(to_driver);
00241 if ( from_driver != to_driver )
00242 {
00243 db_close_database_shutdown_driver(from_driver);
00244 }
00245 return DB_FAILED;
00246 }
00247 G_debug ( 3, "Select cursor opened" );
00248
00249 table = db_get_cursor_table (&cursor);
00250 ncols = db_get_table_number_of_columns(table);
00251 G_debug ( 3, "ncols = %d", ncols );
00252
00253
00254 while ( 1 ) {
00255 int select;
00256
00257 if ( db_fetch (&cursor, DB_NEXT, &more ) != DB_OK ) {
00258 G_warning ( "Cannot fetch row" );
00259 db_close_cursor(&cursor);
00260 db_close_database_shutdown_driver(to_driver);
00261 if ( from_driver != to_driver )
00262 {
00263 db_close_database_shutdown_driver(from_driver);
00264 }
00265 return DB_FAILED;
00266 }
00267 if (!more) break;
00268
00269 sprintf ( buf, "insert into %s values ( ", to_tblname );
00270 db_set_string ( &sql, buf);
00271 select = 1;
00272 for ( col = 0; col < ncols; col++ ) {
00273 column = db_get_table_column (table, col);
00274 colname = db_get_column_name (column);
00275 sqltype = db_get_column_sqltype (column);
00276 ctype = db_sqltype_to_Ctype(sqltype);
00277 value = db_get_column_value(column);
00278
00279 if ( selcol && G_strcasecmp ( colname, selcol) == 0 ) {
00280 if ( db_test_value_isnull(value) ) continue;
00281 if ( !bsearch(&(value->i), ivalues, nvals, sizeof(int), cmp) ) {
00282 select = 0;
00283 break;
00284 }
00285 }
00286 if ( col > 0 ) db_append_string ( &sql, ", " );
00287 db_convert_value_to_string( value, sqltype, &value_string);
00288 switch ( ctype ) {
00289 case DB_C_TYPE_STRING:
00290 case DB_C_TYPE_DATETIME:
00291 if ( db_test_value_isnull(value) ) {
00292 db_append_string ( &sql, "null" );
00293 } else {
00294 db_double_quote_string ( &value_string );
00295 sprintf (buf, "'%s'", db_get_string(&value_string) );
00296 db_append_string ( &sql, buf);
00297 }
00298 break;
00299 case DB_C_TYPE_INT:
00300 case DB_C_TYPE_DOUBLE:
00301 if ( db_test_value_isnull(value) ) {
00302 db_append_string ( &sql, "null" );
00303 } else {
00304 db_append_string ( &sql, db_get_string(&value_string) );
00305 }
00306 break;
00307 default:
00308 G_warning ( "Unknown column type (%s)", colname);
00309 db_close_cursor(&cursor);
00310 db_close_database_shutdown_driver(to_driver);
00311 if ( from_driver != to_driver )
00312 {
00313 db_close_database_shutdown_driver(from_driver);
00314 }
00315 return DB_FAILED;
00316 }
00317 }
00318 if ( !select ) continue;
00319 db_append_string ( &sql, ")" );
00320 G_debug ( 3, db_get_string(&sql) );
00321 if (db_execute_immediate (to_driver, &sql) != DB_OK ) {
00322 G_warning ( "Cannot insert new record: '%s'", db_get_string(&sql) );
00323 db_close_cursor(&cursor);
00324 db_close_database_shutdown_driver(to_driver);
00325 if ( from_driver != to_driver )
00326 {
00327 db_close_database_shutdown_driver(from_driver);
00328 }
00329 return DB_FAILED;
00330 }
00331 }
00332 if ( selcol ) free (ivalues);
00333 G_debug ( 3, "Table copy OK" );
00334
00335 db_close_cursor(&cursor);
00336 db_commit_transaction ( to_driver );
00337 db_close_database_shutdown_driver(to_driver);
00338 if ( from_driver != to_driver )
00339 {
00340 db_close_database_shutdown_driver(from_driver);
00341 }
00342
00343 return DB_OK;
00344 }
00345
00353 int
00354 db_copy_table ( char *from_drvname, char *from_dbname, char *from_tblname,
00355 char *to_drvname, char *to_dbname, char *to_tblname )
00356 {
00357 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00358 to_drvname, to_dbname, to_tblname,
00359 NULL, NULL,
00360 NULL, NULL, 0 );
00361 }
00362
00370 int
00371 db_copy_table_where ( char *from_drvname, char *from_dbname, char *from_tblname,
00372 char *to_drvname, char *to_dbname, char *to_tblname, char *where )
00373 {
00374 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00375 to_drvname, to_dbname, to_tblname,
00376 where, NULL,
00377 NULL, NULL, 0 );
00378 }
00379
00387 int
00388 db_copy_table_select ( char *from_drvname, char *from_dbname, char *from_tblname,
00389 char *to_drvname, char *to_dbname, char *to_tblname, char *select )
00390 {
00391 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00392 to_drvname, to_dbname, to_tblname,
00393 NULL, select,
00394 NULL, NULL, 0 );
00395 }
00396
00408 int
00409 db_copy_table_by_ints ( char *from_drvname, char *from_dbname, char *from_tblname,
00410 char *to_drvname, char *to_dbname, char *to_tblname,
00411 char *selcol, int *ivals, int nvals )
00412 {
00413 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00414 to_drvname, to_dbname, to_tblname,
00415 NULL, NULL,
00416 selcol, ivals, nvals );
00417 }