# 2001 September 15

#

# The author disclaims copyright to this source code.  In place of

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

#    May you share freely, never taking more than you give.

#

#***********************************************************************

# This file implements regression tests for SQLite library.  This

# file is a copy of "trans.test" modified to run under autovacuum mode.

# the point is to stress the autovacuum logic and try to get it to fail.

#

# $Id: avtrans.test,v 1.6 2007/09/12 17:01:45 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Create several tables to work with.

#

do_test avtrans-1.0 {

  execsql {

    PRAGMA auto_vacuum=ON;

    CREATE TABLE one(a int PRIMARY KEY, b text);

    INSERT INTO one VALUES(1,'one');

    INSERT INTO one VALUES(2,'two');

    INSERT INTO one VALUES(3,'three');

    SELECT b FROM one ORDER BY a;

  }

} {one two three}

do_test avtrans-1.1 {

  execsql {

    CREATE TABLE two(a int PRIMARY KEY, b text);

    INSERT INTO two VALUES(1,'I');

    INSERT INTO two VALUES(5,'V');

    INSERT INTO two VALUES(10,'X');

    SELECT b FROM two ORDER BY a;

  }

} {I V X}

do_test avtrans-1.9 {

  sqlite3 altdb test.db

  execsql {SELECT b FROM one ORDER BY a} altdb

} {one two three}

do_test avtrans-1.10 {

  execsql {SELECT b FROM two ORDER BY a} altdb

} {I V X}

integrity_check avtrans-1.11

# Basic transactions

#

