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.6 2009/02/02 16:32:55 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 2 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 NULL is returned if any parameter is NULL 83# 84do_test substr-1.90 { 85 db eval {SELECT ifnull(substr(NULL,1,1),'nil')} 86} nil 87do_test substr-1.91 { 88 db eval {SELECT ifnull(substr(NULL,1),'nil')} 89} nil 90do_test substr-1.92 { 91 db eval {SELECT ifnull(substr('abcdefg',NULL,1),'nil')} 92} nil 93do_test substr-1.93 { 94 db eval {SELECT ifnull(substr('abcdefg',NULL),'nil')} 95} nil 96do_test substr-1.94 { 97 db eval {SELECT ifnull(substr('abcdefg',1,NULL),'nil')} 98} nil 99 100# Make sure everything works with long unicode characters 101# 102substr-test 2.1 \u1234\u2345\u3456 1 1 \u1234 103substr-test 2.2 \u1234\u2345\u3456 2 1 \u2345 104substr-test 2.3 \u1234\u2345\u3456 1 2 \u1234\u2345 105substr-test 2.4 \u1234\u2345\u3456 -1 1 \u3456 106substr-test 2.5 a\u1234b\u2345c\u3456c -5 3 b\u2345c 107substr-test 2.6 a\u1234b\u2345c\u3456c -2 -3 b\u2345c 108 109# Basic functionality for BLOBs 110# 111subblob-test 3.1 61626364656667 1 1 61 112subblob-test 3.2 61626364656667 2 1 62 113subblob-test 3.3 61626364656667 1 2 6162 114subblob-test 3.4 61626364656667 1 100 61626364656667 115subblob-test 3.5 61626364656667 0 2 61 116subblob-test 3.6 61626364656667 -1 1 67 117subblob-test 3.7 61626364656667 -1 10 67 118subblob-test 3.8 61626364656667 -5 3 636465 119subblob-test 3.9 61626364656667 -7 3 616263 120subblob-test 3.10 61626364656667 -100 98 6162636465 121 122# If these blobs were strings, then they would contain multi-byte 123# characters. But since they are blobs, the substr indices refer 124# to bytes. 125# 126subblob-test 4.1 61E188B462E28D8563E3919663 1 1 61 127subblob-test 4.2 61E188B462E28D8563E3919663 2 1 E1 128subblob-test 4.3 61E188B462E28D8563E3919663 1 2 61E1 129subblob-test 4.4 61E188B462E28D8563E3919663 -2 1 96 130subblob-test 4.5 61E188B462E28D8563E3919663 -5 4 63E39196 131subblob-test 4.6 61E188B462E28D8563E3919663 -100 98 61E188B462E28D8563E391 132 133# Two-argument SUBSTR 134# 135proc substr-2-test {id string idx result} { 136 db eval { 137 DELETE FROM t1; 138 INSERT INTO t1(t) VALUES($string) 139 } 140 do_test substr-$id.1 [subst { 141 execsql { 142 SELECT substr(t, $idx) FROM t1 143 } 144 }] [list $result] 145 set qstr '[string map {' ''} $string]' 146 do_test substr-$id.2 [subst { 147 execsql { 148 SELECT substr($qstr, $idx) 149 } 150 }] [list $result] 151} 152substr-2-test 5.1 abcdefghijklmnop 5 efghijklmnop 153substr-2-test 5.2 abcdef -5 bcdef 154 155finish_test 156