Return to delete2.test CVS log

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

sqlite3

    1: # 2003 September 6
    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 a test to replicate the bug reported by
   13: # ticket #842.
   14: #
   15: # Ticket #842 was a database corruption problem caused by a DELETE that
   16: # removed an index entry by not the main table entry.  To recreate the
   17: # problem do this:
   18: #
   19: #   (1) Create a table with an index.  Insert some data into that table.
   20: #   (2) Start a query on the table but do not complete the query.
   21: #   (3) Try to delete a single entry from the table.
   22: #
   23: # Step 3 will fail because there is still a read cursor on the table.
   24: # But the database is corrupted by the DELETE.  It turns out that the
   25: # index entry was deleted first, before the table entry.  And the index
   26: # delete worked.  Thus an entry was deleted from the index but not from
   27: # the table.
   28: #
   29: # The solution to the problem was to detect that the table is locked
   30: # before the index entry is deleted.
   31: #
   32: # $Id: delete2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   33: #
   34: 
   35: set testdir [file dirname $argv0]
   36: source $testdir/tester.tcl
   37: 
   38: # Create a table that has an index.
   39: #
   40: do_test delete2-1.1 {
   41:   set DB [sqlite3_connection_pointer db]
   42:   execsql {
   43:     CREATE TABLE q(s string, id string, constraint pk_q primary key(id));
   44:     BEGIN;
   45:     INSERT INTO q(s,id) VALUES('hello','id.1');
   46:     INSERT INTO q(s,id) VALUES('goodbye','id.2');
   47:     INSERT INTO q(s,id) VALUES('again','id.3');
   48:     END;
   49:     SELECT * FROM q;
   50:   }
   51: } {hello id.1 goodbye id.2 again id.3}
   52: do_test delete2-1.2 {
   53:   execsql {
   54:     SELECT * FROM q WHERE id='id.1';
   55:   }
   56: } {hello id.1}
   57: integrity_check delete2-1.3
   58: 
   59: # Start a query on the table.  The query should not use the index.
   60: # Do not complete the query, thus leaving the table locked.
   61: #
   62: do_test delete2-1.4 {
   63:   set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
   64:   sqlite3_step $STMT
   65: } SQLITE_ROW
   66: integrity_check delete2-1.5
   67: 
   68: # Try to delete a row from the table while a read is in process.
   69: # As of 2006-08-16, this is allowed.  (It used to fail with SQLITE_LOCKED.)
   70: #
   71: do_test delete2-1.6 {
   72:   catchsql {
   73:     DELETE FROM q WHERE rowid=1
   74:   }
   75: } {0 {}}
   76: integrity_check delete2-1.7
   77: do_test delete2-1.8 {
   78:   execsql {
   79:     SELECT * FROM q;
   80:   }
   81: } {goodbye id.2 again id.3}
   82: 
   83: # Finalize the query, thus clearing the lock on the table.  Then
   84: # retry the delete.  The delete should work this time.
   85: #
   86: do_test delete2-1.9 {
   87:   sqlite3_finalize $STMT
   88:   catchsql {
   89:     DELETE FROM q WHERE rowid=1
   90:   }
   91: } {0 {}}
   92: integrity_check delete2-1.10
   93: do_test delete2-1.11 {
   94:   execsql {
   95:     SELECT * FROM q;
   96:   }
   97: } {goodbye id.2 again id.3}
   98: 
   99: do_test delete2-2.1 {
  100:   execsql {
  101:     CREATE TABLE t1(a, b);
  102:     CREATE TABLE t2(c, d);
  103:     INSERT INTO t1 VALUES(1, 2);
  104:     INSERT INTO t2 VALUES(3, 4);
  105:     INSERT INTO t2 VALUES(5, 6);
  106:   }
  107: } {}
  108: do_test delete2-2.2 {
  109:   set res [list]
  110:   db eval {
  111:     SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2
  112:   } {
  113:     db eval {DELETE FROM t1}
  114:     lappend res $b $c $d
  115:   }
  116:   set res
  117: } {{} 3 4 {} 5 6}
  118: 
  119: finish_test