# 2016 August 23
#
# 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 $testdir/tester.tcl
ifcapable !session {finish_test; return}
set testprefix changebatch1
proc sql_to_changeset {method sql} {
sqlite3session S db main
S attach *
execsql $sql
set ret [S $method]
S delete
return $ret
}
proc do_changebatch_test {tn method args} {
set C [list]
foreach a $args {
lappend C [sql_to_changeset $method $a]
}
sqlite3changebatch cb db
set i 1
foreach ::cs [lrange $C 0 end-1] {
set rc [cb add $::cs]
if {$rc!="SQLITE_OK"} { error "expected SQLITE_OK, got $rc (i=$i)" }
incr i
}
set ::cs [lindex $C end]
do_test $tn { cb add [set ::cs] } SQLITE_CONSTRAINT
cb delete
}
proc do_changebatch_test1 {tn args} {
uplevel do_changebatch_test $tn changeset $args
}
proc do_changebatch_test2 {tn args} {
uplevel do_changebatch_test $tn fullchangeset $args
}
#-------------------------------------------------------------------------
# The body of the following loop contains tests for database schemas
# that do not feature multi-column UNIQUE constraints. In this case
# it doesn't matter if the changesets are generated using
# sqlite3session_changeset() or sqlite3session_fullchangeset().
#
foreach {tn testfunction} {
1 do_changebatch_test1
2 do_changebatch_test2
} {
reset_db
#-------------------------------------------------------------------------
#
do_execsql_test $tn.1.0 {
CREATE TABLE t1(a PRIMARY KEY, b);
}
$testfunction $tn.1.1 {
INSERT INTO t1 VALUES(1, 1);
} {
DELETE FROM t1 WHERE a=1;
}
do_execsql_test $tn.1.2.0 {
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(3, 3);
}
$testfunction $tn.1.2.1 {
DELETE FROM t1 WHERE a=2;
} {
INSERT INTO t1 VALUES(2, 2);
}
#-------------------------------------------------------------------------
#
do_execsql_test $tn.2.0 {
CREATE TABLE x1(a, b PRIMARY KEY, c UNIQUE);
CREATE TABLE x2(a PRIMARY KEY, b UNIQUE, c UNIQUE);
CREATE INDEX x1a ON x1(a);
INSERT INTO x1 VALUES(1, 1, 'a');
INSERT INTO x1 VALUES(1, 2, 'b');
INSERT INTO x1 VALUES(1, 3, 'c');
}
$testfunction $tn.2.1 {
DELETE FROM x1 WHERE b=2;
} {
UPDATE x1 SET c='b' WHERE b=3;
}
$testfunction $tn.2.2 {
DELETE FROM x1 WHERE b=1;
} {
INSERT INTO x1 VALUES(1, 5, 'a');
}
set L [list]
for {set i 1000} {$i < 10000} {incr i} {
lappend L "INSERT INTO x2 VALUES($i, $i, 'x' || $i)"
}
lappend L "DELETE FROM x2 WHERE b=1005"
$testfunction $tn.2.3 {*}$L
execsql { INSERT INTO x1 VALUES('f', 'f', 'f') }
$testfunction $tn.2.4 {
INSERT INTO x2 VALUES('f', 'f', 'f');
} {
INSERT INTO x1 VALUES('g', 'g', 'g');
} {
DELETE FROM x1 WHERE b='f';
} {
INSERT INTO x2 VALUES('g', 'g', 'g');
} {
INSERT INTO x1 VALUES('f', 'f', 'f');
}
execsql {
DELETE FROM x1;
INSERT INTO x1 VALUES(1.5, 1.5, 1.5);
}
$testfunction $tn.2.5 {
DELETE FROM x1 WHERE b BETWEEN 1 AND 2;
} {
INSERT INTO x1 VALUES(2.5, 2.5, 2.5);
} {
INSERT INTO x1 VALUES(1.5, 1.5, 1.5);
}
execsql {
DELETE FROM x2;
INSERT INTO x2 VALUES(X'abcd', X'1234', X'7890');
INSERT INTO x2 VALUES(X'0000', X'0000', X'0000');
}
breakpoint
$testfunction $tn.2.6 {
UPDATE x2 SET c = X'1234' WHERE a=X'abcd';
INSERT INTO x2 VALUES(X'1234', X'abcd', X'7890');
} {
DELETE FROM x2 WHERE b=X'0000';
} {
INSERT INTO x2 VALUES(1, X'0000', NULL);
}
}
#-------------------------------------------------------------------------
# Test some multi-column UNIQUE constraints. First Using _changeset() to
# demonstrate the problem, then using _fullchangeset() to show that it has
# been fixed.
#
reset_db
do_execsql_test 3.0 {
CREATE TABLE y1(a PRIMARY KEY, b, c, UNIQUE(b, c));
INSERT INTO y1 VALUES(1, 1, 1);
INSERT INTO y1 VALUES(2, 2, 2);
INSERT INTO y1 VALUES(3, 3, 3);
INSERT INTO y1 VALUES(4, 3, 4);
BEGIN;
}
do_test 3.1.1 {
set c1 [sql_to_changeset changeset { DELETE FROM y1 WHERE a=4 }]
set c2 [sql_to_changeset changeset { UPDATE y1 SET c=4 WHERE a=3 }]
sqlite3changebatch cb db
cb add $c1
cb add $c2
} {SQLITE_OK}
do_test 3.1.2 {
cb delete
execsql ROLLBACK
} {}
do_test 3.1.1 {
set c1 [sql_to_changeset fullchangeset { DELETE FROM y1 WHERE a=4 }]
set c2 [sql_to_changeset fullchangeset { UPDATE y1 SET c=4 WHERE a=3 }]
sqlite3changebatch cb db
cb add $c1
cb add $c2
} {SQLITE_CONSTRAINT}
do_test 3.1.2 {
cb delete
} {}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 4.0 {
CREATE TABLE t1(x, y, z, PRIMARY KEY(x, y), UNIQUE(z));
}
do_test 4.1 {
set c1 [sql_to_changeset fullchangeset { INSERT INTO t1 VALUES(1, 2, 3) }]
execsql {
DROP TABLE t1;
CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y), UNIQUE(z));
}
sqlite3changebatch cb db
list [catch { cb add $c1 } msg] $msg
} {1 SQLITE_RANGE}
finish_test