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.

Originally I was using O.Nullable instead of Option[String]. Corrected per Mr. Vogt's advice. — sventechie, Dec 13 '13 at 14:16
Also posted here: https://groups.google.com/d/msg/scalaquery/xNtPT6sexXI/DgW5CQkfgaMJ Please cross-link in the future. — cvogt, Dec 14 '13 at 04:05
Sorry, I got a message that my Google Groups post was rejected, so I didn't think it worked. — sventechie, Dec 16 '13 at 14:27
Here is my previous question when I tried to use the Scala 2.11 pre-release http://stackoverflow.com/questions/19636611/how-can-i-handle-a-22-column-table-with-slick-using-nested-tuples-or-hlists?rq=1 — sventechie, Dec 16 '13 at 14:41
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) https://groups.google.com/forum/#!topic/scalaquery/xNtPT6sexXI — sventechie, Dec 18 '13 at 13:58
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. — cvogt, Dec 18 '13 at 19:07

cvogt · Accepted Answer · 2014-02-06T01:34:02.483

10

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.

edited Feb 06 '14 at 01:34

answered Dec 12 '13 at 22:46

cvogt

11,260
30
46

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? – sventechie Dec 13 '13 at 13:47
Also, do I still use the trailing '.?' for Option columns in the '*' projection or is that inferred? – sventechie Dec 13 '13 at 13:53
.? 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)` – cvogt Dec 13 '13 at 18:31
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? – sventechie Dec 16 '13 at 14:40
3

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. – cvogt Dec 16 '13 at 18:51
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? – cvogt Dec 16 '13 at 18:54
Thanks, I think I understand. I may try to implement a regular class. I found your example code in a recent presentation: http://slick.typesafe.com/talks/2013-12-04_So-Slick-SUG-Berlin-Brandenburg/2013-12-04_So-Slick-SUG-Berlin-Brandenburg.pdf – sventechie Dec 16 '13 at 20:39
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. – Max Feb 02 '14 at 23:24
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 – cvogt Feb 03 '14 at 02:31
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 – Max Feb 03 '14 at 21:37
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. – cvogt Feb 06 '14 at 01:26
Added another comment regarding sporadic compilation errors in 2.10.4-RC2. – cvogt Feb 06 '14 at 01:34

Max · Answer 2 · 2014-02-06T21:12:48.397

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

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. — sventechie, Feb 04 '14 at 00:44
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. — Max, Feb 06 '14 at 18:55

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

2 Answers2

UPDATE

Linked

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

2 Answers2

** UPDATE **

Linked

UPDATE