1# 2016 February 19 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 implements tests for the REGEXP operator in ext/misc/regexp.c. 13# It focuses on the use of the sqlite3_set_auxdata()/get_auxdata() APIs. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix regexp2 19 20load_static_extension db regexp 21 22#------------------------------------------------------------------------- 23# Test that triggers do not become confused and use aux-data created by 24# a different trigger for a different REGEXP invocation. 25# 26do_execsql_test 1.0 { 27 CREATE TABLE t1(a, b, c); 28 CREATE TABLE x1(x, y, z); 29 CREATE TABLE x2(x, y, z); 30 31 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 32 INSERT INTO x1 VALUES( 33 new.a REGEXP 'abc', 34 new.b REGEXP 'abc', 35 new.c REGEXP 'abc' 36 ); 37 END; 38 39 CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN 40 INSERT INTO x2 VALUES( 41 new.a REGEXP 'def', 42 new.b REGEXP 'def', 43 new.c REGEXP 'def' 44 ); 45 END; 46 47 INSERT INTO t1 VALUES('abc', 'def', 'abc'); 48 SELECT * FROM t1; 49} {abc def abc} 50 51do_execsql_test 1.1 { SELECT * FROM x1 } {1 0 1} 52do_execsql_test 1.2 { SELECT * FROM x2 } {0 1 0} 53 54#------------------------------------------------------------------------- 55# Test that if an exception is thrown several triggers deep, all aux-data 56# objects are cleaned up correctly. 57# 58proc sql_error {} { 59 error "SQL error!" 60} 61db func error sql_error 62do_execsql_test 2.0 { 63 CREATE TABLE t2(a, b); 64 CREATE TABLE t3(c, d); 65 CREATE TABLE t4(e, f); 66 67 CREATE TRIGGER t2_tr1 AFTER UPDATE ON t2 BEGIN 68 UPDATE t3 SET d = new.b WHERE c = old.a; 69 END; 70 71 CREATE TRIGGER t3_tr1 AFTER UPDATE ON t3 BEGIN 72 UPDATE t4 SET f = new.d WHERE e = old.c AND new.d REGEXP 'a.*'; 73 END; 74 75 CREATE TRIGGER t4_tr1 AFTER UPDATE ON t4 BEGIN 76 SELECT CASE WHEN new.f REGEXP '.*y.*' THEN error() ELSE 1 END; 77 END; 78 79 INSERT INTO t2 VALUES(1, 'a_x_1'); 80 INSERT INTO t2 VALUES(2, 'a_y_1'); 81 82 INSERT INTO t3 VALUES(1, 'b1'); 83 INSERT INTO t3 VALUES(2, 'b2'); 84 85 INSERT INTO t4 VALUES(1, 'b1'); 86 INSERT INTO t4 VALUES(2, 'b2'); 87} {} 88 89do_catchsql_test 2.1 { 90 UPDATE t2 SET a=a+1 WHERE b REGEXP 'a.*' AND b REGEXP '.*1'; 91} {1 {SQL error!}} 92 93# Test that the triggers used in the test above work as expected. 94# 95do_execsql_test 2.2 { 96 UPDATE t2 SET b = 'a_abc_1'; 97} {} 98do_execsql_test 2.3 { 99 SELECT * FROM t2; 100 SELECT * FROM t3; 101 SELECT * FROM t4; 102} {1 a_abc_1 2 a_abc_1 1 a_abc_1 2 a_abc_1 1 a_abc_1 2 a_abc_1} 103 104#------------------------------------------------------------------------- 105# Test that trigger parameters (i.e. new.* and old.*) refs are not 106# considered to be constant across separate invocations of the trigger. 107# 108do_execsql_test 3.0 { 109 CREATE TABLE t5(a); 110 CREATE TABLE t6(x); 111 112 CREATE TRIGGER t5tr AFTER DELETE ON t5 BEGIN 113 DELETE FROM t6 WHERE t6.x REGEXP old.a; 114 END; 115 116 INSERT INTO t5 VALUES ('^a.*'), ('^b.*'), ('^c.*'); 117 INSERT INTO t6 VALUES ('eab'), ('abc'), ('bcd'), ('cde'), ('dea'); 118 119 DELETE FROM t5; 120 SELECT * FROM t6; 121} {eab dea} 122 123# 2021-06-04 Forum https://sqlite.org/forum/forumpost/9104f0d9e7 124# 125do_execsql_test 4.1 {SELECT 'abc' REGEXP '\W'} {0} 126do_execsql_test 4.2 {SELECT 'a c' REGEXP '\W'} {1} 127do_execsql_test 4.3 {SELECT ' ' REGEXP '\W'} {1} 128do_execsql_test 4.4 {SELECT 'abc' REGEXP '\w'} {1} 129do_execsql_test 4.5 {SELECT 'a c' REGEXP '\w'} {1} 130do_execsql_test 4.6 {SELECT ' ' REGEXP '\w'} {0} 131do_execsql_test 4.7 {SELECT 'abc' REGEXP '\D'} {1} 132do_execsql_test 4.8 {SELECT 'abc' REGEXP '[^a-z]'} {0} 133do_execsql_test 4.9 {SELECT 'a c' REGEXP '[^a-z]'} {1} 134do_execsql_test 4.10 {SELECT ' ' REGEXP '[^a-z]'} {1} 135do_execsql_test 4.11 {SELECT 'abc' REGEXP '[a-z]'} {1} 136do_execsql_test 4.12 {SELECT 'a c' REGEXP '[a-z]'} {1} 137do_execsql_test 4.13 {SELECT ' ' REGEXP '[a-z]'} {0} 138do_execsql_test 4.14 {SELECT 'abc' REGEXP '[^a-z]{2}'} {0} 139do_execsql_test 4.15 {SELECT 'a c' REGEXP '[^a-z]{2}'} {0} 140do_execsql_test 4.16 {SELECT ' ' REGEXP '[^a-z]{2}'} {1} 141do_execsql_test 4.17 {SELECT 'abc' REGEXP '\W{1,1}'} {0} 142do_execsql_test 4.18 {SELECT 'abc' REGEXP '\W{1}'} {0} 143 144finish_test 145