sl@0: # 2007 May 02
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is testing that it is OK to create new tables
sl@0: # and indices while creating existing tables and indices.
sl@0: #
sl@0: # $Id: createtab.test,v 1.3 2007/09/12 17:01:45 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable autovacuum {
sl@0:   set upperBound 2
sl@0: } else {
sl@0:   set upperBound 0
sl@0: }
sl@0: 
sl@0: # Run these tests for all possible values of autovacuum.
sl@0: #
sl@0: for {set av 0} {$av<=$upperBound} {incr av} {
sl@0:   db close
sl@0:   file delete -force test.db test.db-journal
sl@0:   sqlite3 db test.db
sl@0: 
sl@0:   # Create a table that spans multiple pages.  It is important
sl@0:   # that part of the database be in pages beyond the root page.
sl@0:   #
sl@0:   do_test createtab-$av.1 {
sl@0:     execsql "PRAGMA auto_vacuum=$av"
sl@0:     execsql {
sl@0:       PRAGMA page_size=1024;
sl@0:       CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
sl@0:       INSERT INTO t1 VALUES(1, hex(randomblob(200)));
sl@0:       INSERT INTO t1 VALUES(2, hex(randomblob(200)));
sl@0:       INSERT INTO t1 VALUES(3, hex(randomblob(200)));
sl@0:       INSERT INTO t1 VALUES(4, hex(randomblob(200)));
sl@0:       SELECT count(*) FROM t1;
sl@0:     }
sl@0:   } {4}
sl@0: 
sl@0:   set isUtf16 0
sl@0:   ifcapable utf16 { 
sl@0:     set isUtf16 [expr {[execsql {PRAGMA encoding}] != "UTF-8"}]
sl@0:   }
sl@0: 
sl@0:   do_test createtab-$av.2 {
sl@0:     file size test.db
sl@0:   } [expr {1024*(4+($av!=0)+(${isUtf16}*2))}]
sl@0:   
sl@0:   # Start reading the table
sl@0:   #
sl@0:   do_test createtab-$av.3 {
sl@0:     set STMT [sqlite3_prepare db {SELECT x FROM t1} -1 TAIL]
sl@0:     sqlite3_step $STMT
sl@0:   } {SQLITE_ROW}
sl@0:   do_test createtab-$av.4 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {1}
sl@0:   
sl@0:   # While still reading the table, create a new table.
sl@0:   #
sl@0:   do_test createtab-$av.5 {
sl@0:     execsql {
sl@0:       CREATE TABLE t2(a,b);
sl@0:       INSERT INTO t2 VALUES(1,2);
sl@0:       SELECT * FROM t2;
sl@0:     }
sl@0:   } {1 2}
sl@0:   
sl@0:   # Continue reading the original table.
sl@0:   #
sl@0:   do_test createtab-$av.6 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {1}
sl@0:   do_test createtab-$av.7 {
sl@0:     sqlite3_step $STMT
sl@0:   } {SQLITE_ROW}
sl@0:   do_test createtab-$av.8 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {2}
sl@0:   
sl@0:   # Do another cycle of creating a new database table while contining
sl@0:   # to read the original table.
sl@0:   #
sl@0:   do_test createtab-$av.11 {
sl@0:     execsql {
sl@0:       CREATE TABLE t3(a,b);
sl@0:       INSERT INTO t3 VALUES(4,5);
sl@0:       SELECT * FROM t3;
sl@0:     }
sl@0:   } {4 5}
sl@0:   do_test createtab-$av.12 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {2}
sl@0:   do_test createtab-$av.13 {
sl@0:     sqlite3_step $STMT
sl@0:   } {SQLITE_ROW}
sl@0:   do_test createtab-$av.14 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {3}
sl@0:   
sl@0:   # One more cycle.
sl@0:   #
sl@0:   do_test createtab-$av.21 {
sl@0:     execsql {
sl@0:       CREATE TABLE t4(a,b);
sl@0:       INSERT INTO t4 VALUES('abc','xyz');
sl@0:       SELECT * FROM t4;
sl@0:     }
sl@0:   } {abc xyz}
sl@0:   do_test createtab-$av.22 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {3}
sl@0:   do_test createtab-$av.23 {
sl@0:     sqlite3_step $STMT
sl@0:   } {SQLITE_ROW}
sl@0:   do_test createtab-$av.24 {
sl@0:     sqlite3_column_int $STMT 0
sl@0:   } {4}
sl@0:   
sl@0:   # Finish reading.  Do an integrity check on the database.
sl@0:   #
sl@0:   do_test createtab-$av.30 {
sl@0:     sqlite3_step $STMT
sl@0:   } {SQLITE_DONE}
sl@0:   do_test createtab-$av.31 {
sl@0:     sqlite3_finalize $STMT
sl@0:   } {SQLITE_OK}
sl@0:   do_test createtab-$av.32 {
sl@0:     execsql {
sl@0:       SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1
sl@0:     }
sl@0:   } {t1 t2 t3 t4}
sl@0:   integrity_check createtab-$av.40
sl@0: 
sl@0: }
sl@0:   
sl@0: finish_test