The big noteable feature of this library is it's platform agnostic. SQL is SQL whether calling from native, nodejs, or jvm. To enable this we broke the part that creates a query into a seperate module. The executor takes in the constructed queries and passes them in.

This will be covering the async executor developed. Which is not targeting the typical jdbc.

Templating An Executor

When you think of an executor there are two main operations.

  • Executing a query, and getting back a platform specific result.
  • Unpacking the result into a hash map, or a data class.
  • Connecting / disconnecting or maintaning a connection pool.

Looking at the base line we have the above several simple operations to perform.

Configuration

Starting with the configuration. Most executors have a base requirement to connect to a database. The exception being somethings like SQLite. You need a host name, user and password to connect to. This base

interface IExecutorConfig {
    val databaseHost: String
    val database: String
    val databaseUser: String
    val databasePassword: String
    val databasePort: Int
}

Async Executor


interface IAsyncExecutor<C : IExecutorConfig, RSLT> {
    val logger: KLogger
    var connected: Boolean
    val preparedPlaceHolder: PreparedPlacedHolder
    suspend fun connect(config: C): Boolean
    suspend fun disconnect(): Boolean


    suspend fun <D : IDatabase, R : ADatabaseRecord, T : ITable<D, R>, IN, TargetClass : Any, RSP>
            fetchAsync(
        query: IQuery<D, R, T>,
        responseType: ExpectedResponses,
        block: suspend (IN) -> RSP
    ): AsyncDBResponse<D, R, T, C, RSLT, RSP>

    suspend fun <D : IDatabase, R : ADatabaseRecord, T : ITable<D, R>, IN, TargetClass : Any, RSP>
            preparedFetchAsync(
        preparedQuery: IPreparedQuery<D, R, T>,
        responseType: ExpectedResponses,
        values: BoundArguments<D,R,T>,
        block: suspend (IN) -> RSP
    ): AsyncDBResponse<D, R, T, C, RSLT, RSP>
}

Starting in we take in two type parameters.

  • C Is the configuration which must be based off IExecutorConfig
  • RSLT is the generic response type of the database.

The result varies based off the core engine / library. In JaSync it will be a QueryResult in Vertx it is a RowSet<Row>. By making this a generic we allow us to easily swap in different database libraries.

Then we have the two base functions. fetchAsync and preparedFetchAsync. Both are non blocking, the prepared takes in a prepared query, while the other takes in a dynamic query.

The parameters are generally the same with only values being on the prepared query.

  • query Both prepared and dynamic support an interface that has a function buildSQLString. Which constructs the query to send to the database. The prepared query will replace the constant prepared variable with the executor specific. Via the preparedPlaceHolder. The base understanding is we get a type safe SQL string to execute.
  • responseType I will cover this more later but it tells us whether we will be getting a hash map, data class or something else.
  • block We take a lambda that passes the anticipated result type and allows you to execute on it. You of course can get back the direct data class or map.
  • values The values are passed a variable argument list of any, Each query type has a bind function that ensures type safety at runtime of the passed arguments.

The type arguments are the common <D,R,T> seen amongst a number of other functions. Ensuring scoping to the given database. But we take in a few extras.

  • IN Is what is passed into the lambda as an argument.
  • TargetClass is used only for custom class casting.
  • RSP Is the final return value when executing fetch and the anticipated output of the lambda.

Looking at a map

The default anticipation equivalent of a select * that matches the default table record data class. We pass through the generic type paremeters by and large. Except for TargetClass which we cast to Any as it's not being utilized.

suspend fun <C : IExecutorConfig, RSLT, RSP> mapOne(
        executor: IAsyncExecutor<C, RSLT>,
        block: suspend (R) -> RSP
    ): AsyncDBResponse<D, R, T, C, RSLT, RSP> =
        executor.fetchAsync<D, R, T, R, Any, RSP>(query.lockQuery(sqlBuilder), SingleOfRecord, block)

The execution of this function would look like. The block is optional but we can immediately execute a transformation on the data returned record.

.mapOne(executor) { user ->
                assertTrue { user.firstName == "jack" }
                assertTrue { user.lastName == "smith" }
}

Now if we wanted to mapInto a data class we would also pass the ::class representation. Here TargetClass type is used and is usually determined by type inference.

suspend fun <C : IExecutorConfig, RSLT, TargetClass : Any, RSP> mapInto(
        executor: IAsyncExecutor<C, RSLT>,
        targetClass: KClass<TargetClass>,
        block: suspend (List<TargetClass>) -> RSP
    ) =
        executor.fetchAsync<D, R, T, List<TargetClass>, TargetClass, RSP>(
            query.lockQuery(sqlBuilder),
            ListOfCustomRecord(targetClass),
            block
        )

This can be called like the following. Under the covers we initiate the call to the database in the same manner. Both are using the fetchAsync.

.mapInto(executor, UserProfileJoinRecord::class) { profile ->
                val sorted = profile.sortedBy { item -> item.id }
                assertTrue { sorted[0].id == 1 && sorted[0].email == "jdoe@google.com" }
                assertTrue { sorted[1].id == 2 && sorted[1].email == "jsmith@google.com" }
                assertTrue { sorted[2].id == 4 && sorted[2].email == "jblack@hotmail.com" }
            }

