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

# 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.2 2002/01/30 04:32:01 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}}

do_test conflict-2.1 {
  execsql {
    DROP TABLE t1;
    DROP TABLE t2;
    CREATE TABLE t1(a integer primary key, b, c, UNIQUE(a,b));
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1bc ON t1(b,c);
    INSERT INTO t1 VALUES(1,2,3);
    SELECT c FROM t1 ORDER BY c;
  }
} {3}
do_test conflict-2.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-2.3 {
  catchsql {
    INSERT ON CONFLICT IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-2.4 {
  catchsql {
    INSERT ON CONFLICT REPLACE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-2.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-2.6 {
  catchsql {
    INSERT IGNORE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 4}
do_test conflict-2.7 {
  catchsql {
    INSERT REPLACE INTO t1 VALUES(1,2,5);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 5}
do_test conflict-2.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-2.9 {
  execsql {
    BEGIN;
    CREATE TABLE t2(a,b,c INTEGER PRIMARY KEY);
    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(2,2,21);
    INSERT INTO t2 VALUES(2,2,22);
    INSERT INTO t2 VALUES(2,2,23);
    INSERT INTO t2 VALUES(2,2,24);
    COMMIT;
    SELECT count(*) FROM t2;
  }
} 8
do_test conflict-2.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-2.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-2.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-2.13 {
  execsql {
    BEGIN;
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(2,3,4);
    INSERT INTO t1 VALUES(3,3,5);
    COMMIT;
    SELECT * FROM t1 ORDER BY c;
  }
} {1 2 3 2 3 4 3 3 5}
do_test conflict-2.14 {
  catchsql {
    UPDATE ON CONFLICT ABORT t1 SET a=2, b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-2.15 {
  catchsql {
    UPDATE t1 SET a=2, b=3 WHERE b=2;
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}};
do_test conflict-2.16 {
  catchsql {
    UPDATE ON CONFLICT IGNORE t1 SET a=2, b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {1 2 3 2 3 4 3 3 5}}
do_test conflict-2.17 {
  catchsql {
    UPDATE ON CONFLICT REPLACE t1 SET a=2, b=3 WHERE b=2;
    SELECT * FROM t1 ORDER BY c;
  }
} {0 {2 3 3 3 3 5}}


finish_test