xref: /sqlite-3.40.0/test/shell6.test (revision 87f500ce) 
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} {
77  forcedelete test.db
78  sqlite3 db test.db
79  execsql $schema
80
81  set expected ""
82  foreach line [split $output "\n"] {
83    set line [string trim $line]
84    if {$line!=""} {
85      append expected "$line\n"
86    }
87  }
88
89  do_test 1.$tn.1 {
90    set RES [catchcmd test.db [list .lint fkey-indexes]]
91  } [list 0 [string trim $expected]]
92
93  do_test 1.$tn.2 {
94    execsql [lindex $RES 1]
95    catchcmd test.db [list .lint fkey-indexes]
96  } {0 {}}
97
98  db close
99}
100
101finish_test
102