Annotation of embedaddon/sqlite3/test/autoinc.test, revision 1.1
1.1 ! misho 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 before-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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to autoinc.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test