/ Check-in [6a9bab34]
Login

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

Overview
Comment:Add the experimental sqlite3_stmt_scanstatus() API.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: 6a9bab34aeb6a01b612211a28c140de60a3e883c
User & Date: dan 2014-10-31 20:11:32
Context
2014-11-01
18:08
Minor fixes and documentation improvements for sqlite3_stmt_scanstatus(). check-in: 8d8cc960 user: dan tags: scanstatus
2014-10-31
20:11
Add the experimental sqlite3_stmt_scanstatus() API. check-in: 6a9bab34 user: dan tags: scanstatus
14:46
Change the command-line shell man-page to use the ".tr" troff directive instead of ".cc" for escaping the initial "." characters in the ".help" output. check-in: 67f0d469 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  7403   7403   #define SQLITE_ROLLBACK 1
  7404   7404   /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
  7405   7405   #define SQLITE_FAIL     3
  7406   7406   /* #define SQLITE_ABORT 4  // Also an error code */
  7407   7407   #define SQLITE_REPLACE  5
  7408   7408   
  7409   7409   
         7410  +/*
         7411  +** Return status data for a single loop within query pStmt.
         7412  +**
         7413  +** Parameter "idx" identifies the specific loop to retrieve statistics for.
         7414  +** Loops are numbered starting from zero. If idx is out of range - less than
         7415  +** zero or greater than or equal to the total number of loops used to implement
         7416  +** the statement - a non-zero value is returned. In this case the final value
         7417  +** of all five output parameters is undefined. Otherwise, if idx is in range,
         7418  +** the output parameters are populated and zero returned.
         7419  +**
         7420  +** Statistics may not be available for all loops in all statements. In cases
         7421  +** where there exist loops with no available statistics, this function ignores
         7422  +** them completely.
         7423  +**
         7424  +** This API is only available if the library is built with pre-processor
         7425  +** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
         7426  +*/
         7427  +SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus(
         7428  +  sqlite3_stmt *pStmt,
         7429  +  int idx,                        /* Index of loop to report on */
         7430  +  sqlite3_int64 *pnLoop,          /* OUT: Number of times loop was run */
         7431  +  sqlite3_int64 *pnVisit,         /* OUT: Number of rows visited (all loops) */
         7432  +  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
         7433  +  const char **pzName,            /* OUT: Object name (table or index) */
         7434  +  const char **pzExplain          /* OUT: EQP string */
         7435  +);
         7436  +
         7437  +
         7438  +/*
         7439  +** Zero all sqlite3_stmt_scanstatus() related event counters.
         7440  +**
         7441  +** This API is only available if the library is built with pre-processor
         7442  +** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
         7443  +*/
         7444  +SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*);
         7445  +
  7410   7446   
  7411   7447   /*
  7412   7448   ** Undo the hack that converts floating point types to integer for
  7413   7449   ** builds on processors without floating point support.
  7414   7450   */
  7415   7451   #ifdef SQLITE_OMIT_FLOATING_POINT
  7416   7452   # undef double
  7417   7453   #endif
  7418   7454   
  7419   7455   #ifdef __cplusplus
  7420   7456   }  /* End of the 'extern "C"' block */
  7421   7457   #endif
  7422   7458   #endif /* _SQLITE3_H_ */

