SQLite

){
  static const struct {
    const char *zName;
    const char *zCols;
  } aTable[] = {
    { "sqlite_stat1", "tbl,idx,stat" },
#ifdef SQLITE_ENABLE_STAT2
    { "sqlite_stat2", "tbl,idx,sampleno,sample,cnt" },
#endif
  };

  int aRoot[] = {0, 0};
  u8 aCreateTbl[] = {0, 0};

  int i;

  }

  /* Open the sqlite_stat[12] 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]);
    VdbeComment((v, "%s", aTable[i].zName));
  }
}

/*
** Generate code to do an analysis of all indices associated with
** a single table.
*/

  int i;                       /* Loop counter */
  int topOfLoop;               /* The top of the loop */
  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 */
  int regTabname = iMem++;     /* Register containing table name */
  int regIdxname = iMem++;     /* Register containing index name */
  int regSampleno = iMem++;    /* Sampleno (stat2) or stat (stat1) */
#ifdef SQLITE_ENABLE_STAT2
  int regSample = iMem++;      /* The next sample value */
  int regSampleCnt = iMem++;   /* Number of occurrances of regSample value */
  int shortJump = 0;           /* Instruction address */
  int addrStoreStat2 = 0;      /* Address of subroutine to wrote to stat2 */
  int regNext = iMem++;        /* Index of next sample to record */
  int regSampleIdx = iMem++;   /* Index of next sample */
  int regReady = iMem++;       /* True if ready to store a stat2 entry */
  int regGosub = iMem++;       /* Register holding subroutine return addr */
  int regSample2 = iMem++;     /* Number of samples to acquire times 2 */
  int regCount = iMem++;       /* Number of rows in the table */
  int regCount2 = iMem++;      /* regCount*2 */
#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 regRowid = iMem++;       /* Rowid for the inserted record */
  int once = 1;                /* One-time initialization */










  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) ){
    return;
  }
  if( pTab->tnum==0 ){
    /* Do not gather statistics on views or virtual tables */

  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;
    KeyInfo *pKey;
    int addrIfNot;               /* address of OP_IfNot */
    int *aChngAddr;              /* Array of jump instruction addresses */

    if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
    VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
    nCol = pIdx->nColumn;
    pKey = sqlite3IndexKeyinfo(pParse, pIdx);
    if( iMem+1+(nCol*2)>pParse->nMem ){
      pParse->nMem = iMem+1+(nCol*2);
    }
    aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*pIdx->nColumn);
    if( aChngAddr==0 ) continue;

    /* 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_STAT2

    /* If this iteration of the loop is generating code to analyze the
    ** first index in the pTab->pIndex list, then register regLast has
    ** not been populated. In this case populate it now.  */
    if( once ){
      once = 0;
      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regSample2);

      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
      sqlite3VdbeAddOp3(v, OP_Add, regCount, regCount, regCount2);


      /* Generate code for a subroutine that store the most recent sample
      ** in the sqlite_stat2 table
      */
      shortJump = sqlite3VdbeAddOp0(v, OP_Goto);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 5, regRec, "aaaba", 0);
      VdbeComment((v, "begin stat2 write subroutine"));
      sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);

      sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);
      sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1);
      sqlite3VdbeAddOp2(v, OP_AddImm, regReady, -1);
      addrStoreStat2 = sqlite3VdbeAddOp2(v, OP_IfPos, regReady, shortJump+1);
      sqlite3VdbeAddOp1(v, OP_Return, regGosub);
      VdbeComment((v, "end stat2 write subroutine"));
      sqlite3VdbeJumpHere(v, shortJump);
    }
    /* Reset state registers */
    sqlite3VdbeAddOp2(v, OP_Copy, regCount2, regNext);
    shortJump = sqlite3VdbeAddOp3(v, OP_Lt, regSample2, 0, regCount);
    sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regCount, regNext);
    sqlite3VdbeJumpHere(v, shortJump);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleIdx);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regReady);

