SQLite

Check-in [7955342da4]
Login

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

Overview
Comment:Refactor the interface to make it more easily extensible.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: 7955342da4a35b57e4ae26690b8d40f7bba20e8f
User & Date: drh 2014-11-03 16:35:55.977
Context
2014-11-03
16:39
Fix a typo preventing this from building with SQLITE_ENABLE_STMT_SCANSTATUS defined. (Closed-Leaf check-in: 4c5714ab3d user: dan tags: scanstatus)
16:35
Refactor the interface to make it more easily extensible. (check-in: 7955342da4 user: drh tags: scanstatus)
15:33
Add further tests. Fixes so that compilation without ENABLE_STMT_SCANSTATUS works. (check-in: a2303c7192 user: dan tags: scanstatus)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/sqlite.h.in.
7402
7403
7404
7405
7406
7407
7408






































7409
7410
7411
7412
7413
7414





7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
7429
7430
7431
7432
7433
7434
7435
7436
7437
7438
7439
7440
7441

7442
7443
7444
7445
7446
7447
7448
7449
7450
7451
7452
7453
7454

7455
7456
7457
7458
7459
7460
7461
7462
7463
7464
7465
7466
7467
7468
7469
7470
7471
*/
#define SQLITE_ROLLBACK 1
/* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
#define SQLITE_FAIL     3
/* #define SQLITE_ABORT 4  // Also an error code */
#define SQLITE_REPLACE  5








































/*
** CAPI3REF: Prepared Statement Scan Statuses
**
** Return status data for a single loop within query pStmt.
**





** Parameter "idx" identifies the specific loop to retrieve statistics for.
** Loops are numbered starting from zero. If idx is out of range - less than
** zero or greater than or equal to the total number of loops used to implement
** the statement - a non-zero value is returned. In this case the final value
** of all five output parameters is undefined. Otherwise, if idx is in range,
** zero is returned and the output parameters set as follows:
**
** <ul>
**   <li> (*pnLoop) is set to the total number of times the loop has been run.
**   <li> (*pnVisit) is set to the total number of rows visited by the loop.
**   <li> (*pnEst) is set to the estimate of the number of rows visited
**        by each run of the loop used by the SQL optimizer. Ideally, this
**        value should be close to (*pnVisit)/(*pnLoop).
**   <li> (*pzName) is set to point to a nul-terminated string containing the
**        name of the index of table used by this loop.
**   <li> (*pzExplain) is set to point to a nul-terminated string containing 
**        same text that would be returned for this loop by an EXPLAIN
**        QUERY PLAN command.
** </ul>
**
** Output parameters *pzName and *pzExplain are set to point to buffers 
** managed by the statement object. Both of these pointers may be invalidated
** by any API call on the same statement object, including an sqlite3_step()
** sqlite3_bind_*() call.
**
** Statistics may not be available for all loops in all statements. In cases
** where there exist loops with no available statistics, this function ignores

** them completely.
**
** This API is only available if the library is built with pre-processor
** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
*/
SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,
  int idx,                        /* Index of loop to report on */
  sqlite3_int64 *pnLoop,          /* OUT: Number of times loop was run */
  sqlite3_int64 *pnVisit,         /* OUT: Number of rows visited (all loops) */
  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
  const char **pzName,            /* OUT: Object name (table or index) */
  const char **pzExplain          /* OUT: EQP string */

);


/*
** CAPI3REF: Zero Scan-Status Counters
**
** Zero all sqlite3_stmt_scanstatus() related event counters.
**
** This API is only available if the library is built with pre-processor
** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
*/
SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*);


/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


|



>
>
>
>
>



|
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<

<
<
<
<
<
|
|
>
|


|


|
|
|
<
<
<
<
>
|
<







|







7402
7403
7404
7405
7406
7407
7408
7409
7410
7411
7412
7413
7414
7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
7429
7430
7431
7432
7433
7434
7435
7436
7437
7438
7439
7440
7441
7442
7443
7444
7445
7446
7447
7448
7449
7450
7451
7452
7453
7454
7455
7456
7457
7458
7459
7460
7461



7462











7463





7464
7465
7466
7467
7468
7469
7470
7471
7472
7473
7474
7475




