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