Hi Folks, I need to know what columns of my virtual table implementation are used for a particular query or not. The computation of the columns is expensive and cannot be deferred until the call to xColumn(). Thus I need to know before the first row is fetched so that I can be efficient in my operation of the virtual table.
I have found a solution that works for me, but it is a minor breaking change for some of the existing virtual table implementations. I have modified the vdbe.c engine so that after opening the virtual table (OP_VOpen) that it scan the current microcode for columns (OP_VColumn) used by that cursor. I then call xColumn() with the pContext set to NULL (that's the breaking change). The "NULL" indicates to my implementation that the column will be used in the query that is about to happen. In my xOpen() routine I initialize things to keep track of what is used and then identify which columns are used through the call to xColumn(). For existing implementations the required change would be to test pContext and return immediately. My questions for the group are: Is this a reasonable extension / feature to add to virtual table implementations? I need it, but would this help others as well? Would surrounding this new code with a PRAGMA option help with respect to the "breaking change". These interfaces are experimental, but are being used by many. The amount of time used to find these opcodes is small, so I do not think this is an efficiency issue. Rather it is a way to gently introduce this concept. Is there another way to do this that is not a breaking change? Comments? Questions? Thanks, -Allan The patch for the modification against 3.6.18 release vdbe.c that implements the above feature is below. This code is added *after* all the work for OP_VOpen has been completed. I place this code in the public domain under the same license as SQLITE3. diff -r -u sqlite3/src/vdbe.c roqlite3/src/vdbe.c --- sqlite3/src/vdbe.c 2009-09-28 13:34:11.000000000 -0600 +++ new/src/vdbe.c 2009-09-28 13:34:13.000000000 -0600 @@ -5173,6 +5173,18 @@ pModule->xClose(pVtabCursor); } } + if( SQLITE_OK==rc ){ + // Help virtual table module with knowledge of which columns are used + int i ; + + // Scan all opcodes for references to virtual columns associated with this cursor + for(i=0; i<p->nOp; i++){ + Op *anOp = &p->aOp[i]; + if ( anOp->opcode == OP_VColumn && pOp->p1 == anOp->p1 ) { + rc = pModule->xColumn(pVtabCursor, NULL, anOp->p2); + } + } + } break; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users