From 305178f5511bbf7b4499d7cf4142a067ab42e2fc Mon Sep 17 00:00:00 2001 From: Linnea Gräf Date: Thu, 16 Jan 2025 21:49:20 +0100 Subject: refactor: Extract database models to own modules --- .../kotlin/moe/nea/ledger/database/DBLogEntry.kt | 32 ++++++++++ .../kotlin/moe/nea/ledger/database/DBUpgrade.kt | 68 ++++++++++++++++++++++ .../kotlin/moe/nea/ledger/database/Database.kt | 57 ++++++++++++++++++ .../kotlin/moe/nea/ledger/database/Upgrades.kt | 20 +++++++ .../main/kotlin/moe/nea/ledger/database/schema.dot | 23 ++++++++ 5 files changed, 200 insertions(+) create mode 100644 database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt create mode 100644 database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt create mode 100644 database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt create mode 100644 database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt create mode 100644 database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot (limited to 'database/impl/src/main/kotlin') diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt new file mode 100644 index 0000000..e2530cc --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBLogEntry.kt @@ -0,0 +1,32 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.ItemChange +import moe.nea.ledger.ItemId +import moe.nea.ledger.TransactionType +import moe.nea.ledger.database.columns.DBDouble +import moe.nea.ledger.database.columns.DBEnum +import moe.nea.ledger.database.columns.DBString +import moe.nea.ledger.database.columns.DBUlid +import moe.nea.ledger.database.columns.DBUuid + +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) + + fun objMap(result: ResultRow): ItemChange { + return ItemChange( + result[itemId], + result[size], + result[mode], + ) + } +} diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/DBUpgrade.kt new file mode 100644 index 0000000..7d1782a --- /dev/null +++ b/database/impl/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/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt new file mode 100644 index 0000000..e4b34c8 --- /dev/null +++ b/database/impl/src/main/kotlin/moe/nea/ledger/database/Database.kt @@ -0,0 +1,57 @@ +package moe.nea.ledger.database + +import moe.nea.ledger.database.columns.DBString +import java.io.File +import java.sql.Connection +import java.sql.DriverManager + +class Database(val dataFolder: File) { + lateinit var connection: Connection + + object MetaTable : Table("LedgerMeta") { + val key = column("key", DBString) + val value = column("value", DBString) + + init { + unique(key) + } + } + + 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:${dataFolder.resolve("database.db")}") + MetaTable.createIfNotExists(connection) + 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") + setMetaKey(MetaKey.LAST_LAUNCH, System.currentTimeMillis().toString()) + + val oldVersion = meta[MetaKey.DATABASE_VERSION]?.toLong() ?: -1 + println("Old Database Version: $oldVersion; Current version: $databaseVersion") + if (oldVersion > databaseVersion) + error("Outdated software. Database is newer than me!") + // 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()) + } + } + +} \ No newline at end of file diff --git a/database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt b/database/impl/src/main/kotlin/moe/nea/ledger/database/Upgrades.kt new file mode 100644 index 0000000..e83abe7 --- /dev/null +++ b/database/impl/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/database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot b/database/impl/src/main/kotlin/moe/nea/ledger/database/schema.dot new file mode 100644 index 0000000..d932f6a --- /dev/null +++ b/database/impl/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