Debian Codegraph @

test/shell6.test

raw
1 # 2016 December 15
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # TESTRUNNER: shell
12 #
13 # Test the shell tool ".lint fkey-indexes" command.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 ifcapable !vtab {finish_test; return}
19 set testprefix shell6
20 set CLI [test_find_cli]
21 db close
22 forcedelete test.db test.db-journal test.db-wal
23
24 foreach {tn schema output} {
25 1 {
26 CREATE TABLE p1(a PRIMARY KEY, b);
27 CREATE TABLE c1(x, y REFERENCES p1);
28 } {
29 CREATE INDEX 'c1_y' ON 'c1'('y'); --> p1(a)
30 }
31
32 2 {
33 CREATE TABLE p1(a PRIMARY KEY, b);
34 CREATE TABLE c2(x REFERENCES p1, y REFERENCES p1);
35 } {
36 CREATE INDEX 'c2_y' ON 'c2'('y'); --> p1(a)
37 CREATE INDEX 'c2_x' ON 'c2'('x'); --> p1(a)
38 }
39
40 3 {
41 CREATE TABLE 'p 1'(a, b, c, PRIMARY KEY(c, b));
42 CREATE TABLE 'c 1'(x, y, z, FOREIGN KEY (z, y) REFERENCES 'p 1');
43 } {
44 CREATE INDEX 'c 1_z_y' ON 'c 1'('z', 'y'); --> p 1(c,b)
45 }
46
47 4 {
48 CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
49 CREATE TABLE c1('x y z' REFERENCES p1);
50 CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT NULL;
51 } {
52 }
53
54 5 {
55 CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
56 CREATE TABLE c1('x y z' REFERENCES p1);
57 CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT 12;
58 } {
59 CREATE INDEX 'c1_x y z' ON 'c1'('x y z'); --> p1(b b b)
60 }
61
62 6 {
63 CREATE TABLE x1(a, b, c, UNIQUE(a, b));
64 CREATE TABLE y1(a, b, c, FOREIGN KEY(b, a) REFERENCES x1(a, b));
65 CREATE INDEX y1i ON y1(a, c, b);
66 } {
67 CREATE INDEX 'y1_b_a' ON 'y1'('b', 'a'); --> x1(a,b)
68 }
69
70 6 {
71 CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
72 CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
73 } {
74 CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
75 }
76
77 7 {
78 CREATE TABLE x1(a PRIMARY KEY COLLATE nocase, b);
79 CREATE TABLE y1(a REFERENCES x1);
80 } {
81 CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
82 }
83
84 8 {
85 CREATE TABLE x1(a, b COLLATE nocase, c COLLATE rtrim, PRIMARY KEY(c, b, a));
86 CREATE TABLE y1(d, e, f, FOREIGN KEY(d, e, f) REFERENCES x1);
87 } {
88 CREATE INDEX 'y1_d_e_f' ON 'y1'('d' COLLATE rtrim, 'e' COLLATE nocase, 'f'); --> x1(c,b,a)
89 }
90
91 9 {
92 CREATE TABLE p1(a, b UNIQUE);
93 CREATE TABLE c1(x INTEGER PRIMARY KEY REFERENCES p1(b));
94 } {
95 }
96
97 10 {
98 CREATE TABLE parent (id INTEGER PRIMARY KEY);
99 CREATE TABLE child2 (id INT PRIMARY KEY, parentID INT REFERENCES parent)
100 WITHOUT ROWID;
101 } {
102 CREATE INDEX 'child2_parentID' ON 'child2'('parentID'); --> parent(id)
103 }
104
105 } {
106 forcedelete test.db
107 sqlite3 db test.db
108 execsql $schema
109
110 set expected ""
111 foreach line [split $output "\n"] {
112 set line [string trim $line]
113 if {$line!=""} {
114 append expected "$line\n"
115 }
116 }
117
118 do_test 1.$tn.1 {
119 set RES [catchcmd test.db [list .lint fkey-indexes]]
120 } [list 0 [string trim $expected]]
121
122 do_test 1.$tn.2 {
123 execsql [lindex $RES 1]
124 catchcmd test.db [list .lint fkey-indexes]
125 } {0 {}}
126
127 db close
128 }
129
130 finish_test