1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
# 2020 April 29
#
# 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.
#
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix upfromfault
foreach {tn sql} {
1 {
CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE);
CREATE INDEX t1y ON t1(y);
}
2 {
CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) WITHOUT ROWID;
CREATE INDEX t1y ON t1(y);
}
3 {
CREATE TABLE t1(x, y, z UNIQUE, PRIMARY KEY(x,y)) WITHOUT ROWID;
}
4 {
CREATE VIRTUAL TABLE t1 USING fts5(x, y, z);
}
5 {
CREATE TABLE real(x, y, z);
CREATE VIEW t1 AS SELECT * FROM real;
CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN
INSERT INTO real VALUES(new.x, new.y, new.z);
END;
CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN
INSERT INTO log VALUES(old.z || '->' || new.z);
UPDATE real SET y=new.y, z=new.z WHERE x=old.x;
END;
}
} {
if {$tn<5} continue
reset_db
ifcapable !fts5 { if {$tn==4} continue }
execsql $sql
do_execsql_test 1.$tn.0 {
CREATE TABLE log(t TEXT);
INSERT INTO t1 VALUES(1, 'i', 'one');
INSERT INTO t1 VALUES(2, 'ii', 'two');
INSERT INTO t1 VALUES(3, 'iii', 'three');
INSERT INTO t1 VALUES(4, 'iv', 'four');
}
if {$tn!=4 && $tn!=5} {
do_execsql_test 1.$tn.0b {
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN
INSERT INTO log VALUES(old.z || '->' || new.z);
END;
CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
INSERT INTO log VALUES(old.y || '->' || new.y);
END;
}
}
faultsim_save_and_close
do_faultsim_test 1.$tn -prep {
faultsim_restore_and_reopen
execsql { SELECT * FROM t1 }
} -body {
execsql {
WITH data(k, v) AS (
VALUES(3, 'thirty'), (1, 'ten')
)
UPDATE t1 SET z=v FROM data WHERE x=k;
}
} -test {
faultsim_test_result {0 {}} {1 {vtable constructor failed: t1}}
if {$testrc==0} {
set res [execsql { SELECT * FROM t1 }]
if {$res!="1 i ten 2 ii two 3 iii thirty 4 iv four"} {
error "unexpected result: $res"
}
}
}
}
reset_db
do_execsql_test 2.0 {
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(x, y, z);
}
faultsim_save_and_close
do_faultsim_test 2.1 -prep {
faultsim_restore_and_reopen
} -body {
execsql {
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
UPDATE t2 SET x=a FROM t1 WHERE c=z;
END;
}
} -test {
faultsim_test_result {0 {}}
}
faultsim_restore_and_reopen
do_execsql_test 2.2 {
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
UPDATE t1 SET a=x FROM t2 WHERE c=z;
END;
INSERT INTO t2 VALUES(1, 1, 1);
INSERT INTO t2 VALUES(2, 2, 2);
INSERT INTO t2 VALUES(3, 3, 3);
}
faultsim_save_and_close
do_faultsim_test 2.3 -prep {
faultsim_restore_and_reopen
} -body {
execsql {
INSERT INTO t1 VALUES(NULL, NULL, 1), (NULL, NULL, 3);
}
} -test {
faultsim_test_result {0 {}}
if {$testrc==0} {
set res [execsql { SELECT * FROM t1 }]
if {$res!="1 {} 1 3 {} 3"} {
error "unexpected result: $res"
}
}
}
finish_test
|
