sqlite/ext/rbu/rbuvacuum2.test

# 2016 June 1
#
# 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 tests for the RBU module. More specifically, it
# contains tests to ensure that the sqlite3rbu_vacuum() API works as
# expected.
#

source [file join [file dirname [info script]] rbu_common.tcl]
if_no_rbu_support { finish_test ; return }

foreach {step} {0 1} {
foreach {ttt state} {
  s state.db t test.db-vacuum n {}
} {
  set ::testprefix rbuvacuum2-$step$ttt
  
  #-------------------------------------------------------------------------
  # Test that a database that contains fts3 tables can be vacuumed.
  #
  ifcapable fts3 {
    reset_db
    do_execsql_test 1.1 {
      CREATE VIRTUAL TABLE t1 USING fts3(z, y);
      INSERT INTO t1 VALUES('fix this issue', 'at some point');
    }
  
    do_rbu_vacuum_test 1.2 $step $state
  
    do_execsql_test 1.3 {
      SELECT * FROM t1;
    } {{fix this issue} {at some point}}
  
    do_execsql_test 1.4 {
      SELECT rowid FROM t1 WHERE t1 MATCH 'fix';
    } {1}

    do_execsql_test 1.5 {
      INSERT INTO t1 VALUES('a b c', 'd e f');
      INSERT INTO t1 VALUES('l h i', 'd e f');
      DELETE FROM t1 WHERE docid = 2;
      INSERT INTO t1 VALUES('a b c', 'x y z');
    }

    do_rbu_vacuum_test 1.6 $step $state
    do_execsql_test 1.7 {
      INSERT INTO t1(t1) VALUES('integrity-check');
      SELECT * FROM t1;
    } {
      {fix this issue} {at some point}
      {l h i} {d e f}
      {a b c} {x y z}
    }
  }
  
  #-------------------------------------------------------------------------
  # Test that a database that contains fts5 tables can be vacuumed.
  #
  ifcapable fts5 {
    reset_db
    do_execsql_test 2.1 {
      CREATE VIRTUAL TABLE t1 USING fts5(z, y);
      INSERT INTO t1 VALUES('fix this issue', 'at some point');
    }
  
    do_rbu_vacuum_test 2.2 $step $state
  
    do_execsql_test 2.3 {
      SELECT * FROM t1;
    } {{fix this issue} {at some point}}
  
    do_execsql_test 2.4 {
      SELECT rowid FROM t1 ('fix');
    } {1}

    do_execsql_test 2.5 {
      INSERT INTO t1 VALUES('a b c', 'd e f');
      INSERT INTO t1 VALUES('l h i', 'd e f');
      DELETE FROM t1 WHERE rowid = 2;
      INSERT INTO t1 VALUES('a b c', 'x y z');
    }

    do_rbu_vacuum_test 2.6 $step $state
    do_execsql_test 2.7 {
      INSERT INTO t1(t1) VALUES('integrity-check');
      SELECT * FROM t1;
    } {
      {fix this issue} {at some point}
      {l h i} {d e f}
      {a b c} {x y z}
    }
  }

  #-------------------------------------------------------------------------
  # Test that a database that contains an rtree table can be vacuumed.
  #
  ifcapable rtree {
    reset_db
    do_execsql_test 3.1 {
      CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
      INSERT INTO rt VALUES(1, 45, 55);
      INSERT INTO rt VALUES(2, 50, 60);
      INSERT INTO rt VALUES(3, 55, 65);
    }
  
    do_rbu_vacuum_test 3.2 $step $state
  
    do_execsql_test 3.3 {
      SELECT * FROM rt;
    } {1 45.0 55.0 2 50.0 60.0 3 55.0 65.0}
  
    do_execsql_test 3.4.1 {
      SELECT rowid FROM rt WHERE x2>51 AND x1 < 51
    } {1 2}
    do_execsql_test 3.4.2 {
      SELECT rowid FROM rt WHERE x2>59 AND x1 < 59
    } {2 3}

    do_rbu_vacuum_test 3.5 $step $state

    do_execsql_test 3.6.1 {
      SELECT rowid FROM rt WHERE x2>51 AND x1 < 51
    } {1 2}
    do_execsql_test 3.6.2 {
      SELECT rowid FROM rt WHERE x2>59 AND x1 < 59
    } {2 3}
  }

  ifcapable trigger {
    reset_db
    do_execsql_test 4.1 {
      CREATE TABLE t1(a, b, c);
      INSERT INTO t1 VALUES(1, 2, 3);
      CREATE VIEW v1 AS SELECT * FROM t1;
      CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END;
    }

    do_execsql_test 4.2 {
      SELECT * FROM sqlite_master;
    } {
    table t1 t1 2 {CREATE TABLE t1(a, b, c)}
    view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t1}
    trigger tr1 t1 0 {CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END}
    }

    do_rbu_vacuum_test 4.3 $step $state
    do_execsql_test 4.4 {
      SELECT * FROM sqlite_master;
    } {
    table t1 t1 2 {CREATE TABLE t1(a, b, c)}
    view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t1}
    trigger tr1 t1 0 {CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END}
    }
  }
}
}
  
#-------------------------------------------------------------------------
# Test that passing a NULL value as the second argument to 
# sqlite3rbu_vacuum() causes it to:
#
#   * Use <database>-vacuum as the state db, and
#   * Set the state db permissions to the same as those on the db file.
#
db close
if {$::tcl_platform(platform)=="unix"} {
  forcedelete test.db

  sqlite3 db test.db
  do_execsql_test 5.0 {
    CREATE TABLE t1(a, 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 close

  foreach {tn perm} {
    1 00755
    2 00666
    3 00644
    4 00444
  } {
    forcedelete test.db-vacuum

    do_test 5.$tn.1 {
      file attributes test.db -permissions $perm
      sqlite3rbu_vacuum rbu test.db
      rbu step
    } {SQLITE_OK}

    do_test 5.$tn.2 { file exists test.db-vacuum } 1
    # The result pattern might be 00xxx or 0oxxx depending on which
    # version of TCL is being used.  So make perm2 into a regexp that
    # will match either
    regsub {^00} $perm {0.} perm2
    do_test 5.$tn.3 { file attributes test.db-vacuum -permissions} /$perm2/
    rbu close
  }
}

#-------------------------------------------------------------------------
# Test the outcome of some other connection running a checkpoint while
# the incremental checkpoint is suspended.
#
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i1b ON t1(b);
  CREATE INDEX i1c ON t1(c);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
}
forcedelete test.db2

do_test 6.1 {
  sqlite3rbu_vacuum rbu test.db test.db2
  while {[rbu state]!="checkpoint"} { rbu step }
  rbu close
} {SQLITE_OK}

do_test 6.2 {
  execsql { SELECT 1 FROM sqlite_master LIMIT 1 }
  execsql { PRAGMA wal_checkpoint }
  execsql { SELECT 1 FROM sqlite_master LIMIT 1 }
} {1}

do_test 6.3 {
  sqlite3rbu_vacuum rbu test.db test.db2
  while {[rbu step]!="SQLITE_DONE"} { rbu step }
  rbu close
  execsql { PRAGMA integrity_check }
} {ok}

do_test 6.4 {
  sqlite3rbu_vacuum rbu test.db test.db-vactmp
  list [catch { rbu close } msg] $msg
} {1 SQLITE_MISUSE}

finish_test