Changes to src/tclsqlite.c.

  3637   3637     }
  3638   3638   
  3639   3639     pDb->bLegacyPrepare = bPrepare;
  3640   3640   
  3641   3641     Tcl_ResetResult(interp);
  3642   3642     return TCL_OK;
  3643   3643   }
         3644  +
         3645  +/*
         3646  +** Tclcmd: db_last_stmt_ptr DB
         3647  +**
         3648  +**   If the statement cache associated with database DB is not empty,
         3649  +**   return the text representation of the most recently used statement
         3650  +**   handle.
         3651  +*/
         3652  +static int db_last_stmt_ptr(
         3653  +  ClientData cd,
         3654  +  Tcl_Interp *interp,
         3655  +  int objc,
         3656  +  Tcl_Obj *CONST objv[]
         3657  +){
         3658  +  extern int sqlite3TestMakePointerStr(Tcl_Interp*, char*, void*);
         3659  +  Tcl_CmdInfo cmdInfo;
         3660  +  SqliteDb *pDb;
         3661  +  sqlite3_stmt *pStmt = 0;
         3662  +  char zBuf[100];
         3663  +
         3664  +  if( objc!=2 ){
         3665  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
         3666  +    return TCL_ERROR;
         3667  +  }
         3668  +
         3669  +  if( !Tcl_GetCommandInfo(interp, Tcl_GetString(objv[1]), &cmdInfo) ){
         3670  +    Tcl_AppendResult(interp, "no such db: ", Tcl_GetString(objv[1]), (char*)0);
         3671  +    return TCL_ERROR;
         3672  +  }
         3673  +  pDb = (SqliteDb*)cmdInfo.objClientData;
         3674  +
         3675  +  if( pDb->stmtList ) pStmt = pDb->stmtList->pStmt;
         3676  +  if( sqlite3TestMakePointerStr(interp, zBuf, pStmt) ){
         3677  +    return TCL_ERROR;
         3678  +  }
         3679  +  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
         3680  +
         3681  +  return TCL_OK;
         3682  +}
  3644   3683   #endif
  3645   3684   
  3646   3685   /*
  3647   3686   ** Configure the interpreter passed as the first argument to have access
  3648   3687   ** to the commands and linked variables that make up:
  3649   3688   **
  3650   3689   **   * the [sqlite3] extension itself, 
................................................................................
  3756   3795   
  3757   3796       Tcl_CreateObjCommand(
  3758   3797           interp, "load_testfixture_extensions", init_all_cmd, 0, 0
  3759   3798       );
  3760   3799       Tcl_CreateObjCommand(
  3761   3800           interp, "db_use_legacy_prepare", db_use_legacy_prepare_cmd, 0, 0
  3762   3801       );
         3802  +    Tcl_CreateObjCommand(
         3803  +        interp, "db_last_stmt_ptr", db_last_stmt_ptr, 0, 0
         3804  +    );
  3763   3805   
  3764   3806   #ifdef SQLITE_SSE
  3765   3807       Sqlitetestsse_Init(interp);
  3766   3808   #endif
  3767   3809     }
  3768   3810   #endif
  3769   3811   }

Changes to src/test1.c.

  2297   2297     }
  2298   2298     if( Tcl_GetBooleanFromObj(interp, objv[3], &resetFlag) ) return TCL_ERROR;
  2299   2299     iValue = sqlite3_stmt_status(pStmt, op, resetFlag);
  2300   2300     Tcl_SetObjResult(interp, Tcl_NewIntObj(iValue));
  2301   2301     return TCL_OK;
  2302   2302   }
  2303   2303   
         2304  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         2305  +/*
         2306  +** Usage:  sqlite3_stmt_scanstatus STMT IDX
         2307  +*/
         2308  +static int test_stmt_scanstatus(
         2309  +  void * clientData,
         2310  +  Tcl_Interp *interp,
         2311  +  int objc,
         2312  +  Tcl_Obj *CONST objv[]
         2313  +){
         2314  +  sqlite3_stmt *pStmt;            /* First argument */
         2315  +  int idx;                        /* Second argument */
         2316  +
         2317  +  const char *zName;
         2318  +  const char *zExplain;
         2319  +  sqlite3_int64 nLoop;
         2320  +  sqlite3_int64 nVisit;
         2321  +  sqlite3_int64 nEst;
         2322  +  int res;
         2323  +
         2324  +  if( objc!=3 ){
         2325  +    Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
         2326  +    return TCL_ERROR;
         2327  +  }
         2328  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         2329  +  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;
         2330  +
         2331  +  res = sqlite3_stmt_scanstatus(
         2332  +      pStmt, idx, &nLoop, &nVisit, &nEst, &zName, &zExplain
         2333  +  );
         2334  +  if( res==0 ){
         2335  +    Tcl_Obj *pRet = Tcl_NewObj();
         2336  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
         2337  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));
         2338  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
         2339  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));
         2340  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
         2341  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));
         2342  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
         2343  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));
         2344  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
         2345  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zExplain, -1));
         2346  +    Tcl_SetObjResult(interp, pRet);
         2347  +  }else{
         2348  +    Tcl_ResetResult(interp);
         2349  +  }
         2350  +  return TCL_OK;
         2351  +}
         2352  +
         2353  +/*
         2354  +** Usage:  sqlite3_stmt_scanstatus_reset  STMT
         2355  +*/
         2356  +static int test_stmt_scanstatus_reset(
         2357  +  void * clientData,
         2358  +  Tcl_Interp *interp,
         2359  +  int objc,
         2360  +  Tcl_Obj *CONST objv[]
         2361  +){
         2362  +  sqlite3_stmt *pStmt;            /* First argument */
         2363  +  if( objc!=2 ){
         2364  +    Tcl_WrongNumArgs(interp, 1, objv, "STMT");
         2365  +    return TCL_ERROR;
         2366  +  }
         2367  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         2368  +  sqlite3_stmt_scanstatus_reset(pStmt);
         2369  +  return TCL_OK;
         2370  +}
         2371  +#endif
         2372  +
  2304   2373   /*
  2305   2374   ** Usage:  sqlite3_next_stmt  DB  STMT
  2306   2375   **
  2307   2376   ** Return the next statment in sequence after STMT.
  2308   2377   */
  2309   2378   static int test_next_stmt(
  2310   2379     void * clientData,
................................................................................
  6864   6933        { "sorter_test_sort4_helper", sorter_test_sort4_helper },
  6865   6934   #ifdef SQLITE_USER_AUTHENTICATION
  6866   6935        { "sqlite3_user_authenticate", test_user_authenticate, 0 },
  6867   6936        { "sqlite3_user_add",          test_user_add,          0 },
  6868   6937        { "sqlite3_user_change",       test_user_change,       0 },
  6869   6938        { "sqlite3_user_delete",       test_user_delete,       0 },
  6870   6939   #endif
         6940  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         6941  +     { "sqlite3_stmt_scanstatus",       test_stmt_scanstatus,   0 },
         6942  +     { "sqlite3_stmt_scanstatus_reset", test_stmt_scanstatus_reset,   0 },
         6943  +#endif
  6871   6944   
  6872   6945     };
  6873   6946     static int bitmask_size = sizeof(Bitmask)*8;
  6874   6947     int i;
  6875   6948     extern int sqlite3_sync_count, sqlite3_fullsync_count;
  6876   6949     extern int sqlite3_opentemp_count;
  6877   6950     extern int sqlite3_like_count;

