1# 2013 July 04 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 tests that the sessions module handles foreign key constraint 13# violations when applying changesets as required. 14# 15 16if {![info exists testdir]} { 17 set testdir [file join [file dirname [info script]] .. .. test] 18} 19source [file join [file dirname [info script]] session_common.tcl] 20source $testdir/tester.tcl 21ifcapable !session {finish_test; return} 22set testprefix session9 23 24 25#-------------------------------------------------------------------- 26# Basic tests. 27# 28proc populate_db {} { 29 drop_all_tables 30 execsql { 31 PRAGMA foreign_keys = 1; 32 CREATE TABLE p1(a PRIMARY KEY, b); 33 CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1); 34 CREATE TABLE c2(a PRIMARY KEY, 35 b REFERENCES p1 DEFERRABLE INITIALLY DEFERRED 36 ); 37 38 INSERT INTO p1 VALUES(1, 'one'); 39 INSERT INTO p1 VALUES(2, 'two'); 40 INSERT INTO p1 VALUES(3, 'three'); 41 INSERT INTO p1 VALUES(4, 'four'); 42 } 43} 44 45proc capture_changeset {sql} { 46 sqlite3session S db main 47 48 foreach t [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { 49 S attach $t 50 } 51 execsql $sql 52 set ret [S changeset] 53 S delete 54 55 return $ret 56} 57 58do_test 1.1 { 59 populate_db 60 set cc [capture_changeset { 61 INSERT INTO c1 VALUES('ii', 2); 62 INSERT INTO c2 VALUES('iii', 3); 63 }] 64 set {} {} 65} {} 66 67proc xConflict {args} { 68 lappend ::xConflict {*}$args 69 return $::conflictret 70} 71 72foreach {tn delrow trans conflictargs conflictret} { 73 1 2 0 {FOREIGN_KEY 1} OMIT 74 2 3 0 {FOREIGN_KEY 1} OMIT 75 3 2 1 {FOREIGN_KEY 1} OMIT 76 4 3 1 {FOREIGN_KEY 1} OMIT 77 5 2 0 {FOREIGN_KEY 1} ABORT 78 6 3 0 {FOREIGN_KEY 1} ABORT 79 7 2 1 {FOREIGN_KEY 1} ABORT 80 8 3 1 {FOREIGN_KEY 1} ABORT 81} { 82 83 set A(OMIT) {0 {}} 84 set A(ABORT) {1 SQLITE_CONSTRAINT} 85 do_test 1.2.$tn.1 { 86 populate_db 87 execsql { DELETE FROM p1 WHERE a=($delrow+0) } 88 if {$trans} { execsql BEGIN } 89 90 set ::xConflict [list] 91 list [catch {sqlite3changeset_apply db $::cc xConflict} msg] $msg 92 } $A($conflictret) 93 94 do_test 1.2.$tn.2 { set ::xConflict } $conflictargs 95 96 set A(OMIT) {1 1} 97 set A(ABORT) {0 0} 98 do_test 1.2.$tn.3 { 99 execsql { SELECT count(*) FROM c1 UNION ALL SELECT count(*) FROM c2 } 100 } $A($conflictret) 101 102 do_test 1.2.$tn.4 { expr ![sqlite3_get_autocommit db] } $trans 103 do_test 1.2.$tn.5 { 104 if { $trans } { execsql COMMIT } 105 } {} 106} 107 108#-------------------------------------------------------------------- 109# Test that closing a transaction clears the defer_foreign_keys flag. 110# 111foreach {tn open noclose close} { 112 1 BEGIN {} COMMIT 113 2 BEGIN {} ROLLBACK 114 115 3 {SAVEPOINT one} {} {RELEASE one} 116 4 {SAVEPOINT one} {ROLLBACK TO one} {RELEASE one} 117} { 118 execsql $open 119 do_execsql_test 2.$tn.1 { PRAGMA defer_foreign_keys } {0} 120 121 do_execsql_test 2.$tn.2 { 122 PRAGMA defer_foreign_keys = 1; 123 PRAGMA defer_foreign_keys; 124 } {1} 125 126 execsql $noclose 127 do_execsql_test 2.$tn.3 { PRAGMA defer_foreign_keys } {1} 128 129 execsql $close 130 do_execsql_test 2.$tn.4 { PRAGMA defer_foreign_keys } {0} 131} 132 133#-------------------------------------------------------------------- 134# Test that a cyclic relationship can be inserted and deleted. 135# 136# This situation does not come up in practice, but testing it serves to 137# show that it does not matter which order parent and child keys 138# are processed in internally when applying a changeset. 139# 140drop_all_tables 141 142do_execsql_test 3.1 { 143 CREATE TABLE t1(a PRIMARY KEY, b); 144 CREATE TABLE t2(x PRIMARY KEY, y); 145} 146 147# Create changesets as follows: 148# 149# $cc1 - Insert a row into t1. 150# $cc2 - Insert a row into t2. 151# $cc - Combination of $cc1 and $cc2. 152# 153# $ccdel1 - Delete the row from t1. 154# $ccdel2 - Delete the row from t2. 155# $ccdel - Combination of $cc1 and $cc2. 156# 157do_test 3.2 { 158 set cc1 [capture_changeset { 159 INSERT INTO t1 VALUES('one', 'value one'); 160 }] 161 set ccdel1 [capture_changeset { DELETE FROM t1; }] 162 set cc2 [capture_changeset { 163 INSERT INTO t2 VALUES('value one', 'one'); 164 }] 165 set ccdel2 [capture_changeset { DELETE FROM t2; }] 166 set cc [capture_changeset { 167 INSERT INTO t1 VALUES('one', 'value one'); 168 INSERT INTO t2 VALUES('value one', 'one'); 169 }] 170 set ccdel [capture_changeset { 171 DELETE FROM t1; 172 DELETE FROM t2; 173 }] 174 set {} {} 175} {} 176 177# Now modify the database schema to create a cyclic foreign key dependency 178# between tables t1 and t2. This means that although changesets $cc and 179# $ccdel can be applied, none of the others may without violating the 180# foreign key constraints. 181# 182do_test 3.3 { 183 184 drop_all_tables 185 execsql { 186 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t2); 187 CREATE TABLE t2(x PRIMARY KEY, y REFERENCES t1); 188 } 189 190 191 proc conflict_handler {args} { return "ABORT" } 192 sqlite3changeset_apply db $cc conflict_handler 193 194 execsql { 195 SELECT * FROM t1; 196 SELECT * FROM t2; 197 } 198} {one {value one} {value one} one} 199 200do_test 3.3.1 { 201 list [catch {sqlite3changeset_apply db $::ccdel1 conflict_handler} msg] $msg 202} {1 SQLITE_CONSTRAINT} 203 204do_test 3.3.2 { 205 list [catch {sqlite3changeset_apply db $::ccdel2 conflict_handler} msg] $msg 206} {1 SQLITE_CONSTRAINT} 207 208do_test 3.3.4.1 { 209 list [catch {sqlite3changeset_apply db $::ccdel conflict_handler} msg] $msg 210} {0 {}} 211do_execsql_test 3.3.4.2 { 212 SELECT * FROM t1; 213 SELECT * FROM t2; 214} {} 215 216do_test 3.5.1 { 217 list [catch {sqlite3changeset_apply db $::cc1 conflict_handler} msg] $msg 218} {1 SQLITE_CONSTRAINT} 219do_test 3.5.2 { 220 list [catch {sqlite3changeset_apply db $::cc2 conflict_handler} msg] $msg 221} {1 SQLITE_CONSTRAINT} 222 223#-------------------------------------------------------------------- 224# Test that if a change that affects FK processing is not applied 225# due to a separate constraint, SQLite does not get confused and 226# increment FK counters anyway. 227# 228drop_all_tables 229do_execsql_test 4.1 { 230 CREATE TABLE p1(x PRIMARY KEY, y); 231 CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1); 232 INSERT INTO p1 VALUES(1,1); 233} 234 235do_execsql_test 4.2.1 { 236 BEGIN; 237 PRAGMA defer_foreign_keys = 1; 238 INSERT INTO c1 VALUES('x', 'x'); 239} 240do_catchsql_test 4.2.2 { COMMIT } {1 {FOREIGN KEY constraint failed}} 241do_catchsql_test 4.2.3 { ROLLBACK } {0 {}} 242 243do_execsql_test 4.3.1 { 244 BEGIN; 245 PRAGMA defer_foreign_keys = 1; 246 INSERT INTO c1 VALUES(1, 1); 247} 248do_catchsql_test 4.3.2 { 249 INSERT INTO c1 VALUES(1, 'x') 250} {1 {UNIQUE constraint failed: c1.a}} 251 252do_catchsql_test 4.3.3 { COMMIT } {0 {}} 253do_catchsql_test 4.3.4 { BEGIN ; COMMIT } {0 {}} 254 255#-------------------------------------------------------------------- 256# Test that if a DELETE change cannot be applied due to an 257# SQLITE_CONSTRAINT error thrown by a trigger program, things do not 258# go awry. 259 260drop_all_tables 261reset_db 262do_execsql_test 5.1 { 263 CREATE TABLE x1(x PRIMARY KEY, y); 264 CREATE TABLE x2(x PRIMARY KEY, y); 265 INSERT INTO x2 VALUES(1, 1); 266 INSERT INTO x1 VALUES(1, 1); 267} 268 269set ::cc [changeset_from_sql { DELETE FROM x1; }] 270 271do_execsql_test 5.2 { 272 INSERT INTO x1 VALUES(1, 1); 273 CREATE TRIGGER tr1 AFTER DELETE ON x1 BEGIN 274 INSERT INTO x2 VALUES(old.x, old.y); 275 END; 276} {} 277 278proc conflict_handler {args} { return "ABORT" } 279do_test 5.3 { 280 list [catch {sqlite3changeset_apply db $::cc conflict_handler} msg] $msg 281} {1 SQLITE_ABORT} 282 283do_execsql_test 5.4 { 284 SELECT * FROM X1; 285} {1 1} 286 287finish_test 288