copy_tab.c

Go to the documentation of this file.

#include <stdlib.h>
#include <string.h>
#include <grass/dbmi.h>
#include <grass/glocale.h>
#include "macros.h"

static int cmp(const void *pa, const void *pb)
{
    int *p1 = (int *)pa;
    int *p2 = (int *)pb;

    if (*p1 < *p2)
        return -1;
    if (*p1 > *p2)
        return 1;
    return 0;
}

int db__copy_table(const char *from_drvname, const char *from_dbname,
                   const char *from_tblname, const char *to_drvname,
                   const char *to_dbname, const char *to_tblname,
                   const char *where, const char *select, const char *selcol,
                   int *ivals, int nvals)
{
    int col, ncols, sqltype, ctype, more, selcol_found;
    char buf[1000];
    int *ivalues;
    dbHandle from_handle, to_handle;
    dbString tblname, sql;
    dbString value_string;
    dbString *tblnames;
    dbTable *table, *out_table;
    dbCursor cursor;
    dbColumn *column;
    dbValue *value;
    const char *colname;
    dbDriver *from_driver, *to_driver;
    int count, i;

    G_debug(3, "db_copy_table():\n  from driver = %s, db = %s, table = %s\n"
            "  to driver = %s, db = %s, table = %s, where = %s, select = %s",
            from_drvname, from_dbname, from_tblname, to_drvname, to_dbname,
            to_tblname, where, select);

    db_init_handle(&from_handle);
    db_init_handle(&to_handle);
    db_init_string(&tblname);
    db_init_string(&sql);
    db_init_string(&value_string);

    /* Make a copy of input values and sort it */
    if (ivals) {
        ivalues = (int *)G_malloc(nvals * sizeof(int));
        memcpy(ivalues, ivals, nvals * sizeof(int));
        qsort((void *)ivalues, nvals, sizeof(int), cmp);
    }

    /* Open input driver and database */
    from_driver = db_start_driver(from_drvname);
    if (from_driver == NULL) {
        G_warning(_("Unable to start driver <%s>"), from_drvname);
        return DB_FAILED;
    }
    db_set_handle(&from_handle, from_dbname, NULL);
    if (db_open_database(from_driver, &from_handle) != DB_OK) {
        G_warning(_("Unable to open database <%s> by driver <%s>"),
                  from_dbname, from_drvname);
        db_close_database_shutdown_driver(from_driver);
        return DB_FAILED;
    }

    /* Open output driver and database */
    if (strcmp(from_drvname, to_drvname) == 0
        && strcmp(from_dbname, to_dbname) == 0) {
        G_debug(3, "Use the same driver");
        to_driver = from_driver;
    }
    else {
        to_driver = db_start_driver(to_drvname);
        if (to_driver == NULL) {
            G_warning(_("Unable to start driver <%s>"), to_drvname);
            db_close_database_shutdown_driver(from_driver);
            return DB_FAILED;
        }
        db_set_handle(&to_handle, to_dbname, NULL);
        if (db_open_database(to_driver, &to_handle) != DB_OK) {
            G_warning(_("Unable to open database <%s> by driver <%s>"),
                      to_dbname, to_drvname);
            db_close_database_shutdown_driver(to_driver);
            if (from_driver != to_driver) {
                db_close_database_shutdown_driver(from_driver);
            }
            return DB_FAILED;
        }
    }

    db_begin_transaction(to_driver);

    /* Because in SQLite3 an opened cursor is no more valid
       if 'schema' is modified (create table), we have to open
       cursor twice */

    /* test if the table exists */
    if (db_list_tables(to_driver, &tblnames, &count, 0) != DB_OK) {
        G_warning(_("Unable to get list tables in database <%s>"),
                  to_dbname);
        db_close_database_shutdown_driver(to_driver);
        if (from_driver != to_driver)
            db_close_database_shutdown_driver(from_driver);

        return DB_FAILED;
    }

    for (i = 0; i < count; i++) {
        const char *tblname = db_get_string(&tblnames[i]);

        if (strcmp(to_tblname, tblname) == 0) {
            G_warning(_("Table <%s> already exists in database <%s>"),
                      to_tblname, to_dbname);
            db_close_database_shutdown_driver(to_driver);
            if (from_driver != to_driver)
                db_close_database_shutdown_driver(from_driver);

            return DB_FAILED;
        }
    }

    /* Create new table */
    /* Open cursor for data structure */
    if (select) {
        db_set_string(&sql, select);

        /* TODO!: cannot use this because it will not work if a query 
         *         ends with 'group by' for example */
        /*
           tmp = strdup ( select );
           G_tolcase ( tmp );

           if ( !strstr( tmp,"where") )
           {
           db_append_string ( &sql, " where 0 = 1");
           }
           else
           {
           db_append_string ( &sql, " and 0 = 1");
           }

           free (tmp);
         */
    }
    else {
        db_set_string(&sql, "select * from ");
        db_append_string(&sql, from_tblname);
        db_append_string(&sql, " where 0 = 1"); /* to get no data */
    }

    G_debug(3, "db__copy_table: %s", db_get_string(&sql));
    if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) !=
        DB_OK) {
        G_warning(_("Unable to open select cursor: '%s'"),
                  db_get_string(&sql));
        db_close_database_shutdown_driver(to_driver);
        if (from_driver != to_driver) {
            db_close_database_shutdown_driver(from_driver);
        }
        return DB_FAILED;
    }
    G_debug(3, "Select cursor opened");

    table = db_get_cursor_table(&cursor);
    ncols = db_get_table_number_of_columns(table);
    G_debug(3, "ncols = %d", ncols);

    out_table = db_alloc_table(ncols);
    db_set_table_name(out_table, to_tblname);

    selcol_found = 0;
    for (col = 0; col < ncols; col++) {
        dbColumn *out_column;

        column = db_get_table_column(table, col);
        colname = db_get_column_name(column);
        sqltype = db_get_column_sqltype(column);
        ctype = db_sqltype_to_Ctype(sqltype);

        G_debug(3, "%s (%s)", colname, db_sqltype_name(sqltype));

        out_column = db_get_table_column(out_table, col);

        if (selcol && G_strcasecmp(colname, selcol) == 0) {
            if (ctype != DB_C_TYPE_INT)
                G_fatal_error(_("Column <%s> is not integer"),
                              colname);
            selcol_found = 1;
        }

        db_set_column_name(out_column, db_get_column_name(column));
        db_set_column_description(out_column,
                                  db_get_column_description(column));
        db_set_column_sqltype(out_column, db_get_column_sqltype(column));
        db_set_column_length(out_column, db_get_column_length(column));
        db_set_column_precision(out_column, db_get_column_precision(column));
        db_set_column_scale(out_column, db_get_column_scale(column));
    }

    db_close_cursor(&cursor);

    if (selcol && !selcol_found)
        G_fatal_error(_("Column <%s> not found"), selcol);

    if (db_create_table(to_driver, out_table) != DB_OK) {
        G_warning(_("Unable to create table <%s>"),
                  to_tblname);
        db_close_database_shutdown_driver(to_driver);
        if (from_driver != to_driver) {
            db_close_database_shutdown_driver(from_driver);
        }
        return DB_FAILED;
    }

    /* Open cursor with data */
    if (select) {
        db_set_string(&sql, select);
    }
    else {
        db_set_string(&sql, "select * from ");
        db_append_string(&sql, from_tblname);
        if (where) {
            db_append_string(&sql, " where ");
            db_append_string(&sql, where);
        }
    }

    G_debug(3, "db__copy_table: %s", db_get_string(&sql));
    if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) !=
        DB_OK) {
        G_warning(_("Unable to open select cursor: '%s'"),
                  db_get_string(&sql));
        db_close_database_shutdown_driver(to_driver);
        if (from_driver != to_driver) {
            db_close_database_shutdown_driver(from_driver);
        }
        return DB_FAILED;
    }
    G_debug(3, "Select cursor opened");

    table = db_get_cursor_table(&cursor);
    ncols = db_get_table_number_of_columns(table);
    G_debug(3, "ncols = %d", ncols);

    /* Copy all rows */
    while (1) {
        int select;

        if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK) {
            G_warning(_("Unable to fetch data from table <%s>"),
                      from_tblname);
            db_close_cursor(&cursor);
            db_close_database_shutdown_driver(to_driver);
            if (from_driver != to_driver) {
                db_close_database_shutdown_driver(from_driver);
            }
            return DB_FAILED;
        }
        if (!more)
            break;

        sprintf(buf, "insert into %s values ( ", to_tblname);
        db_set_string(&sql, buf);
        select = 1;
        for (col = 0; col < ncols; col++) {
            column = db_get_table_column(table, col);
            colname = db_get_column_name(column);
            sqltype = db_get_column_sqltype(column);
            ctype = db_sqltype_to_Ctype(sqltype);
            value = db_get_column_value(column);

            if (selcol && G_strcasecmp(colname, selcol) == 0) {
                if (db_test_value_isnull(value))
                    continue;
                if (!bsearch(&(value->i), ivalues, nvals, sizeof(int), cmp)) {
                    select = 0;
                    break;
                }
            }
            if (col > 0)
                db_append_string(&sql, ", ");
            db_convert_value_to_string(value, sqltype, &value_string);
            switch (ctype) {
            case DB_C_TYPE_STRING:
            case DB_C_TYPE_DATETIME:
                if (db_test_value_isnull(value)) {
                    db_append_string(&sql, "null");
                }
                else {
                    db_double_quote_string(&value_string);
                    db_append_string(&sql, "'");
                    db_append_string(&sql, db_get_string(&value_string));
                    db_append_string(&sql, "'");
                }
                break;
            case DB_C_TYPE_INT:
            case DB_C_TYPE_DOUBLE:
                if (db_test_value_isnull(value)) {
                    db_append_string(&sql, "null");
                }
                else {
                    db_append_string(&sql, db_get_string(&value_string));
                }
                break;
            default:
                G_warning(_("Unknown column type (column <%s>)"),
                          colname);
                db_close_cursor(&cursor);
                db_close_database_shutdown_driver(to_driver);
                if (from_driver != to_driver) {
                    db_close_database_shutdown_driver(from_driver);
                }
                return DB_FAILED;
            }
        }
        if (!select)
            continue;
        db_append_string(&sql, ")");
        G_debug(3, "db__copy_table: %s", db_get_string(&sql));
        if (db_execute_immediate(to_driver, &sql) != DB_OK) {
            G_warning("Unable to insert new record: '%s'",
                      db_get_string(&sql));
            db_close_cursor(&cursor);
            db_close_database_shutdown_driver(to_driver);
            if (from_driver != to_driver) {
                db_close_database_shutdown_driver(from_driver);
            }
            return DB_FAILED;
        }
    }
    if (selcol)
        G_free(ivalues);
    G_debug(3, "Table copy OK");

    db_close_cursor(&cursor);
    db_commit_transaction(to_driver);
    db_close_database_shutdown_driver(to_driver);
    if (from_driver != to_driver) {
        db_close_database_shutdown_driver(from_driver);
    }

    return DB_OK;
}

