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;
}