00001 #include <stdlib.h>
00002 #include <string.h>
00003 #include "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 dbTable *table, *out_table;
00042 dbCursor cursor;
00043 dbColumn *column;
00044 dbValue *value;
00045 char *colname;
00046 dbDriver *from_driver, *to_driver;
00047
00048 G_debug ( 3, "db_copy_table():\n from driver = %s, db = %s, table = %s\n"
00049 " to driver = %s, db = %s, table = %s, where = %s, select = %s",
00050 from_drvname, from_dbname, from_tblname, to_drvname, to_dbname, to_tblname, where, select);
00051
00052 db_init_handle (&from_handle);
00053 db_init_handle (&to_handle);
00054 db_init_string (&tblname);
00055 db_init_string (&sql);
00056 db_init_string (&value_string);
00057
00058
00059 if ( ivals ) {
00060 ivalues = (int*) G_malloc ( nvals * sizeof(int) );
00061 memcpy ( ivalues, ivals, nvals * sizeof(int) );
00062 qsort( (void *)ivalues, nvals, sizeof(int), cmp);
00063 }
00064
00065
00066 from_driver = db_start_driver(from_drvname);
00067 if ( from_driver == NULL) {
00068 G_warning ( "Cannot open driver '%s'", from_drvname);
00069 return DB_FAILED;
00070 }
00071 db_set_handle (&from_handle, from_dbname, NULL);
00072 if (db_open_database(from_driver, &from_handle) != DB_OK) {
00073 G_warning ( "Cannot open database '%s'", from_dbname);
00074 db_close_database_shutdown_driver(from_driver);
00075 return DB_FAILED;
00076 }
00077
00078
00079 to_driver = db_start_driver(to_drvname);
00080 if ( to_driver == NULL) {
00081 G_warning ( "Cannot open driver '%s'", to_drvname);
00082 db_close_database_shutdown_driver(from_driver);
00083 return DB_FAILED;
00084 }
00085 db_set_handle (&to_handle, to_dbname, NULL);
00086 if (db_open_database(to_driver, &to_handle) != DB_OK) {
00087 G_warning ( "Cannot open database '%s'", to_dbname);
00088 db_close_database_shutdown_driver(to_driver);
00089 db_close_database_shutdown_driver(from_driver);
00090 return DB_FAILED;
00091 }
00092
00093 db_begin_transaction ( to_driver );
00094
00095
00096
00097 if ( select ) {
00098 db_set_string ( &sql, select );
00099 } else {
00100 db_set_string ( &sql, "select * from ");
00101 db_append_string ( &sql, from_tblname);
00102 if ( where ) {
00103 db_append_string ( &sql, " where ");
00104 db_append_string ( &sql, where);
00105 }
00106 }
00107
00108 G_debug ( 3, db_get_string(&sql) );
00109 if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) != DB_OK) {
00110 G_warning ( "Cannot open select cursor: '%s'", db_get_string(&sql) );
00111 db_close_database_shutdown_driver(to_driver);
00112 db_close_database_shutdown_driver(from_driver);
00113 return DB_FAILED;
00114 }
00115 G_debug ( 3, "Select cursor opened" );
00116
00117 table = db_get_cursor_table (&cursor);
00118 ncols = db_get_table_number_of_columns(table);
00119 G_debug ( 3, "ncols = %d", ncols );
00120
00121 out_table = db_alloc_table ( ncols );
00122 db_set_table_name ( out_table, to_tblname );
00123
00124 selcol_found = 0;
00125 for ( col = 0; col < ncols; col++ ) {
00126 dbColumn *out_column;
00127
00128 column = db_get_table_column (table, col);
00129 colname = db_get_column_name (column);
00130 sqltype = db_get_column_sqltype (column);
00131 ctype = db_sqltype_to_Ctype ( sqltype );
00132
00133 G_debug ( 3, "%s (%s)", colname, db_sqltype_name(sqltype) );
00134
00135 out_column = db_get_table_column (out_table, col);
00136
00137 if ( selcol && G_strcasecmp ( colname, selcol) == 0 ) {
00138 if ( ctype != DB_C_TYPE_INT )
00139 G_fatal_error ("Column '%s' is not integer", colname);
00140 selcol_found = 1;
00141 }
00142
00143 db_set_column_name ( out_column, db_get_column_name ( column ) );
00144 db_set_column_description ( out_column, db_get_column_description ( column ) );
00145 db_set_column_sqltype ( out_column, db_get_column_sqltype ( column ) );
00146 db_set_column_length ( out_column, db_get_column_length ( column ) );
00147 db_set_column_precision ( out_column, db_get_column_precision ( column ) );
00148 db_set_column_scale ( out_column, db_get_column_scale ( column ) );
00149 }
00150
00151 if ( selcol && !selcol_found)
00152 G_fatal_error ("Column '%s' not found", selcol);
00153
00154 if ( db_create_table ( to_driver, out_table ) != DB_OK ) {
00155 G_warning ( "Cannot create new table" );
00156 db_close_cursor(&cursor);
00157 db_close_database_shutdown_driver(to_driver);
00158 db_close_database_shutdown_driver(from_driver);
00159 return DB_FAILED;
00160 }
00161
00162
00163 while ( 1 ) {
00164 int select;
00165
00166 if ( db_fetch (&cursor, DB_NEXT, &more ) != DB_OK ) {
00167 G_warning ( "Cannot fetch row" );
00168 db_close_cursor(&cursor);
00169 db_close_database_shutdown_driver(to_driver);
00170 db_close_database_shutdown_driver(from_driver);
00171 return DB_FAILED;
00172 }
00173 if (!more) break;
00174
00175 sprintf ( buf, "insert into %s values ( ", to_tblname );
00176 db_set_string ( &sql, buf);
00177 select = 1;
00178 for ( col = 0; col < ncols; col++ ) {
00179 column = db_get_table_column (table, col);
00180 colname = db_get_column_name (column);
00181 sqltype = db_get_column_sqltype (column);
00182 ctype = db_sqltype_to_Ctype(sqltype);
00183 value = db_get_column_value(column);
00184
00185 if ( selcol && G_strcasecmp ( colname, selcol) == 0 ) {
00186 if ( db_test_value_isnull(value) ) continue;
00187 if ( !bsearch(&(value->i), ivalues, nvals, sizeof(int), cmp) ) {
00188 select = 0;
00189 break;
00190 }
00191 }
00192 if ( col > 0 ) db_append_string ( &sql, ", " );
00193 db_convert_value_to_string( value, sqltype, &value_string);
00194 switch ( ctype ) {
00195 case DB_C_TYPE_STRING:
00196 case DB_C_TYPE_DATETIME:
00197 if ( db_test_value_isnull(value) ) {
00198 db_append_string ( &sql, "null" );
00199 } else {
00200 db_double_quote_string ( &value_string );
00201 sprintf (buf, "'%s'", db_get_string(&value_string) );
00202 db_append_string ( &sql, buf);
00203 }
00204 break;
00205 case DB_C_TYPE_INT:
00206 case DB_C_TYPE_DOUBLE:
00207 if ( db_test_value_isnull(value) ) {
00208 db_append_string ( &sql, "null" );
00209 } else {
00210 db_append_string ( &sql, db_get_string(&value_string) );
00211 }
00212 break;
00213 default:
00214 G_warning ( "Unknown column type (%s)", colname);
00215 db_close_cursor(&cursor);
00216 db_close_database_shutdown_driver(to_driver);
00217 db_close_database_shutdown_driver(from_driver);
00218 return DB_FAILED;
00219 }
00220 }
00221 if ( !select ) continue;
00222 db_append_string ( &sql, ")" );
00223 G_debug ( 3, db_get_string(&sql) );
00224 if (db_execute_immediate (to_driver, &sql) != DB_OK ) {
00225 G_warning ( "Cannot insert new record: '%s'", db_get_string(&sql) );
00226 db_close_cursor(&cursor);
00227 db_close_database_shutdown_driver(to_driver);
00228 db_close_database_shutdown_driver(from_driver);
00229 return DB_FAILED;
00230 }
00231 }
00232 if ( selcol ) free (ivalues);
00233 G_debug ( 3, "Table copy OK" );
00234
00235 db_close_cursor(&cursor);
00236 db_commit_transaction ( to_driver );
00237 db_close_database_shutdown_driver(to_driver);
00238 db_close_database_shutdown_driver(from_driver);
00239
00240 return DB_OK;
00241 }
00242
00249 int
00250 db_copy_table ( char *from_drvname, char *from_dbname, char *from_tblname,
00251 char *to_drvname, char *to_dbname, char *to_tblname )
00252 {
00253 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00254 to_drvname, to_dbname, to_tblname,
00255 NULL, NULL,
00256 NULL, NULL, 0 );
00257 }
00258
00265 int
00266 db_copy_table_where ( char *from_drvname, char *from_dbname, char *from_tblname,
00267 char *to_drvname, char *to_dbname, char *to_tblname, char *where )
00268 {
00269 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00270 to_drvname, to_dbname, to_tblname,
00271 where, NULL,
00272 NULL, NULL, 0 );
00273 }
00274
00281 int
00282 db_copy_table_select ( char *from_drvname, char *from_dbname, char *from_tblname,
00283 char *to_drvname, char *to_dbname, char *to_tblname, char *select )
00284 {
00285 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00286 to_drvname, to_dbname, to_tblname,
00287 NULL, select,
00288 NULL, NULL, 0 );
00289 }
00290
00300 int
00301 db_copy_table_by_ints ( char *from_drvname, char *from_dbname, char *from_tblname,
00302 char *to_drvname, char *to_dbname, char *to_tblname,
00303 char *selcol, int *ivals, int nvals )
00304 {
00305 return db__copy_table ( from_drvname, from_dbname, from_tblname,
00306 to_drvname, to_dbname, to_tblname,
00307 NULL, NULL,
00308 selcol, ivals, nvals );
00309 }