#endif /* SQLITE_ENABLE_STAT2 */

    /* The block of memory cells initialized here is used as follows.
    **
    **    iMem:                
    **        The total number of rows in the table.
    **
    **    iMem+1 .. iMem+nCol: 

    }

    /* Start the analysis loop. This loop runs through all the entries in
    ** the index b-tree.  */
    endOfLoop = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
    topOfLoop = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);  /* Increment row counter */

    for(i=0; i<nCol; i++){
      CollSeq *pColl;
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
      if( i==0 ){
#ifdef SQLITE_ENABLE_STAT2
        /* Check if the record that cursor iIdxCur points to contains a
        ** value that should be stored in the sqlite_stat2 table. If so,
        ** store it.  */
        int ne = sqlite3VdbeAddOp3(v, OP_Ne, iMem, 0, regNext);
        VdbeComment((v, "jump if not a sample"));



        sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
        sqlite3VdbeAddOp2(v, OP_Copy, regCol, regSample);
        sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1);


        /* Calculate new values for regNextSample.  Where N is the number
        ** of rows in the table and S is the number of samples to take:
        **
        **   nextSample = (sampleNumber*N*2 + N)/(2*S)

        */
        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1);
        sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext);


        sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext);
        sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext);


        sqlite3VdbeJumpHere(v, ne);

#endif

        /* Always record the very first row */
        addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
      }
      assert( pIdx->azColl!=0 );
      assert( pIdx->azColl[i]!=0 );
      pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
      aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
                                      (char*)pColl, P4_COLLSEQ);
      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
      VdbeComment((v, "jump if column %d changed", i));
#ifdef SQLITE_ENABLE_STAT2
      if( i==0 && addrStoreStat2 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleCnt, 1);
        VdbeComment((v, "incr repeat count"));
      }





#endif
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
    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 */
#ifdef SQLITE_ENABLE_STAT2
        sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
        sqlite3VdbeAddOp2(v, OP_Integer, 1, regSampleCnt);
#endif        
      }

      sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
    }
    sqlite3DbFree(db, aChngAddr);

    /* End of the analysis loop. */
    sqlite3VdbeResolveLabel(v, endOfLoop);
    sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
    sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
#ifdef SQLITE_ENABLE_STAT2
    sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
#endif        

    /* Store the results in sqlite_stat1.
    **
    ** The result is a single row of the sqlite_stat1 table.  The first
    ** two columns are the names of the table and index.  The third column
    ** is a string composed of a list of integer statistics about the
    ** index.  The first integer in the list is the total number of entries

*/
case OP_Copy: {             /* in1, out2 */
  pIn1 = &aMem[pOp->p1];
  pOut = &aMem[pOp->p2];
  assert( pOut!=pIn1 );
  sqlite3VdbeMemShallowCopy(pOut, pIn1, MEM_Ephem);
  Deephemeralize(pOut);

  break;
}

/* Opcode: SCopy P1 P2 * * *
**
** Make a shallow copy of register P1 into register P2.
**

** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  pIn1 = &aMem[pOp->p1];
  memAboutToChange(p, pIn1);
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;
  REGISTER_TRACE(pOp->p1, pIn1);
  break;
}

/* Opcode: MustBeInt P1 P2 * * *
** 
** Force the value in register P1 to be an integer.  If the value
** in P1 is not an integer and cannot be converted into an integer

do_test analyze2-1.1 {
  execsql { CREATE TABLE t1(x PRIMARY KEY) }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i) }
  }
  execsql { 
    ANALYZE;
    SELECT tbl, idx, sampleno, sample FROM sqlite_stat2;
  }
} [list t1 sqlite_autoindex_t1_1 0 49  \
        t1 sqlite_autoindex_t1_1 1 149 \
        t1 sqlite_autoindex_t1_1 2 249 \
        t1 sqlite_autoindex_t1_1 3 349 \
        t1 sqlite_autoindex_t1_1 4 449 \
        t1 sqlite_autoindex_t1_1 5 549 \
        t1 sqlite_autoindex_t1_1 6 649 \
        t1 sqlite_autoindex_t1_1 7 749 \
        t1 sqlite_autoindex_t1_1 8 849 \
        t1 sqlite_autoindex_t1_1 9 949 \
]

do_test analyze2-1.2 {
  execsql {
    DELETE FROM t1 WHERE x>9;
    ANALYZE;
    SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
  }
} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
do_test analyze2-1.3 {
  execsql {
    DELETE FROM t1 WHERE x>8;

  execsql ANALYZE
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x {99 299 499 699 899 ajj cjj ejj gjj ijj}}
do_test analyze2-3.2 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {99 299 499 699 899 ajj cjj ejj gjj ijj}}

do_eqp_test 3.3 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {