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

/**
 * \file describe.c
 *
 * \brief Low level SQLite database driver.
 *
 * This program is free software under the GNU General Public License
 * (>=v2). Read the file COPYING that comes with GRASS for details.
 *
 * \author Radim Blazek
 *
 * \date 2005-2007
 */

#include <string.h>
#include <grass/dbmi.h>
#include <grass/datetime.h>
#include <grass/glocale.h>
#include "globals.h"
#include "proto.h"

/* function prototypes */
static int affinity_type(const char *);
static int parse_type(const char *, int *);
static void get_column_info(sqlite3_stmt * statement, int col,
			    int *litetype, int *sqltype, int *length);

/**
 * \fn int db__driver_describe_table (dbString *table_name, dbTable **table)
 *
 * \brief Low level SQLite describe database table.
 *
 * \param[in] table_name
 * \param[in] table
 * \return int DB_FAILED on error; DB_OK on success
 */

int db__driver_describe_table(dbString * table_name, dbTable ** table)
{
    dbString sql;
    sqlite3_stmt *statement;
    const char *rest;
    int ret;

    db_init_string(&sql);

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

    ret = sqlite3_prepare(sqlite, db_get_string(&sql), -1, &statement, &rest);

    if (ret != SQLITE_OK) {
	append_error("Error in sqlite3_prepare():");
	append_error(db_get_string(&sql));
	append_error("\n");
	append_error((char *)sqlite3_errmsg(sqlite));
	report_error();
	db_free_string(&sql);
	return DB_FAILED;
    }

    db_free_string(&sql);

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

    sqlite3_finalize(statement);

    return DB_OK;
}


/**
 * \fn int describe_table (sqlite3_stmt *statement, dbTable **table, cursor *c)
 *
 * \brief SQLite describe table.
 *
 * NOTE: If <b>c</b> is not NULL c->cols and c->ncols are also set.
 *
 * \param[in] statement
 * \param[in] table
 * \param[in] c SQLite cursor. See NOTE.
 * \return int DB_FAILED on error; DB_OK on success
 */

int describe_table(sqlite3_stmt * statement, dbTable ** table, cursor * c)
{
    int i, ncols, nkcols;

    G_debug(3, "describe_table()");

    ncols = sqlite3_column_count(statement);
    G_debug(3, "ncols = %d", ncols);

    /* Try to get first row */
    sqlite3_step(statement);

    /* Count columns of known type */
    nkcols = 0;

    for (i = 0; i < ncols; i++) {
	int litetype, sqltype, length;

	get_column_info(statement, i, &litetype, &sqltype, &length);

	if (sqltype == DB_SQL_TYPE_UNKNOWN)
	    continue;

	nkcols++;		/* known types */
    }

    G_debug(3, "nkcols = %d", nkcols);

    if (c) {
	c->kcols = (int *)G_malloc(nkcols * sizeof(int));
	c->nkcols = nkcols;
    }

    if (!(*table = db_alloc_table(nkcols))) {
	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);
     */

    nkcols = 0;
    for (i = 0; i < ncols; i++) {
	const char *fname;
	dbColumn *column;
	int litetype, sqltype, fsize, precision, scale;

	fname = sqlite3_column_name(statement, i);

	get_column_info(statement, i, &litetype, &sqltype, &fsize);

	G_debug(2, "col: %s, nkcols %d, litetype : %d, sqltype %d",
		fname, nkcols, litetype, sqltype);

	if (sqltype == DB_SQL_TYPE_UNKNOWN) {
	    /* Warn, ignore and continue */
	    G_warning(_("SQLite driver: column '%s', SQLite type %d  is not supported"),
		      fname, litetype);
	    continue;
	}

	switch (sqltype) {
	case DB_SQL_TYPE_SMALLINT:
	case DB_SQL_TYPE_INTEGER:
	case DB_SQL_TYPE_SERIAL:
	    fsize = 20;
	    break;

	case DB_SQL_TYPE_REAL:
	case DB_SQL_TYPE_DOUBLE_PRECISION:
	case DB_SQL_TYPE_DECIMAL:
	case DB_SQL_TYPE_NUMERIC:
	    fsize = 20;
	    break;

	case DB_SQL_TYPE_DATE:
	case DB_SQL_TYPE_TIME:
	case DB_SQL_TYPE_TIMESTAMP:
	case DB_SQL_TYPE_INTERVAL:
	    fsize = 20;
	    break;

	case DB_SQL_TYPE_CHARACTER:
	    /* fsize is already correct */
	    break;

	case DB_SQL_TYPE_TEXT:
	    /* fudge for clients which don't understand variable-size fields */
	    fsize = 1000;
	    break;

	default:
	    G_warning("SQLite driver: unknown type: %d", sqltype);
	    fsize = 99999;	/* sqlite doesn't care, it must be long enough to
				   satisfy tests in GRASS */
	}

	column = db_get_table_column(*table, nkcols);

	db_set_column_name(column, fname);
	db_set_column_length(column, fsize);
	db_set_column_host_type(column, litetype);
	db_set_column_sqltype(column, sqltype);

	/* TODO */
	precision = 0;
	scale = 0;
	/*
	   db_set_column_precision (column, precision);
	   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->kcols[nkcols] = i;
	}

	nkcols++;
    }

    sqlite3_reset(statement);

    return DB_OK;
}


static int dbmi_type(int litetype)
{
    switch (litetype) {
    case SQLITE_INTEGER:
	return DB_SQL_TYPE_INTEGER;
    case SQLITE_FLOAT:
	return DB_SQL_TYPE_DOUBLE_PRECISION;
    case SQLITE_TEXT:
	return DB_SQL_TYPE_TEXT;
    case SQLITE_NULL:
	return DB_SQL_TYPE_TEXT;	/* good choice? */
    default:
	return DB_SQL_TYPE_UNKNOWN;
    }
}

