xref: /sqlite-3.40.0/test/shell6.test (revision 11da002c) 
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
17set testprefix shell6
18set CLI [test_find_cli]
19db close
20forcedelete test.db test.db-journal test.db-wal
21
22foreach {tn schema output} {
23  1 {
24    CREATE TABLE p1(a PRIMARY KEY, b);
25    CREATE TABLE c1(x, y REFERENCES p1);
26  } {
27    CREATE INDEX 'c1_y' ON 'c1'('y'); --> p1(a)
28  }
29
30  2 {
31    CREATE TABLE p1(a PRIMARY KEY, b);
32    CREATE TABLE c2(x REFERENCES p1, y REFERENCES p1);
33  } {
34    CREATE INDEX 'c2_y' ON 'c2'('y'); --> p1(a)
35    CREATE INDEX 'c2_x' ON 'c2'('x'); --> p1(a)
36  }
37
38  3 {
39    CREATE TABLE 'p 1'(a, b, c, PRIMARY KEY(c, b));
40    CREATE TABLE 'c 1'(x, y, z, FOREIGN KEY (z, y) REFERENCES 'p 1');
41  } {
42    CREATE INDEX 'c 1_z_y' ON 'c 1'('z', 'y'); --> p 1(c,b)
43  }
44
45  4 {
46    CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
47    CREATE TABLE c1('x y z' REFERENCES p1);
48    CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT NULL;
49  } {
50  }
51
52  5 {
53    CREATE TABLE p1(a, 'b b b' PRIMARY KEY);
54    CREATE TABLE c1('x y z' REFERENCES p1);
55    CREATE INDEX i1 ON c1('x y z') WHERE "x y z" IS NOT 12;
56  } {
57    CREATE INDEX 'c1_x y z' ON 'c1'('x y z'); --> p1(b b b)
58  }
59
60  6 {
61    CREATE TABLE x1(a, b, c, UNIQUE(a, b));
62    CREATE TABLE y1(a, b, c, FOREIGN KEY(b, a) REFERENCES x1(a, b));
63    CREATE INDEX y1i ON y1(a, c, b);
64  } {
65    CREATE INDEX 'y1_b_a' ON 'y1'('b', 'a'); --> x1(a,b)
66  }
67
68  6 {
69    CREATE TABLE x1(a COLLATE nocase, b, UNIQUE(a));
70    CREATE TABLE y1(a COLLATE rtrim REFERENCES x1(a));
71  } {
72    CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
73  }
74
75} {
76  forcedelete test.db
77  sqlite3 db test.db
78  execsql $schema
79
80  set expected ""
81  foreach line [split $output "\n"] {
82    set line [string trim $line]
83    if {$line!=""} {
84      append expected "$line\n"
85    }
86  }
87
88  do_test 1.$tn.1 {
89    set RES [catchcmd test.db [list .lint fkey-indexes]]
90  } [list 0 [string trim $expected]]
91
92  do_test 1.$tn.2 {
93    execsql [lindex $RES 1]
94    catchcmd test.db [list .lint fkey-indexes]
95  } {0 {}}
96
97  db close
98}
99
100finish_test
101
102
103