SQLite

# 2018 January 12
#
# 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.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source [file join [file dirname [info script]] session_common.tcl]
source $testdir/tester.tcl
ifcapable !session {finish_test; return}

set testprefix sessionstat1

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  CREATE INDEX t1b ON t1(b);
  CREATE INDEX t1c ON t1(c);

  WITH s(i) AS (
    SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
  )
  INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
}

do_iterator_test 1.1 {} {
  ANALYZE
} {
  {INSERT sqlite_stat1 0 XX. {} {t t1 t sqlite_autoindex_t1_1 t {32 1}}}
  {INSERT sqlite_stat1 0 XX. {} {t t1 t t1b t {32 4}}} 
  {INSERT sqlite_stat1 0 XX. {} {t t1 t t1c t {32 16}}}
}

do_execsql_test 1.2 {
  WITH s(i) AS (
    SELECT 32 UNION ALL SELECT i+1 FROM s WHERE (i+1)<64
  )
  INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
}

do_iterator_test 1.3 {} {
  ANALYZE
} {
  {UPDATE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {32 1}} {{} {} {} {} t {64 1}}} 
  {UPDATE sqlite_stat1 0 XX. {t t1 t t1b t {32 4}} {{} {} {} {} t {64 8}}} 
  {UPDATE sqlite_stat1 0 XX. {t t1 t t1c t {32 16}} {{} {} {} {} t {64 32}}}
}

do_iterator_test 1.5 {} {
  DROP INDEX t1b;
} {
  {DELETE sqlite_stat1 0 XX. {t t1 t t1b t {64 8}} {}}
}

do_iterator_test 1.6 {} {
  DROP TABLE t1;
} {
  {DELETE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {64 1}} {}}
  {DELETE sqlite_stat1 0 XX. {t t1 t t1c t {64 32}} {}}
}

#-------------------------------------------------------------------------
#
catch { db2 close }
forcedelete test.db2
sqlite3 db2 test.db2

do_test 2.0 {
  do_common_sql {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);
    ANALYZE;
  }
} {}

do_test 2.1 {
  do_then_apply_sql {
    WITH s(i) AS (
        SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
    )
    INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
    ANALYZE;
  }
} {}

do_execsql_test -db db2 2.2 {
  SELECT * FROM sqlite_stat1
} {
  t1 sqlite_autoindex_t1_1 {32 1} 
  t1 t1b {32 4} 
  t1 t1c {32 16}
}

do_test 2.3 {
  do_then_apply_sql { DROP INDEX t1c }
} {}

do_execsql_test -db db2 2.4 {
  SELECT * FROM sqlite_stat1
} {
  t1 sqlite_autoindex_t1_1 {32 1} 
  t1 t1b {32 4} 
}

do_test 2.3 {
  do_then_apply_sql { DROP TABLE t1 }
} {}

do_execsql_test -db db2 2.4 {
  SELECT * FROM sqlite_stat1
} {
}

do_execsql_test -db db2 2.4 { SELECT count(*) FROM t1 } 32

