1 /* 2 ** 2005 July 8 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains code associated with the ANALYZE command. 13 */ 14 #ifndef SQLITE_OMIT_ANALYZE 15 #include "sqliteInt.h" 16 17 /* 18 ** This routine generates code that opens the sqlite_stat1 table for 19 ** writing with cursor iStatCur. If the library was built with the 20 ** SQLITE_ENABLE_STAT2 macro defined, then the sqlite_stat2 table is 21 ** opened for writing using cursor (iStatCur+1) 22 ** 23 ** If the sqlite_stat1 tables does not previously exist, it is created. 24 ** Similarly, if the sqlite_stat2 table does not exist and the library 25 ** is compiled with SQLITE_ENABLE_STAT2 defined, it is created. 26 ** 27 ** Argument zWhere may be a pointer to a buffer containing a table name, 28 ** or it may be a NULL pointer. If it is not NULL, then all entries in 29 ** the sqlite_stat1 and (if applicable) sqlite_stat2 tables associated 30 ** with the named table are deleted. If zWhere==0, then code is generated 31 ** to delete all stat table entries. 32 */ 33 static void openStatTable( 34 Parse *pParse, /* Parsing context */ 35 int iDb, /* The database we are looking in */ 36 int iStatCur, /* Open the sqlite_stat1 table on this cursor */ 37 const char *zWhere, /* Delete entries for this table or index */ 38 const char *zWhereType /* Either "tbl" or "idx" */ 39 ){ 40 static const struct { 41 const char *zName; 42 const char *zCols; 43 } aTable[] = { 44 { "sqlite_stat1", "tbl,idx,stat" }, 45 #ifdef SQLITE_ENABLE_STAT2 46 { "sqlite_stat2", "tbl,idx,sampleno,sample" }, 47 #endif 48 }; 49 50 int aRoot[] = {0, 0}; 51 u8 aCreateTbl[] = {0, 0}; 52 53 int i; 54 sqlite3 *db = pParse->db; 55 Db *pDb; 56 Vdbe *v = sqlite3GetVdbe(pParse); 57 if( v==0 ) return; 58 assert( sqlite3BtreeHoldsAllMutexes(db) ); 59 assert( sqlite3VdbeDb(v)==db ); 60 pDb = &db->aDb[iDb]; 61 62 for(i=0; i<ArraySize(aTable); i++){ 63 const char *zTab = aTable[i].zName; 64 Table *pStat; 65 if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){ 66 /* The sqlite_stat[12] table does not exist. Create it. Note that a 67 ** side-effect of the CREATE TABLE statement is to leave the rootpage 68 ** of the new table in register pParse->regRoot. This is important 69 ** because the OpenWrite opcode below will be needing it. */ 70 sqlite3NestedParse(pParse, 71 "CREATE TABLE %Q.%s(%s)", pDb->zName, zTab, aTable[i].zCols 72 ); 73 aRoot[i] = pParse->regRoot; 74 aCreateTbl[i] = 1; 75 }else{ 76 /* The table already exists. If zWhere is not NULL, delete all entries 77 ** associated with the table zWhere. If zWhere is NULL, delete the 78 ** entire contents of the table. */ 79 aRoot[i] = pStat->tnum; 80 sqlite3TableLock(pParse, iDb, aRoot[i], 1, zTab); 81 if( zWhere ){ 82 sqlite3NestedParse(pParse, 83 "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere 84 ); 85 }else{ 86 /* The sqlite_stat[12] table already exists. Delete all rows. */ 87 sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb); 88 } 89 } 90 } 91 92 /* Open the sqlite_stat[12] tables for writing. */ 93 for(i=0; i<ArraySize(aTable); i++){ 94 sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb); 95 sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32); 96 sqlite3VdbeChangeP5(v, aCreateTbl[i]); 97 } 98 } 99 100 /* 101 ** Generate code to do an analysis of all indices associated with 102 ** a single table. 103 */ 104 static void analyzeOneTable( 105 Parse *pParse, /* Parser context */ 106 Table *pTab, /* Table whose indices are to be analyzed */ 107 Index *pOnlyIdx, /* If not NULL, only analyze this one index */ 108 int iStatCur, /* Index of VdbeCursor that writes the sqlite_stat1 table */ 109 int iMem /* Available memory locations begin here */ 110 ){ 111 sqlite3 *db = pParse->db; /* Database handle */ 112 Index *pIdx; /* An index to being analyzed */ 113 int iIdxCur; /* Cursor open on index being analyzed */ 114 Vdbe *v; /* The virtual machine being built up */ 115 int i; /* Loop counter */ 116 int topOfLoop; /* The top of the loop */ 117 int endOfLoop; /* The end of the loop */ 118 int jZeroRows = -1; /* Jump from here if number of rows is zero */ 119 int iDb; /* Index of database containing pTab */ 120 int regTabname = iMem++; /* Register containing table name */ 121 int regIdxname = iMem++; /* Register containing index name */ 122 int regSampleno = iMem++; /* Register containing next sample number */ 123 int regCol = iMem++; /* Content of a column analyzed table */ 124 int regRec = iMem++; /* Register holding completed record */ 125 int regTemp = iMem++; /* Temporary use register */ 126 int regRowid = iMem++; /* Rowid for the inserted record */ 127 128 #ifdef SQLITE_ENABLE_STAT2 129 int addr = 0; /* Instruction address */ 130 int regTemp2 = iMem++; /* Temporary use register */ 131 int regSamplerecno = iMem++; /* Index of next sample to record */ 132 int regRecno = iMem++; /* Current sample index */ 133 int regLast = iMem++; /* Index of last sample to record */ 134 int regFirst = iMem++; /* Index of first sample to record */ 135 #endif 136 137 v = sqlite3GetVdbe(pParse); 138 if( v==0 || NEVER(pTab==0) ){ 139 return; 140 } 141 if( pTab->tnum==0 ){ 142 /* Do not gather statistics on views or virtual tables */ 143 return; 144 } 145 if( sqlite3_strnicmp(pTab->zName, "sqlite_", 7)==0 ){ 146 /* Do not gather statistics on system tables */ 147 return; 148 } 149 assert( sqlite3BtreeHoldsAllMutexes(db) ); 150 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 151 assert( iDb>=0 ); 152 assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); 153 #ifndef SQLITE_OMIT_AUTHORIZATION 154 if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0, 155 db->aDb[iDb].zName ) ){ 156 return; 157 } 158 #endif 159 160 /* Establish a read-lock on the table at the shared-cache level. */ 161 sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); 162 163 iIdxCur = pParse->nTab++; 164 sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); 165 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 166 int nCol; 167 KeyInfo *pKey; 168 169 if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; 170 nCol = pIdx->nColumn; 171 pKey = sqlite3IndexKeyinfo(pParse, pIdx); 172 if( iMem+1+(nCol*2)>pParse->nMem ){ 173 pParse->nMem = iMem+1+(nCol*2); 174 } 175 176 /* Open a cursor to the index to be analyzed. */ 177 assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); 178 sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb, 179 (char *)pKey, P4_KEYINFO_HANDOFF); 180 VdbeComment((v, "%s", pIdx->zName)); 181 182 /* Populate the register containing the index name. */ 183 sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); 184 185 #ifdef SQLITE_ENABLE_STAT2 186 187 /* If this iteration of the loop is generating code to analyze the 188 ** first index in the pTab->pIndex list, then register regLast has 189 ** not been populated. In this case populate it now. */ 190 if( pTab->pIndex==pIdx ){ 191 sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno); 192 sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2-1, regTemp); 193 sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regTemp2); 194 195 sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regLast); 196 sqlite3VdbeAddOp2(v, OP_Null, 0, regFirst); 197 addr = sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, 0, regLast); 198 sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regFirst); 199 sqlite3VdbeAddOp3(v, OP_Multiply, regLast, regTemp, regLast); 200 sqlite3VdbeAddOp2(v, OP_AddImm, regLast, SQLITE_INDEX_SAMPLES*2-2); 201 sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regLast); 202 sqlite3VdbeJumpHere(v, addr); 203 } 204 205 /* Zero the regSampleno and regRecno registers. */ 206 sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno); 207 sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno); 208 sqlite3VdbeAddOp2(v, OP_Copy, regFirst, regSamplerecno); 209 #endif 210 211 /* The block of memory cells initialized here is used as follows. 212 ** 213 ** iMem: 214 ** The total number of rows in the table. 215 ** 216 ** iMem+1 .. iMem+nCol: 217 ** Number of distinct entries in index considering the 218 ** left-most N columns only, where N is between 1 and nCol, 219 ** inclusive. 220 ** 221 ** iMem+nCol+1 .. Mem+2*nCol: 222 ** Previous value of indexed columns, from left to right. 223 ** 224 ** Cells iMem through iMem+nCol are initialized to 0. The others are 225 ** initialized to contain an SQL NULL. 226 */ 227 for(i=0; i<=nCol; i++){ 228 sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem+i); 229 } 230 for(i=0; i<nCol; i++){ 231 sqlite3VdbeAddOp2(v, OP_Null, 0, iMem+nCol+i+1); 232 } 233 234 /* Start the analysis loop. This loop runs through all the entries in 235 ** the index b-tree. */ 236 endOfLoop = sqlite3VdbeMakeLabel(v); 237 sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); 238 topOfLoop = sqlite3VdbeCurrentAddr(v); 239 sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); 240 241 for(i=0; i<nCol; i++){ 242 CollSeq *pColl; 243 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); 244 if( i==0 ){ 245 #ifdef SQLITE_ENABLE_STAT2 246 /* Check if the record that cursor iIdxCur points to contains a 247 ** value that should be stored in the sqlite_stat2 table. If so, 248 ** store it. */ 249 int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno); 250 assert( regTabname+1==regIdxname 251 && regTabname+2==regSampleno 252 && regTabname+3==regCol 253 ); 254 sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); 255 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0); 256 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid); 257 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid); 258 259 /* Calculate new values for regSamplerecno and regSampleno. 260 ** 261 ** sampleno = sampleno + 1 262 ** samplerecno = samplerecno+(remaining records)/(remaining samples) 263 */ 264 sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1); 265 sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regLast, regTemp); 266 sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); 267 sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2); 268 sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2); 269 sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp); 270 sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno); 271 272 sqlite3VdbeJumpHere(v, ne); 273 sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); 274 #endif 275 276 /* Always record the very first row */ 277 sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); 278 } 279 assert( pIdx->azColl!=0 ); 280 assert( pIdx->azColl[i]!=0 ); 281 pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); 282 sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1, 283 (char*)pColl, P4_COLLSEQ); 284 sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); 285 } 286 if( db->mallocFailed ){ 287 /* If a malloc failure has occurred, then the result of the expression 288 ** passed as the second argument to the call to sqlite3VdbeJumpHere() 289 ** below may be negative. Which causes an assert() to fail (or an 290 ** out-of-bounds write if SQLITE_DEBUG is not defined). */ 291 return; 292 } 293 sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop); 294 for(i=0; i<nCol; i++){ 295 int addr2 = sqlite3VdbeCurrentAddr(v) - (nCol*2); 296 if( i==0 ){ 297 sqlite3VdbeJumpHere(v, addr2-1); /* Set jump dest for the OP_IfNot */ 298 } 299 sqlite3VdbeJumpHere(v, addr2); /* Set jump dest for the OP_Ne */ 300 sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); 301 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); 302 } 303 304 /* End of the analysis loop. */ 305 sqlite3VdbeResolveLabel(v, endOfLoop); 306 sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); 307 sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); 308 309 /* Store the results in sqlite_stat1. 310 ** 311 ** The result is a single row of the sqlite_stat1 table. The first 312 ** two columns are the names of the table and index. The third column 313 ** is a string composed of a list of integer statistics about the 314 ** index. The first integer in the list is the total number of entries 315 ** in the index. There is one additional integer in the list for each 316 ** column of the table. This additional integer is a guess of how many 317 ** rows of the table the index will select. If D is the count of distinct 318 ** values and K is the total number of rows, then the integer is computed 319 ** as: 320 ** 321 ** I = (K+D-1)/D 322 ** 323 ** If K==0 then no entry is made into the sqlite_stat1 table. 324 ** If K>0 then it is always the case the D>0 so division by zero 325 ** is never possible. 326 */ 327 sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno); 328 if( jZeroRows<0 ){ 329 jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); 330 } 331 for(i=0; i<nCol; i++){ 332 sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); 333 sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); 334 sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp); 335 sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); 336 sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp); 337 sqlite3VdbeAddOp1(v, OP_ToInt, regTemp); 338 sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); 339 } 340 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); 341 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); 342 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); 343 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 344 } 345 346 /* If the table has no indices, create a single sqlite_stat1 entry 347 ** containing NULL as the index name and the row count as the content. 348 */ 349 if( pTab->pIndex==0 ){ 350 sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb); 351 VdbeComment((v, "%s", pTab->zName)); 352 sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno); 353 sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); 354 jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regSampleno); 355 }else{ 356 sqlite3VdbeJumpHere(v, jZeroRows); 357 jZeroRows = sqlite3VdbeAddOp0(v, OP_Goto); 358 } 359 sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); 360 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); 361 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); 362 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); 363 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 364 if( pParse->nMem<regRec ) pParse->nMem = regRec; 365 sqlite3VdbeJumpHere(v, jZeroRows); 366 } 367 368 /* 369 ** Generate code that will cause the most recent index analysis to 370 ** be loaded into internal hash tables where is can be used. 371 */ 372 static void loadAnalysis(Parse *pParse, int iDb){ 373 Vdbe *v = sqlite3GetVdbe(pParse); 374 if( v ){ 375 sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb); 376 } 377 } 378 379 /* 380 ** Generate code that will do an analysis of an entire database 381 */ 382 static void analyzeDatabase(Parse *pParse, int iDb){ 383 sqlite3 *db = pParse->db; 384 Schema *pSchema = db->aDb[iDb].pSchema; /* Schema of database iDb */ 385 HashElem *k; 386 int iStatCur; 387 int iMem; 388 389 sqlite3BeginWriteOperation(pParse, 0, iDb); 390 iStatCur = pParse->nTab; 391 pParse->nTab += 2; 392 openStatTable(pParse, iDb, iStatCur, 0, 0); 393 iMem = pParse->nMem+1; 394 assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); 395 for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){ 396 Table *pTab = (Table*)sqliteHashData(k); 397 analyzeOneTable(pParse, pTab, 0, iStatCur, iMem); 398 } 399 loadAnalysis(pParse, iDb); 400 } 401 402 /* 403 ** Generate code that will do an analysis of a single table in 404 ** a database. If pOnlyIdx is not NULL then it is a single index 405 ** in pTab that should be analyzed. 406 */ 407 static void analyzeTable(Parse *pParse, Table *pTab, Index *pOnlyIdx){ 408 int iDb; 409 int iStatCur; 410 411 assert( pTab!=0 ); 412 assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); 413 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); 414 sqlite3BeginWriteOperation(pParse, 0, iDb); 415 iStatCur = pParse->nTab; 416 pParse->nTab += 2; 417 if( pOnlyIdx ){ 418 openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx"); 419 }else{ 420 openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl"); 421 } 422 analyzeOneTable(pParse, pTab, pOnlyIdx, iStatCur, pParse->nMem+1); 423 loadAnalysis(pParse, iDb); 424 } 425 426 /* 427 ** Generate code for the ANALYZE command. The parser calls this routine 428 ** when it recognizes an ANALYZE command. 429 ** 430 ** ANALYZE -- 1 431 ** ANALYZE <database> -- 2 432 ** ANALYZE ?<database>.?<tablename> -- 3 433 ** 434 ** Form 1 causes all indices in all attached databases to be analyzed. 435 ** Form 2 analyzes all indices the single database named. 436 ** Form 3 analyzes all indices associated with the named table. 437 */ 438 void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2){ 439 sqlite3 *db = pParse->db; 440 int iDb; 441 int i; 442 char *z, *zDb; 443 Table *pTab; 444 Index *pIdx; 445 Token *pTableName; 446 447 /* Read the database schema. If an error occurs, leave an error message 448 ** and code in pParse and return NULL. */ 449 assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); 450 if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ 451 return; 452 } 453 454 assert( pName2!=0 || pName1==0 ); 455 if( pName1==0 ){ 456 /* Form 1: Analyze everything */ 457 for(i=0; i<db->nDb; i++){ 458 if( i==1 ) continue; /* Do not analyze the TEMP database */ 459 analyzeDatabase(pParse, i); 460 } 461 }else if( pName2->n==0 ){ 462 /* Form 2: Analyze the database or table named */ 463 iDb = sqlite3FindDb(db, pName1); 464 if( iDb>=0 ){ 465 analyzeDatabase(pParse, iDb); 466 }else{ 467 z = sqlite3NameFromToken(db, pName1); 468 if( z ){ 469 if( (pIdx = sqlite3FindIndex(db, z, 0))!=0 ){ 470 analyzeTable(pParse, pIdx->pTable, pIdx); 471 }else if( (pTab = sqlite3LocateTable(pParse, 0, z, 0))!=0 ){ 472 analyzeTable(pParse, pTab, 0); 473 } 474 sqlite3DbFree(db, z); 475 } 476 } 477 }else{ 478 /* Form 3: Analyze the fully qualified table name */ 479 iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pTableName); 480 if( iDb>=0 ){ 481 zDb = db->aDb[iDb].zName; 482 z = sqlite3NameFromToken(db, pTableName); 483 if( z ){ 484 if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){ 485 analyzeTable(pParse, pIdx->pTable, pIdx); 486 }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ 487 analyzeTable(pParse, pTab, 0); 488 } 489 sqlite3DbFree(db, z); 490 } 491 } 492 } 493 } 494 495 /* 496 ** Used to pass information from the analyzer reader through to the 497 ** callback routine. 498 */ 499 typedef struct analysisInfo analysisInfo; 500 struct analysisInfo { 501 sqlite3 *db; 502 const char *zDatabase; 503 }; 504 505 /* 506 ** This callback is invoked once for each index when reading the 507 ** sqlite_stat1 table. 508 ** 509 ** argv[0] = name of the table 510 ** argv[1] = name of the index (might be NULL) 511 ** argv[2] = results of analysis - on integer for each column 512 ** 513 ** Entries for which argv[1]==NULL simply record the number of rows in 514 ** the table. 515 */ 516 static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){ 517 analysisInfo *pInfo = (analysisInfo*)pData; 518 Index *pIndex; 519 Table *pTable; 520 int i, c, n; 521 unsigned int v; 522 const char *z; 523 524 assert( argc==3 ); 525 UNUSED_PARAMETER2(NotUsed, argc); 526 527 if( argv==0 || argv[0]==0 || argv[2]==0 ){ 528 return 0; 529 } 530 pTable = sqlite3FindTable(pInfo->db, argv[0], pInfo->zDatabase); 531 if( pTable==0 ){ 532 return 0; 533 } 534 if( argv[1] ){ 535 pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); 536 }else{ 537 pIndex = 0; 538 } 539 n = pIndex ? pIndex->nColumn : 0; 540 z = argv[2]; 541 for(i=0; *z && i<=n; i++){ 542 v = 0; 543 while( (c=z[0])>='0' && c<='9' ){ 544 v = v*10 + c - '0'; 545 z++; 546 } 547 if( i==0 ) pTable->nRowEst = v; 548 if( pIndex==0 ) break; 549 pIndex->aiRowEst[i] = v; 550 if( *z==' ' ) z++; 551 if( strcmp(z, "unordered")==0 ){ 552 pIndex->bUnordered = 1; 553 break; 554 } 555 } 556 return 0; 557 } 558 559 /* 560 ** If the Index.aSample variable is not NULL, delete the aSample[] array 561 ** and its contents. 562 */ 563 void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){ 564 #ifdef SQLITE_ENABLE_STAT2 565 if( pIdx->aSample ){ 566 int j; 567 for(j=0; j<SQLITE_INDEX_SAMPLES; j++){ 568 IndexSample *p = &pIdx->aSample[j]; 569 if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){ 570 sqlite3DbFree(db, p->u.z); 571 } 572 } 573 sqlite3DbFree(db, pIdx->aSample); 574 } 575 #else 576 UNUSED_PARAMETER(db); 577 UNUSED_PARAMETER(pIdx); 578 #endif 579 } 580 581 /* 582 ** Load the content of the sqlite_stat1 and sqlite_stat2 tables. The 583 ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[] 584 ** arrays. The contents of sqlite_stat2 are used to populate the 585 ** Index.aSample[] arrays. 586 ** 587 ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR 588 ** is returned. In this case, even if SQLITE_ENABLE_STAT2 was defined 589 ** during compilation and the sqlite_stat2 table is present, no data is 590 ** read from it. 591 ** 592 ** If SQLITE_ENABLE_STAT2 was defined during compilation and the 593 ** sqlite_stat2 table is not present in the database, SQLITE_ERROR is 594 ** returned. However, in this case, data is read from the sqlite_stat1 595 ** table (if it is present) before returning. 596 ** 597 ** If an OOM error occurs, this function always sets db->mallocFailed. 598 ** This means if the caller does not care about other errors, the return 599 ** code may be ignored. 600 */ 601 int sqlite3AnalysisLoad(sqlite3 *db, int iDb){ 602 analysisInfo sInfo; 603 HashElem *i; 604 char *zSql; 605 int rc; 606 607 assert( iDb>=0 && iDb<db->nDb ); 608 assert( db->aDb[iDb].pBt!=0 ); 609 610 /* Clear any prior statistics */ 611 assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); 612 for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){ 613 Index *pIdx = sqliteHashData(i); 614 sqlite3DefaultRowEst(pIdx); 615 sqlite3DeleteIndexSamples(db, pIdx); 616 pIdx->aSample = 0; 617 } 618 619 /* Check to make sure the sqlite_stat1 table exists */ 620 sInfo.db = db; 621 sInfo.zDatabase = db->aDb[iDb].zName; 622 if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){ 623 return SQLITE_ERROR; 624 } 625 626 /* Load new statistics out of the sqlite_stat1 table */ 627 zSql = sqlite3MPrintf(db, 628 "SELECT tbl, idx, stat FROM %Q.sqlite_stat1", sInfo.zDatabase); 629 if( zSql==0 ){ 630 rc = SQLITE_NOMEM; 631 }else{ 632 rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); 633 sqlite3DbFree(db, zSql); 634 } 635 636 637 /* Load the statistics from the sqlite_stat2 table. */ 638 #ifdef SQLITE_ENABLE_STAT2 639 if( rc==SQLITE_OK && !sqlite3FindTable(db, "sqlite_stat2", sInfo.zDatabase) ){ 640 rc = SQLITE_ERROR; 641 } 642 if( rc==SQLITE_OK ){ 643 sqlite3_stmt *pStmt = 0; 644 645 zSql = sqlite3MPrintf(db, 646 "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase); 647 if( !zSql ){ 648 rc = SQLITE_NOMEM; 649 }else{ 650 rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 651 sqlite3DbFree(db, zSql); 652 } 653 654 if( rc==SQLITE_OK ){ 655 while( sqlite3_step(pStmt)==SQLITE_ROW ){ 656 char *zIndex; /* Index name */ 657 Index *pIdx; /* Pointer to the index object */ 658 659 zIndex = (char *)sqlite3_column_text(pStmt, 0); 660 pIdx = zIndex ? sqlite3FindIndex(db, zIndex, sInfo.zDatabase) : 0; 661 if( pIdx ){ 662 int iSample = sqlite3_column_int(pStmt, 1); 663 if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){ 664 int eType = sqlite3_column_type(pStmt, 2); 665 666 if( pIdx->aSample==0 ){ 667 static const int sz = sizeof(IndexSample)*SQLITE_INDEX_SAMPLES; 668 pIdx->aSample = (IndexSample *)sqlite3DbMallocRaw(0, sz); 669 if( pIdx->aSample==0 ){ 670 db->mallocFailed = 1; 671 break; 672 } 673 memset(pIdx->aSample, 0, sz); 674 } 675 676 assert( pIdx->aSample ); 677 { 678 IndexSample *pSample = &pIdx->aSample[iSample]; 679 pSample->eType = (u8)eType; 680 if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ 681 pSample->u.r = sqlite3_column_double(pStmt, 2); 682 }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){ 683 const char *z = (const char *)( 684 (eType==SQLITE_BLOB) ? 685 sqlite3_column_blob(pStmt, 2): 686 sqlite3_column_text(pStmt, 2) 687 ); 688 int n = sqlite3_column_bytes(pStmt, 2); 689 if( n>24 ){ 690 n = 24; 691 } 692 pSample->nByte = (u8)n; 693 if( n < 1){ 694 pSample->u.z = 0; 695 }else{ 696 pSample->u.z = sqlite3DbStrNDup(0, z, n); 697 if( pSample->u.z==0 ){ 698 db->mallocFailed = 1; 699 break; 700 } 701 } 702 } 703 } 704 } 705 } 706 } 707 rc = sqlite3_finalize(pStmt); 708 } 709 } 710 #endif 711 712 if( rc==SQLITE_NOMEM ){ 713 db->mallocFailed = 1; 714 } 715 return rc; 716 } 717 718 719 #endif /* SQLITE_OMIT_ANALYZE */ 720
