From dc19995f2b11ba595775b7224df200e365e7c4bf Mon Sep 17 00:00:00 2001 From: Linnea Gräf Date: Tue, 7 Jan 2025 15:57:48 +0100 Subject: refactor: Extract database to its own module --- database/core/build.gradle.kts | 12 +++ .../main/kotlin/moe/nea/ledger/database/Column.kt | 10 ++ .../kotlin/moe/nea/ledger/database/Constraint.kt | 6 ++ .../kotlin/moe/nea/ledger/database/DBSchema.kt | 13 +++ .../main/kotlin/moe/nea/ledger/database/DBType.kt | 33 +++++++ .../moe/nea/ledger/database/InsertStatement.kt | 7 ++ .../main/kotlin/moe/nea/ledger/database/Query.kt | 93 +++++++++++++++++++ .../kotlin/moe/nea/ledger/database/ResultRow.kt | 9 ++ .../main/kotlin/moe/nea/ledger/database/Table.kt | 103 +++++++++++++++++++++ .../moe/nea/ledger/database/UniqueConstraint.kt | 14 +++ .../moe/nea/ledger/database/columns/DBDouble.kt | 18 ++++ .../moe/nea/ledger/database/columns/DBEnum.kt | 31 +++++++ .../moe/nea/ledger/database/columns/DBInstant.kt | 19 ++++ .../moe/nea/ledger/database/columns/DBInt.kt | 18 ++++ .../moe/nea/ledger/database/columns/DBString.kt | 18 ++++ .../moe/nea/ledger/database/columns/DBUlid.kt | 21 +++++ .../moe/nea/ledger/database/columns/DBUuid.kt | 20 ++++ .../moe/nea/ledger/database/sql/ANDExpression.kt | 23 +++++ .../nea/ledger/database/sql/BooleanExpression.kt | 3 + .../kotlin/moe/nea/ledger/database/sql/Clause.kt | 10 ++ .../moe/nea/ledger/database/sql/ClauseBuilder.kt | 11 +++ .../moe/nea/ledger/database/sql/ColumnOperand.kt | 14 +++ .../moe/nea/ledger/database/sql/EqualsClause.kt | 16 ++++ .../kotlin/moe/nea/ledger/database/sql/Join.kt | 19 ++++ .../moe/nea/ledger/database/sql/LikeClause.kt | 17 ++++ .../moe/nea/ledger/database/sql/ORExpression.kt | 23 +++++ .../kotlin/moe/nea/ledger/database/sql/Operand.kt | 5 + .../nea/ledger/database/sql/SQLQueryComponent.kt | 26 ++++++ .../nea/ledger/database/sql/SQLQueryGenerator.kt | 25 +++++ .../moe/nea/ledger/database/sql/StringOperand.kt | 14 +++ 30 files changed, 651 insertions(+) create mode 100644 database/core/build.gradle.kts create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/Constraint.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/InsertStatement.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/Table.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/UniqueConstraint.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBDouble.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBEnum.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInstant.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInt.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBString.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUlid.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUuid.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/BooleanExpression.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/EqualsClause.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/Join.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/LikeClause.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/ORExpression.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryGenerator.kt create mode 100644 database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt (limited to 'database') diff --git a/database/core/build.gradle.kts b/database/core/build.gradle.kts new file mode 100644 index 0000000..a4390fc --- /dev/null +++ b/database/core/build.gradle.kts @@ -0,0 +1,12 @@ +plugins { + `java-library` + kotlin("jvm") +} + +dependencies { + api(project(":basetypes")) +} + +java { + toolchain.languageVersion.set(JavaLanguageVersion.of(8)) +} diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt new file mode 100644 index 0000000..d1294f7 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt @@ -0,0 +1,10 @@ +package moe.nea.ledger.database + +class Column @Deprecated("Use Table.column instead") constructor( + val table: Table, + val name: String, + val type: DBType +) { + val sqlName get() = "`$name`" + val qualifiedSqlName get() = table.sqlName + "." + sqlName +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/Constraint.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/Constraint.kt new file mode 100644 index 0000000..9f7c9ef --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Constraint.kt @@ -0,0 +1,6 @@ +package moe.nea.ledger.database + +interface Constraint { + val affectedColumns: Collection> + fun asSQL(): String +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt new file mode 100644 index 0000000..764cd26 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt @@ -0,0 +1,13 @@ +package moe.nea.ledger.database + +import java.sql.Connection +import java.sql.PreparedStatement + +fun Connection.prepareAndLog(statement: String): PreparedStatement { + println("Preparing to execute $statement") + return prepareStatement(statement) +} + + + + diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt new file mode 100644 index 0000000..86ff544 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt @@ -0,0 +1,33 @@ +package moe.nea.ledger.database + +import java.sql.PreparedStatement +import java.sql.ResultSet + +interface DBType { + val dbType: String + + fun get(result: ResultSet, index: Int): T + fun set(stmt: PreparedStatement, index: Int, value: T) + fun getName(): String = javaClass.simpleName + fun mapped( + from: (R) -> T, + to: (T) -> R, + ): DBType { + return object : DBType { + override fun getName(): String { + return "Mapped(${this@DBType.getName()})" + } + + override val dbType: String + get() = this@DBType.dbType + + override fun get(result: ResultSet, index: Int): R { + return to(this@DBType.get(result, index)) + } + + override fun set(stmt: PreparedStatement, index: Int, value: R) { + this@DBType.set(stmt, index, from(value)) + } + } + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/InsertStatement.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/InsertStatement.kt new file mode 100644 index 0000000..7871ba8 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/InsertStatement.kt @@ -0,0 +1,7 @@ +package moe.nea.ledger.database + +class InsertStatement(val properties: MutableMap, Any>) { + operator fun set(key: Column, value: T) { + properties[key] = value + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt new file mode 100644 index 0000000..7829fbb --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt @@ -0,0 +1,93 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.sql.ANDExpression +import moe.nea.ledger.database.sql.BooleanExpression +import moe.nea.ledger.database.sql.Clause +import moe.nea.ledger.database.sql.Join +import moe.nea.ledger.database.sql.SQLQueryComponent +import moe.nea.ledger.database.sql.SQLQueryGenerator.concatToFilledPreparedStatement +import java.sql.Connection + +class Query( + val connection: Connection, + val selectedColumns: MutableList>, + var table: Table, + var limit: UInt? = null, + var skip: UInt? = null, + val joins: MutableList = mutableListOf(), + val conditions: MutableList = mutableListOf(), +// var order: OrderClause?= null, +) : Iterable { + fun join(table: Table, on: Clause): Query { + joins.add(Join(table, on)) + return this + } + + fun where(binOp: BooleanExpression): Query { + conditions.add(binOp) + return this + } + + fun select(vararg columns: Column<*>): Query { + selectedColumns.addAll(columns) + return this + } + + fun skip(skip: UInt): Query { + require(limit != null) + this.skip = skip + return this + } + + fun limit(limit: UInt): Query { + this.limit = limit + return this + } + + override fun iterator(): Iterator { + val columnSelections = selectedColumns.joinToString { it.qualifiedSqlName } + val elements = mutableListOf( + SQLQueryComponent.standalone("SELECT $columnSelections FROM ${table.sqlName}"), + ) + elements.addAll(joins) + if (conditions.any()) { + elements.add(SQLQueryComponent.standalone("WHERE")) + elements.add(ANDExpression(conditions)) + } + if (limit != null) { + elements.add(SQLQueryComponent.standalone("LIMIT $limit")) + if (skip != null) { + elements.add(SQLQueryComponent.standalone("OFFSET $skip")) + } + } + val prepared = elements.concatToFilledPreparedStatement(connection) + val results = prepared.executeQuery() + return object : Iterator { + var hasAdvanced = false + var hasEnded = false + override fun hasNext(): Boolean { + if (hasEnded) return false + if (hasAdvanced) return true + if (results.next()) { + hasAdvanced = true + return true + } else { + results.close() // TODO: somehow enforce closing this + hasEnded = true + return false + } + } + + override fun next(): ResultRow { + if (!hasNext()) { + throw NoSuchElementException() + } + hasAdvanced = false + return ResultRow(selectedColumns.withIndex().associate { + it.value to it.value.type.get(results, it.index + 1) + }) + } + + } + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt new file mode 100644 index 0000000..d92f913 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt @@ -0,0 +1,9 @@ +package moe.nea.ledger.database + +class ResultRow(val columnValues: Map, *>) { + operator fun get(column: Column): T { + val value = columnValues[column] + ?: error("Invalid column ${column.name}. Only ${columnValues.keys.joinToString { it.name }} are available.") + return value as T + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/Table.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/Table.kt new file mode 100644 index 0000000..c136f48 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Table.kt @@ -0,0 +1,103 @@ +package moe.nea.ledger.database + +import java.sql.Connection + +abstract class Table(val name: String) { + val sqlName get() = "`$name`" + protected val _mutable_columns: MutableList> = mutableListOf() + protected val _mutable_constraints: MutableList = mutableListOf() + val columns: List> get() = _mutable_columns + val constraints get() = _mutable_constraints + protected fun unique(vararg columns: Column<*>) { + _mutable_constraints.add(UniqueConstraint(columns.toList())) + } + + protected fun column(name: String, type: DBType): Column { + @Suppress("DEPRECATION") val column = Column(this, name, type) + _mutable_columns.add(column) + return column + } + + fun debugSchema() { + val nameWidth = columns.maxOf { it.name.length } + val typeWidth = columns.maxOf { it.type.getName().length } + val totalWidth = maxOf(2 + nameWidth + 3 + typeWidth + 2, name.length + 4) + val adjustedTypeWidth = totalWidth - nameWidth - 2 - 3 - 2 + + var string = "\n" + string += ("+" + "-".repeat(totalWidth - 2) + "+\n") + string += ("| $name${" ".repeat(totalWidth - 4 - name.length)} |\n") + string += ("+" + "-".repeat(totalWidth - 2) + "+\n") + for (column in columns) { + string += ("| ${column.name}${" ".repeat(nameWidth - column.name.length)} |") + string += (" ${column.type.getName()}" + + "${" ".repeat(adjustedTypeWidth - column.type.getName().length)} |\n") + } + string += ("+" + "-".repeat(totalWidth - 2) + "+") + println(string) + } + + fun createIfNotExists( + connection: Connection, + filteredColumns: List> = columns + ) { + val properties = mutableListOf() + for (column in filteredColumns) { + properties.add("${column.sqlName} ${column.type.dbType}") + } + val columnSet = filteredColumns.toSet() + for (constraint in constraints) { + if (columnSet.containsAll(constraint.affectedColumns)) { + properties.add(constraint.asSQL()) + } + } + connection.prepareAndLog("CREATE TABLE IF NOT EXISTS $sqlName (" + properties.joinToString() + ")") + .execute() + } + + fun alterTableAddColumns( + connection: Connection, + newColumns: List> + ) { + for (column in newColumns) { + connection.prepareAndLog("ALTER TABLE $sqlName ADD ${column.sqlName} ${column.type.dbType}") + .execute() + } + for (constraint in constraints) { + // TODO: automatically add constraints, maybe (or maybe move constraints into the upgrade schema) + } + } + + enum class OnConflict { + FAIL, + IGNORE, + REPLACE, + ; + + fun asSql(): String { + return name + } + } + + fun insert(connection: Connection, onConflict: OnConflict = OnConflict.FAIL, block: (InsertStatement) -> Unit) { + val insert = InsertStatement(HashMap()) + block(insert) + require(insert.properties.keys == columns.toSet()) + val columnNames = columns.joinToString { it.sqlName } + val valueNames = columns.joinToString { "?" } + val statement = + connection.prepareAndLog("INSERT OR ${onConflict.asSql()} INTO $sqlName ($columnNames) VALUES ($valueNames)") + for ((index, column) in columns.withIndex()) { + (column as Column).type.set(statement, index + 1, insert.properties[column]!!) + } + statement.execute() + } + + fun from(connection: Connection): Query { + return Query(connection, mutableListOf(), this) + } + + fun selectAll(connection: Connection): Query { + return Query(connection, columns.toMutableList(), this) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/UniqueConstraint.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/UniqueConstraint.kt new file mode 100644 index 0000000..32e9f79 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/UniqueConstraint.kt @@ -0,0 +1,14 @@ +package moe.nea.ledger.database + +class UniqueConstraint(val columns: List>) : Constraint { + init { + require(columns.isNotEmpty()) + } + + override val affectedColumns: Collection> + get() = columns + + override fun asSQL(): String { + return "UNIQUE (${columns.joinToString() { it.sqlName }})" + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBDouble.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBDouble.kt new file mode 100644 index 0000000..6828308 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBDouble.kt @@ -0,0 +1,18 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet + +object DBDouble : DBType { + override val dbType: String + get() = "DOUBLE" + + override fun get(result: ResultSet, index: Int): Double { + return result.getDouble(index) + } + + override fun set(stmt: PreparedStatement, index: Int, value: Double) { + stmt.setDouble(index, value) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBEnum.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBEnum.kt new file mode 100644 index 0000000..3cce8bc --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBEnum.kt @@ -0,0 +1,31 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet + +class DBEnum>( + val type: Class, +) : DBType { + companion object { + inline operator fun > invoke(): DBEnum { + return DBEnum(T::class.java) + } + } + + override val dbType: String + get() = "TEXT" + + override fun getName(): String { + return "DBEnum(${type.simpleName})" + } + + override fun set(stmt: PreparedStatement, index: Int, value: T) { + stmt.setString(index, value.name) + } + + override fun get(result: ResultSet, index: Int): T { + val name = result.getString(index) + return java.lang.Enum.valueOf(type, name) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInstant.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInstant.kt new file mode 100644 index 0000000..a9f36ef --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInstant.kt @@ -0,0 +1,19 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet +import java.time.Instant + +object DBInstant : DBType { + override val dbType: String + get() = "INTEGER" + + override fun set(stmt: PreparedStatement, index: Int, value: Instant) { + stmt.setLong(index, value.toEpochMilli()) + } + + override fun get(result: ResultSet, index: Int): Instant { + return Instant.ofEpochMilli(result.getLong(index)) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInt.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInt.kt new file mode 100644 index 0000000..9cf2aa0 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInt.kt @@ -0,0 +1,18 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet + +object DBInt : DBType { + override val dbType: String + get() = "INTEGER" + + override fun get(result: ResultSet, index: Int): Long { + return result.getLong(index) + } + + override fun set(stmt: PreparedStatement, index: Int, value: Long) { + stmt.setLong(index, value) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBString.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBString.kt new file mode 100644 index 0000000..3994b7d --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBString.kt @@ -0,0 +1,18 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet + +object DBString : DBType { + override val dbType: String + get() = "TEXT" + + override fun get(result: ResultSet, index: Int): String { + return result.getString(index) + } + + override fun set(stmt: PreparedStatement, index: Int, value: String) { + stmt.setString(index, value) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUlid.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUlid.kt new file mode 100644 index 0000000..33db65f --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUlid.kt @@ -0,0 +1,21 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.utils.ULIDWrapper +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement +import java.sql.ResultSet + +object DBUlid : DBType { + override val dbType: String + get() = "TEXT" + + override fun get(result: ResultSet, index: Int): ULIDWrapper { + val text = result.getString(index) + return ULIDWrapper(text) + } + + override fun set(stmt: PreparedStatement, index: Int, value: ULIDWrapper) { + stmt.setString(index, value.wrapped) + } +} + diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUuid.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUuid.kt new file mode 100644 index 0000000..ede385f --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUuid.kt @@ -0,0 +1,20 @@ +package moe.nea.ledger.database.columns + +import moe.nea.ledger.database.DBType +import moe.nea.ledger.utils.UUIDUtil +import java.sql.PreparedStatement +import java.sql.ResultSet +import java.util.UUID + +object DBUuid : DBType { + override val dbType: String + get() = "TEXT" + + override fun get(result: ResultSet, index: Int): UUID { + return UUIDUtil.parsePotentiallyDashlessUUID(result.getString(index)) + } + + override fun set(stmt: PreparedStatement, index: Int, value: UUID) { + stmt.setString(index, value.toString()) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt new file mode 100644 index 0000000..6ea1b64 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt @@ -0,0 +1,23 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class ANDExpression( + val elements: List +) : BooleanExpression { + init { + require(elements.isNotEmpty()) + } + + override fun asSql(): String { + return (elements + SQLQueryComponent.standalone("TRUE")).joinToString(" AND ", "(", ")") { it.asSql() } + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + for (element in elements) { + index = element.appendToStatement(stmt, index) + } + return index + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/BooleanExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/BooleanExpression.kt new file mode 100644 index 0000000..5f2fba5 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/BooleanExpression.kt @@ -0,0 +1,3 @@ +package moe.nea.ledger.database.sql + +interface BooleanExpression : SQLQueryComponent \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt new file mode 100644 index 0000000..2921d80 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt @@ -0,0 +1,10 @@ +package moe.nea.ledger.database.sql + +interface Clause : BooleanExpression { + companion object { + operator fun invoke(builder: ClauseBuilder.() -> Clause): Clause { + return builder(ClauseBuilder()) + } + } + +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt new file mode 100644 index 0000000..2b141f0 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt @@ -0,0 +1,11 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Column + +class ClauseBuilder { + fun column(column: Column): Operand = ColumnOperand(column) + fun string(string: String): StringOperand = StringOperand(string) + infix fun Operand<*>.eq(operand: Operand<*>) = EqualsClause(this, operand) + infix fun Operand<*>.like(op: StringOperand) = LikeClause(this, op) + infix fun Operand<*>.like(op: String) = LikeClause(this, string(op)) +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt new file mode 100644 index 0000000..9150aa7 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt @@ -0,0 +1,14 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Column +import java.sql.PreparedStatement + +data class ColumnOperand(val column: Column) : Operand { + override fun asSql(): String { + return column.qualifiedSqlName + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return startIndex + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/EqualsClause.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/EqualsClause.kt new file mode 100644 index 0000000..c6c482a --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/EqualsClause.kt @@ -0,0 +1,16 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class EqualsClause(val left: Operand<*>, val right: Operand<*>) : Clause { // TODO: typecheck this somehow + override fun asSql(): String { + return left.asSql() + " = " + right.asSql() + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + index = left.appendToStatement(stmt, index) + index = right.appendToStatement(stmt, index) + return index + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Join.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Join.kt new file mode 100644 index 0000000..621aa05 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Join.kt @@ -0,0 +1,19 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Table +import java.sql.PreparedStatement + +data class Join( + val table: Table, +//TODO: aliased columns val tableAlias: String, + val filter: Clause, +) : SQLQueryComponent { + // JOIN ItemEntry on LogEntry.transactionId = ItemEntry.transactionId + override fun asSql(): String { + return "JOIN ${table.sqlName} ON ${filter.asSql()}" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return filter.appendToStatement(stmt, startIndex) + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LikeClause.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LikeClause.kt new file mode 100644 index 0000000..f84c5cc --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LikeClause.kt @@ -0,0 +1,17 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class LikeClause(val left: Operand, val right: StringOperand) : Clause { + //TODO: check type safety with this one + override fun asSql(): String { + return "(" + left.asSql() + " LIKE " + right.asSql() + ")" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + index = left.appendToStatement(stmt, index) + index = right.appendToStatement(stmt, index) + return index + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ORExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ORExpression.kt new file mode 100644 index 0000000..637963d --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ORExpression.kt @@ -0,0 +1,23 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class ORExpression( + val elements: List +) : BooleanExpression { + init { + require(elements.isNotEmpty()) + } + + override fun asSql(): String { + return (elements + SQLQueryComponent.standalone("FALSE")).joinToString(" OR ", "(", ")") { it.asSql() } + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + for (element in elements) { + index = element.appendToStatement(stmt, index) + } + return index + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt new file mode 100644 index 0000000..9937414 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt @@ -0,0 +1,5 @@ +package moe.nea.ledger.database.sql + +interface Operand : SQLQueryComponent { + +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt new file mode 100644 index 0000000..10b2be6 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt @@ -0,0 +1,26 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +interface SQLQueryComponent { + fun asSql(): String + + /** + * @return the next writable index (should equal to the amount of `?` in [asSql] + [startIndex]) + */ + fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int + + companion object { + fun standalone(sql: String): SQLQueryComponent { + return object : SQLQueryComponent { + override fun asSql(): String { + return sql + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return startIndex + } + } + } + } +} \ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryGenerator.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryGenerator.kt new file mode 100644 index 0000000..be81ff2 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryGenerator.kt @@ -0,0 +1,25 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.prepareAndLog +import java.sql.Connection +import java.sql.PreparedStatement + +object SQLQueryGenerator { + fun List.concatToFilledPreparedStatement(connection: Connection): PreparedStatement { + var query = "" + for (element in this) { + if (query.isNotEmpty()) { + query += " " + } + query += element.asSql() + } + val statement = connection.prepareAndLog(query) + var index = 1 + for (element in this) { + val nextIndex = element.appendToStatement(statement, index) + if (nextIndex < index) error("$element went back in time") + index = nextIndex + } + return statement + } +} diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt new file mode 100644 index 0000000..24f4e78 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt @@ -0,0 +1,14 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class StringOperand(val value: String) : Operand { + override fun asSql(): String { + return "?" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + stmt.setString(startIndex, value) + return 1 + startIndex + } +} \ No newline at end of file -- cgit