/ Check-in [340822af]
Login

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

Overview
Comment:Experimental est_count pragma.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | est_count_pragma
Files: files | file ages | folders
SHA1:340822afbe0c6b47fdd789c56acd9e1058a434cb
User & Date: drh 2016-10-20 22:02:43
Context
2016-10-21
15:36
Fix problems in the est_count pragma for indexes and WITHOUT ROWID tables. check-in: c39fd9b8 user: drh tags: est_count_pragma
2016-10-20
22:02
Experimental est_count pragma. check-in: 340822af user: drh tags: est_count_pragma
18:20
Add the ability for the PRAGMA statement to accept multiple arguments. Currently all arguments other than the first are ignored. Leaf check-in: fd81d8a4 user: drh tags: multi-arg-pragma
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5032
5033
5034
5035
5036
5037
5038































































5039
5040
5041
5042
5043
5044
5045
        pCur->curFlags &= ~BTCF_AtLast;
      }
   
    }
  }
  return rc;
}
































































/* Move the cursor so that it points to an entry near the key 
** specified by pIdxKey or intKey.   Return a success code.
**
** For INTKEY tables, the intKey parameter is used.  pIdxKey 
** must be NULL.  For index tables, pIdxKey is used and intKey
** is ignored.







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







5032
5033
5034
5035
5036
5037
5038
5039
5040
5041
5042
5043
5044
5045
5046
5047
5048
5049
5050
5051
5052
5053
5054
5055
5056
5057
5058
5059
5060
5061
5062
5063
5064
5065
5066
5067
5068
5069
5070
5071
5072
5073
5074
5075
5076
5077
5078
5079
5080
5081
5082
5083
5084
5085
5086
5087
5088
5089
5090
5091
5092
5093
5094
5095
5096
5097
5098
5099
5100
5101
5102
5103
5104
5105
5106
5107
5108
        pCur->curFlags &= ~BTCF_AtLast;
      }
   
    }
  }
  return rc;
}

/*
** Move the cursor pCur to a location within its b-tree that is
** approximately the x/1e9*nRow entry in the table, assuming the
** table contains nRow entries.  So, in other words, if x==0 move
** to the first entry and if x=1e9 move to the last entry and if
** x=5e8 move to the middle entry.  The final landing spot is
** approximate.
**
** Write an estimate of the number of entries in the b-tree into
** the *pnRowEst variable.
**
** This routine works by first moving the cursor to the root of the
** b-tree, then following pointers down to a leaf, selecting a pointer
** according to x.
**
** The estimated number of entries is found by multiplying the number of
** entries on the leaf page by the number of pointers at each layer of
** non-leaf pages.
**
** Return SQLITE_OK on success or an error code if problems are encountered.
*/
int sqlite3BtreeMovetoProportional(
  BtCursor *pCur,            /* Cursor to reposition */
  u32 x,                     /* approximate location to position the cursor */
  sqlite3_uint64 *pnRowEst   /* Write estimated entry count here */
){
  sqlite3_uint64 n = 1;
  int rc;
  Pgno chldPg;
  u32 mx = 1000000000;
  u32 perChild;
  u16 rx;
  MemPage *pPage;
  rc = moveToRoot(pCur);
  if( rc ) return rc;
  pPage = pCur->apPage[0];
  while( !pPage->leaf ){
    perChild = (mx+pPage->nCell)/(pPage->nCell+1);
    rx = x/perChild;
    x %= perChild;
    mx = perChild;
    if( rx>=pPage->nCell ){
      chldPg = get4byte(&pPage->aData[pPage->hdrOffset+8]);
    }else{
      chldPg = get4byte(findCell(pPage,rx));
    }
    n *= pPage->nCell+1;
    pCur->aiIdx[pCur->iPage] = rx;
    rc = moveToChild(pCur, chldPg);
    if( rc ) return rc;
    pPage = pCur->apPage[pCur->iPage];
  }
  *pnRowEst = n*pPage->nCell;
  if( pPage->nCell==0 ){
    rx = 0;
  }else{
    perChild = mx/pPage->nCell;
    rx = x/perChild;
  }
  pCur->aiIdx[pCur->iPage] = rx;
  return SQLITE_OK;
}

