copy_tab.c

Go to the documentation of this file.
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 /* 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     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     /* Make a copy of input values and sort it */
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     /* Open input driver and database */
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     /* Open output driver and database */
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     /* Because in SQLite3 an opened cursor is no more valid
00110        if 'schema' is modified (create table), we have to open
00111        cursor twice */
00112 
00113     /* test if the table exists */
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     /* Create new table */
00137     /* Open cursor for data structure */
00138     if ( select ) {
00139         db_set_string ( &sql, select );
00140 
00141         /* TODO!: cannot use this because it will not work if a query 
00142          *         ends with 'group by' for example */
00143         /*
00144         tmp = strdup ( select );
00145         G_tolcase ( tmp );
00146 
00147         if ( !strstr( tmp,"where") )
00148         {
00149             db_append_string ( &sql, " where 0 = 1");
00150         }
00151         else
00152         {
00153             db_append_string ( &sql, " and 0 = 1");
00154         }
00155 
00156         free (tmp);
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"); /* to get no data */
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     /* Open cursor with data */
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     /* Copy all rows */
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 }

Generated on Sun Apr 6 17:31:38 2008 for GRASS by  doxygen 1.5.5