com.sxtanna.db.KueryTask.kt Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of Kuery Show documentation
Show all versions of Kuery Show documentation
MySQL Kotlin DSL/ORM based on HikariCP
package com.sxtanna.db
import com.sxtanna.db.ext.PrimaryKey
import com.sxtanna.db.ext.Value
import com.sxtanna.db.ext.value
import com.sxtanna.db.ext.whileNext
import com.sxtanna.db.struct.Database
import com.sxtanna.db.struct.Resolver
import com.sxtanna.db.struct.Table
import com.sxtanna.db.struct.base.Duplicate
import com.sxtanna.db.struct.base.Order
import com.sxtanna.db.struct.base.Order.*
import com.sxtanna.db.struct.base.Order.Direction.ASCEND
import com.sxtanna.db.struct.base.Where
import com.sxtanna.db.struct.base.Where.Clause.Between
import com.sxtanna.db.struct.statement.*
import com.sxtanna.db.type.Executed
import org.intellij.lang.annotations.Language
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.util.*
import kotlin.reflect.KParameter
import kotlin.reflect.KProperty1
import kotlin.reflect.full.declaredMemberProperties
import kotlin.reflect.full.findAnnotation
import kotlin.reflect.full.primaryConstructor
/**
* Main task class
* * If a call doesn't return [Unit] it requires you to make a call to either
* * [SelectN.componentN] directly or through destructuring
* * or
* * [Executed.execute] for [Delete] and [Update]
*
* [kotlin.reflect.KClass.properties]
*/
class KueryTask(private val kuery : Kuery, private val connection : Connection) : DBCreator, DBDeleter, DBDropper, DBInserter, DBSelector, DBTruncater, DBUpdater, DBUser {
/**
* Push a statement to the database
*/
fun push(@Language("MySQL") statement : String, block : PreparedStatement.() -> Unit = {}) {
kuery.logger.debug("Pushing statement `$statement`")
with(kuery) { connection.push(statement, block) }
}
/**
* Pull the results of a query from the database
*/
fun pull(@Language("MySQL") statement : String, block : PreparedStatement.() -> Unit = {}) : ResultSet {
kuery.logger.debug("Pulling statement `$statement`")
return with(kuery) { connection.pull(statement, block) }
}
//region Table cursors
/**
* Open and return a Table task handler
*/
fun on(table : Table) = KueryTaskTable(table, this)
/**
* Open and use a Table task handler
*/
fun on(table : Table, block : KueryTaskTable.() -> R) : R {
return on(table).block()
}
//endregion
//region Create statement
override fun create(database : Database, andTables : Boolean) {
push("CREATE DATABASE ${database.name}")
if (andTables) database.tables.forEach { create(it) }
}
override fun create(table : Table) {
val columns = table.columns.entries.joinToString { "${it.key} ${it.value}" }
push("CREATE TABLE IF NOT EXISTS ${table.name}($columns)")
}
//endregion
//region Delete statements
override fun delete(table : Table) : Delete {
return DeleteImpl(table)
}
override fun delete(table : Table, rows : Collection) {
if (rows.isEmpty()) return
val statement = "DELETE FROM ${table.name} WHERE ${table.fields.joinToString(" AND ") { "${it.name}=?" }}"
kuery.logger.debug("Pushing statement `$statement`")
val state = connection.prepareStatement(statement).apply {
var index = 1
rows.forEach { e ->
table.fields.map { it.get(e) }.forEach { this[index++] = it }
index = 1
addBatch()
}
}
state.executeBatch()
}
override fun deleteAllRows(table : Table) {
push("DELETE FROM ${table.name}")
}
//endregion
//region Drop statements
override fun drop(database : Database) {
push("DROP DATABASE ${database.name}")
}
override fun drop(table : Table) {
push("DROP TABLE ${table.name}")
}
//endregion
//region Insert statements
override fun insert(table : Table, rows : Collection) {
if (rows.isEmpty()) return
val columns = table.columns.keys
val values = "(${Array(columns.size) { "?" }.joinToString()})"
push("INSERT INTO ${table.name} (${columns.joinToString()}) VALUES ${(0 until rows.size).joinToString { values }}") {
var index = 1
rows.forEach { e ->
table.fields.map { it.get(e) }.forEach { this[index++] = it }
}
}
}
override fun insert(table : Table, duplicate : Duplicate>, rows : Collection) {
if (rows.isEmpty()) return
val columns = table.columns.keys
val values = "(${Array(columns.size) { "?" }.joinToString()})"
push("INSERT INTO ${table.name} (${columns.joinToString()}) VALUES ${(0 until rows.size).joinToString { values }} ${duplicate(table)}") {
var index = 1
rows.forEach { e ->
table.fields.map { it.get(e) }.forEach { this[index++] = it }
}
}
}
//endregion
//region Select statements
override fun select(table : Table) : Select1> {
return Select1Impl(table, emptyList())
}
override fun selectOne(table : Table, prop : KProperty1) : Select1 {
return SelectOneImpl(table, prop)
}
override fun
select(table : Table,
prop : KProperty1) : Select1> {
return Select1Impl(table, listOf(prop))
}
override fun
select(table : Table,
prop1 : KProperty1,
prop2 : KProperty1) : Select2, List> {
return Select2Impl(table, listOf(prop1, prop2))
}
override fun
select(table : Table,
prop1 : KProperty1,
prop2 : KProperty1,
prop3 : KProperty1) : Select3, List, List> {
return Select3Impl(table, listOf(prop1, prop2, prop3))
}
override fun
select(table : Table,
prop1 : KProperty1,
prop2 : KProperty1,
prop3 : KProperty1,
prop4 : KProperty1) : Select4, List, List, List> {
return Select4Impl(table, listOf(prop1, prop2, prop3, prop4))
}
override fun
select(table : Table,
prop1 : KProperty1,
prop2 : KProperty1,
prop3 : KProperty1,
prop4 : KProperty1,
prop5 : KProperty1) : Select5, List, List, List, List> {
return Select5Impl(table, listOf(prop1, prop2, prop3, prop4, prop5))
}
//endregion
//region Truncate statement
override fun truncate(table : Table) {
push("TRUNCATE TABLE ${table.name}")
}
//endregion
//region Update statements
override fun update(table : Table) : Update {
return UpdateImpl(table)
}
override fun update(table : Table, rows : Collection) {
val key = table.getPrimaryKey() ?: return kuery.logger.error("Cannot update entire rows in a table without a primary key")
val statement = "UPDATE ${table.name} SET ${table.fields.joinToString { "${it.name}=?" }} WHERE ${key.name}=?"
kuery.logger.debug("Pushing statement `$statement`")
connection.prepareStatement(statement).apply {
var index = 1
rows.forEach { e ->
table.fields.map { it.get(e) }.forEach { this[index++] = it }
this[index++] = key.get(e)
index = 1
addBatch()
}
}.executeBatch()
}
override fun updateAllRows(table : Table, row : E) {
if (table.getPrimaryKey() != null) return kuery.logger.error("Cannot update all rows to a single row of a table with a primary key")
push("UPDATE ${table.name} SET ${table.fields.joinToString { "${it.name}=?" }}") {
var index = 1
table.fields.map { it.get(row) }.forEach { this[index++] = it }
}
}
override fun updateAllRows(table : Table, vararg values : Value) {
if (values.any { it.prop.findAnnotation() != null }) {
return kuery.logger.error("Cannot update all rows in a table with a primary key to the same value")
}
push("UPDATE ${table.name} SET ${values.joinToString { "${it.prop.name}=?" }}") {
var index = 1
values.forEach { this[index++] = it.value }
}
}
//endregion
//region Use statement
override fun use(database : Database) {
push("USE ${database.name}")
}
//endregion
//region Select implementations
internal inner abstract class SelectImpl(protected val table : Table, target : List>)
: Select1 {
private val order = mutableListOf()
private val where = mutableListOf()
internal val results = mutableMapOf>()
protected val target = if (target.isEmpty()) "*" else target.joinToString { it.name }
protected lateinit var lastResult : ResultSet
override fun execute() {
if (::lastResult.isInitialized) return // not going to pull results again
val order = order.isNotEmpty().value(" ORDER BY ${order.joinToString()}")
val where = where.isNotEmpty().value(" WHERE ${where.joinToString(" AND ")}")
// pull results from database
val result = pull("SELECT $target FROM ${table.name}$where$order") {
var offset = 0
[email protected] { index, it ->
this[index + 1 + offset] = it.data
if (it is Between) {
this[index + 1 + ++offset] = it.data
}
}
}
// return early if empty
if (result.isBeforeFirst.not()) {
result.close()
lastResult = result
return
}
// gather column names
val columns = result.metaData.let { m ->
(1..m.columnCount).map { m.getColumnName(it) }
}
// gather fields
val fields = table.clazz.declaredMemberProperties.associateBy { it.name }
// populate results with empty
columns.forEachIndexed { index, _ ->
results[index] = mutableListOf()
}
// for every result, populate every index
result.whileNext {
columns.forEachIndexed { index, s ->
val field = fields[s]
results[index]?.add(field?.let { Resolver.SqlI[this, it] })
}
}
lastResult = result
}
override final fun component1() : R1 {
if (::lastResult.isInitialized.not()) execute() // maintain past behavior
return first() // return first component
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) : Select1 = apply {
where.addAll(Where().apply { block(prop) }.clauses)
}
override fun order(prop : KProperty1, direction : Direction) : Select1 = apply {
order.add(if (direction == ASCEND) Ascend(prop.name) else Descend(prop.name))
}
protected open fun first() : R1 {
if (results.isEmpty()) return emptyList() as R1 // gotta keep that quick return
return checkNotNull(results[0] as R1) {
table.fields[1].let { "Results for ${it.name} weren't ${it.returnType}" }
}
}
}
internal inner class SelectOneImpl(table : Table, target : KProperty1)
: SelectImpl(table, listOf(target)) {
override fun first() : R1 {
return (super.first() as List)[0]
}
}
internal open inner class Select1Impl(table : Table, target : List>)
: SelectImpl>(table, target) {
override final fun first() : List {
return if (target != "*") super.first() else { // try to create the entire object
// get primary constructor
val construct = table.clazz.primaryConstructor ?: return emptyList()
val params = construct.parameters
val pNames = params.associateBy { it.name ?: "" }
// associate columns with indexes
val target = lastResult.metaData.let { m ->
(1..m.columnCount).associate { it to m.getColumnName(it) }
}
// associate params with indexes
val map = results.keys.associateBy { pNames[target[it + 1]] }
// the least amount of creatable objects (might not actually need this due to nullability)
val min = results.values.minBy { it.size }?.size ?: 0
val values = mutableMapOf()
(0 until min).mapNotNull { ri ->
params.forEach {
val index = map[it]
values[it] = results[index]?.get(ri)
}
construct.callBy(values).also { values.clear() }
} as List
}
}
}
internal open inner class Select2Impl(table : Table, target : List>)
: Select1Impl(table, target), Select2, List> {
override final fun component2() : List {
return checkNotNull(results[1] as List) {
table.fields[2].let { "Results for ${it.name} weren't ${it.returnType}" }
}
}
override fun order(prop : KProperty1, direction : Direction) : Select2, List> {
super.order(prop, direction)
return this
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) : Select2, List> {
super.where(prop, block)
return this
}
}
internal open inner class Select3Impl(table : Table, target : List>)
: Select2Impl(table, target), Select3, List, List> {
override final fun component3() : List {
return checkNotNull(results[2] as? List) {
table.fields[3].let { "Results for ${it.name} weren't ${it.returnType}" }
}
}
override fun order(prop : KProperty1, direction : Direction) : Select3, List, List> {
super.order(prop, direction)
return this
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) : Select3, List, List> {
super.where(prop, block)
return this
}
}
internal open inner class Select4Impl(table : Table, target : List>)
: Select3Impl(table, target), Select4, List, List, List> {
override final fun component4() : List {
return checkNotNull(results[3] as List) {
table.fields[4].let { "Results for ${it.name} weren't ${it.returnType}" }
}
}
override fun order(prop : KProperty1, direction : Direction) : Select4, List, List, List> {
super.order(prop, direction)
return this
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) : Select4, List, List, List> {
super.where(prop, block)
return this
}
}
internal open inner class Select5Impl(table : Table, target : List>)
: Select4Impl(table, target), Select5, List, List, List, List> {
override final fun component5() : List {
return checkNotNull(results[4] as List) {
table.fields[5].let { "Results for ${it.name} weren't ${it.returnType}" }
}
}
override fun order(prop : KProperty1, direction : Direction) : Select5, List, List, List, List> {
super.order(prop, direction)
return this
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) : Select5, List, List, List, List> {
super.where(prop, block)
return this
}
}
//endregion
//region Delete implementation
internal inner class DeleteImpl(private val table : Table) : Delete {
private val where = mutableListOf()
private var executed = false
override fun execute() {
if (executed) return
executed = true
if (where.isEmpty()) {
return kuery.logger.error("Attempting to run delete with no clauses, this will delete all rows, please use KueryTask#deleteAllRows")
}
push("DELETE FROM ${table.name} WHERE ${where.joinToString(" AND ")}") {
var offset = 0
where.forEachIndexed { index, it ->
this[index + 1 + offset] = it.data
if (it is Between) {
this[index + 1 + ++offset] = it.data
}
}
}
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) = apply {
where.addAll(Where().apply { block(prop) }.clauses)
}
}
//endregion
//region Update implementation
internal inner class UpdateImpl(private val table : Table) : Update {
private val value = mutableListOf>()
private val where = mutableListOf()
private var executed = false
override fun execute() {
if (executed) return
executed = true
if (value.isEmpty()) {
return kuery.logger.error("Attempting to run update with no values, this will do nothing")
}
if (where.isEmpty()) {
return kuery.logger.error("Attempting to run update with no clauses, this will update every row, please use KueryTask#updateAllRows")
}
push("UPDATE ${table.name} SET ${value.joinToString { "${it.prop.name}=?" }} WHERE ${where.joinToString(" AND ")}") {
var index = 1
value.forEach { this[index++] = it.value }
var offset = 0
where.forEachIndexed { index, it ->
this[index + 1 + offset] = it.data
if (it is Between) {
this[index + 1 + ++offset] = it.data
}
}
}
}
override fun set(column : KProperty1, value : R) : Update = apply {
this.value.add(Value(column, value))
}
override fun where(prop : KProperty1, block : Where.(KProperty1) -> Unit) = apply {
where.addAll(Where().apply { block(prop) }.clauses)
}
}
//endregion
private operator fun PreparedStatement.set(index : Int, any : Any?) {
setObject(index, (any as? UUID)?.toString() ?: any)
}
}