/**
 * \fn void get_column_info (sqlite3_stmt *statement, int col, int *litetype, int *sqltype)
 *
 * \brief Low level SQLite get column information.
 *
 * \param[in] statement
 * \param[in] col
 * \param[in,out] litetype
 * \param[in,out] sqltype
 */

static void get_column_info(sqlite3_stmt * statement, int col,
			    int *litetype, int *sqltype, int *length)
{
    const char *decltype;

    decltype = sqlite3_column_decltype(statement, col);
    if (decltype) {
	G_debug(4, "column: %s, decltype = %s",
		sqlite3_column_name(statement, col), decltype);
	*sqltype = parse_type(decltype, length);
	*litetype = affinity_type(decltype);
    }
    else {
	G_debug(4, "this is not a table column");

	/* If there are no results it gives 0 */
	*litetype = sqlite3_column_type(statement, col);
	*sqltype = dbmi_type(*litetype);
	*length = 0;
    }

    G_debug(3, "sqltype = %d", *sqltype);
    G_debug(3, "litetype = %d", *litetype);
}

/*  SQLite documentation:
 *
 *   The type affinity of a column is determined by the declared 
 *   type of the column, according to the following rules:
 *
 *   1. If the datatype contains the string "INT" 
 *      then it is assigned INTEGER affinity.
 *
 *   2. If the datatype of the column contains any of the strings 
 *      "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. 
 *      Notice that the type VARCHAR contains the string "CHAR" 
 *      and is thus assigned TEXT affinity.
 *
 *   3. If the datatype for a column contains the string "BLOB" 
 *      or if no datatype is specified then the column has affinity NONE.
 *
 *   4. Otherwise, the affinity is NUMERIC.
 */

static int affinity_type(const char *declared)
{
    char *lc;
    int aff = SQLITE_FLOAT;

    lc = G_store(declared);
    G_tolcase(lc);
    G_debug(4, "affinity_type: %s", lc);

    if (strstr(lc, "int")) {
	aff = SQLITE_INTEGER;
    }
    else if (strstr(lc, "char") || strstr(lc, "clob")
	     || strstr(lc, "text") || strstr(lc, "date")) {
	aff = SQLITE_TEXT;
    }
    else if (strstr(lc, "blob")) {
	aff = SQLITE_BLOB;
    }

    G_free(lc);

    return aff;
}

