SQLite

Artifact [e3a3f5876c]
Login

Artifact e3a3f5876ce1526b48f6f447ee0b18960ac683e3fc891791e1ca0c08e823d498:


# 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.5 { SELECT count(*) FROM t1 } 32

finish_test