int db_copy_table(const char *from_drvname, const char *from_dbname,
                  const char *from_tblname, const char *to_drvname,
                  const char *to_dbname, const char *to_tblname)
{
    return db__copy_table(from_drvname, from_dbname, from_tblname,
                          to_drvname, to_dbname, to_tblname,
                          NULL, NULL, NULL, NULL, 0);
}

int db_copy_table_where(const char *from_drvname, const char *from_dbname,
                        const char *from_tblname, const char *to_drvname,
                        const char *to_dbname, const char *to_tblname,
                        const char *where)
{
    return db__copy_table(from_drvname, from_dbname, from_tblname,
                          to_drvname, to_dbname, to_tblname,
                          where, NULL, NULL, NULL, 0);
}

int db_copy_table_select(const char *from_drvname, const char *from_dbname,
                         const char *from_tblname, const char *to_drvname,
                         const char *to_dbname, const char *to_tblname,
                         const char *select)
{
    return db__copy_table(from_drvname, from_dbname, from_tblname,
                          to_drvname, to_dbname, to_tblname,
                          NULL, select, NULL, NULL, 0);
}

int db_copy_table_by_ints(const char *from_drvname, const char *from_dbname,
                          const char *from_tblname, const char *to_drvname,
                          const char *to_dbname, const char *to_tblname,
                          const char *selcol, int *ivals, int nvals)
{
    return db__copy_table(from_drvname, from_dbname, from_tblname,
                          to_drvname, to_dbname, to_tblname,
                          NULL, NULL, selcol, ivals, nvals);
}