SQLite

  const char *zWhereType  /* Either "tbl" or "idx" */
){
  static const struct {
    const char *zName;
    const char *zCols;
  } aTable[] = {
    { "sqlite_stat1", "tbl,idx,stat" },
#if defined(SQLITE_ENABLE_STAT4)
    { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" },
#elif defined(SQLITE_ENABLE_STAT3)
    { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
#endif
  };

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

  int i;

#else
  UNUSED_PARAMETER(db);
  UNUSED_PARAMETER(pIdx);
#endif
}

#ifdef SQLITE_ENABLE_STAT4

/*
** The implementation of the sqlite_record() function. This function accepts
** a single argument of any type. The return value is a formatted database 
** record (a blob) containing the argument value.
**
** This is used to convert the value stored in the 'sample' column of the
** sqlite_stat3 table to the record format SQLite uses internally.
*/
static void recordFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const int file_format = 1;
  int iSerial;                    /* Serial type */
  int nSerial;                    /* Bytes of space for iSerial as varint */
  int nVal;                       /* Bytes of space required for argv[0] */
  int nRet;
  sqlite3 *db;
  u8 *aRet;

  iSerial = sqlite3VdbeSerialType(argv[0], file_format);
  nSerial = sqlite3VarintLen(iSerial);
  nVal = sqlite3VdbeSerialTypeLen(iSerial);
  db = sqlite3_context_db_handle(context);

  nRet = 1 + nSerial + nVal;
  aRet = sqlite3DbMallocRaw(db, nRet);
  if( aRet==0 ){
    sqlite3_result_error_nomem(context);
  }else{
    aRet[0] = nSerial+1;
    sqlite3PutVarint(&aRet[1], iSerial);
    sqlite3VdbeSerialPut(&aRet[1+nSerial], nVal, argv[0], file_format);
    sqlite3_result_blob(context, aRet, nRet, SQLITE_TRANSIENT);
    sqlite3DbFree(db, aRet);
  }
}

/*
** Register built-in functions used to help read ANALYZE data.
*/
void sqlite3AnalyzeFunctions(void){
  static SQLITE_WSD FuncDef aAnalyzeTableFuncs[] = {
    FUNCTION(sqlite_record,   1, 0, 0, recordFunc),
  };
  int i;
  FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
  FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aAnalyzeTableFuncs);
  for(i=0; i<ArraySize(aAnalyzeTableFuncs); i++){
    sqlite3FuncDefInsert(pHash, &aFunc[i]);
  }
}

