wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.
12 #
13 # This file implements tests for the special processing associated
14 # with INTEGER PRIMARY KEY columns.
15 #
16 # $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $
17  
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20  
21 # Create a table with a primary key and a datatype other than
22 # integer
23 #
24 do_test intpkey-1.0 {
25 execsql {
26 CREATE TABLE t1(a TEXT PRIMARY KEY, b, c);
27 }
28 } {}
29  
30 # There should be an index associated with the primary key
31 #
32 do_test intpkey-1.1 {
33 execsql {
34 SELECT name FROM sqlite_master
35 WHERE type='index' AND tbl_name='t1';
36 }
37 } {sqlite_autoindex_t1_1}
38  
39 # Now create a table with an integer primary key and verify that
40 # there is no associated index.
41 #
42 do_test intpkey-1.2 {
43 execsql {
44 DROP TABLE t1;
45 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
46 SELECT name FROM sqlite_master
47 WHERE type='index' AND tbl_name='t1';
48 }
49 } {}
50  
51 # Insert some records into the new table. Specify the primary key
52 # and verify that the key is used as the record number.
53 #
54 do_test intpkey-1.3 {
55 execsql {
56 INSERT INTO t1 VALUES(5,'hello','world');
57 }
58 db last_insert_rowid
59 } {5}
60 do_test intpkey-1.4 {
61 execsql {
62 SELECT * FROM t1;
63 }
64 } {5 hello world}
65 do_test intpkey-1.5 {
66 execsql {
67 SELECT rowid, * FROM t1;
68 }
69 } {5 5 hello world}
70  
71 # Attempting to insert a duplicate primary key should give a constraint
72 # failure.
73 #
74 do_test intpkey-1.6 {
75 set r [catch {execsql {
76 INSERT INTO t1 VALUES(5,'second','entry');
77 }} msg]
78 lappend r $msg
79 } {1 {PRIMARY KEY must be unique}}
80 do_test intpkey-1.7 {
81 execsql {
82 SELECT rowid, * FROM t1;
83 }
84 } {5 5 hello world}
85 do_test intpkey-1.8 {
86 set r [catch {execsql {
87 INSERT INTO t1 VALUES(6,'second','entry');
88 }} msg]
89 lappend r $msg
90 } {0 {}}
91 do_test intpkey-1.8.1 {
92 db last_insert_rowid
93 } {6}
94 do_test intpkey-1.9 {
95 execsql {
96 SELECT rowid, * FROM t1;
97 }
98 } {5 5 hello world 6 6 second entry}
99  
100 # A ROWID is automatically generated for new records that do not specify
101 # the integer primary key.
102 #
103 do_test intpkey-1.10 {
104 execsql {
105 INSERT INTO t1(b,c) VALUES('one','two');
106 SELECT b FROM t1 ORDER BY b;
107 }
108 } {hello one second}
109  
110 # Try to change the ROWID for the new entry.
111 #
112 do_test intpkey-1.11 {
113 execsql {
114 UPDATE t1 SET a=4 WHERE b='one';
115 SELECT * FROM t1;
116 }
117 } {4 one two 5 hello world 6 second entry}
118  
119 # Make sure SELECT statements are able to use the primary key column
120 # as an index.
121 #
122 do_test intpkey-1.12.1 {
123 execsql {
124 SELECT * FROM t1 WHERE a==4;
125 }
126 } {4 one two}
127 do_test intpkey-1.12.2 {
128 set sqlite_query_plan
129 } {t1 *}
130  
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}
161  
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}
191  
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}
245  
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 }
252  
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 4}
297 do_test intpkey-3.9 {
298 count {
299 SELECT * FROM t1 WHERE 7<a;
300 }
301 } {11 hello world 1}
302  
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}
335  
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}
366  
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}
382  
383 # Test the ability of the COPY command to put data into a
384 # table that contains an integer primary key.
385 #
386 # COPY command has been removed. But we retain these tests so
387 # that the tables will contain the right data for tests that follow.
388 #
389 do_test intpkey-6.1 {
390 execsql {
391 BEGIN;
392 INSERT INTO t1 VALUES(20,'b-20','c-20');
393 INSERT INTO t1 VALUES(21,'b-21','c-21');
394 INSERT INTO t1 VALUES(22,'b-22','c-22');
395 COMMIT;
396 SELECT * FROM t1 WHERE a>=20;
397 }
398 } {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
399 do_test intpkey-6.2 {
400 execsql {
401 SELECT * FROM t1 WHERE b=='hello'
402 }
403 } {5 hello world 11 hello world}
404 do_test intpkey-6.3 {
405 execsql {
406 DELETE FROM t1 WHERE b='b-21';
407 SELECT * FROM t1 WHERE b=='b-21';
408 }
409 } {}
410 do_test intpkey-6.4 {
411 execsql {
412 SELECT * FROM t1 WHERE a>=20
413 }
414 } {20 b-20 c-20 22 b-22 c-22}
415  
416 # Do an insert of values with the columns specified out of order.
417 #
418 do_test intpkey-7.1 {
419 execsql {
420 INSERT INTO t1(c,b,a) VALUES('row','new',30);
421 SELECT * FROM t1 WHERE rowid>=30;
422 }
423 } {30 new row}
424 do_test intpkey-7.2 {
425 execsql {
426 SELECT * FROM t1 WHERE rowid>20;
427 }
428 } {22 b-22 c-22 30 new row}
429  
430 # Do an insert from a select statement.
431 #
432 do_test intpkey-8.1 {
433 execsql {
434 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
435 INSERT INTO t2 SELECT * FROM t1;
436 SELECT rowid FROM t2;
437 }
438 } {-4 0 5 6 11 20 22 30}
439 do_test intpkey-8.2 {
440 execsql {
441 SELECT x FROM t2;
442 }
443 } {-4 0 5 6 11 20 22 30}
444  
445 do_test intpkey-9.1 {
446 execsql {
447 UPDATE t1 SET c='www' WHERE c='world';
448 SELECT rowid, a, c FROM t1 WHERE c=='www';
449 }
450 } {5 5 www 11 11 www}
451  
452  
453 # Check insert of NULL for primary key
454 #
455 do_test intpkey-10.1 {
456 execsql {
457 DROP TABLE t2;
458 CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z);
459 INSERT INTO t2 VALUES(NULL, 1, 2);
460 SELECT * from t2;
461 }
462 } {1 1 2}
463 do_test intpkey-10.2 {
464 execsql {
465 INSERT INTO t2 VALUES(NULL, 2, 3);
466 SELECT * from t2 WHERE x=2;
467 }
468 } {2 2 3}
469 do_test intpkey-10.3 {
470 execsql {
471 INSERT INTO t2 SELECT NULL, z, y FROM t2;
472 SELECT * FROM t2;
473 }
474 } {1 1 2 2 2 3 3 2 1 4 3 2}
475  
476 # This tests checks to see if a floating point number can be used
477 # to reference an integer primary key.
478 #
479 do_test intpkey-11.1 {
480 execsql {
481 SELECT b FROM t1 WHERE a=2.0+3.0;
482 }
483 } {hello}
484 do_test intpkey-11.1 {
485 execsql {
486 SELECT b FROM t1 WHERE a=2.0+3.5;
487 }
488 } {}
489  
490 integrity_check intpkey-12.1
491  
492 # Try to use a string that looks like a floating point number as
493 # an integer primary key. This should actually work when the floating
494 # point value can be rounded to an integer without loss of data.
495 #
496 do_test intpkey-13.1 {
497 execsql {
498 SELECT * FROM t1 WHERE a=1;
499 }
500 } {}
501 do_test intpkey-13.2 {
502 execsql {
503 INSERT INTO t1 VALUES('1.0',2,3);
504 SELECT * FROM t1 WHERE a=1;
505 }
506 } {1 2 3}
507 do_test intpkey-13.3 {
508 catchsql {
509 INSERT INTO t1 VALUES('1.5',3,4);
510 }
511 } {1 {datatype mismatch}}
512 ifcapable {bloblit} {
513 do_test intpkey-13.4 {
514 catchsql {
515 INSERT INTO t1 VALUES(x'123456',3,4);
516 }
517 } {1 {datatype mismatch}}
518 }
519 do_test intpkey-13.5 {
520 catchsql {
521 INSERT INTO t1 VALUES('+1234567890',3,4);
522 }
523 } {0 {}}
524  
525 # Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
526 # affinity should be applied to the text value before the comparison
527 # takes place.
528 #
529 do_test intpkey-14.1 {
530 execsql {
531 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
532 INSERT INTO t3 VALUES(1, 1, 'one');
533 INSERT INTO t3 VALUES(2, 2, '2');
534 INSERT INTO t3 VALUES(3, 3, 3);
535 }
536 } {}
537 do_test intpkey-14.2 {
538 execsql {
539 SELECT * FROM t3 WHERE a>2;
540 }
541 } {3 3 3}
542 do_test intpkey-14.3 {
543 execsql {
544 SELECT * FROM t3 WHERE a>'2';
545 }
546 } {3 3 3}
547 do_test intpkey-14.4 {
548 execsql {
549 SELECT * FROM t3 WHERE a<'2';
550 }
551 } {1 1 one}
552 do_test intpkey-14.5 {
553 execsql {
554 SELECT * FROM t3 WHERE a<c;
555 }
556 } {1 1 one}
557 do_test intpkey-14.6 {
558 execsql {
559 SELECT * FROM t3 WHERE a=c;
560 }
561 } {2 2 2 3 3 3}
562  
563 # Check for proper handling of primary keys greater than 2^31.
564 # Ticket #1188
565 #
566 do_test intpkey-15.1 {
567 execsql {
568 INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
569 SELECT * FROM t1 WHERE a>2147483648;
570 }
571 } {}
572 do_test intpkey-15.2 {
573 execsql {
574 INSERT INTO t1 VALUES(NULL, 'big-2', 234);
575 SELECT b FROM t1 WHERE a>=2147483648;
576 }
577 } {big-2}
578 do_test intpkey-15.3 {
579 execsql {
580 SELECT b FROM t1 WHERE a>2147483648;
581 }
582 } {}
583 do_test intpkey-15.4 {
584 execsql {
585 SELECT b FROM t1 WHERE a>=2147483647;
586 }
587 } {big-1 big-2}
588 do_test intpkey-15.5 {
589 execsql {
590 SELECT b FROM t1 WHERE a<2147483648;
591 }
592 } {y zero 2 hello second hello b-20 b-22 new 3 big-1}
593 do_test intpkey-15.6 {
594 execsql {
595 SELECT b FROM t1 WHERE a<12345678901;
596 }
597 } {y zero 2 hello second hello b-20 b-22 new 3 big-1 big-2}
598 do_test intpkey-15.7 {
599 execsql {
600 SELECT b FROM t1 WHERE a>12345678901;
601 }
602 } {}
603  
604  
605 finish_test