do_test avtrans-2.1 {

  set v [catch {execsql {BEGIN}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.2 {

  set v [catch {execsql {END}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.3 {

  set v [catch {execsql {BEGIN TRANSACTION}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.4 {

  set v [catch {execsql {COMMIT TRANSACTION}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.5 {

  set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.6 {

  set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]

  lappend v $msg

} {0 {}}

do_test avtrans-2.10 {

  execsql {

    BEGIN;

    SELECT a FROM one ORDER BY a;

    SELECT a FROM two ORDER BY a;

    END;

  }

} {1 2 3 1 5 10}

integrity_check avtrans-2.11

# Check the locking behavior

#

sqlite3_soft_heap_limit 0

do_test avtrans-3.1 {

  execsql {

    BEGIN;

    UPDATE one SET a = 0 WHERE 0;

    SELECT a FROM one ORDER BY a;

  }

} {1 2 3}

do_test avtrans-3.2 {

  catchsql {

    SELECT a FROM two ORDER BY a;

  } altdb

} {0 {1 5 10}}

do_test avtrans-3.3 {

  catchsql {

    SELECT a FROM one ORDER BY a;

  } altdb

} {0 {1 2 3}}

do_test avtrans-3.4 {

  catchsql {

    INSERT INTO one VALUES(4,'four');

  }

} {0 {}}

do_test avtrans-3.5 {

  catchsql {

    SELECT a FROM two ORDER BY a;

  } altdb

} {0 {1 5 10}}

do_test avtrans-3.6 {

  catchsql {

    SELECT a FROM one ORDER BY a;

  } altdb

} {0 {1 2 3}}

do_test avtrans-3.7 {

  catchsql {

    INSERT INTO two VALUES(4,'IV');

  }

} {0 {}}

do_test avtrans-3.8 {

  catchsql {

    SELECT a FROM two ORDER BY a;

  } altdb

} {0 {1 5 10}}

do_test avtrans-3.9 {

  catchsql {

    SELECT a FROM one ORDER BY a;

  } altdb

} {0 {1 2 3}}

do_test avtrans-3.10 {

  execsql {END TRANSACTION}

} {}

do_test avtrans-3.11 {

  set v [catch {execsql {

    SELECT a FROM two ORDER BY a;

  } altdb} msg]

  lappend v $msg

} {0 {1 4 5 10}}

do_test avtrans-3.12 {

  set v [catch {execsql {

    SELECT a FROM one ORDER BY a;

  } altdb} msg]

  lappend v $msg

} {0 {1 2 3 4}}

do_test avtrans-3.13 {

  set v [catch {execsql {

    SELECT a FROM two ORDER BY a;

  } db} msg]

  lappend v $msg

} {0 {1 4 5 10}}

do_test avtrans-3.14 {

  set v [catch {execsql {

    SELECT a FROM one ORDER BY a;

  } db} msg]

  lappend v $msg

} {0 {1 2 3 4}}

sqlite3_soft_heap_limit $soft_limit

integrity_check avtrans-3.15

do_test avtrans-4.1 {

  set v [catch {execsql {

    COMMIT;

  } db} msg]

  lappend v $msg

} {1 {cannot commit - no transaction is active}}

do_test avtrans-4.2 {

  set v [catch {execsql {

    ROLLBACK;

  } db} msg]

  lappend v $msg

} {1 {cannot rollback - no transaction is active}}

do_test avtrans-4.3 {

  catchsql {

    BEGIN TRANSACTION;

    UPDATE two SET a = 0 WHERE 0;

    SELECT a FROM two ORDER BY a;

  } db

} {0 {1 4 5 10}}

do_test avtrans-4.4 {

  catchsql {

    SELECT a FROM two ORDER BY a;

  } altdb

} {0 {1 4 5 10}}

do_test avtrans-4.5 {

  catchsql {

    SELECT a FROM one ORDER BY a;

  } altdb

} {0 {1 2 3 4}}

do_test avtrans-4.6 {

  catchsql {

    BEGIN TRANSACTION;

    SELECT a FROM one ORDER BY a;

  } db

} {1 {cannot start a transaction within a transaction}}

do_test avtrans-4.7 {

  catchsql {

    SELECT a FROM two ORDER BY a;

  } altdb

} {0 {1 4 5 10}}

do_test avtrans-4.8 {

  catchsql {

    SELECT a FROM one ORDER BY a;

  } altdb

} {0 {1 2 3 4}}

do_test avtrans-4.9 {

  set v [catch {execsql {

    END TRANSACTION;

    SELECT a FROM two ORDER BY a;

  } db} msg]

  lappend v $msg

} {0 {1 4 5 10}}

do_test avtrans-4.10 {

  set v [catch {execsql {

    SELECT a FROM two ORDER BY a;

  } altdb} msg]

  lappend v $msg

} {0 {1 4 5 10}}

do_test avtrans-4.11 {

  set v [catch {execsql {

    SELECT a FROM one ORDER BY a;

  } altdb} msg]

  lappend v $msg

} {0 {1 2 3 4}}

integrity_check avtrans-4.12

do_test avtrans-4.98 {

  altdb close

  execsql {

    DROP TABLE one;

    DROP TABLE two;

  }

} {}

integrity_check avtrans-4.99

# Check out the commit/rollback behavior of the database

#

do_test avtrans-5.1 {

  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

} {}

do_test avtrans-5.2 {

  execsql {BEGIN TRANSACTION}

  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

} {}

do_test avtrans-5.3 {

  execsql {CREATE TABLE one(a text, b int)}

  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

} {one}

do_test avtrans-5.4 {

  execsql {SELECT a,b FROM one ORDER BY b}

} {}

do_test avtrans-5.5 {

  execsql {INSERT INTO one(a,b) VALUES('hello', 1)}

  execsql {SELECT a,b FROM one ORDER BY b}

} {hello 1}

do_test avtrans-5.6 {

  execsql {ROLLBACK}

  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}

} {}

do_test avtrans-5.7 {

  set v [catch {

    execsql {SELECT a,b FROM one ORDER BY b}

  } msg]

  lappend v $msg

} {1 {no such table: one}}

# Test commits and rollbacks of table CREATE TABLEs, CREATE INDEXs

# DROP TABLEs and DROP INDEXs

#

do_test avtrans-5.8 {

  execsql {

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name

  }

} {}

do_test avtrans-5.9 {

  execsql {

    BEGIN TRANSACTION;

    CREATE TABLE t1(a int, b int, c int);

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {t1}

do_test avtrans-5.10 {

  execsql {

    CREATE INDEX i1 ON t1(a);

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i1 t1}

do_test avtrans-5.11 {

  execsql {

    COMMIT;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i1 t1}

do_test avtrans-5.12 {

  execsql {

    BEGIN TRANSACTION;

    CREATE TABLE t2(a int, b int, c int);

    CREATE INDEX i2a ON t2(a);

    CREATE INDEX i2b ON t2(b);

    DROP TABLE t1;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i2a i2b t2}

do_test avtrans-5.13 {

  execsql {

    ROLLBACK;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i1 t1}

do_test avtrans-5.14 {

  execsql {

    BEGIN TRANSACTION;

    DROP INDEX i1;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {t1}

do_test avtrans-5.15 {

  execsql {

    ROLLBACK;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i1 t1}

do_test avtrans-5.16 {

  execsql {

    BEGIN TRANSACTION;

    DROP INDEX i1;

    CREATE TABLE t2(x int, y int, z int);

    CREATE INDEX i2x ON t2(x);

    CREATE INDEX i2y ON t2(y);

    INSERT INTO t2 VALUES(1,2,3);

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i2x i2y t1 t2}

do_test avtrans-5.17 {

  execsql {

    COMMIT;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i2x i2y t1 t2}

do_test avtrans-5.18 {

  execsql {

    SELECT * FROM t2;

  }

} {1 2 3}

do_test avtrans-5.19 {

  execsql {

    SELECT x FROM t2 WHERE y=2;

  }

} {1}

do_test avtrans-5.20 {

  execsql {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    DROP TABLE t2;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {}

do_test avtrans-5.21 {

  set r [catch {execsql {

    SELECT * FROM t2

  }} msg]

  lappend r $msg

} {1 {no such table: t2}}

do_test avtrans-5.22 {

  execsql {

    ROLLBACK;

    SELECT name fROM sqlite_master 

    WHERE type='table' OR type='index'

    ORDER BY name;

  }

} {i2x i2y t1 t2}

do_test avtrans-5.23 {

  execsql {

    SELECT * FROM t2;

  }

} {1 2 3}

integrity_check avtrans-5.23

# Try to DROP and CREATE tables and indices with the same name

# within a transaction.  Make sure ROLLBACK works.

#

do_test avtrans-6.1 {

  execsql2 {

    INSERT INTO t1 VALUES(1,2,3);

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(p,q,r);

    ROLLBACK;

    SELECT * FROM t1;

  }

} {a 1 b 2 c 3}

do_test avtrans-6.2 {

  execsql2 {

    INSERT INTO t1 VALUES(1,2,3);

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(p,q,r);

    COMMIT;

    SELECT * FROM t1;

  }

} {}

do_test avtrans-6.3 {

  execsql2 {

    INSERT INTO t1 VALUES(1,2,3);

    SELECT * FROM t1;

  }

} {p 1 q 2 r 3}

do_test avtrans-6.4 {

  execsql2 {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(a,b,c);

    INSERT INTO t1 VALUES(4,5,6);

    SELECT * FROM t1;

    DROP TABLE t1;

  }

} {a 4 b 5 c 6}

do_test avtrans-6.5 {

  execsql2 {

    ROLLBACK;

    SELECT * FROM t1;

  }

} {p 1 q 2 r 3}

do_test avtrans-6.6 {

  execsql2 {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(a,b,c);

    INSERT INTO t1 VALUES(4,5,6);

    SELECT * FROM t1;

    DROP TABLE t1;

  }

} {a 4 b 5 c 6}

do_test avtrans-6.7 {

  catchsql {

    COMMIT;

    SELECT * FROM t1;

  }

} {1 {no such table: t1}}

# Repeat on a table with an automatically generated index.

#

do_test avtrans-6.10 {

  execsql2 {

    CREATE TABLE t1(a unique,b,c);

    INSERT INTO t1 VALUES(1,2,3);

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(p unique,q,r);

    ROLLBACK;

    SELECT * FROM t1;

  }

} {a 1 b 2 c 3}

do_test avtrans-6.11 {

  execsql2 {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(p unique,q,r);

    COMMIT;

    SELECT * FROM t1;

  }

} {}

do_test avtrans-6.12 {

  execsql2 {

    INSERT INTO t1 VALUES(1,2,3);

    SELECT * FROM t1;

  }

} {p 1 q 2 r 3}

do_test avtrans-6.13 {

  execsql2 {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(a unique,b,c);

    INSERT INTO t1 VALUES(4,5,6);

    SELECT * FROM t1;

    DROP TABLE t1;

  }

} {a 4 b 5 c 6}

do_test avtrans-6.14 {

  execsql2 {

    ROLLBACK;

    SELECT * FROM t1;

  }

} {p 1 q 2 r 3}

do_test avtrans-6.15 {

  execsql2 {

    BEGIN TRANSACTION;

    DROP TABLE t1;

    CREATE TABLE t1(a unique,b,c);

    INSERT INTO t1 VALUES(4,5,6);

    SELECT * FROM t1;

    DROP TABLE t1;

  }

} {a 4 b 5 c 6}

do_test avtrans-6.16 {

  catchsql {

    COMMIT;

    SELECT * FROM t1;

  }