How can I use the new Slick 2.0 HList to overcome 22 column limit?

Question

I'm currently writing Slick code to target an old schema with two tables > 22 columns. How do I use the new HList code? I've got 2.0-M3 working fine in other respects under Scala 2.10.3. Here's the syntax I'm currently using with case classes / tuples. What would I do to use the new HLists mentioned in the docs?

  case class Joiner(
      id: Int,
      name: Option[String],
      contact: Option[String]
  )

  class Joiners(tag: Tag) extends Table[Joiner](tag, "joiner") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc, O.DBType("int(11)"))
    def name = column[Option[String]]("name", O.DBType("varchar(255)"))
    def contact = column[Option[String]]("contact", O.DBType("text"))
    def * = (id, name.?, contact.?) <> (Joiner.tupled, Joiner.unapply)
  }
  val joiners = TableQuery[Joiners]

I don't see any in the examples and only a brief mention in the newly updated docs. I'm new to Scala as well as Slick.

Preamplifier

Originally I was using O.Nullable instead of Option[String]. Corrected per Mr. Vogt's advice. – Preamplifier

Also posted here: groups.google.com/d/msg/scalaquery/xNtPT6sexXI/DgW5CQkfgaMJ Please cross-link in the future. – Micropyle

Sorry, I got a message that my Google Groups post was rejected, so I didn't think it worked. – Preamplifier

Here is my previous question when I tried to use the Scala 2.11 pre-release #19637111 – Preamplifier

NOTE: If anyone tries to follow this solution (below) as of Slick 2.0-M3 the HList code is not working -- it will take hours and gigabytes of RAM to compile more than 22 columns (it grows exponentially) groups.google.com/forum/#!topic/scalaquery/xNtPT6sexXI – Preamplifier

Just to be sure, the implementation is correct (to our knowledge). However it currently seems to trigger exponential compilation times in the Scala compiler, which bite you badly for sizes around 25 and up. We'll look into fixing that. – Micropyle

Answer 1

Definition

With Scala >= 2.10.4-RC2 (also emitted by the Slick 2.0.0 code generator):

import scala.slick.collection.heterogenous._
import syntax._
class Joiners(tag: Tag) extends Table[
    Int :: Option[String] :: Option[String] :: HNil
](tag, "joiner") {
  ...
  def * = id :: name :: contact :: HNil
}

The above leads to exponential compilation times in Scala 2.10.3 / 2.10.4-RC1. Not feasible for more than 26 columns due to extremely long compilation.

Workaround for Scala <= 2.10.3 / 2.10.4-RC1 (also emitted by the Slick 2.0.1 code generator)

import scala.slick.collection.heterogenous._
import syntax._
class Joiners(tag: Tag) extends Table[
    HCons[Int, HCons[Option[String], HCons[Option[String], HNil]]]
](tag, "joiner") {
  ...
  def * = id :: name :: contact :: HNil
}

Tested by us with 30-40 columns without problems.

There currently still seem to be a problem with occasional sporadic compilation errors in Scala 2.10.4-RC2, which looks like it will be fixed in the upcoming 2.10.4-RC3. See https://issues.scala-lang.org/browse/SI-8146

Example usage

Joiners.run.map( r => r(2) ) // Gets column contact. It's typesafe. .apply is a macro. Only works for literals not for variables as positions.

Use tuples for < 22 to be able to map them to a case class. Use HLists for > 22 without mapping to a case class (max field limit in Scala 2.10 is 22).

Also: Do NOT use O.Nullable. Use column[Option[String]] instead. It infers nullability.

Micropyle

If I don't use a case class, can I define a regular class, or do I need to list all the types in some kind of list? – Preamplifier

Also, do I still use the trailing '.?' for Option columns in the '*' projection or is that inferred? – Preamplifier

.? is inferred as well so to speak. You don't need them. Not sure what you mean regarding list all the types, but you can map your rows to anything. the concept is <> (factoryFunction, extractorFunction) – Micropyle

What do I use in place of the case class, since that is limited to 22 columns until Scala 2.11? How do I declare what used to be a case class as an HList and pass it to the table class as the type parameter? – Preamplifier

I update the example to show the type. Regarding the case class, you can simply omit it. You will not be able to access the columns by name in the result, only by position r(2). If you really want named access you can map to an ordinary class in <> by writing appropriate factory and extractor functions. – Micropyle