finish_test

  u8 *pAlloc = 0;
  char **azCol = 0;
  u8 *abPK = 0;

  assert( pazCol && pabPK );

  nThis = sqlite3Strlen30(zThis);
  if( nThis==12 && 0==sqlite3_stricmp("sqlite_stat1", zThis) ){
    /* For sqlite_stat1, pretend that (tbl,idx) is the PRIMARY KEY. */
    zPragma = sqlite3_mprintf(
        "SELECT 0, 'tbl',  '', 0, '', 1     UNION ALL "
        "SELECT 1, 'idx',  '', 0, '', 2     UNION ALL "
        "SELECT 2, 'stat', '', 0, '', 0"
    );
  }else{
    zPragma = sqlite3_mprintf("PRAGMA '%q'.table_info('%q')", zDb, zThis);
  }
  if( !zPragma ) return SQLITE_NOMEM;

  rc = sqlite3_prepare_v2(db, zPragma, -1, &pStmt, 0);
  sqlite3_free(zPragma);
  if( rc!=SQLITE_OK ) return rc;

  nByte = nThis + 1;

      aCreateTbl[i] = 0;
      sqlite3TableLock(pParse, iDb, aRoot[i], 1, zTab);
      if( zWhere ){
        sqlite3NestedParse(pParse,
           "DELETE FROM %Q.%s WHERE %s=%Q",
           pDb->zDbSName, zTab, zWhereType, zWhere
        );
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
      }else if( db->xPreUpdateCallback ){
        sqlite3NestedParse(pParse, "DELETE FROM %Q.%s", pDb->zDbSName, zTab);
#endif
      }else{
        /* The sqlite_stat[134] table already exists.  Delete all rows. */
        sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
      }
    }
  }

  int regRowid = iMem++;       /* Rowid argument passed to stat_push() */
#endif
  int regTemp = iMem++;        /* Temporary use register */
  int regTabname = iMem++;     /* Register containing table name */
  int regIdxname = iMem++;     /* Register containing index name */
  int regStat1 = iMem++;       /* Value for the stat column of sqlite_stat1 */
  int regPrev = iMem;          /* MUST BE LAST (see below) */
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  Table *pStat1 = 0; 
#endif

  pParse->nMem = MAX(pParse->nMem, iMem);
  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) ){
    return;
  }
  if( pTab->tnum==0 ){

  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
#ifndef SQLITE_OMIT_AUTHORIZATION
  if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0,
      db->aDb[iDb].zDbSName ) ){
    return;
  }
#endif

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
  if( db->xPreUpdateCallback ){
    pStat1 = (Table*)sqlite3DbMallocZero(db, sizeof(Table) + 13);
    if( pStat1==0 ) return;
    pStat1->zName = (char*)&pStat1[1];
    memcpy(pStat1->zName, "sqlite_stat1", 13);
    pStat1->nCol = 3;
    pStat1->iPKey = -1;
    sqlite3VdbeAddOp4(pParse->pVdbe, OP_Noop, 0, 0, 0,(char*)pStat1,P4_DYNBLOB);
  }
#endif

  /* Establish a read-lock on the table at the shared-cache level. 
  ** Open a read-only cursor on the table. Also allocate a cursor number
  ** to use for scanning indexes (iIdxCur). No index cursor is opened at
  ** this time though.  */
  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  iTabCur = iTab++;

    /* Add the entry to the stat1 table. */
    callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
    assert( "BBB"[0]==SQLITE_AFF_TEXT );
    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
    sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE);
#endif
    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);

    /* Add the entries to the stat3 or stat4 table. */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
    {
      int regEq = regStat1;
      int regLt = regStat1+1;

  ** the update-hook is not invoked for rows removed by REPLACE, but the 
  ** pre-update-hook is.
  */ 
  if( pTab->pSelect==0 ){
    u8 p5 = 0;
    sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,iIdxNoSeek);
    sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0));
    if( pParse->nested==0 || 0==sqlite3_stricmp(pTab->zName, "sqlite_stat1") ){
      sqlite3VdbeAppendP4(v, (char*)pTab, P4_TABLE);
    }
    if( eMode!=ONEPASS_OFF ){
      sqlite3VdbeChangeP5(v, OPFLAG_AUXDELETE);
    }
    if( iIdxNoSeek>=0 && iIdxNoSeek!=iDataCur ){
      sqlite3VdbeAddOp1(v, OP_Delete, iIdxNoSeek);

      (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), seekResult
  );
  pC->deferredMoveto = 0;
  pC->cacheStatus = CACHE_STALE;

  /* Invoke the update-hook if required. */
  if( rc ) goto abort_due_to_error;
  assert( !op || pTab->aCol || !sqlite3_stricmp(pTab->zName,"sqlite_stat1") );
  if( db->xUpdateCallback && op && pTab->aCol ){
    db->xUpdateCallback(db->pUpdateArg, op, zDb, pTab->zName, x.nKey);
  }
  break;
}

