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
140
141
142
143
144
|
# 2016 February 19
#
# 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.
#
#***********************************************************************
#
# This file implements tests for the REGEXP operator in ext/misc/regexp.c.
# It focuses on the use of the sqlite3_set_auxdata()/get_auxdata() APIs.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix regexp2
load_static_extension db regexp
#-------------------------------------------------------------------------
# Test that triggers do not become confused and use aux-data created by
# a different trigger for a different REGEXP invocation.
#
do_execsql_test 1.0 {
CREATE TABLE t1(a, b, c);
CREATE TABLE x1(x, y, z);
CREATE TABLE x2(x, y, z);
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
INSERT INTO x1 VALUES(
new.a REGEXP 'abc',
new.b REGEXP 'abc',
new.c REGEXP 'abc'
);
END;
CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN
INSERT INTO x2 VALUES(
new.a REGEXP 'def',
new.b REGEXP 'def',
new.c REGEXP 'def'
);
END;
INSERT INTO t1 VALUES('abc', 'def', 'abc');
SELECT * FROM t1;
} {abc def abc}
do_execsql_test 1.1 { SELECT * FROM x1 } {1 0 1}
do_execsql_test 1.2 { SELECT * FROM x2 } {0 1 0}
#-------------------------------------------------------------------------
# Test that if an exception is thrown several triggers deep, all aux-data
# objects are cleaned up correctly.
#
proc sql_error {} {
error "SQL error!"
}
db func error sql_error
do_execsql_test 2.0 {
CREATE TABLE t2(a, b);
CREATE TABLE t3(c, d);
CREATE TABLE t4(e, f);
CREATE TRIGGER t2_tr1 AFTER UPDATE ON t2 BEGIN
UPDATE t3 SET d = new.b WHERE c = old.a;
END;
CREATE TRIGGER t3_tr1 AFTER UPDATE ON t3 BEGIN
UPDATE t4 SET f = new.d WHERE e = old.c AND new.d REGEXP 'a.*';
END;
CREATE TRIGGER t4_tr1 AFTER UPDATE ON t4 BEGIN
SELECT CASE WHEN new.f REGEXP '.*y.*' THEN error() ELSE 1 END;
END;
INSERT INTO t2 VALUES(1, 'a_x_1');
INSERT INTO t2 VALUES(2, 'a_y_1');
INSERT INTO t3 VALUES(1, 'b1');
INSERT INTO t3 VALUES(2, 'b2');
INSERT INTO t4 VALUES(1, 'b1');
INSERT INTO t4 VALUES(2, 'b2');
} {}
do_catchsql_test 2.1 {
UPDATE t2 SET a=a+1 WHERE b REGEXP 'a.*' AND b REGEXP '.*1';
} {1 {SQL error!}}
# Test that the triggers used in the test above work as expected.
#
do_execsql_test 2.2 {
UPDATE t2 SET b = 'a_abc_1';
} {}
do_execsql_test 2.3 {
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
} {1 a_abc_1 2 a_abc_1 1 a_abc_1 2 a_abc_1 1 a_abc_1 2 a_abc_1}
#-------------------------------------------------------------------------
# Test that trigger parameters (i.e. new.* and old.*) refs are not
# considered to be constant across separate invocations of the trigger.
#
do_execsql_test 3.0 {
CREATE TABLE t5(a);
CREATE TABLE t6(x);
CREATE TRIGGER t5tr AFTER DELETE ON t5 BEGIN
DELETE FROM t6 WHERE t6.x REGEXP old.a;
END;
INSERT INTO t5 VALUES ('^a.*'), ('^b.*'), ('^c.*');
INSERT INTO t6 VALUES ('eab'), ('abc'), ('bcd'), ('cde'), ('dea');
DELETE FROM t5;
SELECT * FROM t6;
} {eab dea}
# 2021-06-04 Forum https://sqlite.org/forum/forumpost/9104f0d9e7
#
do_execsql_test 4.1 {SELECT 'abc' REGEXP '\W'} {0}
do_execsql_test 4.2 {SELECT 'a c' REGEXP '\W'} {1}
do_execsql_test 4.3 {SELECT ' ' REGEXP '\W'} {1}
do_execsql_test 4.4 {SELECT 'abc' REGEXP '\w'} {1}
do_execsql_test 4.5 {SELECT 'a c' REGEXP '\w'} {1}
do_execsql_test 4.6 {SELECT ' ' REGEXP '\w'} {0}
do_execsql_test 4.7 {SELECT 'abc' REGEXP '\D'} {1}
do_execsql_test 4.8 {SELECT 'abc' REGEXP '[^a-z]'} {0}
do_execsql_test 4.9 {SELECT 'a c' REGEXP '[^a-z]'} {1}
do_execsql_test 4.10 {SELECT ' ' REGEXP '[^a-z]'} {1}
do_execsql_test 4.11 {SELECT 'abc' REGEXP '[a-z]'} {1}
do_execsql_test 4.12 {SELECT 'a c' REGEXP '[a-z]'} {1}
do_execsql_test 4.13 {SELECT ' ' REGEXP '[a-z]'} {0}
do_execsql_test 4.14 {SELECT 'abc' REGEXP '[^a-z]{2}'} {0}
do_execsql_test 4.15 {SELECT 'a c' REGEXP '[^a-z]{2}'} {0}
do_execsql_test 4.16 {SELECT ' ' REGEXP '[^a-z]{2}'} {1}
do_execsql_test 4.17 {SELECT 'abc' REGEXP '\W{1,1}'} {0}
do_execsql_test 4.18 {SELECT 'abc' REGEXP '\W{1}'} {0}
finish_test
|
