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