| # 2023 October 20 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements regression tests for SQLite library. |
| # |
| |
| if {![info exists testdir]} { |
| set testdir [file join [file dirname [info script]] .. .. test] |
| } |
| source [file join [file dirname [info script]] session_common.tcl] |
| source $testdir/tester.tcl |
| ifcapable !session {finish_test; return} |
| |
| set testprefix sessionnoact |
| |
| do_execsql_test 1.0 { |
| CREATE TABLE p1(a INTEGER PRIMARY KEY, b, c UNIQUE); |
| INSERT INTO p1 VALUES(1, 1, 'one'); |
| INSERT INTO p1 VALUES(2, 2, 'two'); |
| INSERT INTO p1 VALUES(3, 3, 'three'); |
| INSERT INTO p1 VALUES(4, 4, 'four'); |
| } |
| |
| db_save |
| |
| set C [changeset_from_sql { |
| DELETE FROM p1 WHERE a=2; |
| UPDATE p1 SET c='six' WHERE a=3; |
| INSERT INTO p1 VALUES(5, 5, 'two'); |
| INSERT INTO p1 VALUES(6, 6, 'three'); |
| }] |
| |
| db_restore_and_reopen |
| |
| do_execsql_test 1.1 { |
| CREATE TABLE c1(x INTEGER PRIMARY KEY, y, |
| FOREIGN KEY(y) REFERENCES p1(c) ON DELETE CASCADE ON UPDATE SET NULL |
| ); |
| |
| INSERT INTO c1 VALUES(10, 'one'); |
| INSERT INTO c1 VALUES(20, 'two'); |
| INSERT INTO c1 VALUES(30, 'three'); |
| INSERT INTO c1 VALUES(40, 'four'); |
| } |
| |
| db_save |
| |
| do_execsql_test 1.2 { |
| PRAGMA foreign_keys = 1; |
| } |
| |
| set ::nConflict 0 |
| proc conflict {args} { |
| incr ::nConflict |
| return "ABORT" |
| } |
| |
| sqlite3changeset_apply_v2 db $C conflict |
| |
| do_execsql_test 1.3 { |
| SELECT * FROM c1 |
| } { |
| 10 one |
| 30 {} |
| 40 four |
| } |
| |
| db_restore_and_reopen |
| |
| do_execsql_test 1.4 { |
| PRAGMA foreign_keys = 1; |
| } |
| |
| do_execsql_test 1.5 { |
| UPDATE p1 SET c=12345 WHERE a = 45; |
| } |
| |
| sqlite3changeset_apply_v2 -noaction db $C conflict |
| do_execsql_test 1.6 { |
| SELECT * FROM c1 |
| } { |
| 10 one |
| 20 two |
| 30 three |
| 40 four |
| } |
| |
| do_execsql_test 1.7 { |
| PRAGMA foreign_keys = 1; |
| UPDATE p1 SET c = 'ten' WHERE c='two'; |
| SELECT * FROM c1; |
| } { |
| 10 one |
| 20 {} |
| 30 three |
| 40 four |
| } |
| |
| do_execsql_test 1.8 { |
| PRAGMA foreign_key_check |
| } |
| |
| #------------------------------------------------------------------------- |
| # Check that a changeset that causes an FK violation may not be applied, |
| # even if SQLITE_CHANGESETAPPLY_FKNOACTION is specified. |
| # |
| # UPDATE: Unless the conflict-handler returns OMIT. In that case it can |
| # be committed. See test cases 3.* in this file. |
| # |
| reset_db |
| do_execsql_test 2.0 { |
| CREATE TABLE p1(a INTEGER PRIMARY KEY, b, c UNIQUE); |
| INSERT INTO p1 VALUES(1, 1, 'one'); |
| INSERT INTO p1 VALUES(2, 2, 'two'); |
| |
| CREATE TABLE c1(x REFERENCES p1(c) ON DELETE CASCADE); |
| INSERT INTO c1 VALUES('two'); |
| } |
| |
| db_save |
| |
| set C [changeset_from_sql { |
| DELETE FROM p1 WHERE a=2; |
| }] |
| |
| db_restore_and_reopen |
| |
| do_test 2.1 { |
| sqlite3changeset_apply_v2 -noaction db $C conflict |
| } {} |
| do_execsql_test 2.2 { |
| SELECT * FROM p1 |
| } {1 1 one} |
| |
| db_restore_and_reopen |
| db eval { PRAGMA foreign_keys = 1 } |
| |
| do_test 2.3 { |
| list [catch { sqlite3changeset_apply_v2 -noaction db $C conflict } msg] $msg |
| } {1 SQLITE_CONSTRAINT} |
| do_execsql_test 2.4 { |
| SELECT * FROM p1; |
| } {1 1 one 2 2 two} |
| do_execsql_test 2.5 { |
| SELECT * FROM c1; |
| } {two} |
| |
| db_restore_and_reopen |
| db eval { PRAGMA foreign_keys = 1 } |
| |
| do_test 2.6 { |
| list [catch { |
| sqlite3changeset_apply_v2 -ignorenoop -noaction db $C conflict |
| } msg] $msg |
| } {1 SQLITE_CONSTRAINT} |
| do_execsql_test 2.7 { |
| SELECT * FROM p1; |
| } {1 1 one 2 2 two} |
| do_execsql_test 2.8 { |
| SELECT * FROM c1; |
| } {two} |
| |
| #------------------------------------------------------------------------- |
| # |
| reset_db |
| do_execsql_test 3.0 { |
| CREATE TABLE p1(a INTEGER PRIMARY KEY, b, c UNIQUE); |
| INSERT INTO p1 VALUES(1, 1, 'one'); |
| INSERT INTO p1 VALUES(2, 2, 'two'); |
| |
| CREATE TABLE c1(x REFERENCES p1(c) ON DELETE CASCADE); |
| INSERT INTO c1 VALUES('two'); |
| } |
| |
| set ::nConflict 0 |
| proc conflict {args} { |
| incr ::nConflict |
| return "OMIT" |
| } |
| |
| db_save |
| |
| set C [changeset_from_sql { |
| DELETE FROM p1 WHERE a=2; |
| }] |
| |
| db_restore_and_reopen |
| |
| do_test 3.1 { |
| sqlite3changeset_apply_v2 -noaction db $C conflict |
| } {} |
| do_execsql_test 3.2 { |
| SELECT * FROM p1 |
| } {1 1 one} |
| |
| db_restore_and_reopen |
| db eval { PRAGMA foreign_keys = 1 } |
| |
| do_test 3.3 { |
| list [catch { sqlite3changeset_apply_v2 -noaction db $C conflict } msg] $msg |
| } {0 {}} |
| do_execsql_test 3.4 { |
| SELECT * FROM p1; |
| } {1 1 one} |
| do_execsql_test 3.5 { |
| SELECT * FROM c1; |
| } {two} |
| |
| db_restore_and_reopen |
| db eval { PRAGMA foreign_keys = 1 } |
| |
| do_test 3.6 { |
| list [catch { |
| sqlite3changeset_apply_v2 -ignorenoop -noaction db $C conflict |
| } msg] $msg |
| } {0 {}} |
| do_execsql_test 3.7 { |
| SELECT * FROM p1; |
| } {1 1 one} |
| do_execsql_test 3.8 { |
| SELECT * FROM c1; |
| } {two} |
| |
| finish_test |
| |