package require sqlite3

proc do_test {name cmd expected} {

  puts -nonewline "$name ..."

  set res [uplevel $cmd]

  if {$res eq $expected} {

    puts Ok

  } else {

    puts Error

    puts "  Got: $res"

    puts "  Expected: $expected"

    exit

  }

}

proc execsql {sql} {

  uplevel [list db eval $sql]

}

proc catchsql {sql} {

  set rc [catch {uplevel [list db eval $sql]} msg]

  list $rc $msg

}

file delete -force test.db test.db.journal

sqlite3 db test.db

# The following tests - genfkey-1.* - test RESTRICT foreign keys.

#

do_test genfkey-1.1 {

  execsql {

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));

    CREATE TABLE t2(e REFERENCES t1, f);

    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));

  }

} {}

do_test genfkey-1.2 {

  execsql [exec ./sqlite3 test.db .genfkey]

} {}

do_test genfkey-1.3 {

  catchsql { INSERT INTO t2 VALUES(1, 2) }

} {1 {constraint failed}}

do_test genfkey-1.4 {

  execsql {

    INSERT INTO t1 VALUES(1, 2, 3);

    INSERT INTO t2 VALUES(1, 2);

  }

} {}

do_test genfkey-1.5 {

  execsql { INSERT INTO t2 VALUES(NULL, 3) }

} {}

do_test genfkey-1.6 {

  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }

} {1 {constraint failed}}

do_test genfkey-1.7 {

  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }

} {}

do_test genfkey-1.8 {

  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }

} {}

do_test genfkey-1.9 {

  catchsql { UPDATE t1 SET a = 10 }

} {1 {constraint failed}}

do_test genfkey-1.9a {

  catchsql { UPDATE t1 SET a = NULL }

} {1 {datatype mismatch}}

do_test genfkey-1.10 {

  catchsql { DELETE FROM t1 }

} {1 {constraint failed}}

do_test genfkey-1.11 {

  execsql { UPDATE t2 SET e = NULL }

} {}

do_test genfkey-1.12 {

  execsql { 

    UPDATE t1 SET a = 10 ;

    DELETE FROM t1;

    DELETE FROM t2;

  }

} {}

do_test genfkey-1.13 {

  execsql {

    INSERT INTO t3 VALUES(1, NULL, NULL);

    INSERT INTO t3 VALUES(1, 2, NULL);

    INSERT INTO t3 VALUES(1, NULL, 3);

  }

} {}

do_test genfkey-1.14 {

  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }

} {1 {constraint failed}}

do_test genfkey-1.15 {

  execsql { 

    INSERT INTO t1 VALUES(1, 1, 4);

    INSERT INTO t3 VALUES(3, 1, 4);

  }

} {}

do_test genfkey-1.16 {

  catchsql { DELETE FROM t1 }

} {1 {constraint failed}}

do_test genfkey-1.17 {

  catchsql { UPDATE t1 SET b = 10}

} {1 {constraint failed}}

do_test genfkey-1.18 {

  execsql { UPDATE t1 SET a = 10}

} {}

do_test genfkey-1.19 {

  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}

} {1 {constraint failed}}

do_test genfkey-1.X {

  execsql {

    DROP TABLE t1;

    DROP TABLE t2;

    DROP TABLE t3;

  }

} {}

# The following tests - genfkey-2.* - test CASCADE foreign keys.

#

do_test genfkey-2.1 {

  execsql {

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));

    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);

    CREATE TABLE t3(g, h, i, 

        FOREIGN KEY (h, i) 

        REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE

    );

  }

} {}

do_test genfkey-2.2 {

  execsql [exec ./sqlite3 test.db .genfkey]

} {}

do_test genfkey-2.3 {

  execsql {

    INSERT INTO t1 VALUES(1, 2, 3);

    INSERT INTO t1 VALUES(4, 5, 6);

    INSERT INTO t2 VALUES(1, 'one');

    INSERT INTO t2 VALUES(4, 'four');

  }

} {}

do_test genfkey-2.4 {

  execsql {

    UPDATE t1 SET a = 2 WHERE a = 1;

    SELECT * FROM t2;

  }

} {2 one 4 four}

do_test genfkey-2.5 {

  execsql {

    DELETE FROM t1 WHERE a = 4;

    SELECT * FROM t2;

  }

} {2 one}

do_test genfkey-2.6 {

  execsql {

    INSERT INTO t3 VALUES('hello', 2, 3);

    UPDATE t1 SET c = 2;

    SELECT * FROM t3;

  }

} {hello 2 2}

do_test genfkey-2.7 {

  execsql {

    DELETE FROM t1;

    SELECT * FROM t3;

  }

} {}

do_test genfkey-2.X {

  execsql {

    DROP TABLE t1;

    DROP TABLE t2;

    DROP TABLE t3;

  }

} {}

# The following tests - genfkey-3.* - test SET NULL foreign keys.

#

do_test genfkey-3.1 {

  execsql {

    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));

    CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);

    CREATE TABLE t3(g, h, i, 

        FOREIGN KEY (h, i) 

        REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL

    );

  }

} {}

do_test genfkey-3.2 {

  execsql [exec ./sqlite3 test.db .genfkey]

} {}

do_test genfkey-3.3 {

  execsql {

    INSERT INTO t1 VALUES(1, 2, 3);

    INSERT INTO t1 VALUES(4, 5, 6);

    INSERT INTO t2 VALUES(1, 'one');

    INSERT INTO t2 VALUES(4, 'four');

  }

} {}

