1# Copyright (c) 1999, 2000 D. Richard Hipp 2# 3# This program is free software; you can redistribute it and/or 4# modify it under the terms of the GNU General Public 5# License as published by the Free Software Foundation; either 6# version 2 of the License, or (at your option) any later version. 7# 8# This program is distributed in the hope that it will be useful, 9# but WITHOUT ANY WARRANTY; without even the implied warranty of 10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 11# General Public License for more details. 12# 13# You should have received a copy of the GNU General Public 14# License along with this library; if not, write to the 15# Free Software Foundation, Inc., 59 Temple Place - Suite 330, 16# Boston, MA 02111-1307, USA. 17# 18# Author contact information: 19# [email protected] 20# http://www.hwaci.com/drh/ 21# 22#*********************************************************************** 23# This file implements regression tests for SQLite library. The 24# focus of this file is testing the CREATE TABLE statement. 25# 26# $Id: table.test,v 1.6 2000/08/02 13:47:43 drh Exp $ 27 28set testdir [file dirname $argv0] 29source $testdir/tester.tcl 30 31# Create a basic table and verify it is added to sqlite_master 32# 33do_test table-1.1 { 34 execsql { 35 CREATE TABLE test1 ( 36 one varchar(10), 37 two text 38 ) 39 } 40 execsql { 41 SELECT sql FROM sqlite_master WHERE type!='meta' 42 } 43} {{CREATE TABLE test1 ( 44 one varchar(10), 45 two text 46 )}} 47 48# Verify that both table files exists in the database directory 49# 50do_test table-1.2 { 51 execsql {INSERT INTO test1 VALUES('hi', 'y''all')} 52 lsort [glob -nocomplain testdb/*.tbl] 53} {testdb/sqlite_master.tbl testdb/test1.tbl} 54 55# Verify the other fields of the sqlite_master file. 56# 57do_test table-1.3 { 58 execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'} 59} {test1 test1 table} 60 61# Close and reopen the database. Verify that everything is 62# still the same. 63# 64do_test table-1.4 { 65 db close 66 sqlite db testdb 67 execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'} 68} {test1 test1 table} 69 70# Drop the database and make sure it disappears. 71# 72do_test table-1.5 { 73 execsql {DROP TABLE test1} 74 execsql {SELECT * FROM sqlite_master WHERE type!='meta'} 75} {} 76 77# Verify that the file associated with the database is gone. 78# 79do_test table-1.5 { 80 lsort [glob -nocomplain testdb/*.tbl] 81} {testdb/sqlite_master.tbl} 82 83# Close and reopen the database. Verify that the table is 84# still gone. 85# 86do_test table-1.6 { 87 db close 88 sqlite db testdb 89 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 90} {} 91 92# Repeat the above steps, but this time quote the table name. 93# 94do_test table-1.10 { 95 execsql {CREATE TABLE "create" (f1 int)} 96 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 97} {create} 98do_test table-1.11 { 99 execsql {DROP TABLE "create"} 100 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 101} {} 102do_test table-1.12 { 103 execsql {CREATE TABLE test1("f1 ho" int)} 104 execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'} 105} {test1} 106do_test table-1.13 { 107 execsql {DROP TABLE "TEST1"} 108 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 109} {} 110 111 112 113# Verify that we cannot make two tables with the same name 114# 115do_test table-2.1 { 116 execsql {CREATE TABLE TEST2(one text)} 117 set v [catch {execsql {CREATE TABLE test2(two text)}} msg] 118 lappend v $msg 119} {1 {table test2 already exists}} 120do_test table-2.1b { 121 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 122 lappend v $msg 123} {1 {table sqlite_master already exists}} 124do_test table-2.1c { 125 db close 126 sqlite db testdb 127 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 128 lappend v $msg 129} {1 {table sqlite_master already exists}} 130do_test table-2.1d { 131 execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'} 132} {} 133 134# Verify that we cannot make a table with the same name as an index 135# 136do_test table-2.2a { 137 execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)} 138 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 139 lappend v $msg 140} {1 {there is already an index named test3}} 141do_test table-2.2b { 142 db close 143 sqlite db testdb 144 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 145 lappend v $msg 146} {1 {there is already an index named test3}} 147do_test table-2.2c { 148 execsql {DROP INDEX test3} 149 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 150 lappend v $msg 151} {0 {}} 152do_test table-2.2d { 153 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 154} {test2 test3} 155do_test table-2.2e { 156 execsql {DROP TABLE test2; DROP TABLE test3} 157 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 158} {} 159 160# Create a table with many field names 161# 162set big_table \ 163{CREATE TABLE big( 164 f1 varchar(20), 165 f2 char(10), 166 f3 varchar(30) primary key, 167 f4 text, 168 f5 text, 169 f6 text, 170 f7 text, 171 f8 text, 172 f9 text, 173 f10 text, 174 f11 text, 175 f12 text, 176 f13 text, 177 f14 text, 178 f15 text, 179 f16 text, 180 f17 text, 181 f18 text, 182 f19 text, 183 f20 text 184)} 185do_test table-3.1 { 186 execsql $big_table 187 execsql {SELECT sql FROM sqlite_master WHERE type!='meta'} 188} \{$big_table\} 189do_test table-3.2 { 190 set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg] 191 lappend v $msg 192} {1 {table BIG already exists}} 193do_test table-3.3 { 194 set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg] 195 lappend v $msg 196} {1 {table biG already exists}} 197do_test table-3.4 { 198 set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg] 199 lappend v $msg 200} {1 {table bIg already exists}} 201do_test table-3.5 { 202 db close 203 sqlite db testdb 204 set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg] 205 lappend v $msg 206} {1 {table Big already exists}} 207do_test table-3.6 { 208 execsql {DROP TABLE big} 209 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 210} {} 211 212# Try creating large numbers of tables 213# 214set r {} 215for {set i 1} {$i<=100} {incr i} { 216 lappend r test$i 217} 218do_test table-4.1 { 219 for {set i 1} {$i<=100} {incr i} { 220 set sql "CREATE TABLE test$i (" 221 for {set k 1} {$k<$i} {incr k} { 222 append sql "field$k text," 223 } 224 append sql "last_field text)" 225 execsql $sql 226 } 227 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 228} $r 229do_test table-4.1b { 230 db close 231 sqlite db testdb 232 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 233} $r 234 235# Drop the even number tables 236# 237set r {} 238for {set i 1} {$i<=100} {incr i 2} { 239 lappend r test$i 240} 241do_test table-4.2 { 242 for {set i 2} {$i<=100} {incr i 2} { 243 set sql "DROP TABLE TEST$i" 244 execsql $sql 245 } 246 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 247} $r 248 249# Drop the odd number tables 250# 251do_test table-4.3 { 252 for {set i 1} {$i<=100} {incr i 2} { 253 set sql "DROP TABLE test$i" 254 execsql $sql 255 } 256 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 257} {} 258 259# Try to drop a table that does not exist 260# 261do_test table-5.1 { 262 set v [catch {execsql {DROP TABLE test9}} msg] 263 lappend v $msg 264} {1 {no such table: test9}} 265 266# Try to drop sqlite_master 267# 268do_test table-5.2 { 269 set v [catch {execsql {DROP TABLE sqlite_master}} msg] 270 lappend v $msg 271} {1 {table sqlite_master may not be dropped}} 272 273# Make sure an EXPLAIN does not really create a new table 274# 275do_test table-5.3 { 276 execsql {EXPLAIN CREATE TABLE test1(f1 int)} 277 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 278} {} 279 280# Make sure an EXPLAIN does not really drop an existing table 281# 282do_test table-5.4 { 283 execsql {CREATE TABLE test1(f1 int)} 284 execsql {EXPLAIN DROP TABLE test1} 285 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 286} {test1} 287 288# Create a table with a goofy name 289# 290do_test table-6.1 { 291 execsql {CREATE TABLE 'Spaces In This Name!'(x int)} 292 execsql {INSERT INTO 'spaces in this name!' VALUES(1)} 293 set list [glob -nocomplain testdb/spaces*.tbl] 294} {testdb/spaces+in+this+name+.tbl} 295 296finish_test 297