From 282b14c9c6e74a9f7e053e924e52a38c97e5fbaf Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 27 Apr 2019 20:39:38 +0000 Subject: [PATCH] Add the shardvtab virtual table that uses the new cost estimation functions. FossilOrigin-Name: 9404300ac1dd0ef4e4b42f618901c6120b15a158c230f76e47c4c6346f6f4f58 --- ext/misc/shardvtab.c | 375 +++++++++++++++++++++++++++++++++++++++++++ manifest | 14 +- manifest.uuid | 2 +- 3 files changed, 382 insertions(+), 9 deletions(-) create mode 100644 ext/misc/shardvtab.c diff --git a/ext/misc/shardvtab.c b/ext/misc/shardvtab.c new file mode 100644 index 0000000000..c9b1612b4c --- /dev/null +++ b/ext/misc/shardvtab.c @@ -0,0 +1,375 @@ +/* +** 2019-04-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 a virtual-table that can be used to access a +** sharded table implemented as the UNION ALL of various separate tables. +*/ +#if !defined(SQLITEINT_H) +#include "sqlite3ext.h" +#endif +SQLITE_EXTENSION_INIT1 +#include +#include +#include + +/* shardvtab_vtab is a subclass of sqlite3_vtab which is +** underlying representation of the virtual table +*/ +typedef struct shardvtab_vtab shardvtab_vtab; +struct shardvtab_vtab { + sqlite3_vtab base; /* Base class - must be first */ + sqlite3 *db; /* The database connection */ + char *zView; /* Name of view that implements the shard */ + int nCol; /* Number of columns in the view */ + char **azCol; /* Names of the columns, individually malloced */ +}; + +/* shardvtab_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 shardvtab_cursor shardvtab_cursor; +struct shardvtab_cursor { + sqlite3_vtab_cursor base; /* Base class - must be first */ + sqlite3_stmt *pStmt; /* Prepared statement to access the shard */ + int rcLastStep; /* Last return from sqlite3_step() */ +}; + +/* +** The shardvtabConnect() method is invoked to create a new +** shard virtual table. +** +** Think of this routine as the constructor for shardvtab_vtab objects. +** +** All this routine needs to do is: +** +** (1) Allocate the shardvtab_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 shardvtabConnect( + sqlite3 *db, + void *pAux, + int argc, const char *const*argv, + sqlite3_vtab **ppVtab, + char **pzErr +){ + shardvtab_vtab *pNew; + int rc; + char *zSql; + sqlite3_str *pSchema; + sqlite3_stmt *pStmt = 0; + const char *zView = 0; + char **azCol = 0; + int nCol = 0; + char cSep; + int i; + + if( argc!=4 || argv[0]==0 ){ + *pzErr = sqlite3_mprintf("one argument requires: the name of a view"); + return SQLITE_ERROR; + } + zView = argv[3]; + zSql = sqlite3_mprintf("SELECT * FROM \"%w\"", zView); + if( zSql==0 ){ + return SQLITE_NOMEM; + } + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); + sqlite3_free(zSql); + if( rc ){ + *pzErr = sqlite3_mprintf("not a valid view: \"%w\"", zView); + return SQLITE_NOMEM; + } + pSchema = sqlite3_str_new(db); + if( pSchema==0 ){ + sqlite3_finalize(pStmt); + return SQLITE_NOMEM; + } + sqlite3_str_appendall(pSchema, "CREATE TABLE x"); + cSep = '('; + for(i=0; idb = db; + pNew->zView = (char*)&pNew[1]; + memcpy(pNew->zView, zView, n); + pNew->nCol = nCol; + pNew->azCol = azCol; + } + return SQLITE_OK; + +shardvtab_connect_error: + sqlite3_finalize(pStmt); + for(i=0; inCol; i++) sqlite3_free(p->azCol[i]); + sqlite3_free(p->azCol); + sqlite3_free(p); + return SQLITE_OK; +} + +/* +** Constructor for a new shardvtab_cursor object. +*/ +static int shardvtabOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){ + shardvtab_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 shardvtab_cursor. +*/ +static int shardvtabClose(sqlite3_vtab_cursor *cur){ + shardvtab_cursor *pCur = (shardvtab_cursor*)cur; + sqlite3_finalize(pCur->pStmt); + sqlite3_free(pCur); + return SQLITE_OK; +} + + +/* +** Advance a shardvtab_cursor to its next row of output. +*/ +static int shardvtabNext(sqlite3_vtab_cursor *cur){ + shardvtab_cursor *pCur = (shardvtab_cursor*)cur; + int rc; + rc = pCur->rcLastStep = sqlite3_step(pCur->pStmt); + if( rc==SQLITE_ROW || rc==SQLITE_DONE ) return SQLITE_OK; + return rc; +} + +/* +** Return values of columns for the row at which the shardvtab_cursor +** is currently pointing. +*/ +static int shardvtabColumn( + sqlite3_vtab_cursor *cur, /* The cursor */ + sqlite3_context *ctx, /* First argument to sqlite3_result_...() */ + int i /* Which column to return */ +){ + shardvtab_cursor *pCur = (shardvtab_cursor*)cur; + sqlite3_result_value(ctx, sqlite3_column_value(pCur->pStmt, i)); + return SQLITE_OK; +} + +/* +** Return the rowid for the current row. In this implementation, the +** rowid is the same as the output value. +*/ +static int shardvtabRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ + *pRowid = 0; + return SQLITE_OK; +} + +/* +** Return TRUE if the cursor has been moved off of the last +** row of output. +*/ +static int shardvtabEof(sqlite3_vtab_cursor *cur){ + shardvtab_cursor *pCur = (shardvtab_cursor*)cur; + return pCur->rcLastStep!=SQLITE_ROW; +} + +/* +** This method is called to "rewind" the shardvtab_cursor object back +** to the first row of output. This method is always called at least +** once prior to any call to shardvtabColumn() or shardvtabRowid() or +** shardvtabEof(). +*/ +static int shardvtabFilter( + sqlite3_vtab_cursor *pVtabCursor, + int idxNum, const char *idxStr, + int argc, sqlite3_value **argv +){ + shardvtab_cursor *pCur = (shardvtab_cursor *)pVtabCursor; + shardvtab_vtab *pTab = (shardvtab_vtab *)pVtabCursor->pVtab; + int rc; + sqlite3_finalize(pCur->pStmt); + pCur->pStmt = 0; + rc = sqlite3_prepare_v2(pTab->db, idxStr, -1, &pCur->pStmt, 0); + if( rc==SQLITE_OK ){ + int i; + for(i=0; ipStmt, i+1, argv[i]); + } + }else{ + sqlite3_finalize(pCur->pStmt); + pCur->pStmt = 0; + } + pCur->rcLastStep = rc; + return rc; +} + +/* +** 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 shardvtabBestIndex( + sqlite3_vtab *tab, + sqlite3_index_info *p +){ + shardvtab_vtab *pTab = (shardvtab_vtab*)tab; + int i; + int n; + sqlite3_stmt *pStmt; + int rc; + sqlite3_str *pSql; + char *zSep = "WHERE"; + char *zSql; + pSql = sqlite3_str_new(pTab->db); + if( pSql==0 ) return SQLITE_NOMEM; + sqlite3_str_appendf(pSql, "SELECT * FROM \"%w\"", pTab->zView); + for(i=n=0; inConstraint; i++){ + const char *zOp; + int iCol; + if( p->aConstraint[i].usable==0 ) continue; + iCol = p->aConstraint[i].iColumn; + if( iCol<0 ) continue; + zOp = 0; + switch( p->aConstraint[i].op ){ + case SQLITE_INDEX_CONSTRAINT_EQ: zOp = "=="; break; + case SQLITE_INDEX_CONSTRAINT_GT: zOp = ">"; break; + case SQLITE_INDEX_CONSTRAINT_LE: zOp = "<="; break; + case SQLITE_INDEX_CONSTRAINT_LT: zOp = "<"; break; + case SQLITE_INDEX_CONSTRAINT_GE: zOp = ">="; break; + case SQLITE_INDEX_CONSTRAINT_MATCH: zOp = "MATCH"; break; + case SQLITE_INDEX_CONSTRAINT_LIKE: zOp = "LIKE"; break; + case SQLITE_INDEX_CONSTRAINT_GLOB: zOp = "GLOB"; break; + case SQLITE_INDEX_CONSTRAINT_REGEXP: zOp = "REGEXP"; break; + case SQLITE_INDEX_CONSTRAINT_NE: zOp = "<>"; break; + case SQLITE_INDEX_CONSTRAINT_IS: zOp = "IS"; break; + } + if( zOp ){ + n++; + p->aConstraintUsage[i].argvIndex = n; + sqlite3_str_appendf(pSql, " %s (\"%w\" %s ?%d)", + zSep, pTab->azCol[iCol], zOp, n); + zSep = "AND"; + } + } + zSql = sqlite3_str_finish(pSql); + if( zSql==0 ){ + return SQLITE_NOMEM; + } + rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pStmt, 0); + if( rc==SQLITE_OK ){ + int x = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_COST, 0); + p->estimatedCost = pow(2.0, 0.1*x); + p->estimatedRows = + sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_ROWS, 0); + p->idxStr = zSql; + p->needToFreeIdxStr = 1; + }else{ + sqlite3_free(zSql); + } + sqlite3_finalize(pStmt); + return rc; +} + +/* +** This following structure defines all the methods for the +** virtual table. +*/ +static sqlite3_module shardvtabModule = { + /* iVersion */ 0, + /* xCreate */ shardvtabConnect, + /* xConnect */ shardvtabConnect, + /* xBestIndex */ shardvtabBestIndex, + /* xDisconnect */ shardvtabDisconnect, + /* xDestroy */ shardvtabDisconnect, + /* xOpen */ shardvtabOpen, + /* xClose */ shardvtabClose, + /* xFilter */ shardvtabFilter, + /* xNext */ shardvtabNext, + /* xEof */ shardvtabEof, + /* xColumn */ shardvtabColumn, + /* xRowid */ shardvtabRowid, + /* xUpdate */ 0, + /* xBegin */ 0, + /* xSync */ 0, + /* xCommit */ 0, + /* xRollback */ 0, + /* xFindMethod */ 0, + /* xRename */ 0, + /* xSavepoint */ 0, + /* xRelease */ 0, + /* xRollbackTo */ 0, + /* xShadowName */ 0 +}; + + +#ifdef _WIN32 +__declspec(dllexport) +#endif +int sqlite3_shardvtab_init( + sqlite3 *db, + char **pzErrMsg, + const sqlite3_api_routines *pApi +){ + int rc = SQLITE_OK; + SQLITE_EXTENSION_INIT2(pApi); + rc = sqlite3_create_module(db, "shardvtab", &shardvtabModule, 0); + return rc; +} diff --git a/manifest b/manifest index f9983c512a..1a8787ed67 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C An\sexperimental\sinterface\sfor\sretrieving\sthe\sestimated\scost\sand\sestimated\nnumber\sof\soutput\srows\sfor\sa\squery. -D 2019-04-26T17:20:33.254 +C Add\sthe\sshardvtab\svirtual\stable\sthat\suses\sthe\snew\scost\sestimation\sfunctions. +D 2019-04-27T20:39:38.045 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -306,6 +306,7 @@ F ext/misc/rot13.c 540a169cb0d74f15522a8930b0cccdcb37a4fd071d219a5a083a319fc6e8d F ext/misc/scrub.c db9fff56fed322ca587d73727c6021b11ae79ce3f31b389e1d82891d144f22ad F ext/misc/series.c 0c97f63378fddc9f425e82ba139b9aaf902211f24ced115c2b6ae12b425f7334 F ext/misc/sha1.c df0a667211baa2c0612d8486acbf6331b9f8633fd4d605c17c7cccd26d59c6bd +F ext/misc/shardvtab.c 5ff824125e1cbb4040ac2646f3ab3444fdc867a617c263f04f2f4d4da6ef4667 F ext/misc/shathree.c 22ba7ca84a433d6466a7d05dcc876910b435a715da8cc462517db9351412b8c8 F ext/misc/showauth.c 732578f0fe4ce42d577e1c86dc89dd14a006ab52 F ext/misc/spellfix.c f88ecb2c0294453ce8b7704b211f5350c41b085b38c8e056852e3a08b0f5e484 @@ -1818,10 +1819,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 7be6222c9ec44596e4eddd906c831eb1272b90fbdf68641d791f216264feb7cf -R f445bfaab7875d7e9d6792f1def24978 -T *branch * cost-est -T *sym-cost-est * -T -sym-trunk * +P 1b25fa108ab2c4ada75935abf919de2b4c3b39553b2a0ab2a485645a02352e7e +R d379d98b062e48bddb17018fbaab4555 U drh -Z b1b37fe7015f8bb5921c7a5e02cfd8b8 +Z d532b28c8391dbdee92daa0c2fe6ec79 diff --git a/manifest.uuid b/manifest.uuid index 575253ef18..6aaca71081 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -1b25fa108ab2c4ada75935abf919de2b4c3b39553b2a0ab2a485645a02352e7e \ No newline at end of file +9404300ac1dd0ef4e4b42f618901c6120b15a158c230f76e47c4c6346f6f4f58 \ No newline at end of file -- 2.39.5