Return to delete.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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