/*
** Load the content from either the sqlite_stat4 or sqlite_stat3 table 
** into the relevant Index.aSample[] arrays.
**
** Arguments zSql1 and zSql2 must point to SQL statements that return
** data equivalent to the following (statements are different for stat3,
** see the caller of this function for details):
**
**    zSql1: SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx
**    zSql2: SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4
**
** where %Q is replaced with the database name before the SQL is executed.
*/
static int loadStatTbl(
  sqlite3 *db,                  /* Database handle */
  int bStat3,                   /* Assume single column records only */
  const char *zSql1,            /* SQL statement 1 (see above) */
  const char *zSql2,            /* SQL statement 2 (see above) */
  const char *zDb               /* Database name (e.g. "main") */
){
  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 );




  zSql = sqlite3MPrintf(db, zSql1, 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 ){
    int nIdxCol = 1;              /* Number of columns in stat4 records */
    int nAvgCol = 1;              /* Number of entries in Index.aAvgEq */

    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 );
    if( bStat3==0 ){
      nIdxCol = pIdx->nColumn+1;
      nAvgCol = pIdx->nColumn;
    }
    pIdx->nSample = nSample;
    nByte = sizeof(IndexSample) * nSample;
    nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
    nByte += nAvgCol * sizeof(tRowcnt);     /* Space for Index.aAvgEq[] */

    pIdx->aSample = sqlite3DbMallocZero(db, nByte);
    if( pIdx->aSample==0 ){
      sqlite3_finalize(pStmt);
      return SQLITE_NOMEM;
    }
    pSpace = (tRowcnt*)&pIdx->aSample[nSample];
    pIdx->aAvgEq = pSpace; pSpace += nAvgCol;
    for(i=0; i<pIdx->nSample; i++){
      pIdx->aSample[i].anEq = pSpace; pSpace += nIdxCol;
      pIdx->aSample[i].anLt = pSpace; pSpace += nIdxCol;
      pIdx->aSample[i].anDLt = pSpace; pSpace += nIdxCol;
    }
    assert( ((u8*)pSpace)-nByte==(u8*)(pIdx->aSample) );
  }
  rc = sqlite3_finalize(pStmt);
  if( rc ) return rc;

  zSql = sqlite3MPrintf(db, zSql2, 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 */
    int nCol = 1;                 /* 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];

    if( bStat3==0 ){
      nCol = pIdx->nColumn+1;
    }
    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 ){
      IndexSample *aSample = pIdx->aSample;
      int iCol;

          }
        }
        if( nDLt>nSum ){
          avgEq = (pSample->anLt[iCol] - sumEq)/(nDLt - nSum);
        }
        if( avgEq==0 ) avgEq = 1;
        pIdx->aAvgEq[iCol] = avgEq;
        if( bStat3 ) break;
      }
    }

    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);
}

/*
** Load content from the sqlite_stat4 and sqlite_stat3 tables into 
** the Index.aSample[] arrays of all indices.
*/
static int loadStat4(sqlite3 *db, const char *zDb){
  int rc = SQLITE_OK;             /* Result codes from subroutines */

  assert( db->lookaside.bEnabled==0 );
  if( sqlite3FindTable(db, "sqlite_stat4", zDb) ){
    rc = loadStatTbl(db, 0,
      "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx", 
      "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4",
      zDb
    );
  }

  if( rc==SQLITE_OK && sqlite3FindTable(db, "sqlite_stat3", zDb) ){
    rc = loadStatTbl(db, 1,
      "SELECT idx,count(*) FROM %Q.sqlite_stat3 GROUP BY idx", 
      "SELECT idx,neq,nlt,ndlt,sqlite_record(sample) FROM %Q.sqlite_stat3",
      zDb
    );
  }

  return rc;
}
#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.

  for(i=0; i<ArraySize(aBuiltinFunc); i++){
    sqlite3FuncDefInsert(pHash, &aFunc[i]);
  }
  sqlite3RegisterDateTimeFunctions();
#ifndef SQLITE_OMIT_ALTERTABLE
  sqlite3AlterFunctions();
#endif
#if defined(SQLITE_ENABLE_STAT3) || defined(SQLITE_ENABLE_STAT4)
  sqlite3AnalyzeFunctions();
#endif
}

#ifndef SQLITE_OMIT_WSD
extern int sqlite3PendingByte;
#endif
#endif
void sqlite3RootPageMoved(sqlite3*, int, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(void);
void sqlite3AnalyzeFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);

      zOut[14-i*2]   = "0123456789abcdef"[v.x[i]>>4];
      zOut[14-i*2+1] = "0123456789abcdef"[v.x[i]&0xf];
    }
  }
  zOut[16] = 0;
  sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
}

/*
** tclcmd: test_extract(record, field)
**
** This function implements an SQL user-function that accepts a blob
** containing a formatted database record as the first argument. The
** second argument is the index of the field within that record to
** extract and return.
*/
static void test_extract(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  u8 *pRec;
  u8 *pEndHdr;                    /* Points to one byte past record header */
  u8 *pHdr;                       /* Current point in record header */
  u8 *pBody;                      /* Current point in record data */
  u64 nHdr;                       /* Bytes in record header */
  int iIdx;                       /* Required field */
  int iCurrent = 0;               /* Current field */

  assert( argc==2 );
  pRec = (u8*)sqlite3_value_blob(argv[0]);
  iIdx = sqlite3_value_int(argv[1]);

  pHdr = pRec + sqlite3GetVarint(pRec, &nHdr);
  pBody = pEndHdr = &pRec[nHdr];

  for(iCurrent=0; pHdr<pEndHdr && iCurrent<=iIdx; iCurrent++){
    u64 iSerialType;
    Mem mem;

    memset(&mem, 0, sizeof(mem));
    mem.db = db;
    mem.enc = SQLITE_UTF8;
    pHdr += sqlite3GetVarint(pHdr, &iSerialType);
    pBody += sqlite3VdbeSerialGet(pBody, (u32)iSerialType, &mem);
    sqlite3VdbeMemStoreType(&mem);

    if( iCurrent==iIdx ){
      sqlite3_result_value(context, &mem);
    }

    sqlite3DbFree(db, mem.zMalloc);
  }
}

