/ Check-in [9404300a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the shardvtab virtual table that uses the new cost estimation functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | cost-est
Files: files | file ages | folders
SHA3-256: 9404300ac1dd0ef4e4b42f618901c6120b15a158c230f76e47c4c6346f6f4f58
User & Date: drh 2019-04-27 20:39:38
Context
2019-04-27
20:39
Add the shardvtab virtual table that uses the new cost estimation functions. Leaf check-in: 9404300a user: drh tags: cost-est
2019-04-26
17:20
An experimental interface for retrieving the estimated cost and estimated number of output rows for a query. check-in: 1b25fa10 user: drh tags: cost-est
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/misc/shardvtab.c.

            1  +/*
            2  +** 2019-04-26
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file implements a virtual-table that can be used to access a
           14  +** sharded table implemented as the UNION ALL of various separate tables.
           15  +*/
           16  +#if !defined(SQLITEINT_H)
           17  +#include "sqlite3ext.h"
           18  +#endif
           19  +SQLITE_EXTENSION_INIT1
           20  +#include <string.h>
           21  +#include <assert.h>
           22  +#include <math.h>
           23  +
           24  +/* shardvtab_vtab is a subclass of sqlite3_vtab which is
           25  +** underlying representation of the virtual table
           26  +*/
           27  +typedef struct shardvtab_vtab shardvtab_vtab;
           28  +struct shardvtab_vtab {
           29  +  sqlite3_vtab base;  /* Base class - must be first */
           30  +  sqlite3 *db;        /* The database connection */
           31  +  char *zView;        /* Name of view that implements the shard */
           32  +  int nCol;           /* Number of columns in the view */
           33  +  char **azCol;       /* Names of the columns, individually malloced */
           34  +};
           35  +
           36  +/* shardvtab_cursor is a subclass of sqlite3_vtab_cursor which will
           37  +** serve as the underlying representation of a cursor that scans
           38  +** over rows of the result
           39  +*/
           40  +typedef struct shardvtab_cursor shardvtab_cursor;
           41  +struct shardvtab_cursor {
           42  +  sqlite3_vtab_cursor base;  /* Base class - must be first */
           43  +  sqlite3_stmt *pStmt;       /* Prepared statement to access the shard */
           44  +  int rcLastStep;            /* Last return from sqlite3_step() */
           45  +};
           46  +
           47  +/*
           48  +** The shardvtabConnect() method is invoked to create a new
           49  +** shard virtual table.
           50  +**
           51  +** Think of this routine as the constructor for shardvtab_vtab objects.
           52  +**
           53  +** All this routine needs to do is:
           54  +**
           55  +**    (1) Allocate the shardvtab_vtab object and initialize all fields.
           56  +**
           57  +**    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
           58  +**        result set of queries against the virtual table will look like.
           59  +*/
           60  +static int shardvtabConnect(
           61  +  sqlite3 *db,
           62  +  void *pAux,
           63  +  int argc, const char *const*argv,
           64  +  sqlite3_vtab **ppVtab,
           65  +  char **pzErr
           66  +){
           67  +  shardvtab_vtab *pNew;
           68  +  int rc;
           69  +  char *zSql;
           70  +  sqlite3_str *pSchema;
           71  +  sqlite3_stmt *pStmt = 0;
           72  +  const char *zView = 0;
           73  +  char **azCol = 0;
           74  +  int nCol = 0;
           75  +  char cSep;
           76  +  int i;
           77  +
           78  +  if( argc!=4 || argv[0]==0 ){
           79  +    *pzErr = sqlite3_mprintf("one argument requires: the name of a view");
           80  +    return SQLITE_ERROR;
           81  +  }
           82  +  zView = argv[3];
           83  +  zSql = sqlite3_mprintf("SELECT * FROM \"%w\"", zView);
           84  +  if( zSql==0 ){
           85  +    return SQLITE_NOMEM;
           86  +  }
           87  +  rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
           88  +  sqlite3_free(zSql);
           89  +  if( rc ){
           90  +    *pzErr = sqlite3_mprintf("not a valid view: \"%w\"", zView);
           91  +    return SQLITE_NOMEM;
           92  +  }
           93  +  pSchema = sqlite3_str_new(db);
           94  +  if( pSchema==0 ){
           95  +    sqlite3_finalize(pStmt);
           96  +    return SQLITE_NOMEM;
           97  +  }
           98  +  sqlite3_str_appendall(pSchema, "CREATE TABLE x");
           99  +  cSep = '(';
          100  +  for(i=0; i<sqlite3_column_count(pStmt); i++){
          101  +    const char *zName = sqlite3_column_name(pStmt,i);
          102  +    char **azNew = sqlite3_realloc64(azCol, sizeof(azCol[0])*(i+1));
          103  +    if( azNew==0 ){
          104  +      rc = SQLITE_NOMEM;
          105  +      goto shardvtab_connect_error;
          106  +    }
          107  +    sqlite3_str_appendf(pSchema, "%c\"%w\"", cSep, zName);
          108  +    cSep = ',';
          109  +    azCol = azNew;
          110  +    azCol[nCol] = sqlite3_mprintf("%s", zName);
          111  +    if( azCol[nCol]==0 ){
          112  +      rc = SQLITE_NOMEM;
          113  +      goto shardvtab_connect_error;
          114  +    }
          115  +    nCol++;
          116  +  }
          117  +  sqlite3_str_appendall(pSchema, ")");
          118  +  sqlite3_finalize(pStmt);
          119  +  pStmt = 0;
          120  +  zSql = sqlite3_str_finish(pSchema);
          121  +  pSchema = 0;
          122  +  if( zSql==0 ){
          123  +    rc = SQLITE_NOMEM;
          124  +    goto shardvtab_connect_error;
          125  +  }
          126  +  rc = sqlite3_declare_vtab(db, zSql);
          127  +  sqlite3_free(zSql);
          128  +  if( rc!=SQLITE_OK ){
          129  +    goto shardvtab_connect_error;
          130  +  }else{
          131  +    size_t n = strlen(zView) + 1;
          132  +    pNew = sqlite3_malloc64( sizeof(*pNew) + n );
          133  +    *ppVtab = (sqlite3_vtab*)pNew;
          134  +    if( pNew==0 ){
          135  +      rc = SQLITE_NOMEM;
          136  +      goto shardvtab_connect_error;
          137  +    }
          138  +    memset(pNew, 0, sizeof(*pNew));
          139  +    pNew->db = db;
          140  +    pNew->zView = (char*)&pNew[1];
          141  +    memcpy(pNew->zView, zView, n);
          142  +    pNew->nCol = nCol;
          143  +    pNew->azCol = azCol;
          144  +  }
          145  +  return SQLITE_OK;
          146  +
          147  +shardvtab_connect_error:
          148  +  sqlite3_finalize(pStmt);
          149  +  for(i=0; i<nCol; i++) sqlite3_free(azCol[i]);
          150  +  sqlite3_free(azCol);
          151  +  sqlite3_free(sqlite3_str_finish(pSchema));
          152  +  return rc;
          153  +}
          154  +
          155  +/*
          156  +** This method is the destructor for shardvtab_vtab objects.
          157  +*/
          158  +static int shardvtabDisconnect(sqlite3_vtab *pVtab){
          159  +  int i;
          160  +  shardvtab_vtab *p = (shardvtab_vtab*)pVtab;
          161  +  for(i=0; i<p->nCol; i++) sqlite3_free(p->azCol[i]);
          162  +  sqlite3_free(p->azCol);
          163  +  sqlite3_free(p);
          164  +  return SQLITE_OK;
          165  +}
          166  +
          167  +/*
          168  +** Constructor for a new shardvtab_cursor object.
          169  +*/
          170  +static int shardvtabOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
          171  +  shardvtab_cursor *pCur;
          172  +  pCur = sqlite3_malloc( sizeof(*pCur) );
          173  +  if( pCur==0 ) return SQLITE_NOMEM;
          174  +  memset(pCur, 0, sizeof(*pCur));
          175  +  *ppCursor = &pCur->base;
          176  +  return SQLITE_OK;
          177  +}
          178  +
          179  +/*
          180  +** Destructor for a shardvtab_cursor.
          181  +*/
          182  +static int shardvtabClose(sqlite3_vtab_cursor *cur){
          183  +  shardvtab_cursor *pCur = (shardvtab_cursor*)cur;
          184  +  sqlite3_finalize(pCur->pStmt);
          185  +  sqlite3_free(pCur);
          186  +  return SQLITE_OK;
          187  +}
          188  +
          189  +
          190  +/*
          191  +** Advance a shardvtab_cursor to its next row of output.
          192  +*/
          193  +static int shardvtabNext(sqlite3_vtab_cursor *cur){
          194  +  shardvtab_cursor *pCur = (shardvtab_cursor*)cur;
          195  +  int rc;
          196  +  rc = pCur->rcLastStep = sqlite3_step(pCur->pStmt);
          197  +  if( rc==SQLITE_ROW || rc==SQLITE_DONE ) return SQLITE_OK;
          198  +  return rc;
          199  +}
          200  +
          201  +/*
          202  +** Return values of columns for the row at which the shardvtab_cursor
          203  +** is currently pointing.
          204  +*/
          205  +static int shardvtabColumn(
          206  +  sqlite3_vtab_cursor *cur,   /* The cursor */
          207  +  sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
          208  +  int i                       /* Which column to return */
          209  +){
          210  +  shardvtab_cursor *pCur = (shardvtab_cursor*)cur;
          211  +  sqlite3_result_value(ctx, sqlite3_column_value(pCur->pStmt, i));
          212  +  return SQLITE_OK;
          213  +}
          214  +
          215  +/*
          216  +** Return the rowid for the current row.  In this implementation, the
          217  +** rowid is the same as the output value.
          218  +*/
          219  +static int shardvtabRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          220  +  *pRowid = 0;
          221  +  return SQLITE_OK;
          222  +}
          223  +
          224  +/*
          225  +** Return TRUE if the cursor has been moved off of the last
          226  +** row of output.
          227  +*/
          228  +static int shardvtabEof(sqlite3_vtab_cursor *cur){
          229  +  shardvtab_cursor *pCur = (shardvtab_cursor*)cur;
          230  +  return pCur->rcLastStep!=SQLITE_ROW;
          231  +}
          232  +
          233  +/*
          234  +** This method is called to "rewind" the shardvtab_cursor object back
          235  +** to the first row of output.  This method is always called at least
          236  +** once prior to any call to shardvtabColumn() or shardvtabRowid() or 
          237  +** shardvtabEof().
          238  +*/
          239  +static int shardvtabFilter(
          240  +  sqlite3_vtab_cursor *pVtabCursor, 
          241  +  int idxNum, const char *idxStr,
          242  +  int argc, sqlite3_value **argv
          243  +){
          244  +  shardvtab_cursor *pCur = (shardvtab_cursor *)pVtabCursor;
          245  +  shardvtab_vtab *pTab = (shardvtab_vtab *)pVtabCursor->pVtab;
          246  +  int rc;
          247  +  sqlite3_finalize(pCur->pStmt);
          248  +  pCur->pStmt = 0;
          249  +  rc = sqlite3_prepare_v2(pTab->db, idxStr, -1, &pCur->pStmt, 0);
          250  +  if( rc==SQLITE_OK ){
          251  +    int i;
          252  +    for(i=0; i<argc; i++){
          253  +      sqlite3_bind_value(pCur->pStmt, i+1, argv[i]);
          254  +    }
          255  +  }else{
          256  +    sqlite3_finalize(pCur->pStmt);
          257  +    pCur->pStmt = 0;
          258  +  }
          259  +  pCur->rcLastStep = rc;
          260  +  return rc;
          261  +}
          262  +
          263  +/*
          264  +** SQLite will invoke this method one or more times while planning a query
          265  +** that uses the virtual table.  This routine needs to create
          266  +** a query plan for each invocation and compute an estimated cost for that
          267  +** plan.
          268  +*/
          269  +static int shardvtabBestIndex(
          270  +  sqlite3_vtab *tab,
          271  +  sqlite3_index_info *p
          272  +){
          273  +  shardvtab_vtab *pTab = (shardvtab_vtab*)tab;
          274  +  int i;
          275  +  int n;
          276  +  sqlite3_stmt *pStmt;
          277  +  int rc;
          278  +  sqlite3_str *pSql;
          279  +  char *zSep = "WHERE";
          280  +  char *zSql;
          281  +  pSql = sqlite3_str_new(pTab->db);
          282  +  if( pSql==0 ) return SQLITE_NOMEM;
          283  +  sqlite3_str_appendf(pSql, "SELECT * FROM \"%w\"", pTab->zView);
          284  +  for(i=n=0; i<p->nConstraint; i++){
          285  +    const char *zOp;
          286  +    int iCol;
          287  +    if( p->aConstraint[i].usable==0 ) continue;
          288  +    iCol = p->aConstraint[i].iColumn;
          289  +    if( iCol<0 ) continue;
          290  +    zOp = 0;
          291  +    switch( p->aConstraint[i].op ){
          292  +      case SQLITE_INDEX_CONSTRAINT_EQ:     zOp = "==";     break;
          293  +      case SQLITE_INDEX_CONSTRAINT_GT:     zOp = ">";      break;
          294  +      case SQLITE_INDEX_CONSTRAINT_LE:     zOp = "<=";     break;
          295  +      case SQLITE_INDEX_CONSTRAINT_LT:     zOp = "<";      break;
          296  +      case SQLITE_INDEX_CONSTRAINT_GE:     zOp = ">=";     break;
          297  +      case SQLITE_INDEX_CONSTRAINT_MATCH:  zOp = "MATCH";  break;
          298  +      case SQLITE_INDEX_CONSTRAINT_LIKE:   zOp = "LIKE";   break;
          299  +      case SQLITE_INDEX_CONSTRAINT_GLOB:   zOp = "GLOB";   break;
          300  +      case SQLITE_INDEX_CONSTRAINT_REGEXP: zOp = "REGEXP"; break;
          301  +      case SQLITE_INDEX_CONSTRAINT_NE:     zOp = "<>";     break;
          302  +      case SQLITE_INDEX_CONSTRAINT_IS:     zOp = "IS";     break;
          303  +    }
          304  +    if( zOp ){
          305  +      n++;
          306  +      p->aConstraintUsage[i].argvIndex = n;
          307  +      sqlite3_str_appendf(pSql, " %s (\"%w\" %s ?%d)", 
          308  +                          zSep, pTab->azCol[iCol], zOp, n);
          309  +      zSep = "AND";
          310  +    }
          311  +  }
          312  +  zSql = sqlite3_str_finish(pSql);
          313  +  if( zSql==0 ){
          314  +    return SQLITE_NOMEM;
          315  +  }
          316  +  rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pStmt, 0);
          317  +  if( rc==SQLITE_OK ){
          318  +    int x = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_COST, 0);
          319  +    p->estimatedCost = pow(2.0, 0.1*x);
          320  +    p->estimatedRows =
          321  +            sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_ROWS, 0);
          322  +    p->idxStr = zSql;
          323  +    p->needToFreeIdxStr = 1;
          324  +  }else{
          325  +    sqlite3_free(zSql);
          326  +  }
          327  +  sqlite3_finalize(pStmt);
          328  +  return rc;
          329  +}
          330  +
          331  +/*
          332  +** This following structure defines all the methods for the 
          333  +** virtual table.
          334  +*/
          335  +static sqlite3_module shardvtabModule = {
          336  +  /* iVersion    */ 0,
          337  +  /* xCreate     */ shardvtabConnect,
          338  +  /* xConnect    */ shardvtabConnect,
          339  +  /* xBestIndex  */ shardvtabBestIndex,
          340  +  /* xDisconnect */ shardvtabDisconnect,
          341  +  /* xDestroy    */ shardvtabDisconnect,
          342  +  /* xOpen       */ shardvtabOpen,
          343  +  /* xClose      */ shardvtabClose,
          344  +  /* xFilter     */ shardvtabFilter,
          345  +  /* xNext       */ shardvtabNext,
          346  +  /* xEof        */ shardvtabEof,
          347  +  /* xColumn     */ shardvtabColumn,
          348  +  /* xRowid      */ shardvtabRowid,
          349  +  /* xUpdate     */ 0,
          350  +  /* xBegin      */ 0,
          351  +  /* xSync       */ 0,
          352  +  /* xCommit     */ 0,
          353  +  /* xRollback   */ 0,
          354  +  /* xFindMethod */ 0,
          355  +  /* xRename     */ 0,
          356  +  /* xSavepoint  */ 0,
          357  +  /* xRelease    */ 0,
          358  +  /* xRollbackTo */ 0,
          359  +  /* xShadowName */ 0
          360  +};
          361  +
          362  +
          363  +#ifdef _WIN32
          364  +__declspec(dllexport)
          365  +#endif
          366  +int sqlite3_shardvtab_init(
          367  +  sqlite3 *db, 
          368  +  char **pzErrMsg, 
          369  +  const sqlite3_api_routines *pApi
          370  +){
          371  +  int rc = SQLITE_OK;
          372  +  SQLITE_EXTENSION_INIT2(pApi);
          373  +  rc = sqlite3_create_module(db, "shardvtab", &shardvtabModule, 0);
          374  +  return rc;
          375  +}