1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate9.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,178 @@
1.4 +#
1.5 +# 2007 November 12
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 making sure that the names of collation
1.17 +# sequences may be quoted using double quotes in SQL statements.
1.18 +#
1.19 +# $Id: collate9.test,v 1.2 2008/07/10 00:32:42 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +proc reverse_sort {lhs rhs} {
1.25 + return [string compare $rhs $lhs]
1.26 +}
1.27 +db collate "reverse sort" reverse_sort
1.28 +
1.29 +# This procedure executes the SQL. Then it checks to see if the OP_Sort
1.30 +# opcode was executed. If an OP_Sort did occur, then "sort" is appended
1.31 +# to the result. If no OP_Sort happened, then "nosort" is appended.
1.32 +#
1.33 +# This procedure is used to check to make sure sorting is or is not
1.34 +# occurring as expected.
1.35 +#
1.36 +proc cksort {sql} {
1.37 + set ::sqlite_sort_count 0
1.38 + set data [execsql $sql]
1.39 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.40 + lappend data $x
1.41 + return $data
1.42 +}
1.43 +
1.44 +# Test plan:
1.45 +#
1.46 +# collate9-1.* - Test collation sequences attached to table columns
1.47 +# collate9-2.* - Test collation sequences attached to expressions
1.48 +# collate9-3.* - Test collation sequences attached to an index
1.49 +# collate9-4.* - Test collation sequences as an argument to REINDEX
1.50 +#
1.51 +
1.52 +do_test collate9-1.1 {
1.53 + execsql {
1.54 + CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);
1.55 + INSERT INTO xy VALUES('one', 'one');
1.56 + INSERT INTO xy VALUES('two', 'two');
1.57 + INSERT INTO xy VALUES('three', 'three');
1.58 + }
1.59 +} {}
1.60 +do_test collate9-1.2 {
1.61 + execsql {
1.62 + SELECT x FROM xy ORDER BY x
1.63 + }
1.64 +} {two three one}
1.65 +do_test collate9-1.3 {
1.66 + execsql {
1.67 + SELECT y FROM xy ORDER BY y
1.68 + }
1.69 +} {one three two}
1.70 +do_test collate9-1.4 {
1.71 + cksort {
1.72 + SELECT x FROM xy ORDER BY x
1.73 + }
1.74 +} {two three one sort}
1.75 +do_test collate9-1.5 {
1.76 + execsql {
1.77 + CREATE INDEX xy_i ON xy(x)
1.78 + }
1.79 +} {}
1.80 +do_test collate9-1.6 {
1.81 + cksort {
1.82 + SELECT x FROM xy ORDER BY x
1.83 + }
1.84 +} {two three one nosort}
1.85 +
1.86 +do_test collate9-2.1 {
1.87 + execsql {
1.88 + SELECT x, x < 'seven' FROM xy ORDER BY x
1.89 + }
1.90 +} {two 1 three 1 one 0}
1.91 +do_test collate9-2.2 {
1.92 + execsql {
1.93 + SELECT y, y < 'seven' FROM xy ORDER BY x
1.94 + }
1.95 +} {two 0 three 0 one 1}
1.96 +do_test collate9-2.3 {
1.97 + execsql {
1.98 + SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x
1.99 + }
1.100 +} {two 1 three 1 one 0}
1.101 +do_test collate9-2.4 {
1.102 + execsql {
1.103 + SELECT y FROM xy ORDER BY y
1.104 + }
1.105 +} {one three two}
1.106 +do_test collate9-2.5 {
1.107 + execsql {
1.108 + SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
1.109 + }
1.110 +} {two three one}
1.111 +do_test collate9-2.6 {
1.112 + execsql {
1.113 + SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
1.114 + }
1.115 +} {two three one}
1.116 +
1.117 +do_test collate9-3.1 {
1.118 + execsql {
1.119 + CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
1.120 + }
1.121 +} {}
1.122 +do_test collate9-3.2 {
1.123 + cksort {
1.124 + SELECT y FROM xy ORDER BY y
1.125 + }
1.126 +} {one three two sort}
1.127 +do_test collate9-3.3 {
1.128 + cksort {
1.129 + SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
1.130 + }
1.131 +} {two three one nosort}
1.132 +do_test collate9-3.4 {
1.133 + cksort {
1.134 + SELECT y AS aaa FROM xy ORDER BY aaa
1.135 + }
1.136 +} {one three two sort}
1.137 +do_test collate9-3.5 {
1.138 + cksort {
1.139 + SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
1.140 + }
1.141 +} {two three one nosort}
1.142 +
1.143 +ifcapable reindex {
1.144 + do_test collate9-4.1 {
1.145 + execsql {
1.146 + REINDEX "reverse sort"
1.147 + }
1.148 + } {}
1.149 +
1.150 + # Modify the "reverse sort" collation so that it now sorts in the same
1.151 + # order as binary.
1.152 + proc reverse_sort {lhs rhs} {
1.153 + return [string compare $lhs $rhs]
1.154 + }
1.155 +
1.156 + # The integrity check should now fail because the indexes created using
1.157 + # "reverse sort" are no longer in sync with the collation sequence
1.158 + # implementation.
1.159 + do_test collate9-4.2 {
1.160 + expr {"ok" eq [execsql { PRAGMA integrity_check }]}
1.161 + } {0}
1.162 +
1.163 + do_test collate9-4.3 {
1.164 + execsql {
1.165 + REINDEX "reverse sort"
1.166 + }
1.167 + } {}
1.168 +
1.169 + # Integrity check should now pass.
1.170 + do_test collate9-4.4 {
1.171 + expr {"ok" eq [execsql { PRAGMA integrity_check }]}
1.172 + } {1}
1.173 +
1.174 + do_test collate9-4.5 {
1.175 + cksort {
1.176 + SELECT x FROM xy ORDER BY x COLLATE "reverse sort"
1.177 + }
1.178 + } {one three two nosort}
1.179 +}
1.180 +
1.181 +finish_test