/* Move the cursor so that it points to an entry near the key 
** specified by pIdxKey or intKey.   Return a success code.
**
** For INTKEY tables, the intKey parameter is used.  pIdxKey 
** must be NULL.  For index tables, pIdxKey is used and intKey
** is ignored.

Changes to src/btree.h.

234
235
236
237
238
239
240

241
242
243
244
245
246
247
void sqlite3BtreeCursorZero(BtCursor*);
void sqlite3BtreeCursorHintFlags(BtCursor*, unsigned);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
void sqlite3BtreeCursorHint(BtCursor*, int, ...);
#endif

int sqlite3BtreeCloseCursor(BtCursor*);

int sqlite3BtreeMovetoUnpacked(
  BtCursor*,
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,
  int *pRes
);







>







234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
void sqlite3BtreeCursorZero(BtCursor*);
void sqlite3BtreeCursorHintFlags(BtCursor*, unsigned);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
void sqlite3BtreeCursorHint(BtCursor*, int, ...);
#endif

int sqlite3BtreeCloseCursor(BtCursor*);
int sqlite3BtreeMovetoProportional(BtCursor*,u32,u64*);
int sqlite3BtreeMovetoUnpacked(
  BtCursor*,
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,
  int *pRes
);

Changes to src/pragma.c.

1377
1378
1379
1380
1381
1382
1383











































1384
1385
1386
1387
1388
1389
1390
  case PragTyp_CASE_SENSITIVE_LIKE: {
    if( zRight ){
      sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0));
    }
  }
  break;












































#ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX
# define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100
#endif

#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  /* Pragma "quick_check" is reduced version of 
  ** integrity_check designed to detect most database corruption







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







1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
  case PragTyp_CASE_SENSITIVE_LIKE: {
    if( zRight ){
      sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0));
    }
  }
  break;

  /*
  **   PRAGMA est_row_cnt(<table-or-index>,<fraction>);
  **
  ** Seek in <table-or-index> through the first <fraction> of rows and
  ** estimate the total number of rows based on the path back up to the
  ** root.
  */
  case PragTyp_EST_COUNT: {
    Index *pIdx;
    Table *pTab;
    Pgno iRoot = 0;
    const char *zName = 0;
    int regResult;
    double r;
    static const char *azCol[] = { "est" };
    if( (pIdx = sqlite3FindIndex(db, zRight, zDb))!=0 ){
      iRoot = pIdx->tnum;
      zName = pIdx->zName;
    }else if( (pTab = sqlite3FindTable(db, zRight, zDb))!=0 ){
      iRoot = pTab->tnum;
      zName = pTab->zName;
    }else{
      break;
    }
    sqlite3TableLock(pParse, iDb, iRoot, 0, zName);
    regResult = ++pParse->nMem;
    setAllColumnNames(v, 1, azCol);
    if( pValues->nId>=2 ){
      const char *z = pValues->a[1].zName;
      sqlite3AtoF(z, &r, sqlite3Strlen30(z), SQLITE_UTF8);
    }else{
      r = 0.5;
    }
    if( r<0.0 ) r = 0.0;
    if( r>1.0 ) r = 1.0;
    sqlite3CodeVerifySchema(pParse, iDb);
    pParse->nTab++;
    sqlite3VdbeAddOp4Int(v, OP_OpenRead, 0, iRoot, iDb, 1);
    sqlite3VdbeAddOp3(v, OP_EstRowCnt, 0, regResult, (int)(r*1000000000));
    sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 1);
  }
  break;

#ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX
# define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100
#endif

#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  /* Pragma "quick_check" is reduced version of 
  ** integrity_check designed to detect most database corruption

Changes to src/pragma.h.

12
13
14
15
16
17
18

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
...
168
169
170
171
172
173
174




175
176
177
178
179
180
181
...
457
458
459
460
461
462
463
464
#define PragTyp_CASE_SENSITIVE_LIKE            6
#define PragTyp_COLLATION_LIST                 7
#define PragTyp_COMPILE_OPTIONS                8
#define PragTyp_DATA_STORE_DIRECTORY           9
#define PragTyp_DATABASE_LIST                 10
#define PragTyp_DEFAULT_CACHE_SIZE            11
#define PragTyp_ENCODING                      12

