# 2005 November 2

#

# 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.  The

# focus of this file is testing CHECK constraints

#

# $Id: check.test,v 1.11 2007/07/23 19:39:47 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Only run these tests if the build includes support for CHECK constraints

ifcapable !check {

  finish_test

  return

}

do_test check-1.1 {

  execsql {

    CREATE TABLE t1(

      x INTEGER CHECK( x<5 ),

      y REAL CHECK( y>x )

    );

  }

} {}

do_test check-1.2 {

  execsql {

    INSERT INTO t1 VALUES(3,4);

    SELECT * FROM t1;

  }  

} {3 4.0}

do_test check-1.3 {

  catchsql {

    INSERT INTO t1 VALUES(6,7);

  }

} {1 {constraint failed}}

do_test check-1.4 {

  execsql {

    SELECT * FROM t1;

  }  

} {3 4.0}

do_test check-1.5 {

  catchsql {

    INSERT INTO t1 VALUES(4,3);

  }

} {1 {constraint failed}}

do_test check-1.6 {

  execsql {

    SELECT * FROM t1;

  }  

} {3 4.0}

do_test check-1.7 {

  catchsql {

    INSERT INTO t1 VALUES(NULL,6);

  }

} {0 {}}

do_test check-1.8 {

  execsql {

    SELECT * FROM t1;

  }  

} {3 4.0 {} 6.0}

do_test check-1.9 {

  catchsql {

    INSERT INTO t1 VALUES(2,NULL);

  }

} {0 {}}

do_test check-1.10 {

  execsql {

    SELECT * FROM t1;

  }  

} {3 4.0 {} 6.0 2 {}}

do_test check-1.11 {

  execsql {

    DELETE FROM t1 WHERE x IS NULL OR x!=3;

    UPDATE t1 SET x=2 WHERE x==3;

    SELECT * FROM t1;

  }

} {2 4.0}

do_test check-1.12 {

  catchsql {

    UPDATE t1 SET x=7 WHERE x==2

  }

} {1 {constraint failed}}

do_test check-1.13 {

  execsql {

    SELECT * FROM t1;

  }

} {2 4.0}

do_test check-1.14 {

  catchsql {

    UPDATE t1 SET x=5 WHERE x==2

  }

} {1 {constraint failed}}

do_test check-1.15 {

  execsql {

    SELECT * FROM t1;

  }

} {2 4.0}

do_test check-1.16 {

  catchsql {

    UPDATE t1 SET x=4, y=11 WHERE x==2

  }

} {0 {}}

do_test check-1.17 {

  execsql {

    SELECT * FROM t1;

  }

} {4 11.0}

do_test check-2.1 {

  execsql {

    CREATE TABLE t2(

      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),

      y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),

      z TEXT CHECK( typeof(coalesce(z,''))=="text" )

    );

  }

} {}

do_test check-2.2 {

  execsql {

    INSERT INTO t2 VALUES(1,2.2,'three');

    SELECT * FROM t2;

  }

} {1 2.2 three}

do_test check-2.3 {

  execsql {

    INSERT INTO t2 VALUES(NULL, NULL, NULL);

    SELECT * FROM t2;

  }

} {1 2.2 three {} {} {}}

do_test check-2.4 {

  catchsql {

    INSERT INTO t2 VALUES(1.1, NULL, NULL);

  }

} {1 {constraint failed}}

do_test check-2.5 {

  catchsql {

    INSERT INTO t2 VALUES(NULL, 5, NULL);

  }

} {1 {constraint failed}}

do_test check-2.6 {

  catchsql {

    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);

  }

} {1 {constraint failed}}

ifcapable subquery {

  do_test check-3.1 {

    catchsql {

      CREATE TABLE t3(

        x, y, z,

        CHECK( x<(SELECT min(x) FROM t1) )

      );

    }

  } {1 {subqueries prohibited in CHECK constraints}}

}

do_test check-3.2 {

  execsql {

    SELECT name FROM sqlite_master ORDER BY name

  }

} {t1 t2}

do_test check-3.3 {

  catchsql {

    CREATE TABLE t3(

      x, y, z,

      CHECK( q<x )

    );

  }

} {1 {no such column: q}}

do_test check-3.4 {

  execsql {

    SELECT name FROM sqlite_master ORDER BY name

  }

} {t1 t2}

do_test check-3.5 {

  catchsql {

    CREATE TABLE t3(

      x, y, z,

      CHECK( t2.x<x )

    );

  }

} {1 {no such column: t2.x}}

