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