github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/shell6.test (about)

     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  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  ifcapable !vtab {finish_test; return}
    18  set testprefix shell6
    19  set CLI [test_find_cli]
    20  db close
    21  forcedelete test.db test.db-journal test.db-wal
    22  
    23  foreach {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    7 {
    77      CREATE TABLE x1(a PRIMARY KEY COLLATE nocase, b);
    78      CREATE TABLE y1(a REFERENCES x1);
    79    } {
    80      CREATE INDEX 'y1_a' ON 'y1'('a' COLLATE nocase); --> x1(a)
    81    }
    82  
    83    8 {
    84      CREATE TABLE x1(a, b COLLATE nocase, c COLLATE rtrim, PRIMARY KEY(c, b, a));
    85      CREATE TABLE y1(d, e, f, FOREIGN KEY(d, e, f) REFERENCES x1);
    86    } {
    87      CREATE INDEX 'y1_d_e_f' ON 'y1'('d' COLLATE rtrim, 'e' COLLATE nocase, 'f'); --> x1(c,b,a)
    88    }
    89  
    90    9 {
    91      CREATE TABLE p1(a, b UNIQUE);
    92      CREATE TABLE c1(x INTEGER PRIMARY KEY REFERENCES p1(b));
    93    } {
    94    }
    95  
    96    10 {
    97      CREATE TABLE parent (id INTEGER PRIMARY KEY); 
    98      CREATE TABLE child2 (id INT PRIMARY KEY, parentID INT REFERENCES parent) 
    99        WITHOUT ROWID;
   100    } {
   101      CREATE INDEX 'child2_parentID' ON 'child2'('parentID'); --> parent(id)
   102    }
   103  
   104  } {
   105    forcedelete test.db
   106    sqlite3 db test.db
   107    execsql $schema
   108  
   109    set expected ""
   110    foreach line [split $output "\n"] {
   111      set line [string trim $line]
   112      if {$line!=""} {
   113        append expected "$line\n"
   114      }
   115    }
   116  
   117    do_test 1.$tn.1 {
   118      set RES [catchcmd test.db [list .lint fkey-indexes]]
   119    } [list 0 [string trim $expected]]
   120  
   121    do_test 1.$tn.2 {
   122      execsql [lindex $RES 1]
   123      catchcmd test.db [list .lint fkey-indexes]
   124    } {0 {}}
   125  
   126    db close
   127  }
   128  
   129  finish_test