1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
# 2021-08-18
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library. The
# focus of this file is testing STRICT tables.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix strict1
# STRICT tables have on a limited number of allowed datatypes.
#
do_catchsql_test strict1-1.1 {
CREATE TABLE t1(a) STRICT;
} {1 {missing datatype for t1.a}}
do_catchsql_test strict1-1.2 {
CREATE TABLE t1(a PRIMARY KEY) STRICT, WITHOUT ROWID;
} {1 {missing datatype for t1.a}}
do_catchsql_test strict1-1.3 {
CREATE TABLE t1(a PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {missing datatype for t1.a}}
do_catchsql_test strict1-1.4 {
CREATE TABLE t1(a BANJO PRIMARY KEY) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.a: "BANJO"}}
do_catchsql_test strict1-1.5 {
CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f DATE) strict;
} {1 {unknown datatype for t1.f: "DATE"}}
do_catchsql_test strict1-1.6 {
CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INTEGER, d REAL, e BLOB, f TEXT(50)) WITHOUT ROWID, STRICT;
} {1 {unknown datatype for t1.f: "TEXT(50)"}}
do_execsql_test strict1-2.0 {
CREATE TABLE t1(
a INT,
b INTEGER,
c BLOB,
d TEXT,
e REAL
) STRICT;
} {}
ifcapable vtab {
do_execsql_test strict1-2.0a {
SELECT strict FROM pragma_table_list('t1');
} {1}
}
do_catchsql_test strict1-2.1 {
INSERT INTO t1(a) VALUES('xyz');
} {1 {cannot store TEXT value in INT column t1.a}}
do_catchsql_test strict1-2.2 {
INSERT INTO t1(b) VALUES('xyz');
} {1 {cannot store TEXT value in INTEGER column t1.b}}
do_catchsql_test strict1-2.3 {
INSERT INTO t1(c) VALUES('xyz');
} {1 {cannot store TEXT value in BLOB column t1.c}}
do_catchsql_test strict1-2.4 {
INSERT INTO t1(d) VALUES(x'3142536475');
} {1 {cannot store BLOB value in TEXT column t1.d}}
do_catchsql_test strict1-2.5 {
INSERT INTO t1(e) VALUES('xyz');
} {1 {cannot store TEXT value in REAL column t1.e}}
do_execsql_test strict1-3.1 {
INSERT INTO t1(a, b) VALUES(1,2),('3','4'),(5.0, 6.0),(null,null);
SELECT a, b, '|' FROM t1;
} {1 2 | 3 4 | 5 6 | {} {} |}
do_catchsql_test strict1-3.2 {
INSERT INTO t1(a) VALUES(1.2);
} {1 {cannot store REAL value in INT column t1.a}}
do_catchsql_test strict1-3.3 {
INSERT INTO t1(a) VALUES(x'313233');
} {1 {cannot store BLOB value in INT column t1.a}}
do_catchsql_test strict1-3.4 {
INSERT INTO t1(b) VALUES(1.2);
} {1 {cannot store REAL value in INTEGER column t1.b}}
do_catchsql_test strict1-3.5 {
INSERT INTO t1(b) VALUES(x'313233');
} {1 {cannot store BLOB value in INTEGER column t1.b}}
do_execsql_test strict1-4.1 {
DELETE FROM t1;
INSERT INTO t1(c) VALUES(x'313233'), (NULL);
SELECT typeof(c), c FROM t1;
} {blob 123 null {}}
do_catchsql_test strict1-4.2 {
INSERT INTO t1(c) VALUES('456');
} {1 {cannot store TEXT value in BLOB column t1.c}}
do_execsql_test strict1-5.1 {
DELETE FROM t1;
INSERT INTO t1(d) VALUES('xyz'),(4),(5.5),(NULL);
SELECT typeof(d), d FROM t1;
} {text xyz text 4 text 5.5 null {}}
do_catchsql_test strict1-5.2 {
INSERT INTO t1(d) VALUES(x'4567');
} {1 {cannot store BLOB value in TEXT column t1.d}}
do_execsql_test strict1-6.1 {
DELETE FROM t1;
INSERT INTO t1(e) VALUES(1),(2.5),('3'),('4.5'),(6.0),(NULL);
SELECT typeof(e), e FROM t1;
} {real 1.0 real 2.5 real 3.0 real 4.5 real 6.0 null {}}
do_catchsql_test strict1-6.2 {
INSERT INTO t1(e) VALUES('xyz');
} {1 {cannot store TEXT value in REAL column t1.e}}
do_catchsql_test strict1-6.3 {
INSERT INTO t1(e) VALUES(x'3456');
} {1 {cannot store BLOB value in REAL column t1.e}}
ifcapable altertable {
do_execsql_test strict1-7.1 {
DROP TABLE IF EXISTS t4;
CREATE TABLE t4(
a INT AS (b*2) VIRTUAL,
b INT AS (c*2) STORED,
c INT PRIMARY KEY
) STRICT;
INSERT INTO t4(c) VALUES(1);
SELECT * FROM t4;
} {4 2 1}
do_catchsql_test strict1-7.2 {
ALTER TABLE t4 ADD COLUMN d VARCHAR;
} {1 {error in table t4 after add column: unknown datatype for t4.d: "VARCHAR"}}
do_catchsql_test strict1-7.3 {
ALTER TABLE t4 ADD COLUMN d;
} {1 {error in table t4 after add column: missing datatype for t4.d}}
}
# 2022-01-17 https://sqlite.org/forum/forumpost/fa012c77796d9399
#
reset_db
do_execsql_test strict1-8.1 {
CREATE TABLE csv_import_table (
"debit" TEXT,
"credit" TEXT
);
INSERT INTO csv_import_table VALUES ('', '250.00');
CREATE TABLE IF NOT EXISTS transactions (
debit REAL,
credit REAL,
amount REAL GENERATED ALWAYS AS (ifnull(credit, 0.0) - ifnull(debit, 0.0))
) STRICT;
INSERT INTO transactions
SELECT
nullif(debit, '') AS debit,
nullif(credit, '') AS credit
FROM csv_import_table;
SELECT * FROM transactions;
} {{} 250.0 250.0}
do_execsql_test strict1-8.2 {
CREATE TABLE t1(x REAL, y REAL AS (x)) STRICT;
INSERT INTO t1 VALUES(5),(4611686018427387904);
SELECT *, '|' FROM t1;
} {/5.0 5.0 4.6116\d*e\+18 4.6116\d+e\+18 |/}
finish_test
|
