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_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_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_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; }
/* describe table, if c is not NULL cur->cols and cur->ncols is also set * cursor may be null */ int describe_table(OGRLayerH hLayer, dbTable ** table, cursor * c) { int i, ncols, kcols; dbColumn *column; OGRFeatureDefnH hFeatureDefn; OGRFieldDefnH hFieldDefn; const char *fieldName; int ogrType; int *cols; G_debug(3, "describe_table()"); hFeatureDefn = OGR_L_GetLayerDefn(hLayer); ncols = OGR_FD_GetFieldCount(hFeatureDefn); G_debug(3, "ncols = %d", ncols); /* Identify known columns */ cols = (int *)G_malloc(ncols * sizeof(int)); kcols = 0; for (i = 0; i < ncols; i++) { hFieldDefn = OGR_FD_GetFieldDefn(hFeatureDefn, i); ogrType = OGR_Fld_GetType(hFieldDefn); OGR_Fld_GetNameRef(hFieldDefn); fieldName = OGR_Fld_GetNameRef(hFieldDefn); if (ogrType != OFTInteger && ogrType != OFTReal && ogrType != OFTString && ogrType != OFTDate && ogrType != OFTTime && ogrType != OFTDateTime ) { G_warning(_("OGR driver: column '%s', OGR type %d is not supported"), fieldName, ogrType); cols[i] = 0; } else { cols[i] = 1; kcols++; } } if (!(*table = db_alloc_table(kcols))) { return DB_FAILED; } /* set the table name */ /* TODO */ db_set_table_name(*table, ""); /* set the table description */ db_set_table_description(*table, ""); /* TODO */ /* db_set_table_delete_priv_granted (*table); db_set_table_insert_priv_granted (*table); db_set_table_delete_priv_not_granted (*table); db_set_table_insert_priv_not_granted (*table); */ for (i = 0; i < ncols; i++) { int sqlType; int size, precision, scale; if (!(cols[i])) continue; /* unknown type */ hFieldDefn = OGR_FD_GetFieldDefn(hFeatureDefn, i); ogrType = OGR_Fld_GetType(hFieldDefn); fieldName = OGR_Fld_GetNameRef(hFieldDefn); G_debug(3, "field %d : ogrType = %d, name = %s", i, ogrType, fieldName); switch (ogrType) { case OFTInteger: sqlType = DB_SQL_TYPE_INTEGER; size = OGR_Fld_GetWidth(hFieldDefn); /* OK ? */ precision = 0; break; case OFTReal: sqlType = DB_SQL_TYPE_DOUBLE_PRECISION; size = OGR_Fld_GetWidth(hFieldDefn); /* OK ? */ precision = OGR_Fld_GetPrecision(hFieldDefn); break; case OFTString: case OFTDate: case OFTTime: case OFTDateTime: sqlType = DB_SQL_TYPE_CHARACTER; size = OGR_Fld_GetWidth(hFieldDefn); if (size == 0) { G_warning(_("column '%s', type 'string': unknown width -> stored as varchar(250) " "some data may be lost"), fieldName); size = 250; } precision = 0; break; default: G_warning(_("Unknown type")); break; } column = db_get_table_column(*table, i); db_set_column_host_type(column, ogrType); db_set_column_sqltype(column, sqlType); db_set_column_name(column, fieldName); db_set_column_length(column, size); db_set_column_precision(column, precision); /* TODO */ scale = 0; /* db_set_column_scale (column, scale); */ /* TODO */ db_set_column_null_allowed(column); db_set_column_has_undefined_default_value(column); db_unset_column_use_default_value(column); /* TODO */ /* db_set_column_select_priv_granted (column); db_set_column_update_priv_granted (column); db_set_column_update_priv_not_granted (column); */ } if (c) { c->cols = cols; c->ncols = ncols; } else { G_free(cols); } return DB_OK; }
/* describe table, if c is not NULL cur->cols and cur->ncols is also set */ int describe_table(MYSQL_RES * res, dbTable ** table, cursor * c) { int i, ncols, kcols; char *name; int sqltype, length; dbColumn *column; MYSQL_FIELD *fields; G_debug(3, "describe_table()"); ncols = mysql_num_fields(res); fields = mysql_fetch_fields(res); /* Count columns of known type */ kcols = 0; for (i = 0; i < ncols; i++) { field_info(&(fields[i]), &sqltype, &length); if (sqltype == DB_SQL_TYPE_UNKNOWN) continue; kcols++; /* known types */ } G_debug(3, "kcols = %d", kcols); if (!(*table = db_alloc_table(kcols))) { return DB_FAILED; } if (c) { c->ncols = kcols; c->cols = (int *)G_malloc(kcols * sizeof(int)); } db_set_table_name(*table, ""); db_set_table_description(*table, ""); /* Currently not used in GRASS */ /* db_set_table_delete_priv_granted (*table); db_set_table_insert_priv_granted (*table); db_set_table_delete_priv_not_granted (*table); db_set_table_insert_priv_not_granted (*table); */ kcols = 0; for (i = 0; i < ncols; i++) { name = fields[i].name; field_info(&(fields[i]), &sqltype, &length); G_debug(3, "col: %s, kcols %d, sqltype %d", name, kcols, sqltype); G_debug(3, "flags = %d", fields[i].flags); if (sqltype == DB_SQL_TYPE_UNKNOWN) { /* Print warning and continue */ G_warning(_("MySQL driver: column '%s', type %d " "is not supported"), name, fields[i].type); continue; } if (fields[i].type == MYSQL_TYPE_LONGLONG) G_warning(_("column '%s' : type BIGINT is stored as " "integer (4 bytes) some data may be damaged"), name); column = db_get_table_column(*table, kcols); db_set_column_name(column, name); db_set_column_length(column, length); db_set_column_host_type(column, (int)fields[i].type); db_set_column_sqltype(column, sqltype); db_set_column_precision(column, (int)fields[i].decimals); db_set_column_scale(column, 0); if (!(fields[i].flags & NOT_NULL_FLAG)) { db_set_column_null_allowed(column); } db_set_column_has_undefined_default_value(column); db_unset_column_use_default_value(column); /* Currently not used in GRASS */ /* db_set_column_select_priv_granted (column); db_set_column_update_priv_granted (column); db_set_column_update_priv_not_granted (column); */ if (c) { c->cols[kcols] = i; } kcols++; } return DB_OK; }