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