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
|
# 2005 August 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 corner cases of the DEFAULT syntax
# on table definitions.
#
# $Id: default.test,v 1.3 2009/02/19 14:39:25 danielk1977 Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable bloblit {
do_test default-1.1 {
execsql {
CREATE TABLE t1(
a INTEGER,
b BLOB DEFAULT x'6869'
);
INSERT INTO t1(a) VALUES(1);
SELECT * from t1;
}
} {1 hi}
}
do_test default-1.2 {
execsql {
CREATE TABLE t2(
x INTEGER,
y INTEGER DEFAULT NULL
);
INSERT INTO t2(x) VALUES(1);
SELECT * FROM t2;
}
} {1 {}}
do_test default-1.3 {
catchsql {
CREATE TABLE t3(
x INTEGER,
y INTEGER DEFAULT (max(x,5))
)
}
} {1 {default value of column [y] is not constant}}
ifcapable pragma {
do_test default-2.1 {
execsql {
CREATE TABLE t4(c DEFAULT 'abc');
PRAGMA table_info(t4);
}
} {0 c {} 0 'abc' 0}
do_test default-2.2 {
execsql {
INSERT INTO t4 DEFAULT VALUES;
PRAGMA table_info(t4);
}
} {0 c {} 0 'abc' 0}
}
do_execsql_test default-3.1 {
CREATE TABLE t3(
a INTEGER PRIMARY KEY AUTOINCREMENT,
b INT DEFAULT 12345 UNIQUE NOT NULL CHECK( b>=0 AND b<99999 ),
c VARCHAR(123,456) DEFAULT 'hello' NOT NULL ON CONFLICT REPLACE,
d REAL,
e FLOATING POINT(5,10) DEFAULT 4.36,
f NATIONAL CHARACTER(15) COLLATE RTRIM,
g LONG INTEGER DEFAULT( 3600*12 )
);
INSERT INTO t3 VALUES(null, 5, 'row1', '5.25', 'xyz', 321, '432');
SELECT a, typeof(a), b, typeof(b), c, typeof(c),
d, typeof(d), e, typeof(e), f, typeof(f),
g, typeof(g) FROM t3;
} {1 integer 5 integer row1 text 5.25 real xyz text 321 text 432 integer}
do_execsql_test default-3.2 {
DELETE FROM t3;
INSERT INTO t3 DEFAULT VALUES;
SELECT * FROM t3;
} {2 12345 hello {} 4.36 {} 43200}
do_execsql_test default-3.3 {
CREATE TABLE t300(
a INT DEFAULT 2147483647,
b INT DEFAULT 2147483648,
c INT DEFAULT +9223372036854775807,
d INT DEFAULT -2147483647,
e INT DEFAULT -2147483648,
f INT DEFAULT -9223372036854775808,
g INT DEFAULT (-(-9223372036854775808)),
h INT DEFAULT (-(-9223372036854775807))
);
INSERT INTO t300 DEFAULT VALUES;
SELECT * FROM t300;
} {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807}
# Do now allow bound parameters in new DEFAULT values.
# Silently convert bound parameters to NULL in DEFAULT causes
# in the sqlite_master table, for backwards compatibility.
#
db close
forcedelete test.db
sqlite3 db test.db
do_execsql_test default-4.0 {
CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99));
PRAGMA writable_schema=ON;
UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))';
} {}
db close
sqlite3 db test.db
do_execsql_test default-4.1 {
INSERT INTO t1(a) VALUES('xyzzy');
SELECT a, quote(b) FROM t1;
} {xyzzy NULL}
do_catchsql_test default-4.2 {
CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz));
} {1 {default value of column [b] is not constant}}
do_catchsql_test default-4.3 {
CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz)));
} {1 {default value of column [b] is not constant}}
do_catchsql_test default-4.4 {
CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz)));
} {1 {default value of column [b] is not constant}}
finish_test
|
