gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tableopts.test (about) 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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 do_test tableopt-1.1 { 21 catchsql { 22 CREATE TABLE t1(a,b) WITHOUT rowid; 23 } 24 } {1 {PRIMARY KEY missing on table t1}} 25 do_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}} 30 do_test tableopt-1.2 { 31 catchsql { 32 CREATE TABLE t1(a,b) WITHOUT unknown2; 33 } 34 } {1 {unknown table option: unknown2}} 35 36 do_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} 41 do_test tableopt-2.1.1 { 42 catchsql { 43 SELECT rowid, * FROM t1; 44 } 45 } {1 {no such column: rowid}} 46 do_test tableopt-2.1.2 { 47 catchsql { 48 SELECT _rowid_, * FROM t1; 49 } 50 } {1 {no such column: _rowid_}} 51 do_test tableopt-2.1.3 { 52 catchsql { 53 SELECT oid, * FROM t1; 54 } 55 } {1 {no such column: oid}} 56 do_execsql_test tableopt-2.2 { 57 VACUUM; 58 SELECT c FROM t1 WHERE a IN (1,2) ORDER BY b; 59 } {3 4} 60 do_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} 64 db2 close 65 66 # Make sure the "without" keyword is still usable as a table or 67 # column name. 68 # 69 do_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 76 finish_test