How to store a list (or struct...) in SQL and extract again as original Racket value

[Marc Kaufmann]

Hi,

I want to store matrices of the following form (but larger) in a database:

(define m '((0 1) (1 0)))

Currently I manage to store them by turning them into strings first via:

(~a (serialize m)); Or just drop the serialize, but I figured I might benefit from it later.

The problem is that I can only get a string out of the database. Obviously I could write a parser for this, but it feels a little odd having to write one for such a simple, and probably common, task.

The question I have is whether there is a common best practice for storing such things in a database, or whether I should write a parser? And if I should write a parser, I presume I should use either Parsack or Megaparsack?

Thanks,
Marc

[Jay McCarthy]

Is there a reason not to use deserialize?

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



--
-=[ Jay McCarthy http://jeapostrophe.github.io ]=-
-=[ Associate Professor PLT @ CS @ UMass Lowell ]=-
-=[ Moses 1:33: And worlds without number have I created; ]=-

[Jon Zeppieri]

On Tue, Mar 21, 2017 at 5:34 PM, Marc Kaufmann
<marc.ka...@gmail.com> wrote:

Depends on your needs. However, postgres (if that's what you're using)
has multidimensional arrays as a native type, so you could use those
[https://www.postgresql.org/docs/current/static/arrays.html]. The
Racket db package has a `pg-array` struct with conversion to/from
lists to help you.

If you're not using postgres and you don't actually need to query
against the data, just store and retrieve it, then
serialize/deserialize is fine.

[Jon Zeppieri]

On Tue, Mar 21, 2017 at 5:44 PM, Jon Zeppieri <zepp...@gmail.com> wrote:
> However, postgres (if that's what you're using)
> has multidimensional arrays as a native type, so you could use those
> [https://www.postgresql.org/docs/current/static/arrays.html]. The
> Racket db package has a `pg-array` struct with conversion to/from
> lists to help you.
>

Ah, except apparently `pg-array` only supports arrays with dimension
1. So... that won't help.

[Philip McGrath]

I would do:
(with-input-from-string from-db
(λ () (deserialize (read))))

[George Neuner]

On 3/21/2017 5:48 PM, Jon Zeppieri wrote:

Ah, except apparently `pg-array` only supports arrays with dimension
1. So... that won't help.

I *think* Ryan Culpepper fixed that a long time ago ... though the docs may never have been updated.  I had a workaround at the time and unfortunately I never did go back to verify the fix in later releases.

George


On 12/18/2014 09:03 PM, George Neuner wrote:

Using 6.0.1.   I just painfully discovered that

   (pg-array->list (list->pg-array (list)))
   => ERROR
   pg-array->list: expected argument of type <pg-array of dimension 1>; given: (pg-array 0 '() '() '#())

The documentation for  list->pg-array  states that it produces an array of dimension 1.  However, if you pass an empty list, you get back an array of dimension zero which you then can't transform back to a list [ except by going straight to the internal vector ].

My question is, "shouldn't these conversions be symmetric?"   I understand  an array with no elements is meaningless as an array, but Postgresql (ab)uses arrays as substitutes for lists and sets, so an empty array does have meaning.

[Marc Kaufmann]

Regarding not using deserialize directly: I may be using deserialize in the wrong way, but the following doesn't work (and I had tried that before posting):

> (define-values (in out) (make-pipe))
> (write (~a (serialize '((0 1) (1 0)))))
"((3) 0 () 0 () () (q (0 1) (1 0)))"
> (write (~a (serialize '((0 1) (1 0)))) out)
> (deserialize (read in))
; car: contract violation
;   expected: pair?
;   given: "((3) 0 () 0 () () (q (0 1) (1 0)))"
; [,bt for context]

'read simply returns a string. On the other hand, using Philip's suggestion works (although I can't say I fully understand the subtleties involved):

> (define serialized-string (~a (serialize '((0 1) (1 0)))))

> (with-input-from-string serialized-string (lambda () (deserialize (read))))
'((0 1) (1 0))

Regarding pg-array, I thought (from the docs) that it allowed for multi-dimensional arrays, but since I might need a similar functionality for structs and the like, I thought that I should find another solution.

Thanks everyone for the quick response.

Cheers,

Marc

--
You received this message because you are subscribed to a topic in the Google Groups "Racket Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/racket-users/xAbm8mlPX-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to racket-users+unsubscribe@googlegroups.com.

[Philip McGrath]

When you use:

(write (~a (serialize '((0 1) (1 0)))) out)

you are first turning the result of deserialize into a string, then writing the string, so read will produce a string.

Your example will work correctly if you drop the ~a and just use:

(write (serialize '((0 1) (1 0))) out)

If that's less than totally clear, for illustrative purposes, consider the following:

> (serialize '((0 1) (1 0)))

'((3) 0 () 0 () () (q (0 1) (1 0)))

> (~a '((3) 0 () 0 () () (q (0 1) (1 0))))

"((3) 0 () 0 () () (q (0 1) (1 0)))"

> (with-output-to-string

   (λ () (write "((3) 0 () 0 () () (q (0 1) (1 0)))")))

"\"((3) 0 () 0 () () (q (0 1) (1 0)))\""

> (with-input-from-string "\"((3) 0 () 0 () () (q (0 1) (1 0)))\""

      read)

"((3) 0 () 0 () () (q (0 1) (1 0)))"

> (with-output-to-string

   (λ () (write '((3) 0 () 0 () () (q (0 1) (1 0))))))

"((3) 0 () 0 () () (q (0 1) (1 0)))"

> (with-input-from-string "((3) 0 () 0 () () (q (0 1) (1 0)))"

      read)

'((3) 0 () 0 () () (q (0 1) (1 0)))

> (deserialize '((3) 0 () 0 () () (q (0 1) (1 0))))

'((0 1) (1 0))

To unsubscribe from this group and all its topics, send an email to racket-users...@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...@googlegroups.com.

[Marc Kaufmann]

Thanks. But I have to turn it into a string before storing it in the database; when I tried to store a serialized list in it (in a VARCHAR field), it complained that it expected a string. That's where all the trouble came from. The trouble I had was parsing the string back into the serialized form, which 'with-input-from-string does (because, I presume, it treats the contents of the string as the input, rather than the string itself).

Of course, if there is a way to store serialized data directly in the database, that would be great - but -- except for arrays in a Postgre database -- that doesn't seem to be the case.

To unsubscribe from this group and all its topics, send an email to racket-users+unsubscribe@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+unsubscribe@googlegroups.com.

[Jon Zeppieri]

Did you try a using a bytea field?

>>>> racket-users...@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...@googlegroups.com.

[Jon Zeppieri]

Oh, never mind. I see what you mean.

[George Neuner]

On 3/21/2017 8:36 PM, Marc Kaufmann wrote:

Thanks. But I have to turn it into a string before storing it in the database; when I tried to store a serialized list in it (in a VARCHAR field), it complained that it expected a string. That's where all the trouble came from. The trouble I had was parsing the string back into the serialized form, which 'with-input-from-string does (because, I presume, it treats the contents of the string as the input, rather than the string itself).

Of course, if there is a way to store serialized data directly in the database, that would be great - but -- except for arrays in a Postgre database -- that doesn't seem to be the case.

Serialize produce a list.  You might try storing it as JSON.  Postgresql can parse and search JSON values.

https://www.postgresql.org/docs/current/static/datatype-json.html
https://www.postgresql.org/docs/current/static/functions-json.html


George

[Marc Kaufmann]

Thanks, I will give that a try in the future, once I have time to look at PG database (as I have some experience with Mysql, but none with PG). But that would be exactly the kind of thing I was looking for.

Cheers,

Marc

[Philip McGrath]

To work with strings for the database, do something like this:

(define (serialize-to-string v)
(with-output-to-string
(λ () (write (serialize v)))))
(define (deserialize-from-string str)
(with-input-from-string str
(λ () (deserialize (read)))))