1 # 2007 Dec 4

     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 #

    12 # This file is to test that ticket #2820 has been fixed.

    13 # Ticket #2820 observes that a DROP TABLE statement that

    14 # occurs while a query is in process will fail with a

    15 # "database is locked" error, but the entry in the sqlite_master

    16 # table will still be removed.  This is incorrect.  The

    17 # entry in the sqlite_master table should persist when 

    18 # the DROP fails due to an error.

    19 #

    20 # $Id: tkt2820.test,v 1.1 2007/12/04 16:54:53 drh Exp $

    21 #

    22 

    23 set testdir [file dirname $argv0]

    24 source $testdir/tester.tcl

    25 

    26 proc test_schema_change {testid init ddl res} {

    27   db close

    28   file delete -force test.db test.db-journal

    29   sqlite3 db test.db

    30   execsql $init

    31   do_test tkt2820-$testid.1 {

    32     set STMT [sqlite3_prepare db {SELECT * FROM sqlite_master} -1 DUMMY]

    33     sqlite3_step $STMT

    34   } {SQLITE_ROW}

    35 #if {$testid==3} {execsql {PRAGMA vdbe_trace=ON}}

    36   do_test tkt2820-$testid.2 "catchsql [list $ddl]" \

    37        {1 {database table is locked}}

    38   do_test tkt2820-$testid.3 {

    39     sqlite3_finalize $STMT

    40     execsql {SELECT name FROM sqlite_master ORDER BY 1}

    41   } $res

    42   integrity_check tkt2820-$testid.4

    43   db close

    44   sqlite3 db test.db

    45   integrity_check tkt2820-$testid.5

    46 }

    47 

    48 test_schema_change 1 {

    49   CREATE TABLE t1(a);

    50 } {

    51   DROP TABLE t1

    52 } {t1}

    53 test_schema_change 2 {

    54   CREATE TABLE t1(a);

    55   CREATE TABLE t2(b);

    56 } {

    57   DROP TABLE t2

    58 } {t1 t2}

    59 test_schema_change 3 {

    60   CREATE TABLE t1(a);

    61   CREATE INDEX i1 ON t1(a);

    62 } {

    63   DROP INDEX i1

    64 } {i1 t1}

    65 

    66 # We further observe that prior to the fix associated with ticket #2820,

    67 # no statement journal would be created on an SQL statement that was run

    68 # while a second statement was active, as long as we are in autocommit

    69 # mode.  This is incorrect.

    70 #

    71 do_test tkt2820-4.1 {

    72   db close

    73   file delete -force test.db test.db-journal

    74   sqlite3 db test.db

    75   db eval {

    76     CREATE TABLE t1(a INTEGER PRIMARY KEY);

    77     INSERT INTO t1 VALUES(1);

    78     INSERT INTO t1 VALUES(2);

    79   }

    80 

    81   # The INSERT statement within the loop should fail on a

    82   # constraint violation on the second inserted row.  This

    83   # should cause the entire INSERT to rollback using a statement

    84   # journal.

    85   #

    86   db eval {SELECT name FROM sqlite_master} {

    87     catch {db eval {

    88       INSERT INTO t1 SELECT a+1 FROM t1 ORDER BY a DESC

    89     }}

    90   }

    91   db eval {SELECT a FROM t1 ORDER BY a}

    92 } {1 2}

    93 

    94 finish_test