Annotation of embedaddon/sqlite3/test/laststmtchanges.test, revision 1.1
1.1 ! misho 1: #
! 2: # The author disclaims copyright to this source code. In place of
! 3: # a legal notice, here is a blessing:
! 4: #
! 5: # May you do good and not evil.
! 6: # May you find forgiveness for yourself and forgive others.
! 7: # May you share freely, never taking more than you give.
! 8: #
! 9: #***********************************************************************
! 10: #
! 11: # Tests to make sure that values returned by changes() and total_changes()
! 12: # are updated properly, especially inside triggers
! 13: #
! 14: # Note 1: changes() remains constant within a statement and only updates
! 15: # once the statement is finished (triggers count as part of
! 16: # statement).
! 17: # Note 2: changes() is changed within the context of a trigger much like
! 18: # last_insert_rowid() (see lastinsert.test), but is restored once
! 19: # the trigger exits.
! 20: # Note 3: changes() is not changed by a change to a view (since everything
! 21: # is done within instead of trigger context).
! 22: #
! 23: # $Id: laststmtchanges.test,v 1.7 2008/10/27 13:59:34 danielk1977 Exp $
! 24:
! 25: set testdir [file dirname $argv0]
! 26: source $testdir/tester.tcl
! 27:
! 28: # ----------------------------------------------------------------------------
! 29: # 1.x - basic tests (no triggers)
! 30:
! 31: # changes() set properly after insert
! 32: do_test laststmtchanges-1.1 {
! 33: catchsql {
! 34: create table t0 (x);
! 35: insert into t0 values (1);
! 36: insert into t0 values (1);
! 37: insert into t0 values (2);
! 38: insert into t0 values (2);
! 39: insert into t0 values (1);
! 40: insert into t0 values (1);
! 41: insert into t0 values (1);
! 42: insert into t0 values (2);
! 43: select changes(), total_changes();
! 44: }
! 45: } {0 {1 8}}
! 46:
! 47: # changes() set properly after update
! 48: do_test laststmtchanges-1.2 {
! 49: catchsql {
! 50: update t0 set x=3 where x=1;
! 51: select changes(), total_changes();
! 52: }
! 53: } {0 {5 13}}
! 54:
! 55: # There was some goofy change-counting logic in sqlite3_exec() that
! 56: # appears to have been left over from SQLite version 2. This test
! 57: # makes sure it has been removed.
! 58: #
! 59: do_test laststmtchanges-1.2.1 {
! 60: db cache flush
! 61: sqlite3_exec_printf db {update t0 set x=4 where x=3; select 1;} {}
! 62: execsql {select changes()}
! 63: } {5}
! 64:
! 65: # changes() unchanged within an update statement
! 66: do_test laststmtchanges-1.3 {
! 67: execsql {update t0 set x=3 where x=4}
! 68: catchsql {
! 69: update t0 set x=x+changes() where x=3;
! 70: select count() from t0 where x=8;
! 71: }
! 72: } {0 5}
! 73:
! 74: # changes() set properly after update on table where no rows changed
! 75: do_test laststmtchanges-1.4 {
! 76: catchsql {
! 77: update t0 set x=77 where x=88;
! 78: select changes();
! 79: }
! 80: } {0 0}
! 81:
! 82: # changes() set properly after delete from table
! 83: do_test laststmtchanges-1.5 {
! 84: catchsql {
! 85: delete from t0 where x=2;
! 86: select changes();
! 87: }
! 88: } {0 3}
! 89:
! 90: # All remaining tests involve triggers. Skip them if triggers are not
! 91: # supported in this build.
! 92: #
! 93: ifcapable {!trigger} {
! 94: finish_test
! 95: return
! 96: }
! 97:
! 98:
! 99: # ----------------------------------------------------------------------------
! 100: # 2.x - tests with after insert trigger
! 101:
! 102: # changes() changed properly after insert into table containing after trigger
! 103: do_test laststmtchanges-2.1 {
! 104: set ::tc [db total_changes]
! 105: catchsql {
! 106: create table t1 (k integer primary key);
! 107: create table t2 (k integer primary key, v1, v2);
! 108: create trigger r1 after insert on t1 for each row begin
! 109: insert into t2 values (NULL, changes(), NULL);
! 110: update t0 set x=x;
! 111: update t2 set v2=changes();
! 112: end;
! 113: insert into t1 values (77);
! 114: select changes();
! 115: }
! 116: } {0 1}
! 117:
! 118: # changes() unchanged upon entry into after insert trigger
! 119: do_test laststmtchanges-2.2 {
! 120: catchsql {
! 121: select v1 from t2;
! 122: }
! 123: } {0 3}
! 124:
! 125: # changes() changed properly by update within context of after insert trigger
! 126: do_test laststmtchanges-2.3 {
! 127: catchsql {
! 128: select v2 from t2;
! 129: }
! 130: } {0 5}
! 131:
! 132: # Total changes caused by firing the trigger above:
! 133: #
! 134: # 1 from "insert into t1 values(77)" +
! 135: # 1 from "insert into t2 values (NULL, changes(), NULL);" +
! 136: # 5 from "update t0 set x=x;" +
! 137: # 1 from "update t2 set v2=changes();"
! 138: #
! 139: do_test laststmtchanges-2.4 {
! 140: expr [db total_changes] - $::tc
! 141: } {8}
! 142:
! 143: # ----------------------------------------------------------------------------
! 144: # 3.x - tests with after update trigger
! 145:
! 146: # changes() changed properly after update into table containing after trigger
! 147: do_test laststmtchanges-3.1 {
! 148: catchsql {
! 149: drop trigger r1;
! 150: delete from t2; delete from t2;
! 151: create trigger r1 after update on t1 for each row begin
! 152: insert into t2 values (NULL, changes(), NULL);
! 153: delete from t0 where oid=1 or oid=2;
! 154: update t2 set v2=changes();
! 155: end;
! 156: update t1 set k=k;
! 157: select changes();
! 158: }
! 159: } {0 1}
! 160:
! 161: # changes() unchanged upon entry into after update trigger
! 162: do_test laststmtchanges-3.2 {
! 163: catchsql {
! 164: select v1 from t2;
! 165: }
! 166: } {0 0}
! 167:
! 168: # changes() changed properly by delete within context of after update trigger
! 169: do_test laststmtchanges-3.3 {
! 170: catchsql {
! 171: select v2 from t2;
! 172: }
! 173: } {0 2}
! 174:
! 175: # ----------------------------------------------------------------------------
! 176: # 4.x - tests with before delete trigger
! 177:
! 178: # changes() changed properly on delete from table containing before trigger
! 179: do_test laststmtchanges-4.1 {
! 180: catchsql {
! 181: drop trigger r1;
! 182: delete from t2; delete from t2;
! 183: create trigger r1 before delete on t1 for each row begin
! 184: insert into t2 values (NULL, changes(), NULL);
! 185: insert into t0 values (5);
! 186: update t2 set v2=changes();
! 187: end;
! 188: delete from t1;
! 189: select changes();
! 190: }
! 191: } {0 1}
! 192:
! 193: # changes() unchanged upon entry into before delete trigger
! 194: do_test laststmtchanges-4.2 {
! 195: catchsql {
! 196: select v1 from t2;
! 197: }
! 198: } {0 0}
! 199:
! 200: # changes() changed properly by insert within context of before delete trigger
! 201: do_test laststmtchanges-4.3 {
! 202: catchsql {
! 203: select v2 from t2;
! 204: }
! 205: } {0 1}
! 206:
! 207: # ----------------------------------------------------------------------------
! 208: # 5.x - complex tests with temporary tables and nested instead of triggers
! 209: # These tests cannot run if the library does not have view support enabled.
! 210:
! 211: ifcapable view&&tempdb {
! 212:
! 213: do_test laststmtchanges-5.1 {
! 214: catchsql {
! 215: drop table t0; drop table t1; drop table t2;
! 216: create temp table t0(x);
! 217: create temp table t1 (k integer primary key);
! 218: create temp table t2 (k integer primary key);
! 219: create temp view v1 as select * from t1;
! 220: create temp view v2 as select * from t2;
! 221: create temp table n1 (k integer primary key, n);
! 222: create temp table n2 (k integer primary key, n);
! 223: insert into t0 values (1);
! 224: insert into t0 values (2);
! 225: insert into t0 values (1);
! 226: insert into t0 values (1);
! 227: insert into t0 values (1);
! 228: insert into t0 values (2);
! 229: insert into t0 values (2);
! 230: insert into t0 values (1);
! 231: create temp trigger r1 instead of insert on v1 for each row begin
! 232: insert into n1 values (NULL, changes());
! 233: update t0 set x=x*10 where x=1;
! 234: insert into n1 values (NULL, changes());
! 235: insert into t1 values (NEW.k);
! 236: insert into n1 values (NULL, changes());
! 237: update t0 set x=x*10 where x=0;
! 238: insert into v2 values (100+NEW.k);
! 239: insert into n1 values (NULL, changes());
! 240: end;
! 241: create temp trigger r2 instead of insert on v2 for each row begin
! 242: insert into n2 values (NULL, changes());
! 243: insert into t2 values (1000+NEW.k);
! 244: insert into n2 values (NULL, changes());
! 245: update t0 set x=x*100 where x=0;
! 246: insert into n2 values (NULL, changes());
! 247: delete from t0 where x=2;
! 248: insert into n2 values (NULL, changes());
! 249: end;
! 250: insert into t1 values (77);
! 251: select changes();
! 252: }
! 253: } {0 1}
! 254:
! 255: do_test laststmtchanges-5.2 {
! 256: catchsql {
! 257: delete from t1 where k=88;
! 258: select changes();
! 259: }
! 260: } {0 0}
! 261:
! 262: do_test laststmtchanges-5.3 {
! 263: catchsql {
! 264: insert into v1 values (5);
! 265: select changes();
! 266: }
! 267: } {0 0}
! 268:
! 269: do_test laststmtchanges-5.4 {
! 270: catchsql {
! 271: select n from n1;
! 272: }
! 273: } {0 {0 5 1 0}}
! 274:
! 275: do_test laststmtchanges-5.5 {
! 276: catchsql {
! 277: select n from n2;
! 278: }
! 279: } {0 {0 1 0 3}}
! 280:
! 281: } ;# ifcapable view
! 282:
! 283:
! 284: # ----------------------------------------------------------------------------
! 285: # 6.x - Test "DELETE FROM <table>" in the absence of triggers
! 286: #
! 287: do_test laststmtchanges-6.1 {
! 288: execsql {
! 289: CREATE TABLE t3(a, b, c);
! 290: INSERT INTO t3 VALUES(1, 2, 3);
! 291: INSERT INTO t3 VALUES(4, 5, 6);
! 292: }
! 293: } {}
! 294: do_test laststmtchanges-6.2 {
! 295: execsql {
! 296: BEGIN;
! 297: DELETE FROM t3;
! 298: SELECT changes();
! 299: }
! 300: } {2}
! 301: do_test laststmtchanges-6.3 {
! 302: execsql {
! 303: ROLLBACK;
! 304: BEGIN;
! 305: DELETE FROM t3 WHERE a IS NOT NULL;
! 306: SELECT changes();
! 307: }
! 308: } {2}
! 309: do_test laststmtchanges-6.4 {
! 310: execsql {
! 311: ROLLBACK;
! 312: CREATE INDEX t3_i1 ON t3(a);
! 313: BEGIN;
! 314: DELETE FROM t3;
! 315: SELECT changes();
! 316: }
! 317: } {2}
! 318: do_test laststmtchanges-6.5 {
! 319: execsql { ROLLBACK }
! 320: set nTotalChange [execsql {SELECT total_changes()}]
! 321: expr 0
! 322: } {0}
! 323: do_test laststmtchanges-6.6 {
! 324: execsql {
! 325: SELECT total_changes();
! 326: DELETE FROM t3;
! 327: SELECT total_changes();
! 328: }
! 329: } [list $nTotalChange [expr $nTotalChange+2]]
! 330:
! 331: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to laststmtchanges.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