MODULE SQLiteConstraints;
(* Extended constraint result codes:
   unique, not-null, primary-key, check and foreign-key failures. *)

FROM SYSTEM IMPORT ADDRESS;
FROM SQLite IMPORT
  SQLiteOk, SQLiteConstraint,
  SQLiteConstraintUnique, SQLiteConstraintNotNull,
  SQLiteConstraintPrimaryKey, SQLiteConstraintCheck,
  SQLiteConstraintForeignKey,
  SQLiteOpenReadWrite, SQLiteOpenCreate, SQLiteOpenMemory,
  sqlite3_open_v2, sqlite3_exec, sqlite3_close_v2,
  sqlite3_extended_result_codes, sqlite3_extended_errcode,
  sqlite3_free;

VAR
  ExitCode : INTEGER;
  db : ADDRESS;
  rc : INTEGER;
  ok : BOOLEAN;

PROCEDURE ExecSQL (sql : ARRAY OF CHAR) : BOOLEAN;
VAR
  errmsg : ADDRESS;
  good : BOOLEAN;
BEGIN
  errmsg := NIL;
  good := sqlite3_exec(db, sql, NIL, NIL, errmsg) = SQLiteOk;
  IF errmsg # NIL THEN
    sqlite3_free(errmsg)
  END;
  RETURN good
END ExecSQL;

PROCEDURE Expect (sql : ARRAY OF CHAR; code : INTEGER) : BOOLEAN;
VAR
  errmsg : ADDRESS;
  got : INTEGER;
  good : BOOLEAN;
BEGIN
  errmsg := NIL;
  got := sqlite3_exec(db, sql, NIL, NIL, errmsg);
  good := ((got = code) OR (got = SQLiteConstraint))
          AND (sqlite3_extended_errcode(db) = code);
  IF errmsg # NIL THEN
    sqlite3_free(errmsg)
  END;
  RETURN good
END Expect;

BEGIN
  ExitCode := 1;
  db := NIL; ok := FALSE;

  IF (sqlite3_open_v2(":memory:", db,
        SQLiteOpenReadWrite + SQLiteOpenCreate + SQLiteOpenMemory,
        NIL) = SQLiteOk) AND (db # NIL)
     AND (sqlite3_extended_result_codes(db, 1) = SQLiteOk) THEN
    ok := TRUE
  END;

  IF ok THEN
    ok := ExecSQL("CREATE TABLE u(v TEXT UNIQUE);")
          AND ExecSQL("INSERT INTO u VALUES('a');")
          AND Expect("INSERT INTO u VALUES('a');",
                     SQLiteConstraintUnique)
  END;

  IF ok THEN
    ok := ExecSQL("CREATE TABLE n(v TEXT NOT NULL);")
          AND Expect("INSERT INTO n VALUES(NULL);",
                     SQLiteConstraintNotNull)
  END;

  IF ok THEN
    ok := ExecSQL("CREATE TABLE p(id INTEGER PRIMARY KEY);")
          AND ExecSQL("INSERT INTO p VALUES(1);")
          AND Expect("INSERT INTO p VALUES(1);",
                     SQLiteConstraintPrimaryKey)
  END;

  IF ok THEN
    ok := ExecSQL("CREATE TABLE c(v INTEGER CHECK(v > 0));")
          AND Expect("INSERT INTO c VALUES(0);",
                     SQLiteConstraintCheck)
  END;

  IF ok THEN
    ok := ExecSQL("PRAGMA foreign_keys=ON;")
          AND ExecSQL("CREATE TABLE f(pid INTEGER REFERENCES p(id));")
          AND Expect("INSERT INTO f VALUES(99);",
                     SQLiteConstraintForeignKey)
  END;

  IF db # NIL THEN
    IF sqlite3_close_v2(db) # SQLiteOk THEN ok := FALSE END
  END;
  IF ok THEN ExitCode := 42 END
END SQLiteConstraints.