1# 2006 June 10 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# This file implements regression tests for SQLite library. The 12# focus is on testing the following virtual table methods: 13# 14# xBegin 15# xSync 16# xCommit 17# xRollback 18# 19# $Id: vtab4.test,v 1.3 2008/07/12 14:52:21 drh Exp $ 20 21set testdir [file dirname $argv0] 22source $testdir/tester.tcl 23 24unset -nocomplain echo_module 25unset -nocomplain echo_module_sync_fail 26 27ifcapable !vtab { 28 finish_test 29 return 30} 31 32# Register the echo module 33db cache size 0 34register_echo_module [sqlite3_connection_pointer db] 35 36do_test vtab4-1.1 { 37 execsql { 38 CREATE TABLE treal(a PRIMARY KEY, b, c); 39 CREATE VIRTUAL TABLE techo USING echo(treal); 40 } 41} {} 42 43# Test an INSERT, UPDATE and DELETE statement on the virtual table 44# in an implicit transaction. Each should result in a single call 45# to xBegin, xSync and xCommit. 46# 47do_test vtab4-1.2 { 48 set echo_module [list] 49 execsql { 50 INSERT INTO techo VALUES(1, 2, 3); 51 } 52 set echo_module 53} {xBegin echo(treal) xSync echo(treal) xCommit echo(treal)} 54do_test vtab4-1.3 { 55 set echo_module [list] 56 execsql { 57 UPDATE techo SET a = 2; 58 } 59 set echo_module 60} [list xBestIndex {SELECT rowid, * FROM 'treal'} \ 61 xBegin echo(treal) \ 62 xFilter {SELECT rowid, * FROM 'treal'} \ 63 xSync echo(treal) \ 64 xCommit echo(treal) \ 65] 66do_test vtab4-1.4 { 67 set echo_module [list] 68 execsql { 69 DELETE FROM techo; 70 } 71 set echo_module 72} [list xBestIndex {SELECT rowid, * FROM 'treal'} \ 73 xBegin echo(treal) \ 74 xFilter {SELECT rowid, * FROM 'treal'} \ 75 xSync echo(treal) \ 76 xCommit echo(treal) \ 77] 78 79# Ensure xBegin is not called more than once in a single transaction. 80# 81do_test vtab4-2.1 { 82 set echo_module [list] 83 execsql { 84 BEGIN; 85 INSERT INTO techo VALUES(1, 2, 3); 86 INSERT INTO techo VALUES(4, 5, 6); 87 INSERT INTO techo VALUES(7, 8, 9); 88 COMMIT; 89 } 90 set echo_module 91} {xBegin echo(treal) xSync echo(treal) xCommit echo(treal)} 92 93# Try a transaction with two virtual tables. 94# 95do_test vtab4-2.2 { 96 execsql { 97 CREATE TABLE sreal(a, b, c UNIQUE); 98 CREATE VIRTUAL TABLE secho USING echo(sreal); 99 } 100 set echo_module [list] 101 execsql { 102 BEGIN; 103 INSERT INTO secho SELECT * FROM techo; 104 DELETE FROM techo; 105 COMMIT; 106 } 107 set echo_module 108} [list xBestIndex {SELECT rowid, * FROM 'treal'} \ 109 xBegin echo(sreal) \ 110 xFilter {SELECT rowid, * FROM 'treal'} \ 111 xBestIndex {SELECT rowid, * FROM 'treal'} \ 112 xBegin echo(treal) \ 113 xFilter {SELECT rowid, * FROM 'treal'} \ 114 xSync echo(sreal) \ 115 xSync echo(treal) \ 116 xCommit echo(sreal) \ 117 xCommit echo(treal) \ 118] 119do_test vtab4-2.3 { 120 execsql { 121 SELECT * FROM secho; 122 } 123} {1 2 3 4 5 6 7 8 9} 124do_test vtab4-2.4 { 125 execsql { 126 SELECT * FROM techo; 127 } 128} {} 129 130# Try an explicit ROLLBACK on a transaction with two open virtual tables. 131do_test vtab4-2.5 { 132 set echo_module [list] 133 execsql { 134 BEGIN; 135 INSERT INTO techo SELECT * FROM secho; 136 DELETE FROM secho; 137 ROLLBACK; 138 } 139 set echo_module 140} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \ 141 xBegin echo(treal) \ 142 xFilter {SELECT rowid, * FROM 'sreal'} \ 143 xBestIndex {SELECT rowid, * FROM 'sreal'} \ 144 xBegin echo(sreal) \ 145 xFilter {SELECT rowid, * FROM 'sreal'} \ 146 xRollback echo(treal) \ 147 xRollback echo(sreal) \ 148] 149do_test vtab4-2.6 { 150 execsql { 151 SELECT * FROM secho; 152 } 153} {1 2 3 4 5 6 7 8 9} 154do_test vtab4-2.7 { 155 execsql { 156 SELECT * FROM techo; 157 } 158} {} 159 160do_test vtab4-3.1 { 161 set echo_module [list] 162 set echo_module_sync_fail treal 163 catchsql { 164 INSERT INTO techo VALUES(1, 2, 3); 165 } 166} {1 {unknown error}} 167do_test vtab4-3.2 { 168 set echo_module 169} {xBegin echo(treal) xSync echo(treal) xRollback echo(treal)} 170 171do_test vtab4-3.3 { 172 set echo_module [list] 173 set echo_module_sync_fail sreal 174 catchsql { 175 BEGIN; 176 INSERT INTO techo SELECT * FROM secho; 177 DELETE FROM secho; 178 COMMIT; 179 } 180 set echo_module 181} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \ 182 xBegin echo(treal) \ 183 xFilter {SELECT rowid, * FROM 'sreal'} \ 184 xBestIndex {SELECT rowid, * FROM 'sreal'} \ 185 xBegin echo(sreal) \ 186 xFilter {SELECT rowid, * FROM 'sreal'} \ 187 xSync echo(treal) \ 188 xSync echo(sreal) \ 189 xRollback echo(treal) \ 190 xRollback echo(sreal) \ 191] 192 193finish_test 194