From ef11dd51a61d25bc8722cc844358869b00a5369c Mon Sep 17 00:00:00 2001 From: Linnea Gräf Date: Sun, 8 Dec 2024 03:42:07 +0100 Subject: feat: Add query command --- src/main/kotlin/moe/nea/ledger/ConfigCommand.kt | 2 +- src/main/kotlin/moe/nea/ledger/ItemChange.kt | 44 +++++ src/main/kotlin/moe/nea/ledger/Ledger.kt | 1 + src/main/kotlin/moe/nea/ledger/LedgerLogger.kt | 6 +- src/main/kotlin/moe/nea/ledger/NumberUtil.kt | 42 +++- src/main/kotlin/moe/nea/ledger/QueryCommand.kt | 164 ++++++++++++++++ src/main/kotlin/moe/nea/ledger/UUIDUtil.kt | 4 + .../kotlin/moe/nea/ledger/database/DBSchema.kt | 217 ++++++++++++++++++++- src/main/kotlin/moe/nea/ledger/utils/DI.kt | 3 + 9 files changed, 469 insertions(+), 14 deletions(-) create mode 100644 src/main/kotlin/moe/nea/ledger/QueryCommand.kt diff --git a/src/main/kotlin/moe/nea/ledger/ConfigCommand.kt b/src/main/kotlin/moe/nea/ledger/ConfigCommand.kt index 5222e3b..5b964c8 100644 --- a/src/main/kotlin/moe/nea/ledger/ConfigCommand.kt +++ b/src/main/kotlin/moe/nea/ledger/ConfigCommand.kt @@ -10,7 +10,7 @@ class ConfigCommand : CommandBase() { } override fun getCommandName(): String { - return "ledger" + return "ledgerconfig" } override fun getCommandUsage(sender: ICommandSender?): String { diff --git a/src/main/kotlin/moe/nea/ledger/ItemChange.kt b/src/main/kotlin/moe/nea/ledger/ItemChange.kt index a8bb7e8..834cd2b 100644 --- a/src/main/kotlin/moe/nea/ledger/ItemChange.kt +++ b/src/main/kotlin/moe/nea/ledger/ItemChange.kt @@ -1,16 +1,52 @@ package moe.nea.ledger +import moe.nea.ledger.database.DBItemEntry +import moe.nea.ledger.database.ResultRow +import net.minecraft.event.HoverEvent +import net.minecraft.util.ChatComponentText +import net.minecraft.util.ChatStyle +import net.minecraft.util.EnumChatFormatting +import net.minecraft.util.IChatComponent + data class ItemChange( val itemId: ItemId, val count: Double, val direction: ChangeDirection, ) { + fun formatChat(): IChatComponent { + return ChatComponentText(" ") + .appendSibling(direction.chatFormat) + .appendText(" ") + .appendSibling(ChatComponentText("$count").setChatStyle(ChatStyle().setColor(EnumChatFormatting.WHITE))) + .appendSibling(ChatComponentText("x").setChatStyle(ChatStyle().setColor(EnumChatFormatting.DARK_GRAY))) + .appendText(" ") + .appendSibling(ChatComponentText(itemId.string).setChatStyle(ChatStyle().setParentStyle(ChatStyle().setColor( + EnumChatFormatting.WHITE)))) + } + enum class ChangeDirection { GAINED, TRANSFORM, SYNC, CATALYST, LOST; + + val chatFormat by lazy { formatChat0() } + private fun formatChat0(): IChatComponent { + val (text, color) = when (this) { + GAINED -> "+" to EnumChatFormatting.GREEN + TRANSFORM -> "~" to EnumChatFormatting.YELLOW + SYNC -> "=" to EnumChatFormatting.BLUE + CATALYST -> "*" to EnumChatFormatting.DARK_PURPLE + LOST -> "-" to EnumChatFormatting.RED + } + return ChatComponentText(text) + .setChatStyle( + ChatStyle() + .setColor(color) + .setChatHoverEvent(HoverEvent(HoverEvent.Action.SHOW_TEXT, + ChatComponentText(name).setChatStyle(ChatStyle().setColor(color))))) + } } companion object { @@ -29,5 +65,13 @@ data class ItemChange( fun loseCoins(number: Double): ItemChange { return lose(ItemId.COINS, number) } + + fun from(result: ResultRow): ItemChange { + return ItemChange( + result[DBItemEntry.itemId], + result[DBItemEntry.size], + result[DBItemEntry.mode], + ) + } } } \ No newline at end of file diff --git a/src/main/kotlin/moe/nea/ledger/Ledger.kt b/src/main/kotlin/moe/nea/ledger/Ledger.kt index 5dafe4a..b3afd37 100644 --- a/src/main/kotlin/moe/nea/ledger/Ledger.kt +++ b/src/main/kotlin/moe/nea/ledger/Ledger.kt @@ -108,6 +108,7 @@ class Ledger { LogChatCommand::class.java, MinionDetection::class.java, NpcDetection::class.java, + QueryCommand::class.java, VisitorDetection::class.java, ) di.instantiateAll() diff --git a/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt b/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt index fd439c9..5005040 100644 --- a/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt +++ b/src/main/kotlin/moe/nea/ledger/LedgerLogger.kt @@ -9,6 +9,7 @@ import moe.nea.ledger.events.ChatReceived import moe.nea.ledger.utils.Inject import net.minecraft.client.Minecraft import net.minecraft.util.ChatComponentText +import net.minecraft.util.IChatComponent import net.minecraftforge.fml.common.eventhandler.SubscribeEvent import net.minecraftforge.fml.common.gameevent.TickEvent.ClientTickEvent import java.io.File @@ -17,8 +18,9 @@ import java.util.Date import java.util.UUID class LedgerLogger { - fun printOut(text: String) { - Minecraft.getMinecraft().ingameGUI?.chatGUI?.printChatMessage(ChatComponentText(text)) + fun printOut(text: String) = printOut(ChatComponentText(text)) + fun printOut(comp: IChatComponent) { + Minecraft.getMinecraft().ingameGUI?.chatGUI?.printChatMessage(comp) } val profileIdPattern = diff --git a/src/main/kotlin/moe/nea/ledger/NumberUtil.kt b/src/main/kotlin/moe/nea/ledger/NumberUtil.kt index b0e47db..008cfbf 100644 --- a/src/main/kotlin/moe/nea/ledger/NumberUtil.kt +++ b/src/main/kotlin/moe/nea/ledger/NumberUtil.kt @@ -1,5 +1,17 @@ package moe.nea.ledger +import net.minecraft.event.ClickEvent +import net.minecraft.event.HoverEvent +import net.minecraft.util.ChatComponentText +import net.minecraft.util.ChatStyle +import net.minecraft.util.EnumChatFormatting +import net.minecraft.util.IChatComponent +import java.time.Instant +import java.time.LocalDateTime +import java.time.ZoneId +import java.time.format.DateTimeFormatter +import java.time.format.DateTimeFormatterBuilder +import java.time.temporal.ChronoField import java.util.regex.Matcher import java.util.regex.Pattern @@ -66,4 +78,32 @@ fun parseShortNumber(string: String): Double { inline fun Pattern.useMatcher(string: String, block: Matcher.() -> T): T? = matcher(string).takeIf { it.matches() }?.let(block) -fun String.unformattedString(): String = replace("§.".toRegex(), "") \ No newline at end of file +fun String.unformattedString(): String = replace("§.".toRegex(), "") + +val timeFormat: DateTimeFormatter = DateTimeFormatterBuilder() + .appendValue(ChronoField.DAY_OF_MONTH, 2) + .appendLiteral(".") + .appendValue(ChronoField.MONTH_OF_YEAR, 2) + .appendLiteral(".") + .appendValue(ChronoField.YEAR, 4) + .appendLiteral(" ") + .appendValue(ChronoField.HOUR_OF_DAY, 2) + .appendLiteral(":") + .appendValue(ChronoField.MINUTE_OF_HOUR, 2) + .appendLiteral(":") + .appendValue(ChronoField.SECOND_OF_MINUTE, 2) + .toFormatter() + +fun Instant.formatChat(): IChatComponent { + val text = ChatComponentText( + LocalDateTime.ofInstant(this, ZoneId.systemDefault()).format(timeFormat) + ) + text.setChatStyle( + ChatStyle() + .setChatClickEvent( + ClickEvent(ClickEvent.Action.OPEN_URL, "https://time.is/${this.epochSecond}")) + .setChatHoverEvent( + HoverEvent(HoverEvent.Action.SHOW_TEXT, ChatComponentText("Click to show on time.is"))) + .setColor(EnumChatFormatting.AQUA)) + return text +} diff --git a/src/main/kotlin/moe/nea/ledger/QueryCommand.kt b/src/main/kotlin/moe/nea/ledger/QueryCommand.kt new file mode 100644 index 0000000..e5cbb7f --- /dev/null +++ b/src/main/kotlin/moe/nea/ledger/QueryCommand.kt @@ -0,0 +1,164 @@ +package moe.nea.ledger + +import moe.nea.ledger.database.ANDExpression +import moe.nea.ledger.database.BooleanExpression +import moe.nea.ledger.database.Clause +import moe.nea.ledger.database.DBItemEntry +import moe.nea.ledger.database.DBLogEntry +import moe.nea.ledger.database.Database +import moe.nea.ledger.utils.Inject +import net.minecraft.command.CommandBase +import net.minecraft.command.ICommandSender +import net.minecraft.util.ChatComponentText +import net.minecraft.util.ChatStyle +import net.minecraft.util.EnumChatFormatting + +class QueryCommand : CommandBase() { + override fun canCommandSenderUseCommand(sender: ICommandSender?): Boolean { + return true + } + + override fun getCommandName(): String { + return "ledger" + } + + override fun getCommandUsage(sender: ICommandSender?): String { + return "" + } + + override fun getCommandAliases(): List { + return listOf("lgq") + } + + @Inject + lateinit var logger: LedgerLogger + + override fun processCommand(sender: ICommandSender, args: Array) { + if (args.isEmpty()) { + logger.printOut("§eHere is how you can look up transactions:") + logger.printOut("") + logger.printOut("§f- §e/ledger withitem %POTATO%") + logger.printOut(" §aLook up transactions involving potatoes!") + logger.printOut("§f- §e/ledger withitem ENCHANTED_POTATO") + logger.printOut(" §aLook up transactions involving just enchanted potatoes!") + logger.printOut("§f- §e/ledger withitem %POTATO% withitem %CARROT%") + logger.printOut(" §aLook up transactions involving potatoes or carrots!") + logger.printOut("§f- §e/ledger withtype AUCTION_SOLD") + logger.printOut(" §aLook up transactions of sold auctions!") + logger.printOut("§f- §e/ledger withtype AUCTION_SOLD withitem CRIMSON%") + logger.printOut(" §aLook up sold auctions involving crimson armor pieces!") + logger.printOut("") + logger.printOut("§eFilters of the same type apply using §aOR§e and loggers of different types apply using §aAND§e.") + logger.printOut("§eYou can use % as a wildcard!") + return + } + val p = parseArgs(args) + when (p) { + is ParseResult.Success -> { + executeQuery(p) + } + + is ParseResult.UnknownFilter -> { + logger.printOut("§cUnknown filter name ${p.start}. Available filter names are: ${mFilters.keys.joinToString()}") + } + + is ParseResult.MissingArg -> { + logger.printOut("§cFilter ${p.filterM.name} is missing an argument.") + } + } + } + + @Inject + lateinit var database: Database + private fun executeQuery(parse: ParseResult.Success) { + val grouped = parse.filters + val query = DBLogEntry.from(database.connection) + .select(DBLogEntry.type, DBLogEntry.transactionId) + .join(DBItemEntry, on = Clause { column(DBLogEntry.transactionId) eq column(DBItemEntry.transactionId) }) + for (value in grouped.values) { + query.where(ANDExpression(value)) + } + query.limit(80u) + query.forEach { + val type = it[DBLogEntry.type] + val transactionId = it[DBLogEntry.transactionId] + val timestamp = transactionId.getTimestamp() + val items = DBItemEntry.selectAll(database.connection) + .where(Clause { column(DBItemEntry.transactionId) eq string(transactionId.wrapped) }) + .map { ItemChange.from(it) } + val text = ChatComponentText("") + .setChatStyle(ChatStyle().setColor(EnumChatFormatting.YELLOW)) + .appendSibling( + ChatComponentText(type.name) + .setChatStyle(ChatStyle().setColor(EnumChatFormatting.GREEN)) + ) + .appendText(" on ") + .appendSibling(timestamp.formatChat()) + .appendText("\n") + .appendSibling( + ChatComponentText(transactionId.wrapped).setChatStyle(ChatStyle().setColor(EnumChatFormatting.DARK_GRAY)) + ) + for (item in items) { + text.appendText("\n") + .appendSibling(item.formatChat()) + } + text.appendText("\n") + logger.printOut(text) + } + } + + sealed interface ParseResult { + data class UnknownFilter(val start: String) : ParseResult + data class MissingArg(val filterM: FilterM) : ParseResult + data class Success(val lastFilterM: FilterM, val filters: Map>) : ParseResult + } + + fun parseArgs(args: Array): ParseResult { + require(args.isNotEmpty()) + val arr = args.iterator() + val filters = mutableMapOf>() + var lastFilterM: FilterM? = null + while (arr.hasNext()) { + val filterName = arr.next() + val filterM = mFilters[filterName] + if (filterM == null) { + return ParseResult.UnknownFilter(filterName) + } + if (!arr.hasNext()) { + return ParseResult.MissingArg(filterM) + } + filters.getOrPut(filterM, ::mutableListOf).add(filterM.getFilter(arr.next())) + lastFilterM = filterM + } + return ParseResult.Success(lastFilterM!!, filters) + } + + + val mFilters = listOf(TypeFilter, ItemFilter).associateBy { it.name } + + object TypeFilter : FilterM { + override val name: String + get() = "withtype" + + override fun getFilter(text: String): BooleanExpression { + val preparedText = "%" + text.trim('%') + "%" + return Clause { column(DBLogEntry.type) like preparedText } + } + } + + object ItemFilter : FilterM { + override val name: String + get() = "withitem" + + override fun getFilter(text: String): BooleanExpression { + return Clause { column(DBItemEntry.itemId) like text } + } + } + + interface FilterM { + val name: String + fun getFilter(text: String): BooleanExpression +// fun tabCompleteFilter() TODO + } + +} \ No newline at end of file diff --git a/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt b/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt index ddfb8c2..5549908 100644 --- a/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt +++ b/src/main/kotlin/moe/nea/ledger/UUIDUtil.kt @@ -12,6 +12,10 @@ object UUIDUtil { value class ULIDWrapper( val wrapped: String ) { + fun getTimestamp(): Instant { + return Instant.ofEpochMilli(ULID.getTimestamp(wrapped)) + } + init { require(ULID.isValid(wrapped)) } diff --git a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt index dee99e4..492f261 100644 --- a/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt +++ b/src/main/kotlin/moe/nea/ledger/database/DBSchema.kt @@ -145,8 +145,13 @@ object DBInstant : DBType { } } -class Column @Deprecated("Use Table.column instead") constructor(val name: String, val type: DBType) { +class Column @Deprecated("Use Table.column instead") constructor( + val table: Table, + val name: String, + val type: DBType +) { val sqlName get() = "`$name`" + val qualifiedSqlName get() = table.sqlName + "." + sqlName } interface Constraint { @@ -178,7 +183,7 @@ abstract class Table(val name: String) { } protected fun column(name: String, type: DBType): Column { - @Suppress("DEPRECATION") val column = Column(name, type) + @Suppress("DEPRECATION") val column = Column(this, name, type) _mutable_columns.add(column) return column } @@ -258,9 +263,12 @@ abstract class Table(val name: String) { statement.execute() } + fun from(connection: Connection): Query { + return Query(connection, mutableListOf(), this) + } fun selectAll(connection: Connection): Query { - return Query(connection, columns, this) + return Query(connection, columns.toMutableList(), this) } } @@ -275,15 +283,196 @@ fun Connection.prepareAndLog(statement: String): PreparedStatement { return prepareStatement(statement) } +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 standalone(sql: String): SQLQueryComponent { + return object : SQLQueryComponent { + override fun asSql(): String { + return sql + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return startIndex + } + } + } + } +} + +interface BooleanExpression : SQLQueryComponent + +data class ORExpression( + val elements: List +) : 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 + } +} + + +data class ANDExpression( + val elements: List +) : BooleanExpression { + init { + require(elements.isNotEmpty()) + } + + override fun asSql(): String { + return (elements + SQLQueryComponent.standalone("TRUE")).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 + } +} + +class ClauseBuilder { + fun column(column: Column): Operand = Operand.ColumnOperand(column) + fun string(string: String): Operand.StringOperand = Operand.StringOperand(string) + infix fun Operand<*>.eq(operand: Operand<*>) = Clause.EqualsClause(this, operand) + infix fun Operand<*>.like(op: Operand.StringOperand) = Clause.LikeClause(this, op) + infix fun Operand<*>.like(op: String) = Clause.LikeClause(this, string(op)) +} + +interface Clause : BooleanExpression { + companion object { + operator fun invoke(builder: ClauseBuilder.() -> Clause): Clause { + return builder(ClauseBuilder()) + } + } + + data class EqualsClause(val left: Operand<*>, val right: Operand<*>) : Clause { // TODO: typecheck this somehow + 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 + } + } + + data class LikeClause(val left: Operand, val right: Operand.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 + } + } +} + +interface Operand : SQLQueryComponent { + data class ColumnOperand(val column: Column) : Operand { + override fun asSql(): String { + return column.qualifiedSqlName + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + return startIndex + } + } + + data class StringOperand(val value: String) : Operand { + override fun asSql(): String { + return "?" + } + + override fun appendToStatement(stmt: PreparedStatement, startIndex: Int): Int { + stmt.setString(startIndex, value) + return 1 + startIndex + } + } +} + +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) + } +} + +fun List.concatToFilledPreparedStatement(connection: Connection): PreparedStatement { + var query = "" + for (element in this) { + if (query.isNotEmpty()) { + query += " " + } + query += element.asSql() + } + val statement = connection.prepareAndLog(query) + 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 +} + class Query( val connection: Connection, - val selectedColumns: List>, + val selectedColumns: MutableList>, var table: Table, var limit: UInt? = null, var skip: UInt? = null, + val joins: MutableList = mutableListOf(), + val conditions: MutableList = mutableListOf(), // var order: OrderClause?= null, -// val condition: List ) : Iterable { + 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: Column<*>): Query { + selectedColumns.addAll(columns) + return this + } + fun skip(skip: UInt): Query { require(limit != null) this.skip = skip @@ -296,15 +485,22 @@ class Query( } override fun iterator(): Iterator { - val columnSelections = selectedColumns.joinToString { it.sqlName } - var query = "SELECT $columnSelections FROM ${table.sqlName} " + val columnSelections = selectedColumns.joinToString { it.qualifiedSqlName } + val elements = mutableListOf( + SQLQueryComponent.standalone("SELECT $columnSelections FROM ${table.sqlName}"), + ) + elements.addAll(joins) + if (conditions.any()) { + elements.add(SQLQueryComponent.standalone("WHERE")) + elements.add(ANDExpression(conditions)) + } if (limit != null) { - query += "LIMIT $limit " + elements.add(SQLQueryComponent.standalone("LIMIT $limit")) if (skip != null) { - query += "OFFSET $skip " + elements.add(SQLQueryComponent.standalone("OFFSET $skip")) } } - val prepared = connection.prepareAndLog(query.trim()) + val prepared = elements.concatToFilledPreparedStatement(connection) val results = prepared.executeQuery() return object : Iterator { var hasAdvanced = false @@ -316,6 +512,7 @@ class Query( hasAdvanced = true return true } else { + results.close() // TODO: somehow enforce closing this hasEnded = true return false } diff --git a/src/main/kotlin/moe/nea/ledger/utils/DI.kt b/src/main/kotlin/moe/nea/ledger/utils/DI.kt index abd6272..e998c65 100644 --- a/src/main/kotlin/moe/nea/ledger/utils/DI.kt +++ b/src/main/kotlin/moe/nea/ledger/utils/DI.kt @@ -62,4 +62,7 @@ class DI { private val values = mutableMapOf, *>, Any>() private val providers = mutableMapOf, BaseDIProvider<*, *>>() + init { + registerSingleton(this) + } } \ No newline at end of file -- cgit