AryaWu/sqlite

1# 2016 February 192#3# The author disclaims copyright to this source code.  In place of4# 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.tcl18set testprefix regexp219 20load_static_extension db regexp21 22#-------------------------------------------------------------------------23# Test that triggers do not become confused and use aux-data created by24# 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 BEGIN32    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 BEGIN40    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-data56# objects are cleaned up correctly.57#58proc sql_error {} {59  error "SQL error!"60}61db func error sql_error62do_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 BEGIN68    UPDATE t3 SET d = new.b WHERE c = old.a;69  END;70 71  CREATE TRIGGER t3_tr1 AFTER UPDATE ON t3 BEGIN72    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 BEGIN76    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 BEGIN113    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/9104f0d9e7124#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 144do_execsql_test 5.0 {145  SELECT 'abc' REGEXP 'a{1,999}bc';146} 1147do_catchsql_test 5.1 {148  SELECT 'abc' REGEXP 'a{1,25000}bc';149} {1 {REGEXP pattern too big}}150do_execsql_test 5.2 {151  SELECT 'abc' REGEXP 'a{999}bc';152} 0153do_catchsql_test 5.3 {154  SELECT 'abc' REGEXP 'a{25000}bc';155} {1 {REGEXP pattern too big}}156 157 158 159finish_test160