The prepared fetch is very similar. They are just instaniated as a prepared query vs. a dynamic query.

Mapping Reponses.

Initially it was coded that there was a fetch for each given response type. But it was boiled down to a set numer of response types. The responseType paremeter foreshadowed this above.

sealed class ExpectedResponses

object SingleOfRecord : ExpectedResponses()
object ListOfRecord : ExpectedResponses()
object HashMap : ExpectedResponses()
object ListOfHashMap : ExpectedResponses()
data class CustomRecord<TargetClass : Any>(val targetClass: KClass<TargetClass>) : ExpectedResponses()
data class ListOfCustomRecord<TargetClass : Any>(val targetClass: KClass<TargetClass>) : ExpectedResponses()
object Save : ExpectedResponses()
object Delete : ExpectedResponses()
object DirectOfRecord : ExpectedResponses()
object DirectListOfRecord : ExpectedResponses()

A majority of these are just type markers. Where they act as a traffic director. If you have this type, go this way. Only the CustomRecord sub set maintain state. Where they take in that target class to be cast too.

As this is a sealed class we can do pattern matching and easily direct the output. Unfortunately due to type erasure we have to suppres casting warnings.

This is cast into a AsyncDBResponse. This is the given return type of the two base functions noted above.

interface IAsyncDBResponse<D : IDatabase, R : ADatabaseRecord, T : ITable<D, R>, C : IExecutorConfig, RSLT, RSP> {
    val futureResponse: RSLT
    val asyncExecutor: IAsyncExecutor<C, RSLT>
    val query: IBaseQuery<D, R, T>
    suspend fun fetch(): RSP
}

sealed class AsyncDBResponse<D : IDatabase, R : ADatabaseRecord, T : ITable<D, R>, C : IExecutorConfig, RSLT, RSP> :
    IAsyncDBResponse<D, R, T, C, RSLT, RSP>

This is similar to the other set we have seen. This is a base wrapper for the possible response of the executor. We're abstracting a lot of this from the end user.

  • futureResponse Is the returned response type, i.e. from JaSync, JDBC, or Vertx.
  • asyncExecutor Due to lack of reflection on some platforms outside of the jvm, combined with type erasure. I've just started referencing instance of items.
  • query Stores a reference to the query.
  • fetch Is what will wait on the result, and give back the result.

With these type paremeters we are able to construct a GenericWrapper.

data class GenericWrapper<D : IDatabase, R : ADatabaseRecord, T : ITable<D, R>, C : IExecutorConfig, RSLT, IN, TargetClass : Any, RSP>(
    override val futureResponse: RSLT,
    override val asyncExecutor: IAsyncExecutor<C, RSLT>,
    val responseType: ExpectedResponses,
    override val query: IBaseQuery<D, R, T>,
    val callback: suspend (IN) -> RSP
) : AsyncDBResponse<D, R, T, C, RSLT, RSP>() {
    @Suppress("UNCHECKED_CAST")
    override suspend fun fetch(): RSP {
        val rsp = when {
            responseType is SingleOfRecord || responseType is DirectOfRecord -> {
                asyncExecutor.unpackDBFuture<D, R, T, IN>(futureResponse, query)
            }
            responseType is ListOfRecord || responseType is DirectListOfRecord -> {
                asyncExecutor.unpackDBFutures<D, R, T, IN>(futureResponse, query)
            }
            responseType is HashMap -> {
                asyncExecutor.unpackDBFutureAsMap<D, R, T, IN>(futureResponse, query)
            }
            responseType is ListOfHashMap -> {
                asyncExecutor.unpackDBFutureAsMultiMap<D, R, T, IN>(futureResponse, query)
            }
            responseType is Delete && query is IDeleteQuery<D, R, T> -> {
                asyncExecutor.unpackDBDelete<D, R, T, Any, IN, RSP>(futureResponse, query)
            }
            responseType is Save -> {
                asyncExecutor.unpackDBSave<D, R, T, Any, IN, RSP>(futureResponse)
            }
            responseType is CustomRecord<*> -> {
                asyncExecutor.unpackDBFutureAsCustomClass<D, R, T, TargetClass, IN, RSP>(
                    futureResponse,
                    responseType.targetClass as KClass<TargetClass>, query
                )
            }
            responseType is ListOfCustomRecord<*> -> {
                asyncExecutor.unpackDBFutureAsListOfCustomClass<D, R, T, TargetClass, IN, RSP>(
                    futureResponse,
                    responseType.targetClass as KClass<TargetClass>, query
                )
            }
            else -> {
                throw UnsupportedQueryOperation("Got a response type of : $responseType which is not supported in generic.")
            }
        }
        return callback(rsp)
    }
}

This gets in to the latter part. The executor has a number of unpack methods. Which will unpack into a given desired response type. We have to do some casting here, and suppress warnings. But it works. This allows us to do the abstraction at the upper map level. Wrapping it down to the desired output type.

Wrapping Up

With this making an executor is relatively simple. You need to provide a way to execute a query, then map a result to the desired output. Given the result set. It is commonly a list of rows returned.