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
|
# 2016 December 15
#
# 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.
#
#***********************************************************************
#
# Test the shell tool ".lint fkey-indexes" command.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !vtab {finish_test; return}
set testprefix shell6
set CLI [test_find_cli]
db close
forcedelete test.db test.db-journal test.db-wal
foreach {tn schema output} {
1 {
CREATE TABLE p1(a PRIMARY KEY, b);
CREATE TABLE c1(x, y REFERENCES p1);
} {
CREATE INDEX 'c1_y' ON 'c1'('y'); --> p1(a)
}
2 {
CREATE TABLE p1(a PRIMARY KEY, b);
CREATE TABLE c2(x REFERENCES p1, y REFERENCES p1);
} {
CREATE INDEX 'c2_y' ON 'c2'('y'); --> p1(a)
CREATE INDEX 'c2_x' ON 'c2'('x'); --> p1(a)
}
3 {
CREATE TABLE 'p 1'(a, b, c, PRIMARY KEY(c, b));
CREATE TABLE 'c 1'(x, y, z, FOREIGN KEY (z, y) REFERENCES 'p 1');
} {
CREATE INDEX 'c 1_z_y' ON 'c 1'('z', 'y'); --> p 1(c,b)
}
4 {
CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
CREATE TABLE c1('x y z' REFERENCES p1);
CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT NULL;
} {
}
5 {
CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
CREATE TABLE c1('x y z' REFERENCES p1);
CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT 12;
} {
CREATE INDEX 'c1_x y z' ON 'c1'('x y z'); --> p1(b b b)
}
6 {
CREATE TABLE x1(a, b, c, UNIQUE(a, b));
CREATE TABLE y1(a, b, c, FOREIGN KEY(b, a) REFERENCES x1(a, b));
CREATE INDEX y1i ON y1(a, c, b);
} {
CREATE INDEX 'y1_b_a' ON 'y1'('b', 'a'); --> x1(a,b)
}
6 {
CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
} {
CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
}
7 {
CREATE TABLE x1(a PRIMARY KEY COLLATE nocase, b);
CREATE TABLE y1(a REFERENCES x1);
} {
CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
}
8 {
CREATE TABLE x1(a, b COLLATE nocase, c COLLATE rtrim, PRIMARY KEY(c, b, a));
CREATE TABLE y1(d, e, f, FOREIGN KEY(d, e, f) REFERENCES x1);
} {
CREATE INDEX 'y1_d_e_f' ON 'y1'('d' COLLATE rtrim, 'e' COLLATE nocase, 'f'); --> x1(c,b,a)
}
9 {
CREATE TABLE p1(a, b UNIQUE);
CREATE TABLE c1(x INTEGER PRIMARY KEY REFERENCES p1(b));
} {
}
10 {
CREATE TABLE parent (id INTEGER PRIMARY KEY);
CREATE TABLE child2 (id INT PRIMARY KEY, parentID INT REFERENCES parent)
WITHOUT ROWID;
} {
CREATE INDEX 'child2_parentID' ON 'child2'('parentID'); --> parent(id)
}
} {
forcedelete test.db
sqlite3 db test.db
execsql $schema
set expected ""
foreach line [split $output "\n"] {
set line [string trim $line]
if {$line!=""} {
append expected "$line\n"
}
}
do_test 1.$tn.1 {
set RES [catchcmd test.db [list .lint fkey-indexes]]
} [list 0 [string trim $expected]]
do_test 1.$tn.2 {
execsql [lindex $RES 1]
catchcmd test.db [list .lint fkey-indexes]
} {0 {}}
db close
}
finish_test
|
