mirror of
https://github.com/sqlite/sqlite.git
synced 2024-11-25 00:49:41 +01:00
640 lines
18 KiB
Plaintext
640 lines
18 KiB
Plaintext
# 2011 Mar 16
|
|
#
|
|
# 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.
|
|
#
|
|
#***********************************************************************
|
|
#
|
|
# The focus of this file is testing the session module.
|
|
#
|
|
|
|
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 session2
|
|
|
|
proc test_reset {} {
|
|
catch { db close }
|
|
catch { db2 close }
|
|
forcedelete test.db test.db2
|
|
sqlite3 db test.db
|
|
sqlite3 db2 test.db2
|
|
}
|
|
|
|
##########################################################################
|
|
# End of proc definitions. Start of tests.
|
|
##########################################################################
|
|
|
|
test_reset
|
|
do_execsql_test 1.0 {
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b);
|
|
INSERT INTO t1 VALUES('i', 'one');
|
|
}
|
|
do_iterator_test 1.1 t1 {
|
|
DELETE FROM t1 WHERE a = 'i';
|
|
INSERT INTO t1 VALUES('ii', 'two');
|
|
} {
|
|
{DELETE t1 0 X. {t i t one} {}}
|
|
{INSERT t1 0 X. {} {t ii t two}}
|
|
}
|
|
|
|
do_iterator_test 1.2 t1 {
|
|
INSERT INTO t1 VALUES(1.5, 99.9)
|
|
} {
|
|
{INSERT t1 0 X. {} {f 1.5 f 99.9}}
|
|
}
|
|
|
|
do_iterator_test 1.3 t1 {
|
|
UPDATE t1 SET b = 100.1 WHERE a = 1.5;
|
|
UPDATE t1 SET b = 99.9 WHERE a = 1.5;
|
|
} { }
|
|
|
|
do_iterator_test 1.4 t1 {
|
|
UPDATE t1 SET b = 100.1 WHERE a = 1.5;
|
|
} {
|
|
{UPDATE t1 0 X. {f 1.5 f 99.9} {{} {} f 100.1}}
|
|
}
|
|
|
|
|
|
# Execute each of the following blocks of SQL on database [db1]. Collect
|
|
# changes using a session object. Apply the resulting changeset to
|
|
# database [db2]. Then check that the contents of the two databases are
|
|
# identical.
|
|
#
|
|
|
|
set set_of_tests {
|
|
1 { INSERT INTO %T1% VALUES(1, 2) }
|
|
|
|
2 {
|
|
INSERT INTO %T2% VALUES(1, NULL);
|
|
INSERT INTO %T2% VALUES(2, NULL);
|
|
INSERT INTO %T2% VALUES(3, NULL);
|
|
DELETE FROM %T2% WHERE a = 2;
|
|
INSERT INTO %T2% VALUES(4, NULL);
|
|
UPDATE %T2% SET b=0 WHERE b=1;
|
|
}
|
|
|
|
3 { INSERT INTO %T3% SELECT *, NULL FROM %T2% }
|
|
|
|
4 {
|
|
INSERT INTO %T3% SELECT a||a, b||b, NULL FROM %T3%;
|
|
DELETE FROM %T3% WHERE rowid%2;
|
|
}
|
|
|
|
5 { UPDATE %T3% SET c = a||b }
|
|
|
|
6 { UPDATE %T1% SET a = 32 }
|
|
|
|
7 {
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
DELETE FROM %T1% WHERE (rowid%3)==0;
|
|
}
|
|
|
|
8 {
|
|
BEGIN;
|
|
INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%;
|
|
ROLLBACK;
|
|
}
|
|
9 {
|
|
BEGIN;
|
|
UPDATE %T1% SET b = 'xxx';
|
|
ROLLBACK;
|
|
}
|
|
10 {
|
|
BEGIN;
|
|
DELETE FROM %T1% WHERE 1;
|
|
ROLLBACK;
|
|
}
|
|
11 {
|
|
INSERT INTO %T1% VALUES(randomblob(21000), randomblob(0));
|
|
INSERT INTO %T1% VALUES(1.5, 1.5);
|
|
INSERT INTO %T1% VALUES(4.56, -99.999999999999999999999);
|
|
}
|
|
12 {
|
|
INSERT INTO %T2% VALUES(NULL, NULL);
|
|
}
|
|
|
|
13 {
|
|
DELETE FROM %T1% WHERE 1;
|
|
|
|
-- Insert many rows with real primary keys. Enough to force the session
|
|
-- objects hash table to resize.
|
|
INSERT INTO %T1% VALUES(0.1, 0.1);
|
|
INSERT INTO %T1% SELECT a+0.1, b+0.1 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+0.2, b+0.2 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+0.4, b+0.4 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+0.8, b+0.8 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+1.6, b+1.6 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+3.2, b+3.2 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+6.4, b+6.4 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+12.8, b+12.8 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+25.6, b+25.6 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+51.2, b+51.2 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+102.4, b+102.4 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+204.8, b+204.8 FROM %T1%;
|
|
}
|
|
|
|
14 {
|
|
DELETE FROM %T1% WHERE 1;
|
|
}
|
|
|
|
15 {
|
|
INSERT INTO %T1% VALUES(1, 1);
|
|
INSERT INTO %T1% SELECT a+2, b+2 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+4, b+4 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+8, b+8 FROM %T1%;
|
|
INSERT INTO %T1% SELECT a+256, b+256 FROM %T1%;
|
|
}
|
|
|
|
16 {
|
|
INSERT INTO %T4% VALUES('abc', 'def');
|
|
INSERT INTO %T4% VALUES('def', 'abc');
|
|
}
|
|
17 { UPDATE %T4% SET b = 1 }
|
|
|
|
18 { DELETE FROM %T4% WHERE 1 }
|
|
|
|
19 {
|
|
INSERT INTO t1 VALUES('', '');
|
|
INSERT INTO t1 VALUES(X'', X'');
|
|
}
|
|
20 {
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 VALUES('', NULL);
|
|
}
|
|
}
|
|
|
|
test_reset
|
|
do_common_sql {
|
|
CREATE TABLE t1(a int PRIMARY KEY, b);
|
|
CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
|
|
CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
|
|
CREATE TABLE t4(a, b, PRIMARY KEY(b, a));
|
|
}
|
|
|
|
foreach {tn sql} [string map {%T1% t1 %T2% t2 %T3% t3 %T4% t4} $set_of_tests] {
|
|
do_then_apply_sql -ignorenoop $sql
|
|
do_test 2.$tn { compare_db db db2 } {}
|
|
}
|
|
|
|
# The following block of tests is similar to the last, except that the
|
|
# session object is recording changes made to an attached database. The
|
|
# main database contains a table of the same name as the table being
|
|
# modified within the attached db.
|
|
#
|
|
test_reset
|
|
forcedelete test.db3
|
|
sqlite3 db3 test.db3
|
|
do_test 3.0 {
|
|
execsql {
|
|
ATTACH 'test.db3' AS 'aux';
|
|
CREATE TABLE t1(a int, b PRIMARY KEY);
|
|
CREATE TABLE t2(x, y, z);
|
|
CREATE TABLE t3(a);
|
|
|
|
CREATE TABLE aux.t1(a int PRIMARY KEY, b);
|
|
CREATE TABLE aux.t2(a, b INTEGER PRIMARY KEY);
|
|
CREATE TABLE aux.t3(a, b, c, PRIMARY KEY(a, b));
|
|
CREATE TABLE aux.t4(a, b, PRIMARY KEY(b, a));
|
|
}
|
|
execsql {
|
|
CREATE TABLE t1(a int PRIMARY KEY, b);
|
|
CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
|
|
CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
|
|
CREATE TABLE t4(a, b, PRIMARY KEY(b, a));
|
|
} db2
|
|
} {}
|
|
|
|
proc xTrace {args} { puts $args }
|
|
|
|
foreach {tn sql} [
|
|
string map {%T1% aux.t1 %T2% aux.t2 %T3% aux.t3 %T4% aux.t4} $set_of_tests
|
|
] {
|
|
do_then_apply_sql $sql aux
|
|
do_test 3.$tn { compare_db db2 db3 } {}
|
|
}
|
|
catch {db3 close}
|
|
|
|
|
|
#-------------------------------------------------------------------------
|
|
# The following tests verify that NULL values in primary key columns are
|
|
# handled correctly by the session module.
|
|
#
|
|
test_reset
|
|
do_execsql_test 4.0 {
|
|
CREATE TABLE t1(a PRIMARY KEY);
|
|
CREATE TABLE t2(a, b, c, PRIMARY KEY(c, b));
|
|
CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
|
|
}
|
|
|
|
foreach {tn sql changeset} {
|
|
1 {
|
|
INSERT INTO t1 VALUES(123);
|
|
INSERT INTO t1 VALUES(NULL);
|
|
INSERT INTO t1 VALUES(456);
|
|
} {
|
|
{INSERT t1 0 X {} {i 456}}
|
|
{INSERT t1 0 X {} {i 123}}
|
|
}
|
|
|
|
2 {
|
|
UPDATE t1 SET a = NULL;
|
|
} {
|
|
{DELETE t1 0 X {i 456} {}}
|
|
{DELETE t1 0 X {i 123} {}}
|
|
}
|
|
|
|
3 { DELETE FROM t1 } { }
|
|
|
|
4 {
|
|
INSERT INTO t3 VALUES(NULL, NULL)
|
|
} {
|
|
{INSERT t3 0 .X {} {n {} i 1}}
|
|
}
|
|
|
|
5 { INSERT INTO t2 VALUES(1, 2, NULL) } { }
|
|
6 { INSERT INTO t2 VALUES(1, NULL, 3) } { }
|
|
7 { INSERT INTO t2 VALUES(1, NULL, NULL) } { }
|
|
8 { INSERT INTO t2 VALUES(1, 2, 3) } { {INSERT t2 0 .XX {} {i 1 i 2 i 3}} }
|
|
9 { DELETE FROM t2 WHERE 1 } { {DELETE t2 0 .XX {i 1 i 2 i 3} {}} }
|
|
|
|
} {
|
|
do_iterator_test 4.$tn {t1 t2 t3} $sql $changeset
|
|
}
|
|
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that if NULL is passed to sqlite3session_attach(), all database
|
|
# tables are attached to the session object.
|
|
#
|
|
test_reset
|
|
do_execsql_test 5.0 {
|
|
CREATE TABLE t1(a PRIMARY KEY);
|
|
CREATE TABLE t2(x, y PRIMARY KEY);
|
|
}
|
|
|
|
foreach {tn sql changeset} {
|
|
1 { INSERT INTO t1 VALUES(35) } { {INSERT t1 0 X {} {i 35}} }
|
|
2 { INSERT INTO t2 VALUES(36, 37) } { {INSERT t2 0 .X {} {i 36 i 37}} }
|
|
3 {
|
|
DELETE FROM t1 WHERE 1;
|
|
UPDATE t2 SET x = 34;
|
|
} {
|
|
{DELETE t1 0 X {i 35} {}}
|
|
{UPDATE t2 0 .X {i 36 i 37} {i 34 {} {}}}
|
|
}
|
|
} {
|
|
do_iterator_test 5.$tn * $sql $changeset
|
|
}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# The next block of tests verify that the "indirect" flag is set
|
|
# correctly within changesets. The indirect flag is set for a change
|
|
# if either of the following are true:
|
|
#
|
|
# * The sqlite3session_indirect() API has been used to set the session
|
|
# indirect flag to true, or
|
|
# * The change was made by a trigger.
|
|
#
|
|
# If the same row is updated more than once during a session, then the
|
|
# change is considered indirect only if all changes meet the criteria
|
|
# above.
|
|
#
|
|
test_reset
|
|
db function indirect [list S indirect]
|
|
|
|
do_execsql_test 6.0 {
|
|
CREATE TABLE t1(a PRIMARY KEY, b, c);
|
|
|
|
CREATE TABLE t2(x PRIMARY KEY, y);
|
|
CREATE TRIGGER AFTER INSERT ON t2 WHEN new.x%2 BEGIN
|
|
INSERT INTO t2 VALUES(new.x+1, NULL);
|
|
END;
|
|
}
|
|
|
|
do_iterator_test 6.1.1 * {
|
|
INSERT INTO t1 VALUES(1, 'one', 'i');
|
|
SELECT indirect(1);
|
|
INSERT INTO t1 VALUES(2, 'two', 'ii');
|
|
SELECT indirect(0);
|
|
INSERT INTO t1 VALUES(3, 'three', 'iii');
|
|
} {
|
|
{INSERT t1 0 X.. {} {i 1 t one t i}}
|
|
{INSERT t1 1 X.. {} {i 2 t two t ii}}
|
|
{INSERT t1 0 X.. {} {i 3 t three t iii}}
|
|
}
|
|
|
|
do_iterator_test 6.1.2 * {
|
|
SELECT indirect(1);
|
|
UPDATE t1 SET c = 'I' WHERE a = 1;
|
|
SELECT indirect(0);
|
|
} {
|
|
{UPDATE t1 1 X.. {i 1 {} {} t i} {{} {} {} {} t I}}
|
|
}
|
|
do_iterator_test 6.1.3 * {
|
|
SELECT indirect(1);
|
|
UPDATE t1 SET c = '.' WHERE a = 1;
|
|
SELECT indirect(0);
|
|
UPDATE t1 SET c = 'o' WHERE a = 1;
|
|
} {
|
|
{UPDATE t1 0 X.. {i 1 {} {} t I} {{} {} {} {} t o}}
|
|
}
|
|
do_iterator_test 6.1.4 * {
|
|
SELECT indirect(0);
|
|
UPDATE t1 SET c = 'x' WHERE a = 1;
|
|
SELECT indirect(1);
|
|
UPDATE t1 SET c = 'i' WHERE a = 1;
|
|
} {
|
|
{UPDATE t1 0 X.. {i 1 {} {} t o} {{} {} {} {} t i}}
|
|
}
|
|
do_iterator_test 6.1.4 * {
|
|
SELECT indirect(1);
|
|
UPDATE t1 SET c = 'y' WHERE a = 1;
|
|
SELECT indirect(1);
|
|
UPDATE t1 SET c = 'I' WHERE a = 1;
|
|
} {
|
|
{UPDATE t1 1 X.. {i 1 {} {} t i} {{} {} {} {} t I}}
|
|
}
|
|
|
|
do_iterator_test 6.1.5 * {
|
|
INSERT INTO t2 VALUES(1, 'x');
|
|
} {
|
|
{INSERT t2 0 X. {} {i 1 t x}}
|
|
{INSERT t2 1 X. {} {i 2 n {}}}
|
|
}
|
|
|
|
do_iterator_test 6.1.6 * {
|
|
SELECT indirect(1);
|
|
INSERT INTO t2 VALUES(3, 'x');
|
|
SELECT indirect(0);
|
|
UPDATE t2 SET y = 'y' WHERE x>2;
|
|
} {
|
|
{INSERT t2 0 X. {} {i 3 t y}}
|
|
{INSERT t2 0 X. {} {i 4 t y}}
|
|
}
|
|
|
|
do_iterator_test 6.1.7 * {
|
|
SELECT indirect(1);
|
|
DELETE FROM t2 WHERE x = 4;
|
|
SELECT indirect(0);
|
|
INSERT INTO t2 VALUES(4, 'new');
|
|
} {
|
|
{UPDATE t2 0 X. {i 4 t y} {{} {} t new}}
|
|
}
|
|
|
|
do_iterator_test 6.1.8 * {
|
|
CREATE TABLE t3(a, b PRIMARY KEY);
|
|
CREATE TABLE t4(a, b PRIMARY KEY);
|
|
CREATE TRIGGER t4t AFTER UPDATE ON t4 BEGIN
|
|
UPDATE t3 SET a = new.a WHERE b = new.b;
|
|
END;
|
|
|
|
SELECT indirect(1);
|
|
INSERT INTO t3 VALUES('one', 1);
|
|
INSERT INTO t4 VALUES('one', 1);
|
|
SELECT indirect(0);
|
|
UPDATE t4 SET a = 'two' WHERE b = 1;
|
|
} {
|
|
{INSERT t3 1 .X {} {t two i 1}}
|
|
{INSERT t4 0 .X {} {t two i 1}}
|
|
}
|
|
|
|
sqlite3session S db main
|
|
do_execsql_test 6.2.1 {
|
|
SELECT indirect(0);
|
|
SELECT indirect(-1);
|
|
SELECT indirect(45);
|
|
SELECT indirect(-100);
|
|
} {0 0 1 1}
|
|
S delete
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that if a conflict-handler that has been passed either NOTFOUND or
|
|
# CONSTRAINT returns REPLACE - the sqlite3changeset_apply() call returns
|
|
# MISUSE and rolls back any changes made so far.
|
|
#
|
|
# 7.1.*: NOTFOUND conflict-callback.
|
|
# 7.2.*: CONSTRAINT conflict-callback.
|
|
#
|
|
proc xConflict {args} {return REPLACE}
|
|
test_reset
|
|
|
|
do_execsql_test 7.1.1 {
|
|
CREATE TABLE t1(a PRIMARY KEY, b);
|
|
INSERT INTO t1 VALUES(1, 'one');
|
|
INSERT INTO t1 VALUES(2, 'two');
|
|
}
|
|
do_test 7.1.2 {
|
|
execsql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b NOT NULL);
|
|
INSERT INTO t1 VALUES(1, 'one');
|
|
} db2
|
|
} {}
|
|
do_test 7.1.3 {
|
|
set changeset [changeset_from_sql {
|
|
UPDATE t1 SET b = 'five' WHERE a = 1;
|
|
UPDATE t1 SET b = 'six' WHERE a = 2;
|
|
}]
|
|
set x [list]
|
|
sqlite3session_foreach c $changeset { lappend x $c }
|
|
set x
|
|
} [list \
|
|
{UPDATE t1 0 X. {i 1 t one} {{} {} t five}} \
|
|
{UPDATE t1 0 X. {i 2 t two} {{} {} t six}} \
|
|
]
|
|
do_test 7.1.4 {
|
|
list [catch {sqlite3changeset_apply db2 $changeset xConflict} msg] $msg
|
|
} {1 SQLITE_MISUSE}
|
|
do_test 7.1.5 { execsql { SELECT * FROM t1 } db2 } {1 one}
|
|
|
|
do_test 7.2.1 {
|
|
set changeset [changeset_from_sql { UPDATE t1 SET b = NULL WHERE a = 1 }]
|
|
|
|
set x [list]
|
|
sqlite3session_foreach c $changeset { lappend x $c }
|
|
set x
|
|
} [list \
|
|
{UPDATE t1 0 X. {i 1 t five} {{} {} n {}}} \
|
|
]
|
|
do_test 7.2.2 {
|
|
list [catch {sqlite3changeset_apply db2 $changeset xConflict} msg] $msg
|
|
} {1 SQLITE_MISUSE}
|
|
do_test 7.2.3 { execsql { SELECT * FROM t1 } db2 } {1 one}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that if a conflict-handler returns ABORT, application of the
|
|
# changeset is rolled back and the sqlite3changeset_apply() method returns
|
|
# SQLITE_ABORT.
|
|
#
|
|
# Also test that the same thing happens if a conflict handler returns an
|
|
# unrecognized integer value. Except, in this case SQLITE_MISUSE is returned
|
|
# instead of SQLITE_ABORT.
|
|
#
|
|
foreach {tn conflict_return apply_return} {
|
|
1 ABORT SQLITE_ABORT
|
|
2 567 SQLITE_MISUSE
|
|
} {
|
|
test_reset
|
|
proc xConflict {args} [list return $conflict_return]
|
|
|
|
do_test 8.$tn.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
|
|
INSERT INTO t1 VALUES('x', 'y');
|
|
}
|
|
execsql { INSERT INTO t1 VALUES('w', 'w') }
|
|
|
|
set changeset [changeset_from_sql { DELETE FROM t1 WHERE 1 }]
|
|
|
|
set x [list]
|
|
sqlite3session_foreach c $changeset { lappend x $c }
|
|
set x
|
|
} [list \
|
|
{DELETE t1 0 XX {t w t w} {}} \
|
|
{DELETE t1 0 XX {t x t y} {}} \
|
|
]
|
|
|
|
do_test 8.$tn.1 {
|
|
list [catch {sqlite3changeset_apply db2 $changeset xConflict} msg] $msg
|
|
} [list 1 $apply_return]
|
|
|
|
do_test 8.$tn.2 {
|
|
execsql {SELECT * FROM t1} db2
|
|
} {x y}
|
|
}
|
|
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Try to cause an infinite loop as follows:
|
|
#
|
|
# 1. Have a changeset insert a row that causes a CONFLICT callback,
|
|
# 2. Have the conflict handler return REPLACE,
|
|
# 3. After the session module deletes the conflicting row, have a trigger
|
|
# re-insert it.
|
|
# 4. Goto step 1...
|
|
#
|
|
# This doesn't work, as the second invocation of the conflict handler is a
|
|
# CONSTRAINT, not a CONFLICT. There is at most one CONFLICT callback for
|
|
# each change in the changeset.
|
|
#
|
|
test_reset
|
|
proc xConflict {type args} {
|
|
if {$type == "CONFLICT"} { return REPLACE }
|
|
return OMIT
|
|
}
|
|
do_test 9.1 {
|
|
execsql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b);
|
|
}
|
|
execsql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b);
|
|
INSERT INTO t1 VALUES('x', 2);
|
|
CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
|
|
INSERT INTO t1 VALUES(old.a, old.b);
|
|
END;
|
|
} db2
|
|
} {}
|
|
do_test 9.2 {
|
|
set changeset [changeset_from_sql { INSERT INTO t1 VALUES('x', 1) }]
|
|
sqlite3changeset_apply db2 $changeset xConflict
|
|
} {}
|
|
do_test 9.3 {
|
|
execsql { SELECT * FROM t1 } db2
|
|
} {x 2}
|
|
|
|
#-------------------------------------------------------------------------
|
|
#
|
|
test_reset
|
|
db function enable [list S enable]
|
|
|
|
do_common_sql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b);
|
|
INSERT INTO t1 VALUES('x', 'X');
|
|
}
|
|
|
|
do_iterator_test 10.1 t1 {
|
|
INSERT INTO t1 VALUES('y', 'Y');
|
|
SELECT enable(0);
|
|
INSERT INTO t1 VALUES('z', 'Z');
|
|
SELECT enable(1);
|
|
} {
|
|
{INSERT t1 0 X. {} {t y t Y}}
|
|
}
|
|
|
|
sqlite3session S db main
|
|
do_execsql_test 10.2 {
|
|
SELECT enable(0);
|
|
SELECT enable(-1);
|
|
SELECT enable(1);
|
|
SELECT enable(-1);
|
|
} {0 0 1 1}
|
|
S delete
|
|
|
|
#-------------------------------------------------------------------------
|
|
test_reset
|
|
do_common_sql {
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d, e, f);
|
|
WITH s(i) AS (
|
|
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<32
|
|
)
|
|
INSERT INTO t1 SELECT NULL, 0, 0, 0, 0, 0 FROM s
|
|
}
|
|
|
|
do_then_apply_sql -ignorenoop {
|
|
UPDATE t1 SET f=f+1 WHERE a=1;
|
|
UPDATE t1 SET e=e+1 WHERE a=2;
|
|
UPDATE t1 SET e=e+1, f=f+1 WHERE a=3;
|
|
UPDATE t1 SET d=d+1 WHERE a=4;
|
|
UPDATE t1 SET d=d+1, f=f+1 WHERE a=5;
|
|
UPDATE t1 SET d=d+1, e=e+1 WHERE a=6;
|
|
UPDATE t1 SET d=d+1, e=e+1, f=f+1 WHERE a=7;
|
|
UPDATE t1 SET c=c+1 WHERE a=8;
|
|
UPDATE t1 SET c=c+1, f=f+1 WHERE a=9;
|
|
UPDATE t1 SET c=c+1, e=e+1 WHERE a=10;
|
|
UPDATE t1 SET c=c+1, e=e+1, f=f+1 WHERE a=11;
|
|
UPDATE t1 SET c=c+1, d=d+1 WHERE a=12;
|
|
UPDATE t1 SET c=c+1, d=d+1, f=f+1 WHERE a=13;
|
|
UPDATE t1 SET c=c+1, d=d+1, e=e+1 WHERE a=14;
|
|
UPDATE t1 SET c=c+1, d=d+1, e=e+1, f=f+1 WHERE a=15;
|
|
UPDATE t1 SET d=d+1 WHERE a=16;
|
|
UPDATE t1 SET d=d+1, f=f+1 WHERE a=17;
|
|
UPDATE t1 SET d=d+1, e=e+1 WHERE a=18;
|
|
UPDATE t1 SET d=d+1, e=e+1, f=f+1 WHERE a=19;
|
|
UPDATE t1 SET d=d+1, d=d+1 WHERE a=20;
|
|
UPDATE t1 SET d=d+1, d=d+1, f=f+1 WHERE a=21;
|
|
UPDATE t1 SET d=d+1, d=d+1, e=e+1 WHERE a=22;
|
|
UPDATE t1 SET d=d+1, d=d+1, e=e+1, f=f+1 WHERE a=23;
|
|
UPDATE t1 SET d=d+1, c=c+1 WHERE a=24;
|
|
UPDATE t1 SET d=d+1, c=c+1, f=f+1 WHERE a=25;
|
|
UPDATE t1 SET d=d+1, c=c+1, e=e+1 WHERE a=26;
|
|
UPDATE t1 SET d=d+1, c=c+1, e=e+1, f=f+1 WHERE a=27;
|
|
UPDATE t1 SET d=d+1, c=c+1, d=d+1 WHERE a=28;
|
|
UPDATE t1 SET d=d+1, c=c+1, d=d+1, f=f+1 WHERE a=29;
|
|
UPDATE t1 SET d=d+1, c=c+1, d=d+1, e=e+1 WHERE a=30;
|
|
UPDATE t1 SET d=d+1, c=c+1, d=d+1, e=e+1, f=f+1 WHERE a=31;
|
|
}
|
|
|
|
do_test 11.0 {
|
|
compare_db db db2
|
|
} {}
|
|
|
|
finish_test
|