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

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