7476
7477

7478
7479
7480
7481
7482
7483
7484
7485
7486
7487
7488
7489
7490
7491
7492
*/
#define SQLITE_ROLLBACK 1
/* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
#define SQLITE_FAIL     3
/* #define SQLITE_ABORT 4  // Also an error code */
#define SQLITE_REPLACE  5

/* CAPI3REF: Prepared Statement Scan Status Opcodes
** KEYWORDS: {scanstatus option}
**
** The following constants can be used for the T parameter to the
** [sqlite3_stmt_scanstatus(S,X,T,V)] interface.  Each constant designates a
** different metric for sqlite3_stmt_scanstatus() to return.
**
** <dl>
** [[SQLITE_SCANSTAT_NLOOP]] <td>SQLITE_SCANSTAT_NLOOP</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** total number of times that the X-th loop has run.</dd>
**
** [[SQLITE_SCANSTAT_NVISIT]] <td>SQLITE_SCANSTAT_NVISIT</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** total number of rows visited by the X-th loop.</dd>
**
** [[SQLITE_SCANSTAT_EST]] <td>SQLITE_SCANSTAT_EST</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** query planner's estimate for the number of rows visited for each
** iteration of the X-th loop.  If the query planner's estimate was accurate,
** then this value should be approximately NVISIT/NLOOP.
**
** [[SQLITE_SCANSTAT_NAME]] <td>SQLITE_SCANSTAT_NAME</dt>
** <dd>The "const char *" variable pointed to by the T parameter will be set to 
** a zero-terminated UTF-8 string containing the name of the index or table used
** for the X-th loop.
**
** [[SQLITE_SCANSTAT_EXPLAIN]] <td>SQLITE_SCANSTAT_EXPLAIN</dt>
** <dd>The "const char *" variable pointed to by the T parameter will be set to 
** a zero-terminated UTF-8 string containing the [EXPLAIN QUERY PLAN] description
** for the X-th loop.
** </dl>
*/
#define SQLITE_SCANSTAT_NLOOP    0
#define SQLITE_SCANSTAT_NVISIT   1
#define SQLITE_SCANSTAT_NEST     2
#define SQLITE_SCANSTAT_NAME     3
#define SQLITE_SCANSTAT_EXPLAIN  4

/*
** CAPI3REF: Prepared Statement Scan Status
**
** Return status data for a single loop within query pStmt.
**
** The "iScanStatusOp" parameter determines which status information to return.
** The "iScanStatusOp" must be one of the [scanstatus options] or the behavior of
** this interface is undefined.
** The requested measurement is written into a variable pointed to by
** the "pOut" parameter.
** Parameter "idx" identifies the specific loop to retrieve statistics for.
** Loops are numbered starting from zero. If idx is out of range - less than
** zero or greater than or equal to the total number of loops used to implement
** the statement - a non-zero value is returned and the variable that pOut



** points to is unchanged.











**





** Statistics might not be available for all loops in all statements. In cases
** where there exist loops with no available statistics, this function behaves
** as if the loop did not exist - it returns non-zero and leave the variable
** that pOut points to unchanged.
**
** This API is only available if the library is built with pre-processor
** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
*/
SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,      /* Prepared statement for which info desired */
  int idx,                  /* Index of loop to report on */
  int iScanStatusOp,        /* Information desired.  SQLITE_SCANSTAT_* */




  void *pOut                /* Result written here */
);     


/*
** CAPI3REF: Zero Scan-Status Counters
**
** Zero all sqlite3_stmt_scanstatus() related event counters.
**
** This API is only available if the library is built with pre-processor
** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
*/
SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*);


/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
Changes to src/test1.c.
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337

2338
2339

2340
2341

2342
2343

2344
2345
2346
2347
2348
2349
2350
  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
    return TCL_ERROR;
  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;

  res = sqlite3_stmt_scanstatus(
      pStmt, idx, &nLoop, &nVisit, &nEst, &zName, &zExplain
  );
  if( res==0 ){
    Tcl_Obj *pRet = Tcl_NewObj();
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));

    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));

    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));

    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));

    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zExplain, -1));
    Tcl_SetObjResult(interp, pRet);
  }else{
    Tcl_ResetResult(interp);
  }
  return TCL_OK;







