gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/without_rowid2.test (about)

     1  # 2013-11-02
     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  # This file implements regression tests for SQLite library.  The
    13  # focus of this file is testing WITHOUT ROWID tables, and especially
    14  # FOREIGN KEY constraints.
    15  #
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  ifcapable {!foreignkey} {
    21    finish_test
    22    return
    23  }
    24  
    25  # Create a table and some data to work with.
    26  #
    27  do_test without_rowid2-1.0 {
    28    execsql {
    29      CREATE TABLE t1(
    30        a INT PRIMARY KEY,
    31        b INT
    32             REFERENCES t1 ON DELETE CASCADE
    33             REFERENCES t2,
    34        c TEXT,
    35        FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
    36      ) WITHOUT rowid;
    37    }
    38  } {}
    39  do_test without_rowid2-1.1 {
    40    execsql {
    41      CREATE TABLE t2(
    42        x INT PRIMARY KEY,
    43        y TEXT
    44      ) WITHOUT rowid;
    45    }
    46  } {}
    47  do_test without_rowid2-1.2 {
    48    execsql {
    49      CREATE TABLE t3(
    50        a INT REFERENCES t2,
    51        b INT REFERENCES t1,
    52        FOREIGN KEY (a,b) REFERENCES t2(x,y)
    53      );
    54    }
    55  } {}
    56  
    57  do_test without_rowid2-2.1 {
    58    execsql {
    59      CREATE TABLE t4(a int primary key) WITHOUT rowid;
    60      CREATE TABLE t5(x references t4);
    61      CREATE TABLE t6(x references t4);
    62      CREATE TABLE t7(x references t4);
    63      CREATE TABLE t8(x references t4);
    64      CREATE TABLE t9(x references t4);
    65      CREATE TABLE t10(x references t4);
    66      DROP TABLE t7;
    67      DROP TABLE t9;
    68      DROP TABLE t5;
    69      DROP TABLE t8;
    70      DROP TABLE t6;
    71      DROP TABLE t10;
    72    }
    73  } {}
    74  
    75  do_test without_rowid2-3.1 {
    76    execsql {
    77      CREATE TABLE t5(a PRIMARY KEY, b, c) WITHOUT rowid;
    78      CREATE TABLE t6(
    79        d REFERENCES t5,
    80        e REFERENCES t5(c)
    81      );
    82      PRAGMA foreign_key_list(t6);
    83    }
    84  } [concat                                         \
    85    {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
    86    {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
    87  ]
    88  do_test without_rowid2-3.2 {
    89    execsql {
    90      CREATE TABLE t7(d, e, f,
    91        FOREIGN KEY (d, e) REFERENCES t5(a, b)
    92      );
    93      PRAGMA foreign_key_list(t7);
    94    }
    95  } [concat                                   \
    96    {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
    97    {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
    98  ]
    99  do_test without_rowid2-3.3 {
   100    execsql {
   101      CREATE TABLE t8(d, e, f,
   102        FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
   103      );
   104      PRAGMA foreign_key_list(t8);
   105    }
   106  } [concat                        \
   107    {0 0 t5 d {} {SET NULL} CASCADE NONE} \
   108    {0 1 t5 e {} {SET NULL} CASCADE NONE} \
   109  ]
   110  do_test without_rowid2-3.4 {
   111    execsql {
   112      CREATE TABLE t9(d, e, f,
   113        FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
   114      );
   115      PRAGMA foreign_key_list(t9);
   116    }
   117  } [concat                        \
   118    {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
   119    {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
   120  ]
   121  do_test without_rowid2-3.5 {
   122    sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
   123  } {0 0 0}
   124  
   125  finish_test