slick mapping an array of records for postgres

Question

Could someone help me out by showing me how to map these two types :

case class forumQuote(
            index:          Int,
            startOffset:    Int,
            endOffset:      Int,
            isDirect:       Boolean,
            quotedId:       Int)
case class forumQuotes(quotes: List[forumQuote])

represented in postgres As :

CREATE TYPE forum_quote AS
(
    index           INTEGER,
    q_start_offset  INTEGER,
    q_end_offset    INTEGER,
    is_direct       BOOLEAN,
    quoted_id       INTEGER
);

Used as a array field in

CREATE TABLE forum_posts
(
...
quotes      forum_quote [],
...
)

Used in my own lifted table as :

object ForumPosts extends Table[...] {
...
def quotes = Column[forumQuotes]("forum_quotes")
...
}

Note: I prefer not see any use of JDBC arrays since I need to do some funky stuff with hstore later on (Key[String] => Value[Array[T]]) where T is a postgresql record.

This seems like quite an odd data design - that's a pretty rich data structure to store in a single column. Would it not make more sense to have a `forum_quotes` table with a Foreign Key to `forum_posts` (since this is a one-to-many relationship) and then let your ORM traverse it for you? — IMSoP, Nov 26 '13 at 19:42
it's too slow. I have A couple of gigs worth of raw data (20 or so, without indexes up). and I'm going to be building custom inverted search indexes later on, i'm mainly learning how to do it for those purposes. — Hassan Syed, Nov 26 '13 at 19:45
I don't know the technologies you're using, but I'm wondering if there are other approaches to the actual underlying problem which wouldn't require such non-standard/unusual functionality: a "NoSQL" document store? a lazy-loading approach to getting the quotes on the posts you've retrieved (I presume you're never loading the other way around, else this structure really wouldn't make sense)? custom SQL that makes better use of indexes than what the ORM is generating? — IMSoP, Nov 26 '13 at 19:55
postgres is perfectly capable of doing this "non standard" stuff. as proof the gist/gin index structures. pivoting data is how you get the advantages of nosql. If you never need to access data B in isolation of data A, and it is a 1 to many relation it only makes sense to pivot it. The data in question does not need to be in an index. However hstore/gin performs better than traditional indexes for my work. I am porting over existing code and before I move onto the hstore stuff I wanted to cut my teeth at something simpler. I would have made it into a relation otherwise. — Hassan Syed, Nov 26 '13 at 21:45

Hassan Syed · Accepted Answer · 2013-12-02T18:58:33.677

FYI slick-pg will have full support for records and arrays soon.

I adapted the code from postgres extension plugin from the slick dudes. The following shows how you would go about getting type mapper for records, and arrays of records.

  object ForumQuoteMapper extends RecordMapper[ForumQuote] {
    val name = "forum_quote"
    val extractorRegex = "\\((\\d+),(\\d+),(\\d+),([t|f]),(\\d+)\\)".r
    def fnFromString = (lit: String) =>  {
      def toBool(str: String) = { str match { case "t" => true; case "f" => false}}

      lit match {
        case extractorRegex(index,startOffset,endOffset,bol,quotedId) =>
          ForumQuote(index.toInt,startOffset.toInt,endOffset.toInt,toBool(bol),quotedId.toInt)
      }}
    def fnToString = (v: ForumQuote)=>s"(${v.index}, ${v.startOffset}, ${v.endOffset}, ${v.isDirect},${v.quotedId})"
  }
  implicit val forumQuoteRecordMapper = ForumQuoteMapper.getRecordMapper
  implicit val forumQuoteArrayMapper = ForumQuoteMapper.getRecordArrayMapper

The adapted code can be found here. I'll probably find and fix tons of bugs once I start integrating it into my code :D but it passes the basic test cases as is.

slick mapping an array of records for postgres

1 Answers1