1# 2007 May 14 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing the built-in SUBSTR() functions. 13# 14# $Id: substr.test,v 1.4 2009/02/01 19:23:32 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable !tclvar { 20 finish_test 21 return 22} 23 24# Create a table to work with. 25# 26execsql { 27 CREATE TABLE t1(t text, b blob) 28} 29proc substr-test {id string i1 i2 result} { 30 db eval { 31 DELETE FROM t1; 32 INSERT INTO t1(t) VALUES($string) 33 } 34 do_test substr-$id.1 [subst { 35 execsql { 36 SELECT substr(t, $i1, $i2) FROM t1 37 } 38 }] [list $result] 39 set qstr '[string map {' ''} $string]' 40 do_test substr-$id.2 [subst { 41 execsql { 42 SELECT substr($qstr, $i1, $i2) 43 } 44 }] [list $result] 45} 46proc subblob-test {id hex i1 i2 hexresult} { 47 db eval " 48 DELETE FROM t1; 49 INSERT INTO t1(b) VALUES(x'$hex') 50 " 51 do_test substr-$id.1 [subst { 52 execsql { 53 SELECT hex(substr(b, $i1, $i2)) FROM t1 54 } 55 }] [list $hexresult] 56 do_test substr-$id.2 [subst { 57 execsql { 58 SELECT hex(substr(x'$hex', $i1, $i2)) 59 } 60 }] [list $hexresult] 61} 62 63# Basic SUBSTR functionality 64# 65substr-test 1.1 abcdefg 1 1 a 66substr-test 1.2 abcdefg 2 1 b 67substr-test 1.3 abcdefg 1 2 ab 68substr-test 1.4 abcdefg 1 100 abcdefg 69substr-test 1.5 abcdefg 0 1 a 70substr-test 1.6 abcdefg -1 1 g 71substr-test 1.7 abcdefg -1 10 g 72substr-test 1.8 abcdefg -5 3 cde 73substr-test 1.9 abcdefg -7 3 abc 74substr-test 1.10 abcdefg -100 98 abcde 75substr-test 1.11 abcdefg 5 -1 d 76substr-test 1.12 abcdefg 5 -4 abcd 77substr-test 1.13 abcdefg 5 -5 abcd 78substr-test 1.14 abcdefg -5 -1 b 79substr-test 1.15 abcdefg -5 -2 ab 80substr-test 1.16 abcdefg -5 -3 ab 81 82# Make sure everything works with long unicode characters 83# 84substr-test 2.1 \u1234\u2345\u3456 1 1 \u1234 85substr-test 2.2 \u1234\u2345\u3456 2 1 \u2345 86substr-test 2.3 \u1234\u2345\u3456 1 2 \u1234\u2345 87substr-test 2.4 \u1234\u2345\u3456 -1 1 \u3456 88substr-test 2.5 a\u1234b\u2345c\u3456c -5 3 b\u2345c 89substr-test 2.6 a\u1234b\u2345c\u3456c -2 -3 b\u2345c 90 91# Basic functionality for BLOBs 92# 93subblob-test 3.1 61626364656667 1 1 61 94subblob-test 3.2 61626364656667 2 1 62 95subblob-test 3.3 61626364656667 1 2 6162 96subblob-test 3.4 61626364656667 1 100 61626364656667 97subblob-test 3.5 61626364656667 0 1 61 98subblob-test 3.6 61626364656667 -1 1 67 99subblob-test 3.7 61626364656667 -1 10 67 100subblob-test 3.8 61626364656667 -5 3 636465 101subblob-test 3.9 61626364656667 -7 3 616263 102subblob-test 3.10 61626364656667 -100 98 6162636465 103 104# If these blobs were strings, then they would contain multi-byte 105# characters. But since they are blobs, the substr indices refer 106# to bytes. 107# 108subblob-test 4.1 61E188B462E28D8563E3919663 1 1 61 109subblob-test 4.2 61E188B462E28D8563E3919663 2 1 E1 110subblob-test 4.3 61E188B462E28D8563E3919663 1 2 61E1 111subblob-test 4.4 61E188B462E28D8563E3919663 -2 1 96 112subblob-test 4.5 61E188B462E28D8563E3919663 -5 4 63E39196 113subblob-test 4.6 61E188B462E28D8563E3919663 -100 98 61E188B462E28D8563E391 114 115# Two-argument SUBSTR 116# 117proc substr-2-test {id string idx result} { 118 db eval { 119 DELETE FROM t1; 120 INSERT INTO t1(t) VALUES($string) 121 } 122 do_test substr-$id.1 [subst { 123 execsql { 124 SELECT substr(t, $idx) FROM t1 125 } 126 }] [list $result] 127 set qstr '[string map {' ''} $string]' 128 do_test substr-$id.2 [subst { 129 execsql { 130 SELECT substr($qstr, $idx) 131 } 132 }] [list $result] 133} 134substr-2-test 5.1 abcdefghijklmnop 5 efghijklmnop 135substr-2-test 5.2 abcdef -5 bcdef 136 137finish_test 138