| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| 20 # If the library is not compiled with autoincrement support then | |
| 21 # skip all tests in this file. | |
| 22 # | |
| 23 ifcapable {!autoinc} { | |
| 24 finish_test | |
| 25 return | |
| 26 } | |
| 27 | |
| 28 sqlite3_db_config_lookaside db 0 0 0 | |
| 29 | |
| 30 # The database is initially empty. | |
| 31 # | |
| 32 do_test autoinc-1.1 { | |
| 33 execsql { | |
| 34 SELECT name FROM sqlite_master WHERE type='table'; | |
| 35 } | |
| 36 } {} | |
| 37 | |
| 38 # Add a table with the AUTOINCREMENT feature. Verify that the | |
| 39 # SQLITE_SEQUENCE table gets created. | |
| 40 # | |
| 41 do_test autoinc-1.2 { | |
| 42 execsql { | |
| 43 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); | |
| 44 SELECT name FROM sqlite_master WHERE type='table'; | |
| 45 } | |
| 46 } {t1 sqlite_sequence} | |
| 47 | |
| 48 # The SQLITE_SEQUENCE table is initially empty | |
| 49 # | |
| 50 do_test autoinc-1.3 { | |
| 51 execsql { | |
| 52 SELECT * FROM sqlite_sequence; | |
| 53 } | |
| 54 } {} | |
| 55 do_test autoinc-1.3.1 { | |
| 56 catchsql { | |
| 57 CREATE INDEX seqidx ON sqlite_sequence(name) | |
| 58 } | |
| 59 } {1 {table sqlite_sequence may not be indexed}} | |
| 60 | |
| 61 # Close and reopen the database. Verify that everything is still there. | |
| 62 # | |
| 63 do_test autoinc-1.4 { | |
| 64 db close | |
| 65 sqlite3 db test.db | |
| 66 execsql { | |
| 67 SELECT * FROM sqlite_sequence; | |
| 68 } | |
| 69 } {} | |
| 70 | |
| 71 # We are not allowed to drop the sqlite_sequence table. | |
| 72 # | |
| 73 do_test autoinc-1.5 { | |
| 74 catchsql {DROP TABLE sqlite_sequence} | |
| 75 } {1 {table sqlite_sequence may not be dropped}} | |
| 76 do_test autoinc-1.6 { | |
| 77 execsql {SELECT name FROM sqlite_master WHERE type='table'} | |
| 78 } {t1 sqlite_sequence} | |
| 79 | |
| 80 # Insert an entries into the t1 table and make sure the largest key | |
| 81 # is always recorded in the sqlite_sequence table. | |
| 82 # | |
| 83 do_test autoinc-2.1 { | |
| 84 execsql { | |
| 85 SELECT * FROM sqlite_sequence | |
| 86 } | |
| 87 } {} | |
| 88 do_test autoinc-2.2 { | |
| 89 execsql { | |
| 90 INSERT INTO t1 VALUES(12,34); | |
| 91 SELECT * FROM sqlite_sequence; | |
| 92 } | |
| 93 } {t1 12} | |
| 94 do_test autoinc-2.3 { | |
| 95 execsql { | |
| 96 INSERT INTO t1 VALUES(1,23); | |
| 97 SELECT * FROM sqlite_sequence; | |
| 98 } | |
| 99 } {t1 12} | |
| 100 do_test autoinc-2.4 { | |
| 101 execsql { | |
| 102 INSERT INTO t1 VALUES(123,456); | |
| 103 SELECT * FROM sqlite_sequence; | |
| 104 } | |
| 105 } {t1 123} | |
| 106 do_test autoinc-2.5 { | |
| 107 execsql { | |
| 108 INSERT INTO t1 VALUES(NULL,567); | |
| 109 SELECT * FROM sqlite_sequence; | |
| 110 } | |
| 111 } {t1 124} | |
| 112 do_test autoinc-2.6 { | |
| 113 execsql { | |
| 114 DELETE FROM t1 WHERE y=567; | |
| 115 SELECT * FROM sqlite_sequence; | |
| 116 } | |
| 117 } {t1 124} | |
| 118 do_test autoinc-2.7 { | |
| 119 execsql { | |
| 120 INSERT INTO t1 VALUES(NULL,567); | |
| 121 SELECT * FROM sqlite_sequence; | |
| 122 } | |
| 123 } {t1 125} | |
| 124 do_test autoinc-2.8 { | |
| 125 execsql { | |
| 126 DELETE FROM t1; | |
| 127 SELECT * FROM sqlite_sequence; | |
| 128 } | |
| 129 } {t1 125} | |
| 130 do_test autoinc-2.9 { | |
| 131 execsql { | |
| 132 INSERT INTO t1 VALUES(12,34); | |
| 133 SELECT * FROM sqlite_sequence; | |
| 134 } | |
| 135 } {t1 125} | |
| 136 do_test autoinc-2.10 { | |
| 137 execsql { | |
| 138 INSERT INTO t1 VALUES(125,456); | |
| 139 SELECT * FROM sqlite_sequence; | |
| 140 } | |
| 141 } {t1 125} | |
| 142 do_test autoinc-2.11 { | |
| 143 execsql { | |
| 144 INSERT INTO t1 VALUES(-1234567,-1); | |
| 145 SELECT * FROM sqlite_sequence; | |
| 146 } | |
| 147 } {t1 125} | |
| 148 do_test autoinc-2.12 { | |
| 149 execsql { | |
| 150 INSERT INTO t1 VALUES(234,5678); | |
| 151 SELECT * FROM sqlite_sequence; | |
| 152 } | |
| 153 } {t1 234} | |
| 154 do_test autoinc-2.13 { | |
| 155 execsql { | |
| 156 DELETE FROM t1; | |
| 157 INSERT INTO t1 VALUES(NULL,1); | |
| 158 SELECT * FROM sqlite_sequence; | |
| 159 } | |
| 160 } {t1 235} | |
| 161 do_test autoinc-2.14 { | |
| 162 execsql { | |
| 163 SELECT * FROM t1; | |
| 164 } | |
| 165 } {235 1} | |
| 166 | |
| 167 # Manually change the autoincrement values in sqlite_sequence. | |
| 168 # | |
| 169 do_test autoinc-2.20 { | |
| 170 execsql { | |
| 171 UPDATE sqlite_sequence SET seq=1234 WHERE name='t1'; | |
| 172 INSERT INTO t1 VALUES(NULL,2); | |
| 173 SELECT * FROM t1; | |
| 174 } | |
| 175 } {235 1 1235 2} | |
| 176 do_test autoinc-2.21 { | |
| 177 execsql { | |
| 178 SELECT * FROM sqlite_sequence; | |
| 179 } | |
| 180 } {t1 1235} | |
| 181 do_test autoinc-2.22 { | |
| 182 execsql { | |
| 183 UPDATE sqlite_sequence SET seq=NULL WHERE name='t1'; | |
| 184 INSERT INTO t1 VALUES(NULL,3); | |
| 185 SELECT * FROM t1; | |
| 186 } | |
| 187 } {235 1 1235 2 1236 3} | |
| 188 do_test autoinc-2.23 { | |
| 189 execsql { | |
| 190 SELECT * FROM sqlite_sequence; | |
| 191 } | |
| 192 } {t1 1236} | |
| 193 do_test autoinc-2.24 { | |
| 194 execsql { | |
| 195 UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1'; | |
| 196 INSERT INTO t1 VALUES(NULL,4); | |
| 197 SELECT * FROM t1; | |
| 198 } | |
| 199 } {235 1 1235 2 1236 3 1237 4} | |
| 200 do_test autoinc-2.25 { | |
| 201 execsql { | |
| 202 SELECT * FROM sqlite_sequence; | |
| 203 } | |
| 204 } {t1 1237} | |
| 205 do_test autoinc-2.26 { | |
| 206 execsql { | |
| 207 DELETE FROM sqlite_sequence WHERE name='t1'; | |
| 208 INSERT INTO t1 VALUES(NULL,5); | |
| 209 SELECT * FROM t1; | |
| 210 } | |
| 211 } {235 1 1235 2 1236 3 1237 4 1238 5} | |
| 212 do_test autoinc-2.27 { | |
| 213 execsql { | |
| 214 SELECT * FROM sqlite_sequence; | |
| 215 } | |
| 216 } {t1 1238} | |
| 217 do_test autoinc-2.28 { | |
| 218 execsql { | |
| 219 UPDATE sqlite_sequence SET seq='-12345678901234567890' | |
| 220 WHERE name='t1'; | |
| 221 INSERT INTO t1 VALUES(NULL,6); | |
| 222 SELECT * FROM t1; | |
| 223 } | |
| 224 } {235 1 1235 2 1236 3 1237 4 1238 5 1239 6} | |
| 225 do_test autoinc-2.29 { | |
| 226 execsql { | |
| 227 SELECT * FROM sqlite_sequence; | |
| 228 } | |
| 229 } {t1 1239} | |
| 230 | |
| 231 # Test multi-row inserts | |
| 232 # | |
| 233 do_test autoinc-2.50 { | |
| 234 execsql { | |
| 235 DELETE FROM t1 WHERE y>=3; | |
| 236 INSERT INTO t1 SELECT NULL, y+2 FROM t1; | |
| 237 SELECT * FROM t1; | |
| 238 } | |
| 239 } {235 1 1235 2 1240 3 1241 4} | |
| 240 do_test autoinc-2.51 { | |
| 241 execsql { | |
| 242 SELECT * FROM sqlite_sequence | |
| 243 } | |
| 244 } {t1 1241} | |
| 245 | |
| 246 ifcapable tempdb { | |
| 247 do_test autoinc-2.52 { | |
| 248 execsql { | |
| 249 CREATE TEMP TABLE t2 AS SELECT y FROM t1; | |
| 250 } | |
| 251 execsql { | |
| 252 INSERT INTO t1 SELECT NULL, y+4 FROM t2; | |
| 253 SELECT * FROM t1; | |
| 254 } | |
| 255 } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8} | |
| 256 do_test autoinc-2.53 { | |
| 257 execsql { | |
| 258 SELECT * FROM sqlite_sequence | |
| 259 } | |
| 260 } {t1 1245} | |
| 261 do_test autoinc-2.54 { | |
| 262 execsql { | |
| 263 DELETE FROM t1; | |
| 264 INSERT INTO t1 SELECT NULL, y FROM t2; | |
| 265 SELECT * FROM t1; | |
| 266 } | |
| 267 } {1246 1 1247 2 1248 3 1249 4} | |
| 268 do_test autoinc-2.55 { | |
| 269 execsql { | |
| 270 SELECT * FROM sqlite_sequence | |
| 271 } | |
| 272 } {t1 1249} | |
| 273 } | |
| 274 | |
| 275 # Create multiple AUTOINCREMENT tables. Make sure all sequences are | |
| 276 # tracked separately and do not interfere with one another. | |
| 277 # | |
| 278 do_test autoinc-2.70 { | |
| 279 catchsql { | |
| 280 DROP TABLE t2; | |
| 281 } | |
| 282 execsql { | |
| 283 CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f); | |
| 284 INSERT INTO t2(d) VALUES(1); | |
| 285 SELECT * FROM sqlite_sequence; | |
| 286 } | |
| 287 } [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}] | |
| 288 do_test autoinc-2.71 { | |
| 289 execsql { | |
| 290 INSERT INTO t2(d) VALUES(2); | |
| 291 SELECT * FROM sqlite_sequence; | |
| 292 } | |
| 293 } [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}] | |
| 294 do_test autoinc-2.72 { | |
| 295 execsql { | |
| 296 INSERT INTO t1(x) VALUES(10000); | |
| 297 SELECT * FROM sqlite_sequence; | |
| 298 } | |
| 299 } {t1 10000 t2 2} | |
| 300 do_test autoinc-2.73 { | |
| 301 execsql { | |
| 302 CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h); | |
| 303 INSERT INTO t3(h) VALUES(1); | |
| 304 SELECT * FROM sqlite_sequence; | |
| 305 } | |
| 306 } {t1 10000 t2 2 t3 1} | |
| 307 do_test autoinc-2.74 { | |
| 308 execsql { | |
| 309 INSERT INTO t2(d,e) VALUES(3,100); | |
| 310 SELECT * FROM sqlite_sequence; | |
| 311 } | |
| 312 } {t1 10000 t2 100 t3 1} | |
| 313 | |
| 314 | |
| 315 # When a table with an AUTOINCREMENT is deleted, the corresponding entry | |
| 316 # in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE | |
| 317 # table itself should remain behind. | |
| 318 # | |
| 319 do_test autoinc-3.1 { | |
| 320 execsql {SELECT name FROM sqlite_sequence} | |
| 321 } {t1 t2 t3} | |
| 322 do_test autoinc-3.2 { | |
| 323 execsql { | |
| 324 DROP TABLE t1; | |
| 325 SELECT name FROM sqlite_sequence; | |
| 326 } | |
| 327 } {t2 t3} | |
| 328 do_test autoinc-3.3 { | |
| 329 execsql { | |
| 330 DROP TABLE t3; | |
| 331 SELECT name FROM sqlite_sequence; | |
| 332 } | |
| 333 } {t2} | |
| 334 do_test autoinc-3.4 { | |
| 335 execsql { | |
| 336 DROP TABLE t2; | |
| 337 SELECT name FROM sqlite_sequence; | |
| 338 } | |
| 339 } {} | |
| 340 | |
| 341 # AUTOINCREMENT on TEMP tables. | |
| 342 # | |
| 343 ifcapable tempdb { | |
| 344 do_test autoinc-4.1 { | |
| 345 execsql { | |
| 346 SELECT 1, name FROM sqlite_master WHERE type='table'; | |
| 347 SELECT 2, name FROM sqlite_temp_master WHERE type='table'; | |
| 348 } | |
| 349 } {1 sqlite_sequence} | |
| 350 do_test autoinc-4.2 { | |
| 351 execsql { | |
| 352 CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); | |
| 353 CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); | |
| 354 SELECT 1, name FROM sqlite_master WHERE type='table'; | |
| 355 SELECT 2, name FROM sqlite_temp_master WHERE type='table'; | |
| 356 } | |
| 357 } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence} | |
| 358 do_test autoinc-4.3 { | |
| 359 execsql { | |
| 360 SELECT 1, * FROM main.sqlite_sequence; | |
| 361 SELECT 2, * FROM temp.sqlite_sequence; | |
| 362 } | |
| 363 } {} | |
| 364 do_test autoinc-4.4 { | |
| 365 execsql { | |
| 366 INSERT INTO t1 VALUES(10,1); | |
| 367 INSERT INTO t3 VALUES(20,2); | |
| 368 INSERT INTO t1 VALUES(NULL,3); | |
| 369 INSERT INTO t3 VALUES(NULL,4); | |
| 370 } | |
| 371 } {} | |
| 372 | |
| 373 ifcapable compound { | |
| 374 do_test autoinc-4.4.1 { | |
| 375 execsql { | |
| 376 SELECT * FROM t1 UNION ALL SELECT * FROM t3; | |
| 377 } | |
| 378 } {10 1 11 3 20 2 21 4} | |
| 379 } ;# ifcapable compound | |
| 380 | |
| 381 do_test autoinc-4.5 { | |
| 382 execsql { | |
| 383 SELECT 1, * FROM main.sqlite_sequence; | |
| 384 SELECT 2, * FROM temp.sqlite_sequence; | |
| 385 } | |
| 386 } {1 t1 11 2 t3 21} | |
| 387 do_test autoinc-4.6 { | |
| 388 execsql { | |
| 389 INSERT INTO t1 SELECT * FROM t3; | |
| 390 SELECT 1, * FROM main.sqlite_sequence; | |
| 391 SELECT 2, * FROM temp.sqlite_sequence; | |
| 392 } | |
| 393 } {1 t1 21 2 t3 21} | |
| 394 do_test autoinc-4.7 { | |
| 395 execsql { | |
| 396 INSERT INTO t3 SELECT x+100, y FROM t1; | |
| 397 SELECT 1, * FROM main.sqlite_sequence; | |
| 398 SELECT 2, * FROM temp.sqlite_sequence; | |
| 399 } | |
| 400 } {1 t1 21 2 t3 121} | |
| 401 do_test autoinc-4.8 { | |
| 402 execsql { | |
| 403 DROP TABLE t3; | |
| 404 SELECT 1, * FROM main.sqlite_sequence; | |
| 405 SELECT 2, * FROM temp.sqlite_sequence; | |
| 406 } | |
| 407 } {1 t1 21} | |
| 408 do_test autoinc-4.9 { | |
| 409 execsql { | |
| 410 CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q); | |
| 411 INSERT INTO t2 SELECT * FROM t1; | |
| 412 DROP TABLE t1; | |
| 413 SELECT 1, * FROM main.sqlite_sequence; | |
| 414 SELECT 2, * FROM temp.sqlite_sequence; | |
| 415 } | |
| 416 } {2 t2 21} | |
| 417 do_test autoinc-4.10 { | |
| 418 execsql { | |
| 419 DROP TABLE t2; | |
| 420 SELECT 1, * FROM main.sqlite_sequence; | |
| 421 SELECT 2, * FROM temp.sqlite_sequence; | |
| 422 } | |
| 423 } {} | |
| 424 } | |
| 425 | |
| 426 # Make sure AUTOINCREMENT works on ATTACH-ed tables. | |
| 427 # | |
| 428 ifcapable tempdb&&attach { | |
| 429 do_test autoinc-5.1 { | |
| 430 forcedelete test2.db | |
| 431 forcedelete test2.db-journal | |
| 432 sqlite3 db2 test2.db | |
| 433 execsql { | |
| 434 CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n); | |
| 435 CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT); | |
| 436 } db2; | |
| 437 execsql { | |
| 438 ATTACH 'test2.db' as aux; | |
| 439 SELECT 1, * FROM main.sqlite_sequence; | |
| 440 SELECT 2, * FROM temp.sqlite_sequence; | |
| 441 SELECT 3, * FROM aux.sqlite_sequence; | |
| 442 } | |
| 443 } {} | |
| 444 do_test autoinc-5.2 { | |
| 445 execsql { | |
| 446 INSERT INTO t4 VALUES(NULL,1); | |
| 447 SELECT 1, * FROM main.sqlite_sequence; | |
| 448 SELECT 2, * FROM temp.sqlite_sequence; | |
| 449 SELECT 3, * FROM aux.sqlite_sequence; | |
| 450 } | |
| 451 } {3 t4 1} | |
| 452 do_test autoinc-5.3 { | |
| 453 execsql { | |
| 454 INSERT INTO t5 VALUES(100,200); | |
| 455 SELECT * FROM sqlite_sequence | |
| 456 } db2 | |
| 457 } {t4 1 t5 200} | |
| 458 do_test autoinc-5.4 { | |
| 459 execsql { | |
| 460 SELECT 1, * FROM main.sqlite_sequence; | |
| 461 SELECT 2, * FROM temp.sqlite_sequence; | |
| 462 SELECT 3, * FROM aux.sqlite_sequence; | |
| 463 } | |
| 464 } {3 t4 1 3 t5 200} | |
| 465 } | |
| 466 | |
| 467 # Requirement REQ00310: Make sure an insert fails if the sequence is | |
| 468 # already at its maximum value. | |
| 469 # | |
| 470 ifcapable {rowid32} { | |
| 471 do_test autoinc-6.1 { | |
| 472 execsql { | |
| 473 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); | |
| 474 INSERT INTO t6 VALUES(2147483647,1); | |
| 475 SELECT seq FROM main.sqlite_sequence WHERE name='t6'; | |
| 476 } | |
| 477 } 2147483647 | |
| 478 } | |
| 479 ifcapable {!rowid32} { | |
| 480 do_test autoinc-6.1 { | |
| 481 execsql { | |
| 482 CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w); | |
| 483 INSERT INTO t6 VALUES(9223372036854775807,1); | |
| 484 SELECT seq FROM main.sqlite_sequence WHERE name='t6'; | |
| 485 } | |
| 486 } 9223372036854775807 | |
| 487 } | |
| 488 do_test autoinc-6.2 { | |
| 489 catchsql { | |
| 490 INSERT INTO t6 VALUES(NULL,1); | |
| 491 } | |
| 492 } {1 {database or disk is full}} | |
| 493 | |
| 494 # Allow the AUTOINCREMENT keyword inside the parentheses | |
| 495 # on a separate PRIMARY KEY designation. | |
| 496 # | |
| 497 do_test autoinc-7.1 { | |
| 498 execsql { | |
| 499 CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT)); | |
| 500 INSERT INTO t7(y) VALUES(123); | |
| 501 INSERT INTO t7(y) VALUES(234); | |
| 502 DELETE FROM t7; | |
| 503 INSERT INTO t7(y) VALUES(345); | |
| 504 SELECT * FROM t7; | |
| 505 } | |
| 506 } {3 345.0} | |
| 507 | |
| 508 # Test that if the AUTOINCREMENT is applied to a non integer primary key | |
| 509 # the error message is sensible. | |
| 510 do_test autoinc-7.2 { | |
| 511 catchsql { | |
| 512 CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT); | |
| 513 } | |
| 514 } {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}} | |
| 515 | |
| 516 | |
| 517 # Ticket #1283. Make sure that preparing but never running a statement | |
| 518 # that creates the sqlite_sequence table does not mess up the database. | |
| 519 # | |
| 520 do_test autoinc-8.1 { | |
| 521 catch {db2 close} | |
| 522 catch {db close} | |
| 523 forcedelete test.db | |
| 524 sqlite3 db test.db | |
| 525 set DB [sqlite3_connection_pointer db] | |
| 526 set STMT [sqlite3_prepare $DB { | |
| 527 CREATE TABLE t1( | |
| 528 x INTEGER PRIMARY KEY AUTOINCREMENT | |
| 529 ) | |
| 530 } -1 TAIL] | |
| 531 sqlite3_finalize $STMT | |
| 532 set STMT [sqlite3_prepare $DB { | |
| 533 CREATE TABLE t1( | |
| 534 x INTEGER PRIMARY KEY AUTOINCREMENT | |
| 535 ) | |
| 536 } -1 TAIL] | |
| 537 sqlite3_step $STMT | |
| 538 sqlite3_finalize $STMT | |
| 539 execsql { | |
| 540 INSERT INTO t1 VALUES(NULL); | |
| 541 SELECT * FROM t1; | |
| 542 } | |
| 543 } {1} | |
| 544 | |
| 545 # Ticket #3148 | |
| 546 # Make sure the sqlite_sequence table is not damaged when doing | |
| 547 # an empty insert - an INSERT INTO ... SELECT ... where the SELECT | |
| 548 # clause returns an empty set. | |
| 549 # | |
| 550 do_test autoinc-9.1 { | |
| 551 db eval { | |
| 552 CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y); | |
| 553 INSERT INTO t2 VALUES(NULL, 1); | |
| 554 CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b); | |
| 555 INSERT INTO t3 SELECT * FROM t2 WHERE y>1; | |
| 556 | |
| 557 SELECT * FROM sqlite_sequence WHERE name='t3'; | |
| 558 } | |
| 559 } {t3 0} | |
| 560 | |
| 561 ifcapable trigger { | |
| 562 catchsql { pragma recursive_triggers = off } | |
| 563 | |
| 564 # Ticket #3928. Make sure that triggers to not make extra slots in | |
| 565 # the SQLITE_SEQUENCE table. | |
| 566 # | |
| 567 do_test autoinc-3928.1 { | |
| 568 db eval { | |
| 569 CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b); | |
| 570 CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN | |
| 571 INSERT INTO t3928(b) VALUES('before1'); | |
| 572 INSERT INTO t3928(b) VALUES('before2'); | |
| 573 END; | |
| 574 CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN | |
| 575 INSERT INTO t3928(b) VALUES('after1'); | |
| 576 INSERT INTO t3928(b) VALUES('after2'); | |
| 577 END; | |
| 578 INSERT INTO t3928(b) VALUES('test'); | |
| 579 SELECT * FROM t3928 ORDER BY a; | |
| 580 } | |
| 581 } {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} | |
| 582 do_test autoinc-3928.2 { | |
| 583 db eval { | |
| 584 SELECT * FROM sqlite_sequence WHERE name='t3928' | |
| 585 } | |
| 586 } {t3928 13} | |
| 587 | |
| 588 do_test autoinc-3928.3 { | |
| 589 db eval { | |
| 590 DROP TRIGGER t3928r1; | |
| 591 DROP TRIGGER t3928r2; | |
| 592 CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928 | |
| 593 WHEN typeof(new.b)=='integer' BEGIN | |
| 594 INSERT INTO t3928(b) VALUES('before-int-' || new.b); | |
| 595 END; | |
| 596 CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928 | |
| 597 WHEN typeof(new.b)=='integer' BEGIN | |
| 598 INSERT INTO t3928(b) VALUES('after-int-' || new.b); | |
| 599 END; | |
| 600 DELETE FROM t3928 WHERE a!=1; | |
| 601 UPDATE t3928 SET b=456 WHERE a=1; | |
| 602 SELECT * FROM t3928 ORDER BY a; | |
| 603 } | |
| 604 } {1 456 14 before-int-456 15 after-int-456} | |
| 605 do_test autoinc-3928.4 { | |
| 606 db eval { | |
| 607 SELECT * FROM sqlite_sequence WHERE name='t3928' | |
| 608 } | |
| 609 } {t3928 15} | |
| 610 | |
| 611 do_test autoinc-3928.5 { | |
| 612 db eval { | |
| 613 CREATE TABLE t3928b(x); | |
| 614 INSERT INTO t3928b VALUES(100); | |
| 615 INSERT INTO t3928b VALUES(200); | |
| 616 INSERT INTO t3928b VALUES(300); | |
| 617 DELETE FROM t3928; | |
| 618 CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z); | |
| 619 CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN | |
| 620 INSERT INTO t3928(b) VALUES('before-del-'||old.x); | |
| 621 INSERT INTO t3928c(z) VALUES('before-del-'||old.x); | |
| 622 END; | |
| 623 CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN | |
| 624 INSERT INTO t3928(b) VALUES('after-del-'||old.x); | |
| 625 INSERT INTO t3928c(z) VALUES('after-del-'||old.x); | |
| 626 END; | |
| 627 DELETE FROM t3928b; | |
| 628 SELECT * FROM t3928 ORDER BY a; | |
| 629 } | |
| 630 } {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 be
fore-del-300 21 after-del-300} | |
| 631 do_test autoinc-3928.6 { | |
| 632 db eval { | |
| 633 SELECT * FROM t3928c ORDER BY y; | |
| 634 } | |
| 635 } {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-
del-300 6 after-del-300} | |
| 636 do_test autoinc-3928.7 { | |
| 637 db eval { | |
| 638 SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name; | |
| 639 } | |
| 640 } {t3928 21 t3928c 6} | |
| 641 | |
| 642 # Ticket [a696379c1f0886615541a48b35bd8181a80e88f8] | |
| 643 do_test autoinc-a69637.1 { | |
| 644 db eval { | |
| 645 CREATE TABLE ta69637_1(x INTEGER PRIMARY KEY AUTOINCREMENT, y); | |
| 646 CREATE TABLE ta69637_2(z); | |
| 647 CREATE TRIGGER ra69637_1 AFTER INSERT ON ta69637_2 BEGIN | |
| 648 INSERT INTO ta69637_1(y) VALUES(new.z+1); | |
| 649 END; | |
| 650 INSERT INTO ta69637_2 VALUES(123); | |
| 651 SELECT * FROM ta69637_1; | |
| 652 } | |
| 653 } {1 124} | |
| 654 do_test autoinc-a69637.2 { | |
| 655 db eval { | |
| 656 CREATE VIEW va69637_2 AS SELECT * FROM ta69637_2; | |
| 657 CREATE TRIGGER ra69637_2 INSTEAD OF INSERT ON va69637_2 BEGIN | |
| 658 INSERT INTO ta69637_1(y) VALUES(new.z+10000); | |
| 659 END; | |
| 660 INSERT INTO va69637_2 VALUES(123); | |
| 661 SELECT * FROM ta69637_1; | |
| 662 } | |
| 663 } {1 124 2 10123} | |
| 664 } | |
| 665 | |
| 666 | |
| 667 | |
| 668 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
