refactor: Extract database to its own module

43 files changed, 755 insertions, 575 deletions

diff --git a/basetypes/build.gradle.kts b/basetypes/build.gradle.kts
new file mode 100644
index 0000000..f4b1a8b
--- /dev/null
+++ b/basetypes/build.gradle.kts
@@ -0,0 +1,12 @@
+plugins {
+	`java-library`
+	kotlin("jvm")
+}
+
+java {
+	toolchain.languageVersion.set(JavaLanguageVersion.of(8))
+}
+
+dependencies {
+	implementation("io.azam.ulidj:ulidj:1.0.4")
+}
diff --git a/basetypes/src/main/kotlin/moe/nea/ledger/utils/ULIDWrapper.kt b/basetypes/src/main/kotlin/moe/nea/ledger/utils/ULIDWrapper.kt
new file mode 100644
index 0000000..b8c5d3b
--- /dev/null
+++ b/basetypes/src/main/kotlin/moe/nea/ledger/utils/ULIDWrapper.kt
@@ -0,0 +1,27 @@
+package moe.nea.ledger.utils
+
+import io.azam.ulidj.ULID
+import java.time.Instant
+import kotlin.random.Random
+
+@JvmInline
+value class ULIDWrapper(
+	val wrapped: String
+) {
+	companion object {
+		fun createULIDAt(timestamp: Instant): ULIDWrapper {
+			return ULIDWrapper(ULID.generate(
+				timestamp.toEpochMilli(),
+				Random.nextBytes(10)
+			))
+		}
+	}
+
+	fun getTimestamp(): Instant {
+		return Instant.ofEpochMilli(ULID.getTimestamp(wrapped))
+	}
+
+	init {
+		require(ULID.isValid(wrapped))
+	}
+}
\ No newline at end of file
diff --git a/basetypes/src/main/kotlin/moe/nea/ledger/utils/UUIDUtil.kt b/basetypes/src/main/kotlin/moe/nea/ledger/utils/UUIDUtil.kt
new file mode 100644
index 0000000..92a29f7
--- /dev/null
+++ b/basetypes/src/main/kotlin/moe/nea/ledger/utils/UUIDUtil.kt
@@ -0,0 +1,41 @@
+package moe.nea.ledger.utils
+
+import java.util.UUID
+import kotlin.uuid.ExperimentalUuidApi
+import kotlin.uuid.Uuid
+import kotlin.uuid.toJavaUuid
+
+object UUIDUtil {
+	@OptIn(ExperimentalUuidApi::class)
+	fun parsePotentiallyDashlessUUID(str: String): UUID {
+		val bytes = ByteArray(16)
+		var i = -1
+		var bi = 0
+		while (++i < str.length) {
+			val char = str[i]
+			if (char == '-') {
+				if (bi != 4 && bi != 6 && bi != 8 && bi != 10) {
+					error("Unexpected dash in UUID: $str")
+				}
+				continue
+			}
+			val current = parseHexDigit(str, char)
+			++i
+			if (i >= str.length)
+				error("Unexpectedly short UUID: $str")
+			val next = parseHexDigit(str, str[i])
+			bytes[bi++] = (current * 16 or next).toByte()
+		}
+		if (bi != 16)
+			error("Unexpectedly short UUID: $str")
+		return Uuid.fromByteArray(bytes).toJavaUuid()
+	}
+
+	private fun parseHexDigit(str: String, char: Char): Int {
+		val d = char - '0'
+		if (d < 10) return d
+		val h = char - 'a'
+		if (h < 6) return 10 + h
+		error("Unexpected hex digit $char in UUID: $str")
+	}
+}
\ No newline at end of file
diff --git a/build.gradle.kts b/build.gradle.kts
index a431b42..e2ada17 100644
--- a/build.gradle.kts
+++ b/build.gradle.kts
@@ -125,6 +125,7 @@ dependencies {
 	shadowImpl("org.notenoughupdates.moulconfig:legacy:3.0.0-beta.9")
 	shadowImpl("io.azam.ulidj:ulidj:1.0.4")
 	shadowImpl(project(":dependency-injection"))
+	shadowImpl(project(":database:core"))
 	shadowImpl("moe.nea:libautoupdate:1.3.1")
 	runtimeOnly("me.djtheredstoner:DevAuth-forge-legacy:1.2.1")
 	testImplementation("org.junit.jupiter:junit-jupiter:5.9.2")
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<T> @Deprecated("Use Table.column instead") constructor(
+	val table: Table,
+	val name: String,
+	val type: DBType<T>
+) {
+	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<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..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<T> {
+	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> {
+		return object : DBType<R> {
+			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<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..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<Column<*>>,
+	var table: Table,
+	var limit: UInt? = null,
+	var skip: UInt? = null,
+	val joins: MutableList<Join> = mutableListOf(),
+	val conditions: MutableList<BooleanExpression> = mutableListOf(),
+//	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: 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<ResultRow> {
+		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<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.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<Column<*>, *>) {
+	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
+	}
+}
\ 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<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> column(name: String, type: DBType<T>): Column<T> {
+		@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.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<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..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<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..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<T : Enum<T>>(
+	val type: Class<T>,
+) : DBType<T> {
+	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..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<Instant> {
+	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<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..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<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..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<ULIDWrapper> {
+	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<