gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/types.test (about) 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 17 set testdir [file dirname $argv0] 18 source $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. 41 db close 42 set DB [sqlite3 db test.db; sqlite3_connection_pointer db] 43 sqlite3_rekey $DB {} 44 45 # Create a table with one column for each type of affinity 46 do_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. 58 set 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 } 70 ifcapable {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 # 80 set tnum 1 81 foreach 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 # 99 set tnum 1 100 foreach 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 # 118 set tnum 1 119 foreach 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 130 execsql { 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. 137 proc 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. 154 do_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 } {} 162 do_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) 169 do_test types-2.1.3 { 170 execsql { 171 INSERT INTO t1 VALUES(30000); 172 INSERT INTO t1 VALUES(-30000); 173 } 174 } {} 175 do_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) 182 do_test types-2.1.5 { 183 execsql { 184 INSERT INTO t1 VALUES(2100000000); 185 INSERT INTO t1 VALUES(-2100000000); 186 } 187 } {} 188 do_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) 195 do_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 } {} 201 do_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. 209 ifcapable 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. 222 do_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 } {} 230 do_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. 237 ifcapable 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. 250 do_test types-2.3.1 { 251 execsql { 252 CREATE TABLE t3(a nullvalue); 253 INSERT INTO t3 VALUES(NULL); 254 } 255 } {} 256 do_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. 263 do_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. 269 do_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 } {} 281 do_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. 289 if { $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 301 do_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 } {} 310 do_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 } {} 319 do_test types-2.5.3 { 320 execsql { 321 SELECT * FROM t1; 322 } 323 } [list {} $string10 4000 $string500 4000 {} 4000 {} $string500000] 324 325 finish_test