Monthly Archives: March 2016

Slick (1) – fix more than 22 columns case

Slick is a modern database query and access library for Scala. So it is quite clear to understand Slick’s purpose is to  query database. This post is to explain how to fix the access to a table which has more than 22 columns. First, we give known solution for less than 22 columns case. And then, we explain why this solution is not applied to more than 22 columns case. Last, we give modified solution to fix more than 22 columns case.

Solution – less than 22 columns case

Here is a graph to show this simple table’s schema.

Screenshot 2016-03-31 09.52.41

Here is the solution by Slick to access this table.

case class Friend(
 id: Int,
 name: String,
 firstName: String,
 lastName: String,
 createTime: String)

class Friends(tag: Tag) extends Table[Friend](tag, "friends") {
 def id = column[Int]("id", O.AutoInc)
 def name = column[String]("name")
 def firstName = column[String]("first_name")
 def lastName = column[String]("last_name")
 def createTime = column[String]("create_time")

 def * = (id, name, firstName, lastName, createTime) <> (Friend.tupled, Friend.unapply _)
}

Reason

The * operator is to project to a custom type. the <> operator as Scala tuple. Because the result row of a query as a Scala tuple. The type of the tuple will match the Projection that is defined. The root reason is caused by tupled.  Scala programming language limits the max size of a tuples to 22 and tuples are a nice way to represent table rows. You can’t use tupled and unapply for more than 22 columns case.

Solution – more than 22 columns case

Here we give out solution directly. The important part is to build a custom type to satisfy * operator. So you can see we package some columns to one case Class and then apply tupled method back. Here I use three different case Class according to different meanings. You can use your logic to select which columns packaging together, which not.

This is table schema, I also use different colors to help you to know where are the three packages. In fact, these case Class will not influence real physical columns.

Screenshot 2016-03-31 09.55.49

Here is the real code, please notice the colorful marks which will help you to grab important point quickly.

case class User(
                 id: Int,
                 var basicInfo: BasicInfo,
                 var oauth1Info: Oauth1Info,
                 var oauth2Info: Oauth2Info)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.AutoInc)
  def name = column[String]("name")
  def firstName = column[String]("first_name")
  def lastName = column[String]("last_name")
  def email = column[String]("email")
  def avatarUrl = column[String]("avatar_url")

  def timeZone = column[Int]("time_zone")
  def token = column[String]("token")
  def showStatus = column[String]("show_status")
  def showTutorial = column[String]("show_tutorial")
  def createTime = column[String]("create_time")
  def updateTime = column[String]("update_time")

  def provider = column[String]("provider")
  def password = column[String]("password")
  def teamIDs = column[String]("team_ids")

  def oauth1Id = column[String]("oauth1_id")
  def oauth1Token = column[String]("oauth1_token")
  def oauth1Secret = column[String]("oauth1_secret")

  def oauth2Id = column[String]("oauth2_id")
  def oauth2AccessToken = column[String]("oauth2_access_token")
  def oauth2Scope = column[String]("oauth2_scope")
  def oauth2ExpiresIn = column[String]("oauth2_expires_in")
  def oauth2LongLivedToken = column[String]("oauth2_long_lived_token")

  def * = (id,
    (name, firstName, lastName, email, avatarUrl, timeZone, token, showStatus, showTutorial,
      createTime, updateTime, provider, password, teamIDs),
    (oauth1Id, oauth1Token, oauth1Secret),
    (oauth2Id, oauth2AccessToken, oauth2Scope, oauth2ExpiresIn, oauth2LongLivedToken)).shaped <> (
    {case (id, basicInfo, oauth1Info, oauth2Info) =>
     User(id,
       BasicInfo.tupled.apply(basicInfo),
       Oauth1Info.tupled.apply(oauth1Info),
       Oauth2Info.tupled.apply(oauth2Info))},
    { u: User =>
      def f1(p: BasicInfo) = BasicInfo.unapply(p).get
      def f2(p: Oauth1Info) = Oauth1Info.unapply(p).get
      def f3(p: Oauth2Info) = Oauth2Info.unapply(p).get
      Some((u.id, f1(u.basicInfo), f2(u.oauth1Info), f3(u.oauth2Info)))}
    )
}

case class BasicInfo(
                      name: String,
                      firstName: String,
                      lastName: String,
                      email: String,
                      avatarUrl: String,
                      timeZone: Int,
                      token: String,
                      showStatus: String,
                      showTutorial: String,
                      createTime: String,
                      updateTime: String,
                      provider: String,
                      password: String,
                      teamIDs: String)

case class Oauth1Info(
                       oauth1Id: String,
                       oauth1Token: String,
                       oauth1Secret: String)

case class Oauth2Info(
                       oauth2Id: String,
                       oauth2AccessToken: String,
                       oauth2Scope: String,
                       oauth2ExpiresIn: String,
                       oauth2LongLivedToken: String)

Another important thing is that when you use filter to do query or update by Slick, the additional case Class will not influence it. Only when you do save or fetch , you need to open each case Class.  Here we give examples: The first one is to filter.

def isExist(token: String,): Boolean = {
  implicit val db = Database.forDataSource(DB.getDataSource()).createSession()
  val users = TableQuery[Users]
  var findFlag = false
  val filterInfo = users.filter(_.token === token)
  if (filterInfo.exists.run) findFlag = true
  db.close()
  findFlag
}

The second one is to fetch.

def search(name: String): Option[JsArray] = {
  implicit val db = Database.forDataSource(DB.getDataSource()).createSession()
  val users = TableQuery[Users]
  val filterInfo = users.filter{ u => u.name.toLowerCase.like(name)}
  val result =
    if (filterInfo.exists.run) {
      var resultInfo = Json.arr()
      filterInfo.foreach{ f =>
        val tempInfo = Json.obj(
          "id" -> f.id,
          "avatar_url" -> f.basicInfo.avatarUrl)
        resultInfo ++= Json.arr(tempInfo)
      }
      Some(resultInfo)
    } else None
  db.close()
  result
}

References

Here are some useful links which greatly help me to understand and fix this problem. I recommend to read them again if you have time.

Scala Slick method I can not understand so far

Custom mapping to nested case class structure in Slick (more than 22 columns)

GitHub-Example