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 <ry...@ccs.neu.edu> 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 racket-users+unsubscr...@googlegroups.com
> > <mailto:racket-users+unsubscr...@googlegroups.com>.
> > 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 racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