/* Opcode: Delete P1 P2 P3 P4 P5
**

#define P4_EXPR       (-10) /* P4 is a pointer to an Expr tree */
#define P4_MEM        (-11) /* P4 is a pointer to a Mem*    structure */
#define P4_VTAB       (-12) /* P4 is a pointer to an sqlite3_vtab structure */
#define P4_REAL       (-13) /* P4 is a 64-bit floating point value */
#define P4_INT64      (-14) /* P4 is a 64-bit signed integer */
#define P4_INTARRAY   (-15) /* P4 is a vector of 32-bit integers */
#define P4_FUNCCTX    (-16) /* P4 is a pointer to an sqlite3_context object */
#define P4_DYNBLOB    (-17) /* Pointer to memory from sqliteMalloc() */

/* Error message codes for OP_Halt */
#define P5_ConstraintNotNull 1
#define P5_ConstraintUnique  2
#define P5_ConstraintCheck   3
#define P5_ConstraintFK      4

    case P4_FUNCCTX: {
      freeP4FuncCtx(db, (sqlite3_context*)p4);
      break;
    }
    case P4_REAL:
    case P4_INT64:
    case P4_DYNAMIC:
    case P4_DYNBLOB:
    case P4_INTARRAY: {
      sqlite3DbFree(db, p4);
      break;
    }
    case P4_KEYINFO: {
      if( db->pnBytesFreed==0 ) sqlite3KeyInfoUnref((KeyInfo*)p4);
      break;

      sqlite3StrAccumAppend(&x, "]", 1);
      break;
    }
    case P4_SUBPROGRAM: {
      sqlite3XPrintf(&x, "program");
      break;
    }
    case P4_DYNBLOB:
    case P4_ADVANCE: {
      zTemp[0] = 0;
      break;
    }
    case P4_TABLE: {
      sqlite3XPrintf(&x, "%s", pOp->p4.pTab->zName);
      break;

} {
  INSERT main t3 1 1 0 {} 1
}
do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
do_preupdate_test 10.3 {
  DELETE FROM t3 WHERE b=1
} {DELETE main t3 1 1 0 {} 1}

#-------------------------------------------------------------------------
# Test that the "update" hook is not fired for operations on the 
# sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is
# registered.
ifcapable analyze {
  reset_db
  do_execsql_test 11.1 {
    CREATE TABLE t1(a, b);
    CREATE INDEX idx1 ON t1(a);
    CREATE INDEX idx2 ON t1(b);

    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
    INSERT INTO t1 VALUES(5, 6);
    INSERT INTO t1 VALUES(7, 8);
  }

  db preupdate hook preupdate_cb
  db update_hook update_cb

  proc preupdate_cb {args} { lappend ::res "preupdate" $args }
  proc update_cb {args} { lappend ::res "update" $args }

  set ::res [list]
  do_test 11.2 {
    execsql ANALYZE
    set ::res
  } [list {*}{
    preupdate {INSERT main sqlite_stat1 1 1}
    preupdate {INSERT main sqlite_stat1 2 2}
  }]

  do_execsql_test 11.3 {
    INSERT INTO t1 VALUES(9, 10);
    INSERT INTO t1 VALUES(11, 12);
    INSERT INTO t1 VALUES(13, 14);
    INSERT INTO t1 VALUES(15, 16);
  }

  set ::res [list]
  do_test 11.4 {
    execsql ANALYZE
    set ::res
  } [list {*}{
    preupdate {DELETE main sqlite_stat1 1 1}
    preupdate {DELETE main sqlite_stat1 2 2}
    preupdate {INSERT main sqlite_stat1 1 1}
    preupdate {INSERT main sqlite_stat1 2 2}
  }]
}


finish_test