Changes to src/vdbe.c.

   163    163   #define Deephemeralize(P) \
   164    164      if( ((P)->flags&MEM_Ephem)!=0 \
   165    165          && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}
   166    166   
   167    167   /* Return true if the cursor was opened using the OP_OpenSorter opcode. */
   168    168   #define isSorter(x) ((x)->pSorter!=0)
   169    169   
          170  +/*
          171  +** The first argument passed to the IncrementExplainCounter() macro must
          172  +** be a non-NULL pointer to an object of type VdbeCursor. The second 
          173  +** argument must be either "nLoop" or "nVisit" (without the double-quotes).
          174  +*/
          175  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          176  +# define IncrementExplainCounter(pCsr, counter) \
          177  +    if( (pCsr)->pExplain ) (pCsr)->pExplain->counter++
          178  +#else
          179  +# define IncrementExplainCounter(pCsr, counter)
          180  +#endif
          181  +
   170    182   /*
   171    183   ** Allocate VdbeCursor number iCur.  Return a pointer to it.  Return NULL
   172    184   ** if we run out of memory.
   173    185   */
   174    186   static VdbeCursor *allocateCursor(
   175    187     Vdbe *p,              /* The virtual machine */
   176    188     int iCur,             /* Index of the new VdbeCursor */
................................................................................
  3552   3564     assert( pC->isOrdered );
  3553   3565     assert( pC->pCursor!=0 );
  3554   3566     oc = pOp->opcode;
  3555   3567     pC->nullRow = 0;
  3556   3568   #ifdef SQLITE_DEBUG
  3557   3569     pC->seekOp = pOp->opcode;
  3558   3570   #endif
         3571  +  IncrementExplainCounter(pC, nLoop);
  3559   3572     if( pC->isTable ){
  3560   3573       /* The input value in P3 might be of any type: integer, real, string,
  3561   3574       ** blob, or NULL.  But it needs to be an integer before we can do
  3562   3575       ** the seek, so convert it. */
  3563   3576       pIn3 = &aMem[pOp->p3];
  3564   3577       if( (pIn3->flags & (MEM_Int|MEM_Real|MEM_Str))==MEM_Str ){
  3565   3578         applyNumericAffinity(pIn3, 0);
................................................................................
  3660   3673         res = sqlite3BtreeEof(pC->pCursor);
  3661   3674       }
  3662   3675     }
  3663   3676     assert( pOp->p2>0 );
  3664   3677     VdbeBranchTaken(res!=0,2);
  3665   3678     if( res ){
  3666   3679       pc = pOp->p2 - 1;
         3680  +  }else{
         3681  +    IncrementExplainCounter(pC, nVisit);
  3667   3682     }
  3668   3683     break;
  3669   3684   }
  3670   3685   
  3671   3686   /* Opcode: Seek P1 P2 * * *
  3672   3687   ** Synopsis:  intkey=r[P2]
  3673   3688   **
................................................................................
  4445   4460     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  4446   4461     pC = p->apCsr[pOp->p1];
  4447   4462     assert( pC!=0 );
  4448   4463     pCrsr = pC->pCursor;
  4449   4464     res = 0;
  4450   4465     assert( pCrsr!=0 );
  4451   4466     rc = sqlite3BtreeLast(pCrsr, &res);
         4467  +  IncrementExplainCounter(pC, nLoop);
         4468  +  if( res==0 ) IncrementExplainCounter(pC, nVisit);
  4452   4469     pC->nullRow = (u8)res;
  4453   4470     pC->deferredMoveto = 0;
  4454   4471     pC->cacheStatus = CACHE_STALE;
  4455   4472   #ifdef SQLITE_DEBUG
  4456   4473     pC->seekOp = OP_Last;
  4457   4474   #endif
  4458   4475     if( pOp->p2>0 ){
................................................................................
  4484   4501     p->aCounter[SQLITE_STMTSTATUS_SORT]++;
  4485   4502     /* Fall through into OP_Rewind */
  4486   4503   }
  4487   4504   /* Opcode: Rewind P1 P2 * * *
  4488   4505   **
  4489   4506   ** The next use of the Rowid or Column or Next instruction for P1 
  4490   4507   ** will refer to the first entry in the database table or index.
  4491         -** If the table or index is empty and P2>0, then jump immediately to P2.
  4492         -** If P2 is 0 or if the table or index is not empty, fall through
  4493         -** to the following instruction.
         4508  +** If the table or index is empty, jump immediately to P2.
         4509  +** If the table or index is not empty, fall through to the following 
         4510  +** instruction.
  4494   4511   **
  4495   4512   ** This opcode leaves the cursor configured to move in forward order,
  4496   4513   ** from the beginning toward the end.  In other words, the cursor is
  4497   4514   ** configured to use Next, not Prev.
  4498   4515   */
  4499   4516   case OP_Rewind: {        /* jump */
  4500   4517     VdbeCursor *pC;
................................................................................
  4514   4531     }else{
  4515   4532       pCrsr = pC->pCursor;
  4516   4533       assert( pCrsr );
  4517   4534       rc = sqlite3BtreeFirst(pCrsr, &res);
  4518   4535       pC->deferredMoveto = 0;
  4519   4536       pC->cacheStatus = CACHE_STALE;
  4520   4537     }
         4538  +  IncrementExplainCounter(pC, nLoop);
  4521   4539     pC->nullRow = (u8)res;
  4522   4540     assert( pOp->p2>0 && pOp->p2<p->nOp );
  4523   4541     VdbeBranchTaken(res!=0,2);
  4524   4542     if( res ){
  4525   4543       pc = pOp->p2 - 1;
         4544  +  }else{
         4545  +    IncrementExplainCounter(pC, nVisit);
  4526   4546     }
  4527   4547     break;
  4528   4548   }
  4529   4549   
  4530   4550   /* Opcode: Next P1 P2 P3 P4 P5
  4531   4551   **
  4532   4552   ** Advance cursor P1 so that it points to the next key/data pair in its
................................................................................
  4629   4649          || pC->seekOp==OP_Last );
  4630   4650   
  4631   4651     rc = pOp->p4.xAdvance(pC->pCursor, &res);
  4632   4652   next_tail:
  4633   4653     pC->cacheStatus = CACHE_STALE;
  4634   4654     VdbeBranchTaken(res==0,2);
  4635   4655     if( res==0 ){
         4656  +    IncrementExplainCounter(pC, nVisit);
  4636   4657       pC->nullRow = 0;
  4637   4658       pc = pOp->p2 - 1;
  4638   4659       p->aCounter[pOp->p5]++;
  4639   4660   #ifdef SQLITE_TEST
  4640   4661       sqlite3_search_count++;
  4641   4662   #endif
  4642   4663     }else{
................................................................................
  6051   6072       sqlite3VtabImportErrmsg(p, pVtab);
  6052   6073       if( rc==SQLITE_OK ){
  6053   6074         res = pModule->xEof(pVtabCursor);
  6054   6075       }
  6055   6076       VdbeBranchTaken(res!=0,2);
  6056   6077       if( res ){
  6057   6078         pc = pOp->p2 - 1;
         6079  +    }else{
         6080  +      IncrementExplainCounter(pCur, nVisit);
  6058   6081       }
         6082  +    IncrementExplainCounter(pCur, nLoop);
  6059   6083     }
  6060   6084     pCur->nullRow = 0;
  6061   6085   
  6062   6086     break;
  6063   6087   }
  6064   6088   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  6065   6089   
................................................................................
  6144   6168     if( rc==SQLITE_OK ){
  6145   6169       res = pModule->xEof(pCur->pVtabCursor);
  6146   6170     }
  6147   6171     VdbeBranchTaken(!res,2);
  6148   6172     if( !res ){
  6149   6173       /* If there is data, jump to P2 */
  6150   6174       pc = pOp->p2 - 1;
         6175  +    IncrementExplainCounter(pCur, nVisit);
  6151   6176     }
  6152   6177     goto check_for_interrupt;
  6153   6178   }
  6154   6179   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  6155   6180   
  6156   6181   #ifndef SQLITE_OMIT_VIRTUALTABLE
  6157   6182   /* Opcode: VRename P1 * * P4 *
................................................................................
  6342   6367     ){
  6343   6368       sqlite3DebugPrintf("SQL-trace: %s\n", zTrace);
  6344   6369     }
  6345   6370   #endif /* SQLITE_DEBUG */
  6346   6371   #endif /* SQLITE_OMIT_TRACE */
  6347   6372     break;
  6348   6373   }
         6374  +
         6375  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         6376  +case OP_Explain: {
         6377  +  ExplainArg *pArg;
         6378  +  VdbeCursor *pCur;
         6379  +  if( pOp->p4type==P4_EXPLAIN && (pArg = pOp->p4.pExplain)->iCsr>=0 ){
         6380  +    pArg = pOp->p4.pExplain;
         6381  +    pCur = p->apCsr[pArg->iCsr];
         6382  +    pCur->pExplain = pArg;
         6383  +  }
         6384  +  break;
         6385  +}
         6386  +#endif
  6349   6387   
  6350   6388   
  6351   6389   /* Opcode: Noop * * * * *
  6352   6390   **
  6353   6391   ** Do nothing.  This instruction is often useful as a jump
  6354   6392   ** destination.
  6355   6393   */

