refactor: Extract database models to own modules

6 files changed, 212 insertions, 0 deletions

diff --git a/database/impl/build.gradle.kts b/database/impl/build.gradle.kts
new file mode 100644
index 0000000..17a7a5a
--- /dev/null
+++ b/database/impl/build.gradle.kts
@@ -0,0 +1,12 @@
+plugins {
+	`java-library`
+	kotlin("jvm")
+}
+
+dependencies {
+	api(project(":database:core"))
+}
+
+java {
+	toolchain.languageVersion.set(JavaLanguageVersion.of(8))
+}
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<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)
+
+	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<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/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<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/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=<
+        <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