From d708dca108dcbfe3d67dfe90c27de9cdb41184a6 Mon Sep 17 00:00:00 2001 From: Linnea Gräf Date: Sat, 7 Dec 2024 00:50:33 +0100 Subject: feat: Add SQLITE database entry logging --- .../kotlin/moe/nea/ledger/database/DBLogEntry.kt | 19 ++++ .../kotlin/moe/nea/ledger/database/DBSchema.kt | 122 ++++++++++++++++++++- .../kotlin/moe/nea/ledger/database/DBUpgrade.kt | 68 ++++++++++++ .../kotlin/moe/nea/ledger/database/Database.kt | 41 +++++-- .../kotlin/moe/nea/ledger/database/Upgrades.kt | 20 ++++ src/main/kotlin/moe/nea/ledger/database/schema.dot | 23 ++++ 6 files changed, 279 insertions(+), 14 deletions(-) create mode 100644 src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt create mode 100644 src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt create mode 100644 src/main/kotlin/moe/nea/ledger/database/Upgrades.kt create mode 100644 src/main/kotlin/moe/nea/ledger/database/schema.dot (limited to 'src/main/kotlin/moe/nea/ledger/database') 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()) + 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()) + 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 @@ -16,6 +17,54 @@ interface DBType { fun get(result: ResultSet, index: Int): T fun set(stmt: PreparedStatement, index: Int, value: T) fun getName(): String = javaClass.simpleName + fun mapped( + from: (R) -> T, + to: (T) -> R, + ): DBType { + return object : DBType { + 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 { + 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 { + 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 { @@ -31,6 +80,45 @@ object DBString : DBType { } } +class DBEnum>( + val type: Class, +) : DBType { + companion object { + inline operator fun > invoke(): DBEnum { + 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 { + 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 { override val dbType: String get() = "INTEGER" @@ -57,12 +145,12 @@ object DBInstant : DBType { } } -// TODO: add table class Column @Deprecated("Use Table.column instead") constructor(val name: String, val type: DBType) { val sqlName get() = "`$name`" } interface Constraint { + val affectedColumns: Collection> fun asSQL(): String } @@ -71,6 +159,9 @@ class UniqueConstraint(val columns: List>) : Constraint { require(columns.isNotEmpty()) } + override val affectedColumns: Collection> + 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> = columns + ) { val properties = mutableListOf() - 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> + ) { + 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, + ) { + for (upgrade in upgrades) { + upgrade.performUpgrade(connection) + } + } + + fun performUpgradeChain( + connection: Connection, + from: Long, to: Long, + upgrades: Iterable, + 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): Map> { + 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() + + 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=< +
+ + + + + +
Log Entry
playerId
profileId
timestamp
Type
+ >]; + item [label=< + + + + + + +
Item Stack
Transaction
Item ID
Count
Transfer Direction
+ >]; +// item:transaction -> entry; +} \ No newline at end of file -- cgit