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
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(11)
--!./tcltestrunner.lua
-- 2011 August 22
--
-- 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 sql library.
--
-- This file implements tests for foreign keys. Specifically, it tests
-- that ticket b1d3a2e531 has been fixed.
--
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
testprefix = "tkt-b1d3a2e531"
test:do_execsql_test(
1.1,
[[
CREATE TABLE pp(x TEXT PRIMARY KEY);
CREATE TABLE cc(y TEXT primary key REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
INSERT INTO pp VALUES('abc');
INSERT INTO cc VALUES('abc');
]])
test:do_execsql_test(
1.2,
[[
DROP TABLE cc;
DROP TABLE pp;
]])
test:do_execsql_test(
1.3,
[[
CREATE TABLE pp(x TEXT PRIMARY KEY);
CREATE TABLE cc(y TEXT primary key REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
INSERT INTO pp VALUES('abc');
INSERT INTO cc VALUES('abc');
]])
test:do_execsql_test(
1.4,
[[
DROP TABLE cc;
DROP TABLE pp;
]])
test:do_execsql_test(
2.1,
[[
CREATE TABLE pp(x INTEGER PRIMARY KEY);
CREATE TABLE cc(
y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO pp VALUES(5);
INSERT INTO cc VALUES(5);
]])
test:do_execsql_test(
2.2,
[[
DROP TABLE cc;
DROP TABLE pp;
]])
test:do_execsql_test(
2.3,
[[
CREATE TABLE pp(x INTEGER PRIMARY KEY);
CREATE TABLE cc(
y INTEGER PRIMARY KEY REFERENCES pp DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO pp VALUES(5);
INSERT INTO cc VALUES(5);
]])
test:do_execsql_test(
2.4,
[[
DROP TABLE cc;
DROP TABLE pp;
]])
test:do_execsql_test(
3.1,
[[
CREATE TABLE pp1(x INT PRIMARY KEY);
CREATE TABLE cc1(y INT REFERENCES pp1 DEFERRABLE INITIALLY DEFERRED, a INT primary key);
CREATE TABLE pp2(x INT PRIMARY KEY);
CREATE TABLE cc2(y INT primary key REFERENCES pp1 DEFERRABLE INITIALLY DEFERRED);
INSERT INTO pp1 VALUES(2200);
INSERT INTO cc1 VALUES(NULL, 1);
INSERT INTO pp2 VALUES(2200);
INSERT INTO cc2 VALUES(2200);
]])
test:do_catchsql_test(
3.2,
[[
-- START TRANSACTION;
DELETE FROM pp2;
-- COMMIT;
DROP TABLE pp1;
DROP TABLE cc1;
]], {
-- <3.2>
1, "Can't drop space 'PP1': other objects depend on it"
-- </3.2>
})
test:do_catchsql_test(
3.3,
[[
DROP TABLE cc2;
-- COMMIT;
]], {
-- <3.3>
0
-- </3.3>
})
test:finish_test()
|
