interpolation of boolean values with sqlite prepared statements

[chrismatheson]

i think this is as small an example as i can make

(define-schema recipe
  ([id id/f #:primary-key #:auto-increment]
   [Favorite? boolean/f  #:name "Favorite"]))

(favs (val)
  (sequence->list (in-entities conn
                               (~> (from recipe #:as b)
                                   (where (= ,val b.Favorite))
                                   )))
(favs true) ;throws errors/contract violations

[Bogdanp]

I've cleaned up your example a little bit:

#lang racket/base

(require db
         deta
         threading)

(define conn (sqlite3-connect #:database 'memory))

(define-schema recipe
  ([id id/f #:primary-key #:auto-increment]
   [Favorite? boolean/f #:name "Favorite"]))

(create-table! conn recipe-schema)

(insert-one! conn (make-recipe #:Favorite? #t))

;; Fails:
#;
(for ([r (in-entities conn
                      (~> (from recipe #:as b)
                          (where (= ,#t b.Favorite))))])
  (displayln (recipe-id r)))

;; Works:
(for ([r (in-entities conn
                      (~> (from recipe #:as b)
                          (where (= ,1 b.Favorite))))])
  (displayln (recipe-id r)))

The issue here is that SQLite doesn't have a native boolean type, so deta's boolean/f is just an INTEGER type under SQLite meaning that when you construct an arbitrary query, deta can't hook in and translate the boolean values to integers for you. I don't know if deta can do any better here for SQLite.

[chrismatheson]

yeah, i figured out the boolean <--> integer stuff after a while (sorry should have said) i was mostly sort of loging for posterity / maybe you might have some thoughts in the future :)

at the point the prepared statement is created, is there any way to determine the DB type it will run on? then at that point the interpolation function generated could add in the required value transformers maybe?

[chrismatheson]

So the end result might be something like

(~> (from recipe #:as b)
  (where (= ,INPUT b.Favorite)))

(query
 "SELECT b.id, b.\"Name\", b.\"Ingredients\", b.\"Favorite\", b.\"Notes\" FROM recipes AS b WHERE $1 = b.\"Favorite\""
 (bool->integer INPUT))```

[Bogdanp]

at the point the prepared statement is created, is there any way to determine the DB type it will run on? then at that point the interpolation function generated could add in the required value transformers maybe?

Yep, I've thought about that as well. That information is definitely available to db-lib, but I'm not sure if db-lib exposes it. I'll have to look into that later this week.