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
|
# 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.
#
#***********************************************************************
# TESTRUNNER: shell
#
# 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
|
