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.)

score 3 · Accepted Answer · answered Jun 21 '12 at 12:21

3

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...

answered Jun 21 '12 at 12:21

virtualeyes

11,147
6
56
91

Thank you! I think that using database-native enums is just more overhead than I want to deal with. I do still want the enumeration enforced at the database level. However, I think I will achieve this by switching to a VARCHAR type... and adding a constraint trigger on the table which will block INSERT's and UPDATE's with invalid values. – Steve Perkins Jun 21 '12 at 16:13
Yeah, DB enums, not sold on that, how would you create an html select/radio/checkbox list? You'd have to query the DB. With Java enums you can produce html elements without querying the DB (or at least only querying for the target record that contains the enum value you'd be getting anyway), and, if you create a type mapper enum-string, can enforce the enum value constraint in DAO persistence operations. DB trigger is the safest, of course, but then you have to handle an exception at application layer that would never occur if you always persist via DAO layer in the first place. Fun, fun ;-) – virtualeyes Jun 21 '12 at 17:57
Well, the nice thing about database-native enums is that you still have enforcement when the database is used outside of Java. I've spent most of my career working for large companies, and I'm just used to assuming that a database might be used by Java web apps, .NET desktop apps, maybe some Perl or Python cron jobs, etc. So my instinct is to stay away from relying on the application tier to enforce constraints in the persistence tier. I guess if you're working on a small app where nothing else will EVER write to the database, then you can put more responsibility on the app's shoulders. – Steve Perkins Jun 21 '12 at 20:49
Oh, and if you do use database-native triggers, the values still come across as Strings (or whatever you cast them to) in your JDBC calls. You don't have to do any fancy translation to get the user-friendly values, for populating HTML selects, etc. – Steve Perkins Jun 21 '12 at 20:50
BTW... in my first comment, I meant "check constraint" rather than "constraint trigger". This type of enforcement can be baked into the initial "CREATE TABLE" statement, no need to add a separate trigger. – Steve Perkins Jun 21 '12 at 20:51
@StevePerkins, yes, in my current use case, Scala is the only persistence mechanism, so constraints live in the application layer, as does the db schema itself -- application runs the show. Safe to say, as a travelling surfer/programmer, enterprise is not in my vocabulary, although Scala adds a virtual coat & tie of sorts ;-) – virtualeyes Jun 21 '12 at 21:29
I share the suspicion of database ENUM—it never seemed relational to me (nor SET). If you set up a table for the domain of the enumeration you can use a foreign key constraint to enforce it, of course, and you can always obtain the set of valid values from that parent table. Plus the ability exists to add attributes to each value—an ENUM lacks this. The credit card example suggests that this can be useful. – qu1j0t3 Aug 15 '12 at 03:33

Mapping custom types in the ScalaQuery O/R framework

1 Answers1