Allow virtual tables to mark columns as 'hidden'. Hidden columns do not show
up in "*" expansion, the implicit column list of an INSERT statement, or the results of a "PRAGMA table_info" statement. (CVS 4127)
FossilOrigin-Name: 7f887a6a00fdd6efc3740bd8a1731bb1abde087c
diff --git a/manifest b/manifest
index ea0e82d..ded2f5c 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Try\sto\swork\saround\san\sMSVC\scompiler\sbug.\s\sTicket\s#2457.\s(CVS\s4126)
-D 2007-06-26T01:04:49
+C Allow\svirtual\stables\sto\smark\scolumns\sas\s'hidden'.\sHidden\scolumns\sdo\snot\sshow\nup\sin\s"*"\sexpansion,\sthe\simplicit\scolumn\slist\sof\san\sINSERT\sstatement,\sor\sthe\sresults\sof\sa\s"PRAGMA\stable_info"\sstatement.\s(CVS\s4127)
+D 2007-06-26T10:38:54
F Makefile.in 7f7485a4cc039476a42e534b3f26ec90e2f9753e
F Makefile.linux-gcc 2d8574d1ba75f129aba2019f0b959db380a90935
F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028
@@ -79,7 +79,7 @@
F src/func.c 6b45261aa2c514f642201b90493af68469c04af6
F src/hash.c 67b23e14f0257b69a3e8aa663e4eeadc1a2b6fd5
F src/hash.h 1b3f7e2609141fd571f62199fc38687d262e9564
-F src/insert.c e595ca26805dfb3a9ebaabc28e7947c479f3b14d
+F src/insert.c 89d184422d85db0418e0f66032ccea3657078ecd
F src/legacy.c 388c71ad7fbcd898ba1bcbfc98a3ac954bfa5d01
F src/limits.h 71ab25f17e35e0a9f3f6f234b8ed49cc56731d35
F src/loadext.c b85b4e777cda9bf95475152ed240b6dfd2a0ecd9
@@ -100,16 +100,16 @@
F src/pager.c 39352b58ee840cae715a4f0d20e446aa5e1445fe
F src/pager.h 94110a5570dca30d54a883e880a3633b2e4c05ae
F src/parse.y ad2ce25665be7f7303137f774a4e3e72e0d036ff
-F src/pragma.c 838f7460e6f228cc80cd10846ee1139f3ae723dd
+F src/pragma.c 7914a6b9ea05f158800116dfcae11e52ab8e39c4
F src/prepare.c 609bb27860ce98ab39889fecc0998dfd8220891b
F src/printf.c 9b3048d270e8bb2f8b910b491ac3aadece6cfab2
F src/random.c 6119474a6f6917f708c1dee25b9a8e519a620e88
-F src/select.c 2ee53f929fe3755a35de1fb937f22e2e60f7d3e5
+F src/select.c e363327d0eba8d758ab00055de962a3bb0bc213e
F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96
F src/shell.c 4b0fc3c76a9f23a1c963e01703c0fbbca1b5c34d
F src/sqlite.h.in 6f290b660b2e7c3359968bb4b344ec31a1178746
F src/sqlite3ext.h 95575e0d175a0271fe2c3232c0d11e8720ed6887
-F src/sqliteInt.h db7e6fafc4c2d02eaebdd1c94aa235f0b5cc1892
+F src/sqliteInt.h 81183ae71162818bf60478e738ff68604128bb06
F src/sqliteLimit.h f14609c27636ebc217c9603ade26dbdd7d0f6afa
F src/table.c a8de75bcedf84d4060d804264b067ab3b1a3561d
F src/tclsqlite.c 4bffe56752d2c24ade23340e46a91fd92c316e08
@@ -146,7 +146,7 @@
F src/vdbeblob.c 96f3572fdc45eda5be06e6372b612bc30742d9f0
F src/vdbefifo.c 3ca8049c561d5d67cbcb94dc909ae9bb68c0bf8f
F src/vdbemem.c ca4d3994507cb0a9504820293af69f5c778b4abd
-F src/vtab.c 51d43cda45d25e6f3a15d19fe32992b7756e74db
+F src/vtab.c 60dc6d881c3049ec0e9f780e6beb953dbd78673d
F src/where.c 12387641659605318ae03d87f0687f223dfc9568
F tclinstaller.tcl 4356d9d94d2b5ed5e68f9f0c80c4df3048dd7617
F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
@@ -429,6 +429,7 @@
F test/vtab7.test 5f9ef9fb84733e928d5d0267c821072561b198d5
F test/vtab8.test e19fa4a538fcd1bb66c22825fa8f71618fb13583
F test/vtab9.test ea58d2b95d61955f87226381716b2d0b1d4e4f9b
+F test/vtabA.test 9cb6b1afead6fdd91bbdf1ca65c44ccfd9b10936
F test/vtab_err.test 9eabf98b26838fed8bac4aea986580be0a2bd52e
F test/vtab_shared.test d631d1f820c38c18939d53aab1fc35db5f0a8094
F test/where.test 1bcde8984c63747ac6d6bafcacd20fd6e8a223de
@@ -515,7 +516,7 @@
F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0
F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b
F www/whentouse.tcl fc46eae081251c3c181bd79c5faef8195d7991a5
-P 474a52347d454ad499d7a78c88eb995c9d3254d1
-R b2bdfee21240066b385b15bc59a27391
-U drh
-Z 5937892d2d6652ad5bd5b6e488145ae0
+P 4a7079a19ab5c36ed88cf597c21bf9f8b63bdf86
+R 1e94a93b6821f455c11a54099eee8bff
+U danielk1977
+Z 9b735fe91e23f76676dae3e37a5e71b2
diff --git a/manifest.uuid b/manifest.uuid
index 0b90ea9..8a490da 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-4a7079a19ab5c36ed88cf597c21bf9f8b63bdf86
\ No newline at end of file
+7f887a6a00fdd6efc3740bd8a1731bb1abde087c
\ No newline at end of file
diff --git a/src/insert.c b/src/insert.c
index f18f55c..4daa339 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
-** $Id: insert.c,v 1.186 2007/05/04 13:15:56 drh Exp $
+** $Id: insert.c,v 1.187 2007/06/26 10:38:55 danielk1977 Exp $
*/
#include "sqliteInt.h"
@@ -349,6 +349,8 @@
int appendFlag = 0; /* True if the insert is likely to be an append */
int iDb;
+ int nHidden = 0;
+
#ifndef SQLITE_OMIT_TRIGGER
int isView; /* True if attempting to insert into a view */
int triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
@@ -525,7 +527,12 @@
/* Make sure the number of columns in the source data matches the number
** of columns to be inserted into the table.
*/
- if( pColumn==0 && nColumn && nColumn!=pTab->nCol ){
+ if( IsVirtual(pTab) ){
+ for(i=0; i<pTab->nCol; i++){
+ nHidden += (IsHiddenColumn(&pTab->aCol[i]) ? 1 : 0);
+ }
+ }
+ if( pColumn==0 && nColumn && nColumn!=(pTab->nCol-nHidden) ){
sqlite3ErrorMsg(pParse,
"table %S has %d columns but %d values were supplied",
pTabList, 0, pTab->nCol, nColumn);
@@ -640,6 +647,11 @@
sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
}
+ /* Cannot have triggers on a virtual table. If it were possible,
+ ** this block would have to account for hidden column.
+ */
+ assert(!IsVirtual(pTab));
+
/* Create the new column data
*/
for(i=0; i<pTab->nCol; i++){
@@ -732,6 +744,7 @@
/* Push onto the stack, data for all columns of the new entry, beginning
** with the first column.
*/
+ nHidden = 0;
for(i=0; i<pTab->nCol; i++){
if( i==pTab->iPKey ){
/* The value of the INTEGER PRIMARY KEY column is always a NULL.
@@ -742,13 +755,19 @@
continue;
}
if( pColumn==0 ){
- j = i;
+ if( IsHiddenColumn(&pTab->aCol[i]) ){
+ assert( IsVirtual(pTab) );
+ j = -1;
+ nHidden++;
+ }else{
+ j = i - nHidden;
+ }
}else{
for(j=0; j<pColumn->nId; j++){
if( pColumn->a[j].idx==i ) break;
}
}
- if( nColumn==0 || (pColumn && j>=pColumn->nId) ){
+ if( j<0 || nColumn==0 || (pColumn && j>=pColumn->nId) ){
sqlite3ExprCode(pParse, pTab->aCol[i].pDflt);
}else if( useTempTable ){
sqlite3VdbeAddOp(v, OP_Column, srcTab, j);
diff --git a/src/pragma.c b/src/pragma.c
index f6c1aa2..9a9dd30 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -11,7 +11,7 @@
*************************************************************************
** This file contains code used to implement the PRAGMA command.
**
-** $Id: pragma.c,v 1.141 2007/06/25 08:16:58 danielk1977 Exp $
+** $Id: pragma.c,v 1.142 2007/06/26 10:38:55 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
@@ -627,6 +627,7 @@
pTab = sqlite3FindTable(db, zRight, zDb);
if( pTab ){
int i;
+ int nHidden = 0;
Column *pCol;
sqlite3VdbeSetNumCols(v, 6);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "cid", P3_STATIC);
@@ -638,7 +639,11 @@
sqlite3ViewGetColumnNames(pParse, pTab);
for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
const Token *pDflt;
- sqlite3VdbeAddOp(v, OP_Integer, i, 0);
+ if( IsHiddenColumn(pCol) ){
+ nHidden++;
+ continue;
+ }
+ sqlite3VdbeAddOp(v, OP_Integer, i-nHidden, 0);
sqlite3VdbeOp3(v, OP_String8, 0, 0, pCol->zName, 0);
sqlite3VdbeOp3(v, OP_String8, 0, 0,
pCol->zType ? pCol->zType : "", 0);
diff --git a/src/select.c b/src/select.c
index dae6f93..4b78d9e 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
-** $Id: select.c,v 1.352 2007/06/24 06:32:18 danielk1977 Exp $
+** $Id: select.c,v 1.353 2007/06/26 10:38:55 danielk1977 Exp $
*/
#include "sqliteInt.h"
@@ -1334,6 +1334,15 @@
Expr *pExpr, *pRight;
char *zName = pTab->aCol[j].zName;
+ /* If a column is marked as 'hidden' (currently only possible
+ ** for virtual tables), do not include it in the expanded
+ ** result-set list.
+ */
+ if( IsHiddenColumn(&pTab->aCol[j]) ){
+ assert(IsVirtual(pTab));
+ continue;
+ }
+
if( i>0 ){
struct SrcList_item *pLeft = &pTabList->a[i-1];
if( (pLeft[1].jointype & JT_NATURAL)!=0 &&
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 1f4a3ab..9bc0f32 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.577 2007/06/26 00:37:28 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.578 2007/06/26 10:38:55 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_
@@ -606,6 +606,9 @@
u8 notNull; /* True if there is a NOT NULL constraint */
u8 isPrimKey; /* True if this column is part of the PRIMARY KEY */
char affinity; /* One of the SQLITE_AFF_... values */
+#ifndef SQLITE_OMIT_VIRTUALTABLE
+ u8 isHidden; /* True if this column is 'hidden' */
+#endif
};
/*
@@ -744,9 +747,11 @@
** table support is omitted from the build.
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
-# define IsVirtual(X) ((X)->isVirtual)
+# define IsVirtual(X) ((X)->isVirtual)
+# define IsHiddenColumn(X) ((X)->isHidden)
#else
-# define IsVirtual(X) 0
+# define IsVirtual(X) 0
+# define IsHiddenColumn(X) 0
#endif
/*
diff --git a/src/vtab.c b/src/vtab.c
index 5b9fbc4..611725d 100644
--- a/src/vtab.c
+++ b/src/vtab.c
@@ -11,7 +11,7 @@
*************************************************************************
** This file contains code used to help implement virtual tables.
**
-** $Id: vtab.c,v 1.47 2007/06/22 15:21:16 danielk1977 Exp $
+** $Id: vtab.c,v 1.48 2007/06/26 10:38:55 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_VIRTUALTABLE
#include "sqliteInt.h"
@@ -376,6 +376,44 @@
}
db->pVTab = 0;
sqliteFree(zModuleName);
+
+ /* If everything went according to plan, loop through the columns
+ ** of the table to see if any of them contain the token "hidden".
+ ** If so, set the Column.isHidden flag and remove the token from
+ ** the type string.
+ */
+ if( rc==SQLITE_OK ){
+ int iCol;
+ for(iCol=0; iCol<pTab->nCol; iCol++){
+ char *zType = pTab->aCol[iCol].zType;
+ int nType;
+ int i = 0;
+ if( !zType ) continue;
+ nType = strlen(zType);
+ if( sqlite3StrNICmp("hidden", zType, 6) || (zType[6] && zType[6]!=' ') ){
+ for(i=0; i<nType; i++){
+ if( (0==sqlite3StrNICmp(" hidden", &zType[i], 7))
+ && (zType[i+7]=='\0' || zType[i+7]==' ')
+ ){
+ i++;
+ break;
+ }
+ }
+ }
+ if( i<nType ){
+ int j;
+ int nDel = 6 + (zType[i+6] ? 1 : 0);
+ for(j=i; (j+nDel)<=nType; j++){
+ zType[j] = zType[j+nDel];
+ }
+ if( zType[i]=='\0' && i>0 ){
+ assert(zType[i-1]==' ');
+ zType[i-1] = '\0';
+ }
+ pTab->aCol[iCol].isHidden = 1;
+ }
+ }
+ }
return rc;
}
diff --git a/test/vtabA.test b/test/vtabA.test
new file mode 100644
index 0000000..bd9e551
--- /dev/null
+++ b/test/vtabA.test
@@ -0,0 +1,135 @@
+# 2007 June 26
+#
+# 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 file is 'hidden' virtual table columns.
+#
+# $Id: vtabA.test,v 1.1 2007/06/26 10:38:54 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !vtab {
+ finish_test
+ return
+}
+
+proc get_decltype {table col} {
+ set STMT [sqlite3_prepare $::DB "SELECT $col FROM $table" -1 TAIL]
+ set decltype [sqlite3_column_decltype $STMT 0]
+ sqlite3_finalize $STMT
+ set decltype
+}
+
+proc get_collist {table} {
+ set ret [list]
+ db eval "PRAGMA table_info($table)" { lappend ret $name }
+ set ret
+}
+
+# Register the echo module
+register_echo_module [sqlite3_connection_pointer db]
+
+# Create a virtual table with a 'hidden' column (column b).
+#
+do_test vtabA-1.1 {
+ execsql { CREATE TABLE t1(a, b HIDDEN VARCHAR, c INTEGER) }
+} {}
+do_test vtabA-1.2 {
+ execsql { CREATE VIRTUAL TABLE t1e USING echo(t1) }
+} {}
+
+# Test that the hidden column is not listed by [PRAGMA table_info].
+#
+do_test vtabA-1.3 {
+ execsql { PRAGMA table_info(t1e) }
+} [list \
+ 0 a {} 0 {} 0 \
+ 1 c INTEGER 0 {} 0 \
+]
+
+# Test that the hidden column is not require in the default column
+# list for an INSERT statement.
+#
+do_test vtabA-1.4 {
+ catchsql {
+ INSERT INTO t1e VALUES('value a', 'value c');
+ }
+} {0 {}}
+do_test vtabA-1.5 {
+ execsql {
+ SELECT a, b, c FROM t1e;
+ }
+} {{value a} {} {value c}}
+
+do_test vtabA-1.6 {
+ execsql {
+ SELECT * FROM t1e;
+ }
+} {{value a} {value c}}
+
+# Test that the expansion of a '*' expression in the result set of
+# a SELECT does not include the hidden column.
+#
+do_test vtabA-1.7 {
+ execsql {
+ INSERT INTO t1e SELECT * FROM t1e;
+ }
+} {}
+do_test vtabA-1.8 {
+ execsql {
+ SELECT * FROM t1e;
+ }
+} {{value a} {value c} {value a} {value c}}
+
+# Test that the declaration type of the hidden column does not include
+# the token "HIDDEN".
+#
+do_test vtabA-1.9 {
+ get_decltype t1e b
+} {VARCHAR}
+do_test vtabA-1.10 {
+ get_collist t1e
+} {a c}
+
+#----------------------------------------------------------------------
+# These tests vtabA-2.* concentrate on testing that the HIDDEN token
+# is detected and handled correctly in various declarations.
+#
+proc analyse_parse {columns decltype_list} {
+ db eval { DROP TABLE IF EXISTS t1e; }
+ db eval { DROP TABLE IF EXISTS t1; }
+ db eval " CREATE TABLE t1 $columns "
+ db eval { CREATE VIRTUAL TABLE t1e USING echo(t1) }
+ set ret [list [get_collist t1e]]
+ foreach c $decltype_list {
+ lappend ret [get_decltype t1e $c]
+ }
+ set ret
+}
+
+do_test vtabA-2.1 {
+ analyse_parse {(a text, b integer hidden, c hidden)} {a b c}
+} {a text integer {}}
+
+do_test vtabA-2.2 {
+ analyse_parse {(a hidden , b integerhidden, c hidden1)} {a b c}
+} {{b c} {} integerhidden hidden1}
+
+do_test vtabA-2.3 {
+ analyse_parse {(a HiDden, b HIDDEN, c hidden)} {a b c}
+} {{} {} {} {}}
+
+do_test vtabA-2.4 {
+ analyse_parse {(a whatelse can i hidden test, b HIDDEN hidden)} {a b}
+} {{} {whatelse can i test} hidden}
+
+finish_test
+