Return to delete2.test CVS log

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

Annotation of embedaddon/sqlite3/test/delete2.test, revision 1.1

1.1     ! misho       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.8 2008/07/08 15:59:52 danielk1977 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