CoroutineSqlClient.kt

import io.vertx.core.json.JsonArray
import io.vertx.ext.sql.ResultSet
import io.vertx.ext.sql.SQLConnection
import io.vertx.ext.sql.UpdateResult
import io.vertx.kotlin.core.json.JsonArray
import io.vertx.kotlin.core.json.get
import io.vertx.kotlin.coroutines.awaitResult
import io.vertx.ext.sql.SQLClient as VertxSQLClient

// ---- Db access
class SQLClient(val dbClient: VertxSQLClient): VertxSQLClient by dbClient {
	suspend fun query(sql: String, params: JsonArray? = null): ResultSet =
			awaitResult<SQLConnection> { dbClient.getConnection(it) }.use { conn ->
				return if (params == null)
					awaitResult { conn.query(sql, it) }
				else
					awaitResult { conn.queryWithParams(sql, params, it) }
			}

	suspend fun <T> queryScalar(sql: String, params: JsonArray? = null): T? =
			awaitResult<SQLConnection> { dbClient.getConnection(it) }.use { conn ->
				return try {
					if (params == null)
						awaitResult<JsonArray?> { conn.querySingle(sql, it) }?.firstOrNull() as? T
					else
						awaitResult<JsonArray?> { conn.querySingleWithParams(sql, params, it) }?.firstOrNull() as? T
				} catch (e: IndexOutOfBoundsException) { null }
			}

	suspend fun update(sql: String, params: JsonArray? = null): UpdateResult =
			awaitResult<SQLConnection> { dbClient.getConnection(it) }.use { conn ->
				return if (params == null)
					awaitResult { conn.update(sql, it) }
				else
					awaitResult { conn.updateWithParams(sql, params, it) }
			}

	suspend fun update(tableName: String, fields: Map<String, Any>, where: Map<String, Any>): UpdateResult =
			awaitResult<SQLConnection> { dbClient.getConnection(it) }.use { conn ->
				val setSql = fields.keys.joinToString { "$it = ?" }
				val whereSql = where.keys.joinToString(" AND ") { "$it = ?" }

				val sql = "UPDATE $tableName SET $setSql WHERE $whereSql"

				val params = (fields.values + where.values).toTypedArray()

				return awaitResult { conn.updateWithParams(sql, JsonArray(*params), it) }
			}

	suspend fun insert(tableName: String, fieldNames: List<String>, values: List<List<Any>>): UpdateResult =
			awaitResult<SQLConnection> { dbClient.getConnection(it) }.use { conn ->
				val fieldSql = fieldNames.joinToString()
				val valuesSql = values.joinToString {
					it.joinToString(prefix = "(", postfix = ")") { "?" }
				}

				val sql = "INSERT $tableName ($fieldSql) VALUES $valuesSql"

				val params = values.flatten().toTypedArray()

				return awaitResult { conn.updateWithParams(sql, JsonArray(*params), it) }
			}
}