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