15821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 25821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2001 September 15 35821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 45821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# The author disclaims copyright to this source code. In place of 55821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# a legal notice, here is a blessing: 65821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 75821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you do good and not evil. 85821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you find forgiveness for yourself and forgive others. 95821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you share freely, never taking more than you give. 105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#*********************************************************************** 125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# This file implements regression tests for SQLite library. The 135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# focus of this script is collation sequences in concert with triggers. 145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# $Id: collate6.test,v 1.4 2007/07/30 14:40:48 danielk1977 Exp $ 165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set testdir [file dirname $argv0] 185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)source $testdir/tester.tcl 195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# There are no tests in this file that will work without 215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# trigger support. 225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)ifcapable {!trigger} { 245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) finish_test 255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return 265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Create a case-insensitive collation type NOCASE for use in testing. 295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Normally, capital letters are less than their lower-case counterparts. 305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)db collate NOCASE nocase_collate 315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)proc nocase_collate {a b} { 325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return [string compare -nocase $a $b] 335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Tests are organized as follows: 375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# collate6-1.* - triggers. 385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.0 { 415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE collate6log(a, b); 435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE collate6tab(a COLLATE NOCASE, b COLLATE BINARY); 445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Test that the default collation sequence applies to new.* references 485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# in WHEN clauses. 495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.1 { 505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab 525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) WHEN new.a = 'a' BEGIN 535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6log VALUES(new.a, new.b); 545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) END; 555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.2 { 585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6tab VALUES('a', 'b'); 605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM collate6log; 615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {a b} 635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.3 { 645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6tab VALUES('A', 'B'); 665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM collate6log; 675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {a b A B} 695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.4 { 705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TRIGGER collate6trig; 725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM collate6log; 735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Test that the default collation sequence applies to new.* references 775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# in the body of triggers. 785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.5 { 795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab BEGIN 815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6log VALUES(new.a='a', new.b='b'); 825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) END; 835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.6 { 865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6tab VALUES('a', 'b'); 885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM collate6log; 895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 1} 915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.7 { 925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO collate6tab VALUES('A', 'B'); 945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM collate6log; 955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 1 1 0} 975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.8 { 985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TRIGGER collate6trig; 1005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM collate6log; 1015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-1.9 { 1055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE collate6tab; 1075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Test that an explicit collation sequence overrides an implicit 1115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# one attached to a 'new' reference. 1125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-2.1 { 1145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE abc(a COLLATE binary, b, c); 1165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE def(a, b, c); 1175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TRIGGER abc_t1 AFTER INSERT ON abc BEGIN 1185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase; 1195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) END 1205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-2.2 { 1235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc VALUES('One', 'Two', 'Three'); 1255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc VALUES('one', 'two', 'three'); 1265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM def; 1275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-2.3 { 1305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE abc SET a = 'four' WHERE a = 'one'; 1325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TRIGGER abc_t2 AFTER UPDATE ON abc BEGIN 1335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase; 1345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) END; 1355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM def; 1365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# At one point the 6-3.2 (but not 6-3.1) was causing an assert() to fail. 1405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-3.1 { 1425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT 1 FROM sqlite_master WHERE name COLLATE nocase = 'hello'; 1445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test collate6-3.2 { 1475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT 1 FROM sqlite_master WHERE 'hello' = name COLLATE nocase; 1495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)finish_test 154