1# 2013-11-26 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# Requirements testing for WITHOUT ROWID tables. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18 19# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a 20# special column, usually called the "rowid", that uniquely identifies 21# that row within the table. 22# 23# EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is 24# added to the end of a CREATE TABLE statement, then the special "rowid" 25# column is omitted. 26# 27do_execsql_test without_rowid5-1.1 { 28 CREATE TABLE t1(a PRIMARY KEY,b,c); 29 CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID; 30 INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306); 31 INSERT INTO t1w SELECT a,b,c FROM t1; 32 SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC; 33} {1 1 1 2 2 2 3 3 3} 34do_catchsql_test without_rowid5-1.2 { 35 SELECT rowid FROM t1w; 36} {1 {no such column: rowid}} 37do_catchsql_test without_rowid5-1.3 { 38 SELECT _rowid_ FROM t1w; 39} {1 {no such column: _rowid_}} 40do_catchsql_test without_rowid5-1.4 { 41 SELECT oid FROM t1w; 42} {1 {no such column: oid}} 43 44# EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add 45# the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement. 46# For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY 47# KEY, cnt INTEGER ) WITHOUT ROWID; 48# 49do_execsql_test without_rowid5-2.1 { 50 CREATE TABLE IF NOT EXISTS wordcount( 51 word TEXT PRIMARY KEY, 52 cnt INTEGER 53 ) WITHOUT ROWID; 54 INSERT INTO wordcount VALUES('one',1); 55} {} 56do_catchsql_test without_rowid5-2.2 { 57 SELECT rowid FROM wordcount; 58} {1 {no such column: rowid}} 59 60# EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the 61# keywords does not matter. One can write "WITHOUT rowid" or "without 62# rowid" or "WiThOuT rOwId" and it will mean the same thing. 63# 64do_execsql_test without_rowid5-2.3 { 65 CREATE TABLE IF NOT EXISTS wordcount_b( 66 word TEXT PRIMARY KEY, 67 cnt INTEGER 68 ) WITHOUT rowid; 69 INSERT INTO wordcount_b VALUES('one',1); 70} {} 71do_catchsql_test without_rowid5-2.4 { 72 SELECT rowid FROM wordcount_b; 73} {1 {no such column: rowid}} 74do_execsql_test without_rowid5-2.5 { 75 CREATE TABLE IF NOT EXISTS wordcount_c( 76 word TEXT PRIMARY KEY, 77 cnt INTEGER 78 ) without rowid; 79 INSERT INTO wordcount_c VALUES('one',1); 80} {} 81do_catchsql_test without_rowid5-2.6 { 82 SELECT rowid FROM wordcount_c; 83} {1 {no such column: rowid}} 84do_execsql_test without_rowid5-2.7 { 85 CREATE TABLE IF NOT EXISTS wordcount_d( 86 word TEXT PRIMARY KEY, 87 cnt INTEGER 88 ) WITHOUT rowid; 89 INSERT INTO wordcount_d VALUES('one',1); 90} {} 91do_catchsql_test without_rowid5-2.8 { 92 SELECT rowid FROM wordcount_d; 93} {1 {no such column: rowid}} 94 95# EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword 96# in the CREATE TABLE statement. 97# 98do_catchsql_test without_rowid5-3.1 { 99 CREATE TABLE IF NOT EXISTS error1( 100 word TEXT PRIMARY KEY, 101 cnt INTEGER 102 ) WITHOUT _rowid_; 103} {1 {unknown table option: _rowid_}} 104do_catchsql_test without_rowid5-3.2 { 105 CREATE TABLE IF NOT EXISTS error2( 106 word TEXT PRIMARY KEY, 107 cnt INTEGER 108 ) WITHOUT oid; 109} {1 {unknown table option: oid}} 110 111# EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE 112# statement with the WITHOUT ROWID clause lacks a PRIMARY KEY. 113# 114# EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a 115# PRIMARY KEY. 116# 117# EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table 118# without a PRIMARY KEY results in an error. 119# 120do_catchsql_test without_rowid5-4.1 { 121 CREATE TABLE IF NOT EXISTS error3( 122 word TEXT UNIQUE, 123 cnt INTEGER 124 ) WITHOUT ROWID; 125} {1 {PRIMARY KEY missing on table error3}} 126 127# EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER 128# PRIMARY KEY" do not apply on WITHOUT ROWID tables. 129# 130do_execsql_test without_rowid5-5.1 { 131 CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID; 132 INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key 133 SELECT * FROM ipk; 134} {rival bonus} 135do_catchsql_test without_rowid5-5.2 { 136 INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys 137} {1 {NOT NULL constraint failed: ipk.key}} 138 139# EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT 140# ROWID tables. 141# 142# EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT" 143# keyword is used in the CREATE TABLE statement for a WITHOUT ROWID 144# table. 145# 146do_catchsql_test without_rowid5-5.3 { 147 CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID; 148} {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}} 149 150# EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the 151# PRIMARY KEY in a WITHOUT ROWID table. 152# 153# EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate 154# the SQL standard and allow NULL values in PRIMARY KEY fields. 155# 156# EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the 157# standard and will throw an error on any attempt to insert a NULL into 158# a PRIMARY KEY column. 159# 160do_execsql_test without_rowid5-5.4 { 161 CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e)); 162 CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID; 163 INSERT INTO nn VALUES(1,2,3,4,5); 164 INSERT INTO nnw VALUES(1,2,3,4,5); 165} {} 166do_execsql_test without_rowid5-5.5 { 167 INSERT INTO nn VALUES(NULL, 3,4,5,6); 168 INSERT INTO nn VALUES(3,4,NULL,7,8); 169 INSERT INTO nn VALUES(4,5,6,7,NULL); 170 SELECT count(*) FROM nn; 171} {4} 172do_catchsql_test without_rowid5-5.6 { 173 INSERT INTO nnw VALUES(NULL, 3,4,5,6); 174} {1 {NOT NULL constraint failed: nnw.a}} 175do_catchsql_test without_rowid5-5.7 { 176 INSERT INTO nnw VALUES(3,4,NULL,7,8) 177} {1 {NOT NULL constraint failed: nnw.c}} 178do_catchsql_test without_rowid5-5.8 { 179 INSERT INTO nnw VALUES(4,5,6,7,NULL) 180} {1 {NOT NULL constraint failed: nnw.e}} 181do_execsql_test without_rowid5-5.9 { 182 SELECT count(*) FROM nnw; 183} {1} 184 185# EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not 186# work for WITHOUT ROWID tables. 187# 188# EVIDENCE-OF: R-25760-33257 The sqlite3_blob_open() interface will fail 189# for a WITHOUT ROWID table. 190# 191do_execsql_test without_rowid5-6.1 { 192 CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID; 193 INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f'); 194} {} 195do_test without_rowid5-6.2 { 196 set rc [catch {db incrblob b1 b 1} msg] 197 lappend rc $msg 198} {1 {cannot open table without rowid: b1}} 199 200 201finish_test 202