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