Changes to src/vdbe.h.

    21     21   
    22     22   /*
    23     23   ** A single VDBE is an opaque structure named "Vdbe".  Only routines
    24     24   ** in the source file sqliteVdbe.c are allowed to see the insides
    25     25   ** of this structure.
    26     26   */
    27     27   typedef struct Vdbe Vdbe;
           28  +typedef struct ExplainArg ExplainArg;
    28     29   
    29     30   /*
    30     31   ** The names of the following types declared in vdbeInt.h are required
    31     32   ** for the VdbeOp definition.
    32     33   */
    33     34   typedef struct Mem Mem;
    34     35   typedef struct SubProgram SubProgram;
................................................................................
    55     56       FuncDef *pFunc;        /* Used when p4type is P4_FUNCDEF */
    56     57       CollSeq *pColl;        /* Used when p4type is P4_COLLSEQ */
    57     58       Mem *pMem;             /* Used when p4type is P4_MEM */
    58     59       VTable *pVtab;         /* Used when p4type is P4_VTAB */
    59     60       KeyInfo *pKeyInfo;     /* Used when p4type is P4_KEYINFO */
    60     61       int *ai;               /* Used when p4type is P4_INTARRAY */
    61     62       SubProgram *pProgram;  /* Used when p4type is P4_SUBPROGRAM */
           63  +    ExplainArg *pExplain;  /* Used when p4type is P4_EXPLAIN */
    62     64       int (*xAdvance)(BtCursor *, int *);
    63     65     } p4;
    64     66   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
    65     67     char *zComment;          /* Comment to improve readability */
    66     68   #endif
    67     69   #ifdef VDBE_PROFILE
    68     70     u32 cnt;                 /* Number of times this instruction was executed */
