Debian Codegraph @

test/without_rowid5.test

raw
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
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 ifcapable !incrblob {
19 finish_test
20 return
21 }
22
23 # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
24 # special column, usually called the "rowid", that uniquely identifies
25 # that row within the table.
26 #
27 # EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is
28 # added to the end of a CREATE TABLE statement, then the special "rowid"
29 # column is omitted.
30 #
31 do_execsql_test without_rowid5-1.1 {
32 CREATE TABLE t1(a PRIMARY KEY,b,c);
33 CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID;
34 INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306);
35 INSERT INTO t1w SELECT a,b,c FROM t1;
36 SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC;
37 } {1 1 1 2 2 2 3 3 3}
38 do_catchsql_test without_rowid5-1.2 {
39 SELECT rowid FROM t1w;
40 } {1 {no such column: rowid}}
41 do_catchsql_test without_rowid5-1.3 {
42 SELECT _rowid_ FROM t1w;
43 } {1 {no such column: _rowid_}}
44 do_catchsql_test without_rowid5-1.4 {
45 SELECT oid FROM t1w;
46 } {1 {no such column: oid}}
47
48 # EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add
49 # the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement.
50 # For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY
51 # KEY, cnt INTEGER ) WITHOUT ROWID;
52 #
53 do_execsql_test without_rowid5-2.1 {
54 CREATE TABLE IF NOT EXISTS wordcount(
55 word TEXT PRIMARY KEY,
56 cnt INTEGER
57 ) WITHOUT ROWID;
58 INSERT INTO wordcount VALUES('one',1);
59 } {}
60 do_catchsql_test without_rowid5-2.2 {
61 SELECT rowid FROM wordcount;
62 } {1 {no such column: rowid}}
63
64 # EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the
65 # keywords does not matter. One can write "WITHOUT rowid" or "without
66 # rowid" or "WiThOuT rOwId" and it will mean the same thing.
67 #
68 do_execsql_test without_rowid5-2.3 {
69 CREATE TABLE IF NOT EXISTS wordcount_b(
70 word TEXT PRIMARY KEY,
71 cnt INTEGER
72 ) WITHOUT rowid;
73 INSERT INTO wordcount_b VALUES('one',1);
74 } {}
75 do_catchsql_test without_rowid5-2.4 {
76 SELECT rowid FROM wordcount_b;
77 } {1 {no such column: rowid}}
78 do_execsql_test without_rowid5-2.5 {
79 CREATE TABLE IF NOT EXISTS wordcount_c(
80 word TEXT PRIMARY KEY,
81 cnt INTEGER
82 ) without rowid;
83 INSERT INTO wordcount_c VALUES('one',1);
84 } {}
85 do_catchsql_test without_rowid5-2.6 {
86 SELECT rowid FROM wordcount_c;
87 } {1 {no such column: rowid}}
88 do_execsql_test without_rowid5-2.7 {
89 CREATE TABLE IF NOT EXISTS wordcount_d(
90 word TEXT PRIMARY KEY,
91 cnt INTEGER
92 ) WITHOUT rowid;
93 INSERT INTO wordcount_d VALUES('one',1);
94 } {}
95 do_catchsql_test without_rowid5-2.8 {
96 SELECT rowid FROM wordcount_d;
97 } {1 {no such column: rowid}}
98
99 # EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword
100 # in the CREATE TABLE statement.
101 #
102 do_catchsql_test without_rowid5-3.1 {
103 CREATE TABLE IF NOT EXISTS error1(
104 word TEXT PRIMARY KEY,
105 cnt INTEGER
106 ) WITHOUT _rowid_;
107 } {1 {unknown table option: _rowid_}}
108 do_catchsql_test without_rowid5-3.2 {
109 CREATE TABLE IF NOT EXISTS error2(
110 word TEXT PRIMARY KEY,
111 cnt INTEGER
112 ) WITHOUT oid;
113 } {1 {unknown table option: oid}}
114
115 # EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE
116 # statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
117 #
118 # EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a
119 # PRIMARY KEY.
120 #
121 # EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table
122 # without a PRIMARY KEY results in an error.
123 #
124 do_catchsql_test without_rowid5-4.1 {
125 CREATE TABLE IF NOT EXISTS error3(
126 word TEXT UNIQUE,
127 cnt INTEGER
128 ) WITHOUT ROWID;
129 } {1 {PRIMARY KEY missing on table error3}}
130
131 # EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER
132 # PRIMARY KEY" do not apply on WITHOUT ROWID tables.
133 #
134 do_execsql_test without_rowid5-5.1 {
135 CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID;
136 INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key
137 SELECT * FROM ipk;
138 } {rival bonus}
139 do_catchsql_test without_rowid5-5.2a {
140 BEGIN;
141 INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys
142 } {1 {NOT NULL constraint failed: ipk.key}}
143 do_execsql_test without_rowid5-5.2b {
144 ROLLBACK;
145 } {}
146
147 # EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT
148 # ROWID tables.
149 #
150 # EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT"
151 # keyword is used in the CREATE TABLE statement for a WITHOUT ROWID
152 # table.
153 #
154 do_catchsql_test without_rowid5-5.3 {
155 CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID;
156 } {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}
157
158 # EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the
159 # PRIMARY KEY in a WITHOUT ROWID table.
160 #
161 # EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate
162 # the SQL standard and allow NULL values in PRIMARY KEY fields.
163 #
164 # EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the
165 # standard and will throw an error on any attempt to insert a NULL into
166 # a PRIMARY KEY column.
167 #
168 do_execsql_test without_rowid5-5.4 {
169 CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e));
170 CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID;
171 INSERT INTO nn VALUES(1,2,3,4,5);
172 INSERT INTO nnw VALUES(1,2,3,4,5);
173 } {}
174 do_execsql_test without_rowid5-5.5 {
175 INSERT INTO nn VALUES(NULL, 3,4,5,6);
176 INSERT INTO nn VALUES(3,4,NULL,7,8);
177 INSERT INTO nn VALUES(4,5,6,7,NULL);
178 SELECT count(*) FROM nn;
179 } {4}
180 do_catchsql_test without_rowid5-5.6 {
181 INSERT INTO nnw VALUES(NULL, 3,4,5,6);
182 } {1 {NOT NULL constraint failed: nnw.a}}
183 do_catchsql_test without_rowid5-5.7 {
184 INSERT INTO nnw VALUES(3,4,NULL,7,8)
185 } {1 {NOT NULL constraint failed: nnw.c}}
186 do_catchsql_test without_rowid5-5.8 {
187 INSERT INTO nnw VALUES(4,5,6,7,NULL)
188 } {1 {NOT NULL constraint failed: nnw.e}}
189 do_execsql_test without_rowid5-5.9 {
190 SELECT count(*) FROM nnw;
191 } {1}
192
193 # Ticket f2be158c57aaa8c6 (2021-08-18)
194 # NOT NULL ON CONFLICT clauses work on WITHOUT ROWID tables now.
195 #
196 do_test without_rowid5-5.100 {
197 db eval {
198 DROP TABLE IF EXISTS t5;
199 CREATE TABLE t5(
200 a INT NOT NULL ON CONFLICT ROLLBACK,
201 b TEXT,
202 c TEXT,
203 PRIMARY KEY(a,b)
204 ) WITHOUT ROWID;
205 BEGIN;
206 INSERT INTO t5(a,b,c) VALUES(1,2,3);
207 }
208 catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
209 db eval {
210 SELECT * FROM t5;
211 }
212 } {}
213 do_test without_rowid5-5.101 {
214 db eval {
215 DROP TABLE IF EXISTS t5;
216 CREATE TABLE t5(
217 a INT NOT NULL ON CONFLICT ABORT,
218 b TEXT,
219 c TEXT,
220 PRIMARY KEY(a,b)
221 ) WITHOUT ROWID;
222 BEGIN;
223 INSERT INTO t5(a,b,c) VALUES(1,2,3);
224 }
225 catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
226 db eval {
227 COMMIT;
228 SELECT * FROM t5;
229 }
230 } {1 2 3}
231 do_test without_rowid5-5.102 {
232 db eval {
233 DROP TABLE IF EXISTS t5;
234 CREATE TABLE t5(
235 a INT NOT NULL ON CONFLICT FAIL,
236 b TEXT,
237 c TEXT,
238 PRIMARY KEY(a,b)
239 ) WITHOUT ROWID;
240 }
241 catch {db eval {INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);}}
242 db eval {
243 SELECT * FROM t5;
244 }
245 } {1 2 3}
246 do_test without_rowid5-5.103 {
247 db eval {
248 DROP TABLE IF EXISTS t5;
249 CREATE TABLE t5(
250 a INT NOT NULL ON CONFLICT IGNORE,
251 b TEXT,
252 c TEXT,
253 PRIMARY KEY(a,b)
254 ) WITHOUT ROWID;
255 INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
256 SELECT * FROM t5;
257 }
258 } {1 2 3 6 7 8}
259 do_test without_rowid5-5.104 {
260 db eval {
261 DROP TABLE IF EXISTS t5;
262 CREATE TABLE t5(
263 a INT NOT NULL ON CONFLICT REPLACE DEFAULT 3,
264 b TEXT,
265 c TEXT,
266 PRIMARY KEY(a,b)
267 ) WITHOUT ROWID;
268 INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
269 SELECT * FROM t5;
270 }
271 } {1 2 3 3 4 5 6 7 8}
272
273
274 # EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
275 # work for WITHOUT ROWID tables.
276 #
277 # EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
278 #
279 do_execsql_test without_rowid5-6.1 {
280 CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
281 INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f');
282 } {}
283 do_test without_rowid5-6.2 {
284 set rc [catch {db incrblob b1 b 1} msg]
285 lappend rc $msg
286 } {1 {cannot open table without rowid: b1}}
287
288
289 finish_test