xref: /sqlite-3.40.0/ext/session/session9.test (revision cb3e4b79) 
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#
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
133finish_test
134
135