................................................................................
    95     97   struct VdbeOpList {
    96     98     u8 opcode;          /* What operation to perform */
    97     99     signed char p1;     /* First operand */
    98    100     signed char p2;     /* Second parameter (often the jump destination) */
    99    101     signed char p3;     /* Third parameter */
   100    102   };
   101    103   typedef struct VdbeOpList VdbeOpList;
          104  +
          105  +/*
          106  +** Structure used as the P4 parameter for OP_Explain opcodes.
          107  +*/
          108  +struct ExplainArg {
          109  +  int iCsr;                       /* Cursor number this applies to */
          110  +  i64 nLoop;                      /* Number of times loop has run */
          111  +  i64 nVisit;                     /* Total number of rows visited */
          112  +  i64 nEst;                       /* Estimated number of rows per scan */
          113  +  const char *zName;              /* Name of table/index being scanned */
          114  +  const char *zExplain;           /* EQP text for this loop */
          115  +};
          116  +
   102    117   
   103    118   /*
   104    119   ** Allowed values of VdbeOp.p4type
   105    120   */
   106    121   #define P4_NOTUSED    0   /* The P4 parameter is not used */
   107    122   #define P4_DYNAMIC  (-1)  /* Pointer to a string obtained from sqliteMalloc() */
   108    123   #define P4_STATIC   (-2)  /* Pointer to a static string */
................................................................................
   115    130   #define P4_MPRINTF  (-11) /* P4 is a string obtained from sqlite3_mprintf() */
   116    131   #define P4_REAL     (-12) /* P4 is a 64-bit floating point value */
   117    132   #define P4_INT64    (-13) /* P4 is a 64-bit signed integer */
   118    133   #define P4_INT32    (-14) /* P4 is a 32-bit signed integer */
   119    134   #define P4_INTARRAY (-15) /* P4 is a vector of 32-bit integers */
   120    135   #define P4_SUBPROGRAM  (-18) /* P4 is a pointer to a SubProgram structure */
   121    136   #define P4_ADVANCE  (-19) /* P4 is a pointer to BtreeNext() or BtreePrev() */
          137  +#define P4_EXPLAIN  (-20) /* P4 is a pointer to an instance of ExplainArg */
   122    138   
   123    139   /* Error message codes for OP_Halt */
   124    140   #define P5_ConstraintNotNull 1
   125    141   #define P5_ConstraintUnique  2
   126    142   #define P5_ConstraintCheck   3
   127    143   #define P5_ConstraintFK      4
   128    144   

