Annotation of embedaddon/sqlite3/test/collate9.test, revision 1.1
1.1 ! misho 1: #
! 2: # 2007 November 12
! 3: #
! 4: # The author disclaims copyright to this source code. In place of
! 5: # 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. The
! 13: # focus of this script is making sure that the names of collation
! 14: # sequences may be quoted using double quotes in SQL statements.
! 15: #
! 16: # $Id: collate9.test,v 1.2 2008/07/10 00:32:42 drh Exp $
! 17:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20:
! 21: proc reverse_sort {lhs rhs} {
! 22: return [string compare $rhs $lhs]
! 23: }
! 24: db collate "reverse sort" reverse_sort
! 25:
! 26: # This procedure executes the SQL. Then it checks to see if the OP_Sort
! 27: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
! 28: # to the result. If no OP_Sort happened, then "nosort" is appended.
! 29: #
! 30: # This procedure is used to check to make sure sorting is or is not
! 31: # occurring as expected.
! 32: #
! 33: proc cksort {sql} {
! 34: set ::sqlite_sort_count 0
! 35: set data [execsql $sql]
! 36: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 37: lappend data $x
! 38: return $data
! 39: }
! 40:
! 41: # Test plan:
! 42: #
! 43: # collate9-1.* - Test collation sequences attached to table columns
! 44: # collate9-2.* - Test collation sequences attached to expressions
! 45: # collate9-3.* - Test collation sequences attached to an index
! 46: # collate9-4.* - Test collation sequences as an argument to REINDEX
! 47: #
! 48:
! 49: do_test collate9-1.1 {
! 50: execsql {
! 51: CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);
! 52: INSERT INTO xy VALUES('one', 'one');
! 53: INSERT INTO xy VALUES('two', 'two');
! 54: INSERT INTO xy VALUES('three', 'three');
! 55: }
! 56: } {}
! 57: do_test collate9-1.2 {
! 58: execsql {
! 59: SELECT x FROM xy ORDER BY x
! 60: }
! 61: } {two three one}
! 62: do_test collate9-1.3 {
! 63: execsql {
! 64: SELECT y FROM xy ORDER BY y
! 65: }
! 66: } {one three two}
! 67: do_test collate9-1.4 {
! 68: cksort {
! 69: SELECT x FROM xy ORDER BY x
! 70: }
! 71: } {two three one sort}
! 72: do_test collate9-1.5 {
! 73: execsql {
! 74: CREATE INDEX xy_i ON xy(x)
! 75: }
! 76: } {}
! 77: do_test collate9-1.6 {
! 78: cksort {
! 79: SELECT x FROM xy ORDER BY x
! 80: }
! 81: } {two three one nosort}
! 82:
! 83: do_test collate9-2.1 {
! 84: execsql {
! 85: SELECT x, x < 'seven' FROM xy ORDER BY x
! 86: }
! 87: } {two 1 three 1 one 0}
! 88: do_test collate9-2.2 {
! 89: execsql {
! 90: SELECT y, y < 'seven' FROM xy ORDER BY x
! 91: }
! 92: } {two 0 three 0 one 1}
! 93: do_test collate9-2.3 {
! 94: execsql {
! 95: SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x
! 96: }
! 97: } {two 1 three 1 one 0}
! 98: do_test collate9-2.4 {
! 99: execsql {
! 100: SELECT y FROM xy ORDER BY y
! 101: }
! 102: } {one three two}
! 103: do_test collate9-2.5 {
! 104: execsql {
! 105: SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
! 106: }
! 107: } {two three one}
! 108: do_test collate9-2.6 {
! 109: execsql {
! 110: SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
! 111: }
! 112: } {two three one}
! 113:
! 114: do_test collate9-3.1 {
! 115: execsql {
! 116: CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
! 117: }
! 118: } {}
! 119: do_test collate9-3.2 {
! 120: cksort {
! 121: SELECT y FROM xy ORDER BY y
! 122: }
! 123: } {one three two sort}
! 124: do_test collate9-3.3 {
! 125: cksort {
! 126: SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
! 127: }
! 128: } {two three one nosort}
! 129: do_test collate9-3.4 {
! 130: cksort {
! 131: SELECT y AS aaa FROM xy ORDER BY aaa
! 132: }
! 133: } {one three two sort}
! 134: do_test collate9-3.5 {
! 135: cksort {
! 136: SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
! 137: }
! 138: } {two three one nosort}
! 139:
! 140: ifcapable reindex {
! 141: do_test collate9-4.1 {
! 142: execsql {
! 143: REINDEX "reverse sort"
! 144: }
! 145: } {}
! 146:
! 147: # Modify the "reverse sort" collation so that it now sorts in the same
! 148: # order as binary.
! 149: proc reverse_sort {lhs rhs} {
! 150: return [string compare $lhs $rhs]
! 151: }
! 152:
! 153: # The integrity check should now fail because the indexes created using
! 154: # "reverse sort" are no longer in sync with the collation sequence
! 155: # implementation.
! 156: do_test collate9-4.2 {
! 157: expr {"ok" eq [execsql { PRAGMA integrity_check }]}
! 158: } {0}
! 159:
! 160: do_test collate9-4.3 {
! 161: execsql {
! 162: REINDEX "reverse sort"
! 163: }
! 164: } {}
! 165:
! 166: # Integrity check should now pass.
! 167: do_test collate9-4.4 {
! 168: expr {"ok" eq [execsql { PRAGMA integrity_check }]}
! 169: } {1}
! 170:
! 171: do_test collate9-4.5 {
! 172: cksort {
! 173: SELECT x FROM xy ORDER BY x COLLATE "reverse sort"
! 174: }
! 175: } {one three two nosort}
! 176: }
! 177:
! 178: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate9.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test