Glynn Clements wrote: > What it should do (IMHO) is to parse the decltype directly to a DBMI > type (including parsing VARCHAR() types, to determine the size). It > should only use the affinity type if there is no decltype (which > occurs when a column in a SELECT statement is an expression rather > than a column reference). > > But db/drivers/sqlite/create_table.c also needs to create the columns > with the correct type in the first place. At present, it "condenses" > the type down to one of SQLite's affinity types (e.g. VARCHAR columns > are created as TEXT, as are TIME and DATE).
I have made these changes locally (patch attached). Can someone suggest some test cases? -- Glynn Clements <[EMAIL PROTECTED]>
Index: db/drivers/sqlite/proto.h =================================================================== --- db/drivers/sqlite/proto.h (revision 32073) +++ db/drivers/sqlite/proto.h (working copy) @@ -1,9 +1,11 @@ #ifndef __SQLITE_PROTO_H__ #define __SQLITE_PROTO_H__ +#include "globals.h" + /* error.c */ void init_error ( void ); -void append_error ( char * ); +void append_error ( const char * ); void report_error ( void ); /* cursor.c */ @@ -12,6 +14,5 @@ /* describe.c*/ int describe_table ( sqlite3_stmt *, dbTable **, cursor * ); -void get_column_info ( sqlite3_stmt *, int, int *, int * ); #endif /* __SQLITE_PROTO_H__ */ Index: db/drivers/sqlite/create_table.c =================================================================== --- db/drivers/sqlite/create_table.c (revision 32073) +++ db/drivers/sqlite/create_table.c (working copy) @@ -29,9 +29,6 @@ db__driver_create_table (dbTable *table) { int col, ncols; - dbColumn *column; - const char *colname; - int sqltype; sqlite3_stmt *statement; dbString sql; const char *rest; @@ -52,10 +49,12 @@ ncols = db_get_table_number_of_columns(table); 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); - + dbColumn *column = db_get_table_column (table, col); + const char *colname = db_get_column_name (column); + int sqltype = db_get_column_sqltype (column); + int collen = db_get_column_length (column); + char buf[32]; + G_debug ( 3, "%s (%s)", colname, db_sqltype_name(sqltype) ); if ( col > 0 ) db_append_string ( &sql, ", " ); @@ -63,25 +62,45 @@ db_append_string ( &sql, " " ); switch ( sqltype ) { case DB_SQL_TYPE_CHARACTER: - case DB_SQL_TYPE_TEXT: - db_append_string ( &sql, "text"); + sprintf(buf, "varchar(%d)", collen); + db_append_string ( &sql, buf); break; case DB_SQL_TYPE_SMALLINT: + db_append_string ( &sql, "smallint"); + break; case DB_SQL_TYPE_INTEGER: db_append_string ( &sql, "integer"); break; case DB_SQL_TYPE_REAL: + db_append_string ( &sql, "real"); + break; case DB_SQL_TYPE_DOUBLE_PRECISION: + db_append_string ( &sql, "double precision"); + break; case DB_SQL_TYPE_DECIMAL: + db_append_string ( &sql, "decimal"); + break; case DB_SQL_TYPE_NUMERIC: - case DB_SQL_TYPE_INTERVAL: - db_append_string ( &sql, "real"); + db_append_string ( &sql, "numeric"); break; case DB_SQL_TYPE_DATE: + db_append_string ( &sql, "date"); + break; case DB_SQL_TYPE_TIME: + db_append_string ( &sql, "time"); + break; case DB_SQL_TYPE_TIMESTAMP: + db_append_string ( &sql, "timestamp"); + break; + case DB_SQL_TYPE_INTERVAL: + db_append_string ( &sql, "interval"); + break; + case DB_SQL_TYPE_TEXT: db_append_string ( &sql, "text"); - break; + break; + case DB_SQL_TYPE_SERIAL: + db_append_string ( &sql, "serial"); + break; default: G_warning ( "Unknown column type (%s)", colname); return DB_FAILED; Index: db/drivers/sqlite/describe.c =================================================================== --- db/drivers/sqlite/describe.c (revision 32073) +++ db/drivers/sqlite/describe.c (working copy) @@ -20,8 +20,10 @@ /* 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) * @@ -105,9 +107,9 @@ for (i = 0; i < ncols; i++) { - int litetype, sqltype; + int litetype, sqltype, length; - get_column_info ( statement, i, &litetype, &sqltype ); + get_column_info ( statement, i, &litetype, &sqltype, &length ); if ( sqltype == DB_SQL_TYPE_UNKNOWN ) continue; @@ -150,7 +152,7 @@ fname = sqlite3_column_name ( statement, i ); - get_column_info ( statement, i, &litetype, &sqltype ); + get_column_info ( statement, i, &litetype, &sqltype, &fsize ); G_debug(2, "col: %s, nkcols %d, litetype : %d, sqltype %d", fname, nkcols, litetype, sqltype ); @@ -163,22 +165,38 @@ continue; } - switch ( litetype) { - case SQLITE_INTEGER: + switch (sqltype) { + case DB_SQL_TYPE_SMALLINT: + case DB_SQL_TYPE_INTEGER: + case DB_SQL_TYPE_SERIAL: fsize = 20; break; - case SQLITE_FLOAT: + 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 SQLITE_TEXT: - fsize = 255; + 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; - /* SQLITE_BLOB, SQLITE_NULL needed here? */ - 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 */ } @@ -223,6 +241,17 @@ } +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) * @@ -234,8 +263,8 @@ * \param[in,out] sqltype */ -void get_column_info ( sqlite3_stmt *statement, int col, - int *litetype, int *sqltype ) +static void get_column_info ( sqlite3_stmt *statement, int col, + int *litetype, int *sqltype, int *length ) { const char *decltype; @@ -243,6 +272,7 @@ 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 @@ -251,54 +281,12 @@ /* 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 ); - - /* http://www.sqlite.org/capi3ref.html#sqlite3_column_type - SQLITE_INTEGER 1 - SQLITE_FLOAT 2 - SQLITE_TEXT 3 - SQLITE_BLOB 4 - SQLITE_NULL 5 - - lib/db/dbmi_base/sqltype.c: - DB_SQL_TYPE_CHARACTER: 0 "CHARACTER" - DB_SQL_TYPE_NUMERIC: 1 "NUMERIC" - DB_SQL_TYPE_DECIMAL: 2 "DECIMAL" - DB_SQL_TYPE_SMALLINT: 3 "SMALLINT" - DB_SQL_TYPE_INTEGER: 4 "INTEGER" - DB_SQL_TYPE_REAL: 5 "REAL" - DB_SQL_TYPE_DOUBLE_PRECISION: 6 "DOUBLE PRECISION" - DB_SQL_TYPE_DATE: 7 "DATE" - DB_SQL_TYPE_TIME: 8 "TIME" - DB_SQL_TYPE_SERIAL: 9 "SERIAL" - DB_SQL_TYPE_TEXT: 10 "TEXT" - DB_SQL_TYPE_TIMESTAMP: 11 "TIMESTAMP" - DB_SQL_TYPE_INTERVAL: 12 "INTERVAL" - default 13 "unknown" - */ - - switch ( *litetype) { - case SQLITE_INTEGER: - *sqltype = DB_SQL_TYPE_INTEGER; - break; - - case SQLITE_FLOAT: - *sqltype = DB_SQL_TYPE_DOUBLE_PRECISION; - break; - - case SQLITE_TEXT: - *sqltype = DB_SQL_TYPE_TEXT; - break; - - case SQLITE_NULL: - *sqltype = DB_SQL_TYPE_TEXT; /* good choice? */ - break; - - default: - *sqltype = DB_SQL_TYPE_UNKNOWN; - } } /* SQLite documentation: @@ -325,7 +313,7 @@ char *lc; int aff = SQLITE_FLOAT; - lc = strdup ( declared ); + lc = G_store ( declared ); G_tolcase ( lc ); G_debug(4, "affinity_type: %s", lc); @@ -342,7 +330,131 @@ { 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; +} Index: db/drivers/sqlite/main.c =================================================================== --- db/drivers/sqlite/main.c (revision 32073) +++ db/drivers/sqlite/main.c (working copy) @@ -11,12 +11,14 @@ * for details. * **************************************************************/ -#define MAIN + #include <stdlib.h> #include <grass/dbmi.h> #include "globals.h" #include "dbdriver.h" +sqlite3 *sqlite; + int main (int argc, char *argv[]) { Index: db/drivers/sqlite/globals.h =================================================================== --- db/drivers/sqlite/globals.h (revision 32073) +++ db/drivers/sqlite/globals.h (working copy) @@ -1,5 +1,8 @@ #include <sqlite3.h> +#ifndef DBMI_SQLITE_PROTO_H +#define DBMI_SQLITE_PROTO_H + /* cursors */ typedef struct _cursor { sqlite3_stmt *statement; @@ -12,12 +15,7 @@ } cursor; -#ifdef MAIN - sqlite3 *sqlite; - dbString *errMsg = NULL; /* error message */ -#else - extern sqlite3 *sqlite; - extern dbString *errMsg; -#endif +extern sqlite3 *sqlite; +#endif Index: db/drivers/sqlite/fetch.c =================================================================== --- db/drivers/sqlite/fetch.c (revision 32073) +++ db/drivers/sqlite/fetch.c (working copy) @@ -106,6 +106,8 @@ int col, litetype, sqltype; dbColumn *column; dbValue *value; + const char *text; + dbDateTime *dt; col = c->kcols[i]; /* known cols */ @@ -115,6 +117,7 @@ litetype = db_get_column_host_type(column); */ litetype = sqlite3_column_type ( c->statement, col ); + text = (const char *) sqlite3_column_text ( c->statement, col); value = db_get_column_value (column); db_zero_string (&value->s); @@ -128,8 +131,7 @@ } G_debug (3, "col %d, litetype %d, sqltype %d: val = '%s'", - col, litetype, sqltype, - sqlite3_column_text ( c->statement, col) ); + col, litetype, sqltype, text ); /* http://www.sqlite.org/capi3ref.html#sqlite3_column_type SQLITE_INTEGER 1 @@ -145,38 +147,88 @@ /* Note: we have set DATESTYLE TO ISO in db_driver_open_select_cursor() so datetime * format should be ISO */ - switch ( litetype ) { - case SQLITE_TEXT: - if (sqltype == DB_SQL_TYPE_DATE ) { /* date string */ - /* Example: '1999-01-25' */ - G_debug(3, "sqlite fetched date: %s",sqlite3_column_text ( c->statement, col)); - ns = sscanf((char *) sqlite3_column_text ( c->statement, col), "%4d-%2d-%2d", - &(value->t.year), &(value->t.month), &(value->t.day) ); - if ( ns != 3 ) { - append_error ( "Cannot scan date:"); - append_error ((char *) sqlite3_column_text (c->statement, col)); - report_error(); - return DB_FAILED; - } - value->t.hour = 0; - value->t.minute = 0; - value->t.seconds = 0.0; - } else { /* other string */ - db_set_string ( &(value->s), (char *) sqlite3_column_text ( c->statement, col)); - } - break; + switch ( sqltype ) { + case DB_SQL_TYPE_INTEGER: + case DB_SQL_TYPE_SMALLINT: + case DB_SQL_TYPE_SERIAL: + value->i = sqlite3_column_int ( c->statement, col); + break; - case SQLITE_INTEGER: - value->i = sqlite3_column_int ( c->statement, col); - break; - - case SQLITE_FLOAT: - value->d = sqlite3_column_double ( c->statement, col); - break; + case DB_SQL_TYPE_REAL: + case DB_SQL_TYPE_DOUBLE_PRECISION: + value->d = sqlite3_column_double ( c->statement, col); + break; - case SQLITE_NULL: - /* do nothing ? */ - break; + case DB_SQL_TYPE_DATE: + dt = &value->t; + dt->hour = 0; + dt->minute = 0; + dt->seconds = 0.0; + G_debug(3, "sqlite fetched date: %s", text); + ns = sscanf(text, "%4d-%2d-%2d", + &dt->year, &dt->month, &dt->day); + if ( ns != 3 ) { + append_error ( "Cannot scan date:"); + append_error (text); + report_error(); + return DB_FAILED; + } + break; + + case DB_SQL_TYPE_TIME: + dt = &value->t; + dt->year = 0; + dt->month = 0; + dt->day = 0; + G_debug(3, "sqlite fetched date: %s", text); + ns = sscanf(text, "%2d:%2d:%lf", + &dt->hour, &dt->minute, &dt->seconds); + if ( ns != 3 ) { + append_error ( "Cannot scan time:"); + append_error (text); + report_error(); + return DB_FAILED; + } + break; + + case DB_SQL_TYPE_TIMESTAMP: + dt = &value->t; + G_debug(3, "sqlite fetched timestamp: %s", text); + ns = sscanf(text, "%4d-%2d-%2d %2d:%2d:%lf", + &dt->year, &dt->month, &dt->day, + &dt->hour, &dt->minute, &dt->seconds); + if ( ns != 6 ) { + append_error ( "Cannot scan timestamp:"); + append_error (text); + report_error(); + return DB_FAILED; + } + break; + + case DB_SQL_TYPE_INTERVAL: + dt = &value->t; + dt->year = 0; + dt->month = 0; + dt->day = 0; + dt->hour = 0; + dt->minute = 0; + G_debug(3, "sqlite fetched interval: %s", text); + G_warning("SQLite driver: parsing of interval values not implemented; assuming seconds"); + ns = sscanf(text, "%lf", &dt->seconds); + if ( ns != 1 ) { + append_error ( "Cannot scan interval:"); + append_error (text); + report_error(); + return DB_FAILED; + } + break; + + case DB_SQL_TYPE_DECIMAL: + case DB_SQL_TYPE_NUMERIC: + case DB_SQL_TYPE_CHARACTER: + case DB_SQL_TYPE_TEXT: + db_set_string ( &value->s, text); + break; } } Index: db/drivers/sqlite/error.c =================================================================== --- db/drivers/sqlite/error.c (revision 32073) +++ db/drivers/sqlite/error.c (working copy) @@ -14,8 +14,11 @@ #include <stdio.h> #include <grass/gis.h> #include <grass/dbmi.h> +#include "proto.h" #include "globals.h" +static dbString *errMsg = NULL; /* error message */ + /* init error message */ void init_error ( void ) @@ -30,7 +33,7 @@ /* append error message */ void -append_error ( char *msg ) +append_error ( const char *msg ) { db_append_string ( errMsg, msg); }
_______________________________________________ grass-dev mailing list grass-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/grass-dev