Re: [racket-users] Getting JSON to work with the DB module

[David Storrs]

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  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 fo

Re: [racket-users] Getting JSON to work with the DB module

[Ryan Culpepper]

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 
.

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.

[racket-users] Getting JSON to work with the DB module

[David Storrs]

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.
For more options, visit https://groups.google.com/d/optout.