usr/src/lib/libsqlite/test/table.test

   1
   2 #pragma ident   "%Z%%M% %I%     %E% SMI"
   3
   4 # 2001 September 15
   5 #
   6 # The author disclaims copyright to this source code.  In place of
   7 # a legal notice, here is a blessing:
   8 #
   9 #    May you do good and not evil.
  10 #    May you find forgiveness for yourself and forgive others.
  11 #    May you share freely, never taking more than you give.
  12 #
  13 #***********************************************************************
  14 # This file implements regression tests for SQLite library.  The
  15 # focus of this file is testing the CREATE TABLE statement.
  16 #
  17 # $Id: table.test,v 1.22 2003/01/29 18:46:54 drh Exp $
  18
  19 set testdir [file dirname $argv0]
  20 source $testdir/tester.tcl
  21
  22 # Create a basic table and verify it is added to sqlite_master
  23 #
  24 do_test table-1.1 {
  25   execsql {
  26     CREATE TABLE test1 (
  27       one varchar(10),
  28       two text
  29     )
  30   }
  31   execsql {
  32     SELECT sql FROM sqlite_master WHERE type!='meta'
  33   }
  34 } {{CREATE TABLE test1 (
  35       one varchar(10),
  36       two text
  37     )}}
  38
  39
  40 # Verify the other fields of the sqlite_master file.
  41 #
  42 do_test table-1.3 {
  43   execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
  44 } {test1 test1 table}
  45
  46 # Close and reopen the database.  Verify that everything is
  47 # still the same.
  48 #
  49 do_test table-1.4 {
  50   db close
  51   sqlite db test.db
  52   execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
  53 } {test1 test1 table}
  54
  55 # Drop the database and make sure it disappears.
  56 #
  57 do_test table-1.5 {
  58   execsql {DROP TABLE test1}
  59   execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
  60 } {}
  61
  62 # Close and reopen the database.  Verify that the table is
  63 # still gone.
  64 #
  65 do_test table-1.6 {
  66   db close
  67   sqlite db test.db
  68   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
  69 } {}
  70
  71 # Repeat the above steps, but this time quote the table name.
  72 #
  73 do_test table-1.10 {
  74   execsql {CREATE TABLE "create" (f1 int)}
  75   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
  76 } {create}
  77 do_test table-1.11 {
  78   execsql {DROP TABLE "create"}
  79   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
  80 } {}
  81 do_test table-1.12 {
  82   execsql {CREATE TABLE test1("f1 ho" int)}
  83   execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
  84 } {test1}
  85 do_test table-1.13 {
  86   execsql {DROP TABLE "TEST1"}
  87   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
  88 } {}
  89
  90
  91
  92 # Verify that we cannot make two tables with the same name
  93 #
  94 do_test table-2.1 {
  95   execsql {CREATE TABLE TEST2(one text)}
  96   set v [catch {execsql {CREATE TABLE test2(two text)}} msg]
  97   lappend v $msg
  98 } {1 {table test2 already exists}}
  99 do_test table-2.1b {
 100   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
 101   lappend v $msg
 102 } {1 {table sqlite_master already exists}}
 103 do_test table-2.1c {
 104   db close
 105   sqlite db test.db
 106   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
 107   lappend v $msg
 108 } {1 {table sqlite_master already exists}}
 109 do_test table-2.1d {
 110   execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
 111 } {}
 112
 113 # Verify that we cannot make a table with the same name as an index
 114 #
 115 do_test table-2.2a {
 116   execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
 117   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 118   lappend v $msg
 119 } {1 {there is already an index named test3}}
 120 do_test table-2.2b {
 121   db close
 122   sqlite db test.db
 123   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 124   lappend v $msg
 125 } {1 {there is already an index named test3}}
 126 do_test table-2.2c {
 127   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 128 } {test2 test3}
 129 do_test table-2.2d {
 130   execsql {DROP INDEX test3}
 131   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 132   lappend v $msg
 133 } {0 {}}
 134 do_test table-2.2e {
 135   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 136 } {test2 test3}
 137 do_test table-2.2f {
 138   execsql {DROP TABLE test2; DROP TABLE test3}
 139   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 140 } {}
 141
 142 # Create a table with many field names
 143 #
 144 set big_table \
 145 {CREATE TABLE big(
 146   f1 varchar(20),
 147   f2 char(10),
 148   f3 varchar(30) primary key,
 149   f4 text,
 150   f5 text,
 151   f6 text,
 152   f7 text,
 153   f8 text,
 154   f9 text,
 155   f10 text,
 156   f11 text,
 157   f12 text,
 158   f13 text,
 159   f14 text,
 160   f15 text,
 161   f16 text,
 162   f17 text,
 163   f18 text,
 164   f19 text,
 165   f20 text
 166 )}
 167 do_test table-3.1 {
 168   execsql $big_table
 169   execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
 170 } \{$big_table\}
 171 do_test table-3.2 {
 172   set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
 173   lappend v $msg
 174 } {1 {table BIG already exists}}
 175 do_test table-3.3 {
 176   set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
 177   lappend v $msg
 178 } {1 {table biG already exists}}
 179 do_test table-3.4 {
 180   set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
 181   lappend v $msg
 182 } {1 {table bIg already exists}}
 183 do_test table-3.5 {
 184   db close
 185   sqlite db test.db
 186   set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
 187   lappend v $msg
 188 } {1 {table Big already exists}}
 189 do_test table-3.6 {
 190   execsql {DROP TABLE big}
 191   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 192 } {}
 193
 194 # Try creating large numbers of tables
 195 #
 196 set r {}
 197 for {set i 1} {$i<=100} {incr i} {
 198   lappend r [format test%03d $i]
 199 }
 200 do_test table-4.1 {
 201   for {set i 1} {$i<=100} {incr i} {
 202     set sql "CREATE TABLE [format test%03d $i] ("
 203     for {set k 1} {$k<$i} {incr k} {
 204       append sql "field$k text,"
 205     }
 206     append sql "last_field text)"
 207     execsql $sql
 208   }
 209   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 210 } $r
 211 do_test table-4.1b {
 212   db close
 213   sqlite db test.db
 214   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 215 } $r
 216
 217 # Drop the even numbered tables
 218 #
 219 set r {}
 220 for {set i 1} {$i<=100} {incr i 2} {
 221   lappend r [format test%03d $i]
 222 }
 223 do_test table-4.2 {
 224   for {set i 2} {$i<=100} {incr i 2} {
 225     # if {$i==38} {execsql {pragma vdbe_trace=on}}
 226     set sql "DROP TABLE [format TEST%03d $i]"
 227     execsql $sql
 228   }
 229   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 230 } $r
 231 #exit
 232
 233 # Drop the odd number tables
 234 #
 235 do_test table-4.3 {
 236   for {set i 1} {$i<=100} {incr i 2} {
 237     set sql "DROP TABLE [format test%03d $i]"
 238     execsql $sql
 239   }
 240   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 241 } {}
 242
 243 # Try to drop a table that does not exist
 244 #
 245 do_test table-5.1 {
 246   set v [catch {execsql {DROP TABLE test009}} msg]
 247   lappend v $msg
 248 } {1 {no such table: test009}}
 249
 250 # Try to drop sqlite_master
 251 #
 252 do_test table-5.2 {
 253   set v [catch {execsql {DROP TABLE sqlite_master}} msg]
 254   lappend v $msg
 255 } {1 {table sqlite_master may not be dropped}}
 256
 257 # Make sure an EXPLAIN does not really create a new table
 258 #
 259 do_test table-5.3 {
 260   execsql {EXPLAIN CREATE TABLE test1(f1 int)}
 261   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 262 } {}
 263
 264 # Make sure an EXPLAIN does not really drop an existing table
 265 #
 266 do_test table-5.4 {
 267   execsql {CREATE TABLE test1(f1 int)}
 268   execsql {EXPLAIN DROP TABLE test1}
 269   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 270 } {test1}
 271
 272 # Create a table with a goofy name
 273 #
 274 #do_test table-6.1 {
 275 #  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
 276 #  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
 277 #  set list [glob -nocomplain testdb/spaces*.tbl]
 278 #} {testdb/spaces+in+this+name+.tbl}
 279
 280 # Try using keywords as table names or column names.
 281 #
 282 do_test table-7.1 {
 283   set v [catch {execsql {
 284     CREATE TABLE weird(
 285       desc text,
 286       asc text,
 287       explain int,
 288       [14_vac] boolean,
 289       fuzzy_dog_12 varchar(10),
 290       begin blob,
 291       end clob
 292     )
 293   }} msg]
 294   lappend v $msg
 295 } {0 {}}
 296 do_test table-7.2 {
 297   execsql {
 298     INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
 299     SELECT * FROM weird;
 300   }
 301 } {a b 9 0 xyz hi y'all}
 302 do_test table-7.3 {
 303   execsql2 {
 304     SELECT * FROM weird;
 305   }
 306 } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 307
 308 # Try out the CREATE TABLE AS syntax
 309 #
 310 do_test table-8.1 {
 311   execsql2 {
 312     CREATE TABLE t2 AS SELECT * FROM weird;
 313     SELECT * FROM t2;
 314   }
 315 } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 316 do_test table-8.1.1 {
 317   execsql {
 318     SELECT sql FROM sqlite_master WHERE name='t2';
 319   }
 320 } {{CREATE TABLE t2(
 321   'desc',
 322   'asc',
 323   'explain',
 324   '14_vac',
 325   fuzzy_dog_12,
 326   'begin',
 327   'end'
 328 )}}
 329 do_test table-8.2 {
 330   execsql {
 331     CREATE TABLE 't3''xyz'(a,b,c);
 332     INSERT INTO [t3'xyz] VALUES(1,2,3);
 333     SELECT * FROM [t3'xyz];
 334   }
 335 } {1 2 3}
 336 do_test table-8.3 {
 337   execsql2 {
 338     CREATE TABLE [t4'abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3'xyz];
 339     SELECT * FROM [t4'abc];
 340   }
 341 } {cnt 1 max(b+c) 5}
 342 do_test table-8.3.1 {
 343   execsql {
 344     SELECT sql FROM sqlite_master WHERE name='t4''abc'
 345   }
 346 } {{CREATE TABLE 't4''abc'(cnt,'max(b+c)')}}
 347 do_test table-8.4 {
 348   execsql2 {
 349     CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz];
 350     SELECT * FROM t5;
 351   }
 352 } {y'all 1}
 353 do_test table-8.5 {
 354   db close
 355   sqlite db test.db
 356   execsql2 {
 357     SELECT * FROM [t4'abc];
 358   }
 359 } {cnt 1 max(b+c) 5}
 360 do_test table-8.6 {
 361   execsql2 {
 362     SELECT * FROM t2;
 363   }
 364 } {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 365 do_test table-8.7 {
 366   catchsql {
 367     SELECT * FROM t5;
 368   }
 369 } {1 {no such table: t5}}
 370 do_test table-8.8 {
 371   catchsql {
 372     CREATE TABLE t5 AS SELECT * FROM no_such_table;
 373   }
 374 } {1 {no such table: no_such_table}}
 375
 376 # Make sure we cannot have duplicate column names within a table.
 377 #
 378 do_test table-9.1 {
 379   catchsql {
 380     CREATE TABLE t6(a,b,a);
 381   }
 382 } {1 {duplicate column name: a}}
 383
 384 # Check the foreign key syntax.
 385 #
 386 do_test table-10.1 {
 387   catchsql {
 388     CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
 389     INSERT INTO t6 VALUES(NULL);
 390   }
 391 } {1 {t6.a may not be NULL}}
 392 do_test table-10.2 {
 393   catchsql {
 394     DROP TABLE t6;
 395     CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
 396   }
 397 } {0 {}}
 398 do_test table-10.3 {
 399   catchsql {
 400     DROP TABLE t6;
 401     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
 402   }
 403 } {0 {}}
 404 do_test table-10.4 {
 405   catchsql {
 406     DROP TABLE t6;
 407     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
 408   }
 409 } {0 {}}
 410 do_test table-10.5 {
 411   catchsql {
 412     DROP TABLE t6;
 413     CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
 414   }
 415 } {0 {}}
 416 do_test table-10.6 {
 417   catchsql {
 418     DROP TABLE t6;
 419     CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
 420   }
 421 } {0 {}}
 422 do_test table-10.7 {
 423   catchsql {
 424     DROP TABLE t6;
 425     CREATE TABLE t6(a,
 426       FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
 427     );
 428   }
 429 } {0 {}}
 430 do_test table-10.8 {
 431   catchsql {
 432     DROP TABLE t6;
 433     CREATE TABLE t6(a,b,c,
 434       FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
 435         ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 436     );
 437   }
 438 } {0 {}}
 439 do_test table-10.9 {
 440   catchsql {
 441     DROP TABLE t6;
 442     CREATE TABLE t6(a,b,c,
 443       FOREIGN KEY (b,c) REFERENCES t4(x)
 444     );
 445   }
 446 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 447 do_test table-10.10 {
 448   catchsql {DROP TABLE t6}
 449   catchsql {
 450     CREATE TABLE t6(a,b,c,
 451       FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
 452     );
 453   }
 454 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 455 do_test table-10.11 {
 456   catchsql {DROP TABLE t6}
 457   catchsql {
 458     CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
 459   }
 460 } {1 {foreign key on c should reference only one column of table t4}}
 461 do_test table-10.12 {
 462   catchsql {DROP TABLE t6}
 463   catchsql {
 464     CREATE TABLE t6(a,b,c,
 465       FOREIGN KEY (b,x) REFERENCES t4(x,y)
 466     );
 467   }
 468 } {1 {unknown column "x" in foreign key definition}}
 469 do_test table-10.13 {
 470   catchsql {DROP TABLE t6}
 471   catchsql {
 472     CREATE TABLE t6(a,b,c,
 473       FOREIGN KEY (x,b) REFERENCES t4(x,y)
 474     );
 475   }
 476 } {1 {unknown column "x" in foreign key definition}}
 477
 478
 479 # Test for the "typeof" function.
 480 #
 481 do_test table-11.1 {
 482   execsql {
 483     CREATE TABLE t7(
 484        a integer primary key,
 485        b number(5,10),
 486        c character varying (8),
 487        d VARCHAR(9),
 488        e clob,
 489        f BLOB,
 490        g Text,
 491        h
 492     );
 493     INSERT INTO t7(a) VALUES(1);
 494     SELECT typeof(a), typeof(b), typeof(c), typeof(d),
 495            typeof(e), typeof(f), typeof(g), typeof(h)
 496     FROM t7 LIMIT 1;
 497   }
 498 } {numeric numeric text text text text text numeric}
 499 do_test table-11.2 {
 500   execsql {
 501     SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
 502     FROM t7 LIMIT 1;
 503   }
 504 } {numeric text numeric text}
 505
 506 finish_test