A ScalaQuery Update
September 27th, 2009 | In Scala, ScalaQuerySince my last post about ScalaQuery one month ago I have wasted quite some time trying to implement the OptionMapper type (which is used to make operations on columns interoperable between base types and Option types) in a way which does not require 2^n implicit functions and 2*n+2 type parameters for operations with n operands. The composable OptionMapper itself is not hard to write (see NewOptionMapper here) but it requires more type fidelity than ScalaQuery offers at the moment: Either Projections need to preserve the subtypes of Columns they are storing, or the required types have to be reconstructed from implicit values where they are needed. You can find my failed attempts in the projection-types and new-option-mapper branches. The first one might actually work once Scala bug #2346 is resolved. The second one would require a much more powerful type inferencer. For now, I just added an OptionMapper3 for 3 parameters (which is needed by the BETWEEN operator).
There are also some interesting new features in ScalaQuery:
Mapped Projections
You can now create a bidirectional mapping of a Projection with the <> operator. This is especially useful for a table’s “*” projection. The <> operator takes a function from the projection tuple type T (either as a tuple or as multiple parameters) to the mapped type R, and a function from R back to Some[T]. The asymmetry with the unnecessary Some wrapper is deliberate, matching exactly the apply und unapply methods of a case class. For example, here is a simple User class and table:
case class User(first: String, last: String)
object Users extends Table[User]("users") {
def first = column[String]("first", O NotNull)
def last = column[String]("last", O NotNull)
def * = first ~ last <> (User, User.unapply _)
}
Whenever you perform an insert, update or query directly on such a table (instead of a projection of individual columns), you can use the mapped type:
Users.insert(User("Stefan", "Zeiger"))
val someUsers: List[User] = Users.where(_.first startsWith "S").list
Finders
The new convenience method Table.createFinderBy allows you to create a simple finder query template which selects values from a table by matching on a single column, e.g.:
val findUserByFirstName = Users.createFinderBy(_.first)
This is equivalent to:
val findUserByFirstName = for {
first <- Parameters[String]
u <- Users if u.first is first
} yield u
Updatable ResultSets
JDBC allows you to modify a ResultSet which was created with the CONCUR_UPDATABLE result set concurrency. ScalaQuery now offers a high-level interface to this functionality with the mutate method, e.g.:
val q1 = for(u <- Users if u.last.is("Simpson") || u.last.is("Bouvier")) yield u
q1.mutate { m =>
if(m()._3 == "Bouvier") m() = m().copy(_3 = "Simpson")
else if(m()._2 == "Homer") m.delete()
else if(m()._2 == "Bart") m.insert((None, "Lisa", "Simpson"))
}
Statement Options
The new ResultSetType, ResultSetConcurrency and ResultSetHoldability classes allow you to request the corresponding JDBC options when ScalaQuery creates a PreparedStatement, e.g.:
myDB withSession {
ResultSetType.ScrollInsensitive {
// All database calls in this block use TYPE_SCROLL_INSENSITIVE
}
}
Or if you don't want to use the implicit threadLocalSession:
myDB withSession { s1 =>
ResultSetType.ScrollInsensitive(s1) { s2 =>
// All database calls on s2 use TYPE_SCROLL_INSENSITIVE
}
}
The default for all three options is Auto which gives you values suitable for the invoker method you are calling (e.g. ResultSetConcurrency.Updatable for mutate and ResultSetConcurrency.ReadOnly for all other methods).
Build System
I have rewritten most of the test classes as proper unit tests (using JUnit 4) with assertions. I'd like to investigate ScalaTest and specs further (possibly together with ScalaCheck) in the future to replace JUnit but the binary incompatibilities between different Scala 2.8 snapshot builds would only complicate the build process right now. If you build with sbt, you still need to download a suitable Scala 2.8 snapshot yourself and set the paths in the properties because the build is done in forking mode. This also means that you cannot use the "test" command to run the unit tests from sbt at the moment.
H2 and JUnit are declared as test dependencies in the sbt project definition and the Eclipse build path refers to the local copies in lib_managed. If you want to build ScalaQuery with Eclipse (as I usually do), just run "sbt update" once to pull in the required JARs.
I am using FreeMarker templates and the FMPP tool to create repetitive code for the Projection and Parameters classes. This is not yet integrated into the sbt build process. You can use the supplied Eclipse launcher instead (after downloading FMPP and pointing the launcher to the correct path). The generated sources are checked in, so you only need to do this if you want to change the templates and rebuild the Scala sources.
October 10th, 2009 at 10:44 am
I just started using this for a long term project.
October 12th, 2009 at 4:03 pm
Can I get from Maven for this?
October 12th, 2009 at 6:49 pm
Some forks of ScalaQuery at github contain Maven POMs but I haven’t tried building it with them myself.
If you just want to use it as a dependency in Maven, you can reference it as com.novocode;scala-query;1.0.0-SNAPSHOT from http://scala-tools.org/repo-snapshots/. The current snapshot is built against Scala 2.8.0-20091006.003811-+.
October 17th, 2009 at 8:14 am
I’m trying to run SimpleTest test class against an oracle database. I’m getting “java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended” on the for(i <- populateUsers) println(" "+i) statement.
What is the best way to troubleshoot this? The USERS table does get created, so I know I'm getting a connection and at least one statement runs successfully.
October 17th, 2009 at 8:16 am
Are comments moderated? Just posted something and it did not show up…
October 17th, 2009 at 8:18 am
Guess it’s not moderated. Reposting:
I’m getting a “ORA-00933: SQL command not properly ended” when running the SimpleTest class against an oracle DB. The error occurs on the for(i <- populateUsers) println(" "+i) statement.
The USERS table is getting created, so I know I'm getting a connection and at least the first statement works.
What is the best way to troubleshoot this?
October 17th, 2009 at 12:07 pm
@JL Huynh: Your comments got eaten by the spam filter. I’ve restored them now.
I don’t think Oracle likes multiple statements being concatenated to one statement “s1; s2″ as in populateUsers. If you want a portable solution, you’ll have to send the statements separately. In the future, ScalaQuery may support statement batches (if the JDBC driver does) to avoid the overhead of sending multiple requests in a portable way.
For an Oracle-only solution, “BEGIN s1; s2; END” should work IIRC.
October 18th, 2009 at 3:12 am
Thank you. Breaking out in individual statements did solve the problem.
Batches inserts would be useful for the kinds of things I do.
October 18th, 2009 at 4:16 pm
Trying to insert rows into an oracle table using the syntax:
val ins1 = (Users.id ~ Users.first ~ Users.last).insert((userSequence.next, “Homer”, Some(”Simpson”)))
Users.id is defined as: column[Int](”userID”, O PrimaryKey, O NotNull)
And userSequence as: val userSequence = Sequence[Int](”user_seq”) start 1 inc 1
The does not compile because userSequence.next is not an Int.
How do I use the combinator syntax to get it to translate to:
insert into users (id, first, last) values (user_seq.nextval, “Homer”, “Simpson”);
October 18th, 2009 at 5:24 pm
Ah, I didn’t think of such cases with computed columns. I suppose they could naturally fall out of an implementation for INSERT…FROM statements which is planned for the future.
The special case of simulating an auto_increment column in Oracle should ideally be handled by ScalaQuery directly. It offers an AutoInc flag among the basic column options, so it should be supported for all drivers. We could automatically create a sequence (together with the table) and get a value from it when an INSERT is done without the AutoInc column or with a NULL value for it.
October 18th, 2009 at 10:10 pm
May be a new option O AutoIncSeq “sequenceName” would work well. I usually need to interact with existing tables that already have particular sequence names assigned to them.
October 21st, 2009 at 9:39 am
Hi,
I use this for two projects now, one is a straight Scala project application the other is using Liftweb.
Liftweb http://liftweb.net/ has some database mapping classes some simular but not as good as this one database mapping features. Do you think that integration of this with liftweb would be useful?
Thanks, Philip
October 21st, 2009 at 9:09 pm
@JL Huynh: Agreed, there needs to be an option for specifying the sequence name. If it’s missing, a name should be generated automatically from the table and column names.
@philip andrew: I read the lift tutorial and experimented with the code, but I haven’t gotten around to building a real application with it. I’d like to get started with that after getting ScalaQuery 1.0 out of the door, and try to build the database connectivity both with Lift’s ORM and with ScalaQuery. What is your experience with this combination so far?
October 23rd, 2009 at 11:50 am
Just came across this via DZone http://www.sts.tu-harburg.de/people/mi.garcia/ScalaQL/