Changes to src/vdbeInt.h.

    79     79     Bool isTable:1;       /* True if a table requiring integer keys */
    80     80     Bool isOrdered:1;     /* True if the underlying table is BTREE_UNORDERED */
    81     81     Pgno pgnoRoot;        /* Root page of the open btree cursor */
    82     82     sqlite3_vtab_cursor *pVtabCursor;  /* The cursor for a virtual table */
    83     83     i64 seqCount;         /* Sequence counter */
    84     84     i64 movetoTarget;     /* Argument to the deferred sqlite3BtreeMoveto() */
    85     85     VdbeSorter *pSorter;  /* Sorter object for OP_SorterOpen cursors */
           86  +  ExplainArg *pExplain; /* Object to store seek/visit counts (may be NULL) */
    86     87   
    87     88     /* Cached information about the header for the data record that the
    88     89     ** cursor is currently pointing to.  Only valid if cacheStatus matches
    89     90     ** Vdbe.cacheCtr.  Vdbe.cacheCtr will never take on the value of
    90     91     ** CACHE_STALE and so setting cacheStatus=CACHE_STALE guarantees that
    91     92     ** the cache is out of date.
    92     93     **
................................................................................
   286    287   struct Explain {
   287    288     Vdbe *pVdbe;       /* Attach the explanation to this Vdbe */
   288    289     StrAccum str;      /* The string being accumulated */
   289    290     int nIndent;       /* Number of elements in aIndent */
   290    291     u16 aIndent[100];  /* Levels of indentation */
   291    292     char zBase[100];   /* Initial space */
   292    293   };
          294  +
   293    295   
   294    296   /* A bitfield type for use inside of structures.  Always follow with :N where
   295    297   ** N is the number of bits.
   296    298   */
   297    299   typedef unsigned bft;  /* Bit Field Type */
   298    300   
   299    301   /*
................................................................................
   364    366     VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
   365    367     int nFrame;             /* Number of frames in pFrame list */
   366    368     u32 expmask;            /* Binding to these vars invalidates VM */
   367    369     SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */
   368    370     int nOnceFlag;          /* Size of array aOnceFlag[] */
   369    371     u8 *aOnceFlag;          /* Flags for OP_Once */
   370    372     AuxData *pAuxData;      /* Linked list of auxdata allocations */
          373  +  ExplainArg **apExplain; /* Array of pointers to P4_EXPLAIN p4 values */
          374  +  int nExplain;           /* Number of entries in array apExplain */
   371    375   };
   372    376   
   373    377   /*
   374    378   ** The following are allowed values for Vdbe.magic
   375    379   */
   376    380   #define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */
   377    381   #define VDBE_MAGIC_RUN      0xbdf20da3    /* VDBE is ready to execute */

Changes to src/vdbeapi.c.

  1471   1471       return 0;
  1472   1472     }
  1473   1473   #endif
  1474   1474     v = pVdbe->aCounter[op];
  1475   1475     if( resetFlag ) pVdbe->aCounter[op] = 0;
  1476   1476     return (int)v;
  1477   1477   }
         1478  +
         1479  +/*
         1480  +** Return status data for a single loop within query pStmt.
         1481  +*/
         1482  +int sqlite3_stmt_scanstatus(
         1483  +  sqlite3_stmt *pStmt,
         1484  +  int idx,                        /* Index of loop to report on */
         1485  +  sqlite3_int64 *pnLoop,          /* OUT: Number of times loop was run */
         1486  +  sqlite3_int64 *pnVisit,         /* OUT: Number of rows visited (all loops) */
         1487  +  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
         1488  +  const char **pzName,            /* OUT: Object name (table or index) */
         1489  +  const char **pzExplain          /* OUT: EQP string */
         1490  +){
         1491  +  Vdbe *p = (Vdbe*)pStmt;
         1492  +  ExplainArg *pExplain;
         1493  +  if( idx<0 || idx>=p->nExplain ) return 1;
         1494  +  pExplain = p->apExplain[idx];
         1495  +  if( pnLoop ) *pnLoop = pExplain->nLoop;
         1496  +  if( pnVisit ) *pnVisit = pExplain->nVisit;
         1497  +  if( pnEst ) *pnEst = pExplain->nEst;
         1498  +  if( *pzName ) *pzName = pExplain->zName;
         1499  +  if( *pzExplain ) *pzExplain = pExplain->zExplain;
         1500  +  return 0;
         1501  +}
         1502  +
         1503  +/*
         1504  +** Zero all counters associated with the sqlite3_stmt_scanstatus() data.
         1505  +*/
         1506  +void sqlite3_stmt_scanstatus_reset(sqlite3_stmt *pStmt){
         1507  +  Vdbe *p = (Vdbe*)pStmt;
         1508  +  int i;
         1509  +  for(i=0; i<p->nExplain; i++){
         1510  +    p->apExplain[i]->nLoop = 0;
         1511  +    p->apExplain[i]->nVisit = 0;
         1512  +  }
         1513  +}
         1514  +