static int parse_type(const char *declared, int *length)
{
    char buf[256];
    char word[4][256];

    strncpy(buf, declared, sizeof(buf));
    buf[sizeof(buf) - 1] = '\0';
    G_chop(buf);
    G_tolcase(buf);

    *length = 1;

#define streq(a,b) (strcmp((a),(b)) == 0)

    if (streq(buf, "smallint") || streq(buf, "int2"))
	return DB_SQL_TYPE_SMALLINT;

    if (streq(buf, "integer") ||
	streq(buf, "int") ||
	streq(buf, "int4") || streq(buf, "bigint") || streq(buf, "int8"))
	return DB_SQL_TYPE_INTEGER;

    if (streq(buf, "real") || streq(buf, "float4"))
	return DB_SQL_TYPE_REAL;

    if (streq(buf, "double") || streq(buf, "float8"))
	return DB_SQL_TYPE_DOUBLE_PRECISION;

    if (streq(buf, "decimal"))
	return DB_SQL_TYPE_DECIMAL;

    if (streq(buf, "numeric"))
	return DB_SQL_TYPE_NUMERIC;

    if (streq(buf, "date"))
	return DB_SQL_TYPE_DATE;
    if (streq(buf, "time") || streq(buf, "timetz"))
	return DB_SQL_TYPE_TIME;

    if (streq(buf, "timestamp") || streq(buf, "timestamptz"))
	return DB_SQL_TYPE_TIMESTAMP;

    if (streq(buf, "interval"))
	return DB_SQL_TYPE_INTERVAL;

    if (streq(buf, "text"))
	return DB_SQL_TYPE_TEXT;

    if (streq(buf, "serial") || streq(buf, "serial4"))
	return DB_SQL_TYPE_SERIAL;

    if (streq(buf, "character")
	|| streq(buf, "char")
	|| streq(buf, "varchar"))
	return DB_SQL_TYPE_CHARACTER;

    if (sscanf(buf, "%s %s", word[0], word[1]) == 2) {
	if (streq(word[0], "double") && streq(word[1], "precision"))
	    return DB_SQL_TYPE_DOUBLE_PRECISION;
	if (streq(word[0], "character") && streq(word[1], "varying"))
	    return DB_SQL_TYPE_CHARACTER;
    }

    if (sscanf(buf, "%s %s %s %s", word[0], word[1], word[2], word[3]) == 4 &&
	(streq(word[1], "with") || streq(word[1], "without")) &&
	streq(word[2], "time") && streq(word[3], "zone")) {
	if (streq(word[0], "time"))
	    return DB_SQL_TYPE_TIME;
	if (streq(word[0], "timestamp"))
	    return DB_SQL_TYPE_TIMESTAMP;
    }

    if (sscanf(buf, "varchar ( %d )", length) == 1 ||
	sscanf(buf, "character varying ( %d )", length) == 1 ||
	sscanf(buf, "character ( %d )", length) == 1 ||
	sscanf(buf, "char ( %d )", length) == 1)
	return DB_SQL_TYPE_CHARACTER;

    if (sscanf(buf, "interval ( %d )", length) == 1)
	return DB_SQL_TYPE_INTERVAL;

    if (sscanf(buf, "numeric ( %d , %d )", length, length) == 2)
	return DB_SQL_TYPE_NUMERIC;

    if (sscanf(buf, "decimal ( %d , %d )", length, length) == 2)
	return DB_SQL_TYPE_DECIMAL;

    if (sscanf(buf, "time ( %d )", length) == 1 ||
	sscanf(buf, "timetz ( %d )", length) == 1)
	return DB_SQL_TYPE_TIME;

    if (sscanf(buf, "timestamp ( %d )", length) == 1 ||
	sscanf(buf, "timestamptz ( %d )", length) == 1)
	return DB_SQL_TYPE_TIMESTAMP;

    if (sscanf
	(buf, "%s ( %d ) %s %s %s", word[0], length, word[1], word[2],
	 word[3]) == 5 && (streq(word[1], "with") ||
			   streq(word[1], "without")) &&
	streq(word[2], "time") && streq(word[3], "zone")) {
	if (streq(word[0], "time"))
	    return DB_SQL_TYPE_TIME;
	if (streq(word[0], "timestamp"))
	    return DB_SQL_TYPE_TIMESTAMP;
    }

#undef streq

    G_warning("SQLite driver: unable to parse decltype: %s", declared);
    return DB_SQL_TYPE_UNKNOWN;
}