Add this to your build.scala file:
resolvers ++= "chrisstucchio" at "http://maven.chrisstucchio.com/",
libraryDependencies ++= "com.chrisstucchio" %% "tiramisu" % "0.19"
Tiramisu is a small library for constructing prepared SQL statements in an intuitive manner. Specifically, you can construct SQL queries in a manner that looks syntactically like string concatenation:
val query = """SELECT * FROM user_profiles
WHERE user_id = (SELECT id FROM users WHERE user_token = """.sqlP() + userToken.sqlV + ");".sqlP()
This format is handy for programmatically creating complex queries. See the section on Polymorphic References for an example of where this comes in very handy.
SQL injection errors are caused by the fact that prepared statements are complex to write, and string concatenation is easy. The purpose of Tiramisu is to make prepared statements as easy to write as code which builds SQL by string concatenation.
Import Tiramisu, together with a collection of ParameterInjector objects (the details of which depend on your specific SQL driver):
import com.chrisstucchio.tiramisu._
import com.chrisstucchio.tiramisu.ParameterInjector._
The .sqlP method creates a Query object from a string:
"SELECT * FROM foo ".sqlP()
It can also be used to assign parameters:
"SELECT * FROM foo WHERE slug=? AND token=?".sqlP(foo.slug, foo.token)
The .sqlV method turns many objects into a SqlParameter object, which can laso be included in queries by concatenation:
"SELECT * FROM bar WHERE slug=".sqlP() + slug.sqlV + " AND token=".sqlP() + token.sqlV
Note that although this looks like string interpolation, we are actually parameterizing the slug and token variables. The generated SQL looks something like this:
SELECT * FROM bar WHERE slug = ? AND token = ?
As a convenience, Query objects have a set of convenience methods which enable us to avoid repetitive boilerplate like " AND ".sqlP():
val offset: Long = 10
val limit: Long = 5
val tokenClause = "token=?".sqlP(token)
val slugClause = "slug=?".sqlP(slug)
"SELECT * FROM foo ".sqlP() WHERE tokenClause AND slugClause LIMIT limit OFFSET offset
A ParameterInjector[T] object knows how to insert an object of type T into a position in a PreparedStatement. Apart from the basic types supported by JDBC, this is generally a property of your specific SQL driver.
The method t.sqlV will only work if an implicit ParameterInjector[T] is present. This grants a degree of type safety, making it impossible to inject objects of a type that JDBC cannot handle into a prepared statement. A custom ParameterInjector can also be created to handle custom types. For example, Tiramisu comes with an implicit converter from a joda DateTime object to a java.sql.Timestamp object:
object DateInjector extends ParameterInjector[DateTime] {
def setParam(position: Int, value: DateTime, statement: PreparedStatement) = statement.setTimestamp(position, new Timestamp(value.getMillis))
}
The package com.chrisstucchio.tiramisu.sqlref provides support for Polymorphic Reference types. A polymorphic reference is a product type which uniquely identifies a row in a SQL table. For example, consider the table:
CREATE TABLE sites (
id BIGINT PRIMARY KEY NOT NULL,
domain VARCHAR(256) NOT NULL UNIQUE,
uuid UUID NOT NULL UNIQUE
)
The polymorphic ref type in scala might look like this:
sealed trait SiteRef
case class SiteRefByDomain(domain: String) extends SiteRef
case class SiteRefByUUID(uuid: UUID) extends SiteRef
The polymorphic ref typeclass would then be:
object SiteRefPolymorphicRefType[SiteRef] {
val table = SqlTable("sites", "id")
def pkClause(ref: SiteRef): Query = ref match {
case SiteRefByDomain(domain) => "(SELECT id FROM sites WHERE domain=?)".sqlP(domain)
case SiteRefByUUID(uuid) => "(SELECT id FROM sites WHERE uuid=?)".sqlP(uuid)
}
}
You can then use this typeclass as follows:
val query = "SELECT * FROM content WHERE content.site_id = ".sqlP() + siteRef.pk
The val siteRef can be either a SiteRefByDomain or a SiteRefByUUID. The generate SQL will then be:
"SELECT * FROM content WHERE content.site_id = (SELECT id FROM sites WHERE domain=?)"
or
"SELECT * FROM content WHERE content.site_id = (SELECT id FROM sites WHERE uuid=?)"
It might be suggested that polymorphic references are a problem for caching, and this suggestion is correct. However, this problem can be solved for functions which return a complete object, i.e. an object from which all polymorphic references can be constructed. We have a typeclass which handles this:
trait ConstructsAllRefs[-B,R] {
def allRefs(b: B): NonEmptyList[R]
}
The allRefs function returns a NonEmptyList because it doesn't make sense for an object to have no reference.
Given this typeclass, we have a set of helper traits which can be mixed into any cache. The first trait is the CacheStore[K,V] which handles the details of putting objects into the cache:
trait CacheStore[K,V] {
protected def putInternal(k: K, v: V)
def put(k: K, v: V): Unit = putInternal(k,v)
def invalidate(k: K): Unit
}
Note the absence of a get method.
For caches of complete rows, we then have the FullRowSqlCache:
trait FullRowSqlCache[K,V] extends CacheStore[K,V] {
protected val refConstructor: ConstructsAllRefs[VP,K]
def putObj(v: V): Unit = refConstructor.allRefs(v).foreach(k => putInternal(k,v))
def invalidateObj(v: V): Unit = refConstructor.allRefs(v).foreach(invalidate)
}
If you provide this trait a ConstructsAllRefs[V,K] instance in addition to the standard CacheStore methods, then you have the power to invalidate all references for the object.
Finally, there are traits which handle retrieving objects from the cache. The simplest is SqlCache, which provides the method get(k: K): Option[V]. A more interesting one is SqlFunctorCache:
trait SqlFunctorCache[M[_],K,V] extends CacheStore[K,V] {
protected implicit val mFunctor: Functor[M]
def get(k: K): Option[M[V]]
def getCertain(k: K)(fallback: =>M[V]): M[V] = {
get(k).getOrElse({
val result: M[V] = fallback
result.map( r => put(k,r) )
result
})
}
}
This is useful for retrieval methods which are wrapped in a functor. The standard example of this is a cache which returns a Future, rather than a value:
trait SqlFutureCache[K,V] extends SqlFunctorCache[Future,K,V] {
protected implicit val executionContext: ExecutionContext
protected implicit object mFunctor extends Functor[Future] {
def map[A, B](fa: Future[A])(f: A => B): Future[B] = fa.map(f)
}
}
This package contains simple traits that make it easy to build your own implementation java.sql.* objects. For example, to build an implementation of java.sql.Connection which measures how much time it spends running methods:
class TimingConnection(protected val conn: java.sql.Connection) extends ConnectionWrapper {
var timeSpent: Long = 0
override protected def methodWrap[t](f: =>T) = {
val start = System.currentTimeMillis()
val result = f
timeSpent += (System.currentTimeMillis() - start)
result
}
}
The ConnectionWrapper trait handles all the boilerplate necessary to implement a java.sql.Connection.
def close(): Unit = methodWrap { conn.close() }
def commit(): Unit = methodWrap { conn.commit() }
def createArrayOf(x: String,y: Array[Object]): java.sql.Array = methodWrap { conn.createArrayOf(x,y) }
...