yesod persistent postgresql complex record

Question

I am using persistent to save the following records (Time is UTCTime).

type Price = Int
type Volume = Int

share [mkPersist sqlSettings, mkMigrate "migrateBook"] 
  [persistLowerCase|
    Level
      limit     Price
      volumes   [Volume]
      deriving Show Read Eq

    Book
      time      Time
      asks      [Level]
      bids      [Level]
      deriving Show Read Eq
  |]

When I migrate the structures I get a Book table:

CREATE TABLE book
(
 id serial NOT NULL,
 "time" timestamp without time zone NOT NULL,
 asks character varying NOT NULL,
 bids character varying NOT NULL,
 CONSTRAINT book_pkey PRIMARY KEY (id )
 )

and a table Level:

 CREATE TABLE level
 (
   id serial NOT NULL,
   "limit" double precision NOT NULL,
   volumes character varying NOT NULL,
   CONSTRAINT level_pkey PRIMARY KEY (id )
 )

When inserting a book, the level table remains empty and the Book table contains an entry containing JSON versions of the intended record.

Questions:

How can I get persistent to use the actual simple types (e.g. int and time) for the columns instead of JSON for the complex types?

Does Persistent know how to store a many to many relation?

e.g. given a record A with a list :: [B], can I get it to create a third table with

AId | B
-------
 1  | b1
 1  | b2 etc

I am using the following packages:

persistent-postgresql-1.0.3
yesod-persistent-1.1.0.1
Postgres 9.1

Persistent does not attempt to manage relations, so you will need to define lookup tables manually. Specify list types such as `[Level]` causes the entire list to be serialized into a single field as you've observed. You can use identifiers such as `BookId` and `LevelId` to refer to records of other tables. — Michael Steele, Dec 18 '13 at 17:22

score 1 · Accepted Answer · answered Jan 20 '14 at 08:21

Firstly, for [Level], storing just the ID allows you to store a simple type in a json list.

type Price = Int
type Volume = Int

share [mkPersist sqlSettings, mkMigrate "migrateBook"] 
  [persistLowerCase|
    Level
      limit     Price
      volumes   [Volume]
      deriving Show Read Eq

    Book
      time      Time
      asks      [LevelId]
      bids      [LevelId]
      deriving Show Read Eq
  |]

Alternatively, if you need to query across the relation, you need to define a "through" or M2M table, just like in a normal relational database design.

type Price = Int
type Volume = Int

share [mkPersist sqlSettings, mkMigrate "migrateBook"] 
  [persistLowerCase|
    Level
      limit     Price
      volumes   [Volume]
      deriving Show Read Eq

    Book
      time      Time
      deriving Show Read Eq

    BookAsk
      book      BookId
      level     LevelId

    BookBid
      book      BookId
      level     LevelId
  |]

As is the case for, for [Volume], the problem is in persistent-postgresql. persistent is hardwired to marshal PersistList values as JSON, instead of using postgres's native support for array column types. If you want to fix this, you'll need to submit an issue or a pull request.

yesod persistent postgresql complex record

1 Answers1