/*
** tclcmd: test_decode(record)
**
** This function implements an SQL user-function that accepts a blob
** containing a formatted database record as its only argument. It returns
** a tcl list (type SQLITE_TEXT) containing each of the values stored

    { "test_error",            1, SQLITE_UTF8, test_error},
    { "test_error",            2, SQLITE_UTF8, test_error},
    { "test_eval",             1, SQLITE_UTF8, test_eval},
    { "test_isolation",        2, SQLITE_UTF8, test_isolation},
    { "test_counter",          1, SQLITE_UTF8, counterFunc},
    { "real2hex",              1, SQLITE_UTF8, real2hex},
    { "test_decode",           1, SQLITE_UTF8, test_decode},
    { "test_extract",          2, SQLITE_UTF8, test_extract},
  };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
    sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
        aFuncs[i].eTextRep, 0, aFuncs[i].xFunc, 0, 0);
  }

# 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.
#
#***********************************************************************
#
# This file contains automated tests used to verify that the sqlite_stat4
# functionality is working.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix analyze9

ifcapable !stat4 {

# 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.
#
#***********************************************************************
#
# This file contains automated tests used to verify that the current build
# (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3
# and stat4 data.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix analyzeA

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

proc populate_stat3 {} {
  # Open a second connection on database "test.db" and run ANALYZE. If this
  # is an ENABLE_STAT3 build, this is all that is required to create and
  # populate the sqlite_stat3 table. 
  # 
  sqlite3 db2 test.db
  execsql { ANALYZE }

  # Now, if this is an ENABLE_STAT4 build, create and populate the 
  # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE
  # above. Then drop the sqlite_stat4 table.
  #
  ifcapable stat4 {
    db2 func lindex lindex
    execsql {
      PRAGMA writable_schema = on;
      CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
      INSERT INTO sqlite_stat3 
      SELECT DISTINCT tbl, idx, 
      lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0)
      FROM sqlite_stat4;
      DROP TABLE sqlite_stat4;
      PRAGMA writable_schema = off;
    } db2
  }

  # Modify the database schema cookie to ensure that the other connection
  # reloads the schema.
  #
  execsql {
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2
  db2 close
}


proc populate_stat4 {} {
  execsql { ANALYZE }
#  ifcapable stat3 {
#    execsql {
#      PRAGMA writable_schema = on;
#      CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
#      INSERT INTO sqlite_stat4 SELECT 
#          tbl, idx, nlt, neq, ndlt,
#          test_extract(sample, 1)
#      FROM sqlite_stat4;
#      DROP TABLE sqlite_stat4;
#      PRAGMA writable_schema = off;
#      ANALYZE sqlite_master;
#    }
#  }
}

foreach {tn analyze_cmd} {1 populate_stat4 2 populate_stat3} {
  reset_db
  do_test 1.$tn.1 {
    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
    for {set i 0} {$i < 100} {incr i} {
      set c [expr int(pow(1.1,$i)/100)]
      set b [expr 125 - int(pow(1.1,99-$i))/100]
      execsql {INSERT INTO t1 VALUES($i, $b, $c)}
    }
  } {}

  execsql { CREATE INDEX t1b ON t1(b) }
  execsql { CREATE INDEX t1c ON t1(c) }
  $analyze_cmd

  do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1
  do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0  } 49
  do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125  } 49
  do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16  } 1

  do_eqp_test 1.$tn.2.5 {
    SELECT * FROM t1 WHERE b = 31 AND c = 0;
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  do_eqp_test 1.$tn.2.6 {
    SELECT * FROM t1 WHERE b = 125 AND c = 16;
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}}

  do_execsql_test 1.$tn.3.1 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50
  } {6}
  do_execsql_test 1.$tn.3.2 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50
  } {90}

  do_execsql_test 1.$tn.3.3 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125
  } {90}
  do_execsql_test 1.$tn.3.4 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125
  } {6}

  do_eqp_test 1.$tn.3.5 {
    SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}

  do_eqp_test 1.$tn.3.6 {
    SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
}


finish_test