| blob | 546ab8649a79046ce7c28a99ecd4101261025c40 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library.
15 #
16 # This file implements tests for the special processing associated
17 # with INTEGER PRIMARY KEY columns.
18 #
19 # $Id: intpkey.test,v 1.14 2003/06/15 23:42:25 drh Exp $
21 set testdir [file dirname $argv0]
22 source $testdir/tester.tcl
24 # Create a table with a primary key and a datatype other than
25 # integer
26 #
27 do_test intpkey-1.0 {
28 execsql {
29 CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
30 }
31 } {}
33 # There should be an index associated with the primary key
34 #
35 do_test intpkey-1.1 {
36 execsql {
37 SELECT name FROM sqlite_master
38 WHERE type='index' AND tbl_name='t1';
39 }
40 } {{(t1 autoindex 1)}}
42 # Now create a table with an integer primary key and verify that
43 # there is no associated index.
44 #
45 do_test intpkey-1.2 {
46 execsql {
47 DROP TABLE t1;
48 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
49 SELECT name FROM sqlite_master
50 WHERE type='index' AND tbl_name='t1';
51 }
52 } {}
54 # Insert some records into the new table. Specify the primary key
55 # and verify that the key is used as the record number.
56 #
57 do_test intpkey-1.3 {
58 execsql {
59 INSERT INTO t1 VALUES(5,'hello','world');
60 }
61 db last_insert_rowid
62 } {5}
63 do_test intpkey-1.4 {
64 execsql {
65 SELECT * FROM t1;
66 }
67 } {5 hello world}
68 do_test intpkey-1.5 {
69 execsql {
70 SELECT rowid, * FROM t1;
71 }
72 } {5 5 hello world}
74 # Attempting to insert a duplicate primary key should give a constraint
75 # failure.
76 #
77 do_test intpkey-1.6 {
78 set r [catch {execsql {
79 INSERT INTO t1 VALUES(5,'second','entry');
80 }} msg]
81 lappend r $msg
82 } {1 {PRIMARY KEY must be unique}}
83 do_test intpkey-1.7 {
84 execsql {
85 SELECT rowid, * FROM t1;
86 }
87 } {5 5 hello world}
88 do_test intpkey-1.8 {
89 set r [catch {execsql {
90 INSERT INTO t1 VALUES(6,'second','entry');
91 }} msg]
92 lappend r $msg
93 } {0 {}}
94 do_test intpkey-1.8.1 {
95 db last_insert_rowid
96 } {6}
97 do_test intpkey-1.9 {
98 execsql {
99 SELECT rowid, * FROM t1;
100 }
101 } {5 5 hello world 6 6 second entry}
103 # A ROWID is automatically generated for new records that do not specify
104 # the integer primary key.
105 #
106 do_test intpkey-1.10 {
107 execsql {
108 INSERT INTO t1(b,c) VALUES('one','two');
109 SELECT b FROM t1 ORDER BY b;
110 }
111 } {hello one second}
113 # Try to change the ROWID for the new entry.
114 #
115 do_test intpkey-1.11 {
116 execsql {
117 UPDATE t1 SET a=4 WHERE b='one';
118 SELECT * FROM t1;
119 }
120 } {4 one two 5 hello world 6 second entry}
122 # Make sure SELECT statements are able to use the primary key column
123 # as an index.
124 #
125 do_test intpkey-1.12 {
126 execsql {
127 SELECT * FROM t1 WHERE a==4;
128 }
129 } {4 one two}
131 # Try to insert a non-integer value into the primary key field. This
132 # should result in a data type mismatch.
133 #
134 do_test intpkey-1.13.1 {
135 set r [catch {execsql {
136 INSERT INTO t1 VALUES('x','y','z');
137 }} msg]
138 lappend r $msg
139 } {1 {datatype mismatch}}
140 do_test intpkey-1.13.2 {
141 set r [catch {execsql {
142 INSERT INTO t1 VALUES('','y','z');
143 }} msg]
144 lappend r $msg
145 } {1 {datatype mismatch}}
146 do_test intpkey-1.14 {
147 set r [catch {execsql {
148 INSERT INTO t1 VALUES(3.4,'y','z');
149 }} msg]
150 lappend r $msg
151 } {1 {datatype mismatch}}
152 do_test intpkey-1.15 {
153 set r [catch {execsql {
154 INSERT INTO t1 VALUES(-3,'y','z');
155 }} msg]
156 lappend r $msg
157 } {0 {}}
158 do_test intpkey-1.16 {
159 execsql {SELECT * FROM t1}
160 } {-3 y z 4 one two 5 hello world 6 second entry}
162 #### INDICES
163 # Check to make sure indices work correctly with integer primary keys
164 #
165 do_test intpkey-2.1 {
166 execsql {
167 CREATE INDEX i1 ON t1(b);
168 SELECT * FROM t1 WHERE b=='y'
169 }
170 } {-3 y z}
171 do_test intpkey-2.1.1 {
172 execsql {
173 SELECT * FROM t1 WHERE b=='y' AND rowid<0
174 }
175 } {-3 y z}
176 do_test intpkey-2.1.2 {
177 execsql {
178 SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
179 }
180 } {-3 y z}
181 do_test intpkey-2.1.3 {
182 execsql {
183 SELECT * FROM t1 WHERE b>='y'
184 }
185 } {-3 y z}
186 do_test intpkey-2.1.4 {
187 execsql {
188 SELECT * FROM t1 WHERE b>='y' AND rowid<10
189 }
190 } {-3 y z}
192 do_test intpkey-2.2 {
193 execsql {
194 UPDATE t1 SET a=8 WHERE b=='y';
195 SELECT * FROM t1 WHERE b=='y';
196 }
197 } {8 y z}
198 do_test intpkey-2.3 {
199 execsql {
200 SELECT rowid, * FROM t1;
201 }
202 } {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
203 do_test intpkey-2.4 {
204 execsql {
205 SELECT rowid, * FROM t1 WHERE b<'second'
206 }
207 } {5 5 hello world 4 4 one two}
208 do_test intpkey-2.4.1 {
209 execsql {
210 SELECT rowid, * FROM t1 WHERE 'second'>b
211 }
212 } {5 5 hello world 4 4 one two}
213 do_test intpkey-2.4.2 {
214 execsql {
215 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
216 }
217 } {4 4 one two 5 5 hello world}
218 do_test intpkey-2.4.3 {
219 execsql {
220 SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
221 }
222 } {4 4 one two 5 5 hello world}
223 do_test intpkey-2.5 {
224 execsql {
225 SELECT rowid, * FROM t1 WHERE b>'a'
226 }
227 } {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
228 do_test intpkey-2.6 {
229 execsql {
230 DELETE FROM t1 WHERE rowid=4;
231 SELECT * FROM t1 WHERE b>'a';
232 }
233 } {5 hello world 6 second entry 8 y z}
234 do_test intpkey-2.7 {
235 execsql {
236 UPDATE t1 SET a=-4 WHERE rowid=8;
237 SELECT * FROM t1 WHERE b>'a';
238 }
239 } {5 hello world 6 second entry -4 y z}
240 do_test intpkey-2.7 {
241 execsql {
242 SELECT * FROM t1
243 }
244 } {-4 y z 5 hello world 6 second entry}
246 # Do an SQL statement. Append the search count to the end of the result.
247 #
248 proc count sql {
249 set ::sqlite_search_count 0
250 return [concat [execsql $sql] $::sqlite_search_count]
251 }
253 # Create indices that include the integer primary key as one of their
254 # columns.
255 #
256 do_test intpkey-3.1 {
257 execsql {
258 CREATE INDEX i2 ON t1(a);
259 }
260 } {}
261 do_test intpkey-3.2 {
262 count {
263 SELECT * FROM t1 WHERE a=5;
264 }
265 } {5 hello world 0}
266 do_test intpkey-3.3 {
267 count {
268 SELECT * FROM t1 WHERE a>4 AND a<6;
269 }
270 } {5 hello world 2}
271 do_test intpkey-3.4 {
272 count {
273 SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
274 }
275 } {5 hello world 3}
276 do_test intpkey-3.5 {
277 execsql {
278 CREATE INDEX i3 ON t1(c,a);
279 }
280 } {}
281 do_test intpkey-3.6 {
282 count {
283 SELECT * FROM t1 WHERE c=='world';
284 }
285 } {5 hello world 3}
286 do_test intpkey-3.7 {
287 execsql {INSERT INTO t1 VALUES(11,'hello','world')}
288 count {
289 SELECT * FROM t1 WHERE c=='world';
290 }
291 } {5 hello world 11 hello world 5}
292 do_test intpkey-3.8 {
293 count {
294 SELECT * FROM t1 WHERE c=='world' AND a>7;
295 }
296 } {11 hello world 5}
297 do_test intpkey-3.9 {
298 count {
299 SELECT * FROM t1 WHERE 7<a;
300 }
301 } {11 hello world 1}
303 # Test inequality constraints on integer primary keys and rowids
304 #
305 do_test intpkey-4.1 {
306 count {
307 SELECT * FROM t1 WHERE 11=rowid
308 }
309 } {11 hello world 0}
310 do_test intpkey-4.2 {
311 count {
312 SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
313 }
314 } {11 hello world 0}
315 do_test intpkey-4.3 {
316 count {
317 SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
318 }
319 } {11 hello world 0}
320 do_test intpkey-4.4 {
321 count {
322 SELECT * FROM t1 WHERE rowid==11
323 }
324 } {11 hello world 0}
325 do_test intpkey-4.5 {
326 count {
327 SELECT * FROM t1 WHERE oid==11 AND b=='hello'
328 }
329 } {11 hello world 0}
330 do_test intpkey-4.6 {
331 count {
332 SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
333 }
334 } {11 hello world 0}
336 do_test intpkey-4.7 {
337 count {
338 SELECT * FROM t1 WHERE 8<rowid;
339 }
340 } {11 hello world 1}
341 do_test intpkey-4.8 {
342 count {
343 SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
344 }
345 } {11 hello world 1}
346 do_test intpkey-4.9 {
347 count {
348 SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
349 }
350 } {11 hello world 1}
351 do_test intpkey-4.10 {
352 count {
353 SELECT * FROM t1 WHERE 0>=_rowid_;
354 }
355 } {-4 y z 1}
356 do_test intpkey-4.11 {
357 count {
358 SELECT * FROM t1 WHERE a<0;
359 }
360 } {-4 y z 1}
361 do_test intpkey-4.12 {
362 count {
363 SELECT * FROM t1 WHERE a<0 AND a>10;
364 }
365 } {1}
367 # Make sure it is OK to insert a rowid of 0
368 #
369 do_test intpkey-5.1 {
370 execsql {
371 INSERT INTO t1 VALUES(0,'zero','entry');
372 }
373 count {
374 SELECT * FROM t1 WHERE a=0;
375 }
376 } {0 zero entry 0}
377 do_test intpkey=5.2 {
378 execsql {
379 SELECT rowid, a FROM t1
380 }
381 } {-4 -4 0 0 5 5 6 6 11 11}
383 # Test the ability of the COPY command to put data into a
384 # table that contains an integer primary key.
385 #
386 do_test intpkey-6.1 {
387 set f [open ./data1.txt w]
388 puts $f "20\tb-20\tc-20"
389 puts $f "21\tb-21\tc-21"
390 puts $f "22\tb-22\tc-22"
391 close $f
392 execsql {
393 COPY t1 FROM 'data1.txt';
394 SELECT * FROM t1 WHERE a>=20;
395 }
396 } {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
397 do_test intpkey-6.2 {
398 execsql {
399 SELECT * FROM t1 WHERE b=='hello'
400 }
401 } {5 hello world 11 hello world}
402 do_test intpkey-6.3 {
403 execsql {
404 DELETE FROM t1 WHERE b='b-21';
405 SELECT * FROM t1 WHERE b=='b-21';
406 }
407 } {}
408 do_test intpkey-6.4 {
409 execsql {
410 SELECT * FROM t1 WHERE a>=20
411 }
412 } {20 b-20 c-20 22 b-22 c-22}
414 # Do an insert of values with the columns specified out of order.
415 #
416 do_test intpkey-7.1 {
417 execsql {
418 INSERT INTO t1(c,b,a) VALUES('row','new',30);
419 SELECT * FROM t1 WHERE rowid>=30;
420 }
421 } {30 new row}
422 do_test intpkey-7.2 {
423 execsql {
424 SELECT * FROM t1 WHERE rowid>20;
425 }
426 } {22 b-22 c-22 30 new row}
428 # Do an insert from a select statement.
429 #
430 do_test intpkey-8.1 {
431 execsql {
432 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
433 INSERT INTO t2 SELECT * FROM t1;
434 SELECT rowid FROM t2;
435 }
436 } {-4 0 5 6 11 20 22 30}
437 do_test intpkey-8.2 {
438 execsql {
439 SELECT x FROM t2;
440 }
441 } {-4 0 5 6 11 20 22 30}
443 do_test intpkey-9.1 {
444 execsql {
445 UPDATE t1 SET c='www' WHERE c='world';
446 SELECT rowid, a, c FROM t1 WHERE c=='www';
447 }
448 } {5 5 www 11 11 www}
451 # Check insert of NULL for primary key
452 #
453 do_test intpkey-10.1 {
454 execsql {
455 DROP TABLE t2;
456 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
457 INSERT INTO t2 VALUES(NULL, 1, 2);
458 SELECT * from t2;
459 }
460 } {1 1 2}
461 do_test intpkey-10.2 {
462 execsql {
463 INSERT INTO t2 VALUES(NULL, 2, 3);
464 SELECT * from t2 WHERE x=2;
465 }
466 } {2 2 3}
467 do_test intpkey-10.3 {
468 execsql {
469 INSERT INTO t2 SELECT NULL, z, y FROM t2;
470 SELECT * FROM t2;
471 }
472 } {1 1 2 2 2 3 3 2 1 4 3 2}
474 # This tests checks to see if a floating point number can be used
475 # to reference an integer primary key.
476 #
477 do_test intpkey-11.1 {
478 execsql {
479 SELECT b FROM t1 WHERE a=2.0+3.0;
480 }
481 } {hello}
482 do_test intpkey-11.1 {
483 execsql {
484 SELECT b FROM t1 WHERE a=2.0+3.5;
485 }
486 } {}
488 integrity_check intpkey-12.1
490 finish_test