Mapping custom types in the ScalaQuery O/R framework

Question

In his comparison of ScalaQuery and Squeryl, Stefan Zeiger (author of ScalaQuery) says in the third bullet-point:

ScalaQuery comes with support for a basic set of JDBC types and can be extended with DBMS- or application-specific types.

I have been unable to find examples or explanations for how to actually do this, however. I am trying to write a ScalaQuery schema for a Postgres database, in which some columns are of custom enum types that I created within Postgres.

For example, I have a enum type called gender, with possible values male and female. This is NOT a Java enum, persisted to the database as an integer. Rather, it is a custom Postgres type defined within the DBMS. Postgres stores those with a special 4-byte data structure rather than as a primitive.

How could I incorporate Postgres columns of type gender into a ScalaQuery schema?

(I would also appreciate comments, if you think a different strongly-typed O/R approach would be better suited for the task. I have already looked at Squeryl, and do not believe it can handle custom types unless they are persisted as primitives in the DBMS.)

Answer 1

import org.scalaquery.ql.{MappedTypeMapper => Mapper}

object TypeMapper {

  type Stamp = java.sql.Timestamp

  val joda2Stamp = 
    Mapper.base[JodaTime, Stamp](
      dt => new Stamp(dt.getMillis), 
      ts => new JodaTime(ts.getTime) )
}

and then, for example, in your DAO (or wherever you run queries), use it:

import TypeMapper._
implicit val j2Stamp = joda2Stamp // type conversion automatically

You'll need to experiment to achieve the same for Enums and PostGres' enum storage type. I tend not to bother, preferring to go with Java Enums and storing as primitive type.

For example:

public enum CardType implements ILabel {
  V("Visa"),
  M("MasterCard"),
  D("Discover"),
  A("American Express");

  private CardType(String label) { this.label = label; }
  public String getLabel() { return this.label; }
  final String label;

  public static List<String> asList() {
    return EnumHelper.asList(CardType.class);
  }

  public static Map<String,String> asMap() {
    return EnumHelper.asMap(CardType.class);
  }
}

and then store as char(1) in DB a la Orders.insert(cardType = cardType.toString), or you could create a type mapper Enum-String conversion and omit the enum.toString on inserts...