If you think about it, the mapping to case classes is not really that important. You DO have named access to columns during the query. And the best way to use Slick is to map your query to only the columns you really need at a certain time before you run it, in which case you can't use the case class for the whole row anyways, because you are only getting some of the columns. Does that make sense? – Micropyle

Thanks, I think I understand. I may try to implement a regular class. I found your example code in a recent presentation: slick.typesafe.com/talks/… – Preamplifier

Hi Christopher, I tried to follow your post to write a class around my Table. Well, it happens that the compiler get stuck consuming 100% of the CPU. It happens when I import scala.slick.collection.heterogenous._ I got it both on 2.10.2 and 2.10.4-RC1, I'm on OS X 10.6.8. I think there's something to investigate. – Cathycathyleen

There is a know problem with exponential compilation times, when using the :: type constructor alias in Scala <= 2.10.3. Either use the HCons constructor or Scala > 2.10.3. If you have the problem in 2.10.4-RC1 please provide a sample project that replicates it and we'll take a look. Thx – Micropyle

Thanks for your precious hint. I've further investigated on this issue and I found out that the number of columns makes the difference: it compiles in a bit long but acceptable time (depending on your patience) up to 26 columns. From 27 columns on, the compiler get stuck! Actually, it might have come to an end if I didn't hit Crtl+C, but it was no worth waiting any longer. Just for sake of clarity, my table is 46 columns... should I refactor it in tuples or such? My code is reported in the answer below – Cathycathyleen

Turns out exponential compilation times have only been fixed in 2.10.4-RC2. I updated my answer to reflect that and show a workaround for 2.10.3 / 2.10.4-RC1. – Micropyle

Added another comment regarding sporadic compilation errors in 2.10.4-RC2. – Micropyle

Answer 2

This code is to demonstrate the performance problem still affecting the compiler (it simply get stuck) in Scala v2.10.4_RC1 when the number of columns exceeds 26

import java.sql.Timestamp
import scala.slick.driver.MySQLDriver.simple._
import scala.slick.collection.heterogenous._
// **** Uncomment this ****
//import scala.slick.collection.heterogenous.syntax._  



object DealSlick {


