gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/delete2.test (about)

     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