Blogging about Life, the Universe, and my baby girl.
Using a Database View With Scala Slick
Scala Slick is pretty darn cool. I’ve been using it for my app MagicNotebook.io,
which is going to change the way teachers guide students through the writing
process.
As I explored in my last post, it is important that design decisions should
drive technology choices and not the other way around. To do that, you need to
be prepared to think outside the box regarding what exactly those technology
choices are, and that’s the focus of today’s post.
To review, here’s the current representation of a Google Drive folder in my app:
To create a MagicNotebook object, we start with the user’s id to get the fileId
of the Google Drive folder that is the root of all the MagicNotebook files in
the user’s Google Drive. With that, we build a GFolder object of that root
folder along with a map of all the subdirectory GFolders and GDocs. But in the
database, the representation of a GFolder is split among four tables. In
addition, there is scions, representing the children, grand-children,
great-grand-children, etc of a folder.
My first attempt to do this transformation was purely with Scala and Scala
Slick code, and seemed a bit clunky. So it was time to think outside the box.
The documentation for Scala Slick explains clearly how to map a Scala
object to a database table, but nowhere in the documentation does it mention
database views. I tested it with Play 2.2.0 and Scala Slick 1.0.1, and it turns
out you can bind a Scala Slick Table to a database view! First, we begin by
defining a view that uses a recursive query to find all scions (children,
grand-children, great-grand-children, etc.) of a folder:
Next, we need to string-agg function that will flatten multiple rows of a
fileIds from a one-to-many relationship to a one-to-one relationship as a
single comma-delimited string row:
The only transformation required to map this view into a GFolder object is to
split the comma-delimited String of parent, child, & scion fileIds, which is
rather trivial to do with Scala. So here is the Scala object: