Index: Makefile.in ================================================================== --- Makefile.in +++ Makefile.in @@ -1010,10 +1010,11 @@ $(TOP)/src/shell.c.in \ $(TOP)/ext/misc/appendvfs.c \ $(TOP)/ext/misc/shathree.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/completion.c \ + $(TOP)/ext/misc/bindvtab.c \ $(TOP)/ext/misc/sqlar.c \ $(TOP)/ext/expert/sqlite3expert.c \ $(TOP)/ext/expert/sqlite3expert.h \ $(TOP)/ext/misc/zipfile.c \ $(TOP)/src/test_windirent.c Index: Makefile.msc ================================================================== --- Makefile.msc +++ Makefile.msc @@ -2097,10 +2097,11 @@ $(TOP)\src\shell.c.in \ $(TOP)\ext\misc\appendvfs.c \ $(TOP)\ext\misc\shathree.c \ $(TOP)\ext\misc\fileio.c \ $(TOP)\ext\misc\completion.c \ + $(TOP)\ext\misc\bindvtab.c \ $(TOP)\ext\expert\sqlite3expert.c \ $(TOP)\ext\expert\sqlite3expert.h \ $(TOP)\src\test_windirent.c # If use of zlib is enabled, add the "zipfile.c" source file. ADDED ext/misc/bindvtab.c Index: ext/misc/bindvtab.c ================================================================== --- /dev/null +++ ext/misc/bindvtab.c @@ -0,0 +1,476 @@ +/* +** 2018-04-27 +** +** 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 a simple key/value store used to hold bind +** parameters for SQLite. The key/value store is a singleton - there +** is exactly one per process. The store can be accessed and controlled +** from SQLite using an eponymous virtual table. +** +** This is used to do parameter binding in the command-line shell. +** +** The ".set key=value" command and the "-Dkey=value" command-line option +** invoke shell_bindings_new_text() on the argument ("key=value") in order +** to create entries in the store. The CLI then invokes +** shell_bindings_apply() on each prepared statement just prior to +** running it. +** +** All bindings are accessible through an eponymous-only virtual table +** named shell_bindings. Ex: +** +** INSERT INTO shell_bindings(k,v) VALUES('p1',12345); +** SELECT $p1, typeof($p1); +** +** The above results in an answer of 12345,'integer'. Bindings generated +** using the virtual table can have any type other than NULL. But bindings +** generated by the .set command and the -D command-line option are always +** text. +** +** The CLI is single-threaded, so there is no attempt to make this code +** threadsafe. +** +** The key/value store is kept in a global list, and uses malloc/free rather +** than sqlite3_malloc64/sqlite3_free so that it can be completely independent +** of SQLite, can exist both before sqlite3_initialize() and after +** sqlite3_shutdown(), and so that it will persist across multiple +** connections created using ".open". +** +** The number of parameters is expected to be small, so they are stored +** on a simple linked list. If this proves to be too inefficient, some other +** algorithm can be substituted in the future without changing the interface. +*/ +#if !defined(SQLITEINT_H) +#include "sqlite3ext.h" +#endif +SQLITE_EXTENSION_INIT1 +#include +#include +#include + +/* Each entry in the key/value store */ +typedef struct BindingEntry BindingEntry; +struct BindingEntry { + char *zKey; /* Key */ + BindingEntry *pNext; /* Next entry in the list */ + BindingEntry *pPrev; /* Previous entry in the list */ + int eType; /* SQLITE_INTEGER, _FLOAT, _TEXT, or _BLOB */ + int len; /* Length for SQLITE_BLOB values */ + union { + sqlite3_int64 i; /* Integer value */ + double r; /* Real value */ + char *z; /* Text value */ + unsigned char *b; /* Blob value */ + } u; +}; + +/* Global list of all entries */ +static BindingEntry *global_pAll = 0; + +/* Locate any entry with the given key. Return NULL if not found. +*/ +static BindingEntry *shellBindingFind(const char *zKey){ + BindingEntry *p; + for(p=global_pAll; p && strcmp(p->zKey,zKey)!=0; p = p->pNext){} + return p; +} + +/* Delete any entry with the given key, if it exists. +*/ +static void shellBindingDelete(const char *zKey){ + BindingEntry *p; + p = shellBindingFind(zKey); + if( p ){ + if( p->pNext ){ + p->pNext->pPrev = p->pPrev; + } + if( p->pPrev ){ + p->pPrev->pNext = p->pNext; + }else{ + global_pAll = p->pNext; + } + free(p); + } +} + +/* Insert a new shell binding */ +static void shellBindingInsert(BindingEntry *p){ + p->pNext = global_pAll; + if( global_pAll ) global_pAll->pPrev = p; + global_pAll = p; + p->pPrev = 0; +} + +/* +** True if c is a valid ID character. +*/ +static int shellBindIdChar(char c){ + if( c>='a' && c<='z' ) return 1; + if( c>='A' && c<='Z' ) return 1; + if( c=='_' ) return 1; + if( c>='0' && c<='9' ) return 2; + return 0; +} + +/* Create a new binding given a string of the form "KEY=VALUE". Return +** values: +** +** 0: success +** 1: out of memory +** 2: Argument is not a valid KEY=VALUE string +** +** The type of VALUE is TEXT. +*/ +int shell_bindings_new_text(const char *z){ + int i; + int nKey; + int nData; + BindingEntry *p; + for(i=0; shellBindIdChar(z[i]); i++){} + if( i==0 ) return 2; + if( shellBindIdChar(z[0])==2 ) return 2; + nKey = i; + if( z[i]!='=' ) return 2; + for(nData=0; z[nKey+1+nData]; nData++){} + p = malloc( sizeof(*p) + nKey + nData + 2 ); + if( p==0 ) return 1; + memset(p, 0, sizeof(*p)); + p->zKey = (char*)&p[1]; + memcpy(p->zKey, z, nKey); + p->zKey[nKey] = 0; + p->u.z = &p->zKey[nKey+1]; + p->len = nData; + p->eType = SQLITE_TEXT; + memcpy(p->u.z, &z[nKey+1], nData+1); + shellBindingDelete(p->zKey); + shellBindingInsert(p); + return 0; +} + +/* +** Delete all shell bindings +*/ +void shell_bindings_clear(void){ + BindingEntry *pNext; + while( global_pAll ){ + pNext = global_pAll->pNext; + free(global_pAll); + global_pAll = pNext; + } +} + +/* Given a prepared statement, apply all bindings for which there are +** known values in the k-v store +*/ +void shell_bindings_apply(sqlite3_stmt *pStmt){ + int n = sqlite3_bind_parameter_count(pStmt); + int i; + BindingEntry *p; + for(i=1; i<=n; i++){ + const char *zKey = sqlite3_bind_parameter_name(pStmt, i); + if( zKey==0 || zKey[0]==0 ) continue; + zKey++; + p = shellBindingFind(zKey); + if( p==0 ) continue; + switch( p->eType ){ + case SQLITE_INTEGER: + sqlite3_bind_int64(pStmt, i, p->u.i); + break; + case SQLITE_FLOAT: + sqlite3_bind_double(pStmt, i, p->u.r); + break; + case SQLITE_TEXT: + sqlite3_bind_text(pStmt, i, p->u.z, p->len, SQLITE_TRANSIENT); + break; + case SQLITE_BLOB: + sqlite3_bind_blob(pStmt, i, p->u.b, p->len, SQLITE_TRANSIENT); + break; + } + } +} + +/* bindvtab_vtab is a subclass of sqlite3_vtab which is +** underlying representation of the virtual table +*/ +typedef struct bindvtab_vtab bindvtab_vtab; +struct bindvtab_vtab { + sqlite3_vtab base; /* Base class - must be first */ +}; + +/* bindvtab_cursor is a subclass of sqlite3_vtab_cursor which will +** serve as the underlying representation of a cursor that scans +** over rows of the result +*/ +typedef struct bindvtab_cursor bindvtab_cursor; +struct bindvtab_cursor { + sqlite3_vtab_cursor base; /* Base class - must be first */ + BindingEntry *p; /* Current entry in the scan */ +}; + +/* +** The bindvtabConnect() method is invoked to create a new +** template virtual table. +** +** Think of this routine as the constructor for bindvtab_vtab objects. +** +** All this routine needs to do is: +** +** (1) Allocate the bindvtab_vtab object and initialize all fields. +** +** (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the +** result set of queries against the virtual table will look like. +*/ +static int bindvtabConnect( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + bindvtab_vtab *pNew; + int rc; + + rc = sqlite3_declare_vtab(db, + "CREATE TABLE shell_bindings(k TEXT PRIMARY KEY,v)" + " WITHOUT ROWID" + ); + /* For convenience, define symbolic names for the index to each column. */ +#define BINDVTAB_KEY 0 +#define BINDVTAB_VALUE 1 + if( rc==SQLITE_OK ){ + pNew = sqlite3_malloc( sizeof(*pNew) ); + *ppVtab = (sqlite3_vtab*)pNew; + if( pNew==0 ) return SQLITE_NOMEM; + memset(pNew, 0, sizeof(*pNew)); + } + return rc; +} + +/* +** This method is the destructor for bindvtab_vtab objects. +*/ +static int bindvtabDisconnect(sqlite3_vtab *pVtab){ + bindvtab_vtab *p = (bindvtab_vtab*)pVtab; + sqlite3_free(p); + return SQLITE_OK; +} + +/* +** Constructor for a new bindvtab_cursor object. +*/ +static int bindvtabOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){ + bindvtab_cursor *pCur; + pCur = sqlite3_malloc( sizeof(*pCur) ); + if( pCur==0 ) return SQLITE_NOMEM; + memset(pCur, 0, sizeof(*pCur)); + *ppCursor = &pCur->base; + return SQLITE_OK; +} + +/* +** Destructor for a bindvtab_cursor. +*/ +static int bindvtabClose(sqlite3_vtab_cursor *cur){ + bindvtab_cursor *pCur = (bindvtab_cursor*)cur; + sqlite3_free(pCur); + return SQLITE_OK; +} + + +/* +** Advance a bindvtab_cursor to its next row of output. +*/ +static int bindvtabNext(sqlite3_vtab_cursor *cur){ + bindvtab_cursor *pCur = (bindvtab_cursor*)cur; + pCur->p = pCur->p->pNext; + return SQLITE_OK; +} + +/* +** Return values of columns for the row at which the bindvtab_cursor +** is currently pointing. +*/ +static int bindvtabColumn( + sqlite3_vtab_cursor *cur, /* The cursor */ + sqlite3_context *ctx, /* First argument to sqlite3_result_...() */ + int i /* Which column to return */ +){ + bindvtab_cursor *pCur = (bindvtab_cursor*)cur; + BindingEntry *p = pCur->p; + if( i==BINDVTAB_KEY ){ + sqlite3_result_text(ctx, p->zKey, -1, SQLITE_TRANSIENT); + }else{ + assert( i==BINDVTAB_VALUE ); + switch( p->eType ){ + case SQLITE_INTEGER: + sqlite3_result_int(ctx, p->u.i); + break; + case SQLITE_FLOAT: + sqlite3_result_double(ctx, p->u.r); + break; + case SQLITE_TEXT: + sqlite3_result_text(ctx, p->u.z, p->len, SQLITE_TRANSIENT); + break; + case SQLITE_BLOB: + sqlite3_result_blob(ctx, p->u.b, p->len, SQLITE_TRANSIENT); + break; + } + } + return SQLITE_OK; +} + +/* +** Return the rowid for the current row. In this implementation, the +** rowid is the same as the output value. +*/ +static int bindvtabRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ + return SQLITE_OK; +} + +/* +** Return TRUE if the cursor has been moved off of the last +** row of output. +*/ +static int bindvtabEof(sqlite3_vtab_cursor *cur){ + bindvtab_cursor *pCur = (bindvtab_cursor*)cur; + return pCur->p==0; +} + +/* +** This method is called to "rewind" the bindvtab_cursor object back +** to the first row of output. This method is always called at least +** once prior to any call to bindvtabColumn() or bindvtabRowid() or +** bindvtabEof(). +*/ +static int bindvtabFilter( + sqlite3_vtab_cursor *pVtabCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + bindvtab_cursor *pCur = (bindvtab_cursor *)pVtabCursor; + pCur->p = global_pAll; + return SQLITE_OK; +} + +/* +** SQLite will invoke this method one or more times while planning a query +** that uses the virtual table. This routine needs to create +** a query plan for each invocation and compute an estimated cost for that +** plan. +*/ +static int bindvtabBestIndex( + sqlite3_vtab *tab, + sqlite3_index_info *pIdxInfo +){ + pIdxInfo->estimatedCost = (double)10; + pIdxInfo->estimatedRows = 10; + return SQLITE_OK; +} + +/* +** Called to make changes to the shell bindings +*/ +static int bindvtabUpdate( + sqlite3_vtab *pVTab, + int argc, + sqlite3_value **argv, + sqlite_int64 *pRowid +){ + const char *zKey; + BindingEntry *p; + int nKey; + int len; + int eType; + if( sqlite3_value_type(argv[0])!=SQLITE_NULL ){ + zKey = (const char*)sqlite3_value_text(argv[0]); + if( zKey ) shellBindingDelete(zKey); + } + if( argc==1 ) return SQLITE_OK; + eType = sqlite3_value_type(argv[3]); + if( eType==SQLITE_NULL ) return SQLITE_OK; + zKey = (const char*)sqlite3_value_text(argv[2]); + if( zKey==0 ) return SQLITE_OK; + nKey = sqlite3_value_bytes(argv[2]); + shellBindingDelete(zKey); + if( eType==SQLITE_BLOB || eType==SQLITE_TEXT ){ + len = sqlite3_value_bytes(argv[3]); + }else{ + len = 0; + } + p = malloc( sizeof(*p) + nKey + len + 2 ); + if( p==0 ) return SQLITE_NOMEM; + memset(p, 0, sizeof(*p)); + p->zKey = (char*)&p[1]; + memcpy(p->zKey, zKey, nKey+1); + p->eType = eType; + switch( eType ){ + case SQLITE_INTEGER: + p->u.i = sqlite3_value_int64(argv[3]); + break; + case SQLITE_FLOAT: + p->u.r = sqlite3_value_double(argv[3]); + break; + case SQLITE_TEXT: + p->u.z = &p->zKey[nKey+1]; + memcpy(p->u.z, sqlite3_value_text(argv[3]), len); + break; + case SQLITE_BLOB: + p->u.b = (unsigned char*)&p->zKey[nKey+1]; + memcpy(p->u.b, sqlite3_value_blob(argv[3]), len); + break; + } + shellBindingInsert(p); + return SQLITE_OK; +} + +/* +** This following structure defines all the methods for the +** virtual table. +*/ +static sqlite3_module bindvtabModule = { + /* iVersion */ 0, + /* xCreate */ 0, + /* xConnect */ bindvtabConnect, + /* xBestIndex */ bindvtabBestIndex, + /* xDisconnect */ bindvtabDisconnect, + /* xDestroy */ 0, + /* xOpen */ bindvtabOpen, + /* xClose */ bindvtabClose, + /* xFilter */ bindvtabFilter, + /* xNext */ bindvtabNext, + /* xEof */ bindvtabEof, + /* xColumn */ bindvtabColumn, + /* xRowid */ bindvtabRowid, + /* xUpdate */ bindvtabUpdate, + /* xBegin */ 0, + /* xSync */ 0, + /* xCommit */ 0, + /* xRollback */ 0, + /* xFindMethod */ 0, + /* xRename */ 0, + /* xSavepoint */ 0, + /* xRelease */ 0, + /* xRollbackTo */ 0 +}; + + +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_bindvtab_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + int rc = SQLITE_OK; + SQLITE_EXTENSION_INIT2(pApi); + rc = sqlite3_create_module(db, "shell_bindings", &bindvtabModule, 0); + return rc; +} Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -705,10 +705,11 @@ $(TOP)/src/shell.c.in \ $(TOP)/ext/misc/appendvfs.c \ $(TOP)/ext/misc/shathree.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/completion.c \ + $(TOP)/ext/misc/bindvtab.c \ $(TOP)/ext/misc/sqlar.c \ $(TOP)/ext/expert/sqlite3expert.c \ $(TOP)/ext/expert/sqlite3expert.h \ $(TOP)/ext/misc/zipfile.c \ $(TOP)/src/test_windirent.c Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -938,10 +938,11 @@ #endif INCLUDE ../ext/misc/shathree.c INCLUDE ../ext/misc/fileio.c INCLUDE ../ext/misc/completion.c INCLUDE ../ext/misc/appendvfs.c +INCLUDE ../ext/misc/bindvtab.c #ifdef SQLITE_HAVE_ZLIB INCLUDE ../ext/misc/zipfile.c INCLUDE ../ext/misc/sqlar.c #endif INCLUDE ../ext/expert/sqlite3expert.h @@ -3005,10 +3006,11 @@ if( pArg->cMode==MODE_Explain ){ explain_data_prepare(pArg, pStmt); } } + shell_bindings_apply(pStmt); exec_prepared_stmt(pArg, pStmt); explain_data_delete(pArg); eqp_render(pArg); /* print usage stats if stats on */ @@ -3404,10 +3406,11 @@ ".separator COL ?ROW? Change the column separator and optionally the row\n" " separator for both the output mode and .import\n" #if defined(SQLITE_ENABLE_SESSION) ".session CMD ... Create or control sessions\n" #endif + ".set KEY=VALUE Set bind parameter KEY to be string VALUE\n" ".sha3sum ?OPTIONS...? Compute a SHA3 hash of database content\n" #ifndef SQLITE_NOHAVE_SYSTEM ".shell CMD ARGS... Run CMD ARGS... in a system shell\n" #endif ".show Show the current values for various settings\n" @@ -3616,10 +3619,11 @@ sqlite3_enable_load_extension(p->db, 1); #endif sqlite3_fileio_init(p->db, 0, 0); sqlite3_shathree_init(p->db, 0, 0); sqlite3_completion_init(p->db, 0, 0); + sqlite3_bindvtab_init(p->db, 0, 0); #ifdef SQLITE_HAVE_ZLIB sqlite3_zipfile_init(p->db, 0, 0); sqlite3_sqlar_init(p->db, 0, 0); #endif sqlite3_create_function(p->db, "shell_add_schema", 3, SQLITE_UTF8, 0, @@ -7145,10 +7149,34 @@ if( nArg>=3 ){ sqlite3_snprintf(sizeof(p->rowSeparator), p->rowSeparator, "%.*s", (int)ArraySize(p->rowSeparator)-1, azArg[2]); } }else + + if( c=='s' && n==3 && strncmp(azArg[0],"set",3)==0 ){ + int x; + char *zKey = 0; + char *zToFree = 0; + if( nArg==2 ){ + zKey = azArg[1]; + }else if( nArg==3 ){ + zKey = zToFree = sqlite3_mprintf("%s=%s",azArg[1],azArg[2]); + }else{ + raw_printf(stderr, + "Usage: .set KEY VALUE\n Or: .set KEY=VALUE\n" + "Use SQL on the \"shell_bindings\" table to query or delete keys.\n" + ); + rc = 1; + goto meta_command_exit; + } + x = shell_bindings_new_text(zKey); + if( x ){ + utf8_printf(stderr, "Error: bad setting: %s\n", zKey); + } + sqlite3_free(zToFree); + }else + if( c=='s' && n>=4 && strncmp(azArg[0],"sha3sum",n)==0 ){ const char *zLike = 0; /* Which table to checksum. 0 means everything */ int i; /* Loop counter */ int bSchema = 0; /* Also hash the schema */ @@ -8114,10 +8142,11 @@ " -bail stop after hitting an error\n" " -batch force batch I/O\n" " -column set output mode to 'column'\n" " -cmd COMMAND run \"COMMAND\" before reading stdin\n" " -csv set output mode to 'csv'\n" + " -Dkey=value set shell binding variable \"key\" to \"value\"\n" " -echo print commands before execution\n" " -init FILENAME read/process named file\n" " -[no]header turn headers on or off\n" #if defined(SQLITE_ENABLE_MEMSYS3) || defined(SQLITE_ENABLE_MEMSYS5) " -heap SIZE Size of heap for memsys3 or memsys5\n" @@ -8417,10 +8446,16 @@ }else if( strncmp(z, "-A",2)==0 ){ /* All remaining command-line arguments are passed to the ".archive" ** command, so ignore them */ break; #endif + }else if( strncmp(z, "-D",2)==0 ){ + int x; + x = shell_bindings_new_text(z+2); + if( x ){ + utf8_printf(stderr, "Error: bad binding: %s\n", z); + } } } verify_uninitialized(); @@ -8613,10 +8648,12 @@ arDotCommand(&data, argv+i, argc-i); } readStdin = 0; break; #endif + }else if( strncmp(z, "-D", 2)==0 ){ + /* Noop */ }else{ utf8_printf(stderr,"%s: Error: unknown option: %s\n", Argv0, z); raw_printf(stderr,"Use -help for a list of options.\n"); return 1; } @@ -8691,14 +8728,15 @@ session_close_all(&data); sqlite3_close(data.db); } sqlite3_free(data.zFreeOnClose); find_home_dir(1); + shell_bindings_clear(); output_reset(&data); data.doXdgOpen = 0; clearTempFile(&data); #if !SQLITE_SHELL_IS_UTF8 for(i=0; i