AryaWu/sqlite
0
1# 2021-08-182#3# The author disclaims copyright to this source code. In place of4# 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. The13# focus of this file is testing STRICT tables.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix strict119 20# STRICT tables have on a limited number of allowed datatypes.21#22do_catchsql_test strict1-1.1 {23 CREATE TABLE t1(a) STRICT;24} {1 {missing datatype for t1.a}}25do_catchsql_test strict1-1.2 {26 CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;27} {1 {missing datatype for t1.a}}28do_catchsql_test strict1-1.3 {29 CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;30} {1 {missing datatype for t1.a}}31do_catchsql_test strict1-1.4 {32 CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;33} {1 {unknown datatype for t1.a: "BANJO"}}34do_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"}}37do_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 41do_execsql_test strict1-2.0 {42 CREATE TABLE t1(43 a INT,44 b INTEGER,45 c BLOB,46 d TEXT,47 e REAL48 ) STRICT;49} {}50ifcapable vtab {51 do_execsql_test strict1-2.0a {52 SELECT strict FROM pragma_table_list('t1');53 } {1}54}55do_catchsql_test strict1-2.1 {56 INSERT INTO t1(a) VALUES('xyz');57} {1 {cannot store TEXT value in INT column t1.a}}58do_catchsql_test strict1-2.2 {59 INSERT INTO t1(b) VALUES('xyz');60} {1 {cannot store TEXT value in INTEGER column t1.b}}61do_catchsql_test strict1-2.3 {62 INSERT INTO t1(c) VALUES('xyz');63} {1 {cannot store TEXT value in BLOB column t1.c}}64do_catchsql_test strict1-2.4 {65 INSERT INTO t1(d) VALUES(x'3142536475');66} {1 {cannot store BLOB value in TEXT column t1.d}}67do_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 72do_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 | {} {} |}76do_catchsql_test strict1-3.2 {77 INSERT INTO t1(a) VALUES(1.2);78} {1 {cannot store REAL value in INT column t1.a}}79do_catchsql_test strict1-3.3 {80 INSERT INTO t1(a) VALUES(x'313233');81} {1 {cannot store BLOB value in INT column t1.a}}82do_catchsql_test strict1-3.4 {83 INSERT INTO t1(b) VALUES(1.2);84} {1 {cannot store REAL value in INTEGER column t1.b}}85do_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 89do_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 {}}94do_catchsql_test strict1-4.2 {95 INSERT INTO t1(c) VALUES('456');96} {1 {cannot store TEXT value in BLOB column t1.c}}97 98do_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 {}}103do_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 107do_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 {}}112do_catchsql_test strict1-6.2 {113 INSERT INTO t1(e) VALUES('xyz');114} {1 {cannot store TEXT value in REAL column t1.e}}115do_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 119ifcapable 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 KEY126 ) 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/fa012c77796d9399139# 140reset_db141do_execsql_test strict1-8.1 {142 CREATE TABLE csv_import_table (143 "debit" TEXT,144 "credit" TEXT145 );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 transactions153 SELECT154 nullif(debit, '') AS debit,155 nullif(credit, '') AS credit156 FROM csv_import_table;157 SELECT * FROM transactions;158} {{} 250.0 250.0}159do_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/6caf195248a849e4166#167# Enforce STRICT table type constraints on STORED generated columns168#169do_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)) STORED177 ) 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}187do_catchsql_test strict1-9.2.13 {188 INSERT INTO strict(k) VALUES(13);189} {1 {cannot store TEXT value in REAL column strict.c1}}190do_catchsql_test strict1-9.2.14 {191 INSERT INTO strict(k) VALUES(14);192} {1 {cannot store BLOB value in REAL column strict.c1}}193do_catchsql_test strict1-9.2.21 {194 INSERT INTO strict(k) VALUES(21);195} {1 {cannot store REAL value in INT column strict.c2}}196do_catchsql_test strict1-9.2.23 {197 INSERT INTO strict(k) VALUES(23);198} {1 {cannot store TEXT value in INT column strict.c2}}199do_catchsql_test strict1-9.2.24 {200 INSERT INTO strict(k) VALUES(24);201} {1 {cannot store BLOB value in INT column strict.c2}}202do_catchsql_test strict1-9.2.34 {203 INSERT INTO strict(k) VALUES(34);204} {1 {cannot store BLOB value in TEXT column strict.c3}}205do_catchsql_test strict1-9.2.41 {206 INSERT INTO strict(k) VALUES(41);207} {1 {cannot store REAL value in BLOB column strict.c4}}208do_catchsql_test strict1-9.2.42 {209 INSERT INTO strict(k) VALUES(42);210} {1 {cannot store INT value in BLOB column strict.c4}}211do_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 215do_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)) VIRTUAL224 ) 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}234do_catchsql_test strict1-9.4.13 {235 INSERT INTO strict(k) VALUES(13);236} {1 {cannot store TEXT value in REAL column strict.c1}}237do_catchsql_test strict1-9.4.14 {238 INSERT INTO strict(k) VALUES(14);239} {1 {cannot store BLOB value in REAL column strict.c1}}240do_catchsql_test strict1-9.4.21 {241 INSERT INTO strict(k) VALUES(21);242} {1 {cannot store REAL value in INT column strict.c2}}243do_catchsql_test strict1-9.4.23 {244 INSERT INTO strict(k) VALUES(23);245} {1 {cannot store TEXT value in INT column strict.c2}}246do_catchsql_test strict1-9.4.24 {247 INSERT INTO strict(k) VALUES(24);248} {1 {cannot store BLOB value in INT column strict.c2}}249do_catchsql_test strict1-9.4.34 {250 INSERT INTO strict(k) VALUES(34);251} {1 {cannot store BLOB value in TEXT column strict.c3}}252do_catchsql_test strict1-9.4.41 {253 INSERT INTO strict(k) VALUES(41);254} {1 {cannot store REAL value in BLOB column strict.c4}}255do_catchsql_test strict1-9.4.42 {256 INSERT INTO strict(k) VALUES(42);257} {1 {cannot store INT value in BLOB column strict.c4}}258do_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 262finish_test263