AryaWu/sqlite

1# 2016 December 152#3# The author disclaims copyright to this source code.  In place of4# 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: shell12#13# Test the shell tool ".lint fkey-indexes" command.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18ifcapable !vtab {finish_test; return}19set testprefix shell620set CLI [test_find_cli]21db close22forcedelete test.db test.db-journal test.db-wal23 24foreach {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.db107  sqlite3 db test.db108  execsql $schema109 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 close128}129 130finish_test131