Geografic-Information-System/db/drivers/mysql/describe.c

/**********************************************************
 * MODULE:    mysql
 * AUTHOR(S): Radim Blazek (radim.blazek@gmail.com)
 * PURPOSE:   MySQL database driver
 * COPYRIGHT: (C) 2001 by the GRASS Development Team
 *            This program is free software under the 
 *            GNU General Public License (>=v2). 
 *            Read the file COPYING that comes with GRASS
 *            for details.
 **********************************************************/
#include <grass/gis.h>
#include <grass/dbmi.h>
#include <grass/glocale.h>

#include "globals.h"
#include "proto.h"

int db__driver_describe_table(dbString * table_name, dbTable ** table)
{
    dbString sql;
    MYSQL_RES *res;

    db_init_string(&sql);

    db_set_string(&sql, "select * from ");
    db_append_string(&sql, db_get_string(table_name));
    db_append_string(&sql, " where 1 = 0");

    if (mysql_query(connection, db_get_string(&sql)) != 0) {
	append_error(db_get_string(&sql));
	append_error("\n");
	append_error(mysql_error(connection));
	report_error();
	return DB_FAILED;
    }

    res = mysql_store_result(connection);

    if (res == NULL) {
	append_error(db_get_string(&sql));
	append_error("\n");
	append_error(mysql_error(connection));
	report_error();
	return DB_FAILED;
    }

    if (describe_table(res, table, NULL) == DB_FAILED) {
	append_error("Cannot describe table\n");
	report_error();
	mysql_free_result(res);
	return DB_FAILED;
    }

    mysql_free_result(res);

    db_set_table_name(*table, db_get_string(table_name));

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

/* Get sqltype for field */
void field_info(MYSQL_FIELD * field, int *sqltype, int *length)
{
    *length = field->length;

    switch (field->type) {
    case MYSQL_TYPE_TINY:
	*sqltype = DB_SQL_TYPE_SMALLINT;
	break;

    case MYSQL_TYPE_SHORT:
    case MYSQL_TYPE_LONG:
    case MYSQL_TYPE_INT24:
    case MYSQL_TYPE_LONGLONG:
	*sqltype = DB_SQL_TYPE_INTEGER;
	break;

    case MYSQL_TYPE_DECIMAL:
    case MYSQL_TYPE_NEWDECIMAL:
	*sqltype = DB_SQL_TYPE_DECIMAL;
	break;

    case MYSQL_TYPE_FLOAT:
	*sqltype = DB_SQL_TYPE_REAL;
	break;

    case MYSQL_TYPE_DOUBLE:
	*sqltype = DB_SQL_TYPE_DOUBLE_PRECISION;
	break;

    case MYSQL_TYPE_TIMESTAMP:
	*sqltype = DB_SQL_TYPE_TIMESTAMP;
	break;

    case MYSQL_TYPE_DATE:
	*sqltype = DB_SQL_TYPE_DATE;
	break;

    case MYSQL_TYPE_TIME:
	*sqltype = DB_SQL_TYPE_TIME;
	break;

    case MYSQL_TYPE_DATETIME:
	//*sqltype = DB_SQL_TYPE_DATETIME;
	//*sqltype |= DB_DATETIME_MASK;
	*sqltype = DB_SQL_TYPE_TIMESTAMP;
	break;

    case MYSQL_TYPE_YEAR:
	*sqltype = DB_SQL_TYPE_INTEGER;
	break;

    case MYSQL_TYPE_STRING:
    case MYSQL_TYPE_VAR_STRING:
    case MYSQL_TYPE_SET:
    case MYSQL_TYPE_ENUM:
	*sqltype = DB_SQL_TYPE_CHARACTER;
	break;

    case MYSQL_TYPE_BLOB:
	if (field->flags & BINARY_FLAG) {
	    *sqltype = DB_SQL_TYPE_UNKNOWN;
	}
	else {
	    *sqltype = DB_SQL_TYPE_TEXT;
	}
	break;

    case MYSQL_TYPE_GEOMETRY:
    case MYSQL_TYPE_NULL:
	*sqltype = DB_SQL_TYPE_UNKNOWN;
	break;

    default:
	*sqltype = DB_SQL_TYPE_UNKNOWN;
    }

    return;
}