1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/delete2.test	Fri Jun 15 03:10:57 2012 +0200
     1.3 @@ -0,0 +1,119 @@
     1.4 +# 2003 September 6
     1.5 +#
     1.6 +# The author disclaims copyright to this source code.  In place of
     1.7 +# a legal notice, here is a blessing:
     1.8 +#
     1.9 +#    May you do good and not evil.
    1.10 +#    May you find forgiveness for yourself and forgive others.
    1.11 +#    May you share freely, never taking more than you give.
    1.12 +#
    1.13 +#***********************************************************************
    1.14 +# This file implements regression tests for SQLite library.  The
    1.15 +# focus of this script is a test to replicate the bug reported by
    1.16 +# ticket #842.
    1.17 +#
    1.18 +# Ticket #842 was a database corruption problem caused by a DELETE that
    1.19 +# removed an index entry by not the main table entry.  To recreate the
    1.20 +# problem do this:
    1.21 +#
    1.22 +#   (1) Create a table with an index.  Insert some data into that table.
    1.23 +#   (2) Start a query on the table but do not complete the query.
    1.24 +#   (3) Try to delete a single entry from the table.
    1.25 +#
    1.26 +# Step 3 will fail because there is still a read cursor on the table.
    1.27 +# But the database is corrupted by the DELETE.  It turns out that the
    1.28 +# index entry was deleted first, before the table entry.  And the index
    1.29 +# delete worked.  Thus an entry was deleted from the index but not from
    1.30 +# the table.
    1.31 +#
    1.32 +# The solution to the problem was to detect that the table is locked
    1.33 +# before the index entry is deleted.
    1.34 +#
    1.35 +# $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $
    1.36 +#
    1.37 +
    1.38 +set testdir [file dirname $argv0]
    1.39 +source $testdir/tester.tcl
    1.40 +
    1.41 +# Create a table that has an index.
    1.42 +#
    1.43 +do_test delete2-1.1 {
    1.44 +  set DB [sqlite3_connection_pointer db]
    1.45 +  execsql {
    1.46 +    CREATE TABLE q(s string, id string, constraint pk_q primary key(id));
    1.47 +    BEGIN;
    1.48 +    INSERT INTO q(s,id) VALUES('hello','id.1');
    1.49 +    INSERT INTO q(s,id) VALUES('goodbye','id.2');
    1.50 +    INSERT INTO q(s,id) VALUES('again','id.3');
    1.51 +    END;
    1.52 +    SELECT * FROM q;
    1.53 +  }
    1.54 +} {hello id.1 goodbye id.2 again id.3}
    1.55 +do_test delete2-1.2 {
    1.56 +  execsql {
    1.57 +    SELECT * FROM q WHERE id='id.1';
    1.58 +  }
    1.59 +} {hello id.1}
    1.60 +integrity_check delete2-1.3
    1.61 +
    1.62 +# Start a query on the table.  The query should not use the index.
    1.63 +# Do not complete the query, thus leaving the table locked.
    1.64 +#
    1.65 +do_test delete2-1.4 {
    1.66 +  set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
    1.67 +  sqlite3_step $STMT
    1.68 +} SQLITE_ROW
    1.69 +integrity_check delete2-1.5
    1.70 +
    1.71 +# Try to delete a row from the table while a read is in process.
    1.72 +# As of 2006-08-16, this is allowed.  (It used to fail with SQLITE_LOCKED.)
    1.73 +#
    1.74 +do_test delete2-1.6 {
    1.75 +  catchsql {
    1.76 +    DELETE FROM q WHERE rowid=1
    1.77 +  }
    1.78 +} {0 {}}
    1.79 +integrity_check delete2-1.7
    1.80 +do_test delete2-1.8 {
    1.81 +  execsql {
    1.82 +    SELECT * FROM q;
    1.83 +  }
    1.84 +} {goodbye id.2 again id.3}
    1.85 +
    1.86 +# Finalize the query, thus clearing the lock on the table.  Then
    1.87 +# retry the delete.  The delete should work this time.
    1.88 +#
    1.89 +do_test delete2-1.9 {
    1.90 +  sqlite3_finalize $STMT
    1.91 +  catchsql {
    1.92 +    DELETE FROM q WHERE rowid=1
    1.93 +  }
    1.94 +} {0 {}}
    1.95 +integrity_check delete2-1.10
    1.96 +do_test delete2-1.11 {
    1.97 +  execsql {
    1.98 +    SELECT * FROM q;
    1.99 +  }
   1.100 +} {goodbye id.2 again id.3}
   1.101 +
   1.102 +do_test delete2-2.1 {
   1.103 +  execsql {
   1.104 +    CREATE TABLE t1(a, b);
   1.105 +    CREATE TABLE t2(c, d);
   1.106 +    INSERT INTO t1 VALUES(1, 2);
   1.107 +    INSERT INTO t2 VALUES(3, 4);
   1.108 +    INSERT INTO t2 VALUES(5, 6);
   1.109 +  }
   1.110 +} {}
   1.111 +do_test delete2-2.2 {
   1.112 +  set res [list]
   1.113 +  db eval {
   1.114 +    SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2
   1.115 +  } {
   1.116 +    db eval {DELETE FROM t1}
   1.117 +    lappend res $b $c $d
   1.118 +  }
   1.119 +  set res
   1.120 +} {{} 3 4 {} 5 6}
   1.121 +
   1.122 +finish_test