AryaWu/sqlite

1#2# 2001 September 153#4# The author disclaims copyright to this source code.  In place of5# a legal notice, here is a blessing:6#7#    May you do good and not evil.8#    May you find forgiveness for yourself and forgive others.9#    May you share freely, never taking more than you give.10#11#***********************************************************************12# This file implements regression tests for SQLite library.  The13# focus of this script is collation sequences in concert with triggers.14#15# $Id: collate6.test,v 1.4 2007/07/30 14:40:48 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# There are no tests in this file that will work without21# trigger support.22#23ifcapable {!trigger} {24  finish_test25  return26}27 28# Create a case-insensitive collation type NOCASE for use in testing. 29# Normally, capital letters are less than their lower-case counterparts.30db collate NOCASE nocase_collate31proc nocase_collate {a b} {32  return [string compare -nocase $a $b]33}34 35#36# Tests are organized as follows:37# collate6-1.* - triggers.38#39 40do_test collate6-1.0 {41  execsql {42    CREATE TABLE collate6log(a, b);43    CREATE TABLE collate6tab(a COLLATE NOCASE, b COLLATE BINARY);44  }45} {}46 47# Test that the default collation sequence applies to new.* references 48# in WHEN clauses.49do_test collate6-1.1 {50  execsql {51    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab 52      WHEN new.a = 'a' BEGIN53        INSERT INTO collate6log VALUES(new.a, new.b);54    END;55  }56} {}57do_test collate6-1.2 {58  execsql {59    INSERT INTO collate6tab VALUES('a', 'b');60    SELECT * FROM collate6log;61  }62} {a b}63do_test collate6-1.3 {64  execsql {65    INSERT INTO collate6tab VALUES('A', 'B');66    SELECT * FROM collate6log;67  }68} {a b A B}69do_test collate6-1.4 {70  execsql {71    DROP TRIGGER collate6trig;72    DELETE FROM collate6log;73  } 74} {}75 76# Test that the default collation sequence applies to new.* references 77# in the body of triggers.78do_test collate6-1.5 {79  execsql {80    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab BEGIN81      INSERT INTO collate6log VALUES(new.a='a', new.b='b');82    END;83  }84} {}85do_test collate6-1.6 {86  execsql {87    INSERT INTO collate6tab VALUES('a', 'b');88    SELECT * FROM collate6log;89  }90} {1 1}91do_test collate6-1.7 {92  execsql {93    INSERT INTO collate6tab VALUES('A', 'B');94    SELECT * FROM collate6log;95  }96} {1 1 1 0}97do_test collate6-1.8 {98  execsql {99    DROP TRIGGER collate6trig;100    DELETE FROM collate6log;101  } 102} {}103 104do_test collate6-1.9 {105  execsql {106    DROP TABLE collate6tab;107  }108} {}109 110# Test that an explicit collation sequence overrides an implicit 111# one attached to a 'new' reference.112#113do_test collate6-2.1 {114  execsql {115    CREATE TABLE abc(a COLLATE binary, b, c);116    CREATE TABLE def(a, b, c);117    CREATE TRIGGER abc_t1 AFTER INSERT ON abc BEGIN118      INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;119    END120  }121} {}122do_test collate6-2.2 {123  execsql {124    INSERT INTO abc VALUES('One', 'Two', 'Three');125    INSERT INTO abc VALUES('one', 'two', 'three');126    SELECT * FROM def;127  }128} {}129do_test collate6-2.3 {130  execsql {131    UPDATE abc SET a = 'four' WHERE a = 'one';132    CREATE TRIGGER abc_t2 AFTER UPDATE ON abc BEGIN133      INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;134    END;135    SELECT * FROM def;136  }137} {}138 139# At one point the 6-3.2 (but not 6-3.1) was causing an assert() to fail.140#141do_test collate6-3.1 {142  execsql {143    SELECT 1 FROM sqlite_master WHERE name COLLATE nocase = 'hello';144  }145} {}146do_test collate6-3.2 {147  execsql {148    SELECT 1 FROM sqlite_master WHERE 'hello' = name COLLATE nocase;149  }150} {}151 152 153finish_test154