do_test check-3.6 {

  execsql {

    SELECT name FROM sqlite_master ORDER BY name

  }

} {t1 t2}

do_test check-3.7 {

  catchsql {

    CREATE TABLE t3(

      x, y, z,

      CHECK( t3.x<25 )

    );

  }

} {0 {}}

do_test check-3.8 {

  execsql {

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

    SELECT * FROM t3;

  }

} {1 2 3}

do_test check-3.9 {

  catchsql {

    INSERT INTO t3 VALUES(111,222,333);

  }

} {1 {constraint failed}}

do_test check-4.1 {

  execsql {

    CREATE TABLE t4(x, y,

      CHECK (

           x+y==11

        OR x*y==12

        OR x/y BETWEEN 5 AND 8

        OR -x==y+10

      )

    );

  }

} {}

do_test check-4.2 {

  execsql {

    INSERT INTO t4 VALUES(1,10);

    SELECT * FROM t4

  }

} {1 10}

do_test check-4.3 {

  execsql {

    UPDATE t4 SET x=4, y=3;

    SELECT * FROM t4

  }

} {4 3}

do_test check-4.3 {

  execsql {

    UPDATE t4 SET x=12, y=2;

    SELECT * FROM t4

  }

} {12 2}

do_test check-4.4 {

  execsql {

    UPDATE t4 SET x=12, y=-22;

    SELECT * FROM t4

  }

} {12 -22}

do_test check-4.5 {

  catchsql {

    UPDATE t4 SET x=0, y=1;

  }

} {1 {constraint failed}}

do_test check-4.6 {

  execsql {

    SELECT * FROM t4;

  }

} {12 -22}

do_test check-4.7 {

  execsql {

    PRAGMA ignore_check_constraints=ON;

    UPDATE t4 SET x=0, y=1;

    SELECT * FROM t4;

  }

} {0 1}

do_test check-4.8 {

  catchsql {

    PRAGMA ignore_check_constraints=OFF;

    UPDATE t4 SET x=0, y=2;

  }

} {1 {constraint failed}}

ifcapable vacuum {

  do_test check_4.9 {

    catchsql {

      VACUUM

    }

  } {0 {}}

}

do_test check-5.1 {

  catchsql {

    CREATE TABLE t5(x, y,

      CHECK( x*y<:abc )

    );

  }

} {1 {parameters prohibited in CHECK constraints}}

do_test check-5.2 {

  catchsql {

    CREATE TABLE t5(x, y,

      CHECK( x*y<? )

    );

  }

} {1 {parameters prohibited in CHECK constraints}}

ifcapable conflict {

do_test check-6.1 {

  execsql {SELECT * FROM t1}

} {4 11.0}

do_test check-6.2 {

  execsql {

    UPDATE OR IGNORE t1 SET x=5;

    SELECT * FROM t1;

  }

} {4 11.0}

do_test check-6.3 {

  execsql {

    INSERT OR IGNORE INTO t1 VALUES(5,4.0);

    SELECT * FROM t1;

  }

} {4 11.0}

do_test check-6.4 {

  execsql {

    INSERT OR IGNORE INTO t1 VALUES(2,20.0);

    SELECT * FROM t1;

  }

} {4 11.0 2 20.0}

do_test check-6.5 {

  catchsql {

    UPDATE OR FAIL t1 SET x=7-x, y=y+1;

  }

} {1 {constraint failed}}

do_test check-6.6 {

  execsql {

    SELECT * FROM t1;

  }

} {3 12.0 2 20.0}

do_test check-6.7 {

  catchsql {

    BEGIN;

    INSERT INTO t1 VALUES(1,30.0);

    INSERT OR ROLLBACK INTO t1 VALUES(8,40.0);

  }

} {1 {constraint failed}}

do_test check-6.8 {

  catchsql {

    COMMIT;

  }

} {1 {cannot commit - no transaction is active}}

do_test check-6.9 {

  execsql {

    SELECT * FROM t1

  }

} {3 12.0 2 20.0}

do_test check-6.11 {

  execsql {SELECT * FROM t1}

} {3 12.0 2 20.0}

do_test check-6.12 {

  catchsql {

    REPLACE INTO t1 VALUES(6,7);

  }

} {1 {constraint failed}}

do_test check-6.13 {

  execsql {SELECT * FROM t1}

} {3 12.0 2 20.0}

do_test check-6.14 {

  catchsql {

    INSERT OR IGNORE INTO t1 VALUES(6,7);

  }

} {0 {}}

do_test check-6.15 {

  execsql {SELECT * FROM t1}

} {3 12.0 2 20.0}

}

finish_test