diff options
Diffstat (limited to 'database')
44 files changed, 1077 insertions, 0 deletions
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..c21a159 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt @@ -0,0 +1,31 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.sql.IntoSelectable +import moe.nea.ledger.database.sql.Selectable +import java.sql.PreparedStatement + +class Column<T, Raw> @Deprecated("Use Table.column instead") constructor( + val table: Table, + val name: String, + val type: DBType<T, Raw> +) : IntoSelectable<T> { + override fun asSelectable() = object : Selectable<T, Raw> { + override fun asSql(): String { + return qualifiedSqlName + } + + override val dbType: DBType<T, Raw> + get() = this@Column.type + + override fun guessColumn(): Column<T, Raw> { + return this@Column + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return startIndex + } + } + + 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..729c6b8 --- /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<Column<*, *>> + 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..622aff3 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt @@ -0,0 +1,43 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.sql.ClauseBuilder +import java.sql.PreparedStatement +import java.sql.ResultSet + + +interface DBType< + /** + * Mapped type of this db type. Represents the Java type this db type accepts for saving to the database. + */ + T, + /** + * Phantom marker type representing how this db type is presented to the actual DB. Is used by APIs such as [ClauseBuilder] to allow for rough typechecking. + */ + RawType> { + val dbType: String + + fun get(result: ResultSet, index: Int): T + fun set(stmt: PreparedStatement, index: Int, value: T) + fun getName(): String = javaClass.simpleName + fun <R> mapped( + from: (R) -> T, + to: (T) -> R, + ): DBType<R, RawType> { + return object : DBType<R, RawType> { + 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..25bef22 --- /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<Column<*, *>, Any>) { + operator fun <T : Any> set(key: Column<T, *>, 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..a23c878 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt @@ -0,0 +1,111 @@ +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.IntoSelectable +import moe.nea.ledger.database.sql.Join +import moe.nea.ledger.database.sql.SQLQueryComponent +import moe.nea.ledger.database.sql.SQLQueryGenerator.concatToFilledPreparedStatement +import moe.nea.ledger.database.sql.Selectable +import java.sql.Connection + +class Query( + val connection: Connection, + val selectedColumns: MutableList<Selectable<*, *>>, + var table: Table, + var limit: UInt? = null, + var skip: UInt? = null, + val joins: MutableList<Join> = mutableListOf(), + val conditions: MutableList<BooleanExpression> = mutableListOf(), + var distinct: Boolean = false, +// var order: OrderClause?= null, +) : Iterable<ResultRow> { + 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: IntoSelectable<*>): Query { + for (column in columns) { + this.selectedColumns.add(column.asSelectable()) + } + return this + } + + fun skip(skip: UInt): Query { + require(limit != null) + this.skip = skip + return this + } + + fun distinct(): Query { + this.distinct = true + return this + } + + fun limit(limit: UInt): Query { + this.limit = limit + return this + } + + override fun iterator(): Iterator<ResultRow> { + val elements = mutableListOf( + SQLQueryComponent.standalone("SELECT"), + ) + if (distinct) + elements.add(SQLQueryComponent.standalone("DISTINCT")) + selectedColumns.forEachIndexed { idx, it -> + elements.add(it) + if (idx != selectedColumns.lastIndex) { + elements.add(SQLQueryComponent.standalone(",")) + } + } + elements.add(SQLQueryComponent.standalone("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<ResultRow> { + 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.dbType.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..7b57abd --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt @@ -0,0 +1,22 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.sql.Selectable + +class ResultRow(val selectableValues: Map<Selectable<*, *>, *>) { + val columnValues = selectableValues.mapNotNull { + val col = it.key.guessColumn() ?: return@mapNotNull null + col to it.value + }.toMap() + + operator fun <T> get(column: Column<T, *>): T { + val value = columnValues[column] + ?: error("Invalid column ${column.name}. Only ${columnValues.keys.joinToString { it.name }} are available.") + return value as T + } + + operator fun <T> get(column: Selectable<T, *>): T { + val value = selectableValues[column] + ?: error("Invalid selectable ${column}. Only ${selectableValues.keys} 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..a462813 --- /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<Column<*, *>> = mutableListOf() + protected val _mutable_constraints: MutableList<Constraint> = mutableListOf() + val columns: List<Column<*, *>> get() = _mutable_columns + val constraints get() = _mutable_constraints + protected fun unique(vararg columns: Column<*, *>) { + _mutable_constraints.add(UniqueConstraint(columns.toList())) + } + + protected fun <T, R> column(name: String, type: DBType<T, R>): Column<T, R> { + @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<Column<*, *>> = columns + ) { + val properties = mutableListOf<String>() + 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<Column<*, *>> + ) { + 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<Any, *>).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.mapTo(mutableListOf()) { it.asSelectable() }, 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..31ef06c --- /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<Column<*, *>>) : Constraint { + init { + require(columns.isNotEmpty()) + } + + override val affectedColumns: Collection<Column<*, *>> + 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..9840df2 --- /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<Double, Double> { + 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..78ac578 --- /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<T : Enum<T>>( + val type: Class<T>, +) : DBType<T, String> { + companion object { + inline operator fun <reified T : Enum<T>> invoke(): DBEnum<T> { + 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..2cf1882 --- /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<Instant, Long> { + 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..b5406e1 --- /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<Long, Long> { + 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..4406627 --- /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<String, String> { + 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..1fcc9d8 --- /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<ULIDWrapper, String> { + 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..eaea440 --- /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<UUID, String> { + 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..43d5a53 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt @@ -0,0 +1,26 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +data class ANDExpression( + val elements: List<BooleanExpression> +) : BooleanExpression { + init { + require(elements.isNotEmpty()) + } + + override fun asSql(): String { + elements.singleOrNull()?.let { + return "(" + it.asSql() + ")" + } + return elements.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..205e566 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt @@ -0,0 +1,12 @@ +package moe.nea.ledger.database.sql + +/** + * Directly constructing [clauses][Clause] is discouraged. Instead [Clause.invoke] should be used. + */ +interface Clause : BooleanExpression { + companion object { + operator fun <T> invoke(builder: ClauseBuilder.() -> T): T { + 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..cb0ddfc --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt @@ -0,0 +1,25 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Column +import moe.nea.ledger.database.DBType + +class ClauseBuilder { + // TODO: should we match on T AND R? maybe allow explicit upcasting + fun <T, R> column(column: Column<T, R>): ColumnOperand<T, R> = ColumnOperand(column) + fun string(string: String): StringOperand = StringOperand(string) + fun <T, R> value(dbType: DBType<T, R>, value: T): Operand<T, R> = ValuedOperand(dbType, value) + infix fun <T> Operand<*, T>.eq(operand: Operand<*, T>): Clause = EqualsClause(this, operand) + infix fun <T, R> TypedOperand<T, R>.eq(value: T): Clause = EqualsClause(this, value(dbType, value)) + infix fun Operand<*, String>.like(op: StringOperand): Clause = LikeClause(this, op) + infix fun Operand<*, String>.like(op: String): Clause = LikeClause(this, string(op)) + infix fun <T> Operand<*, T>.lt(op: Operand<*, T>): BooleanExpression = LessThanExpression(this, op) + infix fun <T> Operand<*, T>.le(op: Operand<*, T>): BooleanExpression = LessThanEqualsExpression(this, op) + infix fun <T> Operand<*, T>.gt(op: Operand<*, T>): BooleanExpression = op lt this + infix fun <T> Operand<*, T>.ge(op: Operand<*, T>): BooleanExpression = op le this + infix fun <T> Operand<*, T>.inList(list: ListExpression<*, T>): Clause = ListClause(this, list) + infix fun <T, R> TypedOperand<T, R>.inList(list: List<T>): Clause = this inList list(dbType, list) + fun <T, R> list(dbType: DBType<T, R>, vararg values: T): ListExpression<T, R> = list(dbType, values.toList()) + fun <T, R> list(dbType: DBType<T, R>, values: List<T>): ListExpression<T, R> = ListExpression(values, dbType) + infix fun BooleanExpression.and(clause: BooleanExpression): BooleanExpression = ANDExpression(listOf(this, clause)) + infix fun BooleanExpression.or(clause: BooleanExpression): BooleanExpression = ORExpression(listOf(this, clause)) +}
\ 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..430d592 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt @@ -0,0 +1,18 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Column +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement + +data class ColumnOperand<T, Raw>(val column: Column<T, Raw>) : TypedOperand<T, Raw> { + override val dbType: DBType<T, Raw> + get() = column.type + + 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..cfe72ef --- /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 { + 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/IntoSelectable.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/IntoSelectable.kt new file mode 100644 index 0000000..3775387 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/IntoSelectable.kt @@ -0,0 +1,5 @@ +package moe.nea.ledger.database.sql + +interface IntoSelectable<T> { + fun asSelectable(): Selectable<T, *> +}
\ 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/LessThanEqualsExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanEqualsExpression.kt new file mode 100644 index 0000000..4820c97 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanEqualsExpression.kt @@ -0,0 +1,15 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +class LessThanEqualsExpression(val lhs: Operand<*, *>, val rhs: Operand<*, *>) : + BooleanExpression { + override fun asSql(): String { + return "${lhs.asSql()} <= ${rhs.asSql()}" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + val next = lhs.appendToStatement(stmt, startIndex) + return rhs.appendToStatement(stmt, next) + } +} diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanExpression.kt new file mode 100644 index 0000000..4609ac1 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanExpression.kt @@ -0,0 +1,15 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +class LessThanExpression(val lhs: Operand<*, *>, val rhs: Operand<*, *>) : + BooleanExpression { + override fun asSql(): String { + return "${lhs.asSql()} < ${rhs.asSql()}" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + val next = lhs.appendToStatement(stmt, startIndex) + return rhs.appendToStatement(stmt, next) + } +} 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..1122329 --- /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<T>(val left: Operand<T, String>, 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/ListClause.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListClause.kt new file mode 100644 index 0000000..d240472 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListClause.kt @@ -0,0 +1,8 @@ +package moe.nea.ledger.database.sql + +class ListClause<R>( + val lhs: Operand<*, R>, + val list: ListExpression<*, R>, +) : Clause, SQLQueryComponent by SQLQueryComponent.composite( + lhs, SQLQueryComponent.standalone("IN"), list +)
\ No newline at end of file diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListExpression.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListExpression.kt new file mode 100644 index 0000000..e1522d0 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListExpression.kt @@ -0,0 +1,22 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement + +data class ListExpression<T, R>( + val elements: List<T>, + val dbType: DBType<T, R> +) : Operand<List<T>, List<R>> { + override fun asSql(): String { + return elements.joinToString(prefix = "(", postfix = ")") { "?" } + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + for (element in elements) { + dbType.set(stmt, index, element) + index++ + } + return index + } +} 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> +) : 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..b085103 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt @@ -0,0 +1,10 @@ +package moe.nea.ledger.database.sql + +interface Operand<T, + /** + * The db sided type (or a rough equivalence). + * @see moe.nea.ledger.database.DBType Raw type parameter + */ + Raw> : 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..77d63d3 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt @@ -0,0 +1,45 @@ +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 composite(vararg elements: SQLQueryComponent): SQLQueryComponent { + return object : SQLQueryComponent { + override fun asSql(): String { + return elements.joinToString(" ") { it.asSql() } + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + var index = startIndex + for (element in elements) { + val lastIndex = index + index = element.appendToStatement(stmt, index) + require(lastIndex <= index) { "$element just tried to go back in time $index < $lastIndex" } + } + return index + + } + } + } + + 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..2eb54fd --- /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<SQLQueryComponent>.concatToFilledPreparedStatement(connection: Connection): PreparedStatement { + val query = StringBuilder() + for (element in this) { + if (query.isNotEmpty()) { + query.append(" ") + } + query.append(element.asSql()) + } + val statement = connection.prepareAndLog(query.toString()) + 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/Selectable.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Selectable.kt new file mode 100644 index 0000000..8241a9d --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/Selectable.kt @@ -0,0 +1,17 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.Column +import moe.nea.ledger.database.DBType + +/** + * Something that can be selected. Like a column, or an expression thereof + */ +interface Selectable<T, Raw> : SQLQueryComponent, IntoSelectable<T> { + override fun asSelectable(): Selectable<T, Raw> { + return this + } + + val dbType: DBType<T, Raw> + fun guessColumn(): Column<T, *>? +} + 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..b8d3690 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt @@ -0,0 +1,17 @@ +package moe.nea.ledger.database.sql + +import java.sql.PreparedStatement + +/** + * As opposed to just any [Operand<*, String>][Operand], this string operand represents a string operand that is part of the query, as opposed to potentially the state of a column. + */ +data class StringOperand(val value: String) : Operand<String, String> { + 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 diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/TypedOperand.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/TypedOperand.kt new file mode 100644 index 0000000..8a1f723 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/TypedOperand.kt @@ -0,0 +1,7 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.DBType + +interface TypedOperand<T, Raw> : Operand<T, Raw> { + val dbType: DBType<T, Raw> +} diff --git a/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ValuedOperand.kt b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ValuedOperand.kt new file mode 100644 index 0000000..714b4b5 --- /dev/null +++ b/database/core/src/main/kotlin/moe/nea/ledger/database/sql/ValuedOperand.kt @@ -0,0 +1,15 @@ +package moe.nea.ledger.database.sql + +import moe.nea.ledger.database.DBType +import java.sql.PreparedStatement + +class ValuedOperand<T, R>(val dbType: DBType<T, R>, val value: T) : Operand<T, R> { + override fun asSql(): String { + return "?" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + dbType.set(stmt, startIndex, value) + return startIndex + 1 + } +} diff --git a/database/impl/build.gradle.kts b/database/impl/build.gradle.kts new file mode 100644 index 0000000..17a7a5a --- /dev/null +++ b/database/impl/build.gradle.kts @@ -0,0 +1,12 @@ +plugins { + `java-library` + kotlin("jvm") +} + +dependencies { + api(project(":database:core")) +} + +java { + toolchain.languageVersion.set(JavaLanguageVersion.of(8)) +} diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt new file mode 100644 index 0000000..e2530cc --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt @@ -0,0 +1,32 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.ItemChange +import moe.nea.ledger.ItemId +import moe.nea.ledger.TransactionType +import moe.nea.ledger.database.columns.DBDouble +import moe.nea.ledger.database.columns.DBEnum +import moe.nea.ledger.database.columns.DBString +import moe.nea.ledger.database.columns.DBUlid +import moe.nea.ledger.database.columns.DBUuid + +object DBLogEntry : Table("LogEntry") { + val transactionId = column("transactionId", DBUlid) + val type = column("type", DBEnum<TransactionType>()) + val profileId = column("profileId", DBUuid) + val playerId = column("playerId", DBUuid) +} + +object DBItemEntry : Table("ItemEntry") { + val transactionId = column("transactionId", DBUlid) // TODO: add foreign keys + val mode = column("mode", DBEnum<ItemChange.ChangeDirection>()) + val itemId = column("item", DBString.mapped(ItemId::string, ::ItemId)) + val size = column("size", DBDouble) + + fun objMap(result: ResultRow): ItemChange { + return ItemChange( + result[itemId], + result[size], + result[mode], + ) + } +} diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt new file mode 100644 index 0000000..9739978 --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt @@ -0,0 +1,68 @@ +package moe.nea.ledger.database + +import java.sql.Connection + +interface DBUpgrade { + val toVersion: Long + val fromVersion get() = toVersion - 1 + fun performUpgrade(connection: Connection) + + companion object { + + fun performUpgrades( + connection: Connection, + upgrades: Iterable<DBUpgrade>, + ) { + for (upgrade in upgrades) { + upgrade.performUpgrade(connection) + } + } + + fun performUpgradeChain( + connection: Connection, + from: Long, to: Long, + upgrades: Iterable<DBUpgrade>, + afterEach: (newVersion: Long) -> Unit, + ) { + val table = buildLookup(upgrades) + for (version in (from + 1)..(to)) { + val currentUpgrades = table[version] ?: listOf() + println("Scheduled ${currentUpgrades.size} upgrades to reach DB version $version") + performUpgrades(connection, currentUpgrades) + afterEach(version) + } + } + + fun buildLookup(upgrades: Iterable<DBUpgrade>): Map<Long, List<DBUpgrade>> { + return upgrades.groupBy { it.toVersion } + } + + fun createTable(to: Long, table: Table, vararg columns: Column<*, *>): DBUpgrade { + require(columns.all { it in table.columns }) + return of("Create table ${table}", to) { + table.createIfNotExists(it, columns.toList()) + } + } + + fun addColumns(to: Long, table: Table, vararg columns: Column<*, *>): DBUpgrade { + return of("Add columns to table $table", to) { + table.alterTableAddColumns(it, columns.toList()) + } + } + + fun of(name: String, to: Long, block: (Connection) -> Unit): DBUpgrade { + return object : DBUpgrade { + override val toVersion: Long + get() = to + + override fun performUpgrade(connection: Connection) { + block(connection) + } + + override fun toString(): String { + return name + } + } + } + } +}
\ No newline at end of file diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt new file mode 100644 index 0000000..e4b34c8 --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt @@ -0,0 +1,57 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.columns.DBString +import java.io.File +import java.sql.Connection +import java.sql.DriverManager + +class Database(val dataFolder: File) { + lateinit var connection: Connection + + object MetaTable : Table("LedgerMeta") { + val key = column("key", DBString) + val value = column("value", DBString) + + init { + unique(key) + } + } + + data class MetaKey(val name: String) { + companion object { + val DATABASE_VERSION = MetaKey("databaseVersion") + val LAST_LAUNCH = MetaKey("lastLaunch") + } + } + + fun setMetaKey(key: MetaKey, value: String) { + MetaTable.insert(connection, Table.OnConflict.REPLACE) { + it[MetaTable.key] = key.name + it[MetaTable.value] = value + } + } + + val databaseVersion: Long = 1 + + fun loadAndUpgrade() { + connection = DriverManager.getConnection("jdbc:sqlite:${dataFolder.resolve("database.db")}") + MetaTable.createIfNotExists(connection) + val meta = MetaTable.selectAll(connection).associate { MetaKey(it[MetaTable.key]) to it[MetaTable.value] } + val lastLaunch = meta[MetaKey.LAST_LAUNCH]?.toLong() ?: 0L + println("Last launch $lastLaunch") + setMetaKey(MetaKey.LAST_LAUNCH, System.currentTimeMillis().toString()) + + val oldVersion = meta[MetaKey.DATABASE_VERSION]?.toLong() ?: -1 + println("Old Database Version: $oldVersion; Current version: $databaseVersion") + if (oldVersion > databaseVersion) + error("Outdated software. Database is newer than me!") + // TODO: create a backup if there is a db version upgrade happening + DBUpgrade.performUpgradeChain( + connection, oldVersion, databaseVersion, + Upgrades().upgrades + ) { version -> + setMetaKey(MetaKey.DATABASE_VERSION, version.toString()) + } + } + +}
\ No newline at end of file diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt new file mode 100644 index 0000000..76dfb5d --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt @@ -0,0 +1,18 @@ +package moe.nea.ledger.database + +class Upgrades { + val upgrades = mutableListOf<DBUpgrade>() + + fun add(upgrade: DBUpgrade) = upgrades.add(upgrade) + + init { + add(DBUpgrade.createTable( + 0, DBLogEntry, + DBLogEntry.type, DBLogEntry.playerId, DBLogEntry.profileId, + DBLogEntry.transactionId)) + add(DBUpgrade.createTable( + 0, DBItemEntry, + DBItemEntry.itemId, DBItemEntry.size, DBItemEntry.mode, DBItemEntry.transactionId + )) + } +}
\ No newline at end of file diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot b/database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot new file mode 100644 index 0000000..d932f6a --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot @@ -0,0 +1,23 @@ +digraph { + node [shape=plain]; + rankdir=LR; + entry [label=< + <table border="0" cellborder="1" cellspacing="0"> + <tr><td>Log Entry</td></tr> + <tr><td port="player">playerId</td></tr> + <tr><td port="profile">profileId</td></tr> + <tr><td port="date">timestamp</td></tr> + <tr><td port="type">Type</td></tr> + </table> + >]; + item [label=< + <table border="0" cellborder="1" cellspacing="0"> + <tr><td>Item Stack</td><tr> + <tr><td port="transaction">Transaction</td></tr> + <tr><td port="id">Item ID</td></tr> + <tr><td port="count">Count</td></tr> + <tr><td port="direction">Transfer Direction</td></tr> + </table> + >]; +// item:transaction -> entry; +}
\ No newline at end of file |