|
<
<




>


>


>


>







2324
2325
2326
2327
2328
2329
2330
2331


2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
    return TCL_ERROR;
  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;

  res = sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop);


  if( res==0 ){
    Tcl_Obj *pRet = Tcl_NewObj();
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EST, (void*)&nEst);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NAME, (void*)&zName);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zExplain, -1));
    Tcl_SetObjResult(interp, pRet);
  }else{
    Tcl_ResetResult(interp);
  }
  return TCL_OK;
Changes to src/vdbeapi.c.
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495


1496



1497



1498



1499
1500



1501
1502
1503
1504





1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
}

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
/*
** Return status data for a single loop within query pStmt.
*/
int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,
  int idx,                        /* Index of loop to report on */
  sqlite3_int64 *pnLoop,          /* OUT: Number of times loop was run */
  sqlite3_int64 *pnVisit,         /* OUT: Number of rows visited (all loops) */
  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
  const char **pzName,            /* OUT: Object name (table or index) */
  const char **pzExplain          /* OUT: EQP string */
){
  Vdbe *p = (Vdbe*)pStmt;
  ScanStatus *pScan;
  if( idx<0 || idx>=p->nScan ) return 1;
  pScan = &p->aScan[idx];


  if( pnLoop ) *pnLoop = p->anExec[pScan->addrLoop];



  if( pnVisit ) *pnVisit = p->anExec[pScan->addrVisit];



  if( pnEst ) *pnEst = pScan->nEst;



  if( *pzName ) *pzName = pScan->zName;
  if( *pzExplain ){



    if( pScan->addrExplain ){
      *pzExplain = p->aOp[ pScan->addrExplain ].p4.z;
    }else{
      *pzExplain = 0;





    }
  }
  return 0;
}

/*
** Zero all counters associated with the sqlite3_stmt_scanstatus() data.
*/
void sqlite3_stmt_scanstatus_reset(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe*)pStmt;
  memset(p->anExec, 0, p->nOp * sizeof(i64));
}
#endif /* SQLITE_ENABLE_STMT_SCANSTATUS */








|

<
<
<
|
|





>
>
|
>
>
>
|
>
>
>
|
>
>
>
|
<
>
>
>
|
|
|
|
>
>
>
>
>













<
1477
1478
1479
1480
1481
1482
1483
1484
1485



1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507

1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532

}

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
/*
** Return status data for a single loop within query pStmt.
*/
int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,            /* Prepared statement being queried */
  int idx,                        /* Index of loop to report on */



  int iScanStatusOp,              /* Which metric to return */
  void *pOut                      /* OUT: Write the answer here */
){
  Vdbe *p = (Vdbe*)pStmt;
  ScanStatus *pScan;
  if( idx<0 || idx>=p->nScan ) return 1;
  pScan = &p->aScan[idx];
  switch( iScanStatusOp ){
    case SQLITE_SCANSTAT_NLOOP: {
      *(sqlite3_int64*)pOut = p->anExec[pScan->addrLoop];
      break;
    }
    case SQLITE_SCANSTAT_NVISIT: {
      *(sqlite3_int64*)pOut = p->anExec[pScan->addrVisit];
      break;
    }
    case SQLITE_SCANSTAT_EST: {
      *(sqlite3_int64*)pOut = pScan->nEst;
      break;
    }
    case SQLITE_SCANSTAT_NAME: {
      *(const char**)pOut = pScan->zName

      break;
    }
    case SQLITE_SCANSTAT_EXPLAIN: {
      if( pScan->addrExplain ){
        *(const char**)pOut = p->aOp[ pScan->addrExplain ].p4.z;
      }else{
        *(const char**)pOut = 0;
      }
      break;
    }
    default: {
      return 1;
    }
  }
  return 0;
}

/*
** Zero all counters associated with the sqlite3_stmt_scanstatus() data.
*/
void sqlite3_stmt_scanstatus_reset(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe*)pStmt;
  memset(p->anExec, 0, p->nOp * sizeof(i64));
}
#endif /* SQLITE_ENABLE_STMT_SCANSTATUS */