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

     1  # 2009 September 22
     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  #
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  
    17  ifcapable !foreignkey||!trigger {
    18    finish_test
    19    return
    20  }
    21  source $testdir/malloc_common.tcl
    22  
    23  do_malloc_test fkey_malloc-1 -sqlprep {
    24    PRAGMA foreign_keys = 1;
    25    CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
    26    CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
    27  } -sqlbody {
    28    INSERT INTO t1 VALUES('aaa', 1);
    29    INSERT INTO t2 VALUES('aaa');
    30    UPDATE t1 SET a = 'bbb';
    31    DELETE FROM t1;
    32    PRAGMA foreign_key_check;
    33  }
    34  
    35  do_malloc_test fkey_malloc-2 -sqlprep {
    36    PRAGMA foreign_keys = 1;
    37    CREATE TABLE t1(a, b, UNIQUE(a, b));
    38  } -sqlbody {
    39    CREATE TABLE t2(x, y, 
    40      FOREIGN KEY(x, y) REFERENCES t1(a, b) DEFERRABLE INITIALLY DEFERRED
    41    );
    42    BEGIN;
    43      INSERT INTO t2 VALUES('a', 'b');
    44      INSERT INTO t1 VALUES('a', 'b');
    45      UPDATE t1 SET a = 'c';
    46      DELETE FROM t2;
    47      INSERT INTO t2 VALUES('d', 'b');
    48      UPDATE t2 SET x = 'c';
    49    COMMIT;
    50  }
    51  
    52  do_malloc_test fkey_malloc-3 -sqlprep {
    53    PRAGMA foreign_keys = 1;
    54    CREATE TABLE t1(x INTEGER PRIMARY KEY);
    55    CREATE TABLE t2(y DEFAULT 14 REFERENCES t1(x) ON UPDATE SET DEFAULT);
    56    CREATE TABLE t3(y REFERENCES t1 ON UPDATE SET NULL);
    57    INSERT INTO t1 VALUES(13);
    58    INSERT INTO t2 VALUES(13);
    59    INSERT INTO t3 VALUES(13);
    60  } -sqlbody {
    61    UPDATE t1 SET x = 14;
    62  }
    63  
    64  proc catch_fk_error {zSql} {
    65    set rc [catch {db eval $zSql} msg]
    66    if {$rc==0} {
    67      return $msg
    68    }
    69    if {[string match {*foreign key*} $msg]} {
    70      return ""
    71    }
    72    if {$msg eq "out of memory" 
    73     || $msg eq "FOREIGN KEY constraint failed"
    74     || $msg eq "constraint failed"
    75    } {
    76      error 1
    77    }
    78    error $msg
    79  }
    80  
    81  do_malloc_test fkey_malloc-4 -sqlprep {
    82    PRAGMA foreign_keys = 1;
    83    CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE);
    84    CREATE TABLE t2(z REFERENCES t1(x), a REFERENCES t1(y));
    85    CREATE TABLE t3(x);
    86    CREATE TABLE t4(z REFERENCES t3);
    87    CREATE TABLE t5(x, y);
    88    CREATE TABLE t6(z REFERENCES t5(x));
    89    CREATE INDEX i51 ON t5(x);
    90    CREATE INDEX i52 ON t5(y, x);
    91    INSERT INTO t1 VALUES(1, 2);
    92  } -tclbody {
    93    catch_fk_error {INSERT INTO t2 VALUES(1, 3)}
    94    catch_fk_error {INSERT INTO t4 VALUES(2)}
    95    catch_fk_error {INSERT INTO t6 VALUES(2)}
    96  }
    97  
    98  do_malloc_test fkey_malloc-5 -sqlprep {
    99    PRAGMA foreign_keys = 1;
   100    CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
   101    CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);
   102    INSERT INTO t1 VALUES(1, 2);
   103    INSERT INTO t2 VALUES(1, 2);
   104  } -sqlbody {
   105    UPDATE t1 SET x = 5;
   106  }
   107  
   108  do_malloc_test fkey_malloc-6 -sqlprep {
   109    PRAGMA foreign_keys = 1;
   110    CREATE TABLE t1(
   111      x PRIMARY KEY, 
   112      y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT
   113    );
   114    INSERT INTO t1 VALUES('abc', 'abc');
   115    INSERT INTO t1 VALUES('def', 'def');
   116  } -sqlbody {
   117    INSERT INTO t1 VALUES('ghi', 'ghi');
   118    DELETE FROM t1 WHERE rowid>1;
   119    UPDATE t1 SET x='jkl', y='jkl';
   120  }
   121  
   122  do_malloc_test fkey_malloc-7 -sqlprep {
   123    PRAGMA foreign_keys = 1;
   124    CREATE TABLE x(a, b, PRIMARY KEY(a, b));
   125    CREATE TABLE y(c, d,
   126      FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED
   127    );
   128    CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
   129  } -sqlbody {
   130    DROP TABLE y;
   131    DROP TABLE x;
   132  }
   133  
   134  finish_test