Annotation of embedaddon/sqlite3/test/lastinsert.test, revision 1.1
1.1 ! misho 1: # The author disclaims copyright to this source code. In place of
! 2: # a legal notice, here is a blessing:
! 3: #
! 4: # May you do good and not evil.
! 5: # May you find forgiveness for yourself and forgive others.
! 6: # May you share freely, never taking more than you give.
! 7: #
! 8: #***********************************************************************
! 9: #
! 10: # Tests to make sure that value returned by last_insert_rowid() (LIRID)
! 11: # is updated properly, especially inside triggers
! 12: #
! 13: # Note 1: insert into table is now the only statement which changes LIRID
! 14: # Note 2: upon entry into before or instead of triggers,
! 15: # LIRID is unchanged (rather than -1)
! 16: # Note 3: LIRID is changed within the context of a trigger,
! 17: # but is restored once the trigger exits
! 18: # Note 4: LIRID is not changed by an insert into a view (since everything
! 19: # is done within instead of trigger context)
! 20: #
! 21:
! 22: set testdir [file dirname $argv0]
! 23: source $testdir/tester.tcl
! 24:
! 25: # ----------------------------------------------------------------------------
! 26: # 1.x - basic tests (no triggers)
! 27:
! 28: # LIRID changed properly after an insert into a table
! 29: do_test lastinsert-1.1 {
! 30: catchsql {
! 31: create table t1 (k integer primary key);
! 32: insert into t1 values (1);
! 33: insert into t1 values (NULL);
! 34: insert into t1 values (NULL);
! 35: select last_insert_rowid();
! 36: }
! 37: } {0 3}
! 38:
! 39: # LIRID unchanged after an update on a table
! 40: do_test lastinsert-1.2 {
! 41: catchsql {
! 42: update t1 set k=4 where k=2;
! 43: select last_insert_rowid();
! 44: }
! 45: } {0 3}
! 46:
! 47: # LIRID unchanged after a delete from a table
! 48: do_test lastinsert-1.3 {
! 49: catchsql {
! 50: delete from t1 where k=4;
! 51: select last_insert_rowid();
! 52: }
! 53: } {0 3}
! 54:
! 55: # LIRID unchanged after create table/view statements
! 56: do_test lastinsert-1.4.1 {
! 57: catchsql {
! 58: create table t2 (k integer primary key, val1, val2, val3);
! 59: select last_insert_rowid();
! 60: }
! 61: } {0 3}
! 62: ifcapable view {
! 63: do_test lastinsert-1.4.2 {
! 64: catchsql {
! 65: create view v as select * from t1;
! 66: select last_insert_rowid();
! 67: }
! 68: } {0 3}
! 69: } ;# ifcapable view
! 70:
! 71: # All remaining tests involve triggers. Skip them if triggers are not
! 72: # supported in this build.
! 73: #
! 74: ifcapable {!trigger} {
! 75: finish_test
! 76: return
! 77: }
! 78:
! 79: # ----------------------------------------------------------------------------
! 80: # 2.x - tests with after insert trigger
! 81:
! 82: # LIRID changed properly after an insert into table containing an after trigger
! 83: do_test lastinsert-2.1 {
! 84: catchsql {
! 85: delete from t2;
! 86: create trigger r1 after insert on t1 for each row begin
! 87: insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
! 88: update t2 set k=k+10, val2=100+last_insert_rowid();
! 89: update t2 set val3=1000+last_insert_rowid();
! 90: end;
! 91: insert into t1 values (13);
! 92: select last_insert_rowid();
! 93: }
! 94: } {0 13}
! 95:
! 96: # LIRID equals NEW.k upon entry into after insert trigger
! 97: do_test lastinsert-2.2 {
! 98: catchsql {
! 99: select val1 from t2;
! 100: }
! 101: } {0 13}
! 102:
! 103: # LIRID changed properly by insert within context of after insert trigger
! 104: do_test lastinsert-2.3 {
! 105: catchsql {
! 106: select val2 from t2;
! 107: }
! 108: } {0 126}
! 109:
! 110: # LIRID unchanged by update within context of after insert trigger
! 111: do_test lastinsert-2.4 {
! 112: catchsql {
! 113: select val3 from t2;
! 114: }
! 115: } {0 1026}
! 116:
! 117: # ----------------------------------------------------------------------------
! 118: # 3.x - tests with after update trigger
! 119:
! 120: # LIRID not changed after an update onto a table containing an after trigger
! 121: do_test lastinsert-3.1 {
! 122: catchsql {
! 123: delete from t2;
! 124: drop trigger r1;
! 125: create trigger r1 after update on t1 for each row begin
! 126: insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
! 127: update t2 set k=k+10, val2=100+last_insert_rowid();
! 128: update t2 set val3=1000+last_insert_rowid();
! 129: end;
! 130: update t1 set k=14 where k=3;
! 131: select last_insert_rowid();
! 132: }
! 133: } {0 13}
! 134:
! 135: # LIRID unchanged upon entry into after update trigger
! 136: do_test lastinsert-3.2 {
! 137: catchsql {
! 138: select val1 from t2;
! 139: }
! 140: } {0 13}
! 141:
! 142: # LIRID changed properly by insert within context of after update trigger
! 143: do_test lastinsert-3.3 {
! 144: catchsql {
! 145: select val2 from t2;
! 146: }
! 147: } {0 128}
! 148:
! 149: # LIRID unchanged by update within context of after update trigger
! 150: do_test lastinsert-3.4 {
! 151: catchsql {
! 152: select val3 from t2;
! 153: }
! 154: } {0 1028}
! 155:
! 156: # ----------------------------------------------------------------------------
! 157: # 4.x - tests with instead of insert trigger
! 158: # These may not be run if either views or triggers were disabled at
! 159: # compile-time
! 160:
! 161: ifcapable {view && trigger} {
! 162: # LIRID not changed after an insert into view containing an instead of trigger
! 163: do_test lastinsert-4.1 {
! 164: catchsql {
! 165: delete from t2;
! 166: drop trigger r1;
! 167: create trigger r1 instead of insert on v for each row begin
! 168: insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
! 169: update t2 set k=k+10, val2=100+last_insert_rowid();
! 170: update t2 set val3=1000+last_insert_rowid();
! 171: end;
! 172: insert into v values (15);
! 173: select last_insert_rowid();
! 174: }
! 175: } {0 13}
! 176:
! 177: # LIRID unchanged upon entry into instead of trigger
! 178: do_test lastinsert-4.2 {
! 179: catchsql {
! 180: select val1 from t2;
! 181: }
! 182: } {0 13}
! 183:
! 184: # LIRID changed properly by insert within context of instead of trigger
! 185: do_test lastinsert-4.3 {
! 186: catchsql {
! 187: select val2 from t2;
! 188: }
! 189: } {0 130}
! 190:
! 191: # LIRID unchanged by update within context of instead of trigger
! 192: do_test lastinsert-4.4 {
! 193: catchsql {
! 194: select val3 from t2;
! 195: }
! 196: } {0 1030}
! 197: } ;# ifcapable (view && trigger)
! 198:
! 199: # ----------------------------------------------------------------------------
! 200: # 5.x - tests with before delete trigger
! 201:
! 202: # LIRID not changed after a delete on a table containing a before trigger
! 203: do_test lastinsert-5.1 {
! 204: catchsql {
! 205: drop trigger r1; -- This was not created if views are disabled.
! 206: }
! 207: catchsql {
! 208: delete from t2;
! 209: create trigger r1 before delete on t1 for each row begin
! 210: insert into t2 values (77, last_insert_rowid(), NULL, NULL);
! 211: update t2 set k=k+10, val2=100+last_insert_rowid();
! 212: update t2 set val3=1000+last_insert_rowid();
! 213: end;
! 214: delete from t1 where k=1;
! 215: select last_insert_rowid();
! 216: }
! 217: } {0 13}
! 218:
! 219: # LIRID unchanged upon entry into delete trigger
! 220: do_test lastinsert-5.2 {
! 221: catchsql {
! 222: select val1 from t2;
! 223: }
! 224: } {0 13}
! 225:
! 226: # LIRID changed properly by insert within context of delete trigger
! 227: do_test lastinsert-5.3 {
! 228: catchsql {
! 229: select val2 from t2;
! 230: }
! 231: } {0 177}
! 232:
! 233: # LIRID unchanged by update within context of delete trigger
! 234: do_test lastinsert-5.4 {
! 235: catchsql {
! 236: select val3 from t2;
! 237: }
! 238: } {0 1077}
! 239:
! 240: # ----------------------------------------------------------------------------
! 241: # 6.x - tests with instead of update trigger
! 242: # These tests may not run if either views or triggers are disabled.
! 243:
! 244: ifcapable {view && trigger} {
! 245: # LIRID not changed after an update on a view containing an instead of trigger
! 246: do_test lastinsert-6.1 {
! 247: catchsql {
! 248: delete from t2;
! 249: drop trigger r1;
! 250: create trigger r1 instead of update on v for each row begin
! 251: insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
! 252: update t2 set k=k+10, val2=100+last_insert_rowid();
! 253: update t2 set val3=1000+last_insert_rowid();
! 254: end;
! 255: update v set k=16 where k=14;
! 256: select last_insert_rowid();
! 257: }
! 258: } {0 13}
! 259:
! 260: # LIRID unchanged upon entry into instead of trigger
! 261: do_test lastinsert-6.2 {
! 262: catchsql {
! 263: select val1 from t2;
! 264: }
! 265: } {0 13}
! 266:
! 267: # LIRID changed properly by insert within context of instead of trigger
! 268: do_test lastinsert-6.3 {
! 269: catchsql {
! 270: select val2 from t2;
! 271: }
! 272: } {0 132}
! 273:
! 274: # LIRID unchanged by update within context of instead of trigger
! 275: do_test lastinsert-6.4 {
! 276: catchsql {
! 277: select val3 from t2;
! 278: }
! 279: } {0 1032}
! 280: } ;# ifcapable (view && trigger)
! 281:
! 282: # ----------------------------------------------------------------------------
! 283: # 7.x - complex tests with temporary tables and nested instead of triggers
! 284: # These do not run if views or triggers are disabled.
! 285:
! 286: ifcapable {trigger && view && tempdb} {
! 287: do_test lastinsert-7.1 {
! 288: catchsql {
! 289: drop table t1; drop table t2; drop trigger r1;
! 290: create temp table t1 (k integer primary key);
! 291: create temp table t2 (k integer primary key);
! 292: create temp view v1 as select * from t1;
! 293: create temp view v2 as select * from t2;
! 294: create temp table rid (k integer primary key, rin, rout);
! 295: insert into rid values (1, NULL, NULL);
! 296: insert into rid values (2, NULL, NULL);
! 297: create temp trigger r1 instead of insert on v1 for each row begin
! 298: update rid set rin=last_insert_rowid() where k=1;
! 299: insert into t1 values (100+NEW.k);
! 300: insert into v2 values (100+last_insert_rowid());
! 301: update rid set rout=last_insert_rowid() where k=1;
! 302: end;
! 303: create temp trigger r2 instead of insert on v2 for each row begin
! 304: update rid set rin=last_insert_rowid() where k=2;
! 305: insert into t2 values (1000+NEW.k);
! 306: update rid set rout=last_insert_rowid() where k=2;
! 307: end;
! 308: insert into t1 values (77);
! 309: select last_insert_rowid();
! 310: }
! 311: } {0 77}
! 312:
! 313: do_test lastinsert-7.2 {
! 314: catchsql {
! 315: insert into v1 values (5);
! 316: select last_insert_rowid();
! 317: }
! 318: } {0 77}
! 319:
! 320: do_test lastinsert-7.3 {
! 321: catchsql {
! 322: select rin from rid where k=1;
! 323: }
! 324: } {0 77}
! 325:
! 326: do_test lastinsert-7.4 {
! 327: catchsql {
! 328: select rout from rid where k=1;
! 329: }
! 330: } {0 105}
! 331:
! 332: do_test lastinsert-7.5 {
! 333: catchsql {
! 334: select rin from rid where k=2;
! 335: }
! 336: } {0 105}
! 337:
! 338: do_test lastinsert-7.6 {
! 339: catchsql {
! 340: select rout from rid where k=2;
! 341: }
! 342: } {0 1205}
! 343:
! 344: do_test lastinsert-8.1 {
! 345: db close
! 346: sqlite3 db test.db
! 347: execsql {
! 348: CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
! 349: CREATE TABLE t3(a, b);
! 350: CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN
! 351: INSERT INTO t3 VALUES(new.x, new.y);
! 352: END;
! 353: INSERT INTO t2 VALUES(5000000000, 1);
! 354: SELECT last_insert_rowid();
! 355: }
! 356: } 5000000000
! 357:
! 358: do_test lastinsert-9.1 {
! 359: db eval {INSERT INTO t2 VALUES(123456789012345,0)}
! 360: db last_insert_rowid
! 361: } {123456789012345}
! 362:
! 363:
! 364: } ;# ifcapable (view && trigger)
! 365:
! 366: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to lastinsert.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