copy_tab.c

Go to the documentation of this file.
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     /* Make a copy of input values and sort it */
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     /* Open input driver and database */
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     /* Open output driver and database */
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     /* Because in SQLite3 an opened cursor is no more valid
00139        if 'schema' is modified (create table), we have to open
00140        cursor twice */
00141 
00142     /* test if the table exists */
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     /* Create new table */
00168     /* Open cursor for data structure */
00169     if (select) {
00170         db_set_string(&sql, select);
00171 
00172         /* TODO!: cannot use this because it will not work if a query 
00173          *         ends with 'group by' for example */
00174         /*
00175            tmp = strdup ( select );
00176            G_tolcase ( tmp );
00177 
00178            if ( !strstr( tmp,"where") )
00179            {
00180            db_append_string ( &sql, " where 0 = 1");
00181            }
00182            else
00183            {
00184            db_append_string ( &sql, " and 0 = 1");
00185            }
00186 
00187            free (tmp);
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"); /* to get no data */
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     /* Open cursor with data */
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     /* Copy all rows */
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 }