Extending SQL dialect functions to Kotlin/Exposed

Exposed is a light-weight SQL library for Kotlin uses underlying driver JDBC for communication with the database. A database can be accessed using typesafe SQL DSL(Domain Specific Language) and database access objects. Currently, it supports databases like MySQL, Oracle, SQLite, SQL Server, H2, PostgreSQL and has an implementation of most common functionality across different databases.

In many scenarios, you need to use SQL dialect functions in your application which are not part of the exposed library. So to use them you need to implement them in your application by extending the exposed library.

Now we will see how to extend these extra functions to the exposed library with an example. Let’s say you want to use ‘FROM_UNIXTIME’ function of MySQL dialect in your query.

Def: FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.

We often encounter a situation where datetime parameter in URLs is specified as Unix time rather than date string format. So to cater such URLs we need to convert given Unix time to MySQL Date/DateTime while querying the underlying database.

Let’s say we have a movies database. there is an URL for getting details of all film released after the given time. So to get the result for requested URL from database typical query would look like this -

SELECT id, title, description, ratings, length, released_on

FROM films

WHERE released_on >= FROM_UNIXTIME(unixTimeParam);

Exposed doesn’t provide this function as part of the library. So we have to provide our implementation of such functions in our application. We need to extend abstract class Function<T> of exposed.

1 abstract class Function<T>(override val columnType: IColumnType) 
2   : ExpressionWithColumnType<T>()

Firstly create a FromUnixTime class for the function we want to implement which extends Function<T> abstract class.

As we extend the class we need to override toSQL function as well as columnType property.

FROM_UNIXTIME function needs unix time as an input parameter, so here we are passing unixTimeParam of type Long in default constructor of our class.

toSQL function takes queryBuilder as input parameter and returns the formatted string. In our case, it returns a string which is MySQL representation of FromUnixTime class.

1 override fun toSQL(queryBuilder: QueryBuilder): String
2       = "UNIX_TIMESTAMP(${expr.toSQL(queryBuilder)})"

Note: Here we have not used queryBuilder parameter but it needs when constructor accepts the expression as input. It provides MySQL string representation of that expression.

Also, columType property is overridden to DateColumnType(true) as FROM_UNIXTIME function returns date/datetime. In exposed, If ‘true’ value is passed to the DateColumnType constructor means column type is of DateTime otherwise Date.

1 override val columnType: IColumnType = LongColumnType()

Below is the full snippet of our defined class -

1 class FromUnixTime(private val unixTimeParam: Long)
2   : Function<DateTime>(DateColumnType(true)) {
3   override fun toSQL(queryBuilder: QueryBuilder): String
4       = "FROM_UNIXTIME($unixTimeParam)"
5   override val columnType: IColumnType = DateColumnType(true)
6 }

Finally, Exposed DSL query to get all the films after the given time will look like -

1 object FilmsTable: LongIdTable("films") {
 2   val title = varchar("title", 50)
 3   val description = varchar("description", 500)
 4   val ratings = varchar("ratings", 250)
 5   val length = integer("length")
 6   val releasedOn = datetime("released_on")
 7   val createdOn = datetime("created_on").clientDefault {
 8     DateTime.now()
 9   }
10   val lastUpdated = datetime("last_updated").nullable()
11   val version = integer("version")
12 }1 FilmsTable
 2     .slice(
 3         FilmsTable.id,
 4         FilmsTable.title,
 5         FilmsTable.description,
 6         FilmsTable.ratings,
 7         FilmsTable.length,
 8         FilmsTable.releasedOn
 9     )
10     .select {
11       GreaterEqOp(FilmsTable.releasedOn,
12           FromUnixTime(unixTimeParam))
13     }

Like UNIX_TIMESTAMP, you can implement other SQL dialect functions.