#define PragTyp_FOREIGN_KEY_CHECK             13
#define PragTyp_FOREIGN_KEY_LIST              14
#define PragTyp_INCREMENTAL_VACUUM            15
#define PragTyp_INDEX_INFO                    16
#define PragTyp_INDEX_LIST                    17
#define PragTyp_INTEGRITY_CHECK               18
#define PragTyp_JOURNAL_MODE                  19
#define PragTyp_JOURNAL_SIZE_LIMIT            20
#define PragTyp_LOCK_PROXY_FILE               21
#define PragTyp_LOCKING_MODE                  22
#define PragTyp_PAGE_COUNT                    23
#define PragTyp_MMAP_SIZE                     24
#define PragTyp_PAGE_SIZE                     25
#define PragTyp_SECURE_DELETE                 26
#define PragTyp_SHRINK_MEMORY                 27
#define PragTyp_SOFT_HEAP_LIMIT               28
#define PragTyp_STATS                         29
#define PragTyp_SYNCHRONOUS                   30
#define PragTyp_TABLE_INFO                    31
#define PragTyp_TEMP_STORE                    32
#define PragTyp_TEMP_STORE_DIRECTORY          33
#define PragTyp_THREADS                       34
#define PragTyp_WAL_AUTOCHECKPOINT            35
#define PragTyp_WAL_CHECKPOINT                36
#define PragTyp_ACTIVATE_EXTENSIONS           37
#define PragTyp_HEXKEY                        38
#define PragTyp_KEY                           39
#define PragTyp_REKEY                         40
#define PragTyp_LOCK_STATUS                   41
#define PragTyp_PARSER_TRACE                  42
#define PragFlag_NeedSchema           0x01
#define PragFlag_ReadOnly             0x02
static const struct sPragmaNames {
  const char *const zName;  /* Name of pragma */
  u8 ePragTyp;              /* PragTyp_XXX value */
  u8 mPragFlag;             /* Zero or more PragFlag_XXX values */
  u32 iArg;                 /* Extra argument */
................................................................................
#endif
#if !defined(SQLITE_OMIT_UTF16)
  { /* zName:     */ "encoding",
    /* ePragTyp:  */ PragTyp_ENCODING,
    /* ePragFlag: */ 0,
    /* iArg:      */ 0 },
#endif




#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  { /* zName:     */ "foreign_key_check",
    /* ePragTyp:  */ PragTyp_FOREIGN_KEY_CHECK,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FOREIGN_KEY)
................................................................................
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
  { /* zName:     */ "writable_schema",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
#endif
};
/* Number of pragmas: 60 on by default, 73 total. */







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|







 







>
>
>
>







 