do_test genfkey-3.4 {

  execsql {

    UPDATE t1 SET a = 2 WHERE a = 1;

    SELECT * FROM t2;

  }

} {{} one 4 four}

do_test genfkey-3.5 {

  execsql {

    DELETE FROM t1 WHERE a = 4;

    SELECT * FROM t2;

  }

} {{} one {} four}

do_test genfkey-3.6 {

  execsql {

    INSERT INTO t3 VALUES('hello', 2, 3);

    UPDATE t1 SET c = 2;

    SELECT * FROM t3;

  }

} {hello {} {}}

do_test genfkey-2.7 {

  execsql {

    UPDATE t3 SET h = 2, i = 2;

    DELETE FROM t1;

    SELECT * FROM t3;

  }

} {hello {} {}}

do_test genfkey-3.X {

  execsql {

    DROP TABLE t1;

    DROP TABLE t2;

    DROP TABLE t3;

  }

} {}

# The following tests - genfkey-4.* - test that errors in the schema

# are detected correctly.

#

do_test genfkey-4.1 {

  execsql {

    CREATE TABLE t1(a REFERENCES nosuchtable, b);

    CREATE TABLE t2(a REFERENCES t1, b);

    CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));

    CREATE TABLE t4(a, b, c, FOREIGN KEY(c, b) REFERENCES t3);

    CREATE TABLE t5(a REFERENCES t4(d), b, c);

    CREATE TABLE t6(a REFERENCES t4(a), b, c);

    CREATE TABLE t7(a REFERENCES t3(a), b, c);

    CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c);

  }

} {}

do_test genfkey-4.X {

  set rc [catch {exec ./sqlite3 test.db .genfkey} msg]

  list $rc $msg

} "1 {[string trim {

Error in table t5: foreign key columns do not exist

Error in table t8: foreign key columns do not exist

Error in table t4: implicit mapping to composite primary key

Error in table t1: implicit mapping to non-existant primary key

Error in table t2: implicit mapping to non-existant primary key

Error in table t6: foreign key is not unique

Error in table t7: foreign key is not unique

}]}"

# Test that ticket #3800 has been resolved.

#

do_test genfkey-5.1 {

  execsql {

    DROP TABLE t1; DROP TABLE t2; DROP TABLE t3;

    DROP TABLE t4; DROP TABLE t5; DROP TABLE t6;

    DROP TABLE t7; DROP TABLE t8;

  }

} {}

do_test genfkey-5.2 {

  execsql {

    CREATE TABLE "t.3" (c1 PRIMARY KEY);

    CREATE TABLE t13 (c1, foreign key(c1) references "t.3"(c1));

  }

} {}

do_test genfkey-5.3 {

  set rc [catch {exec ./sqlite3 test.db .genfkey} msg]

} {0}

do_test genfkey-5.4 {

  db eval $msg

} {}

do_test genfkey-5.5 {

  catchsql { INSERT INTO t13 VALUES(1) }

} {1 {constraint failed}}

do_test genfkey-5.5 {

  catchsql { 

    INSERT INTO "t.3" VALUES(1);

    INSERT INTO t13 VALUES(1);

  }

} {0 {}}

# Test also column names that require quoting.

do_test genfkey-6.1 {

  execsql {

    DROP TABLE "t.3";

    DROP TABLE t13;

    CREATE TABLE p(

      "a.1 first", "b.2 second", 

      UNIQUE("a.1 first", "b.2 second")

    );

    CREATE TABLE c(

      "c.1 I", "d.2 II", 

        FOREIGN KEY("c.1 I", "d.2 II") 

        REFERENCES p("a.1 first", "b.2 second")

        ON UPDATE CASCADE ON DELETE CASCADE

    );

  }

} {}

do_test genfkey-6.2 {

  set rc [catch {exec ./sqlite3 test.db .genfkey} msg]

} {0}

do_test genfkey-6.3 {

  execsql $msg

  execsql {

    INSERT INTO p VALUES('A', 'B');

    INSERT INTO p VALUES('C', 'D');

    INSERT INTO c VALUES('A', 'B');

    INSERT INTO c VALUES('C', 'D');

    UPDATE p SET "a.1 first" = 'X' WHERE rowid = 1;

    DELETE FROM p WHERE rowid = 2;

  }

  execsql { SELECT * FROM c }

} {X B}

do_test genfkey-6.4 {

  execsql {

    DROP TABLE p;

    DROP TABLE c;

    CREATE TABLE parent("a.1", PRIMARY KEY("a.1"));

    CREATE TABLE child("b.2", FOREIGN KEY("b.2") REFERENCES parent("a.1"));

  }

  set rc [catch {exec ./sqlite3 test.db .genfkey} msg]

} {0}

do_test genfkey-6.5 {

  execsql $msg

  execsql {

    INSERT INTO parent VALUES(1);

    INSERT INTO child VALUES(1);

  }

  catchsql { UPDATE parent SET "a.1"=0 }

} {1 {constraint failed}}

do_test genfkey-6.6 {

  catchsql { UPDATE child SET "b.2"=7 }

} {1 {constraint failed}}

do_test genfkey-6.7 {

  execsql {

    SELECT * FROM parent;

    SELECT * FROM child;

  }

} {1 1}