Debian Codegraph @

test/strict1.test

raw
1 # 2021-08-18
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 # This file implements regression tests for SQLite library. The
13 # focus of this file is testing STRICT tables.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix strict1
19
20 # STRICT tables have on a limited number of allowed datatypes.
21 #
22 do_catchsql_test strict1-1.1 {
23 CREATE TABLE t1(a) STRICT;
24 } {1 {missing datatype for t1.a}}
25 do_catchsql_test strict1-1.2 {
26 CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;
27 } {1 {missing datatype for t1.a}}
28 do_catchsql_test strict1-1.3 {
29 CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;
30 } {1 {missing datatype for t1.a}}
31 do_catchsql_test strict1-1.4 {
32 CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;
33 } {1 {unknown datatype for t1.a: "BANJO"}}
34 do_catchsql_test strict1-1.5 {
35 CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict;
36 } {1 {unknown datatype for t1.f: "DATE"}}
37 do_catchsql_test strict1-1.6 {
38 CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT;
39 } {1 {unknown datatype for t1.f: "TEXT(50)"}}
40
41 do_execsql_test strict1-2.0 {
42 CREATE TABLE t1(
43 a INT,
44 b INTEGER,
45 c BLOB,
46 d TEXT,
47 e REAL
48 ) STRICT;
49 } {}
50 ifcapable vtab {
51 do_execsql_test strict1-2.0a {
52 SELECT strict FROM pragma_table_list('t1');
53 } {1}
54 }
55 do_catchsql_test strict1-2.1 {
56 INSERT INTO t1(a) VALUES('xyz');
57 } {1 {cannot store TEXT value in INT column t1.a}}
58 do_catchsql_test strict1-2.2 {
59 INSERT INTO t1(b) VALUES('xyz');
60 } {1 {cannot store TEXT value in INTEGER column t1.b}}
61 do_catchsql_test strict1-2.3 {
62 INSERT INTO t1(c) VALUES('xyz');
63 } {1 {cannot store TEXT value in BLOB column t1.c}}
64 do_catchsql_test strict1-2.4 {
65 INSERT INTO t1(d) VALUES(x'3142536475');
66 } {1 {cannot store BLOB value in TEXT column t1.d}}
67 do_catchsql_test strict1-2.5 {
68 INSERT INTO t1(e) VALUES('xyz');
69 } {1 {cannot store TEXT value in REAL column t1.e}}
70
71
72 do_execsql_test strict1-3.1 {
73 INSERT INTO t1(a, b) VALUES(1,2),('3','4'),(5.0, 6.0),(null,null);
74 SELECT a, b, '|' FROM t1;
75 } {1 2 | 3 4 | 5 6 | {} {} |}
76 do_catchsql_test strict1-3.2 {
77 INSERT INTO t1(a) VALUES(1.2);
78 } {1 {cannot store REAL value in INT column t1.a}}
79 do_catchsql_test strict1-3.3 {
80 INSERT INTO t1(a) VALUES(x'313233');
81 } {1 {cannot store BLOB value in INT column t1.a}}
82 do_catchsql_test strict1-3.4 {
83 INSERT INTO t1(b) VALUES(1.2);
84 } {1 {cannot store REAL value in INTEGER column t1.b}}
85 do_catchsql_test strict1-3.5 {
86 INSERT INTO t1(b) VALUES(x'313233');
87 } {1 {cannot store BLOB value in INTEGER column t1.b}}
88
89 do_execsql_test strict1-4.1 {
90 DELETE FROM t1;
91 INSERT INTO t1(c) VALUES(x'313233'), (NULL);
92 SELECT typeof(c), c FROM t1;
93 } {blob 123 null {}}
94 do_catchsql_test strict1-4.2 {
95 INSERT INTO t1(c) VALUES('456');
96 } {1 {cannot store TEXT value in BLOB column t1.c}}
97
98 do_execsql_test strict1-5.1 {
99 DELETE FROM t1;
100 INSERT INTO t1(d) VALUES('xyz'),(4),(5.5),(NULL);
101 SELECT typeof(d), d FROM t1;
102 } {text xyz text 4 text 5.5 null {}}
103 do_catchsql_test strict1-5.2 {
104 INSERT INTO t1(d) VALUES(x'4567');
105 } {1 {cannot store BLOB value in TEXT column t1.d}}
106
107 do_execsql_test strict1-6.1 {
108 DELETE FROM t1;
109 INSERT INTO t1(e) VALUES(1),(2.5),('3'),('4.5'),(6.0),(NULL);
110 SELECT typeof(e), e FROM t1;
111 } {real 1.0 real 2.5 real 3.0 real 4.5 real 6.0 null {}}
112 do_catchsql_test strict1-6.2 {
113 INSERT INTO t1(e) VALUES('xyz');
114 } {1 {cannot store TEXT value in REAL column t1.e}}
115 do_catchsql_test strict1-6.3 {
116 INSERT INTO t1(e) VALUES(x'3456');
117 } {1 {cannot store BLOB value in REAL column t1.e}}
118
119 ifcapable altertable {
120 do_execsql_test strict1-7.1 {
121 DROP TABLE IF EXISTS t4;
122 CREATE TABLE t4(
123 a INT AS (b*2) VIRTUAL,
124 b INT AS (c*2) STORED,
125 c INT PRIMARY KEY
126 ) STRICT;
127 INSERT INTO t4(c) VALUES(1);
128 SELECT * FROM t4;
129 } {4 2 1}
130 do_catchsql_test strict1-7.2 {
131 ALTER TABLE t4 ADD COLUMN d VARCHAR;
132 } {1 {error in table t4 after add column: unknown datatype for t4.d: "VARCHAR"}}
133 do_catchsql_test strict1-7.3 {
134 ALTER TABLE t4 ADD COLUMN d;
135 } {1 {error in table t4 after add column: missing datatype for t4.d}}
136 }
137
138 # 2022-01-17 https://sqlite.org/forum/forumpost/fa012c77796d9399
139 #
140 reset_db
141 do_execsql_test strict1-8.1 {
142 CREATE TABLE csv_import_table (
143 "debit" TEXT,
144 "credit" TEXT
145 );
146 INSERT INTO csv_import_table VALUES ('', '250.00');
147 CREATE TABLE IF NOT EXISTS transactions (
148 debit REAL,
149 credit REAL,
150 amount REAL GENERATED ALWAYS AS (ifnull(credit, 0.0) - ifnull(debit, 0.0))
151 ) STRICT;
152 INSERT INTO transactions
153 SELECT
154 nullif(debit, '') AS debit,
155 nullif(credit, '') AS credit
156 FROM csv_import_table;
157 SELECT * FROM transactions;
158 } {{} 250.0 250.0}
159 do_execsql_test strict1-8.2 {
160 CREATE TABLE t1(x REAL, y REAL AS (x)) STRICT;
161 INSERT INTO t1 VALUES(5),(4611686018427387904);
162 SELECT *, '|' FROM t1;
163 } {/5.0 5.0 4.6116\d*e\+18 4.6116\d+e\+18 |/}
164
165 # 2025-06-18 https://sqlite.org/forum/forumpost/6caf195248a849e4
166 #
167 # Enforce STRICT table type constraints on STORED generated columns
168 #
169 do_execsql_test strict1-9.1 {
170 CREATE TABLE strict (
171 k INTEGER PRIMARY KEY,
172 c1 REAL AS(if(k=11,1.5, k=12,2, k=13,'x', k=14,x'34', 0.0)) STORED,
173 c2 INT AS(if(k=21,1.5, k=22,2, k=23,'x', k=24,x'34', 0)) STORED,
174 c3 TEXT AS(if(k=31,1.5, k=32,2, k=33,'x', k=34,x'34', 'x')) STORED,
175 c4 BLOB AS(if(k=41,1.5, k=42,2, k=43,'x', k=44,x'34', x'00')) STORED,
176 c5 ANY AS(if(k=51,1.5, k=52,2, k=53,'x', k=54,x'34', 0)) STORED
177 ) STRICT;
178 INSERT INTO strict(k) VALUES(11);
179 INSERT INTO strict(k) VALUES(12);
180 INSERT INTO strict(k) VALUES(22);
181 INSERT INTO strict(k) VALUES(31);
182 INSERT INTO strict(k) VALUES(32);
183 INSERT INTO strict(k) VALUES(33);
184 INSERT INTO strict(k) VALUES(44);
185 PRAGMA integrity_check;
186 } {ok}
187 do_catchsql_test strict1-9.2.13 {
188 INSERT INTO strict(k) VALUES(13);
189 } {1 {cannot store TEXT value in REAL column strict.c1}}
190 do_catchsql_test strict1-9.2.14 {
191 INSERT INTO strict(k) VALUES(14);
192 } {1 {cannot store BLOB value in REAL column strict.c1}}
193 do_catchsql_test strict1-9.2.21 {
194 INSERT INTO strict(k) VALUES(21);
195 } {1 {cannot store REAL value in INT column strict.c2}}
196 do_catchsql_test strict1-9.2.23 {
197 INSERT INTO strict(k) VALUES(23);
198 } {1 {cannot store TEXT value in INT column strict.c2}}
199 do_catchsql_test strict1-9.2.24 {
200 INSERT INTO strict(k) VALUES(24);
201 } {1 {cannot store BLOB value in INT column strict.c2}}
202 do_catchsql_test strict1-9.2.34 {
203 INSERT INTO strict(k) VALUES(34);
204 } {1 {cannot store BLOB value in TEXT column strict.c3}}
205 do_catchsql_test strict1-9.2.41 {
206 INSERT INTO strict(k) VALUES(41);
207 } {1 {cannot store REAL value in BLOB column strict.c4}}
208 do_catchsql_test strict1-9.2.42 {
209 INSERT INTO strict(k) VALUES(42);
210 } {1 {cannot store INT value in BLOB column strict.c4}}
211 do_catchsql_test strict1-9.2.43 {
212 INSERT INTO strict(k) VALUES(43);
213 } {1 {cannot store TEXT value in BLOB column strict.c4}}
214
215 do_execsql_test strict1-9.3 {
216 DROP TABLE strict;
217 CREATE TABLE strict (
218 k INTEGER PRIMARY KEY,
219 c1 REAL AS(if(k=11,1.5, k=12,2, k=13,'x', k=14,x'34', 0.0)) VIRTUAL,
220 c2 INT AS(if(k=21,1.5, k=22,2, k=23,'x', k=24,x'34', 0)) VIRTUAL,
221 c3 TEXT AS(if(k=31,1.5, k=32,2, k=33,'x', k=34,x'34', 'x')) VIRTUAL,
222 c4 BLOB AS(if(k=41,1.5, k=42,2, k=43,'x', k=44,x'34', x'00')) VIRTUAL,
223 c5 ANY AS(if(k=51,1.5, k=52,2, k=53,'x', k=54,x'34', 0)) VIRTUAL
224 ) STRICT;
225 INSERT INTO strict(k) VALUES(11);
226 INSERT INTO strict(k) VALUES(12);
227 INSERT INTO strict(k) VALUES(22);
228 INSERT INTO strict(k) VALUES(31);
229 INSERT INTO strict(k) VALUES(32);
230 INSERT INTO strict(k) VALUES(33);
231 INSERT INTO strict(k) VALUES(44);
232 PRAGMA integrity_check;
233 } {ok}
234 do_catchsql_test strict1-9.4.13 {
235 INSERT INTO strict(k) VALUES(13);
236 } {1 {cannot store TEXT value in REAL column strict.c1}}
237 do_catchsql_test strict1-9.4.14 {
238 INSERT INTO strict(k) VALUES(14);
239 } {1 {cannot store BLOB value in REAL column strict.c1}}
240 do_catchsql_test strict1-9.4.21 {
241 INSERT INTO strict(k) VALUES(21);
242 } {1 {cannot store REAL value in INT column strict.c2}}
243 do_catchsql_test strict1-9.4.23 {
244 INSERT INTO strict(k) VALUES(23);
245 } {1 {cannot store TEXT value in INT column strict.c2}}
246 do_catchsql_test strict1-9.4.24 {
247 INSERT INTO strict(k) VALUES(24);
248 } {1 {cannot store BLOB value in INT column strict.c2}}
249 do_catchsql_test strict1-9.4.34 {
250 INSERT INTO strict(k) VALUES(34);
251 } {1 {cannot store BLOB value in TEXT column strict.c3}}
252 do_catchsql_test strict1-9.4.41 {
253 INSERT INTO strict(k) VALUES(41);
254 } {1 {cannot store REAL value in BLOB column strict.c4}}
255 do_catchsql_test strict1-9.4.42 {
256 INSERT INTO strict(k) VALUES(42);
257 } {1 {cannot store INT value in BLOB column strict.c4}}
258 do_catchsql_test strict1-9.4.43 {
259 INSERT INTO strict(k) VALUES(43);
260 } {1 {cannot store TEXT value in BLOB column strict.c4}}
261
262 finish_test