Debian Codegraph @

test/altercons2.test

raw
1 # 2025 September 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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix altercons2
16
17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18 ifcapable !altertable {
19 finish_test
20 return
21 }
22
23 foreach {tn newsql alter res final} {
24 1 "CREATE TABLE t1(a, b"
25 "ALTER TABLE t1 ALTER c SET NOT NULL"
26 {1 {database disk image is malformed}}
27 "CREATE TABLE t1(a, b"
28
29 2 "CREATE TABLE t1(a, b, "
30 "ALTER TABLE t1 ALTER c DROP NOT NULL"
31 {0 {}}
32 "CREATE TABLE t1(a, b, "
33
34 3 "CREATE TABLE t1(a, b, CHECK( ..."
35 "ALTER TABLE t1 ALTER c DROP NOT NULL"
36 {0 {}}
37 "CREATE TABLE t1(a, b, CHECK( ..."
38
39 4 "CREATE TABLE t1(a, b, c NOT NULL"
40 "ALTER TABLE t1 ALTER c DROP NOT NULL"
41 {0 {}}
42 "CREATE TABLE t1(a, b, c "
43
44 5 "CREATE TABLE"
45 "ALTER TABLE t1 ALTER c DROP NOT NULL"
46 {1 {database disk image is malformed}}
47 "CREATE TABLE"
48
49 6 "CREATE TABLE"
50 "ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a!=0)"
51 {1 {database disk image is malformed}}
52 "CREATE TABLE"
53
54 } {
55 reset_db
56 do_execsql_test 1.$tn.0 {
57 CREATE TABLE t1(a, b, c NOT NULL, CONSTRAINT xyz CHECK( a!=0 ));
58 }
59 do_execsql_test 1.$tn.1 {
60 PRAGMA writable_schema = 1;
61 UPDATE sqlite_schema SET sql = $::newsql
62 }
63 do_catchsql_test 1.$tn.2 $alter $res
64
65 do_execsql_test 1.$tn.3 {
66 SELECT sql FROM sqlite_schema WHERE name='t1'
67 } [list $final]
68 }
69
70 #-------------------------------------------------------------------------
71
72 reset_db
73 proc xAuth {t args} {
74 if {$t=="SQLITE_ALTER_TABLE"} {
75 return "SQLITE_DENY"
76 }
77 return "SQLITE_OK"
78 }
79 sqlite3 db test.db
80 db auth xAuth
81
82 do_execsql_test 2.0 {
83 CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c);
84 }
85
86 do_catchsql_test 2.1.1 {
87 ALTER TABLE x1 ADD CONSTRAINT ccc CHECK (a!='a')
88 } {1 {not authorized}}
89 do_execsql_test 2.1.2 {
90 SELECT sql FROM sqlite_schema WHERE name='x1'
91 } {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}
92
93 do_catchsql_test 2.2.1 {
94 ALTER TABLE x1 ALTER c SET NOT NULL
95 } {1 {not authorized}}
96 do_execsql_test 2.2.2 {
97 SELECT sql FROM sqlite_schema WHERE name='x1'
98 } {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}
99
100 #-------------------------------------------------------------------------
101 reset_db
102 do_execsql_test 3.0 {
103 CREATE TABLE t1(x);
104 }
105 do_execsql_test 3.1 {
106 ALTER TABLE t1 ALTER x SET NOT NULL;
107 }
108
109 #-------------------------------------------------------------------------
110 reset_db
111 do_execsql_test 4.0 {
112 CREATE TABLE abc(a, b, c, CONSTRAINT one CONSTRAINT two CHECK (b!=c));
113 }
114 do_execsql_test 4.1 {
115 ALTER TABLE abc DROP CONSTRAINT one
116 }
117 do_execsql_test 4.2 {
118 SELECT sql FROM sqlite_schema
119 } {
120 {CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c))}
121 }
122
123 #-------------------------------------------------------------------------
124 reset_db
125
126 # The columns must come before the table constraints in a CREATE TABLE
127 # statement. This is useful, as it means the DROP CONSTRAINT code does
128 # not have to handle the constraint immediately following the '(' at
129 # the start of the column-list.
130 do_catchsql_test 5.0 {
131 CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c), d)
132 } {1 {near "d": syntax error}}
133 do_catchsql_test 5.1 {
134 CREATE TABLE def(CONSTRAINT abc CHECK( b!=c ), a, b, c);
135 } {1 {near "CONSTRAINT": syntax error}}
136
137 #-------------------------------------------------------------------------
138 reset_db
139 do_execsql_test 6.0 {
140 CREATE TABLE abc(a, b CONSTRAINT two COLLATE nocase CHECK (a!=b), c CONSTRAINT one DEFAULT 'abc');
141 }
142
143 do_execsql_test 6.1 {
144 ALTER TABLE abc DROP CONSTRAINT one;
145 ALTER TABLE abc DROP CONSTRAINT two;
146 }
147
148 do_execsql_test 6.2 {
149 SELECT sql FROM sqlite_schema
150 } {
151 {CREATE TABLE abc(a, b COLLATE nocase CHECK (a!=b), c DEFAULT 'abc')}
152 }
153
154 #-------------------------------------------------------------------------
155 reset_db
156 do_execsql_test 7.0 {
157 CREATE TABLE abc(a, b, c, CONSTRAINT one CHECK (a>b) FOREIGN KEY(a) REFERENCES abc);
158 }
159 do_execsql_test 7.1 {
160 ALTER TABLE abc DROP CONSTRAINT one
161 }
162 do_execsql_test 7.2 {
163 SELECT sql FROM sqlite_schema
164 } {
165 {CREATE TABLE abc(a, b, c, FOREIGN KEY(a) REFERENCES abc)}
166 }
167
168 #-------------------------------------------------------------------------
169 reset_db
170 do_execsql_test 8.0 {
171 CREATE TABLE abc(a, b, c, CONSTRAINT one FOREIGN KEY(a) REFERENCES abc);
172 }
173 do_catchsql_test 8.1 {
174 ALTER TABLE abc DROP CONSTRAINT one
175 } {1 {constraint may not be dropped: one}}
176
177 #-------------------------------------------------------------------------
178 reset_db
179 do_execsql_test 9.0 {
180 CREATE TABLE abc(a, b NOT NULL AS (a+1))
181 }
182 do_execsql_test 9.1 {
183 ALTER TABLE abc ALTER b DROP NOT NULL;
184 SELECT sql FROM sqlite_schema;
185 } {{CREATE TABLE abc(a, b AS (a+1))}}
186
187 #-------------------------------------------------------------------------
188 reset_db
189 do_execsql_test 10.0 {
190 CREATE TABLE abc(a, b GENERATED ALWAYS AS (a+1));
191 INSERT INTO abc VALUES(1), (2);
192 SELECT * FROM abc;
193 } {1 2 2 3}
194
195 do_execsql_test 10.1 {
196 ALTER TABLE abc ALTER b SET NOT NULL;
197 }
198 do_catchsql_test 10.2 {
199 INSERT INTO abc VALUES(NULL);
200 } {1 {NOT NULL constraint failed: abc.b}}
201 do_execsql_test 10.3 {
202 INSERT INTO abc VALUES(3);
203 ALTER TABLE abc ALTER COLUMN b DROP NOT NULL;
204 }
205 do_execsql_test 10.4 {
206 INSERT INTO abc VALUES(NULL);
207 }
208
209 #-------------------------------------------------------------------------
210 reset_db
211 do_execsql_test 11.0 {
212 CREATE TABLE t1(a, b, c);
213 }
214
215 do_execsql_test 11.1.1 {
216 ALTER TABLE t1 ADD CONSTRAINT c1 CHECK(a=b) --comment
217 ;
218 }
219
220 do_execsql_test 11.1.2 {ALTER TABLE t1 ADD CONSTRAINT c2 CHECK(a=b) --comment}
221
222 do_execsql_test 11.1.3 {
223 SELECT sql FROM sqlite_schema;
224 } {
225 {CREATE TABLE t1(a, b, c, CONSTRAINT c1 CHECK(a=b), CONSTRAINT c2 CHECK(a=b))}
226 }
227
228 do_execsql_test 11.2.1 {
229 CREATE TABLE t2(a, b);
230 }
231 do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons}
232 do_execsql_test 11.2.3 {
233 SELECT sql FROM sqlite_schema WHERE name='t2';
234 } {
235 {CREATE TABLE t2(a, b NOT NULL)}
236 }
237 do_execsql_test 11.2.3 {
238 ALTER TABLE t2 ALTER b DROP NOT NULL;
239 SELECT sql FROM sqlite_schema WHERE name='t2';
240 } {
241 {CREATE TABLE t2(a, b)}
242 }
243 do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons
244 ;
245 }
246
247 #-------------------------------------------------------------------------
248 reset_db
249 do_execsql_test 12.0 {
250 CREATE TABLE "Test" (
251 "IsActive" INTEGER,
252 CONSTRAINT "BooleanZeroOrOne" CHECK ("IsActive" IN (0, 1))
253 );
254 }
255
256 do_execsql_test 12.1 {
257 ALTER TABLE Test DROP CONSTRAINT BooleanZeroOrOne
258 }
259
260 do_execsql_test 12.2 {
261 SELECT sql FROM sqlite_schema
262 } {
263 {CREATE TABLE "Test" (
264 "IsActive" INTEGER)}
265 }
266
267 do_execsql_test 12.3 {
268 DROP TABLE Test;
269 CREATE TABLE "Test" (
270 "IsActive" INTEGER,
271 CONSTRAINT "BooleanZeroOrOne" CHECK ("IsActive" IN (0, 1))
272 );
273 }
274
275 do_execsql_test 12.4 {
276 ALTER TABLE Test DROP CONSTRAINT "BooleanZeroOrOne"
277 }
278
279 do_execsql_test 12.5 {
280 SELECT sql FROM sqlite_schema
281 } {
282 {CREATE TABLE "Test" (
283 "IsActive" INTEGER)}
284 }
285
286 do_execsql_test 12.6 {
287 CREATE TABLE t1(a, b CONSTRAINT "a""b" CHECK (b IS NOT NULL));
288 }
289
290 do_execsql_test 12.7 {
291 ALTER TABLE t1 DROP CONSTRAINT "a""b"
292 }
293
294 finish_test
295