refactor: Extract database to its own module

30 files changed, 651 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..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
--- /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> {
+	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>
+) : 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 <T> column(column: Column<T>): Operand<T> = 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<T>(val column: Column<T>) : Operand<T> {
+	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<T>(val left: Operand<T>, 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>
+) : 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<T> : 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<SQLQueryComponent>.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<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