gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/createtab.test (about) 1 # 2007 May 02 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 file is testing that it is OK to create new tables 13 # and indices while creating existing tables and indices. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 ifcapable autovacuum { 20 set upperBound 2 21 } else { 22 set upperBound 0 23 } 24 25 # Run these tests for all possible values of autovacuum. 26 # 27 for {set av 0} {$av<=$upperBound} {incr av} { 28 db close 29 forcedelete test.db test.db-journal 30 sqlite3 db test.db 31 32 # Create a table that spans multiple pages. It is important 33 # that part of the database be in pages beyond the root page. 34 # 35 do_test createtab-$av.1 { 36 execsql "PRAGMA auto_vacuum=$av" 37 execsql { 38 PRAGMA page_size=1024; 39 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 40 INSERT INTO t1 VALUES(1, hex(randomblob(200))); 41 INSERT INTO t1 VALUES(2, hex(randomblob(200))); 42 INSERT INTO t1 VALUES(3, hex(randomblob(200))); 43 INSERT INTO t1 VALUES(4, hex(randomblob(200))); 44 SELECT count(*) FROM t1; 45 } 46 } {4} 47 48 set isUtf16 0 49 ifcapable utf16 { 50 set isUtf16 [expr {[execsql {PRAGMA encoding}] != "UTF-8"}] 51 } 52 53 do_test createtab-$av.2 { 54 file size test.db 55 } [expr {1024*(4+($av!=0)+(${isUtf16}*2))}] 56 57 # Start reading the table 58 # 59 do_test createtab-$av.3 { 60 set STMT [sqlite3_prepare db {SELECT x FROM t1} -1 TAIL] 61 sqlite3_step $STMT 62 } {SQLITE_ROW} 63 do_test createtab-$av.4 { 64 sqlite3_column_int $STMT 0 65 } {1} 66 67 # While still reading the table, create a new table. 68 # 69 do_test createtab-$av.5 { 70 execsql { 71 CREATE TABLE t2(a,b); 72 INSERT INTO t2 VALUES(1,2); 73 SELECT * FROM t2; 74 } 75 } {1 2} 76 77 # Continue reading the original table. 78 # 79 do_test createtab-$av.6 { 80 sqlite3_column_int $STMT 0 81 } {1} 82 do_test createtab-$av.7 { 83 sqlite3_step $STMT 84 } {SQLITE_ROW} 85 do_test createtab-$av.8 { 86 sqlite3_column_int $STMT 0 87 } {2} 88 89 # Do another cycle of creating a new database table while contining 90 # to read the original table. 91 # 92 do_test createtab-$av.11 { 93 execsql { 94 CREATE TABLE t3(a,b); 95 INSERT INTO t3 VALUES(4,5); 96 SELECT * FROM t3; 97 } 98 } {4 5} 99 do_test createtab-$av.12 { 100 sqlite3_column_int $STMT 0 101 } {2} 102 do_test createtab-$av.13 { 103 sqlite3_step $STMT 104 } {SQLITE_ROW} 105 do_test createtab-$av.14 { 106 sqlite3_column_int $STMT 0 107 } {3} 108 109 # One more cycle. 110 # 111 do_test createtab-$av.21 { 112 execsql { 113 CREATE TABLE t4(a,b); 114 INSERT INTO t4 VALUES('abc','xyz'); 115 SELECT * FROM t4; 116 } 117 } {abc xyz} 118 do_test createtab-$av.22 { 119 sqlite3_column_int $STMT 0 120 } {3} 121 do_test createtab-$av.23 { 122 sqlite3_step $STMT 123 } {SQLITE_ROW} 124 do_test createtab-$av.24 { 125 sqlite3_column_int $STMT 0 126 } {4} 127 128 # Finish reading. Do an integrity check on the database. 129 # 130 do_test createtab-$av.30 { 131 sqlite3_step $STMT 132 } {SQLITE_DONE} 133 do_test createtab-$av.31 { 134 sqlite3_finalize $STMT 135 } {SQLITE_OK} 136 do_test createtab-$av.32 { 137 execsql { 138 SELECT name FROM sqlite_master WHERE type='table' ORDER BY 1 139 } 140 } {t1 t2 t3 t4} 141 integrity_check createtab-$av.40 142 143 } 144 145 # 2019-03-31 Ensure that a proper error is returned for an index 146 # with too many columns. 147 # 148 do_test createtab-3.1 { 149 db eval {DROP TABLE IF EXISTS t1;} 150 set sql "CREATE TABLE t1(x,UNIQUE(x[string repeat ,x 100000]))" 151 catchsql $sql 152 } {1 {too many columns in index}} 153 154 finish_test