QueryResultRow conversion for Product types

[salamonpavel]

Background

At the moment it's difficult to create test assertions of the db results given the fact that one needs to extract fields from the QueryResultRow object. I propose to enhance capabilities of the library to allow for automatic instance creation for product types.

Feature

Balta will be able to create instances of product types from QueryResultRow

Example

class GetActorByIdTest extends DBTestSuite {

  test("Get actor by id - actor with given id exists EXISTING") {
    val expectedActor = Actor(actorId = 1, firstName = "Malin", lastName = "Akerman")

    function("runs.get_actor_by_id")
      .setParam("i_actor_id", 1)
      .execute { queryResult =>
        assert(queryResult.hasNext)
        val row = queryResult.next()
        assert(row.getInt("actor_id").get == expectedActor.actorId)
        assert(row.getString("first_name").get == expectedActor.firstName)
        assert(row.getString("last_name").get == expectedActor.lastName)
        assert(!queryResult.hasNext)
      }
  }

  import za.co.absa.balta.classes.DefaultColumnNameMappers.SnakeCaseForCamelCaseMapper

  test("Get actor by id - actor with given id exists PROPOSAL") {
    val expectedActor = Actor(actorId = 1, firstName = "Malin", lastName = "Akerman")

    function("runs.get_actor_by_id")
      .setParam("i_actor_id", 1)
      .execute { queryResult =>
        assert(queryResult.hasNext)
        val row = queryResult.next()
        val actualActor = row.toProductType[Actor]
        assert(expectedActor == actualActor)
        assert(!queryResult.hasNext)
      }
  }

}

Proposed Solution for Discussion

An example of possible solution (needs to be tested for various scenarios, only tested on the test example above)

package za.co.absa.balta.classes

import org.postgresql.util.PGobject

import java.sql.{Date, ResultSet, Time}
import java.time.{Instant, OffsetDateTime}
import java.util.UUID

import scala.reflect.runtime.universe._
import scala.reflect.runtime.currentMirror

class QueryResultRow private[classes](val resultSet: ResultSet) extends AnyVal {

  // existing code not included

  def toProductType[T <: Product : TypeTag](implicit mapper: ColumnNameMapper): T = {
    val tpe = typeOf[T]
    val classSymbol = tpe.typeSymbol.asClass
    val classMirror = currentMirror.reflectClass(classSymbol)
    val constructorSymbol = tpe.decl(termNames.CONSTRUCTOR)
    val defaultConstructor =
      if (constructorSymbol.isMethod) constructorSymbol.asMethod
      else {
        val ctors = constructorSymbol.asTerm.alternatives
        ctors.map(_.asMethod).find(_.isPrimaryConstructor).get
      }
    val constructorMirror = classMirror.reflectConstructor(defaultConstructor)
    val params = defaultConstructor.paramLists.flatten.map { param =>
      val name = param.name.decodedName.toString
      val columnName = mapper.map(name)
      val value = resultSet.getObject(columnName)
      if (resultSet.wasNull()) null else value
    }
    constructorMirror(params: _*).asInstanceOf[T]
  }

}

trait ColumnNameMapper {
  def map(name: String): String
}

object DefaultColumnNameMappers {

  // when database columns use the same names as product type
  implicit object IdentityMapper extends ColumnNameMapper {
    def map(name: String): String = name
  }

  // when database columns use upper case and product type uses lower case
  implicit object UpperCaseForLowerCaseMapper extends ColumnNameMapper {
    def map(name: String): String = name.toUpperCase
  }

  // when database columns use lower case and product type uses upper case
  implicit object LowerCaseForUpperCaseMapper extends ColumnNameMapper {
    def map(name: String): String = name.toLowerCase
  }

  // when database columns use snake_case and product type uses camelCase
  implicit object SnakeCaseForCamelCaseMapper extends ColumnNameMapper {
    def map(name: String): String = {
      val regex = "([a-z])([A-Z]+)".r
      val replacement = "$1_$2"
      regex.replaceAllIn(name, replacement).toLowerCase
    }
  }

  // when database columns use kebab-case and product type uses camelCase
  implicit object KebabCaseForCamelCaseMapper extends ColumnNameMapper {
    def map(name: String): String = {
      val regex = "([a-z])([A-Z]+)".r
      val replacement = "$1-$2"
      regex.replaceAllIn(name, replacement).toLowerCase
    }
  }

}

[lsulak]

I didn't try it, but I like the proposal!