|
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
...
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
462
463
464
465
466
467
468
469
#define PragTyp_CASE_SENSITIVE_LIKE            6
#define PragTyp_COLLATION_LIST                 7
#define PragTyp_COMPILE_OPTIONS                8
#define PragTyp_DATA_STORE_DIRECTORY           9
#define PragTyp_DATABASE_LIST                 10
#define PragTyp_DEFAULT_CACHE_SIZE            11
#define PragTyp_ENCODING                      12
#define PragTyp_EST_COUNT                     13
#define PragTyp_FOREIGN_KEY_CHECK             14
#define PragTyp_FOREIGN_KEY_LIST              15
#define PragTyp_INCREMENTAL_VACUUM            16
#define PragTyp_INDEX_INFO                    17
#define PragTyp_INDEX_LIST                    18
#define PragTyp_INTEGRITY_CHECK               19
#define PragTyp_JOURNAL_MODE                  20
#define PragTyp_JOURNAL_SIZE_LIMIT            21
#define PragTyp_LOCK_PROXY_FILE               22
#define PragTyp_LOCKING_MODE                  23
#define PragTyp_PAGE_COUNT                    24
#define PragTyp_MMAP_SIZE                     25
#define PragTyp_PAGE_SIZE                     26
#define PragTyp_SECURE_DELETE                 27
#define PragTyp_SHRINK_MEMORY                 28
#define PragTyp_SOFT_HEAP_LIMIT               29
#define PragTyp_STATS                         30
#define PragTyp_SYNCHRONOUS                   31
#define PragTyp_TABLE_INFO                    32
#define PragTyp_TEMP_STORE                    33
#define PragTyp_TEMP_STORE_DIRECTORY          34
#define PragTyp_THREADS                       35
#define PragTyp_WAL_AUTOCHECKPOINT            36
#define PragTyp_WAL_CHECKPOINT                37
#define PragTyp_ACTIVATE_EXTENSIONS           38
#define PragTyp_HEXKEY                        39
#define PragTyp_KEY                           40
#define PragTyp_REKEY                         41
#define PragTyp_LOCK_STATUS                   42
#define PragTyp_PARSER_TRACE                  43
#define PragFlag_NeedSchema           0x01
#define PragFlag_ReadOnly             0x02
static const struct sPragmaNames {
  const char *const zName;  /* Name of pragma */
  u8 ePragTyp;              /* PragTyp_XXX value */
  u8 mPragFlag;             /* Zero or more PragFlag_XXX values */
  u32 iArg;                 /* Extra argument */
................................................................................
#endif
#if !defined(SQLITE_OMIT_UTF16)
  { /* zName:     */ "encoding",
    /* ePragTyp:  */ PragTyp_ENCODING,
    /* ePragFlag: */ 0,
    /* iArg:      */ 0 },
#endif
  { /* zName:     */ "est_count",
    /* ePragTyp:  */ PragTyp_EST_COUNT,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  { /* zName:     */ "foreign_key_check",
    /* ePragTyp:  */ PragTyp_FOREIGN_KEY_CHECK,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FOREIGN_KEY)
................................................................................
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
  { /* zName:     */ "writable_schema",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
#endif
};
/* Number of pragmas: 61 on by default, 74 total. */

Changes to src/vdbe.c.

4892
4893
4894
4895
4896
4897
4898

























4899
4900
4901
4902
4903
4904
4905
  if( rc ) goto abort_due_to_error;
  pC->nullRow = (u8)res;
  assert( pOp->p2>0 && pOp->p2<p->nOp );
  VdbeBranchTaken(res!=0,2);
  if( res ) goto jump_to_p2;
  break;
}


























/* Opcode: Next P1 P2 P3 P4 P5
**
** Advance cursor P1 so that it points to the next key/data pair in its
** table or index.  If there are no more key/value pairs then fall through
** to the following instruction.  But if the cursor advance was successful,
** jump immediately to P2.







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







4892
4893
4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904
4905
4906
4907
4908
4909
4910
4911
4912
4913
4914
4915
4916
4917
4918
4919
4920
4921
4922
4923
4924
4925
4926
4927
4928
4929
4930
  if( rc ) goto abort_due_to_error;
  pC->nullRow = (u8)res;
  assert( pOp->p2>0 && pOp->p2<p->nOp );
  VdbeBranchTaken(res!=0,2);
  if( res ) goto jump_to_p2;
  break;
}

/*
** Opcode: EstRowCnt P1 P2 P3 * * *
**
** Estimate the number of entries in btree for cursor P1 do a proportional
** seek to of P3.  Store the result as a floating point value in P2.
*/
case OP_EstRowCnt: {  /* out2 */
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int rc;
  sqlite3_uint64 n = 0;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC->eCurType==CURTYPE_BTREE );
  pCrsr = pC->uc.pCursor;
  assert( pCrsr );
  rc = sqlite3BtreeMovetoProportional(pCrsr, pOp->p3, &n);
  if( rc ) goto abort_due_to_error;
  pOut = out2Prerelease(p, pOp);
  pOut->flags = MEM_Real;
  pOut->u.r = n;
  break;
}

/* Opcode: Next P1 P2 P3 P4 P5
**
** Advance cursor P1 so that it points to the next key/data pair in its
** table or index.  If there are no more key/value pairs then fall through
** to the following instruction.  But if the cursor advance was successful,
** jump immediately to P2.

Changes to tool/mkpragmatab.tcl.

247
248
249
250
251
252
253



254
255
256
257
258
259
260
  IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)

  NAME: quick_check
  TYPE: INTEGRITY_CHECK
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)




  NAME: encoding
  IF:   !defined(SQLITE_OMIT_UTF16)

  NAME: schema_version
  TYPE: HEADER_VALUE
  ARG:  BTREE_SCHEMA_VERSION
  IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)







>
>
>







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
  IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)

  NAME: quick_check
  TYPE: INTEGRITY_CHECK
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_INTEGRITY_CHECK)

  NAME: est_count
  FLAG: NeedSchema

  NAME: encoding
  IF:   !defined(SQLITE_OMIT_UTF16)

  NAME: schema_version
  TYPE: HEADER_VALUE
  ARG:  BTREE_SCHEMA_VERSION
  IF:   !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)