xref: /sqlite-3.40.0/test/createtab.test (revision 8fe25c64) 
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19ifcapable autovacuum {
20  set upperBound 2
21} else {
22  set upperBound 0
23}
24
25# Run these tests for all possible values of autovacuum.
26#
27for {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#
148do_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
154finish_test
155