1# 2001 September 15 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. Specfically 12# it tests that the different storage classes (integer, real, text etc.) 13# all work correctly. 14# 15# $Id: types.test,v 1.20 2009/06/29 06:00:37 danielk1977 Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Tests in this file are organized roughly as follows: 21# 22# types-1.*.*: Test that values are stored using the expected storage 23# classes when various forms of literals are inserted into 24# columns with different affinities. 25# types-1.1.*: INSERT INTO <table> VALUES(...) 26# types-1.2.*: INSERT INTO <table> SELECT... 27# types-1.3.*: UPDATE <table> SET... 28# 29# types-2.*.*: Check that values can be stored and retrieving using the 30# various storage classes. 31# types-2.1.*: INTEGER 32# types-2.2.*: REAL 33# types-2.3.*: NULL 34# types-2.4.*: TEXT 35# types-2.5.*: Records with a few different storage classes. 36# 37# types-3.*: Test that the '=' operator respects manifest types. 38# 39 40# Disable encryption on the database for this test. 41db close 42set DB [sqlite3 db test.db; sqlite3_connection_pointer db] 43sqlite3_rekey $DB {} 44 45# Create a table with one column for each type of affinity 46do_test types-1.1.0 { 47 execsql { 48 CREATE TABLE t1(i integer, n numeric, t text, o blob); 49 } 50} {} 51 52# Each element of the following list represents one test case. 53# 54# The first value of each sub-list is an SQL literal. The following 55# four value are the storage classes that would be used if the 56# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT 57# or NONE, respectively. 58set values { 59 { 5.0 integer integer text real } 60 { 5.1 real real text real } 61 { 5 integer integer text integer } 62 { '5.0' integer integer text text } 63 { '5.1' real real text text } 64 { '-5.0' integer integer text text } 65 { '-5.0' integer integer text text } 66 { '5' integer integer text text } 67 { 'abc' text text text text } 68 { NULL null null null null } 69} 70ifcapable {bloblit} { 71 lappend values { X'00' blob blob blob blob } 72} 73 74# This code tests that the storage classes specified above (in the $values 75# table) are correctly assigned when values are inserted using a statement 76# of the form: 77# 78# INSERT INTO <table> VALUE(<values>); 79# 80set tnum 1 81foreach val $values { 82 set lit [lindex $val 0] 83 execsql "DELETE FROM t1;" 84 execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);" 85 do_test types-1.1.$tnum { 86 execsql { 87 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1; 88 } 89 } [lrange $val 1 end] 90 incr tnum 91} 92 93# This code tests that the storage classes specified above (in the $values 94# table) are correctly assigned when values are inserted using a statement 95# of the form: 96# 97# INSERT INTO t1 SELECT .... 98# 99set tnum 1 100foreach val $values { 101 set lit [lindex $val 0] 102 execsql "DELETE FROM t1;" 103 execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;" 104 do_test types-1.2.$tnum { 105 execsql { 106 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1; 107 } 108 } [lrange $val 1 end] 109 incr tnum 110} 111 112# This code tests that the storage classes specified above (in the $values 113# table) are correctly assigned when values are inserted using a statement 114# of the form: 115# 116# UPDATE <table> SET <column> = <value>; 117# 118set tnum 1 119foreach val $values { 120 set lit [lindex $val 0] 121 execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;" 122 do_test types-1.3.$tnum { 123 execsql { 124 SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1; 125 } 126 } [lrange $val 1 end] 127 incr tnum 128} 129 130execsql { 131 DROP TABLE t1; 132} 133 134# Open the table with root-page $rootpage at the btree 135# level. Return a list that is the length of each record 136# in the table, in the tables default scanning order. 137proc record_sizes {rootpage} { 138 set bt [btree_open test.db 10] 139 btree_begin_transaction $bt 140 set c [btree_cursor $bt $rootpage 0] 141 btree_first $c 142 while 1 { 143 lappend res [btree_payload_size $c] 144 if {[btree_next $c]} break 145 } 146 btree_close_cursor $c 147 btree_close $bt 148 set res 149} 150 151 152# Create a table and insert some 1-byte integers. Make sure they 153# can be read back OK. These should be 3 byte records. 154do_test types-2.1.1 { 155 execsql { 156 CREATE TABLE t1(a integer); 157 INSERT INTO t1 VALUES(0); 158 INSERT INTO t1 VALUES(120); 159 INSERT INTO t1 VALUES(-120); 160 } 161} {} 162do_test types-2.1.2 { 163 execsql { 164 SELECT a FROM t1; 165 } 166} {0 120 -120} 167 168# Try some 2-byte integers (4 byte records) 169do_test types-2.1.3 { 170 execsql { 171 INSERT INTO t1 VALUES(30000); 172 INSERT INTO t1 VALUES(-30000); 173 } 174} {} 175do_test types-2.1.4 { 176 execsql { 177 SELECT a FROM t1; 178 } 179} {0 120 -120 30000 -30000} 180 181# 4-byte integers (6 byte records) 182do_test types-2.1.5 { 183 execsql { 184 INSERT INTO t1 VALUES(2100000000); 185 INSERT INTO t1 VALUES(-2100000000); 186 } 187} {} 188do_test types-2.1.6 { 189 execsql { 190 SELECT a FROM t1; 191 } 192} {0 120 -120 30000 -30000 2100000000 -2100000000} 193 194# 8-byte integers (10 byte records) 195do_test types-2.1.7 { 196 execsql { 197 INSERT INTO t1 VALUES(9000000*1000000*1000000); 198 INSERT INTO t1 VALUES(-9000000*1000000*1000000); 199 } 200} {} 201do_test types-2.1.8 { 202 execsql { 203 SELECT a FROM t1; 204 } 205} [list 0 120 -120 30000 -30000 2100000000 -2100000000 \ 206 9000000000000000000 -9000000000000000000] 207 208# Check that all the record sizes are as we expected. 209ifcapable legacyformat { 210 do_test types-2.1.9 { 211 set root [db eval {select rootpage from sqlite_master where name = 't1'}] 212 record_sizes $root 213 } {3 3 3 4 4 6 6 10 10} 214} else { 215 do_test types-2.1.9 { 216 set root [db eval {select rootpage from sqlite_master where name = 't1'}] 217 record_sizes $root 218 } {2 3 3 4 4 6 6 10 10} 219} 220 221# Insert some reals. These should be 10 byte records. 222do_test types-2.2.1 { 223 execsql { 224 CREATE TABLE t2(a float); 225 INSERT INTO t2 VALUES(0.0); 226 INSERT INTO t2 VALUES(12345.678); 227 INSERT INTO t2 VALUES(-12345.678); 228 } 229} {} 230do_test types-2.2.2 { 231 execsql { 232 SELECT a FROM t2; 233 } 234} {0.0 12345.678 -12345.678} 235 236# Check that all the record sizes are as we expected. 237ifcapable legacyformat { 238 do_test types-2.2.3 { 239 set root [db eval {select rootpage from sqlite_master where name = 't2'}] 240 record_sizes $root 241 } {3 10 10} 242} else { 243 do_test types-2.2.3 { 244 set root [db eval {select rootpage from sqlite_master where name = 't2'}] 245 record_sizes $root 246 } {2 10 10} 247} 248 249# Insert a NULL. This should be a two byte record. 250do_test types-2.3.1 { 251 execsql { 252 CREATE TABLE t3(a nullvalue); 253 INSERT INTO t3 VALUES(NULL); 254 } 255} {} 256do_test types-2.3.2 { 257 execsql { 258 SELECT a ISNULL FROM t3; 259 } 260} {1} 261 262# Check that all the record sizes are as we expected. 263do_test types-2.3.3 { 264 set root [db eval {select rootpage from sqlite_master where name = 't3'}] 265 record_sizes $root 266} {2} 267 268# Insert a couple of strings. 269do_test types-2.4.1 { 270 set string10 abcdefghij 271 set string500 [string repeat $string10 50] 272 set string500000 [string repeat $string10 50000] 273 274 execsql " 275 CREATE TABLE t4(a string); 276 INSERT INTO t4 VALUES('$string10'); 277 INSERT INTO t4 VALUES('$string500'); 278 INSERT INTO t4 VALUES('$string500000'); 279 " 280} {} 281do_test types-2.4.2 { 282 execsql { 283 SELECT a FROM t4; 284 } 285} [list $string10 $string500 $string500000] 286 287# Check that all the record sizes are as we expected. This is dependant on 288# the database encoding. 289if { $sqlite_options(utf16)==0 || [execsql {pragma encoding}] == "UTF-8" } { 290 do_test types-2.4.3 { 291 set root [db eval {select rootpage from sqlite_master where name = 't4'}] 292 record_sizes $root 293 } {12 503 500004} 294} else { 295 do_test types-2.4.3 { 296 set root [db eval {select rootpage from sqlite_master where name = 't4'}] 297 record_sizes $root 298 } {22 1003 1000004} 299} 300 301do_test types-2.5.1 { 302 execsql { 303 DROP TABLE t1; 304 DROP TABLE t2; 305 DROP TABLE t3; 306 DROP TABLE t4; 307 CREATE TABLE t1(a, b, c); 308 } 309} {} 310do_test types-2.5.2 { 311 set string10 abcdefghij 312 set string500 [string repeat $string10 50] 313 set string500000 [string repeat $string10 50000] 314 315 execsql "INSERT INTO t1 VALUES(NULL, '$string10', 4000);" 316 execsql "INSERT INTO t1 VALUES('$string500', 4000, NULL);" 317 execsql "INSERT INTO t1 VALUES(4000, NULL, '$string500000');" 318} {} 319do_test types-2.5.3 { 320 execsql { 321 SELECT * FROM t1; 322 } 323} [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000] 324 325finish_test 326