search

reposilite-playground / exposed-upsert

Upsert DSL extension for Exposed, Kotlin SQL framework
The Unlicense
34 stars 3 forks source link

Support default values #3

Open MeowRay opened 3 years ago

MeowRay commented 3 years ago

image image

dzikoysk commented 3 years ago

Hi, you can use only one conflict parameter, it means it can be only conflictColumn or conflictIndex. Examples:

MeowRay commented 3 years ago

Hi, you can use only one conflict parameter, it means it can be only conflictColumn or conflictIndex. Examples:

Yes, but the problem still exists, I tried "conflictCoumn=table.uid" and "conflictIndex=table.index"

dzikoysk commented 3 years ago

What's your db?

MeowRay commented 3 years ago

What's your db?

MySQL8

MeowRay commented 3 years ago 
[18:48:16 INFO]: INSERT INTO stats_test (last_seen, locked, uid) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE last_seen=?
[18:48:16 INFO]: INSERT INTO stats_test (last_seen, locked, uid) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE last_seen=?
[18:48:16 INFO]: INSERT INTO stats_test (last_seen, locked, uid) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE last_seen=?
[18:48:16 WARN]: 指令执行错误 label: stats sender: CraftPlayer{name=Ya_Mo_TAT} command: test args: [test, 1]
[18:48:16 WARN]: java.lang.reflect.InvocationTargetException
[18:48:16 WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[18:48:16 WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
[18:48:16 WARN]:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[18:48:16 WARN]:    at java.lang.reflect.Method.invoke(Method.java:498)
[18:48:16 WARN]:    at net.wdsj.servercore.common.command.BaseCommandProxy.lambda$enterCommand$4(BaseCommandProxy.java:468)
[18:48:16 WARN]:    at net.wdsj.servercore.common.command.BaseCommandProxy.lambda$enterCommand$5(BaseCommandProxy.java:489)
[18:48:16 WARN]:    at net.wdsj.servercore.common.command.CommandProxyBukkit.execute(CommandProxyBukkit.java:137)
[18:48:16 WARN]:    at net.wdsj.servercore.common.command.BaseCommandProxy.enterCommand(BaseCommandProxy.java:487)
[18:48:16 WARN]:    at net.wdsj.servercore.common.command.CommandProxyBukkit.onCommand(CommandProxyBukkit.java:93)
[18:48:16 WARN]:    at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
[18:48:16 WARN]:    at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:141)
[18:48:16 WARN]:    at org.bukkit.craftbukkit.v1_8_R3.CraftServer.dispatchCommand(CraftServer.java:646)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.PlayerConnection.handleCommand(PlayerConnection.java:1160)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.PlayerConnection.a(PlayerConnection.java:998)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.PacketPlayInChat.a(PacketPlayInChat.java:45)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.PacketPlayInChat.a(PacketPlayInChat.java:1)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.PlayerConnectionUtils$1.run(SourceFile:13)
[18:48:16 WARN]:    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
[18:48:16 WARN]:    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.SystemUtils.a(SourceFile:44)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.MinecraftServer.B(MinecraftServer.java:742)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:380)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:680)
[18:48:16 WARN]:    at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:568)
[18:48:16 WARN]:    at java.lang.Thread.run(Thread.java:748)
[18:48:16 WARN]: Caused by: java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4).
[18:48:16 WARN]:    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1375)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1388)
[18:48:16 WARN]:    at com.mysql.cj.jdbc.ClientPreparedStatement.setObject(ClientPreparedStatement.java:1672)
[18:48:16 WARN]:    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.set(JdbcPreparedStatementImpl.kt:29)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.IColumnType$DefaultImpls.setParameter(ColumnType.kt:69)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.ColumnType.setParameter(ColumnType.kt:84)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.statements.api.PreparedStatementApi$DefaultImpls.fillParameters(PreparedStatementApi.kt:13)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.fillParameters(JdbcPreparedStatementImpl.kt:12)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:52)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:135)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:121)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:28)
[18:48:16 WARN]:    at com.meowray.common.exposed.upsert.ExposedUpsertExtensionsKt.upsertU(ExposedUpsertExtensions.kt:17)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.dao.impl.StatsDaoImpl.modifyValue(StatsDaoImpl.kt:78)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.command.WdsjStatisticsCommand$test$$inlined$run$lambda$1.invoke(WdsjStatisticsCommand.kt:37)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.command.WdsjStatisticsCommand$test$$inlined$run$lambda$1.invoke(WdsjStatisticsCommand.kt:23)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.dao.impl.StatsDaoImpl$runDb$1.invoke(StatsDaoImpl.kt:107)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.dao.impl.StatsDaoImpl$runDb$1.invoke(StatsDaoImpl.kt:14)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction$run(ThreadLocalTransactionManager.kt:179)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.access$inTopLevelTransaction$run(ThreadLocalTransactionManager.kt:1)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:205)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:213)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:204)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:156)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:213)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:126)
[18:48:16 WARN]:    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:123)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.dao.impl.StatsDaoImpl.runDb(StatsDaoImpl.kt:105)
[18:48:16 WARN]:    at net.wdsj.bukkit.wdsjstatistics.command.WdsjStatisticsCommand.test(WdsjStatisticsCommand.kt:36)
[18:48:16 WARN]:    ... 25 more

    override fun modifyValue(uid: Long, vararg kvs: Pair<StatsColumn<*>, Number>) {
        val l = kvs.map { it.first.name }
     //   if (kvs.isEmpty()) return
        table.upsertU(table.uid,null, insertBody = { t ->
            t[table.uid] = uid
            t[table.lastSeen] = System.currentTimeMillis()
            t[table.locked] = true
            println(t.prepareSQL(TransactionManager.current()))
        }, updateBody = { t ->
            t[lastSeen] = System.currentTimeMillis()
        })
    }
