1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate6.test	Fri Jun 15 03:10:57 2012 +0200
     1.3 @@ -0,0 +1,153 @@
     1.4 +#
     1.5 +# 2001 September 15
     1.6 +#
     1.7 +# The author disclaims copyright to this source code.  In place of
     1.8 +# a legal notice, here is a blessing:
     1.9 +#
    1.10 +#    May you do good and not evil.
    1.11 +#    May you find forgiveness for yourself and forgive others.
    1.12 +#    May you share freely, never taking more than you give.
    1.13 +#
    1.14 +#***********************************************************************
    1.15 +# This file implements regression tests for SQLite library.  The
    1.16 +# focus of this script is collation sequences in concert with triggers.
    1.17 +#
    1.18 +# $Id: collate6.test,v 1.4 2007/07/30 14:40:48 danielk1977 Exp $
    1.19 +
    1.20 +set testdir [file dirname $argv0]
    1.21 +source $testdir/tester.tcl
    1.22 +
    1.23 +# There are no tests in this file that will work without
    1.24 +# trigger support.
    1.25 +#
    1.26 +ifcapable {!trigger} {
    1.27 +  finish_test
    1.28 +  return
    1.29 +}
    1.30 +
    1.31 +# Create a case-insensitive collation type NOCASE for use in testing. 
    1.32 +# Normally, capital letters are less than their lower-case counterparts.
    1.33 +db collate NOCASE nocase_collate
    1.34 +proc nocase_collate {a b} {
    1.35 +  return [string compare -nocase $a $b]
    1.36 +}
    1.37 +
    1.38 +#
    1.39 +# Tests are organized as follows:
    1.40 +# collate6-1.* - triggers.
    1.41 +#
    1.42 +
    1.43 +do_test collate6-1.0 {
    1.44 +  execsql {
    1.45 +    CREATE TABLE collate6log(a, b);
    1.46 +    CREATE TABLE collate6tab(a COLLATE NOCASE, b COLLATE BINARY);
    1.47 +  }
    1.48 +} {}
    1.49 +
    1.50 +# Test that the default collation sequence applies to new.* references 
    1.51 +# in WHEN clauses.
    1.52 +do_test collate6-1.1 {
    1.53 +  execsql {
    1.54 +    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab 
    1.55 +      WHEN new.a = 'a' BEGIN
    1.56 +        INSERT INTO collate6log VALUES(new.a, new.b);
    1.57 +    END;
    1.58 +  }
    1.59 +} {}
    1.60 +do_test collate6-1.2 {
    1.61 +  execsql {
    1.62 +    INSERT INTO collate6tab VALUES('a', 'b');
    1.63 +    SELECT * FROM collate6log;
    1.64 +  }
    1.65 +} {a b}
    1.66 +do_test collate6-1.3 {
    1.67 +  execsql {
    1.68 +    INSERT INTO collate6tab VALUES('A', 'B');
    1.69 +    SELECT * FROM collate6log;
    1.70 +  }
    1.71 +} {a b A B}
    1.72 +do_test collate6-1.4 {
    1.73 +  execsql {
    1.74 +    DROP TRIGGER collate6trig;
    1.75 +    DELETE FROM collate6log;
    1.76 +  } 
    1.77 +} {}
    1.78 +
    1.79 +# Test that the default collation sequence applies to new.* references 
    1.80 +# in the body of triggers.
    1.81 +do_test collate6-1.5 {
    1.82 +  execsql {
    1.83 +    CREATE TRIGGER collate6trig BEFORE INSERT ON collate6tab BEGIN
    1.84 +      INSERT INTO collate6log VALUES(new.a='a', new.b='b');
    1.85 +    END;
    1.86 +  }
    1.87 +} {}
    1.88 +do_test collate6-1.6 {
    1.89 +  execsql {
    1.90 +    INSERT INTO collate6tab VALUES('a', 'b');
    1.91 +    SELECT * FROM collate6log;
    1.92 +  }
    1.93 +} {1 1}
    1.94 +do_test collate6-1.7 {
    1.95 +  execsql {
    1.96 +    INSERT INTO collate6tab VALUES('A', 'B');
    1.97 +    SELECT * FROM collate6log;
    1.98 +  }
    1.99 +} {1 1 1 0}
   1.100 +do_test collate6-1.8 {
   1.101 +  execsql {
   1.102 +    DROP TRIGGER collate6trig;
   1.103 +    DELETE FROM collate6log;
   1.104 +  } 
   1.105 +} {}
   1.106 +
   1.107 +do_test collate6-1.9 {
   1.108 +  execsql {
   1.109 +    DROP TABLE collate6tab;
   1.110 +  }
   1.111 +} {}
   1.112 +
   1.113 +# Test that an explicit collation sequence overrides an implicit 
   1.114 +# one attached to a 'new' reference.
   1.115 +#
   1.116 +do_test collate6-2.1 {
   1.117 +  execsql {
   1.118 +    CREATE TABLE abc(a COLLATE binary, b, c);
   1.119 +    CREATE TABLE def(a, b, c);
   1.120 +    CREATE TRIGGER abc_t1 AFTER INSERT ON abc BEGIN
   1.121 +      INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;
   1.122 +    END
   1.123 +  }
   1.124 +} {}
   1.125 +do_test collate6-2.2 {
   1.126 +  execsql {
   1.127 +    INSERT INTO abc VALUES('One', 'Two', 'Three');
   1.128 +    INSERT INTO abc VALUES('one', 'two', 'three');
   1.129 +    SELECT * FROM def;
   1.130 +  }
   1.131 +} {}
   1.132 +do_test collate6-2.3 {
   1.133 +  execsql {
   1.134 +    UPDATE abc SET a = 'four' WHERE a = 'one';
   1.135 +    CREATE TRIGGER abc_t2 AFTER UPDATE ON abc BEGIN
   1.136 +      INSERT INTO def SELECT * FROM abc WHERE a < new.a COLLATE nocase;
   1.137 +    END;
   1.138 +    SELECT * FROM def;
   1.139 +  }
   1.140 +} {}
   1.141 +
   1.142 +# At one point the 6-3.2 (but not 6-3.1) was causing an assert() to fail.
   1.143 +#
   1.144 +do_test collate6-3.1 {
   1.145 +  execsql {
   1.146 +    SELECT 1 FROM sqlite_master WHERE name COLLATE nocase = 'hello';
   1.147 +  }
   1.148 +} {}
   1.149 +do_test collate6-3.2 {
   1.150 +  execsql {
   1.151 +    SELECT 1 FROM sqlite_master WHERE 'hello' = name COLLATE nocase;
   1.152 +  }
   1.153 +} {}
   1.154 +
   1.155 +
   1.156 +finish_test