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