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