On Mon, Nov 22, 2010 at 01:29:29PM -0500, Igor Tandetnik scratched on the wall:
> Duquette, William H (316H) <[email protected]> wrote:
> > PRAGMA table_info(my_table) returns a row for each column in my_table. Is
> > it possible to do selects on this result set?
>
> No.
>
> > Or do
> > you simply have to loop over it, and pull out what you need.
>
> Yes.
Below is code for the "superview" virtual table module. It allows
*any* SQL statement to be turned into a read-only virtual table--
including PRAGMA statements. In theory, you can issue the commnad:
CREATE VIRTUAL TABLE my_table_info
USING superview( PRAGMA table_info(my_table) );
And then simply SELECT * FROM my_table_info WHERE...;
I say "in theory" because I haven't looked at this code for some
months. I have no idea if it still compiles correctly, or if the
comments are correct. This was put together as a possible example
for the O'Reilly book "Using SQLite." In the end, it was decided
this example was a bit too complex, and a simpler example was used.
If someone wants to take this over, host it somewhere, and keep it
current, please have at it.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
==superview.c=starts=here===========================================
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1;
#include <stdlib.h>
/**************************************************************************
***************************************************************************
***************************************************************************
"superview" virtual table by Jay A. Kreibich
***************************************************************************
THIS SOFTWARE IS PROVIDED ''AS IS'' AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
The author or authors of this code dedicate any and all copyright
interest in this code to the public domain. We make this dedication
for the benefit of the public at large and to the detriment of our
heirs and successors. We intend this dedication to be an overt act
of relinquishment in perpetuity of all present and future rights to
this code under copyright law.
***************************************************************************
*** TO COMPILE:
* Windows (Visual Studio CLI):
$ cl /c superview.c
$ link /dll /out:superview.sqlite3ext /export:superview_init superview.obj
* Mac OS X:
$ gcc -c superview.c
$ ld -dylib -o superview.sqlite3ext superview.o
* Linux:
$ gcc -c superview.c
$ ld -shared -o superview.sqlite3ext superview.o
*** TO LOAD (sqlite3):
sqlite> .load superview.sqlite3ext superview_init
*** TO USE:
sqlite> CREATE VIRTUAL TABLE <tblname> USING superview ( <sqlcommand> );
***************************************************************************
***************************************************************************
**************************************************************************/
typedef struct sview_vtab_s {
sqlite3_vtab vtab; /* this must go first */
sqlite3 *db; /* module specific fields then follow */
char *cmd; /* SQL command */
} sview_vtab;
typedef struct sview_cursor_s {
sqlite3_vtab_cursor cur; /* this must go first */
sqlite3_stmt *stmt; /* statement */
sqlite3_int64 rowid; /* virtual row id */
int eof; /* EOF flag */
} sview_cursor;
static int sview_get_row( sview_cursor *svc )
{
int rc;
if ( svc->eof ) return SQLITE_OK;
rc = sqlite3_step( svc->stmt );
if ( rc == SQLITE_ROW ) {
svc->rowid++;
return SQLITE_OK; /* we have a valid row */
}
sqlite3_reset( svc->stmt );
svc->eof = 1;
return ( rc == SQLITE_DONE ? SQLITE_OK : rc ); /* DONE -> OK */
}
static int sview_connect( sqlite3 *db, void *udp, int argc,
const char *const *argv, sqlite3_vtab **vtab, char **errmsg )
{
sview_vtab *svt = NULL;
int ct, rc;
char *table_sql;
int table_cols = 0;
sqlite3_stmt *stmt = NULL;
*vtab = NULL;
*errmsg = NULL;
if ( argc == 3 ) return SQLITE_ERROR;
svt = sqlite3_malloc( sizeof( sview_vtab ) );
*vtab = (sqlite3_vtab*)svt;
if ( svt == NULL ) return SQLITE_NOMEM;
(*vtab)->zErrMsg = NULL;
svt->db = db;
svt->cmd = sqlite3_mprintf( "%s", argv[3] );
for ( ct=4; ct < argc; ct++ ) {
svt->cmd = sqlite3_mprintf( "%z,%s", svt->cmd, argv[ct] );
}
rc = sqlite3_prepare_v2( svt->db, svt->cmd, -1, &stmt, NULL );
if ( rc != SQLITE_OK ) {
sqlite3_finalize( stmt );
sqlite3_free( svt->cmd );
sqlite3_free( svt );
*vtab = NULL;
return SQLITE_ERROR;
}
table_cols = sqlite3_column_count( stmt );
if ( table_cols == 0 ) {
sqlite3_finalize( stmt );
sqlite3_free( svt->cmd );
sqlite3_free( svt );
*vtab = NULL;
return SQLITE_ERROR;
}
table_sql = sqlite3_mprintf( "CREATE TABLE superview ( " );
for ( ct=0; ct < table_cols; ct++ ) {
sqlite3_column_name( stmt, ct );
table_sql = sqlite3_mprintf( "%z %s%s",
table_sql, sqlite3_column_name( stmt, ct ),
(ct + 1 != table_cols) ? "," : "" );
}
table_sql = sqlite3_mprintf( "%z )", table_sql );
sqlite3_finalize( stmt );
if ( sqlite3_declare_vtab( db, table_sql ) != SQLITE_OK ) {
sqlite3_free( svt->cmd );
sqlite3_free( svt );
*vtab = NULL;
return SQLITE_ERROR;
}
return SQLITE_OK;
}
static int sview_disconnect( sqlite3_vtab *vtab )
{
sview_vtab *svt = (sview_vtab*)vtab;
if ( svt != NULL ) {
sqlite3_free( svt->cmd );
sqlite3_free( svt );
}
return SQLITE_OK;
}
static int sview_bestindex( sqlite3_vtab *vtab, sqlite3_index_info *info )
{
return SQLITE_OK;
}
/***/
static int sview_open( sqlite3_vtab *vtab, sqlite3_vtab_cursor **cur )
{
sview_vtab *svt = (sview_vtab*)vtab;
sview_cursor *svc = NULL;
int rc = 0;
svc = sqlite3_malloc( sizeof( sview_cursor ) );
*cur = (sqlite3_vtab_cursor*)svc;
if ( svc == NULL ) return SQLITE_NOMEM;
rc = sqlite3_prepare_v2( svt->db, svt->cmd, -1, &svc->stmt, NULL );
if ( rc != SQLITE_OK ) {
*cur = NULL;
sqlite3_free( svc );
return rc;
}
return SQLITE_OK;
}
static int sview_close( sqlite3_vtab_cursor *cur )
{
sqlite3_finalize( ((sview_cursor*)cur)->stmt );
sqlite3_free( cur );
return SQLITE_OK;
}
static int sview_filter( sqlite3_vtab_cursor *cur,
int idxnum, const char *idxstr,
int argc, sqlite3_value **value )
{
sview_cursor *svc = (sview_cursor*)cur;
int rc = 0;
rc = sqlite3_reset( svc->stmt ); /* start a new scan */
if ( rc != SQLITE_OK ) return rc;
svc->eof = 0; /* clear EOF flag */
svc->rowid = 0; /* reset rowid */
sview_get_row( svc ); /* fetch first row */
return SQLITE_OK;
}
static int sview_next( sqlite3_vtab_cursor *cur )
{
return sview_get_row( (sview_cursor*)cur );
}
static int sview_eof( sqlite3_vtab_cursor *cur )
{
return ((sview_cursor*)cur)->eof;
}
static int sview_column( sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int
cidx )
{
sqlite3_result_value( ctx,
sqlite3_column_value( ((sview_cursor*)cur)->stmt, cidx ) );
return SQLITE_OK;
}
static int sview_rowid( sqlite3_vtab_cursor *cur, sqlite3_int64 *rowid )
{
*rowid = ((sview_cursor*)cur)->rowid;
return SQLITE_OK;
}
static int sview_rename( sqlite3_vtab *vtab, const char *newname )
{
return SQLITE_OK;
}
static sqlite3_module sview_mod = {
1, /* iVersion */
sview_connect, /* xCreate() */
sview_connect, /* xConnect() */
sview_bestindex, /* xBestIndex() */
sview_disconnect, /* xDisconnect() */
sview_disconnect, /* xDestroy() */
sview_open, /* xOpen() */
sview_close, /* xClose() */
sview_filter, /* xFilter() */
sview_next, /* xNext() */
sview_eof, /* xEof() */
sview_column, /* xColumn() */
sview_rowid, /* xRowid() */
NULL, /* xUpdate() */
NULL, /* xBegin() */
NULL, /* xSync() */
NULL, /* xCommit() */
NULL, /* xRollback() */
NULL, /* xFindFunction() */
sview_rename /* xRename() */
};
int superview_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
{
int rc;
SQLITE_EXTENSION_INIT2(api);
rc = sqlite3_create_module( db, "superview", &sview_mod, NULL );
return rc;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