Changes to src/vdbeaux.c.

   668    668   /*
   669    669   ** Delete a P4 value if necessary.
   670    670   */
   671    671   static void freeP4(sqlite3 *db, int p4type, void *p4){
   672    672     if( p4 ){
   673    673       assert( db );
   674    674       switch( p4type ){
          675  +      case P4_EXPLAIN:
   675    676         case P4_REAL:
   676    677         case P4_INT64:
   677    678         case P4_DYNAMIC:
   678    679         case P4_INTARRAY: {
   679    680           sqlite3DbFree(db, p4);
   680    681           break;
   681    682         }
................................................................................
   816    817       pOp->p4.p = (void*)zP4;
   817    818       pOp->p4type = P4_KEYINFO;
   818    819     }else if( n==P4_VTAB ){
   819    820       pOp->p4.p = (void*)zP4;
   820    821       pOp->p4type = P4_VTAB;
   821    822       sqlite3VtabLock((VTable *)zP4);
   822    823       assert( ((VTable *)zP4)->db==p->db );
          824  +  }else if( n==P4_EXPLAIN ){
          825  +    pOp->p4.p = (void*)zP4;
          826  +    pOp->p4type = P4_EXPLAIN;
          827  +    p->apExplain = (ExplainArg**)sqlite3DbReallocOrFree(
          828  +        p->db, p->apExplain, sizeof(ExplainArg*) * p->nExplain + 1
          829  +    );
          830  +    if( p->apExplain ) p->apExplain[p->nExplain++] = (ExplainArg*)zP4;
   823    831     }else if( n<0 ){
   824    832       pOp->p4.p = (void*)zP4;
   825    833       pOp->p4type = (signed char)n;
   826    834     }else{
   827    835       if( n==0 ) n = sqlite3Strlen30(zP4);
   828    836       pOp->p4.z = sqlite3DbStrNDup(p->db, zP4, n);
   829    837       pOp->p4type = P4_DYNAMIC;
................................................................................
  1098   1106       case P4_SUBPROGRAM: {
  1099   1107         sqlite3_snprintf(nTemp, zTemp, "program");
  1100   1108         break;
  1101   1109       }
  1102   1110       case P4_ADVANCE: {
  1103   1111         zTemp[0] = 0;
  1104   1112         break;
         1113  +    }
         1114  +    case P4_EXPLAIN: {
         1115  +      zP4 = (char*)pOp->p4.pExplain->zExplain;
         1116  +      break;
  1105   1117       }
  1106   1118       default: {
  1107   1119         zP4 = pOp->p4.z;
  1108   1120         if( zP4==0 ){
  1109   1121           zP4 = zTemp;
  1110   1122           zTemp[0] = 0;
  1111   1123         }
................................................................................
  2681   2693       sqlite3DbFree(db, pSub);
  2682   2694     }
  2683   2695     for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]);
  2684   2696     vdbeFreeOpArray(db, p->aOp, p->nOp);
  2685   2697     sqlite3DbFree(db, p->aColName);
  2686   2698     sqlite3DbFree(db, p->zSql);
  2687   2699     sqlite3DbFree(db, p->pFree);
         2700  +  sqlite3DbFree(db, p->apExplain);
  2688   2701   }
  2689   2702   
  2690   2703   /*
  2691   2704   ** Delete an entire VDBE.
  2692   2705   */
  2693   2706   void sqlite3VdbeDelete(Vdbe *p){
  2694   2707     sqlite3 *db;

Changes to src/where.c.

  2816   2816     Parse *pParse,                  /* Parse context */
  2817   2817     SrcList *pTabList,              /* Table list this loop refers to */
  2818   2818     WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  2819   2819     int iLevel,                     /* Value for "level" column of output */
  2820   2820     int iFrom,                      /* Value for "from" column of output */
  2821   2821     u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
  2822   2822   ){
  2823         -#ifndef SQLITE_DEBUG
         2823  +#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
  2824   2824     if( pParse->explain==2 )
  2825   2825   #endif
  2826   2826     {
  2827   2827       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  2828   2828       Vdbe *v = pParse->pVdbe;      /* VM being constructed */
  2829   2829       sqlite3 *db = pParse->db;     /* Database handle */
  2830   2830       int iId = pParse->iSelectId;  /* Select id (left-most output column) */
  2831   2831       int isSearch;                 /* True for a SEARCH. False for SCAN. */
  2832   2832       WhereLoop *pLoop;             /* The controlling WhereLoop object */
  2833   2833       u32 flags;                    /* Flags that describe this loop */
  2834         -    char *zMsg;                   /* Text to add to EQP output */
  2835   2834       StrAccum str;                 /* EQP output string */
  2836   2835       char zBuf[100];               /* Initial space for EQP output string */
         2836  +    const char *zObj;
         2837  +    ExplainArg *pExplain;
         2838  +    i64 nEstRow;                  /* Estimated rows per scan of pLevel */
  2837   2839   
  2838   2840       pLoop = pLevel->pWLoop;
  2839   2841       flags = pLoop->wsFlags;
  2840   2842       if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;
  2841   2843   
         2844  +    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
         2845  +    str.db = db;
         2846  +
         2847  +    /* Reserve space at the start of the buffer managed by the StrAccum
         2848  +    ** object for *pExplain.  */
         2849  +    assert( sizeof(*pExplain)<=sizeof(zBuf) );
         2850  +    str.nChar = sizeof(*pExplain);
         2851  +
         2852  +    /* Append the object (table or index) name to the buffer. */
         2853  +    if( pItem->pSelect ){
         2854  +      zObj = "";
         2855  +    }else if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
         2856  +      zObj = pLoop->u.btree.pIndex->zName;
         2857  +    }else{
         2858  +      zObj = pItem->zName;
         2859  +    }
         2860  +    sqlite3StrAccumAppend(&str, zObj, sqlite3Strlen30(zObj)+1);
         2861  +
         2862  +    /* Append the EQP text to the buffer */
  2842   2863       isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
  2843   2864               || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
  2844   2865               || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));
  2845         -
  2846         -    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
  2847         -    str.db = db;
  2848   2866       sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN");
  2849   2867       if( pItem->pSelect ){
  2850   2868         sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId);
  2851   2869       }else{
  2852   2870         sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName);
  2853   2871       }
  2854   2872   