dzikoysk commented 3 years ago

Could you try to use this:

table.upsertU(table.uid, insertBody = { t ->
    t[table.uid] = uid
    t[table.locked] = true
    t[table.lastSeen] = System.currentTimeMillis()

}, updateBody = { t ->
    t[table.lastSeen] = System.currentTimeMillis()
})

These index out of range exceptions are mostly caused by the fact that Exposed has just terrible internals and is not really able to handle values from 2 bodies.

MeowRay commented 3 years ago

The problem still exists, it's weird this is my testclass code


@GlobalCommand(permission = "wdsjStatistics.admin")
class WdsjStatisticsCommand : WdsjCommand<CommandSender> {

    @SubCommand
    fun test(sender: Player) {
        val table = StatsTable2("test")

        val database = Database.connect(WdsjServerAPI.getDatabaseFactory().defMySqlDbManager.dataSource)

        transaction(database) {
            SchemaUtils.drop(table)
        }

        transaction(database) {
            SchemaUtils.createMissingTablesAndColumns(table)
        }

        transaction(database){
            table.upsertU(table.uid, insertBody = { t ->
                t[table.uid] = sender.uid!!
                t[table.locked] = true
                t[table.lastSeen] = System.currentTimeMillis()
            }, updateBody = { t ->
                t[table.lastSeen] = System.currentTimeMillis()
            })
        }

    }
}

class StatsTable2(suffix: String) : LongIdTable("stats2_$suffix") {

    val uid = long("uid").uniqueIndex()
    val lastSeen = long("last_seen").default(0)
    val locked = bool("locked").default(false)

}
dzikoysk commented 3 years ago

Ok, it looks like Exposed inserts default values for each query which means that it breaks the internal merge method.

object StatsTable2 : LongIdTable("stats2") {
    val uid = long("uid").uniqueIndex()
    val lastSeen = long("last_seen")
    val locked = bool("locked")
}

@Test
fun `gh-3`() {
    transaction {
        SchemaUtils.create(StatsTable2)

        StatsTable2.upsert(StatsTable2.uid,
            insertBody = { t ->
                t[uid] = 1
                t[locked] = true
                t[lastSeen] = System.currentTimeMillis()
            },
            updateBody = { t ->
                t[lastSeen] = System.currentTimeMillis()
            }
        )
    }
}

Removing all .default() calls resolves this for now. I don't think I'll be able to support this but I'll mark it as an enhancement request. Just set default values explicitly within the insert body.