# 2002 January 29## 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.## This file implements tests for the conflict resolution extension# to SQLite.## $Id: conflict.test,v 1.27 2006/01/17 09:35:02 danielk1977 Exp $set testdir [file dirname $argv0]source $testdir/tester.tclifcapable !conflict {  finish_test  return}# Create tables for the first group of tests.#do_test conflict-1.0 {  execsql {    CREATE TABLE t1(a, b, c, UNIQUE(a,b));    CREATE TABLE t2(x);    SELECT c FROM t1 ORDER BY c;  }} {}# Six columns of configuration data as follows:##   i      The reference number of the test#   cmd    An INSERT or REPLACE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "c" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t2#   t3     Number of temporary files created by this test#foreach {i cmd t0 t1 t2 t3} {  1 INSERT                  1 {}  1  0  2 {INSERT OR IGNORE}      0 3   1  0  3 {INSERT OR REPLACE}     0 4   1  0  4 REPLACE                 0 4   1  0  5 {INSERT OR FAIL}        1 {}  1  0  6 {INSERT OR ABORT}       1 {}  1  0  7 {INSERT OR ROLLBACK}    1 {}  {} 0} {  do_test conflict-1.$i {    set ::sqlite_opentemp_count 0    set r0 [catch {execsql [subst {      DELETE FROM t1;      DELETE FROM t2;      INSERT INTO t1 VALUES(1,2,3);      BEGIN;      INSERT INTO t2 VALUES(1);       $cmd INTO t1 VALUES(1,2,4);    }]} r1]    catch {execsql {COMMIT}}    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}    set r2 [execsql {SELECT x FROM t2}]    set r3 $::sqlite_opentemp_count    list $r0 $r1 $r2 $r3  } [list $t0 $t1 $t2 $t3]}# Create tables for the first group of tests.#do_test conflict-2.0 {  execsql {    DROP TABLE t1;    DROP TABLE t2;    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));    CREATE TABLE t2(x);    SELECT c FROM t1 ORDER BY c;  }} {}# Six columns of configuration data as follows:##   i      The reference number of the test#   cmd    An INSERT or REPLACE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "c" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t2#foreach {i cmd t0 t1 t2} {  1 INSERT                  1 {}  1  2 {INSERT OR IGNORE}      0 3   1  3 {INSERT OR REPLACE}     0 4   1  4 REPLACE                 0 4   1  5 {INSERT OR FAIL}        1 {}  1  6 {INSERT OR ABORT}       1 {}  1  7 {INSERT OR ROLLBACK}    1 {}  {}} {  do_test conflict-2.$i {    set r0 [catch {execsql [subst {      DELETE FROM t1;      DELETE FROM t2;      INSERT INTO t1 VALUES(1,2,3);      BEGIN;      INSERT INTO t2 VALUES(1);       $cmd INTO t1 VALUES(1,2,4);    }]} r1]    catch {execsql {COMMIT}}    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}    set r2 [execsql {SELECT x FROM t2}]    list $r0 $r1 $r2  } [list $t0 $t1 $t2]}# Create tables for the first group of tests.#do_test conflict-3.0 {  execsql {    DROP TABLE t1;    DROP TABLE t2;    CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b));    CREATE TABLE t2(x);    SELECT c FROM t1 ORDER BY c;  }} {}# Six columns of configuration data as follows:##   i      The reference number of the test#   cmd    An INSERT or REPLACE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "c" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t2#foreach {i cmd t0 t1 t2} {  1 INSERT                  1 {}  1  2 {INSERT OR IGNORE}      0 3   1  3 {INSERT OR REPLACE}     0 4   1  4 REPLACE                 0 4   1  5 {INSERT OR FAIL}        1 {}  1  6 {INSERT OR ABORT}       1 {}  1  7 {INSERT OR ROLLBACK}    1 {}  {}} {  do_test conflict-3.$i {    set r0 [catch {execsql [subst {      DELETE FROM t1;      DELETE FROM t2;      INSERT INTO t1 VALUES(1,2,3);      BEGIN;      INSERT INTO t2 VALUES(1);       $cmd INTO t1 VALUES(1,2,4);    }]} r1]    catch {execsql {COMMIT}}    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}    set r2 [execsql {SELECT x FROM t2}]    list $r0 $r1 $r2  } [list $t0 $t1 $t2]}do_test conflict-4.0 {  execsql {    DROP TABLE t2;    CREATE TABLE t2(x);    SELECT x FROM t2;  }} {}# Six columns of configuration data as follows:##   i      The reference number of the test#   conf1  The conflict resolution algorithm on the UNIQUE constraint#   cmd    An INSERT or REPLACE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "c" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t2#foreach {i conf1 cmd t0 t1 t2} {  1 {}       INSERT                  1 {}  1  2 REPLACE  INSERT                  0 4   1  3 IGNORE   INSERT                  0 3   1  4 FAIL     INSERT                  1 {}  1  5 ABORT    INSERT                  1 {}  1  6 ROLLBACK INSERT                  1 {}  {}  7 REPLACE  {INSERT OR IGNORE}      0 3   1  8 IGNORE   {INSERT OR REPLACE}     0 4   1  9 FAIL     {INSERT OR IGNORE}      0 3   1 10 ABORT    {INSERT OR REPLACE}     0 4   1 11 ROLLBACK {INSERT OR IGNORE }     0 3   1} {  do_test conflict-4.$i {    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}    set r0 [catch {execsql [subst {      DROP TABLE t1;      CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);      DELETE FROM t2;      INSERT INTO t1 VALUES(1,2,3);      BEGIN;      INSERT INTO t2 VALUES(1);       $cmd INTO t1 VALUES(1,2,4);    }]} r1]    catch {execsql {COMMIT}}    if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}    set r2 [execsql {SELECT x FROM t2}]    list $r0 $r1 $r2  } [list $t0 $t1 $t2]}do_test conflict-5.0 {  execsql {    DROP TABLE t2;    CREATE TABLE t2(x);    SELECT x FROM t2;  }} {}# Six columns of configuration data as follows:##   i      The reference number of the test#   conf1  The conflict resolution algorithm on the NOT NULL constraint#   cmd    An INSERT or REPLACE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "c" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t2#foreach {i conf1 cmd t0 t1 t2} {  1 {}       INSERT                  1 {}  1  2 REPLACE  INSERT                  0 5   1  3 IGNORE   INSERT                  0 {}  1  4 FAIL     INSERT                  1 {}  1  5 ABORT    INSERT                  1 {}  1  6 ROLLBACK INSERT                  1 {}  {}  7 REPLACE  {INSERT OR IGNORE}      0 {}  1  8 IGNORE   {INSERT OR REPLACE}     0 5   1  9 FAIL     {INSERT OR IGNORE}      0 {}  1 10 ABORT    {INSERT OR REPLACE}     0 5   1 11 ROLLBACK {INSERT OR IGNORE}      0 {}  1 12 {}       {INSERT OR IGNORE}      0 {}  1 13 {}       {INSERT OR REPLACE}     0 5   1 14 {}       {INSERT OR FAIL}        1 {}  1 15 {}       {INSERT OR ABORT}       1 {}  1 16 {}       {INSERT OR ROLLBACK}    1 {}  {}} {  if {$t0} {set t1 {t1.c may not be NULL}}  do_test conflict-5.$i {    if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}    set r0 [catch {execsql [subst {      DROP TABLE t1;      CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);      DELETE FROM t2;      BEGIN;      INSERT INTO t2 VALUES(1);       $cmd INTO t1 VALUES(1,2,NULL);    }]} r1]    catch {execsql {COMMIT}}    if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}    set r2 [execsql {SELECT x FROM t2}]    list $r0 $r1 $r2  } [list $t0 $t1 $t2]}do_test conflict-6.0 {  execsql {    DROP TABLE t2;    CREATE TABLE t2(a,b,c);    INSERT INTO t2 VALUES(1,2,1);    INSERT INTO t2 VALUES(2,3,2);    INSERT INTO t2 VALUES(3,4,1);    INSERT INTO t2 VALUES(4,5,4);    SELECT c FROM t2 ORDER BY b;    CREATE TABLE t3(x);    INSERT INTO t3 VALUES(1);  }} {1 2 1 4}# Six columns of configuration data as follows:##   i      The reference number of the test#   conf1  The conflict resolution algorithm on the UNIQUE constraint#   cmd    An UPDATE command to execute against table t1#   t0     True if there is an error from $cmd#   t1     Content of "b" column of t1 assuming no error in $cmd#   t2     Content of "x" column of t3#   t3     Number of temporary files created#foreach {i conf1 cmd t0 t1 t2 t3} {  1 {}       UPDATE                  1 {6 7 8 9}  1 1  2 REPLACE  UPDATE                  0 {7 6 9}    1 1  3 IGNORE   UPDATE                  0 {6 7 3 9}  1 1  4 FAIL     UPDATE                  1 {6 7 3 4}  1 0  5 ABORT    UPDATE                  1 {1 2 3 4}  1 1  6 ROLLBACK UPDATE                  1 {1 2 3 4}  0 0  7 REPLACE  {UPDATE OR IGNORE}      0 {6 7 3 9}  1 1  8 IGNORE   {UPDATE OR REPLACE}     0 {7 6 9}    1 1  9 FAIL     {UPDATE OR IGNORE}      0 {6 7 3 9}  1 1 10 ABORT    {UPDATE OR REPLACE}     0 {7 6 9}    1 1 11 ROLLBACK {UPDATE OR IGNORE}      0 {6 7 3 9}  1 1 12 {}       {UPDATE OR IGNORE}      0 {6 7 3 9}  1 1 13 {}       {UPDATE OR REPLACE}     0 {7 6 9}    1 1 14 {}       {UPDATE OR FAIL}        1 {6 7 3 4}  1 0 15 {}       {UPDATE OR ABORT}       1 {1 2 3 4}  1 1 16 {}       {UPDATE OR ROLLBACK}    1 {1 2 3 4}  0 0} {  if {$t0} {set t1 {column a is not unique}}  do_test conflict-6.$i {    db close    sqlite3 db test.db     if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}    execsql {pragma temp_store=file}    set ::sqlite_opentemp_count 0    set r0 [catch {execsql [subst {      DROP TABLE t1;      CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);      INSERT INTO t1 SELECT * FROM t2;      UPDATE t3 SET x=0;      BEGIN;      $cmd t3 SET x=1;      $cmd t1 SET b=b*2;      $cmd t1 SET a=c+5;    }]} r1]    catch {execsql {COMMIT}}    if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}    set r2 [execsql {SELECT x FROM t3}]    list $r0 $r1 $r2 $::sqlite_opentemp_count  } [list $t0 $t1 $t2 $t3]}# Test to make sure a lot of IGNOREs don't cause a stack overflow#do_test conflict-7.1 {  execsql {    DROP TABLE t1;    DROP TABLE t2;    DROP TABLE t3;    CREATE TABLE t1(a unique, b);  }  for {set i 1} {$i<=50} {incr i} {    execsql "INSERT into t1 values($i,[expr {$i+1}]);"  }  execsql {    SELECT count(*), min(a), max(b) FROM t1;  }} {50 1 51}do_test conflict-7.2 {  execsql {    PRAGMA count_changes=on;    UPDATE OR IGNORE t1 SET a=1000;  }} {1}do_test conflict-7.2.1 {  db changes} {1}do_test conflict-7.3 {  execsql {    SELECT b FROM t1 WHERE a=1000;  }} {2}do_test conflict-7.4 {  execsql {    SELECT count(*) FROM t1;  }} {50}do_test conflict-7.5 {  execsql {    PRAGMA count_changes=on;    UPDATE OR REPLACE t1 SET a=1001;  }} {50}do_test conflict-7.5.1 {  db changes} {50}do_test conflict-7.6 {  execsql {    SELECT b FROM t1 WHERE a=1001;  }} {51}do_test conflict-7.7 {