aboutsummaryrefslogtreecommitdiff
path: root/database/core
diff options
context:
space:
mode:
Diffstat (limited to 'database/core')
-rw-r--r--database/core/build.gradle.kts12
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/Column.kt31
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/Constraint.kt6
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt13
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/DBType.kt43
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/InsertStatement.kt7
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/Query.kt111
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/ResultRow.kt22
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/Table.kt103
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/UniqueConstraint.kt14
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBDouble.kt18
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBEnum.kt31
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInstant.kt19
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBInt.kt18
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBString.kt18
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUlid.kt21
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/columns/DBUuid.kt20
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ANDExpression.kt26
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/BooleanExpression.kt3
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/Clause.kt12
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ClauseBuilder.kt25
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ColumnOperand.kt18
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/EqualsClause.kt16
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/IntoSelectable.kt5
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/Join.kt19
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanEqualsExpression.kt15
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/LessThanExpression.kt15
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/LikeClause.kt17
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListClause.kt8
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ListExpression.kt22
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ORExpression.kt23
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/Operand.kt10
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryComponent.kt45
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/SQLQueryGenerator.kt25
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/Selectable.kt17
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/StringOperand.kt17
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/TypedOperand.kt7
-rw-r--r--database/core/src/main/kotlin/moe/nea/ledger/database/sql/ValuedOperand.kt15
38 files changed, 867 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
+ }
+}