modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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  } {
    97    forcedelete test.db
    98    sqlite3 db test.db
    99    execsql $schema
   100  
   101    set expected ""
   102    foreach line [split $output "\n"] {
   103      set line [string trim $line]
   104      if {$line!=""} {
   105        append expected "$line\n"
   106      }
   107    }
   108  
   109    do_test 1.$tn.1 {
   110      set RES [catchcmd test.db [list .lint fkey-indexes]]
   111    } [list 0 [string trim $expected]]
   112  
   113    do_test 1.$tn.2 {
   114      execsql [lindex $RES 1]
   115      catchcmd test.db [list .lint fkey-indexes]
   116    } {0 {}}
   117  
   118    db close
   119  }
   120  
   121  finish_test