    class Deals(tag: Tag) extends Table[
      Long :: String :: String :: Option[String] :: Option[String] :: Option[String] ::
      // urlKeywords
      Option[String] :: Option[String] :: Option[String] :: Option[String] :: Option[String] ::
      // extTags
      Option[String] :: Option[String] :: Option[String] :: Option[String] :: Option[String] :: 
      // currency
      Option[String] :: Option[String] :: 
      // price
      Option[Double] :: Option[Double] :: Option[Double] :: Option[Double] ::
      // extStatus
      Option[String] :: Option[String] :: Option[Int] :: Option[Int] ::

     /* If you add more columns the compiler get stuck in a never-ending
      * compilation possibly related to 
      * https://github.com/slick/slick/issues/577
      */

     // endAt
     Option[Timestamp] :: /*Option[Timestamp] :: Option[Timestamp] :: Option[Timestamp] ::
     // timeZoneOffset
     Option[Int] :: Option[String] :: Option[Timestamp] :: Option[String] ::
     // locationName
     Option[String] :: Option[String] :: Option[String] :: Option[String] ::
     // city
     Option[String] :: Option[String] :: Option[String] :: Option[String] :: Option[String] :: 
     // latitude
     Option[Double] :: Option[Double] :: 
     // merchantTitle
     Option[String] :: */
     // End of list
     HNil
     ](tag, "deal") {
     def                id = column[Long]("id", O.PrimaryKey)
     def          siteName = column[String]("partner_site_name", O.NotNull)
     def        siteDomain = column[String]("partner_site_domain", O.NotNull)
     def    localeLanguage = column[Option[String]]("deal_language") 
     def     localeCountry = column[Option[String]]("deal_country") 
     def             extId = column[Option[String]]("deal_ext_id") 
     def       urlKeywords = column[Option[String]]("deal_url_keywords") 
     def          keywords = column[Option[String]]("deal_keywords") 
     def     extCategories = column[Option[String]]("deal_ext_categories") 
     def      categoryText = column[Option[String]]("deal_category_text") 
     def          coverage = column[Option[String]]("deal_coverage") 
     def           extTags = column[Option[String]]("deal_ext_tags") 
     def             title = column[Option[String]]("deal_title") 
     def       description = column[Option[String]]("deal_description")
     def          extImage = column[Option[String]]("deal_ext_image") 
     def               url = column[Option[String]]("deal_url") 
     def          currency = column[Option[String]]("deal_currency") 
     def       currencySym = column[Option[String]]("deal_currency_sym") 
     def             price = column[Option[Double]]("deal_price") 
     def            saving = column[Option[Double]]("deal_saving") 
     def          discount = column[Option[Double]]("deal_discount") 
     def            dvalue = column[Option[Double]]("deal_value") 
     def         extStatus = column[Option[String]]("deal_ext_status") 
     def            status = column[Option[String]]("deal_status") 
     def           soldQty = column[Option[Int]]("deal_sold_qty") 
     def           leftQty = column[Option[Int]]("deal_left_qty") 
     def             endAt = column[Option[Timestamp]]("deal_end_at") 
 /*    def          endAtUtc = column[Option[Timestamp]]("deal_end_at_utc") 
     def         expiresAt = column[Option[Timestamp]]("deal_expires_at") 
     def      expiresAtUtc = column[Option[Timestamp]]("deal_expires_at_utc") 
     def    timeZoneOffset = column[Option[Int]]("time_zone_offset") 
     def      timeZoneName = column[Option[String]]("time_zone_name")
     def       timeGrabbed = column[Option[Timestamp]]("time_grabbed") 
     def  timeRemainingStr = column[Option[String]]("time_remaining_str") 
     def      locationName = column[Option[String]]("location_name")
     def           address = column[Option[String]]("location_address") 
     def            street = column[Option[String]]("location_street") 
     def        postalCode = column[Option[String]]("location_postalcode")
     def              city = column[Option[String]]("location_city") 
     def          province = column[Option[String]]("location_province") 
     def            region = column[Option[String]]("location_region") 
     def             state = column[Option[String]]("location_state") 
     def           country = column[Option[String]]("location_country") 
     def          latitude = column[Option[Double]]("location_latitude") 
     def         longitude = column[Option[Double]]("location_longitude") 
     def     merchantTitle = column[Option[String]]("merchant_title") 

*/
def * = (id :: siteName :: siteDomain :: localeLanguage :: localeCountry :: extId :: 
         urlKeywords :: keywords :: extCategories :: categoryText :: coverage :: 
         extTags :: title :: description :: extImage :: url ::
         currency :: currencySym :: price :: saving :: discount :: dvalue ::
         extStatus :: status :: soldQty :: leftQty ::
         endAt :: /*endAtUtc :: expiresAt :: expiresAtUtc ::
         timeZoneOffset :: timeZoneName :: timeGrabbed :: timeRemainingStr ::
         locationName :: address :: street :: postalCode :: 
         city :: province :: region :: state :: country ::
         latitude :: longitude ::
         merchantTitle :: */
         HNil )  
   }

}

** UPDATE **

After updating to Scala 2.10.4-RC2, the compiler goes few steps further in the compilation process, but it get stuck again:

Here's the compiler output when defining only few table columns

[info] [loaded class file /Users/max/.ivy2/cache/com.typesafe.slick/slick_2.10/jars/slick_2.10-2.0.0.jar(scala/slick/backend/DatabaseComponent.class) in 1ms]

[info] [loaded class file /Users/max/.ivy2/cache/com.typesafe.slick/slick_2.10/jars/slick_2.10-2.0.0.jar(scala/slick/lifted/ShapedValue.class) in 2ms]

[info] [loaded package loader util in 2ms] This output never get printed on the screen when using more then 26 columns

Cathycathyleen

This may have been fixed since RC1, though I'm not sure. I tried to track down the commit and bug tracking message I saw it discussed in but could not. There are fixes if you want to patch Scala or Slick yourself. – Preamplifier

Christopher, thanks for the hint, but I've tried Scala v2.10.4-RC2 already, and I must say I just noticed the compiler is no longer blocking on that point, but it get stuck on a further step. You can use the code I posted for a test if you and your team think it's worth to investigate on this issue. – Cathycathyleen