feat: Add SQLITE database entry logging

6 files changed, 279 insertions, 14 deletions

diff --git a/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt b/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt
new file mode 100644
index 0000000..77ac215
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt
@@ -0,0 +1,19 @@
+package moe.nea.ledger.database
+
+import moe.nea.ledger.ItemChange
+import moe.nea.ledger.ItemId
+import moe.nea.ledger.TransactionType
+
+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)
+}
diff --git a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
index 5c9099c..dee99e4 100644
--- a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
+++ b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt
@@ -1,10 +1,11 @@
 package moe.nea.ledger.database
 
+import moe.nea.ledger.UUIDUtil
 import java.sql.Connection
 import java.sql.PreparedStatement
 import java.sql.ResultSet
-import java.sql.Timestamp
 import java.time.Instant
+import java.util.UUID
 
 interface DBSchema {
 	val tables: List<Table>
@@ -16,6 +17,54 @@ interface DBType<T> {
 	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))
+			}
+		}
+	}
+}
+
+object DBUuid : DBType<UUID> {
+	override val dbType: String
+		get() = "TEXT"
+
+	override fun get(result: ResultSet, index: Int): UUID {
+		return UUIDUtil.parseDashlessUuid(result.getString(index))
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: UUID) {
+		stmt.setString(index, value.toString())
+	}
+}
+
+object DBUlid : DBType<UUIDUtil.ULIDWrapper> {
+	override val dbType: String
+		get() = "TEXT"
+
+	override fun get(result: ResultSet, index: Int): UUIDUtil.ULIDWrapper {
+		val text = result.getString(index)
+		return UUIDUtil.ULIDWrapper(text)
+	}
+
+	override fun set(stmt: PreparedStatement, index: Int, value: UUIDUtil.ULIDWrapper) {
+		stmt.setString(index, value.wrapped)
+	}
 }
 
 object DBString : DBType<String> {
@@ -31,6 +80,45 @@ object DBString : DBType<String> {
 	}
 }
 
+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)
+	}
+}
+
+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)
+	}
+}
+
 object DBInt : DBType<Long> {
 	override val dbType: String
 		get() = "INTEGER"
@@ -57,12 +145,12 @@ object DBInstant : DBType<Instant> {
 	}
 }
 
-// TODO: add table
 class Column<T> @Deprecated("Use Table.column instead") constructor(val name: String, val type: DBType<T>) {
 	val sqlName get() = "`$name`"
 }
 
 interface Constraint {
+	val affectedColumns: Collection<Column<*>>
 	fun asSQL(): String
 }
 
@@ -71,6 +159,9 @@ class UniqueConstraint(val columns: List<Column<*>>) : Constraint {
 		require(columns.isNotEmpty())
 	}
 
+	override val affectedColumns: Collection<Column<*>>
+		get() = columns
+
 	override fun asSQL(): String {
 		return "UNIQUE (${columns.joinToString() { it.sqlName }})"
 	}
@@ -111,18 +202,37 @@ abstract class Table(val name: String) {
 		println(string)
 	}
 
-	fun createIfNotExists(connection: Connection) {
+	fun createIfNotExists(
+		connection: Connection,
+		filteredColumns: List<Column<*>> = columns
+	) {
 		val properties = mutableListOf<String>()
-		for (column in columns) {
+		for (column in filteredColumns) {
 			properties.add("${column.sqlName} ${column.type.dbType}")
 		}
+		val columnSet = filteredColumns.toSet()
 		for (constraint in constraints) {
-			properties.add(constraint.asSQL())
+			if (columnSet.containsAll(constraint.affectedColumns)) {
+				properties.add(constraint.asSQL())
+			}
 		}
-		connection.prepareAndLog("CREATE TABLE IF NOT EXISTS `$name` (" + properties.joinToString() + ")")
+		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,
diff --git a/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt b/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt
new file mode 100644
index 0000000..7d1782a
--- /dev/null
+++ b/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/src/main/kotlin/moe/nea/ledger/database/Database.kt b/src/main/kotlin/moe/nea/ledger/database/Database.kt
index 9dc4e00..a77ea30 100644
--- a/src/main/kotlin/moe/nea/ledger/database/Database.kt
+++ b/src/main/kotlin/moe/nea/ledger/database/Database.kt
@@ -1,10 +1,11 @@
 package moe.nea.ledger.database
 
 import moe.nea.ledger.Ledger
+import java.sql.Connection
 import java.sql.DriverManager
 
-object Database {
-	val connection = DriverManager.getConnection("jdbc:sqlite:${Ledger.dataFolder.resolve("database.db")}")
+class Database {
+	lateinit var connection: Connection
 
 	object MetaTable : Table("LedgerMeta") {
 		val key = column("key", DBString)
@@ -15,14 +16,38 @@ object Database {
 		}
 	}
 
-	fun init() {
+	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:${Ledger.dataFolder.resolve("database.db")}")
 		MetaTable.createIfNotExists(connection)
-		val meta = MetaTable.selectAll(connection).associate { it[MetaTable.key] to it[MetaTable.value] }
-		val lastLaunch = meta["lastLaunch"]?.toLong() ?: 0L
+		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")
-		MetaTable.insert(connection, Table.OnConflict.REPLACE) {
-			it[MetaTable.key] = "lastLaunch"
-			it[MetaTable.value] = System.currentTimeMillis().toString()
+		setMetaKey(MetaKey.LAST_LAUNCH, System.currentTimeMillis().toString())
+
+		val oldVersion = meta[MetaKey.DATABASE_VERSION]?.toLong() ?: -1
+		println("Old Database Version: $oldVersion; Current version: $databaseVersion")
+		// 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())
 		}
 	}
 
diff --git a/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt b/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt
new file mode 100644
index 0000000..e83abe7
--- /dev/null
+++ b/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt
@@ -0,0 +1,20 @@
+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/src/main/kotlin/moe/nea/ledger/database/schema.dot b/src/main/kotlin/moe/nea/ledger/database/schema.dot
new file mode 100644
index 0000000..d932f6a
--- /dev/null
+++ b/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