Okay, thanks. I wonder if this is a problem space that the sql module
could fit into? Perhaps tag the fields with wrapper functions.
Regardless, I've found a clean way around it by looping through a smart
struct defined using the struct-plus-plus module.
#lang at-exp racket
(require struct-plus-plus db json)
(define db (sqlite3-connect #:database "test.db"))
(query-exec
db
@~a{CREATE TEMPORARY TABLE user (id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
data JSON NOT NULL DEFAULT '{}')})
(query-exec db "insert into user (name, data) values ('tom jones', '[7]')")
(display "initial rows: ") (query-rows db "select * from user")
(query-exec db "delete from user")
; struct named
user
; id field is optional, defaults to #f, must be #f or
exact-positive-integer?
; name is mandatory, must be string?
; data is optional, defaults to (hash), must be both hash and jsexpr?
(struct++ user
([(id #f) (or/c #f exact-positive-integer?)]
[name string?]
[(data (hash)) (and/c hash? jsexpr?)] )
(#:convert-for (db (#:overwrite
(hash 'data
(lambda (d)
(with-output-to-string
(thunk (write-json
d)))))))
#:convert-from (db (vector? (vector id name
(app (curryr
with-input-from-string
read-json)
data))
(id name data))))
#:transparent)
(define initial (user++ #:name "tom jones"))
(display "initial user struct: ") (println initial)
(define final (user/convert->db (set-user-data initial (hash 'phone
8675309))))
(display "user data for db: ")(println final)
(query-exec db
"INSERT INTO user (name, data) VALUES ($1, $2)"
(hash-ref final 'name) (hash-ref final 'data))
(query-rows db "select * from user")
(db->user++ (query-row db "select id, name, data FROM user"))
;; ------------ EOF
Output:
$ racket test.rkt
initial rows: '(#(1 "tom jones" "[7]"))
initial user struct: (user #f "tom jones" '#hash())
user data for db: '#hash((data . "{\"phone\":8675309}") (id . #f) (name .
"tom jones"))
'(#(1 "tom jones" "{\"phone\":8675309}"))
(user 1 "tom jones" '#hasheq((phone . 8675309)))
On Tue, Apr 23, 2019 at 6:39 PM Ryan Culpepper <[email protected]> wrote:
> It is not possible, unfortunately. You must do the conversion to and
> from strings yourself.
>
> I've thought about adding a hook for additional conversions based on
> declared types, but there's no declared type information at all for
> parameters, and the declared type for results is fragile: a column name
> has a declared type but no other kind of expression does.
>
> Ryan
>
>
> On 4/23/19 20:03, David Storrs wrote:
> > tl;dr I'm having trouble getting JSON support working in the db module
> > when using SQLite and would really appreciate some direction, or
> > confirmation that it's impossible. I suspect that it's impossible,
> > since the docs list the accepted Racket types as exact-integer?, real?,
> > string?, and bytes?. I was hoping that having the JSON extension in
> > would add conversion ability to this, but it looks like not.
> >
> >
> > Longer:
> > SQLite does not natively support JSON, but there's an extension that can
> > be dynamically- or statically linked. https://sqlite.org/json1.html
> >
> > When working with a Postgres database, the DB module will handle
> > transforming things (e.g. hashes) to and from JSON on insert/select,
> > which is insanely useful and convenient. I'd like to get the same
> > behavior in SQLite, especially since that would let me use the json_agg
> > function which would be a reasonable replacement for Pg's ARAAY_AGG
> > feature, of which I make heavy use.
> >
> >
> >
> > Here's what I've done so far:
> >
> > 0. I've read the docs on the db module carefully, which has me concerned
> > about whether this is possible at all. Still, optimism!
> > 1. I've compiled the JSON1 extension into the libsqlite.* files
> > 2. I've verified that JSON is working via the sqlite CLI client (i.e.,
> > not the Racket db module)
> > 3. I've put the libsqlite.* files in my /Applications/Racket_v7.1/lib
> > directory (one of the entries in (get-lib-search-dirs)).
> >
> > At this point I tried this:
> >
> > > (require json db)
> > > (define db (sqlite3-connect #:database "foo.db"))
> > > (query db "create temporary table blogsnort (id integer primary key,
> > data json))
> > (simple-result '((insert-id . #f) (affected-rows . 0)))
> >
> > > (query db "insert into blogsnort (data) values ($1)" (hash 'a 1))
> > ; query: cannot convert given value to SQL type
> > ; given: '#hash((a . 1))
> > ; type: parameter
> > ; dialect: SQLite
> > ; [,bt for context]
> >
> > I tried setting the storage class on the 'data' column to TEXT (I wasn't
> > sure if the JSON extension added a 'JSON' type but figured it was worth
> > trying), but that made no difference.
> >
> > Am I right that it's simply impossible and that I'll need to manually
> > convert to/from strings?
> >
> >
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "Racket Users" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to [email protected]
> > <mailto:[email protected]>.
> > For more options, visit https://groups.google.com/d/optout.
>
>
--
You received this message because you are subscribed to the Google Groups
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
