Check column types in addition to column name

[thizanne]

Not sure if it's possible with the model of sqlexpr, but it would be nice in if the following code, the should_fail SQL queries were detected by auto_check_db.

module Sqlexpr = Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Id)
module S = Sqlexpr

let init_db db =
  S.execute db
    [%sqlinit "CREATE TABLE users(login TEXT UNIQUE);"]

let should_fail db =
  (* wrong type *)
  S.select_one db [%sqlc "SELECT @d{login} FROM users"]

let fail db =
  S.select_one db [%sqlc "SELECT @s{wrongfield} FROM users"]

let should_fail_2 db =
  (* wrong type *)
  S.insert db [%sqlc "INSERT INTO users(login) VALUES (%d)"] 1

let fail_2 db =
  S.insert db [%sqlc "INSERT INTO users(wrongfield) VALUES (%s)"] "john"

let auto_init_db, check_db, auto_check_db = [%sqlcheck "sqlite"]

let () =
  let db = S.open_db ":memory:" in
  init_db db;
  let _ : bool = auto_check_db Format.err_formatter in
  print_int (should_fail db);
  ignore (should_fail_2 db);

I'm guessing this is far more difficult than checking field names with prepared statements, but I am no expert in Sqlite so I'm still asking.

[mfp]

The (auto_)check functions work by creating a database with the schema declared with sqlinit, and then prepare all statements against it. This catches all the errors Sqlite itself would detect (mismatching columns, wrong joins, plain SQL syntax errors...). The problem is that Sqlite uses dynamic typing and is much more lenient that I'd like (it only uses the types as a default "affinity" and then performs conversions automatically), so it doesn't complain when there's a type mismatch and instead performs dynamic conversions.