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
