# 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