xref: /sqlite-3.40.0/test/conflict.test (revision bb37fdc7)

# 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.1 2002/01/30 00:54:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with three fields, two of which must be
# UNIQUE.
#
do_test conflict-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c, UNIQUE(a,b));
    INSERT INTO t1 VALUES(1,2,3);
    SELECT c FROM t1 ORDER BY c;
  }
} {3}
do_test conflict-1.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.3 {
  catchsql {
    INSERT ON CONFLICT IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-1.4 {
  catchsql {
    INSERT ON CONFLICT REPLACE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.5 {
  catchsql {
    INSERT ON CONFLICT ABORT INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-1.6 {
  catchsql {
    INSERT IGNORE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-1.7 {
  catchsql {
    INSERT REPLACE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 5}
do_test conflict-1.8 {
  catchsql {
    INSERT ON CONFLICT ABORT INTO t1 VALUES(1,2,6);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}

do_test conflict-1.9 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(1,2,11);
    INSERT INTO t2 VALUES(1,2,12);
    INSERT INTO t2 VALUES(1,2,13);
    INSERT INTO t2 VALUES(1,2,14);
    INSERT INTO t2 VALUES(1,3,21);
    INSERT INTO t2 VALUES(1,3,22);
    INSERT INTO t2 VALUES(1,3,23);
    INSERT INTO t2 VALUES(1,3,24);
    COMMIT;
    SELECT count(*) FROM t2;
  }
} 8
do_test conflict-1.10 {
  catchsql {
    INSERT IGNORE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {5 21}}
do_test conflict-1.11 {
  catchsql {
    INSERT REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

###### Fix me!
do_test conflict-1.12 {
  catchsql {
    INSERT REPLACE INTO t1 SELECT a,b,c FROM t2 ORDER BY c DESC;
    SELECT c FROM t1 ORDER BY c;
  }
} {0 {14 24}}

do_test conflict-1.13 {
  execsql {
    BEGIN;
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(1,3,4);
    INSERT INTO t1 VALUES(2,3,5);
    COMMIT;
    SELECT * FROM t1 ORDER BY c;
  }
} {1 2 3 1 3 4 2 3 5}
do_test conflict-1.14 {
  catchsql {
    UPDATE ON CONFLICT ABORT t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.15 {
  catchsql {
    UPDATE t1 SET b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-1.16 {
  catchsql {
    UPDATE ON CONFLICT IGNORE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 2 3 1 3 4 2 3 5}}
do_test conflict-1.17 {
  catchsql {
    UPDATE ON CONFLICT REPLACE t1 SET b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 3 3 2 3 5}}


finish_test