Annotation of embedaddon/sqlite3/test/collate1.test, revision 1.1
1.1 ! misho 1: #
! 2: # 2001 September 15
! 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 page cache subsystem.
! 14: #
! 15: # $Id: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: #
! 21: # Tests are roughly organised as follows:
! 22: #
! 23: # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.
! 24: # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.
! 25: # collate1-3.* - ORDER BY using a default collation type. Also that an
! 26: # explict collate type overrides a default collate type.
! 27: # collate1-4.* - ORDER BY using a data type.
! 28: #
! 29:
! 30: #
! 31: # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal
! 32: # number, then it is converted to one before the comparison is performed.
! 33: # Numbers are less than other strings. If neither argument is a number,
! 34: # [string compare] is used.
! 35: #
! 36: db collate HEX hex_collate
! 37: proc hex_collate {lhs rhs} {
! 38: set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]
! 39: set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]
! 40: if {$lhs_ishex && $rhs_ishex} {
! 41: set lhsx [scan $lhs %x]
! 42: set rhsx [scan $rhs %x]
! 43: if {$lhs < $rhs} {return -1}
! 44: if {$lhs == $rhs} {return 0}
! 45: if {$lhs > $rhs} {return 1}
! 46: }
! 47: if {$lhs_ishex} {
! 48: return -1;
! 49: }
! 50: if {$rhs_ishex} {
! 51: return 1;
! 52: }
! 53: return [string compare $lhs $rhs]
! 54: }
! 55: db function hex {format 0x%X}
! 56:
! 57: # Mimic the SQLite 2 collation type NUMERIC.
! 58: db collate numeric numeric_collate
! 59: proc numeric_collate {lhs rhs} {
! 60: if {$lhs == $rhs} {return 0}
! 61: return [expr ($lhs>$rhs)?1:-1]
! 62: }
! 63:
! 64: do_test collate1-1.0 {
! 65: execsql {
! 66: CREATE TABLE collate1t1(c1, c2);
! 67: INSERT INTO collate1t1 VALUES(45, hex(45));
! 68: INSERT INTO collate1t1 VALUES(NULL, NULL);
! 69: INSERT INTO collate1t1 VALUES(281, hex(281));
! 70: }
! 71: } {}
! 72: do_test collate1-1.1 {
! 73: execsql {
! 74: SELECT c2 FROM collate1t1 ORDER BY 1;
! 75: }
! 76: } {{} 0x119 0x2D}
! 77: do_test collate1-1.2 {
! 78: execsql {
! 79: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;
! 80: }
! 81: } {{} 0x2D 0x119}
! 82: do_test collate1-1.3 {
! 83: execsql {
! 84: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;
! 85: }
! 86: } {0x119 0x2D {}}
! 87: do_test collate1-1.4 {
! 88: execsql {
! 89: SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;
! 90: }
! 91: } {{} 0x2D 0x119}
! 92: do_test collate1-1.5 {
! 93: execsql {
! 94: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1
! 95: }
! 96: } {{} 0x2D 0x119}
! 97: do_test collate1-1.6 {
! 98: execsql {
! 99: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC
! 100: }
! 101: } {{} 0x2D 0x119}
! 102: do_test collate1-1.7 {
! 103: execsql {
! 104: SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC
! 105: }
! 106: } {0x119 0x2D {}}
! 107: do_test collate1-1.99 {
! 108: execsql {
! 109: DROP TABLE collate1t1;
! 110: }
! 111: } {}
! 112:
! 113: do_test collate1-2.0 {
! 114: execsql {
! 115: CREATE TABLE collate1t1(c1, c2);
! 116: INSERT INTO collate1t1 VALUES('5', '0x11');
! 117: INSERT INTO collate1t1 VALUES('5', '0xA');
! 118: INSERT INTO collate1t1 VALUES(NULL, NULL);
! 119: INSERT INTO collate1t1 VALUES('7', '0xA');
! 120: INSERT INTO collate1t1 VALUES('11', '0x11');
! 121: INSERT INTO collate1t1 VALUES('11', '0x101');
! 122: }
! 123: } {}
! 124: do_test collate1-2.2 {
! 125: execsql {
! 126: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
! 127: }
! 128: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
! 129: do_test collate1-2.3 {
! 130: execsql {
! 131: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;
! 132: }
! 133: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
! 134: do_test collate1-2.4 {
! 135: execsql {
! 136: SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
! 137: }
! 138: } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
! 139: do_test collate1-2.5 {
! 140: execsql {
! 141: SELECT c1, c2 FROM collate1t1
! 142: ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;
! 143: }
! 144: } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
! 145: do_test collate1-2.6 {
! 146: execsql {
! 147: SELECT c1, c2 FROM collate1t1
! 148: ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
! 149: }
! 150: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
! 151: do_test collate1-2.12.1 {
! 152: execsql {
! 153: SELECT c1 COLLATE numeric, c2 FROM collate1t1
! 154: ORDER BY 1, 2 COLLATE hex;
! 155: }
! 156: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
! 157: do_test collate1-2.12.2 {
! 158: execsql {
! 159: SELECT c1 COLLATE hex, c2 FROM collate1t1
! 160: ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
! 161: }
! 162: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
! 163: do_test collate1-2.12.3 {
! 164: execsql {
! 165: SELECT c1, c2 COLLATE hex FROM collate1t1
! 166: ORDER BY 1 COLLATE numeric, 2;
! 167: }
! 168: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
! 169: do_test collate1-2.12.4 {
! 170: execsql {
! 171: SELECT c1 COLLATE numeric, c2 COLLATE hex
! 172: FROM collate1t1
! 173: ORDER BY 1, 2;
! 174: }
! 175: } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
! 176: do_test collate1-2.13 {
! 177: execsql {
! 178: SELECT c1 COLLATE binary, c2 COLLATE hex
! 179: FROM collate1t1
! 180: ORDER BY 1, 2;
! 181: }
! 182: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
! 183: do_test collate1-2.14 {
! 184: execsql {
! 185: SELECT c1, c2
! 186: FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
! 187: }
! 188: } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
! 189: do_test collate1-2.15 {
! 190: execsql {
! 191: SELECT c1 COLLATE binary, c2 COLLATE hex
! 192: FROM collate1t1
! 193: ORDER BY 1 DESC, 2 DESC;
! 194: }
! 195: } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
! 196: do_test collate1-2.16 {
! 197: execsql {
! 198: SELECT c1 COLLATE hex, c2 COLLATE binary
! 199: FROM collate1t1
! 200: ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
! 201: }
! 202: } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
! 203: do_test collate1-2.99 {
! 204: execsql {
! 205: DROP TABLE collate1t1;
! 206: }
! 207: } {}
! 208:
! 209: #
! 210: # These tests ensure that the default collation type for a column is used
! 211: # by an ORDER BY clause correctly. The focus is all the different ways
! 212: # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.
! 213: #
! 214: do_test collate1-3.0 {
! 215: execsql {
! 216: CREATE TABLE collate1t1(a COLLATE hex, b);
! 217: INSERT INTO collate1t1 VALUES( '0x5', 5 );
! 218: INSERT INTO collate1t1 VALUES( '1', 1 );
! 219: INSERT INTO collate1t1 VALUES( '0x45', 69 );
! 220: INSERT INTO collate1t1 VALUES( NULL, NULL );
! 221: SELECT * FROM collate1t1 ORDER BY a;
! 222: }
! 223: } {{} {} 1 1 0x5 5 0x45 69}
! 224:
! 225: do_test collate1-3.1 {
! 226: execsql {
! 227: SELECT * FROM collate1t1 ORDER BY 1;
! 228: }
! 229: } {{} {} 1 1 0x5 5 0x45 69}
! 230: do_test collate1-3.2 {
! 231: execsql {
! 232: SELECT * FROM collate1t1 ORDER BY collate1t1.a;
! 233: }
! 234: } {{} {} 1 1 0x5 5 0x45 69}
! 235: do_test collate1-3.3 {
! 236: execsql {
! 237: SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;
! 238: }
! 239: } {{} {} 1 1 0x5 5 0x45 69}
! 240: do_test collate1-3.4 {
! 241: execsql {
! 242: SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;
! 243: }
! 244: } {{} {} 1 1 0x5 5 0x45 69}
! 245: do_test collate1-3.5 {
! 246: execsql {
! 247: SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;
! 248: }
! 249: } {{} {} 0x45 69 0x5 5 1 1}
! 250: do_test collate1-3.5.1 {
! 251: execsql {
! 252: SELECT a COLLATE binary as c1, b as c2
! 253: FROM collate1t1 ORDER BY c1;
! 254: }
! 255: } {{} {} 0x45 69 0x5 5 1 1}
! 256: do_test collate1-3.6 {
! 257: execsql {
! 258: DROP TABLE collate1t1;
! 259: }
! 260: } {}
! 261:
! 262: # Update for SQLite version 3. The collate1-4.* test cases were written
! 263: # before manifest types were introduced. The following test cases still
! 264: # work, due to the 'affinity' mechanism, but they don't prove anything
! 265: # about collation sequences.
! 266: #
! 267: do_test collate1-4.0 {
! 268: execsql {
! 269: CREATE TABLE collate1t1(c1 numeric, c2 text);
! 270: INSERT INTO collate1t1 VALUES(1, 1);
! 271: INSERT INTO collate1t1 VALUES(12, 12);
! 272: INSERT INTO collate1t1 VALUES(NULL, NULL);
! 273: INSERT INTO collate1t1 VALUES(101, 101);
! 274: }
! 275: } {}
! 276: do_test collate1-4.1 {
! 277: execsql {
! 278: SELECT c1 FROM collate1t1 ORDER BY 1;
! 279: }
! 280: } {{} 1 12 101}
! 281: do_test collate1-4.2 {
! 282: execsql {
! 283: SELECT c2 FROM collate1t1 ORDER BY 1;
! 284: }
! 285: } {{} 1 101 12}
! 286: do_test collate1-4.3 {
! 287: execsql {
! 288: SELECT c2+0 FROM collate1t1 ORDER BY 1;
! 289: }
! 290: } {{} 1 12 101}
! 291: do_test collate1-4.4 {
! 292: execsql {
! 293: SELECT c1||'' FROM collate1t1 ORDER BY 1;
! 294: }
! 295: } {{} 1 101 12}
! 296: do_test collate1-4.4.1 {
! 297: execsql {
! 298: SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;
! 299: }
! 300: } {{} 1 12 101}
! 301: do_test collate1-4.5 {
! 302: execsql {
! 303: DROP TABLE collate1t1;
! 304: }
! 305: } {}
! 306:
! 307: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate1.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