Debian Codegraph @

test/autoinc.test

raw
1 # 2004 November 12
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 script is testing the AUTOINCREMENT features.
13 #
14 # $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set testprefix autoinc
20
21 # If the library is not compiled with autoincrement support then
22 # skip all tests in this file.
23 #
24 ifcapable {!autoinc} {
25 finish_test
26 return
27 }
28
29 if {[permutation]=="inmemory_journal"} {
30 finish_test
31 return
32 }
33
34 sqlite3_db_config_lookaside db 0 0 0
35
36 # The database is initially empty.
37 #
38 do_test autoinc-1.1 {
39 execsql {
40 SELECT name FROM sqlite_master WHERE type='table';
41 }
42 } {}
43
44 # Add a table with the AUTOINCREMENT feature. Verify that the
45 # SQLITE_SEQUENCE table gets created.
46 #
47 do_test autoinc-1.2 {
48 execsql {
49 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
50 SELECT name FROM sqlite_master WHERE type='table';
51 }
52 } {t1 sqlite_sequence}
53
54 # The SQLITE_SEQUENCE table is initially empty
55 #
56 do_test autoinc-1.3 {
57 execsql {
58 SELECT * FROM sqlite_sequence;
59 }
60 } {}
61 do_test autoinc-1.3.1 {
62 catchsql {
63 CREATE INDEX seqidx ON sqlite_sequence(name)
64 }
65 } {1 {table sqlite_sequence may not be indexed}}
66
67 # Close and reopen the database. Verify that everything is still there.
68 #
69 do_test autoinc-1.4 {
70 db close
71 sqlite3 db test.db
72 execsql {
73 SELECT * FROM sqlite_sequence;
74 }
75 } {}
76
77 # We are not allowed to drop the sqlite_sequence table.
78 #
79 do_test autoinc-1.5 {
80 catchsql {DROP TABLE sqlite_sequence}
81 } {1 {table sqlite_sequence may not be dropped}}
82 do_test autoinc-1.6 {
83 execsql {SELECT name FROM sqlite_master WHERE type='table'}
84 } {t1 sqlite_sequence}
85
86 # Insert an entries into the t1 table and make sure the largest key
87 # is always recorded in the sqlite_sequence table.
88 #
89 do_test autoinc-2.1 {
90 execsql {
91 SELECT * FROM sqlite_sequence
92 }
93 } {}
94 do_test autoinc-2.2 {
95 execsql {
96 INSERT INTO t1 VALUES(12,34);
97 SELECT * FROM sqlite_sequence;
98 }
99 } {t1 12}
100 do_test autoinc-2.3 {
101 execsql {
102 INSERT INTO t1 VALUES(1,23);
103 SELECT * FROM sqlite_sequence;
104 }
105 } {t1 12}
106 do_test autoinc-2.4 {
107 execsql {
108 INSERT INTO t1 VALUES(123,456);
109 SELECT * FROM sqlite_sequence;
110 }
111 } {t1 123}
112 do_test autoinc-2.5 {
113 execsql {
114 INSERT INTO t1 VALUES(NULL,567);
115 SELECT * FROM sqlite_sequence;
116 }
117 } {t1 124}
118 do_test autoinc-2.6 {
119 execsql {
120 DELETE FROM t1 WHERE y=567;
121 SELECT * FROM sqlite_sequence;
122 }
123 } {t1 124}
124 do_test autoinc-2.7 {
125 execsql {
126 INSERT INTO t1 VALUES(NULL,567);
127 SELECT * FROM sqlite_sequence;
128 }
129 } {t1 125}
130 do_test autoinc-2.8 {
131 execsql {
132 DELETE FROM t1;
133 SELECT * FROM sqlite_sequence;
134 }
135 } {t1 125}
136 do_test autoinc-2.9 {
137 execsql {
138 INSERT INTO t1 VALUES(12,34);
139 SELECT * FROM sqlite_sequence;
140 }
141 } {t1 125}
142 do_test autoinc-2.10 {
143 execsql {
144 INSERT INTO t1 VALUES(125,456);
145 SELECT * FROM sqlite_sequence;
146 }
147 } {t1 125}
148 do_test autoinc-2.11 {
149 execsql {
150 INSERT INTO t1 VALUES(-1234567,-1);
151 SELECT * FROM sqlite_sequence;
152 }
153 } {t1 125}
154 do_test autoinc-2.12 {
155 execsql {
156 INSERT INTO t1 VALUES(234,5678);
157 SELECT * FROM sqlite_sequence;
158 }
159 } {t1 234}
160 do_test autoinc-2.13 {
161 execsql {
162 DELETE FROM t1;
163 INSERT INTO t1 VALUES(NULL,1);
164 SELECT * FROM sqlite_sequence;
165 }
166 } {t1 235}
167 do_test autoinc-2.14 {
168 execsql {
169 SELECT * FROM t1;
170 }
171 } {235 1}
172
173 # Manually change the autoincrement values in sqlite_sequence.
174 #
175 do_test autoinc-2.20 {
176 execsql {
177 UPDATE sqlite_sequence SET seq=1234 WHERE name='t1';
178 INSERT INTO t1 VALUES(NULL,2);
179 SELECT * FROM t1;
180 }
181 } {235 1 1235 2}
182 do_test autoinc-2.21 {
183 execsql {
184 SELECT * FROM sqlite_sequence;
185 }
186 } {t1 1235}
187 do_test autoinc-2.22 {
188 execsql {
189 UPDATE sqlite_sequence SET seq=NULL WHERE name='t1';
190 INSERT INTO t1 VALUES(NULL,3);
191 SELECT * FROM t1;
192 }
193 } {235 1 1235 2 1236 3}
194 do_test autoinc-2.23 {
195 execsql {
196 SELECT * FROM sqlite_sequence;
197 }
198 } {t1 1236}
199 do_test autoinc-2.24 {
200 execsql {
201 UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1';
202 INSERT INTO t1 VALUES(NULL,4);
203 SELECT * FROM t1;
204 }
205 } {235 1 1235 2 1236 3 1237 4}
206 do_test autoinc-2.25 {
207 execsql {
208 SELECT * FROM sqlite_sequence;
209 }
210 } {t1 1237}
211 do_test autoinc-2.26 {
212 execsql {
213 DELETE FROM sqlite_sequence WHERE name='t1';
214 INSERT INTO t1 VALUES(NULL,5);
215 SELECT * FROM t1;
216 }
217 } {235 1 1235 2 1236 3 1237 4 1238 5}
218 do_test autoinc-2.27 {
219 execsql {
220 SELECT * FROM sqlite_sequence;
221 }
222 } {t1 1238}
223 do_test autoinc-2.28 {
224 execsql {
225 UPDATE sqlite_sequence SET seq='-12345678901234567890'
226 WHERE name='t1';
227 INSERT INTO t1 VALUES(NULL,6);
228 SELECT * FROM t1;
229 }
230 } {235 1 1235 2 1236 3 1237 4 1238 5 1239 6}
231 do_test autoinc-2.29 {
232 execsql {
233 SELECT * FROM sqlite_sequence;
234 }
235 } {t1 1239}
236
237 # Test multi-row inserts
238 #
239 do_test autoinc-2.50 {
240 execsql {
241 DELETE FROM t1 WHERE y>=3;
242 INSERT INTO t1 SELECT NULL, y+2 FROM t1;
243 SELECT * FROM t1;
244 }
245 } {235 1 1235 2 1240 3 1241 4}
246 do_test autoinc-2.51 {
247 execsql {
248 SELECT * FROM sqlite_sequence
249 }
250 } {t1 1241}
251
252 ifcapable tempdb {
253 do_test autoinc-2.52 {
254 execsql {
255 CREATE TEMP TABLE t2 AS SELECT y FROM t1;
256 }
257 execsql {
258 INSERT INTO t1 SELECT NULL, y+4 FROM t2;
259 SELECT * FROM t1;
260 }
261 } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8}
262 do_test autoinc-2.53 {
263 execsql {
264 SELECT * FROM sqlite_sequence
265 }
266 } {t1 1245}
267 do_test autoinc-2.54 {
268 execsql {
269 DELETE FROM t1;
270 INSERT INTO t1 SELECT NULL, y FROM t2;
271 SELECT * FROM t1;
272 }
273 } {1246 1 1247 2 1248 3 1249 4}
274 do_test autoinc-2.55 {
275 execsql {
276 SELECT * FROM sqlite_sequence
277 }
278 } {t1 1249}
279 }
280
281 # Create multiple AUTOINCREMENT tables. Make sure all sequences are
282 # tracked separately and do not interfere with one another.
283 #
284 do_test autoinc-2.70 {
285 catchsql {
286 DROP TABLE t2;
287 }
288 execsql {
289 CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f);
290 INSERT INTO t2(d) VALUES(1);
291 SELECT * FROM sqlite_sequence;
292 }
293 } [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}]
294 do_test autoinc-2.71 {
295 execsql {
296 INSERT INTO t2(d) VALUES(2);
297 SELECT * FROM sqlite_sequence;
298 }
299 } [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}]
300 do_test autoinc-2.72 {
301 execsql {
302 INSERT INTO t1(x) VALUES(10000);
303 SELECT * FROM sqlite_sequence;
304 }
305 } {t1 10000 t2 2}
306 do_test autoinc-2.73 {
307 execsql {
308 CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h);
309 INSERT INTO t3(h) VALUES(1);
310 SELECT * FROM sqlite_sequence;
311 }
312 } {t1 10000 t2 2 t3 1}
313 do_test autoinc-2.74 {
314 execsql {
315 INSERT INTO t2(d,e) VALUES(3,100);
316 SELECT * FROM sqlite_sequence;
317 }
318 } {t1 10000 t2 100 t3 1}
319
320
321 # When a table with an AUTOINCREMENT is deleted, the corresponding entry
322 # in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE
323 # table itself should remain behind.
324 #
325 do_test autoinc-3.1 {
326 execsql {SELECT name FROM sqlite_sequence}
327 } {t1 t2 t3}
328 do_test autoinc-3.2 {
329 execsql {
330 DROP TABLE t1;
331 SELECT name FROM sqlite_sequence;
332 }
333 } {t2 t3}
334 do_test autoinc-3.3 {
335 execsql {
336 DROP TABLE t3;
337 SELECT name FROM sqlite_sequence;
338 }
339 } {t2}
340 do_test autoinc-3.4 {
341 execsql {
342 DROP TABLE t2;
343 SELECT name FROM sqlite_sequence;
344 }
345 } {}
346
347 # AUTOINCREMENT on TEMP tables.
348 #
349 ifcapable tempdb {
350 do_test autoinc-4.1 {
351 execsql {
352 SELECT 1, name FROM sqlite_master WHERE type='table';
353 SELECT 2, name FROM temp.sqlite_master WHERE type='table';
354 }
355 } {1 sqlite_sequence}
356 do_test autoinc-4.2 {
357 execsql {
358 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
359 CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
360 SELECT 1, name FROM sqlite_master WHERE type='table';
361 SELECT 2, name FROM sqlite_temp_master WHERE type='table';
362 }
363 } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}
364 do_test autoinc-4.3 {
365 execsql {
366 SELECT 1, * FROM main.sqlite_sequence;
367 SELECT 2, * FROM temp.sqlite_sequence;
368 }
369 } {}
370 do_test autoinc-4.4 {
371 execsql {
372 INSERT INTO t1 VALUES(10,1);
373 INSERT INTO t3 VALUES(20,2);
374 INSERT INTO t1 VALUES(NULL,3);
375 INSERT INTO t3 VALUES(NULL,4);
376 }
377 } {}
378
379 ifcapable compound {
380 do_test autoinc-4.4.1 {
381 execsql {
382 SELECT * FROM t1 UNION ALL SELECT * FROM t3;
383 }
384 } {10 1 11 3 20 2 21 4}
385 } ;# ifcapable compound
386
387 do_test autoinc-4.5 {
388 execsql {
389 SELECT 1, * FROM main.sqlite_sequence;
390 SELECT 2, * FROM temp.sqlite_sequence;
391 }
392 } {1 t1 11 2 t3 21}
393 do_test autoinc-4.6 {
394 execsql {
395 INSERT INTO t1 SELECT * FROM t3;
396 SELECT 1, * FROM main.sqlite_sequence;
397 SELECT 2, * FROM temp.sqlite_sequence;
398 }
399 } {1 t1 21 2 t3 21}
400 do_test autoinc-4.7 {
401 execsql {
402 INSERT INTO t3 SELECT x+100, y FROM t1;
403 SELECT 1, * FROM main.sqlite_sequence;
404 SELECT 2, * FROM temp.sqlite_sequence;
405 }
406 } {1 t1 21 2 t3 121}
407 do_test autoinc-4.8 {
408 execsql {
409 DROP TABLE t3;
410 SELECT 1, * FROM main.sqlite_sequence;
411 SELECT 2, * FROM temp.sqlite_sequence;
412 }
413 } {1 t1 21}
414 do_test autoinc-4.9 {
415 execsql {
416 CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);
417 INSERT INTO t2 SELECT * FROM t1;
418 DROP TABLE t1;
419 SELECT 1, * FROM main.sqlite_sequence;
420 SELECT 2, * FROM temp.sqlite_sequence;
421 }
422 } {2 t2 21}
423 do_test autoinc-4.10 {
424 execsql {
425 DROP TABLE t2;
426 SELECT 1, * FROM main.sqlite_sequence;
427 SELECT 2, * FROM temp.sqlite_sequence;
428 }
429 } {}
430 }
431
432 # Make sure AUTOINCREMENT works on ATTACH-ed tables.
433 #
434 ifcapable tempdb&&attach {
435 do_test autoinc-5.1 {
436 forcedelete test2.db
437 forcedelete test2.db-journal
438 sqlite3 db2 test2.db
439 execsql {
440 CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
441 CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);
442 } db2;
443 execsql {
444 ATTACH 'test2.db' as aux;
445 SELECT 1, * FROM main.sqlite_sequence;
446 SELECT 2, * FROM temp.sqlite_sequence;
447 SELECT 3, * FROM aux.sqlite_sequence;
448 }
449 } {}
450 do_test autoinc-5.2 {
451 execsql {
452 INSERT INTO t4 VALUES(NULL,1);
453 SELECT 1, * FROM main.sqlite_sequence;
454 SELECT 2, * FROM temp.sqlite_sequence;
455 SELECT 3, * FROM aux.sqlite_sequence;
456 }
457 } {3 t4 1}
458 do_test autoinc-5.3 {
459 execsql {
460 INSERT INTO t5 VALUES(100,200);
461 SELECT * FROM sqlite_sequence
462 } db2
463 } {t4 1 t5 200}
464 do_test autoinc-5.4 {
465 execsql {
466 SELECT 1, * FROM main.sqlite_sequence;
467 SELECT 2, * FROM temp.sqlite_sequence;
468 SELECT 3, * FROM aux.sqlite_sequence;
469 }
470 } {3 t4 1 3 t5 200}
471 }
472
473 # Requirement REQ00310: Make sure an insert fails if the sequence is
474 # already at its maximum value.
475 #
476 ifcapable {rowid32} {
477 do_test autoinc-6.1 {
478 execsql {
479 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
480 INSERT INTO t6 VALUES(2147483647,1);
481 SELECT seq FROM main.sqlite_sequence WHERE name='t6';
482 }
483 } 2147483647
484 }
485 ifcapable {!rowid32} {
486 do_test autoinc-6.1 {
487 execsql {
488 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
489 INSERT INTO t6 VALUES(9223372036854775807,1);
490 SELECT seq FROM main.sqlite_sequence WHERE name='t6';
491 }
492 } 9223372036854775807
493 }
494 do_test autoinc-6.2 {
495 catchsql {
496 INSERT INTO t6 VALUES(NULL,1);
497 }
498 } {1 {database or disk is full}}
499
500 # Allow the AUTOINCREMENT keyword inside the parentheses
501 # on a separate PRIMARY KEY designation.
502 #
503 do_test autoinc-7.1 {
504 execsql {
505 CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));
506 INSERT INTO t7(y) VALUES(123);
507 INSERT INTO t7(y) VALUES(234);
508 DELETE FROM t7;
509 INSERT INTO t7(y) VALUES(345);
510 SELECT * FROM t7;
511 }
512 } {3 345.0}
513
514 # Test that if the AUTOINCREMENT is applied to a non integer primary key
515 # the error message is sensible.
516 do_test autoinc-7.2 {
517 catchsql {
518 CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);
519 }
520 } {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}}
521
522
523 # Ticket #1283. Make sure that preparing but never running a statement
524 # that creates the sqlite_sequence table does not mess up the database.
525 #
526 do_test autoinc-8.1 {
527 catch {db2 close}
528 catch {db close}
529 forcedelete test.db
530 sqlite3 db test.db
531 set DB [sqlite3_connection_pointer db]
532 set STMT [sqlite3_prepare $DB {
533 CREATE TABLE t1(
534 x INTEGER PRIMARY KEY AUTOINCREMENT
535 )
536 } -1 TAIL]
537 sqlite3_finalize $STMT
538 set STMT [sqlite3_prepare $DB {
539 CREATE TABLE t1(
540 x INTEGER PRIMARY KEY AUTOINCREMENT
541 )
542 } -1 TAIL]
543 sqlite3_step $STMT
544 sqlite3_finalize $STMT
545 execsql {
546 INSERT INTO t1 VALUES(NULL);
547 SELECT * FROM t1;
548 }
549 } {1}
550
551 # Ticket #3148
552 # Make sure the sqlite_sequence table is not damaged when doing
553 # an empty insert - an INSERT INTO ... SELECT ... where the SELECT
554 # clause returns an empty set.
555 #
556 do_test autoinc-9.1 {
557 db eval {
558 CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
559 INSERT INTO t2 VALUES(NULL, 1);
560 CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
561 INSERT INTO t3 SELECT * FROM t2 WHERE y>1;
562
563 SELECT * FROM sqlite_sequence WHERE name='t3';
564 }
565 } {t3 0}
566
567 ifcapable trigger {
568 catchsql { pragma recursive_triggers = off }
569
570 # Ticket #3928. Make sure that triggers to not make extra slots in
571 # the SQLITE_SEQUENCE table.
572 #
573 do_test autoinc-3928.1 {
574 db eval {
575 CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
576 CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN
577 INSERT INTO t3928(b) VALUES('before1');
578 INSERT INTO t3928(b) VALUES('before2');
579 END;
580 CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN
581 INSERT INTO t3928(b) VALUES('after1');
582 INSERT INTO t3928(b) VALUES('after2');
583 END;
584 INSERT INTO t3928(b) VALUES('test');
585 SELECT * FROM t3928 ORDER BY a;
586 }
587 } {1 before1 2 after1 3 after2 4 before2 5 after1 6 after2 7 test 8 before1 9 before2 10 after1 11 before1 12 before2 13 after2}
588 do_test autoinc-3928.2 {
589 db eval {
590 SELECT * FROM sqlite_sequence WHERE name='t3928'
591 }
592 } {t3928 13}
593
594 do_test autoinc-3928.3 {
595 db eval {
596 DROP TRIGGER t3928r1;
597 DROP TRIGGER t3928r2;
598 CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928
599 WHEN typeof(new.b)=='integer' BEGIN
600 INSERT INTO t3928(b) VALUES('before-int-' || new.b);
601 END;
602 CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928
603 WHEN typeof(new.b)=='integer' BEGIN
604 INSERT INTO t3928(b) VALUES('after-int-' || new.b);
605 END;
606 DELETE FROM t3928 WHERE a!=1;
607 UPDATE t3928 SET b=456 WHERE a=1;
608 SELECT * FROM t3928 ORDER BY a;
609 }
610 } {1 456 14 before-int-456 15 after-int-456}
611 do_test autoinc-3928.4 {
612 db eval {
613 SELECT * FROM sqlite_sequence WHERE name='t3928'
614 }
615 } {t3928 15}
616
617 do_test autoinc-3928.5 {
618 db eval {
619 CREATE TABLE t3928b(x);
620 INSERT INTO t3928b VALUES(100);
621 INSERT INTO t3928b VALUES(200);
622 INSERT INTO t3928b VALUES(300);
623 DELETE FROM t3928;
624 CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z);
625 CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN
626 INSERT INTO t3928(b) VALUES('before-del-'||old.x);
627 INSERT INTO t3928c(z) VALUES('before-del-'||old.x);
628 END;
629 CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN
630 INSERT INTO t3928(b) VALUES('after-del-'||old.x);
631 INSERT INTO t3928c(z) VALUES('after-del-'||old.x);
632 END;
633 DELETE FROM t3928b;
634 SELECT * FROM t3928 ORDER BY a;
635 }
636 } {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 before-del-300 21 after-del-300}
637 do_test autoinc-3928.6 {
638 db eval {
639 SELECT * FROM t3928c ORDER BY y;
640 }
641 } {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-del-300 6 after-del-300}
642 do_test autoinc-3928.7 {
643 db eval {
644 SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name;
645 }
646 } {t3928 21 t3928c 6}
647
648 # Ticket [a696379c1f0886615541a48b35bd8181a80e88f8]
649 do_test autoinc-a69637.1 {
650 db eval {
651 CREATE TABLE ta69637_1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
652 CREATE TABLE ta69637_2(z);
653 CREATE TRIGGER ra69637_1 AFTER INSERT ON ta69637_2 BEGIN
654 INSERT INTO ta69637_1(y) VALUES(new.z+1);
655 END;
656 INSERT INTO ta69637_2 VALUES(123);
657 SELECT * FROM ta69637_1;
658 }
659 } {1 124}
660 do_test autoinc-a69637.2 {
661 db eval {
662 CREATE VIEW va69637_2 AS SELECT * FROM ta69637_2;
663 CREATE TRIGGER ra69637_2 INSTEAD OF INSERT ON va69637_2 BEGIN
664 INSERT INTO ta69637_1(y) VALUES(new.z+10000);
665 END;
666 INSERT INTO va69637_2 VALUES(123);
667 SELECT * FROM ta69637_1;
668 }
669 } {1 124 2 10123}
670 }
671
672 # 2016-10-03 ticket https://sqlite.org/src/tktview/7b3328086a5c1
673 # Make sure autoincrement plays nicely with the xfer optimization
674 #
675 do_execsql_test autoinc-10.1 {
676 DELETE FROM sqlite_sequence;
677 CREATE TABLE t10a(a INTEGER PRIMARY KEY AUTOINCREMENT, b UNIQUE);
678 INSERT INTO t10a VALUES(888,9999);
679 CREATE TABLE t10b(x INTEGER PRIMARY KEY AUTOINCREMENT, y UNIQUE);
680 INSERT INTO t10b SELECT * FROM t10a;
681 SELECT * FROM sqlite_sequence;
682 } {t10a 888 t10b 888}
683
684 # 2018-04-21 autoincrement does not cause problems for upsert
685 #
686 do_execsql_test autoinc-11.1 {
687 CREATE TABLE t11(a INTEGER PRIMARY KEY AUTOINCREMENT,b UNIQUE);
688 INSERT INTO t11(a,b) VALUES(2,3),(5,6),(4,3),(1,2)
689 ON CONFLICT(b) DO UPDATE SET a=a+1000;
690 SELECT seq FROM sqlite_sequence WHERE name='t11';
691 } {5}
692
693 # 2018-05-23 ticket d8dc2b3a58cd5dc2918a1d4acbba4676a23ada4c
694 # Does not crash if the sqlite_sequence table schema is missing
695 # or corrupt.
696 #
697 do_test autoinc-12.1 {
698 db close
699 forcedelete test.db
700 sqlite3 db test.db
701 sqlite3_db_config db DEFENSIVE 0
702 db eval {
703 CREATE TABLE fake_sequence(name TEXT PRIMARY KEY,seq) WITHOUT ROWID;
704 PRAGMA writable_schema=on;
705 UPDATE sqlite_master SET
706 sql=replace(sql,'fake_','sqlite_'),
707 name='sqlite_sequence',
708 tbl_name='sqlite_sequence'
709 WHERE name='fake_sequence';
710 }
711 db close
712 sqlite3 db test.db
713 set res [catch {db eval {
714 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
715 INSERT INTO t1(b) VALUES('one');
716 }} msg]
717 lappend res $msg
718 } {1 {database disk image is malformed}}
719 do_test autoinc-12.2 {
720 db close
721 forcedelete test.db
722 sqlite3 db test.db
723 sqlite3_db_config db DEFENSIVE 0
724 db eval {
725 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
726 INSERT INTO t1(b) VALUES('one');
727 PRAGMA writable_schema=on;
728 UPDATE sqlite_master SET
729 sql=replace(sql,'sqlite_','x_'),
730 name='x_sequence',
731 tbl_name='x_sequence'
732 WHERE name='sqlite_sequence';
733 }
734 db close
735 sqlite3 db test.db
736 set res [catch {db eval {
737 INSERT INTO t1(b) VALUES('two');
738 }} msg]
739 lappend res $msg
740 } {1 {database disk image is malformed}}
741 ifcapable vtab {
742 set err "database disk image is malformed"
743 } else {
744 set err {malformed database schema (sqlite_sequence) - near "VIRTUAL": syntax error}
745 }
746 do_test autoinc-12.3 {
747 db close
748 forcedelete test.db
749 sqlite3 db test.db
750 sqlite3_db_config db DEFENSIVE 0
751 db eval {
752 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
753 INSERT INTO t1(b) VALUES('one');
754 PRAGMA writable_schema=on;
755 UPDATE sqlite_master SET
756 sql='CREATE VIRTUAL TABLE sqlite_sequence USING sqlite_dbpage'
757 WHERE name='sqlite_sequence';
758 }
759 db close
760 sqlite3 db test.db
761 set res [catch {db eval {
762 INSERT INTO t1(b) VALUES('two');
763 }} msg]
764 lappend res $msg
765 } [list 1 $err]
766 do_test autoinc-12.4 {
767 db close
768 forcedelete test.db
769 sqlite3 db test.db
770 db eval {
771 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
772 INSERT INTO t1(b) VALUES('one');
773 CREATE TABLE fake(name TEXT PRIMARY KEY,seq) WITHOUT ROWID;
774 }
775 set root1 [db one {SELECT rootpage FROM sqlite_master
776 WHERE name='sqlite_sequence'}]
777 set root2 [db one {SELECT rootpage FROM sqlite_master
778 WHERE name='fake'}]
779 sqlite3_db_config db DEFENSIVE 0
780 db eval {
781 PRAGMA writable_schema=on;
782 UPDATE sqlite_master SET rootpage=$root2
783 WHERE name='sqlite_sequence';
784 UPDATE sqlite_master SET rootpage=$root1
785 WHERE name='fake';
786 }
787 db close
788 sqlite3 db test.db
789 set res [catch {db eval {
790 INSERT INTO t1(b) VALUES('two');
791 }} msg]
792 lappend res $msg
793 } {1 {database disk image is malformed}}
794 breakpoint
795 do_test autoinc-12.5 {
796 db close
797 forcedelete test.db
798 sqlite3 db test.db
799 sqlite3_db_config db DEFENSIVE 0
800 db eval {
801 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
802 INSERT INTO t1(b) VALUES('one');
803 PRAGMA writable_schema=on;
804 UPDATE sqlite_master SET
805 sql='CREATE TABLE sqlite_sequence(x)'
806 WHERE name='sqlite_sequence';
807 }
808 db close
809 sqlite3 db test.db
810 set res [catch {db eval {
811 INSERT INTO t1(b) VALUES('two');
812 }} msg]
813 lappend res $msg
814 } {1 {database disk image is malformed}}
815 do_test autoinc-12.6 {
816 db close
817 forcedelete test.db
818 sqlite3 db test.db
819 sqlite3_db_config db DEFENSIVE 0
820 db eval {
821 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
822 INSERT INTO t1(b) VALUES('one');
823 PRAGMA writable_schema=on;
824 UPDATE sqlite_master SET
825 sql='CREATE TABLE sqlite_sequence(x,y INTEGER PRIMARY KEY)'
826 WHERE name='sqlite_sequence';
827 }
828 db close
829 sqlite3 db test.db
830 set res [catch {db eval {
831 INSERT INTO t1(b) VALUES('two'),('three'),('four');
832 INSERT INTO t1(b) VALUES('five');
833 PRAGMA integrity_check;
834 }} msg]
835 lappend res $msg
836 } {0 ok}
837 do_test autoinc-12.7 {
838 db close
839 forcedelete test.db
840 sqlite3 db test.db
841 sqlite3_db_config db DEFENSIVE 0
842 db eval {
843 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
844 INSERT INTO t1(b) VALUES('one');
845 PRAGMA writable_schema=on;
846 UPDATE sqlite_master SET
847 sql='CREATE TABLE sqlite_sequence(y INTEGER PRIMARY KEY,x)'
848 WHERE name='sqlite_sequence';
849 }
850 db close
851 sqlite3 db test.db
852 set res [catch {db eval {
853 INSERT INTO t1(b) VALUES('two'),('three'),('four');
854 INSERT INTO t1(b) VALUES('five');
855 PRAGMA integrity_check;
856 }} msg]
857 lappend res $msg
858 } {0 ok}
859
860 #--------------------------------------------------------------------------
861 reset_db
862 do_execsql_test 13.0 {
863 CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
864 CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
865 CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
866
867 INSERT INTO t1 VALUES(NULL, 1);
868 INSERT INTO t2 VALUES(NULL, 2);
869 INSERT INTO t3 VALUES(NULL, 3);
870
871 SELECT name FROM sqlite_sequence;
872 } {t1 t2 t3}
873
874 do_execsql_test 13.1 {
875 UPDATE sqlite_sequence SET name=NULL WHERE name='t2';
876 INSERT INTO t3 VALUES(NULL, 4);
877 DELETE FROM t3;
878 INSERT INTO t3 VALUES(NULL, 5);
879 SELECT * FROM t3;
880 } {3 5}
881
882
883 finish_test