Annotation of embedaddon/sqlite3/test/delete.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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 file is testing the DELETE FROM statement.
! 13: #
! 14: # $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: # Try to delete from a non-existant table.
! 20: #
! 21: do_test delete-1.1 {
! 22: set v [catch {execsql {DELETE FROM test1}} msg]
! 23: lappend v $msg
! 24: } {1 {no such table: test1}}
! 25:
! 26: # Try to delete from sqlite_master
! 27: #
! 28: do_test delete-2.1 {
! 29: set v [catch {execsql {DELETE FROM sqlite_master}} msg]
! 30: lappend v $msg
! 31: } {1 {table sqlite_master may not be modified}}
! 32:
! 33: # Delete selected entries from a table with and without an index.
! 34: #
! 35: do_test delete-3.1.1 {
! 36: execsql {CREATE TABLE table1(f1 int, f2 int)}
! 37: execsql {INSERT INTO table1 VALUES(1,2)}
! 38: execsql {INSERT INTO table1 VALUES(2,4)}
! 39: execsql {INSERT INTO table1 VALUES(3,8)}
! 40: execsql {INSERT INTO table1 VALUES(4,16)}
! 41: execsql {SELECT * FROM table1 ORDER BY f1}
! 42: } {1 2 2 4 3 8 4 16}
! 43: do_test delete-3.1.2 {
! 44: execsql {DELETE FROM table1 WHERE f1=3}
! 45: } {}
! 46: do_test delete-3.1.3 {
! 47: execsql {SELECT * FROM table1 ORDER BY f1}
! 48: } {1 2 2 4 4 16}
! 49: do_test delete-3.1.4 {
! 50: execsql {CREATE INDEX index1 ON table1(f1)}
! 51: execsql {PRAGMA count_changes=on}
! 52: ifcapable explain {
! 53: execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
! 54: }
! 55: execsql {DELETE FROM 'table1' WHERE f1=3}
! 56: } {0}
! 57: do_test delete-3.1.5 {
! 58: execsql {SELECT * FROM table1 ORDER BY f1}
! 59: } {1 2 2 4 4 16}
! 60: do_test delete-3.1.6.1 {
! 61: execsql {DELETE FROM table1 WHERE f1=2}
! 62: } {1}
! 63: do_test delete-3.1.6.2 {
! 64: db changes
! 65: } 1
! 66: do_test delete-3.1.7 {
! 67: execsql {SELECT * FROM table1 ORDER BY f1}
! 68: } {1 2 4 16}
! 69: integrity_check delete-3.2
! 70:
! 71:
! 72: # Semantic errors in the WHERE clause
! 73: #
! 74: do_test delete-4.1 {
! 75: execsql {CREATE TABLE table2(f1 int, f2 int)}
! 76: set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
! 77: lappend v $msg
! 78: } {1 {no such column: f3}}
! 79:
! 80: do_test delete-4.2 {
! 81: set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
! 82: lappend v $msg
! 83: } {1 {no such function: xyzzy}}
! 84: integrity_check delete-4.3
! 85:
! 86: # Lots of deletes
! 87: #
! 88: do_test delete-5.1.1 {
! 89: execsql {DELETE FROM table1}
! 90: } {2}
! 91: do_test delete-5.1.2 {
! 92: execsql {SELECT count(*) FROM table1}
! 93: } {0}
! 94: do_test delete-5.2.1 {
! 95: execsql {BEGIN TRANSACTION}
! 96: for {set i 1} {$i<=200} {incr i} {
! 97: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
! 98: }
! 99: execsql {COMMIT}
! 100: execsql {SELECT count(*) FROM table1}
! 101: } {200}
! 102: do_test delete-5.2.2 {
! 103: execsql {DELETE FROM table1}
! 104: } {200}
! 105: do_test delete-5.2.3 {
! 106: execsql {BEGIN TRANSACTION}
! 107: for {set i 1} {$i<=200} {incr i} {
! 108: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
! 109: }
! 110: execsql {COMMIT}
! 111: execsql {SELECT count(*) FROM table1}
! 112: } {200}
! 113: do_test delete-5.2.4 {
! 114: execsql {PRAGMA count_changes=off}
! 115: execsql {DELETE FROM table1}
! 116: } {}
! 117: do_test delete-5.2.5 {
! 118: execsql {SELECT count(*) FROM table1}
! 119: } {0}
! 120: do_test delete-5.2.6 {
! 121: execsql {BEGIN TRANSACTION}
! 122: for {set i 1} {$i<=200} {incr i} {
! 123: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
! 124: }
! 125: execsql {COMMIT}
! 126: execsql {SELECT count(*) FROM table1}
! 127: } {200}
! 128: do_test delete-5.3 {
! 129: for {set i 1} {$i<=200} {incr i 4} {
! 130: execsql "DELETE FROM table1 WHERE f1==$i"
! 131: }
! 132: execsql {SELECT count(*) FROM table1}
! 133: } {150}
! 134: do_test delete-5.4.1 {
! 135: execsql "DELETE FROM table1 WHERE f1>50"
! 136: db changes
! 137: } [db one {SELECT count(*) FROM table1 WHERE f1>50}]
! 138: do_test delete-5.4.2 {
! 139: execsql {SELECT count(*) FROM table1}
! 140: } {37}
! 141: do_test delete-5.5 {
! 142: for {set i 1} {$i<=70} {incr i 3} {
! 143: execsql "DELETE FROM table1 WHERE f1==$i"
! 144: }
! 145: execsql {SELECT f1 FROM table1 ORDER BY f1}
! 146: } {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50}
! 147: do_test delete-5.6 {
! 148: for {set i 1} {$i<40} {incr i} {
! 149: execsql "DELETE FROM table1 WHERE f1==$i"
! 150: }
! 151: execsql {SELECT f1 FROM table1 ORDER BY f1}
! 152: } {42 44 47 48 50}
! 153: do_test delete-5.7 {
! 154: execsql "DELETE FROM table1 WHERE f1!=48"
! 155: execsql {SELECT f1 FROM table1 ORDER BY f1}
! 156: } {48}
! 157: integrity_check delete-5.8
! 158:
! 159:
! 160: # Delete large quantities of data. We want to test the List overflow
! 161: # mechanism in the vdbe.
! 162: #
! 163: do_test delete-6.1 {
! 164: execsql {BEGIN; DELETE FROM table1}
! 165: for {set i 1} {$i<=3000} {incr i} {
! 166: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
! 167: }
! 168: execsql {DELETE FROM table2}
! 169: for {set i 1} {$i<=3000} {incr i} {
! 170: execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
! 171: }
! 172: execsql {COMMIT}
! 173: execsql {SELECT count(*) FROM table1}
! 174: } {3000}
! 175: do_test delete-6.2 {
! 176: execsql {SELECT count(*) FROM table2}
! 177: } {3000}
! 178: do_test delete-6.3 {
! 179: execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
! 180: } {1 2 3 4 5 6 7 8 9}
! 181: do_test delete-6.4 {
! 182: execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
! 183: } {1 2 3 4 5 6 7 8 9}
! 184: do_test delete-6.5.1 {
! 185: execsql {DELETE FROM table1 WHERE f1>7}
! 186: db changes
! 187: } {2993}
! 188: do_test delete-6.5.2 {
! 189: execsql {SELECT f1 FROM table1 ORDER BY f1}
! 190: } {1 2 3 4 5 6 7}
! 191: do_test delete-6.6 {
! 192: execsql {DELETE FROM table2 WHERE f1>7}
! 193: execsql {SELECT f1 FROM table2 ORDER BY f1}
! 194: } {1 2 3 4 5 6 7}
! 195: do_test delete-6.7 {
! 196: execsql {DELETE FROM table1}
! 197: execsql {SELECT f1 FROM table1}
! 198: } {}
! 199: do_test delete-6.8 {
! 200: execsql {INSERT INTO table1 VALUES(2,3)}
! 201: execsql {SELECT f1 FROM table1}
! 202: } {2}
! 203: do_test delete-6.9 {
! 204: execsql {DELETE FROM table2}
! 205: execsql {SELECT f1 FROM table2}
! 206: } {}
! 207: do_test delete-6.10 {
! 208: execsql {INSERT INTO table2 VALUES(2,3)}
! 209: execsql {SELECT f1 FROM table2}
! 210: } {2}
! 211: integrity_check delete-6.11
! 212:
! 213: do_test delete-7.1 {
! 214: execsql {
! 215: CREATE TABLE t3(a);
! 216: INSERT INTO t3 VALUES(1);
! 217: INSERT INTO t3 SELECT a+1 FROM t3;
! 218: INSERT INTO t3 SELECT a+2 FROM t3;
! 219: SELECT * FROM t3;
! 220: }
! 221: } {1 2 3 4}
! 222: ifcapable {trigger} {
! 223: do_test delete-7.2 {
! 224: execsql {
! 225: CREATE TABLE cnt(del);
! 226: INSERT INTO cnt VALUES(0);
! 227: CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
! 228: UPDATE cnt SET del=del+1;
! 229: END;
! 230: DELETE FROM t3 WHERE a<2;
! 231: SELECT * FROM t3;
! 232: }
! 233: } {2 3 4}
! 234: do_test delete-7.3 {
! 235: execsql {
! 236: SELECT * FROM cnt;
! 237: }
! 238: } {1}
! 239: do_test delete-7.4 {
! 240: execsql {
! 241: DELETE FROM t3;
! 242: SELECT * FROM t3;
! 243: }
! 244: } {}
! 245: do_test delete-7.5 {
! 246: execsql {
! 247: SELECT * FROM cnt;
! 248: }
! 249: } {4}
! 250: do_test delete-7.6 {
! 251: execsql {
! 252: INSERT INTO t3 VALUES(1);
! 253: INSERT INTO t3 SELECT a+1 FROM t3;
! 254: INSERT INTO t3 SELECT a+2 FROM t3;
! 255: CREATE TABLE t4 AS SELECT * FROM t3;
! 256: PRAGMA count_changes=ON;
! 257: DELETE FROM t3;
! 258: DELETE FROM t4;
! 259: }
! 260: } {4 4}
! 261: } ;# endif trigger
! 262: ifcapable {!trigger} {
! 263: execsql {DELETE FROM t3}
! 264: }
! 265: integrity_check delete-7.7
! 266:
! 267: # Make sure error messages are consistent when attempting to delete
! 268: # from a read-only database. Ticket #304.
! 269: #
! 270: do_test delete-8.0 {
! 271: execsql {
! 272: PRAGMA count_changes=OFF;
! 273: INSERT INTO t3 VALUES(123);
! 274: SELECT * FROM t3;
! 275: }
! 276: } {123}
! 277: db close
! 278: catch {forcedelete test.db-journal}
! 279: catch {file attributes test.db -permissions 0444}
! 280: catch {file attributes test.db -readonly 1}
! 281: sqlite3 db test.db
! 282: set ::DB [sqlite3_connection_pointer db]
! 283: do_test delete-8.1 {
! 284: catchsql {
! 285: DELETE FROM t3;
! 286: }
! 287: } {1 {attempt to write a readonly database}}
! 288: do_test delete-8.2 {
! 289: execsql {SELECT * FROM t3}
! 290: } {123}
! 291: do_test delete-8.3 {
! 292: catchsql {
! 293: DELETE FROM t3 WHERE 1;
! 294: }
! 295: } {1 {attempt to write a readonly database}}
! 296: do_test delete-8.4 {
! 297: execsql {SELECT * FROM t3}
! 298: } {123}
! 299:
! 300: # Update for v3: In v2 the DELETE statement would succeed because no
! 301: # database writes actually occur. Version 3 refuses to open a transaction
! 302: # on a read-only file, so the statement fails.
! 303: do_test delete-8.5 {
! 304: catchsql {
! 305: DELETE FROM t3 WHERE a<100;
! 306: }
! 307: # v2 result: {0 {}}
! 308: } {1 {attempt to write a readonly database}}
! 309: do_test delete-8.6 {
! 310: execsql {SELECT * FROM t3}
! 311: } {123}
! 312: integrity_check delete-8.7
! 313:
! 314: # Need to do the following for tcl 8.5 on mac. On that configuration, the
! 315: # -readonly flag is taken so seriously that a subsequent [forcedelete]
! 316: # (required before the next test file can be executed) will fail.
! 317: #
! 318: catch {file attributes test.db -readonly 0}
! 319: db close
! 320: forcedelete test.db test.db-journal
! 321:
! 322: # The following tests verify that SQLite correctly handles the case
! 323: # where an index B-Tree is being scanned, the rowid column being read
! 324: # from each index entry and another statement deletes some rows from
! 325: # the index B-Tree. At one point this (obscure) scenario was causing
! 326: # SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect
! 327: # query results.
! 328: #
! 329: do_test delete-9.1 {
! 330: sqlite3 db test.db
! 331: execsql {
! 332: CREATE TABLE t5(a, b);
! 333: CREATE TABLE t6(c, d);
! 334: INSERT INTO t5 VALUES(1, 2);
! 335: INSERT INTO t5 VALUES(3, 4);
! 336: INSERT INTO t5 VALUES(5, 6);
! 337: INSERT INTO t6 VALUES('a', 'b');
! 338: INSERT INTO t6 VALUES('c', 'd');
! 339: CREATE INDEX i5 ON t5(a);
! 340: CREATE INDEX i6 ON t6(c);
! 341: }
! 342: } {}
! 343: do_test delete-9.2 {
! 344: set res [list]
! 345: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
! 346: if {$r==2} { db eval { DELETE FROM t5 } }
! 347: lappend res $r $c $d
! 348: }
! 349: set res
! 350: } {1 a b 1 c d 2 a b {} c d}
! 351: do_test delete-9.3 {
! 352: execsql {
! 353: INSERT INTO t5 VALUES(1, 2);
! 354: INSERT INTO t5 VALUES(3, 4);
! 355: INSERT INTO t5 VALUES(5, 6);
! 356: }
! 357: set res [list]
! 358: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
! 359: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } }
! 360: lappend res $r $c $d
! 361: }
! 362: set res
! 363: } {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}
! 364: do_test delete-9.4 {
! 365: execsql {
! 366: DELETE FROM t5;
! 367: INSERT INTO t5 VALUES(1, 2);
! 368: INSERT INTO t5 VALUES(3, 4);
! 369: INSERT INTO t5 VALUES(5, 6);
! 370: }
! 371: set res [list]
! 372: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
! 373: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } }
! 374: lappend res $r $c $d
! 375: }
! 376: set res
! 377: } {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}
! 378: do_test delete-9.5 {
! 379: execsql {
! 380: DELETE FROM t5;
! 381: INSERT INTO t5 VALUES(1, 2);
! 382: INSERT INTO t5 VALUES(3, 4);
! 383: INSERT INTO t5 VALUES(5, 6);
! 384: }
! 385: set res [list]
! 386: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
! 387: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } }
! 388: lappend res $r $c $d
! 389: }
! 390: set res
! 391: } {1 a b 1 c d 2 a b 2 c d}
! 392:
! 393:
! 394: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to delete.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