Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Begin adding experimental sqlite_stat4 table. This commit is buggy. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sqlite_stat4 |
Files: | files | file ages | folders |
SHA1: |
2beea303a1d609cd2ff252412c50b966 |
User & Date: | dan 2013-08-03 20:24:58.082 |
Context
2013-08-05
| ||
05:34 | Fix a couple of problems in code related to sqlite_stat4. (check-in: badd24d987 user: dan tags: sqlite_stat4) | |
2013-08-03
| ||
20:24 | Begin adding experimental sqlite_stat4 table. This commit is buggy. (check-in: 2beea303a1 user: dan tags: sqlite_stat4) | |
2013-08-02
| ||
23:40 | Updates to requirements marks. No code changes. (check-in: 213020769f user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
115 116 117 118 119 120 121 | */ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" /* ** This routine generates code that opens the sqlite_stat1 table for ** writing with cursor iStatCur. If the library was built with the | | | | | | | | 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | */ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" /* ** This routine generates code that opens the sqlite_stat1 table for ** writing with cursor iStatCur. If the library was built with the ** SQLITE_ENABLE_STAT4 macro defined, then the sqlite_stat4 table is ** opened for writing using cursor (iStatCur+1) ** ** If the sqlite_stat1 tables does not previously exist, it is created. ** Similarly, if the sqlite_stat4 table does not exist and the library ** is compiled with SQLITE_ENABLE_STAT4 defined, it is created. ** ** Argument zWhere may be a pointer to a buffer containing a table name, ** or it may be a NULL pointer. If it is not NULL, then all entries in ** the sqlite_stat1 and (if applicable) sqlite_stat4 tables associated ** with the named table are deleted. If zWhere==0, then code is generated ** to delete all stat table entries. */ static void openStatTable( Parse *pParse, /* Parsing context */ int iDb, /* The database we are looking in */ int iStatCur, /* Open the sqlite_stat1 table on this cursor */ const char *zWhere, /* Delete entries for this table or index */ const char *zWhereType /* Either "tbl" or "idx" */ ){ static const struct { const char *zName; const char *zCols; } aTable[] = { { "sqlite_stat1", "tbl,idx,stat" }, #ifdef SQLITE_ENABLE_STAT4 { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" }, #endif }; int aRoot[] = {0, 0}; u8 aCreateTbl[] = {0, 0}; int i; |
︙ | ︙ | |||
190 191 192 193 194 195 196 | }else{ /* The sqlite_stat[12] table already exists. Delete all rows. */ sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb); } } } | | | | | | | | | > | > | | | | | | | | < | > | | > | | > | > > > | > | > > | > > > > > > > > > > > > | | | | | | | | > > > > > > > > | | | < | > > > | | | > > > | | < > > > > > > > > > | > > > > | | < | > | | < > > < < < > > > > > | > < | < < | | | < < < | | | | | | > | | | | | > < | > > > | > | > > > > > | > > | > > > > > > > > > | | | > | | | | | 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 | }else{ /* The sqlite_stat[12] table already exists. Delete all rows. */ sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb); } } } /* Open the sqlite_stat[14] tables for writing. */ for(i=0; i<ArraySize(aTable); i++){ sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb); sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32); sqlite3VdbeChangeP5(v, aCreateTbl[i]); } } /* ** Recommended number of samples for sqlite_stat4 */ #ifndef SQLITE_STAT4_SAMPLES # define SQLITE_STAT4_SAMPLES 24 #endif /* ** Three SQL functions - stat4_init(), stat4_push(), and stat4_pop() - ** share an instance of the following structure to hold their state ** information. */ typedef struct Stat4Accum Stat4Accum; struct Stat4Accum { tRowcnt nRow; /* Number of rows in the entire table */ tRowcnt nPSample; /* How often to do a periodic sample */ int iMin; /* Index of entry with minimum nSumEq and hash */ int mxSample; /* Maximum number of samples to accumulate */ int nSample; /* Current number of samples */ int nCol; /* Number of columns in the index */ u32 iPrn; /* Pseudo-random number used for sampling */ struct Stat4Sample { i64 iRowid; /* Rowid in main table of the key */ tRowcnt nSumEq; /* Sum of anEq[] values */ tRowcnt *anEq; /* sqlite_stat4.nEq */ tRowcnt *anLt; /* sqlite_stat4.nLt */ tRowcnt *anDLt; /* sqlite_stat4.nDLt */ u8 isPSample; /* True if a periodic sample */ u32 iHash; /* Tiebreaker hash */ } *a; /* An array of samples */ }; #ifdef SQLITE_ENABLE_STAT4 /* ** Implementation of the stat4_init(C,N,S) SQL function. The three parameters ** are the number of rows in the table or index (C), the number of columns ** in the index (N) and the number of samples to accumulate (S). ** ** This routine allocates the Stat4Accum object in heap memory. The return ** value is a pointer to the the Stat4Accum object encoded as a blob (i.e. ** the size of the blob is sizeof(void*) bytes). */ static void stat4Init( sqlite3_context *context, int argc, sqlite3_value **argv ){ Stat4Accum *p; u8 *pSpace; /* Allocated space not yet assigned */ tRowcnt nRow; /* Number of rows in table (C) */ int mxSample; /* Maximum number of samples collected */ int nCol; /* Number of columns in index being sampled */ int n; /* Bytes of space to allocate */ int i; /* Used to iterate through p->aSample[] */ /* Decode the three function arguments */ UNUSED_PARAMETER(argc); nRow = (tRowcnt)sqlite3_value_int64(argv[0]); nCol = sqlite3_value_int(argv[1]); mxSample = sqlite3_value_int(argv[2]); assert( nCol>0 ); /* Allocate the space required for the Stat4Accum object */ n = sizeof(*p) + (sizeof(p->a[0]) + 3*sizeof(tRowcnt)*nCol)*mxSample; p = sqlite3MallocZero( n ); if( p==0 ){ sqlite3_result_error_nomem(context); return; } /* Populate the new Stat4Accum object */ p->nRow = nRow; p->nCol = nCol; p->mxSample = mxSample; p->nPSample = p->nRow/(mxSample/3+1) + 1; sqlite3_randomness(sizeof(p->iPrn), &p->iPrn); p->a = (struct Stat4Sample*)&p[1]; pSpace = (u8*)(&p->a[mxSample]); for(i=0; i<mxSample; i++){ p->a[i].anEq = (tRowcnt *)pSpace; pSpace += (sizeof(tRowcnt) * nCol); p->a[i].anLt = (tRowcnt *)pSpace; pSpace += (sizeof(tRowcnt) * nCol); p->a[i].anDLt = (tRowcnt *)pSpace; pSpace += (sizeof(tRowcnt) * nCol); } assert( (pSpace - (u8*)p)==n ); /* Return a pointer to the allocated object to the caller */ sqlite3_result_blob(context, p, sizeof(p), sqlite3_free); } static const FuncDef stat4InitFuncdef = { 3, /* nArg */ SQLITE_UTF8, /* iPrefEnc */ 0, /* flags */ 0, /* pUserData */ 0, /* pNext */ stat4Init, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ "stat4_init", /* zName */ 0, /* pHash */ 0 /* pDestructor */ }; /* ** Implementation of the stat4_push SQL function. The arguments describe a ** single key instance. This routine makes the decision about whether or ** not to retain this key for the sqlite_stat4 table. ** ** The calling convention is: ** ** stat4_push(P, rowid, ...nEq args..., ...nLt args..., ...nDLt args...) ** ** where each instance of the "...nXX args..." is replaced by an array of ** nCol arguments, where nCol is the number of columns in the index being ** sampled (if the index being sampled is "CREATE INDEX i ON t(a, b)", a ** total of 8 arguments are passed when this function is invoked). ** ** The return value is always NULL. */ static void stat4Push( sqlite3_context *context, int argc, sqlite3_value **argv ){ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); i64 rowid = sqlite3_value_int64(argv[1]); i64 nSumEq = 0; /* Sum of all nEq parameters */ struct Stat4Sample *pSample; u32 h; int iMin = p->iMin; int i; u8 isPSample = 0; u8 doInsert = 0; sqlite3_value **aEq = &argv[2]; sqlite3_value **aLt = &argv[2+p->nCol]; sqlite3_value **aDLt = &argv[2+p->nCol+p->nCol]; i64 nEq = sqlite3_value_int64(aEq[p->nCol-1]); i64 nLt = sqlite3_value_int64(aLt[p->nCol-1]); UNUSED_PARAMETER(context); UNUSED_PARAMETER(argc); assert( p->nCol>0 ); assert( argc==(2 + 3*p->nCol) ); /* Set nSumEq to the sum of all nEq parameters. */ for(i=0; i<p->nCol; i++){ nSumEq += sqlite3_value_int64(aEq[i]); } if( nSumEq==0 ) return; /* Figure out if this sample will be used. Set isPSample to true if this ** is a periodic sample, or false if it is being captured because of a ** large nSumEq value. If the sample will not be used, return early. */ h = p->iPrn = p->iPrn*1103515245 + 12345; if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){ doInsert = isPSample = 1; }else if( (p->nSample<p->mxSample) || (nSumEq>p->a[iMin].nSumEq) || (nSumEq==p->a[iMin].nSumEq && h>p->a[iMin].iHash) ){ doInsert = 1; } if( !doInsert ) return; /* Fill in the new Stat4Sample object. */ if( p->nSample==p->mxSample ){ assert( p->nSample - iMin - 1 >= 0 ); memmove(&p->a[iMin], &p->a[iMin+1], sizeof(p->a[0])*(p->nSample-iMin-1)); pSample = &p->a[p->nSample-1]; }else{ pSample = &p->a[p->nSample++]; } pSample->iRowid = rowid; pSample->iHash = h; pSample->isPSample = isPSample; pSample->nSumEq = nSumEq; for(i=0; i<p->nCol; i++){ pSample->anEq[i] = sqlite3_value_int64(aEq[i]); pSample->anLt[i] = sqlite3_value_int64(aLt[i]); pSample->anDLt[i] = sqlite3_value_int64(aDLt[i]); } /* Find the new minimum */ if( p->nSample==p->mxSample ){ u32 iHash = 0; /* Hash corresponding to iMin/nSumEq entry */ i64 nMinEq = LARGEST_INT64; /* Smallest nSumEq seen so far */ assert( iMin = -1 ); for(i=0; i<p->mxSample; i++){ if( p->a[i].isPSample ) continue; if( (p->a[i].nSumEq<nMinEq) || (p->a[i].nSumEq==nMinEq && p->a[i].iHash<iHash) ){ iMin = i; nMinEq = p->a[i].nSumEq; iHash = p->a[i].iHash; } } assert( iMin>=0 ); p->iMin = iMin; } } static const FuncDef stat4PushFuncdef = { -1, /* nArg */ SQLITE_UTF8, /* iPrefEnc */ 0, /* flags */ 0, /* pUserData */ 0, /* pNext */ stat4Push, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ "stat4_push", /* zName */ 0, /* pHash */ 0 /* pDestructor */ }; /* ** Implementation of the stat3_get(P,N,...) SQL function. This routine is ** used to query the results. Content is returned for the Nth sqlite_stat3 ** row where N is between 0 and S-1 and S is the number of samples. The ** value returned depends on the number of arguments. ** ** argc==2 result: rowid ** argc==3 result: nEq ** argc==4 result: nLt ** argc==5 result: nDLt */ static void stat4Get( sqlite3_context *context, int argc, sqlite3_value **argv ){ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); int n = sqlite3_value_int(argv[1]); assert( p!=0 ); if( n<p->nSample ){ tRowcnt *aCnt = 0; char *zRet; switch( argc ){ case 2: sqlite3_result_int64(context, p->a[n].iRowid); return; case 3: aCnt = p->a[n].anEq; break; case 4: aCnt = p->a[n].anLt; break; default: aCnt = p->a[n].anDLt; break; } zRet = sqlite3MallocZero(p->nCol * 25); if( zRet==0 ){ sqlite3_result_error_nomem(context); }else{ int i; char *z = zRet; for(i=0; i<p->nCol; i++){ sqlite3_snprintf(24, z, "%lld ", aCnt[i]); z += sqlite3Strlen30(z); } assert( z[0]=='\0' && z>zRet ); z[-1] = '\0'; sqlite3_result_text(context, zRet, -1, sqlite3_free); } } } static const FuncDef stat4GetFuncdef = { -1, /* nArg */ SQLITE_UTF8, /* iPrefEnc */ 0, /* flags */ 0, /* pUserData */ 0, /* pNext */ stat4Get, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ "stat4_get", /* zName */ 0, /* pHash */ 0 /* pDestructor */ }; #endif /* SQLITE_ENABLE_STAT4 */ /* ** Generate code to do an analysis of all indices associated with ** a single table. |
︙ | ︙ | |||
441 442 443 444 445 446 447 | int endOfLoop; /* The end of the loop */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ u8 needTableCnt = 1; /* True to count the table */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regStat1 = iMem++; /* The stat column of sqlite_stat1 */ | | | > | 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 | int endOfLoop; /* The end of the loop */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ u8 needTableCnt = 1; /* True to count the table */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regStat1 = iMem++; /* The stat column of sqlite_stat1 */ #ifdef SQLITE_ENABLE_STAT4 int regNumEq = regStat1; /* Number of instances. Same as regStat1 */ int regNumLt = iMem++; /* Number of keys less than regSample */ int regNumDLt = iMem++; /* Number of distinct keys less than regSample */ int regSample = iMem++; /* The next sample value */ int regRowid = regSample; /* Rowid of a sample */ int regAccum = iMem++; /* Register to hold Stat4Accum object */ int regLoop = iMem++; /* Loop counter */ int regCount = iMem++; /* Number of rows in the table or index */ int regTemp1 = iMem++; /* Intermediate register */ int regTemp2 = iMem++; /* Intermediate register */ int once = 1; /* One-time initialization */ int shortJump = 0; /* Instruction address */ int iTabCur = pParse->nTab++; /* Table cursor */ #endif int regCol = iMem++; /* Content of a column in analyzed table */ int regRec = iMem++; /* Register holding completed record */ int regTemp = iMem++; /* Temporary use register */ int regNewRowid = iMem++; /* Rowid for the inserted record */ int regStat4 = iMem++; /* Register to hold Stat4Accum object */ v = sqlite3GetVdbe(pParse); if( v==0 || NEVER(pTab==0) ){ return; } if( pTab->tnum==0 ){ /* Do not gather statistics on views or virtual tables */ |
︙ | ︙ | |||
491 492 493 494 495 496 497 | /* Establish a read-lock on the table at the shared-cache level. */ sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); iIdxCur = pParse->nTab++; sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ | | | | | > > > > > > > > > < < < | | | > > > > > | | | | < | | | | > > > > > | | | | > > | > > > | > > | | > | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > | < < | > | < < < | | | > > | < > | | | < | > | > > > > > > > > > < < < < > | | | > | < | | < | > > > > | > > | < | > | | | > > > > > > > > > > > > > < | | | > > > > > | > | | | > | | > | | > | 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 | /* Establish a read-lock on the table at the shared-cache level. */ sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); iIdxCur = pParse->nTab++; sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int nCol; /* Number of columns indexed by pIdx */ KeyInfo *pKey; /* KeyInfo structure for pIdx */ int addrIfNot = 0; /* address of OP_IfNot */ int *aChngAddr; /* Array of jump instruction addresses */ int regRowid; /* Register for rowid of current row */ int regPrev; /* First in array of previous values */ int regDLt; /* First in array of nDlt registers */ int regLt; /* First in array of nLt registers */ int regEq; /* First in array of nEq registers */ int regCnt; /* Number of index entries */ int addrGoto; if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); nCol = pIdx->nColumn; aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol); if( aChngAddr==0 ) continue; pKey = sqlite3IndexKeyinfo(pParse, pIdx); /* Open a cursor to the index to be analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb, (char *)pKey, P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); /* Populate the register containing the index name. */ sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); #ifdef SQLITE_ENABLE_STAT4 if( once ){ once = 0; sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead); } /* Invoke the stat4_init() function. The arguments are: ** ** * the number of rows in the index, ** * the number of columns in the index, ** * the recommended number of samples for the stat4 table. */ sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+1); sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat4+2); sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT4_SAMPLES, regStat4+3); sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4+1, regStat4); sqlite3VdbeChangeP4(v, -1, (char*)&stat4InitFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 3); #endif /* SQLITE_ENABLE_STAT4 */ /* The block of (1 + 4*nCol) memory cells initialized here is used ** as follows: ** ** TODO: Update this comment: ** ** iMem: ** Loop counter. The number of rows visited so far, including ** the current row (i.e. this register is set to 1 for the ** first iteration of the loop). ** ** iMem+1 .. iMem+nCol: ** Number of distinct index entries seen so far, considering ** the left-most N columns only, where N is between 1 and nCol, ** inclusive. ** ** iMem+nCol+1 .. Mem+2*nCol: ** Previous value of indexed columns, from left to right. ** ** Cells iMem through iMem+nCol are initialized to 0. The others are ** initialized to contain an SQL NULL. */ regRowid = regStat4+1; /* Rowid argument */ regEq = regRowid+1; /* First in array of nEq value registers */ regLt = regEq+nCol; /* First in array of nLt value registers */ regDLt = regLt+nCol; /* First in array of nDLt value registers */ regCnt = regDLt+nCol; /* Row counter */ regPrev = regCnt+1; /* First in array of prev. value registers */ if( regPrev+1>pParse->nMem ){ pParse->nMem = regPrev+1; } for(i=0; i<2+nCol*4; i++){ sqlite3VdbeAddOp2(v, OP_Integer, 0, regRowid+i); } /* ** Loop through all entries in the b-tree index. Pseudo-code for the ** body of the loop is as follows: ** ** foreach i IN index { ** regCnt += 1 ** ** if( regEq(0)==0 ) goto ne_0; ** ** if i(0) != regPrev(0) { ** stat4_push(regRowid, regEq, regLt, regDLt); ** goto ne_0; ** } ** regEq(0) += 1 ** ** if i(1) != regPrev(1){ ** stat4_push(regRowid, regEq, regLt, regDLt); ** goto ne_1; ** } ** regEq(1) += 1 ** ** goto all_eq; ** ** ne_0: ** regPrev(0) = i(0) ** if( regEq(0) != 0 ) regDLt(0) += 1 ** regLt(0) += regEq(0) ** regEq(0) = 1 ** ** ne_1: ** regPrev(1) = $i(1) ** if( regEq(1) != 0 ) regDLt(1) += 1 ** regLt(1) += regEq(1) ** regEq(1) = 1 ** ** all_eq: ** regRowid = i(rowid) ** } ** ** stat4_push(regRowid, regEq, regLt, regDLt); ** ** if( regEq(0) != 0 ) regDLt(0) += 1 ** if( regEq(1) != 0 ) regDLt(1) += 1 ** ** The last two lines above modify the contents of the regDLt array ** so that each element contains the number of distinct key prefixes ** of the corresponding length. As required to calculate the contents ** of the sqlite_stat1 entry. ** ** Note: if regEq(0)==0, stat4_push() is a no-op. */ endOfLoop = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, regCnt, 1); /* Increment row counter */ /* This jump is taken for the first iteration of the loop only. ** ** if( regEq(0)==0 ) goto ne_0; */ addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, regEq); /* Code these bits: ** ** if i(N) != regPrev(N) { ** stat4_push(regRowid, regEq, regLt, regDLt); ** goto ne_N; ** } ** regEq(N) += 1 */ for(i=0; i<nCol; i++){ char *pColl; /* Pointer to CollSeq cast to (char*) */ assert( pIdx->azColl && pIdx->azColl[i]!=0 ); pColl = (char*)sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); sqlite3VdbeAddOp4(v, OP_Eq, regCol, 0, regPrev+i, pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regTemp2); sqlite3VdbeChangeP4(v, -1, (char*)&stat4PushFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2 + 3*nCol); aChngAddr[i] = sqlite3VdbeAddOp0(v, OP_Goto); VdbeComment((v, "jump if column %d changed", i)); sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-3); sqlite3VdbeAddOp2(v, OP_AddImm, regEq+i, 1); VdbeComment((v, "incr repeat count")); } /* Code the "continue" */ addrGoto = sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop); /* And now these: ** ** ne_N: ** regPrev(N) = i(N) ** if( regEq(N) != N ) regDLt(N) += 1 ** regLt(N) += regEq(N) ** regEq(N) = 1 */ for(i=0; i<nCol; i++){ sqlite3VdbeJumpHere(v, aChngAddr[i]); /* Set jump dest for the OP_Ne */ if( i==0 ){ sqlite3VdbeJumpHere(v, addrIfNot); /* Jump dest for OP_IfNot */ } sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regPrev+i); sqlite3VdbeAddOp2(v, OP_IfNot, regEq+i, sqlite3VdbeCurrentAddr(v)+2); sqlite3VdbeAddOp2(v, OP_AddImm, regDLt+i, 1); sqlite3VdbeAddOp3(v, OP_Add, regEq+i, regLt+i, regLt+i); sqlite3VdbeAddOp2(v, OP_Integer, 1, regEq+i); } sqlite3DbFree(db, aChngAddr); /* ** all_eq: ** regRowid = i(rowid) */ sqlite3VdbeJumpHere(v, addrGoto); sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regRowid); /* The end of the loop that iterates through all index entries. Always ** jump here after updating the iMem+1...iMem+1+nCol counters. */ sqlite3VdbeResolveLabel(v, endOfLoop); sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); /* Final invocation of stat4_push() */ sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regTemp2); sqlite3VdbeChangeP4(v, -1, (char*)&stat4PushFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2 + 3*nCol); /* Finally: ** ** if( regEq(0) != 0 ) regDLt(0) += 1 */ for(i=0; i<nCol; i++){ sqlite3VdbeAddOp2(v, OP_IfNot, regEq+i, sqlite3VdbeCurrentAddr(v)+2); sqlite3VdbeAddOp2(v, OP_AddImm, regDLt+i, 1); } #ifdef SQLITE_ENABLE_STAT4 /* Add rows to the sqlite_stat4 table */ regLoop = regStat4+1; sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop); shortJump = sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1); sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4, regTemp1); sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2); sqlite3VdbeAddOp1(v, OP_IsNull, regTemp1); sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regTemp1); for(i=0; i<nCol; i++){ int iCol = pIdx->aiColumn[i]; sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regPrev+i); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regPrev, nCol, regSample); sqlite3VdbeChangeP4(v, -1, pIdx->zColAff, 0); sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumEq); sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 3); sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumLt); sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 4); sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumDLt); sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 5); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 6, regRec, "bbbbbb", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 0, shortJump); sqlite3VdbeJumpHere(v, shortJump+2); #endif |
︙ | ︙ | |||
656 657 658 659 660 661 662 | ** ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ | | | | | | 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 | ** ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, regCnt, regStat1); jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regCnt); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1); sqlite3VdbeAddOp3(v, OP_Add, regCnt, regDLt+i, regTemp); sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); sqlite3VdbeAddOp3(v, OP_Divide, regDLt+i, regTemp, regTemp); sqlite3VdbeAddOp1(v, OP_ToInt, regTemp); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1); } if( pIdx->pPartIdxWhere!=0 ) sqlite3VdbeJumpHere(v, jZeroRows); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid); |
︙ | ︙ | |||
831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 | ** callback routine. */ typedef struct analysisInfo analysisInfo; struct analysisInfo { sqlite3 *db; const char *zDatabase; }; /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** ** argv[0] = name of the table ** argv[1] = name of the index (might be NULL) ** argv[2] = results of analysis - on integer for each column ** ** Entries for which argv[1]==NULL simply record the number of rows in ** the table. */ static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){ analysisInfo *pInfo = (analysisInfo*)pData; Index *pIndex; Table *pTable; | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < < < < | < < | < > | < | | | > | | | < | < | | | < | | > > > > > > | < > > > > > > > | > | > | > > | | | | < < < < < < | < < < < < < < < < < < < < | < < | < < | < | | | | | < < | | | | | | | | 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 | ** callback routine. */ typedef struct analysisInfo analysisInfo; struct analysisInfo { sqlite3 *db; const char *zDatabase; }; /* ** The first argument points to a nul-terminated string containing a ** list of space separated integers. Read the first nOut of these into ** the array aOut[]. */ static void decodeIntArray( char *zIntArray, int nOut, tRowcnt *aOut, int *pbUnordered ){ char *z = zIntArray; int c; int i; tRowcnt v; assert( pbUnordered==0 || *pbUnordered==0 ); for(i=0; *z && i<nOut; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } aOut[i] = v; if( *z==' ' ) z++; } if( pbUnordered && strcmp(z, "unordered")==0 ){ *pbUnordered = 1; } } /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** ** argv[0] = name of the table ** argv[1] = name of the index (might be NULL) ** argv[2] = results of analysis - on integer for each column ** ** Entries for which argv[1]==NULL simply record the number of rows in ** the table. */ static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){ analysisInfo *pInfo = (analysisInfo*)pData; Index *pIndex; Table *pTable; const char *z; assert( argc==3 ); UNUSED_PARAMETER2(NotUsed, argc); if( argv==0 || argv[0]==0 || argv[2]==0 ){ return 0; } pTable = sqlite3FindTable(pInfo->db, argv[0], pInfo->zDatabase); if( pTable==0 ){ return 0; } if( argv[1] ){ pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); }else{ pIndex = 0; } z = argv[2]; if( pIndex ){ int bUnordered = 0; decodeIntArray((char*)z, pIndex->nColumn, pIndex->aiRowEst, &bUnordered); if( pIndex->pPartIdxWhere==0 ) pTable->nRowEst = pIndex->aiRowEst[0]; pIndex->bUnordered = bUnordered; }else{ decodeIntArray((char*)z, 1, &pTable->nRowEst, 0); } return 0; } /* ** If the Index.aSample variable is not NULL, delete the aSample[] array ** and its contents. */ void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){ #ifdef SQLITE_ENABLE_STAT4 if( pIdx->aSample ){ int j; for(j=0; j<pIdx->nSample; j++){ IndexSample *p = &pIdx->aSample[j]; sqlite3DbFree(db, p->p); } sqlite3DbFree(db, pIdx->aSample); } if( db && db->pnBytesFreed==0 ){ pIdx->nSample = 0; pIdx->aSample = 0; } #else UNUSED_PARAMETER(db); UNUSED_PARAMETER(pIdx); #endif } #ifdef SQLITE_ENABLE_STAT4 /* ** Load content from the sqlite_stat4 table into the Index.aSample[] ** arrays of all indices. */ static int loadStat4(sqlite3 *db, const char *zDb){ int rc; /* Result codes from subroutines */ sqlite3_stmt *pStmt = 0; /* An SQL statement being run */ char *zSql; /* Text of the SQL statement */ Index *pPrevIdx = 0; /* Previous index in the loop */ int idx = 0; /* slot in pIdx->aSample[] for next sample */ IndexSample *pSample; /* A slot in pIdx->aSample[] */ assert( db->lookaside.bEnabled==0 ); if( !sqlite3FindTable(db, "sqlite_stat4", zDb) ){ return SQLITE_OK; } zSql = sqlite3MPrintf(db, "SELECT idx,count(*) FROM %Q.sqlite_stat4" " GROUP BY idx", zDb); if( !zSql ){ return SQLITE_NOMEM; } rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); if( rc ) return rc; while( sqlite3_step(pStmt)==SQLITE_ROW ){ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int nSample; /* Number of samples */ int nByte; /* Bytes of space required */ int i; /* Bytes of space required */ tRowcnt *pSpace; zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = sqlite3FindIndex(db, zIndex, zDb); if( pIdx==0 ) continue; assert( pIdx->nSample==0 ); pIdx->nSample = nSample; nByte = sizeof(IndexSample) * nSample; nByte += sizeof(tRowcnt) * pIdx->nColumn * 3 * nSample; pIdx->aSample = sqlite3DbMallocZero(db, nByte); pIdx->avgEq = pIdx->aiRowEst[1]; if( pIdx->aSample==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } pSpace = (tRowcnt*)&pIdx->aSample[nSample]; for(i=0; i<pIdx->nSample; i++){ pIdx->aSample[i].anEq = pSpace; pSpace += pIdx->nColumn; pIdx->aSample[i].anLt = pSpace; pSpace += pIdx->nColumn; pIdx->aSample[i].anDLt = pSpace; pSpace += pIdx->nColumn; } assert( ((u8*)pSpace)-nByte==(u8*)(pIdx->aSample) ); } rc = sqlite3_finalize(pStmt); if( rc ) return rc; zSql = sqlite3MPrintf(db, "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4", zDb); if( !zSql ){ return SQLITE_NOMEM; } rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); if( rc ) return rc; while( sqlite3_step(pStmt)==SQLITE_ROW ){ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int i; /* Loop counter */ tRowcnt sumEq; /* Sum of the nEq values */ int nCol; /* Number of columns in index */ zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; pIdx = sqlite3FindIndex(db, zIndex, zDb); if( pIdx==0 ) continue; if( pIdx==pPrevIdx ){ idx++; }else{ pPrevIdx = pIdx; idx = 0; } assert( idx<pIdx->nSample ); pSample = &pIdx->aSample[idx]; nCol = pIdx->nColumn; decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,3), nCol, pSample->anDLt,0); if( idx==pIdx->nSample-1 ){ if( pSample->anDLt[0]>0 ){ for(i=0, sumEq=0; i<=idx-1; i++) sumEq += pIdx->aSample[i].anEq[0]; pIdx->avgEq = (pSample->anLt[0] - sumEq)/pSample->anDLt[0]; } if( pIdx->avgEq<=0 ) pIdx->avgEq = 1; } pSample->n = sqlite3_column_bytes(pStmt, 4); pSample->p = sqlite3DbMallocZero(db, pSample->n); if( pSample->p==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } memcpy(pSample->p, sqlite3_column_blob(pStmt, 4), pSample->n); } return sqlite3_finalize(pStmt); } #endif /* SQLITE_ENABLE_STAT4 */ /* ** Load the content of the sqlite_stat1 and sqlite_stat4 tables. The ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[] ** arrays. The contents of sqlite_stat4 are used to populate the ** Index.aSample[] arrays. ** ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR ** is returned. In this case, even if SQLITE_ENABLE_STAT4 was defined ** during compilation and the sqlite_stat4 table is present, no data is ** read from it. ** ** If SQLITE_ENABLE_STAT4 was defined during compilation and the ** sqlite_stat4 table is not present in the database, SQLITE_ERROR is ** returned. However, in this case, data is read from the sqlite_stat1 ** table (if it is present) before returning. ** ** If an OOM error occurs, this function always sets db->mallocFailed. ** This means if the caller does not care about other errors, the return ** code may be ignored. */ |
︙ | ︙ | |||
1077 1078 1079 1080 1081 1082 1083 | assert( db->aDb[iDb].pBt!=0 ); /* Clear any prior statistics */ assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); sqlite3DefaultRowEst(pIdx); | | | 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 | assert( db->aDb[iDb].pBt!=0 ); /* Clear any prior statistics */ assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); sqlite3DefaultRowEst(pIdx); #ifdef SQLITE_ENABLE_STAT4 sqlite3DeleteIndexSamples(db, pIdx); pIdx->aSample = 0; #endif } /* Check to make sure the sqlite_stat1 table exists */ sInfo.db = db; |
︙ | ︙ | |||
1101 1102 1103 1104 1105 1106 1107 | rc = SQLITE_NOMEM; }else{ rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); sqlite3DbFree(db, zSql); } | | | | | 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 | rc = SQLITE_NOMEM; }else{ rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); sqlite3DbFree(db, zSql); } /* Load the statistics from the sqlite_stat4 table. */ #ifdef SQLITE_ENABLE_STAT4 if( rc==SQLITE_OK ){ int lookasideEnabled = db->lookaside.bEnabled; db->lookaside.bEnabled = 0; rc = loadStat4(db, sInfo.zDatabase); db->lookaside.bEnabled = lookasideEnabled; } #endif if( rc==SQLITE_NOMEM ){ db->mallocFailed = 1; } return rc; } #endif /* SQLITE_OMIT_ANALYZE */ |
Changes to src/btree.c.
︙ | ︙ | |||
2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 | pBt->max1bytePayload = 127; }else{ pBt->max1bytePayload = (u8)pBt->maxLocal; } assert( pBt->maxLeaf + 23 <= MX_CELL_SIZE(pBt) ); pBt->pPage1 = pPage1; pBt->nPage = nPage; return SQLITE_OK; page1_init_failed: releasePage(pPage1); pBt->pPage1 = 0; return rc; } | > | 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 | pBt->max1bytePayload = 127; }else{ pBt->max1bytePayload = (u8)pBt->maxLocal; } assert( pBt->maxLeaf + 23 <= MX_CELL_SIZE(pBt) ); pBt->pPage1 = pPage1; pBt->nPage = nPage; assert( pPage1->leaf==0 || pPage1->leaf==1 ); return SQLITE_OK; page1_init_failed: releasePage(pPage1); pBt->pPage1 = 0; return rc; } |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
2020 2021 2022 2023 2024 2025 2026 | Parse *pParse, /* The parsing context */ int iDb, /* The database number */ const char *zType, /* "idx" or "tbl" */ const char *zName /* Name of index or table */ ){ int i; const char *zDbName = pParse->db->aDb[iDb].zName; | | | 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 | Parse *pParse, /* The parsing context */ int iDb, /* The database number */ const char *zType, /* "idx" or "tbl" */ const char *zName /* Name of index or table */ ){ int i; const char *zDbName = pParse->db->aDb[iDb].zName; for(i=1; i<=4; i++){ char zTab[24]; sqlite3_snprintf(sizeof(zTab),zTab,"sqlite_stat%d",i); if( sqlite3FindTable(pParse->db, zTab, zDbName) ){ sqlite3NestedParse(pParse, "DELETE FROM %Q.%s WHERE %s=%Q", zDbName, zTab, zType, zName ); |
︙ | ︙ |
Changes to src/ctime.c.
︙ | ︙ | |||
113 114 115 116 117 118 119 | #endif #ifdef SQLITE_ENABLE_OVERSIZE_CELL_CHECK "ENABLE_OVERSIZE_CELL_CHECK", #endif #ifdef SQLITE_ENABLE_RTREE "ENABLE_RTREE", #endif | | | | 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | #endif #ifdef SQLITE_ENABLE_OVERSIZE_CELL_CHECK "ENABLE_OVERSIZE_CELL_CHECK", #endif #ifdef SQLITE_ENABLE_RTREE "ENABLE_RTREE", #endif #ifdef SQLITE_ENABLE_STAT4 "ENABLE_STAT4", #endif #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY "ENABLE_UNLOCK_NOTIFY", #endif #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT "ENABLE_UPDATE_DELETE_LIMIT", #endif |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1554 1555 1556 1557 1558 1559 1560 | /* ** Each sample stored in the sqlite_stat3 table is represented in memory ** using a structure of this type. See documentation at the top of the ** analyze.c source file for additional information. */ struct IndexSample { | < < < | < | < | | | | 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 | /* ** Each sample stored in the sqlite_stat3 table is represented in memory ** using a structure of this type. See documentation at the top of the ** analyze.c source file for additional information. */ struct IndexSample { void *p; /* Pointer to sampled record */ int n; /* Size of record in bytes */ tRowcnt *anEq; /* Est. number of rows where the key equals this sample */ tRowcnt *anLt; /* Est. number of rows where key is less than this sample */ tRowcnt *anDLt; /* Est. number of distinct keys less than this sample */ }; /* ** Each token coming out of the lexer is an instance of ** this structure. Tokens are also used as part of an expression. ** ** Note if Token.z==0 then Token.dyn and Token.n are undefined and |
︙ | ︙ |
Changes to src/test_config.c.
︙ | ︙ | |||
454 455 456 457 458 459 460 | #ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY); #endif | | | | | 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 | #ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_ENABLE_STAT4 Tcl_SetVar2(interp, "sqlite_options", "stat4", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY); #endif #if !defined(SQLITE_ENABLE_LOCKING_STYLE) # if defined(__APPLE__) # define SQLITE_ENABLE_LOCKING_STYLE 1 # else # define SQLITE_ENABLE_LOCKING_STYLE 0 |
︙ | ︙ |
Changes to src/utf.c.
︙ | ︙ | |||
456 457 458 459 460 461 462 | ** is set to the length of the returned string in bytes. The call should ** arrange to call sqlite3DbFree() on the returned pointer when it is ** no longer required. ** ** If a malloc failure occurs, NULL is returned and the db.mallocFailed ** flag set. */ | | | 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 | ** is set to the length of the returned string in bytes. The call should ** arrange to call sqlite3DbFree() on the returned pointer when it is ** no longer required. ** ** If a malloc failure occurs, NULL is returned and the db.mallocFailed ** flag set. */ #ifdef SQLITE_ENABLE_STAT4 char *sqlite3Utf8to16(sqlite3 *db, u8 enc, char *z, int n, int *pnOut){ Mem m; memset(&m, 0, sizeof(m)); m.db = db; sqlite3VdbeMemSetStr(&m, z, n, SQLITE_UTF8, SQLITE_STATIC); if( sqlite3VdbeMemTranslate(&m, enc) ){ assert( db->mallocFailed ); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 | ** See also: Destroy */ case OP_Clear: { int nChange; nChange = 0; assert( p->readOnly==0 ); assert( (p->btreeMask & (((yDbMask)1)<<pOp->p2))!=0 ); rc = sqlite3BtreeClearTable( db->aDb[pOp->p2].pBt, pOp->p1, (pOp->p3 ? &nChange : 0) ); if( pOp->p3 ){ p->nChange += nChange; if( pOp->p3>0 ){ | > | 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 | ** See also: Destroy */ case OP_Clear: { int nChange; nChange = 0; assert( p->readOnly==0 ); assert( pOp->p1!=1 ); assert( (p->btreeMask & (((yDbMask)1)<<pOp->p2))!=0 ); rc = sqlite3BtreeClearTable( db->aDb[pOp->p2].pBt, pOp->p1, (pOp->p3 ? &nChange : 0) ); if( pOp->p3 ){ p->nChange += nChange; if( pOp->p3>0 ){ |
︙ | ︙ |
Changes to src/vdbemem.c.
︙ | ︙ | |||
1030 1031 1032 1033 1034 1035 1036 | if( !pExpr ){ *ppVal = 0; return SQLITE_OK; } op = pExpr->op; | | | | | 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 | if( !pExpr ){ *ppVal = 0; return SQLITE_OK; } op = pExpr->op; /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT4. ** The ifdef here is to enable us to achieve 100% branch test coverage even ** when SQLITE_ENABLE_STAT4 is omitted. */ #ifdef SQLITE_ENABLE_STAT4 if( op==TK_REGISTER ) op = pExpr->op2; #else if( NEVER(op==TK_REGISTER) ) op = pExpr->op2; #endif /* Handle negative integers in a single step. This is needed in the ** case when the value is -9223372036854775808. |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
280 281 282 283 284 285 286 | #define TERM_DYNAMIC 0x01 /* Need to call sqlite3ExprDelete(db, pExpr) */ #define TERM_VIRTUAL 0x02 /* Added by the optimizer. Do not code */ #define TERM_CODED 0x04 /* This term is already coded */ #define TERM_COPIED 0x08 /* Has a child */ #define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */ #define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */ #define TERM_OR_OK 0x40 /* Used during OR-clause processing */ | | | 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 | #define TERM_DYNAMIC 0x01 /* Need to call sqlite3ExprDelete(db, pExpr) */ #define TERM_VIRTUAL 0x02 /* Added by the optimizer. Do not code */ #define TERM_CODED 0x04 /* This term is already coded */ #define TERM_COPIED 0x08 /* Has a child */ #define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */ #define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */ #define TERM_OR_OK 0x40 /* Used during OR-clause processing */ #ifdef SQLITE_ENABLE_STAT4 # define TERM_VNULL 0x80 /* Manufactured x>NULL or x<=NULL term */ #else # define TERM_VNULL 0x00 /* Disabled if not using stat3 */ #endif /* ** An instance of the WhereScan object is used as an iterator for locating |
︙ | ︙ | |||
1780 1781 1782 1783 1784 1785 1786 | pTerm->nChild = 1; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ | | | 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 | pTerm->nChild = 1; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #ifdef SQLITE_ENABLE_STAT4 /* When sqlite_stat3 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a ** virtual term of that form. ** ** Note that the virtual term must be tagged with TERM_VNULL. This ** TERM_VNULL tag will suppress the not-null check at the beginning |
︙ | ︙ | |||
2388 2389 2390 2391 2392 2393 2394 | } return pParse->nErr; } #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */ | | < | > | < < < < < < < < > | < < | < < < < < < < < < < < < < < < < < < < < | | < < < | < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < | < < < < | < < < < < < < < | < < < < < < < < < < < < | | | | | < < > | | | | | | | | | 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 | } return pParse->nErr; } #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */ #ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the location of a particular key among all keys in an ** index. Store the results in aStat as follows: ** ** aStat[0] Est. number of rows less than pVal ** aStat[1] Est. number of rows equal to pVal ** ** Return SQLITE_OK on success. */ static int whereKeyStats( Parse *pParse, /* Database connection */ Index *pIdx, /* Index to consider domain of */ sqlite3_value *pVal, /* Value to consider */ int roundUp, /* Round up if true. Round down if false */ tRowcnt *aStat /* OUT: stats written here */ ){ IndexSample *aSample = pIdx->aSample; UnpackedRecord rec; int i; int isEq = 0; if( pVal==0 ) return SQLITE_ERROR; memset(&rec, 0, sizeof(UnpackedRecord)); rec.pKeyInfo = sqlite3IndexKeyinfo(pParse, pIdx); if( rec.pKeyInfo==0 ) return SQLITE_NOMEM; rec.pKeyInfo->enc = ENC(pParse->db); rec.nField = 1; rec.flags = UNPACKED_PREFIX_MATCH; rec.aMem = pVal; for(i=0; i<pIdx->nSample; i++){ int res = sqlite3VdbeRecordCompare(aSample[i].n, aSample[i].p, &rec); if( res>=0 ){ isEq = (res==0); break; } } sqlite3DbFree(pParse->db, rec.pKeyInfo); /* At this point, aSample[i] is the first sample that is greater than ** or equal to pVal. Or if i==pIdx->nSample, then all samples are less ** than pVal. If aSample[i]==pVal, then isEq==1. */ if( isEq ){ assert( i<pIdx->nSample ); aStat[0] = aSample[i].anLt[0]; aStat[1] = aSample[i].anEq[0]; }else{ tRowcnt iLower, iUpper, iGap; if( i==0 ){ iLower = 0; iUpper = aSample[0].anLt[0]; }else{ iUpper = i>=pIdx->nSample ? pIdx->aiRowEst[0] : aSample[i].anLt[0]; iLower = aSample[i-1].anEq[0] + aSample[i-1].anLt[0]; } aStat[1] = pIdx->avgEq; if( iLower>=iUpper ){ iGap = 0; }else{ iGap = iUpper - iLower; } if( roundUp ){ iGap = (iGap*2)/3; }else{ iGap = iGap/3; } aStat[0] = iLower + iGap; } return SQLITE_OK; } #endif /* SQLITE_ENABLE_STAT4 */ /* ** If expression pExpr represents a literal value, set *pp to point to ** an sqlite3_value structure containing the same value, with affinity ** aff applied to it, before returning. It is the responsibility of the ** caller to eventually release this structure by passing it to ** sqlite3ValueFree(). ** ** If the current parse is a recompile (sqlite3Reprepare()) and pExpr ** is an SQL variable that currently has a non-NULL value bound to it, ** create an sqlite3_value structure containing this value, again with ** affinity aff applied to it, instead. ** ** If neither of the above apply, set *pp to NULL. ** ** If an error occurs, return an error code. Otherwise, SQLITE_OK. */ #ifdef SQLITE_ENABLE_STAT4 static int valueFromExpr( Parse *pParse, Expr *pExpr, u8 aff, sqlite3_value **pp ){ if( pExpr->op==TK_VARIABLE || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE) ){ int iVar = pExpr->iColumn; sqlite3VdbeSetVarmask(pParse->pVdbe, iVar); *pp = sqlite3VdbeGetBoundValue(pParse->pReprepare, iVar, aff); return SQLITE_OK; } return sqlite3ValueFromExpr(pParse->db, pExpr, ENC(pParse->db), aff, pp); } #endif /* ** This function is used to estimate the number of rows that will be visited ** by scanning an index for a range of values. The range may have an upper ** bound, a lower bound, or both. The WHERE clause terms that set the upper |
︙ | ︙ | |||
2633 2634 2635 2636 2637 2638 2639 | int nEq, /* index into p->aCol[] of the range-compared column */ WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ WhereCost *pRangeDiv /* OUT: Reduce search space by this divisor */ ){ int rc = SQLITE_OK; | | | 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 | int nEq, /* index into p->aCol[] of the range-compared column */ WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ WhereCost *pRangeDiv /* OUT: Reduce search space by this divisor */ ){ int rc = SQLITE_OK; #ifdef SQLITE_ENABLE_STAT4 if( nEq==0 && p->nSample && OptimizationEnabled(pParse->db, SQLITE_Stat3) ){ sqlite3_value *pRangeVal; tRowcnt iLower = 0; tRowcnt iUpper = p->aiRowEst[0]; tRowcnt a[2]; u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity; |
︙ | ︙ | |||
2695 2696 2697 2698 2699 2700 2701 | } if( pUpper ){ *pRangeDiv += 20; assert( 20==whereCost(4) ); } return rc; } | | | 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 | } if( pUpper ){ *pRangeDiv += 20; assert( 20==whereCost(4) ); } return rc; } #ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the number of rows that will be returned based on ** an equality constraint x=VALUE and where that VALUE occurs in ** the histogram data. This only works when x is the left-most ** column of an index and sqlite_stat3 histogram data is available ** for that index. When pExpr==NULL that means the constraint is ** "x IS NULL" instead of "x=VALUE". |
︙ | ︙ | |||
2743 2744 2745 2746 2747 2748 2749 | WHERETRACE(0x100,("equality scan regions: %d\n", (int)a[1])); *pnRow = a[1]; } whereEqualScanEst_cancel: sqlite3ValueFree(pRhs); return rc; } | | | | 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 | WHERETRACE(0x100,("equality scan regions: %d\n", (int)a[1])); *pnRow = a[1]; } whereEqualScanEst_cancel: sqlite3ValueFree(pRhs); return rc; } #endif /* defined(SQLITE_ENABLE_STAT4) */ #ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the number of rows that will be returned based on ** an IN constraint where the right-hand side of the IN operator ** is a list of values. Example: ** ** WHERE x IN (1,2,3,4) ** |
︙ | ︙ | |||
2786 2787 2788 2789 2790 2791 2792 | if( rc==SQLITE_OK ){ if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0]; *pnRow = nRowEst; WHERETRACE(0x100,("IN row estimate: est=%g\n", nRowEst)); } return rc; } | | | 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 | if( rc==SQLITE_OK ){ if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0]; *pnRow = nRowEst; WHERETRACE(0x100,("IN row estimate: est=%g\n", nRowEst)); } return rc; } #endif /* defined(SQLITE_ENABLE_STAT4) */ /* ** Disable a term in the WHERE clause. Except, do not disable the term ** if it controls a LEFT OUTER JOIN and it did not originate in the ON ** or USING clause of that join. ** ** Consider the term t2.z='ok' in the following queries: |
︙ | ︙ | |||
4331 4332 4333 4334 4335 4336 4337 | saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = 0; rLogSize = estLog(whereCost(pProbe->aiRowEst[0])); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ int nIn = 0; if( pTerm->prereqRight & pNew->maskSelf ) continue; | | | 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 | saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = 0; rLogSize = estLog(whereCost(pProbe->aiRowEst[0])); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ int nIn = 0; if( pTerm->prereqRight & pNew->maskSelf ) continue; #ifdef SQLITE_ENABLE_STAT4 if( (pTerm->wtFlags & TERM_VNULL)!=0 && pSrc->pTab->aCol[iCol].notNull ){ continue; /* skip IS NOT NULL constraints on a NOT NULL column */ } #endif pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; |
︙ | ︙ | |||
4397 4398 4399 4400 4401 4402 4403 | if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ /* Adjust nOut and rRun for STAT3 range values */ WhereCost rDiv; whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq, pBtm, pTop, &rDiv); pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10; } | | | 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 4323 4324 4325 | if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ /* Adjust nOut and rRun for STAT3 range values */ WhereCost rDiv; whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq, pBtm, pTop, &rDiv); pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10; } #ifdef SQLITE_ENABLE_STAT4 if( pNew->u.btree.nEq==1 && pProbe->nSample && OptimizationEnabled(db, SQLITE_Stat3) ){ tRowcnt nOut = 0; if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){ testcase( pTerm->eOperator & WO_EQ ); testcase( pTerm->eOperator & WO_ISNULL ); rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &nOut); |
︙ | ︙ |
Changes to test/alter.test.
︙ | ︙ | |||
843 844 845 846 847 848 849 | #------------------------------------------------------------------------- # Test that it is not possible to use ALTER TABLE on any system table. # set system_table_list {1 sqlite_master} catchsql ANALYZE ifcapable analyze { lappend system_table_list 2 sqlite_stat1 } | | | 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 | #------------------------------------------------------------------------- # Test that it is not possible to use ALTER TABLE on any system table. # set system_table_list {1 sqlite_master} catchsql ANALYZE ifcapable analyze { lappend system_table_list 2 sqlite_stat1 } ifcapable stat4 { lappend system_table_list 4 sqlite_stat4 } foreach {tn tbl} $system_table_list { do_test alter-15.$tn.1 { catchsql "ALTER TABLE $tbl RENAME TO xyz" } [list 1 "table $tbl may not be altered"] do_test alter-15.$tn.2 { |
︙ | ︙ |
Changes to test/analyze.test.
︙ | ︙ | |||
284 285 286 287 288 289 290 | sqlite3 db test.db execsql { SELECT * FROM t4 WHERE x=1234; } } {} # Verify that DROP TABLE and DROP INDEX remove entries from the | | | | | | | | | | | < | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 | sqlite3 db test.db execsql { SELECT * FROM t4 WHERE x=1234; } } {} # Verify that DROP TABLE and DROP INDEX remove entries from the # sqlite_stat1 and sqlite_stat4 tables. # do_test analyze-5.0 { execsql { DELETE FROM t3; DELETE FROM t4; INSERT INTO t3 VALUES(1,2,3,4); INSERT INTO t3 VALUES(5,6,7,8); INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3; INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3; INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3; INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3; INSERT INTO t4 SELECT a, b, c FROM t3; ANALYZE; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4} ifcapable stat4 { do_test analyze-5.1 { execsql { SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; } } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4} } do_test analyze-5.2 { execsql { DROP INDEX t3i2; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t3i1 t3i3 t4i1 t4i2 t3 t4} ifcapable stat4 { do_test analyze-5.3 { execsql { SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; } } {t3i1 t3i3 t4i1 t4i2 t3 t4} } do_test analyze-5.4 { execsql { DROP TABLE t3; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t4i1 t4i2 t4} ifcapable stat4 { do_test analyze-5.5 { execsql { SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; } } {t4i1 t4i2 t4} } # This test corrupts the database file so it must be the last test # in the series. # do_test analyze-99.1 { execsql { PRAGMA writable_schema=on; UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1'; } db close catch { sqlite3 db test.db } catchsql { ANALYZE } } {1 {malformed database schema (sqlite_stat1) - near "nonsense": syntax error}} finish_test |
Changes to test/analyze3.test.
︙ | ︙ | |||
13 14 15 16 17 18 19 | # implements tests for range and LIKE constraints that use bound variables # instead of literal constant arguments. # set testdir [file dirname $argv0] source $testdir/tester.tcl | | | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | # implements tests for range and LIKE constraints that use bound variables # instead of literal constant arguments. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat4 { finish_test return } #---------------------------------------------------------------------- # Test Organization: # |
︙ | ︙ |
Changes to test/analyze5.test.
1 2 3 4 5 6 7 8 9 10 11 12 | # 2011 January 19 # # 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 tests for SQLite library. The focus of the tests | | | > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 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 | # 2011 January 19 # # 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 tests for SQLite library. The focus of the tests # in this file is the use of the sqlite_stat4 histogram data on tables # with many repeated values and only a few distinct values. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat4 { finish_test return } set testprefix analyze5 proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } proc alpha {blob} { set ret "" foreach c [split $blob {}] { if {[string is alpha $c]} {append ret $c} } return $ret } db func alpha alpha unset -nocomplain i t u v w x y z do_test analyze5-1.0 { db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)} for {set i 0} {$i < 1000} {incr i} { set y [expr {$i>=25 && $i<=50}] set z [expr {($i>=400) + ($i>=700) + ($i>=875)}] |
︙ | ︙ | |||
51 52 53 54 55 56 57 | CREATE INDEX t1u ON t1(u); -- text CREATE INDEX t1v ON t1(v); -- mixed case text CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3 ANALYZE; | | | > | > | | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | CREATE INDEX t1u ON t1(u); -- text CREATE INDEX t1v ON t1(v); -- mixed case text CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3 ANALYZE; SELECT alpha(sample) FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt; } } {alpha bravo charlie delta} do_test analyze5-1.1 { db eval { SELECT DISTINCT lower(alpha(sample)) FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1 } } {alpha bravo charlie delta} do_test analyze5-1.2 { db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1} } {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4} # Verify that range queries generate the correct row count estimates # foreach {testid where index rows} { 1 {z>=0 AND z<=0} t1z 400 2 {z>=1 AND z<=1} t1z 300 |
︙ | ︙ |
Changes to test/analyze6.test.
︙ | ︙ | |||
13 14 15 16 17 18 19 | # in this file a corner-case query planner optimization involving the # join order of two tables of different sizes. # set testdir [file dirname $argv0] source $testdir/tester.tcl | | | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | # in this file a corner-case query planner optimization involving the # join order of two tables of different sizes. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat4 { finish_test return } set testprefix analyze6 proc eqp {sql {db db}} { |
︙ | ︙ |
Changes to test/analyze7.test.
︙ | ︙ | |||
78 79 80 81 82 83 84 | } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test analyze7-3.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_test analyze7-3.2.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} | | | | | | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} do_test analyze7-3.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_test analyze7-3.2.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} ifcapable stat4 { # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated # row count for (c=2) than it does for (c=?). do_test analyze7-3.2.2 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} } else { # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the # same as that for (c=?). do_test analyze7-3.2.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}} } do_test analyze7-3.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} ifcapable {!stat4} { do_test analyze7-3.4 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_test analyze7-3.5 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123} } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}} } |
︙ | ︙ |
Changes to test/analyze8.test.
︙ | ︙ | |||
12 13 14 15 16 17 18 | # This file implements tests for SQLite library. The focus of the tests # in this file is testing the capabilities of sqlite_stat3. # set testdir [file dirname $argv0] source $testdir/tester.tcl | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | # This file implements tests for SQLite library. The focus of the tests # in this file is testing the capabilities of sqlite_stat3. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat4 { finish_test return } set testprefix analyze8 proc eqp {sql {db db}} { |
︙ | ︙ |
Added test/analyze9.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 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 | # 2013 August 3 # # 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. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyze9 proc s {blob} { set ret "" binary scan $blob c* bytes foreach b $bytes { set t [binary format c $b] if {[string is print $t]} { append ret $t } else { append ret . } } return $ret } db function s s do_test 1.0 { execsql { CREATE TABLE t1(a TEXT, b TEXT); } for {set i 0} {$i < 5} {incr i} { execsql {INSERT INTO t1 VALUES ('('||($i%10)||')', '('||($i%7)||')')} } execsql { CREATE INDEX i1 ON t1(a, b) } } {} do_execsql_test 1.1 { ANALYZE; } {} do_execsql_test 1.2 { SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4; } { t1 i1 {1 1} {1 1} {1 1} ...(1)(1) t1 i1 {1 1} {2 2} {2 2} ...(2)(2) t1 i1 {1 1} {3 3} {3 3} ...(3)(3) t1 i1 {1 1} {4 4} {4 4} ...(4)(4) } finish_test |
Changes to test/auth.test.
︙ | ︙ | |||
2321 2322 2323 2324 2325 2326 2327 | } ifcapable view { execsql { DROP TABLE v1chng; } } } | | | | | | 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 | } ifcapable view { execsql { DROP TABLE v1chng; } } } ifcapable stat4 { set stat4 "sqlite_stat4 " } else { set stat4 "" } do_test auth-5.2 { execsql { SELECT name FROM ( SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name } } "sqlite_stat1 ${stat4}t1 t2 t3 t4" } # Ticket #3944 # ifcapable trigger { do_test auth-5.3.1 { execsql { |
︙ | ︙ |
Changes to test/dbstatus.test.
︙ | ︙ | |||
57 58 59 60 61 62 63 | proc lookaside {db} { expr { $::lookaside_buffer_size * [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] } } | | | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | proc lookaside {db} { expr { $::lookaside_buffer_size * [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] } } ifcapable stat4 { set STAT3 1 } else { set STAT3 0 } ifcapable malloc_usable_size { finish_test |
︙ | ︙ | |||
210 211 212 213 214 215 216 | # for any reason is not counted as "schema memory". # # Additionally, in auto-vacuum mode, dropping tables and indexes causes # the page-cache to shrink. So the amount of memory freed is always # much greater than just that reported by DBSTATUS_SCHEMA_USED in this # case. # | | | 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 | # for any reason is not counted as "schema memory". # # Additionally, in auto-vacuum mode, dropping tables and indexes causes # the page-cache to shrink. So the amount of memory freed is always # much greater than just that reported by DBSTATUS_SCHEMA_USED in this # case. # # Some of the memory used for sqlite_stat4 is unaccounted for by # dbstatus. # # Finally, on osx the estimate of memory used by the schema may be # slightly low. # if {[string match *x $tn] || $AUTOVACUUM || ([string match *y $tn] && $STAT3) |
︙ | ︙ |
Changes to test/tkt-cbd054fa6b.test.
︙ | ︙ | |||
12 13 14 15 16 17 18 | # This file implements tests to verify that ticket [cbd054fa6b] has been # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl | | > > > > > > > > > > > > > > > | 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 | # This file implements tests to verify that ticket [cbd054fa6b] has been # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat4 { finish_test return } proc s {blob} { set ret "" binary scan $blob c* bytes foreach b $bytes { set t [binary format c $b] if {[string is print $t]} { append ret $t } else { append ret . } } return $ret } db function s s do_test tkt-cbd05-1.1 { db eval { CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL); CREATE INDEX t1_x ON t1(b); INSERT INTO t1 VALUES (NULL, ''); INSERT INTO t1 VALUES (NULL, 'A'); |
︙ | ︙ | |||
41 42 43 44 45 46 47 | do_test tkt-cbd05-1.2 { db eval { ANALYZE; } } {} do_test tkt-cbd05-1.3 { execsql { | | | | | 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | do_test tkt-cbd05-1.2 { db eval { ANALYZE; } } {} do_test tkt-cbd05-1.3 { execsql { SELECT tbl,idx,group_concat(s(sample),' ') FROM sqlite_stat4 WHERE idx = 't1_x' GROUP BY tbl,idx } } {t1 t1_x {.. ..A ..B ..C ..D ..E ..F ..G ..H ..I}} do_test tkt-cbd05-2.1 { db eval { DROP TABLE t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL); CREATE INDEX t1_x ON t1(b); INSERT INTO t1 VALUES(NULL, X''); |
︙ | ︙ | |||
73 74 75 76 77 78 79 | do_test tkt-cbd05-2.2 { db eval { ANALYZE; } } {} do_test tkt-cbd05-2.3 { execsql { | | | | | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | do_test tkt-cbd05-2.2 { db eval { ANALYZE; } } {} do_test tkt-cbd05-2.3 { execsql { SELECT tbl,idx,group_concat(s(sample),' ') FROM sqlite_stat4 WHERE idx = 't1_x' GROUP BY tbl,idx } } {t1 t1_x {.. ..A ..B ..C ..D ..E ..F ..G ..H ..I}} finish_test |
Changes to test/where9.test.
︙ | ︙ | |||
777 778 779 780 781 782 783 | catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} | | | | | 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 | catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} ifcapable stat4 { # When STAT3 is enabled, the "b NOT NULL" terms get translated # into b>NULL, which can be satified by the index t1b. It is a very # expensive way to do the query, but it works, and so a solution is possible. do_test where9-6.8.3-stat4 { catchsql { UPDATE t1 INDEXED BY t1b SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {0 {}} do_test where9-6.8.4-stat4 { catchsql { DELETE FROM t1 INDEXED BY t1b WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {0 {}} |
︙ | ︙ |
Changes to test/wild001.test.
︙ | ︙ | |||
37 38 39 40 41 42 43 44 45 46 47 48 49 50 | # # This test should work the same with and without SQLITE_ENABLE_STAT3 # ############################################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !stat3 { finish_test return } do_execsql_test wild001.01 { | > > > > | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | # # This test should work the same with and without SQLITE_ENABLE_STAT3 # ############################################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl # TODO: Reenable this test. finish_test return ifcapable !stat3 { finish_test return } do_execsql_test wild001.01 { |
︙ | ︙ |