................................................................................
  2897   2915       }
  2898   2916   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2899   2917       else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
  2900   2918         sqlite3XPrintf(&str, 0, " VIRTUAL TABLE INDEX %d:%s",
  2901   2919                     pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
  2902   2920       }
  2903   2921   #endif
         2922  +    nEstRow = pLoop->nOut>=10 ? sqlite3LogEstToInt(pLoop->nOut) : 1;
  2904   2923   #ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
  2905         -    if( pLoop->nOut>=10 ){
  2906         -      sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
  2907         -    }else{
  2908         -      sqlite3StrAccumAppend(&str, " (~1 row)", 9);
         2924  +    sqlite3XPrintf(&str, 0, " (~%llu rows)", nEstRow);
         2925  +#endif
         2926  +    pExplain = (ExplainArg*)sqlite3StrAccumFinish(&str);
         2927  +    assert( pExplain || db->mallocFailed );
         2928  +    if( pExplain ){
         2929  +      memset(pExplain, 0, sizeof(*pExplain));
         2930  +      if( pLoop->wsFlags & WHERE_INDEXED ){
         2931  +        pExplain->iCsr = pLevel->iIdxCur;
         2932  +      }else if( pItem->pSelect==0 ){
         2933  +        pExplain->iCsr = pLevel->iTabCur;
         2934  +      }else{
         2935  +        pExplain->iCsr = -1;
         2936  +      }
         2937  +      pExplain->nEst = nEstRow;
         2938  +      pExplain->zName = (const char*)&pExplain[1];
         2939  +      pExplain->zExplain = &pExplain->zName[sqlite3Strlen30(pExplain->zName)+1];
         2940  +      sqlite3VdbeAddOp4(v, OP_Explain, 
         2941  +          iId, iLevel, iFrom, (char*)pExplain,P4_EXPLAIN
         2942  +      );
  2909   2943       }
  2910         -#endif
  2911         -    zMsg = sqlite3StrAccumFinish(&str);
  2912         -    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  2913   2944     }
  2914   2945   }
  2915   2946   #else
  2916   2947   # define explainOneScan(u,v,w,x,y,z)
  2917   2948   #endif /* SQLITE_OMIT_EXPLAIN */
  2918   2949   
  2919   2950   

Added test/scanstatus.test.

            1  +# 2014 November 1
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix scanstatus
           16  +
           17  +do_execsql_test 1.0 {
           18  +  CREATE TABLE t1(a, b);
           19  +  CREATE TABLE t2(x, y);
           20  +  INSERT INTO t1 VALUES(1, 2);
           21  +  INSERT INTO t1 VALUES(3, 4);
           22  +  INSERT INTO t2 VALUES('a', 'b');
           23  +  INSERT INTO t2 VALUES('c', 'd');
           24  +  INSERT INTO t2 VALUES('e', 'f');
           25  +}
           26  +
           27  +proc do_scanstatus_test {tn res} {
           28  +  set stmt [db_last_stmt_ptr db]
           29  +  set idx 0
           30  +  set ret [list]
           31  +  while {1} {
           32  +    set r [sqlite3_stmt_scanstatus $stmt $idx]
           33  +    if {[llength $r]==0} break
           34  +    lappend ret {*}$r
           35  +    incr idx
           36  +  }
           37  +
           38  +  uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
           39  +}
           40  +
           41  +do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6
           42  +do_scanstatus_test 1.2 {
           43  +  nLoop 1 nVisit 2 nEst 1048576 zName t1 zExplain {SCAN TABLE t1}
           44  +  nLoop 2 nVisit 6 nEst 1048576 zName t2 zExplain {SCAN TABLE t2}
           45  +}
           46  +
           47  +do_execsql_test 1.3 {
           48  +  ANALYZE;
           49  +  SELECT count(*) FROM t1, t2;
           50  +} 6
           51  +do_scanstatus_test 1.4 {
           52  +  nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           53  +  nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2}
           54  +}
           55  +
           56  +do_execsql_test 1.5 {
           57  +  ANALYZE;
           58  +  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
           59  +} 4
           60  +do_scanstatus_test 1.6 {
           61  +  nLoop 1 nVisit 2 nEst 2 zName t2 zExplain 
           62  +  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
           63  +  nLoop 2 nVisit 4 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           64  +}
           65  +
           66  +
           67  +
           68  +
           69  +finish_test