sqlite3/test/table.test

   1 # 2001 September 15
   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 the CREATE TABLE statement.
  13 #
  14 # $Id: table.test,v 1.39 2005/03/29 03:11:00 danielk1977 Exp $
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Create a basic table and verify it is added to sqlite_master
  20 #
  21 do_test table-1.1 {
  22   execsql {
  23     CREATE TABLE test1 (
  24       one varchar(10),
  25       two text
  26     )
  27   }
  28   execsql {
  29     SELECT sql FROM sqlite_master WHERE type!='meta'
  30   }
  31 } {{CREATE TABLE test1 (
  32       one varchar(10),
  33       two text
  34     )}}
  35
  36
  37 # Verify the other fields of the sqlite_master file.
  38 #
  39 do_test table-1.3 {
  40   execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
  41 } {test1 test1 table}
  42
  43 # Close and reopen the database.  Verify that everything is
  44 # still the same.
  45 #
  46 do_test table-1.4 {
  47   db close
  48   sqlite3 db test.db
  49   execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
  50 } {test1 test1 table}
  51
  52 # Drop the database and make sure it disappears.
  53 #
  54 do_test table-1.5 {
  55   execsql {DROP TABLE test1}
  56   execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
  57 } {}
  58
  59 # Close and reopen the database.  Verify that the table is
  60 # still gone.
  61 #
  62 do_test table-1.6 {
  63   db close
  64   sqlite3 db test.db
  65   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
  66 } {}
  67
  68 # Repeat the above steps, but this time quote the table name.
  69 #
  70 do_test table-1.10 {
  71   execsql {CREATE TABLE "create" (f1 int)}
  72   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
  73 } {create}
  74 do_test table-1.11 {
  75   execsql {DROP TABLE "create"}
  76   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
  77 } {}
  78 do_test table-1.12 {
  79   execsql {CREATE TABLE test1("f1 ho" int)}
  80   execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
  81 } {test1}
  82 do_test table-1.13 {
  83   execsql {DROP TABLE "TEST1"}
  84   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
  85 } {}
  86
  87
  88
  89 # Verify that we cannot make two tables with the same name
  90 #
  91 do_test table-2.1 {
  92   execsql {CREATE TABLE TEST2(one text)}
  93   set v [catch {execsql {CREATE TABLE test2(two text)}} msg]
  94   lappend v $msg
  95 } {1 {table test2 already exists}}
  96 do_test table-2.1b {
  97   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
  98   lappend v $msg
  99 } {1 {object name reserved for internal use: sqlite_master}}
 100 do_test table-2.1c {
 101   db close
 102   sqlite3 db test.db
 103   set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
 104   lappend v $msg
 105 } {1 {object name reserved for internal use: sqlite_master}}
 106 do_test table-2.1d {
 107   execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
 108 } {}
 109
 110 # Verify that we cannot make a table with the same name as an index
 111 #
 112 do_test table-2.2a {
 113   execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
 114   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 115   lappend v $msg
 116 } {1 {there is already an index named test3}}
 117 do_test table-2.2b {
 118   db close
 119   sqlite3 db test.db
 120   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 121   lappend v $msg
 122 } {1 {there is already an index named test3}}
 123 do_test table-2.2c {
 124   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 125 } {test2 test3}
 126 do_test table-2.2d {
 127   execsql {DROP INDEX test3}
 128   set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
 129   lappend v $msg
 130 } {0 {}}
 131 do_test table-2.2e {
 132   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 133 } {test2 test3}
 134 do_test table-2.2f {
 135   execsql {DROP TABLE test2; DROP TABLE test3}
 136   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 137 } {}
 138
 139 # Create a table with many field names
 140 #
 141 set big_table \
 142 {CREATE TABLE big(
 143   f1 varchar(20),
 144   f2 char(10),
 145   f3 varchar(30) primary key,
 146   f4 text,
 147   f5 text,
 148   f6 text,
 149   f7 text,
 150   f8 text,
 151   f9 text,
 152   f10 text,
 153   f11 text,
 154   f12 text,
 155   f13 text,
 156   f14 text,
 157   f15 text,
 158   f16 text,
 159   f17 text,
 160   f18 text,
 161   f19 text,
 162   f20 text
 163 )}
 164 do_test table-3.1 {
 165   execsql $big_table
 166   execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
 167 } \{$big_table\}
 168 do_test table-3.2 {
 169   set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
 170   lappend v $msg
 171 } {1 {table BIG already exists}}
 172 do_test table-3.3 {
 173   set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
 174   lappend v $msg
 175 } {1 {table biG already exists}}
 176 do_test table-3.4 {
 177   set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
 178   lappend v $msg
 179 } {1 {table bIg already exists}}
 180 do_test table-3.5 {
 181   db close
 182   sqlite3 db test.db
 183   set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
 184   lappend v $msg
 185 } {1 {table Big already exists}}
 186 do_test table-3.6 {
 187   execsql {DROP TABLE big}
 188   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 189 } {}
 190
 191 # Try creating large numbers of tables
 192 #
 193 set r {}
 194 for {set i 1} {$i<=100} {incr i} {
 195   lappend r [format test%03d $i]
 196 }
 197 do_test table-4.1 {
 198   for {set i 1} {$i<=100} {incr i} {
 199     set sql "CREATE TABLE [format test%03d $i] ("
 200     for {set k 1} {$k<$i} {incr k} {
 201       append sql "field$k text,"
 202     }
 203     append sql "last_field text)"
 204     execsql $sql
 205   }
 206   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 207 } $r
 208 do_test table-4.1b {
 209   db close
 210   sqlite3 db test.db
 211   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 212 } $r
 213
 214 # Drop the even numbered tables
 215 #
 216 set r {}
 217 for {set i 1} {$i<=100} {incr i 2} {
 218   lappend r [format test%03d $i]
 219 }
 220 do_test table-4.2 {
 221   for {set i 2} {$i<=100} {incr i 2} {
 222     # if {$i==38} {execsql {pragma vdbe_trace=on}}
 223     set sql "DROP TABLE [format TEST%03d $i]"
 224     execsql $sql
 225   }
 226   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 227 } $r
 228 #exit
 229
 230 # Drop the odd number tables
 231 #
 232 do_test table-4.3 {
 233   for {set i 1} {$i<=100} {incr i 2} {
 234     set sql "DROP TABLE [format test%03d $i]"
 235     execsql $sql
 236   }
 237   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 238 } {}
 239
 240 # Try to drop a table that does not exist
 241 #
 242 do_test table-5.1 {
 243   set v [catch {execsql {DROP TABLE test009}} msg]
 244   lappend v $msg
 245 } {1 {no such table: test009}}
 246
 247 # Try to drop sqlite_master
 248 #
 249 do_test table-5.2 {
 250   set v [catch {execsql {DROP TABLE sqlite_master}} msg]
 251   lappend v $msg
 252 } {1 {table sqlite_master may not be dropped}}
 253
 254 # Make sure an EXPLAIN does not really create a new table
 255 #
 256 do_test table-5.3 {
 257   ifcapable {explain} {
 258     execsql {EXPLAIN CREATE TABLE test1(f1 int)}
 259   }
 260   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 261 } {}
 262
 263 # Make sure an EXPLAIN does not really drop an existing table
 264 #
 265 do_test table-5.4 {
 266   execsql {CREATE TABLE test1(f1 int)}
 267   ifcapable {explain} {
 268     execsql {EXPLAIN DROP TABLE test1}
 269   }
 270   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 271 } {test1}
 272
 273 # Create a table with a goofy name
 274 #
 275 #do_test table-6.1 {
 276 #  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
 277 #  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
 278 #  set list [glob -nocomplain testdb/spaces*.tbl]
 279 #} {testdb/spaces+in+this+name+.tbl}
 280
 281 # Try using keywords as table names or column names.
 282 #
 283 do_test table-7.1 {
 284   set v [catch {execsql {
 285     CREATE TABLE weird(
 286       desc text,
 287       asc text,
 288       key int,
 289       [14_vac] boolean,
 290       fuzzy_dog_12 varchar(10),
 291       begin blob,
 292       end clob
 293     )
 294   }} msg]
 295   lappend v $msg
 296 } {0 {}}
 297 do_test table-7.2 {
 298   execsql {
 299     INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
 300     SELECT * FROM weird;
 301   }
 302 } {a b 9 0 xyz hi y'all}
 303 do_test table-7.3 {
 304   execsql2 {
 305     SELECT * FROM weird;
 306   }
 307 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 308
 309 # Try out the CREATE TABLE AS syntax
 310 #
 311 do_test table-8.1 {
 312   execsql2 {
 313     CREATE TABLE t2 AS SELECT * FROM weird;
 314     SELECT * FROM t2;
 315   }
 316 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 317 do_test table-8.1.1 {
 318   execsql {
 319     SELECT sql FROM sqlite_master WHERE name='t2';
 320   }
 321 } {{CREATE TABLE t2(
 322   "desc" text,
 323   "asc" text,
 324   "key" int,
 325   "14_vac" boolean,
 326   fuzzy_dog_12 varchar(10),
 327   "begin" blob,
 328   "end" clob
 329 )}}
 330 do_test table-8.2 {
 331   execsql {
 332     CREATE TABLE "t3""xyz"(a,b,c);
 333     INSERT INTO [t3"xyz] VALUES(1,2,3);
 334     SELECT * FROM [t3"xyz];
 335   }
 336 } {1 2 3}
 337 do_test table-8.3 {
 338   execsql2 {
 339     CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
 340     SELECT * FROM [t4"abc];
 341   }
 342 } {cnt 1 max(b+c) 5}
 343
 344 # Update for v3: The declaration type of anything except a column is now a
 345 # NULL pointer, so the created table has no column types. (Changed result
 346 # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
 347 do_test table-8.3.1 {
 348   execsql {
 349     SELECT sql FROM sqlite_master WHERE name='t4"abc'
 350   }
 351 } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
 352
 353 ifcapable tempdb {
 354   do_test table-8.4 {
 355     execsql2 {
 356       CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
 357       SELECT * FROM t5;
 358     }
 359   } {y'all 1}
 360 }
 361
 362 do_test table-8.5 {
 363   db close
 364   sqlite3 db test.db
 365   execsql2 {
 366     SELECT * FROM [t4"abc];
 367   }
 368 } {cnt 1 max(b+c) 5}
 369 do_test table-8.6 {
 370   execsql2 {
 371     SELECT * FROM t2;
 372   }
 373 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 374 do_test table-8.7 {
 375   catchsql {
 376     SELECT * FROM t5;
 377   }
 378 } {1 {no such table: t5}}
 379 do_test table-8.8 {
 380   catchsql {
 381     CREATE TABLE t5 AS SELECT * FROM no_such_table;
 382   }
 383 } {1 {no such table: no_such_table}}
 384
 385 # Make sure we cannot have duplicate column names within a table.
 386 #
 387 do_test table-9.1 {
 388   catchsql {
 389     CREATE TABLE t6(a,b,a);
 390   }
 391 } {1 {duplicate column name: a}}
 392
 393 # Check the foreign key syntax.
 394 #
 395 ifcapable {foreignkey} {
 396 do_test table-10.1 {
 397   catchsql {
 398     CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
 399     INSERT INTO t6 VALUES(NULL);
 400   }
 401 } {1 {t6.a may not be NULL}}
 402 do_test table-10.2 {
 403   catchsql {
 404     DROP TABLE t6;
 405     CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
 406   }
 407 } {0 {}}
 408 do_test table-10.3 {
 409   catchsql {
 410     DROP TABLE t6;
 411     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
 412   }
 413 } {0 {}}
 414 do_test table-10.4 {
 415   catchsql {
 416     DROP TABLE t6;
 417     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
 418   }
 419 } {0 {}}
 420 do_test table-10.5 {
 421   catchsql {
 422     DROP TABLE t6;
 423     CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
 424   }
 425 } {0 {}}
 426 do_test table-10.6 {
 427   catchsql {
 428     DROP TABLE t6;
 429     CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
 430   }
 431 } {0 {}}
 432 do_test table-10.7 {
 433   catchsql {
 434     DROP TABLE t6;
 435     CREATE TABLE t6(a,
 436       FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
 437     );
 438   }
 439 } {0 {}}
 440 do_test table-10.8 {
 441   catchsql {
 442     DROP TABLE t6;
 443     CREATE TABLE t6(a,b,c,
 444       FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
 445         ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 446     );
 447   }
 448 } {0 {}}
 449 do_test table-10.9 {
 450   catchsql {
 451     DROP TABLE t6;
 452     CREATE TABLE t6(a,b,c,
 453       FOREIGN KEY (b,c) REFERENCES t4(x)
 454     );
 455   }
 456 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 457 do_test table-10.10 {
 458   catchsql {DROP TABLE t6}
 459   catchsql {
 460     CREATE TABLE t6(a,b,c,
 461       FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
 462     );
 463   }
 464 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 465 do_test table-10.11 {
 466   catchsql {DROP TABLE t6}
 467   catchsql {
 468     CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
 469   }
 470 } {1 {foreign key on c should reference only one column of table t4}}
 471 do_test table-10.12 {
 472   catchsql {DROP TABLE t6}
 473   catchsql {
 474     CREATE TABLE t6(a,b,c,
 475       FOREIGN KEY (b,x) REFERENCES t4(x,y)
 476     );
 477   }
 478 } {1 {unknown column "x" in foreign key definition}}
 479 do_test table-10.13 {
 480   catchsql {DROP TABLE t6}
 481   catchsql {
 482     CREATE TABLE t6(a,b,c,
 483       FOREIGN KEY (x,b) REFERENCES t4(x,y)
 484     );
 485   }
 486 } {1 {unknown column "x" in foreign key definition}}
 487 } ;# endif foreignkey
 488
 489 # Test for the "typeof" function. More tests for the
 490 # typeof() function are found in bind.test and types.test.
 491 #
 492 do_test table-11.1 {
 493   execsql {
 494     CREATE TABLE t7(
 495        a integer primary key,
 496        b number(5,10),
 497        c character varying (8),
 498        d VARCHAR(9),
 499        e clob,
 500        f BLOB,
 501        g Text,
 502        h
 503     );
 504     INSERT INTO t7(a) VALUES(1);
 505     SELECT typeof(a), typeof(b), typeof(c), typeof(d),
 506            typeof(e), typeof(f), typeof(g), typeof(h)
 507     FROM t7 LIMIT 1;
 508   }
 509 } {integer null null null null null null null}
 510 do_test table-11.2 {
 511   execsql {
 512     SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
 513     FROM t7 LIMIT 1;
 514   }
 515 } {null null null null}
 516
 517 # Test that when creating a table using CREATE TABLE AS, column types are
 518 # assigned correctly for (SELECT ...) and 'x AS y' expressions.
 519 do_test table-12.1 {
 520   ifcapable subquery {
 521     execsql {
 522       CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
 523     }
 524   } else {
 525     execsql {
 526       CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
 527     }
 528   }
 529 } {}
 530 do_test table-12.2 {
 531   execsql {
 532     SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
 533   }
 534 } {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}
 535
 536 #--------------------------------------------------------------------
 537 # Test cases table-13.*
 538 #
 539 # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
 540 # and CURRENT_TIMESTAMP.
 541 #
 542 do_test table-13.1 {
 543   execsql {
 544     CREATE TABLE tablet8(
 545        a integer primary key,
 546        tm text DEFAULT CURRENT_TIME,
 547        dt text DEFAULT CURRENT_DATE,
 548        dttm text DEFAULT CURRENT_TIMESTAMP
 549     );
 550     SELECT * FROM tablet8;
 551   }
 552 } {}
 553 set i 0
 554 foreach {date time} {
 555   1976-07-04 12:00:00
 556   1994-04-16 14:00:00
 557   2000-01-01 00:00:00
 558   2003-12-31 12:34:56
 559 } {
 560   incr i
 561   set sqlite_current_time [clock scan "$date $time" -gmt 1]
 562   # set sqlite_current_time [execsql "SELECT strftime('%s','$date $time')"]
 563   do_test table-13.2.$i {
 564     execsql "
 565       INSERT INTO tablet8(a) VALUES($i);
 566       SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
 567     "
 568   } [list $time $date [list $date $time]]
 569 }
 570 set sqlite_current_time 0
 571
 572 #--------------------------------------------------------------------
 573 # Test cases table-14.*
 574 #
 575 # Test that a table cannot be created or dropped while other virtual
 576 # machines are active. This is required because otherwise when in
 577 # auto-vacuum mode the btree-layer may need to move the root-pages of
 578 # a table for which there is an open cursor.
 579 #
 580
 581 # db eval {
 582 #   pragma vdbe_trace = 0;
 583 # }
 584 # Try to create a table from within a callback:
 585 unset -nocomplain result
 586 do_test table-14.1 {
 587   set rc [
 588     catch {
 589       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 590         db eval {CREATE TABLE t9(a, b, c)}
 591       }
 592     } msg
 593   ]
 594   set result [list $rc $msg]
 595 } {1 {database table is locked}}
 596
 597 do_test table-14.2 {
 598   execsql {
 599     CREATE TABLE t9(a, b, c)
 600   }
 601 } {}
 602
 603 # Try to drop a table from within a callback:
 604 do_test table-14.3 {
 605   set rc [
 606     catch {
 607       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 608         db eval {DROP TABLE t9;}
 609       }
 610     } msg
 611   ]
 612   set result [list $rc $msg]
 613 } {1 {database table is locked}}
 614
 615 # Now attach a database and ensure that a table can be created in the
 616 # attached database whilst in a callback from a query on the main database.
 617 do_test table-14.4 {
 618   file delete -force test2.db
 619   file delete -force test2.db-journal
 620   execsql {
 621     attach 'test2.db' as aux;
 622   }
 623   db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 624     db eval {CREATE TABLE aux.t1(a, b, c)}
 625   }
 626 } {}
 627
 628 # On the other hand, it should be impossible to drop a table when any VMs
 629 # are active. This is because VerifyCookie instructions may have already
 630 # been executed, and btree root-pages may not move after this (which a
 631 # delete table might do).
 632 do_test table-14.4 {
 633   set rc [
 634     catch {
 635       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 636         db eval {DROP TABLE aux.t1;}
 637       }
 638     } msg
 639   ]
 640   set result [list $rc $msg]
 641 } {1 {database table is locked}}
 642
 643 # Create and drop 2000 tables. This is to check that the balance_shallow()
 644 # routine works correctly on the sqlite_master table. At one point it
 645 # contained a bug that would prevent the right-child pointer of the
 646 # child page from being copied to the root page.
 647 #
 648 do_test table-15.1 {
 649   execsql {BEGIN}
 650   for {set i 0} {$i<2000} {incr i} {
 651     execsql "CREATE TABLE tbl$i (a, b, c)"
 652   }
 653   execsql {COMMIT}
 654 } {}
 655 do_test table-15.2 {
 656   execsql {BEGIN}
 657   for {set i 0} {$i<2000} {incr i} {
 658     execsql "DROP TABLE tbl$i"
 659   }
 660   execsql {COMMIT}
 661 } {}
 662
 663 finish_test
 664