modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/rbu/rbuvacuum2.test (about)

     1  # 2016 June 1
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  # This file contains tests for the RBU module. More specifically, it
    13  # contains tests to ensure that the sqlite3rbu_vacuum() API works as
    14  # expected.
    15  #
    16  
    17  source [file join [file dirname [info script]] rbu_common.tcl]
    18  
    19  foreach step {0 1} {
    20    set ::testprefix rbuvacuum2-$step
    21    
    22    #-------------------------------------------------------------------------
    23    # Test that a database that contains fts3 tables can be vacuumed.
    24    #
    25    ifcapable fts3 {
    26      reset_db
    27      do_execsql_test 1.1 {
    28        CREATE VIRTUAL TABLE t1 USING fts3(z, y);
    29        INSERT INTO t1 VALUES('fix this issue', 'at some point');
    30      }
    31    
    32      do_rbu_vacuum_test 1.2 $step
    33    
    34      do_execsql_test 1.3 {
    35        SELECT * FROM t1;
    36      } {{fix this issue} {at some point}}
    37    
    38      do_execsql_test 1.4 {
    39        SELECT rowid FROM t1 WHERE t1 MATCH 'fix';
    40      } {1}
    41  
    42      do_execsql_test 1.5 {
    43        INSERT INTO t1 VALUES('a b c', 'd e f');
    44        INSERT INTO t1 VALUES('l h i', 'd e f');
    45        DELETE FROM t1 WHERE docid = 2;
    46        INSERT INTO t1 VALUES('a b c', 'x y z');
    47      }
    48  
    49      do_rbu_vacuum_test 1.6 $step
    50      do_execsql_test 1.7 {
    51        INSERT INTO t1(t1) VALUES('integrity-check');
    52        SELECT * FROM t1;
    53      } {
    54        {fix this issue} {at some point}
    55        {l h i} {d e f}
    56        {a b c} {x y z}
    57      }
    58    }
    59    
    60    #-------------------------------------------------------------------------
    61    # Test that a database that contains fts5 tables can be vacuumed.
    62    #
    63    ifcapable fts5 {
    64      reset_db
    65      do_execsql_test 2.1 {
    66        CREATE VIRTUAL TABLE t1 USING fts5(z, y);
    67        INSERT INTO t1 VALUES('fix this issue', 'at some point');
    68      }
    69    
    70      do_rbu_vacuum_test 2.2 $step
    71    
    72      do_execsql_test 2.3 {
    73        SELECT * FROM t1;
    74      } {{fix this issue} {at some point}}
    75    
    76      do_execsql_test 2.4 {
    77        SELECT rowid FROM t1 ('fix');
    78      } {1}
    79  
    80      do_execsql_test 2.5 {
    81        INSERT INTO t1 VALUES('a b c', 'd e f');
    82        INSERT INTO t1 VALUES('l h i', 'd e f');
    83        DELETE FROM t1 WHERE rowid = 2;
    84        INSERT INTO t1 VALUES('a b c', 'x y z');
    85      }
    86  
    87      do_rbu_vacuum_test 2.6 $step
    88      do_execsql_test 2.7 {
    89        INSERT INTO t1(t1) VALUES('integrity-check');
    90        SELECT * FROM t1;
    91      } {
    92        {fix this issue} {at some point}
    93        {l h i} {d e f}
    94        {a b c} {x y z}
    95      }
    96    }
    97  
    98    #-------------------------------------------------------------------------
    99    # Test that a database that contains an rtree table can be vacuumed.
   100    #
   101    ifcapable rtree {
   102      reset_db
   103      do_execsql_test 3.1 {
   104        CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
   105        INSERT INTO rt VALUES(1, 45, 55);
   106        INSERT INTO rt VALUES(2, 50, 60);
   107        INSERT INTO rt VALUES(3, 55, 65);
   108      }
   109    
   110      do_rbu_vacuum_test 3.2 $step
   111    
   112      do_execsql_test 3.3 {
   113        SELECT * FROM rt;
   114      } {1 45.0 55.0 2 50.0 60.0 3 55.0 65.0}
   115    
   116      do_execsql_test 3.4.1 {
   117        SELECT rowid FROM rt WHERE x2>51 AND x1 < 51
   118      } {1 2}
   119      do_execsql_test 3.4.2 {
   120        SELECT rowid FROM rt WHERE x2>59 AND x1 < 59
   121      } {2 3}
   122  
   123      do_rbu_vacuum_test 3.5 $step
   124  
   125      do_execsql_test 3.6.1 {
   126        SELECT rowid FROM rt WHERE x2>51 AND x1 < 51
   127      } {1 2}
   128      do_execsql_test 3.6.2 {
   129        SELECT rowid FROM rt WHERE x2>59 AND x1 < 59
   130      } {2 3}
   131    }
   132  
   133    ifcapable trigger {
   134      reset_db
   135      do_execsql_test 4.1 {
   136        CREATE TABLE t1(a, b, c);
   137        INSERT INTO t1 VALUES(1, 2, 3);
   138        CREATE VIEW v1 AS SELECT * FROM t1;
   139        CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END;
   140      }
   141  
   142      do_execsql_test 4.2 {
   143        SELECT * FROM sqlite_master;
   144      } {
   145      table t1 t1 2 {CREATE TABLE t1(a, b, c)}
   146      view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t1}
   147      trigger tr1 t1 0 {CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END}
   148      }
   149  
   150      do_rbu_vacuum_test 4.3 $step
   151      do_execsql_test 4.4 {
   152        SELECT * FROM sqlite_master;
   153      } {
   154      table t1 t1 2 {CREATE TABLE t1(a, b, c)}
   155      view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t1}
   156      trigger tr1 t1 0 {CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END}
   157      }
   158    }
   159  }
   160    
   161  #-------------------------------------------------------------------------
   162  # Test that passing a NULL value as the second argument to 
   163  # sqlite3rbu_vacuum() causes it to:
   164  #
   165  #   * Use <database>-vacuum as the state db, and
   166  #   * Set the state db permissions to the same as those on the db file.
   167  #
   168  db close
   169  if {$::tcl_platform(platform)=="unix"} {
   170    forcedelete test.db
   171  
   172    sqlite3 db test.db
   173    do_execsql_test 5.0 {
   174      CREATE TABLE t1(a, b);
   175      INSERT INTO t1 VALUES(1, 2);
   176      INSERT INTO t1 VALUES(3, 4);
   177      INSERT INTO t1 VALUES(5, 6);
   178      INSERT INTO t1 VALUES(7, 8);
   179    }
   180    db close
   181  
   182    foreach {tn perm} {
   183      1 00755
   184      2 00666
   185      3 00644
   186      4 00444
   187    } {
   188      forcedelete test.db-vacuum
   189  
   190      do_test 5.$tn.1 {
   191        file attributes test.db -permissions $perm
   192        sqlite3rbu_vacuum rbu test.db
   193        rbu step
   194      } {SQLITE_OK}
   195  
   196      do_test 5.$tn.2 { file exists test.db-vacuum } 1
   197      do_test 5.$tn.3 { file attributes test.db-vacuum -permissions} $perm
   198      rbu close
   199    }
   200  }
   201  
   202  #-------------------------------------------------------------------------
   203  # Test the outcome of some other connection running a checkpoint while
   204  # the incremental checkpoint is suspended.
   205  #
   206  reset_db
   207  do_execsql_test 6.0 {
   208    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   209    CREATE INDEX i1b ON t1(b);
   210    CREATE INDEX i1c ON t1(c);
   211    INSERT INTO t1 VALUES(1, 2, 3);
   212    INSERT INTO t1 VALUES(4, 5, 6);
   213  }
   214  forcedelete test.db2
   215  
   216  do_test 6.1 {
   217    sqlite3rbu_vacuum rbu test.db test.db2
   218    while {[rbu state]!="checkpoint"} { rbu step }
   219    rbu close
   220  } {SQLITE_OK}
   221  
   222  do_execsql_test 6.2 {
   223    SELECT 1 FROM sqlite_master LIMIT 1;
   224    PRAGMA wal_checkpoint;
   225  } {1 0 4 4}
   226  
   227  do_test 6.3 {
   228    sqlite3rbu_vacuum rbu test.db test.db2
   229    while {[rbu step]!="SQLITE_DONE"} { rbu step }
   230    rbu close
   231    execsql { PRAGMA integrity_check }
   232  } {ok}
   233  
   234  finish_test
   235