copy_tab.c

Go to the documentation of this file.
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 /* Copy table, used by various db_copy_table* 
00017  
00018    Parameters: 
00019        where: WHERE SQL condition (without where key word) or NULL
00020        select: full select statement
00021        selcol: name of column used to select records by values in ivals or NULL
00022        ivals: pointer to array of integer values or NULL
00023        nvals: number of values in ivals
00024 
00025    Use either 'where' or 'select' or 'selcol'+'ivals'+'nvals' but never more than one
00026        
00027 */
00028 /* Warning, driver opened as second must be closed as first, otherwise it hangs, not sure why */
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     /* Make a copy of input values and sort it */
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     /* Open input driver and database */
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     /* Open output driver and database */
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     /* Create new table */
00096     /* TODO test if the tables exist */
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     /* Copy all rows */
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 }

Generated on Mon Jan 1 19:49:04 2007 for GRASS by  doxygen 1.5.1