From deb802cd83000da6cc0f7f88a189d40b6b91e9e1 Mon Sep 17 00:00:00 2001 From: danielk1977 Date: Thu, 9 Feb 2006 13:43:28 +0000 Subject: [PATCH] Add the sqlite3_table_column_meta() API. (CVS 3062) FossilOrigin-Name: 1ac72f68c0e9fd63decc97c166f49b405a9d323c --- manifest | 19 ++++---- manifest.uuid | 2 +- src/main.c | 116 +++++++++++++++++++++++++++++++++++++++++++++- src/sqlite.h.in | 68 ++++++++++++++++++++++++++- src/test1.c | 66 +++++++++++++++++++++++++- test/colmeta.test | 77 ++++++++++++++++++++++++++++++ 6 files changed, 335 insertions(+), 13 deletions(-) create mode 100644 test/colmeta.test diff --git a/manifest b/manifest index 384e4c869d..ab1870ba32 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Avoid\soverflowing\sthe\s48-bit\smantissa\sof\sa\sfloating\spoint\snumber\swhen\nsumming\slarge\sintegers\sin\sthe\sSUM()\sfunction.\s\sTicket\s#1664.\s(CVS\s3061) -D 2006-02-09T13:38:20 +C Add\sthe\ssqlite3_table_column_meta()\sAPI.\s(CVS\s3062) +D 2006-02-09T13:43:29 F Makefile.in 5d8dff443383918b700e495de42ec65bc1c8865b F Makefile.linux-gcc 74ba0eadf88748a9ce3fd03d2a3ede2e6715baec F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -48,7 +48,7 @@ F src/hash.c 8747cf51d12de46512880dfcf1b68b4e24072863 F src/hash.h 1b0c445e1c89ff2aaad9b4605ba61375af001e84 F src/insert.c 7e931b7f06afbcefcbbaab175c02eff8268db33f F src/legacy.c 86b669707b3cefd570e34154e2f6457547d1df4f -F src/main.c ce5b9f0af959b6c7dc14c42815edd28e2153621b +F src/main.c 9a42464c44a6532003391486e802e65e88789cfc F src/md5.c c5fdfa5c2593eaee2e32a5ce6c6927c986eaf217 F src/os.c 59f05de8c5777c34876607114a2fbe55ae578235 F src/os.h 93035a0e3b9dd05cdd0aaef32ea28ca28e02fe78 @@ -69,11 +69,11 @@ F src/random.c d40f8d356cecbd351ccfab6eaedd7ec1b54f5261 F src/select.c daee9b20702ba51cf3807fc1b130edd8846e3e48 F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96 F src/shell.c 738f55ed75fb36731e764bfdb40756ac43b90b08 -F src/sqlite.h.in 0faed8909639e4d87a9641e1942065757d23adc5 +F src/sqlite.h.in 965128af2e36334824532aaa9b0909ab49436d1a F src/sqliteInt.h 0121298397ac14eb468ab1ba9d488ac7ed7d88a1 F src/table.c 486dcfce532685b53b5a2b5da8bba0ded6fb2316 F src/tclsqlite.c 7764ab34df617b3d3cfd5f0fdf3444ed219c11d6 -F src/test1.c ce715e15c8045c598fe83a17f862ddeedf60c057 +F src/test1.c 894df7bced48bd30be04ab9990350900ae33557d F src/test2.c ca74a1d8aeb7d9606e8f6b762c5daf85c1a3f92b F src/test3.c 86e99724ee898b119ed575ef9f98618afe7e5e5d F src/test4.c ff4e9406b3d2809966d8f0e82468ac5508be9f56 @@ -142,6 +142,7 @@ F test/collate3.test 947a77f5b8227e037a7094d0e338a5504f155cc4 F test/collate4.test daf498e294dcd596b961d425c3f2dda117e4717e F test/collate5.test 8fb4e7e0241839356bd8710f437c32efb47bfff8 F test/collate6.test 6c9470d1606ee3e564675b229653e320c49ec638 +F test/colmeta.test 0052d4b68dd292b016804001d08bf636c5ecd449 F test/conflict.test 16533a92675f9752c25596093a4d549af7fc3d34 F test/corrupt.test 18c7a995b1af76a8c8600b996257f2c7b7bff083 F test/corrupt2.test 88342570828f2b8cbbd8369eff3891f5c0bdd5ba @@ -350,7 +351,7 @@ F www/tclsqlite.tcl bb0d1357328a42b1993d78573e587c6dcbc964b9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl a99cf5f6d8bd4d5537584a2b342f0fb9fa601d8b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P baef2f66be164910881278ea527d2be75ac2e944 -R 447165c89df570db65d416c0baa9b959 -U drh -Z 308693939eb8035424937f098052bb3a +P a9169e879de5d5e4192d1681bc3e119fb83e739c +R eb59a6ba771a7cc02809648acaa12265 +U danielk1977 +Z 279c4d9c07e1c4addc6e70e0387b9c2f diff --git a/manifest.uuid b/manifest.uuid index 2a1f6fe2ce..7cd672937c 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -a9169e879de5d5e4192d1681bc3e119fb83e739c \ No newline at end of file +1ac72f68c0e9fd63decc97c166f49b405a9d323c \ No newline at end of file diff --git a/src/main.c b/src/main.c index 258731b25d..42e3200fac 100644 --- a/src/main.c +++ b/src/main.c @@ -14,7 +14,7 @@ ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** -** $Id: main.c,v 1.333 2006/02/01 13:50:42 drh Exp $ +** $Id: main.c,v 1.334 2006/02/09 13:43:29 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" @@ -1117,3 +1117,117 @@ void sqlite3_thread_cleanup(void){ sqlite3OsThreadSpecificData(-1); } } + +/* +** Return meta information about a specific column of a database table. +** See comment in sqlite3.h (sqlite.h.in) for details. +*/ +#ifdef SQLITE_ENABLE_COLUMN_METADATA +int sqlite3_table_column_metadata( + sqlite3 *db, /* Connection handle */ + const char *zDbName, /* Database name or NULL */ + const char *zTableName, /* Table name */ + const char *zColumnName, /* Column name */ + char const **pzDataType, /* OUTPUT: Declared data type */ + char const **pzCollSeq, /* OUTPUT: Collation sequence name */ + int *pNotNull, /* OUTPUT: True if NOT NULL constraint exists */ + int *pPrimaryKey, /* OUTPUT: True if column part of PK */ + int *pAutoinc /* OUTPUT: True if colums is auto-increment */ +){ + int rc; + char *zErrMsg = 0; + Table *pTab = 0; + Column *pCol = 0; + int iCol; + + char const *zDataType = 0; + char const *zCollSeq = 0; + int notnull = 0; + int primarykey = 0; + int autoinc = 0; + + /* Ensure the database schema has been loaded */ + if( sqlite3SafetyOn(db) ){ + return SQLITE_MISUSE; + } + rc = sqlite3Init(db, &zErrMsg); + if( SQLITE_OK!=rc ){ + goto error_out; + } + + /* Locate the table in question */ + pTab = sqlite3FindTable(db, zTableName, zDbName); + if( !pTab || pTab->pSelect ){ + pTab = 0; + goto error_out; + } + + /* Find the column for which info is requested */ + if( sqlite3IsRowid(zColumnName) ){ + iCol = pTab->iPKey; + if( iCol>=0 ){ + pCol = &pTab->aCol[iCol]; + } + }else{ + for(iCol=0; iColnCol; iCol++){ + pCol = &pTab->aCol[iCol]; + if( 0==sqlite3StrICmp(pCol->zName, zColumnName) ){ + break; + } + } + if( iCol==pTab->nCol ){ + pTab = 0; + goto error_out; + } + } + + /* The following block stores the meta information that will be returned + ** to the caller in local variables zDataType, zCollSeq, notnull, primarykey + ** and autoinc. At this point there are two possibilities: + ** + ** 1. The specified column name was rowid", "oid" or "_rowid_" + ** and there is no explicitly declared IPK column. + ** + ** 2. The table is not a view and the column name identified an + ** explicitly declared column. Copy meta information from *pCol. + */ + if( pCol ){ + zDataType = pCol->zType; + zCollSeq = pCol->zColl; + notnull = (pCol->notNull?1:0); + primarykey = (pCol->isPrimKey?1:0); + autoinc = ((pTab->iPKey==iCol && pTab->autoInc)?1:0); + }else{ + zDataType = "INTEGER"; + primarykey = 1; + } + if( !zCollSeq ){ + zCollSeq = "BINARY"; + } + +error_out: + if( sqlite3SafetyOff(db) ){ + rc = SQLITE_MISUSE; + } + + /* Whether the function call succeeded or failed, set the output parameters + ** to whatever their local counterparts contain. If an error did occur, + ** this has the effect of zeroing all output parameters. + */ + if( pzDataType ) *pzDataType = zDataType; + if( pzCollSeq ) *pzCollSeq = zCollSeq; + if( pNotNull ) *pNotNull = notnull; + if( pPrimaryKey ) *pPrimaryKey = primarykey; + if( pAutoinc ) *pAutoinc = autoinc; + + if( SQLITE_OK==rc && !pTab ){ + sqlite3SetString(&zErrMsg, "no such table column: ", zTableName, ".", + zColumnName, 0); + rc = SQLITE_ERROR; + } + sqlite3Error(db, rc, (zErrMsg?"%s":0), zErrMsg); + sqliteFree(zErrMsg); + return sqlite3ApiExit(db, rc); +} +#endif + diff --git a/src/sqlite.h.in b/src/sqlite.h.in index 3e8b02266d..61af9a31da 100644 --- a/src/sqlite.h.in +++ b/src/sqlite.h.in @@ -12,7 +12,7 @@ ** This header file defines the interface that the SQLite library ** presents to client programs. ** -** @(#) $Id: sqlite.h.in,v 1.157 2006/01/31 20:49:13 drh Exp $ +** @(#) $Id: sqlite.h.in,v 1.158 2006/02/09 13:43:29 danielk1977 Exp $ */ #ifndef _SQLITE3_H_ #define _SQLITE3_H_ @@ -1370,6 +1370,72 @@ void sqlite3_soft_heap_limit(int); */ void sqlite3_thread_cleanup(void); +/* +** Return meta information about a specific column of a specific database +** table accessible using the connection handle passed as the first function +** argument. +** +** The column is identified by the second, third and fourth parameters to +** this function. The second parameter is either the name of the database +** (i.e. "main", "temp" or an attached database) containing the specified +** table or NULL. If it is NULL, then all attached databases are searched +** for the table using the same algorithm as the database engine uses to +** resolve unqualified table references. +** +** The third and fourth parameters to this function are the table and column +** name of the desired column, respectively. Neither of these parameters +** may be NULL. +** +** Meta information is returned by writing to the memory locations passed as +** the 5th and subsequent parameters to this function. Any of these +** arguments may be NULL, in which case the corresponding element of meta +** information is ommitted. +** +** Parameter Output Type Description +** ----------------------------------- +** +** 5th const char* Data type +** 6th const char* Name of the default collation sequence +** 7th int True if the column has a NOT NULL constraint +** 8th int True if the column is part of the PRIMARY KEY +** 9th int True if the column is AUTOINCREMENT +** +** +** The memory pointed to by the character pointers returned for the +** declaration type and collation sequence is valid only until the next +** call to any sqlite API function. +** +** If the specified table is actually a view, then an error is returned. +** +** If the specified column is "rowid", "oid" or "_rowid_" and an +** INTEGER PRIMARY KEY column has been explicitly declared, then the output +** parameters are set for the explicitly declared column. If there is no +** explicitly declared IPK column, then the output parameters are set as +** follows: +** +** data type: "INTEGER" +** collation sequence: "BINARY" +** not null: 0 +** primary key: 1 +** auto increment: 0 +** +** This function may load one or more schemas from database files. If an +** error occurs during this process, or if the requested table or column +** cannot be found, an SQLITE error code is returned and an error message +** left in the database handle (to be retrieved using sqlite3_errmsg()). +*/ +int sqlite3_table_column_metadata( + sqlite3 *db, /* Connection handle */ + const char *zDbName, /* Database name or NULL */ + const char *zTableName, /* Table name */ + const char *zColumnName, /* Column name */ + char const **pzDataType, /* OUTPUT: Declared data type */ + char const **pzCollSeq, /* OUTPUT: Collation sequence name */ + int *pNotNull, /* OUTPUT: True if NOT NULL constraint exists */ + int *pPrimaryKey, /* OUTPUT: True if column part of PK */ + int *pAutoinc /* OUTPUT: True if colums is auto-increment */ +); + /* ** Undo the hack that converts floating point types to integer for ** builds on processors without floating point support. diff --git a/src/test1.c b/src/test1.c index a3f8562462..f8510cc23f 100644 --- a/src/test1.c +++ b/src/test1.c @@ -13,7 +13,7 @@ ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** -** $Id: test1.c,v 1.203 2006/01/24 10:58:22 danielk1977 Exp $ +** $Id: test1.c,v 1.204 2006/02/09 13:43:29 danielk1977 Exp $ */ #include "sqliteInt.h" #include "tcl.h" @@ -1019,6 +1019,61 @@ static int test_libversion_number( return TCL_OK; } +/* +** Usage: sqlite3_table_column_metadata DB dbname tblname colname +** +*/ +#ifdef SQLITE_ENABLE_COLUMN_METADATA +static int test_table_column_metadata( + ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + sqlite3 *db; + const char *zDb; + const char *zTbl; + const char *zCol; + int rc; + Tcl_Obj *pRet; + + const char *zDatatype; + const char *zCollseq; + int notnull; + int primarykey; + int autoincrement; + + if( objc!=5 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB dbname tblname colname"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; + zDb = Tcl_GetString(objv[2]); + zTbl = Tcl_GetString(objv[3]); + zCol = Tcl_GetString(objv[4]); + + if( strlen(zDb)==0 ) zDb = 0; + + rc = sqlite3_table_column_metadata(db, zDb, zTbl, zCol, + &zDatatype, &zCollseq, ¬null, &primarykey, &autoincrement); + + if( rc!=SQLITE_OK ){ + Tcl_AppendResult(interp, sqlite3_errmsg(db), 0); + return TCL_ERROR; + } + + pRet = Tcl_NewObj(); + Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zDatatype, -1)); + Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zCollseq, -1)); + Tcl_ListObjAppendElement(0, pRet, Tcl_NewIntObj(notnull)); + Tcl_ListObjAppendElement(0, pRet, Tcl_NewIntObj(primarykey)); + Tcl_ListObjAppendElement(0, pRet, Tcl_NewIntObj(autoincrement)); + Tcl_SetObjResult(interp, pRet); + + return TCL_OK; +} +#endif + /* ** Usage: sqlite_abort ** @@ -3210,6 +3265,12 @@ static void set_options(Tcl_Interp *interp){ Tcl_SetVar2(interp, "sqlite_options", "check", "1", TCL_GLOBAL_ONLY); #endif +#ifdef SQLITE_ENABLE_COLUMN_METADATA + Tcl_SetVar2(interp, "sqlite_options", "columnmetadata", "1", TCL_GLOBAL_ONLY); +#else + Tcl_SetVar2(interp, "sqlite_options", "columnmetadata", "0", TCL_GLOBAL_ONLY); +#endif + #ifdef SQLITE_OMIT_COMPLETE Tcl_SetVar2(interp, "sqlite_options", "complete", "0", TCL_GLOBAL_ONLY); #else @@ -3542,6 +3603,9 @@ int Sqlitetest1_Init(Tcl_Interp *interp){ { "sqlite3_enable_shared_cache", test_enable_shared, 0 }, #endif { "sqlite3_libversion_number", test_libversion_number, 0 }, +#ifdef SQLITE_ENABLE_COLUMN_METADATA + { "sqlite3_table_column_metadata", test_table_column_metadata, 0 }, +#endif }; static int bitmask_size = sizeof(Bitmask)*8; int i; diff --git a/test/colmeta.test b/test/colmeta.test new file mode 100644 index 0000000000..10c677cec2 --- /dev/null +++ b/test/colmeta.test @@ -0,0 +1,77 @@ +# +# 2006 February 9 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this script is the sqlite3_table_column_metadata() API. +# +# $Id: colmeta.test,v 1.1 2006/02/09 13:43:29 danielk1977 Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable !columnmetadata { + finish_test + return +} + +# Set up a schema in the main and temp test databases. +do_test colmeta-0 { + execsql { + CREATE TABLE abc(a, b, c); + CREATE TABLE abc2(a PRIMARY KEY COLLATE NOCASE, b VARCHAR(32), c); + CREATE TABLE abc3(a NOT NULL, b INTEGER PRIMARY KEY, c); + CREATE TABLE abc4(a, b INTEGER PRIMARY KEY AUTOINCREMENT, c); + CREATE VIEW v1 AS SELECT * FROM abc2; + } +} {} + +# Return values are of the form: +# +# { } +# +set tests { + 1 {main abc a} {0 {{} BINARY 0 0 0}} + 2 {{} abc a} {0 {{} BINARY 0 0 0}} + 3 {{} abc2 b} {0 {VARCHAR(32) BINARY 0 0 0}} + 4 {main abc2 b} {0 {VARCHAR(32) BINARY 0 0 0}} + 5 {{} abc2 a} {0 {{} NOCASE 0 1 0}} + 6 {{} abc3 a} {0 {{} BINARY 1 0 0}} + 7 {{} abc3 b} {0 {INTEGER BINARY 0 1 0}} + 8 {{} abc4 b} {0 {INTEGER BINARY 0 1 1}} + 9 {{} v1 a} {1 {no such table column: v1.a}} + 10 {main v1 b} {1 {no such table column: v1.b}} + 11 {main v1 badname} {1 {no such table column: v1.badname}} + 12 {main v1 rowid} {1 {no such table column: v1.rowid}} + 13 {main abc rowid} {0 {INTEGER BINARY 0 1 0}} + 14 {main abc3 rowid} {0 {INTEGER BINARY 0 1 0}} + 14 {main abc4 rowid} {0 {INTEGER BINARY 0 1 1}} +} + +foreach {tn params results} $tests { + set ::DB [sqlite3_connection_pointer db] + + set tstbody [concat sqlite3_table_column_metadata $::DB $params] + do_test colmeta-$tn.1 { + list [catch $tstbody msg] [set msg] + } $results + + db close + sqlite3 db test.db + + set ::DB [sqlite3_connection_pointer db] + set tstbody [concat sqlite3_table_column_metadata $::DB $params] + do_test colmeta-$tn.2 { + list [catch $tstbody msg] [set msg] + } $results +} + +finish_test + -- 2.47.2