1# 2013-10-19 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# 12# Test the operation of table-options in the WITH clause of the 13# CREATE TABLE statement. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20do_test tableopt-1.1 { 21 catchsql { 22 CREATE TABLE t1(a,b) WITHOUT rowid; 23 } 24} {1 {PRIMARY KEY missing on table t1}} 25do_test tableopt-1.1b { 26 catchsql { 27 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT,b) WITHOUT rowid; 28 } 29} {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}} 30do_test tableopt-1.2 { 31 catchsql { 32 CREATE TABLE t1(a,b) WITHOUT unknown2; 33 } 34} {1 {unknown table option: unknown2}} 35 36do_execsql_test tableopt-2.1 { 37 CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid; 38 INSERT INTO t1 VALUES(1,2,3),(2,3,4); 39 SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b; 40} {3 4} 41do_test tableopt-2.1.1 { 42 catchsql { 43 SELECT rowid, * FROM t1; 44 } 45} {1 {no such column: rowid}} 46do_test tableopt-2.1.2 { 47 catchsql { 48 SELECT _rowid_, * FROM t1; 49 } 50} {1 {no such column: _rowid_}} 51do_test tableopt-2.1.3 { 52 catchsql { 53 SELECT oid, * FROM t1; 54 } 55} {1 {no such column: oid}} 56do_execsql_test tableopt-2.2 { 57 VACUUM; 58 SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b; 59} {3 4} 60do_test tableopt-2.3 { 61 sqlite3 db2 test.db 62 db2 eval {SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b;} 63} {3 4} 64db2 close 65 66# Make sure the "without" keyword is still usable as a table or 67# column name. 68# 69do_execsql_test tableopt-3.1 { 70 CREATE TABLE without(x INTEGER PRIMARY KEY, without TEXT); 71 INSERT INTO without VALUES(1, 'xyzzy'), (2, 'fizzle'); 72 SELECT * FROM without WHERE without='xyzzy'; 73} {1 xyzzy} 74 75 76finish_test 77