reposilite-playground/exposed-upsert

Upsert DSL extension for Exposed, Kotlin SQL framework

exposed-upsert's Issues

SqlLite: Invalid sql leads to ArrayIndexOutOfBoundsException

Stacktrace: https://pastebin.com/raw/XzKVWhuB

This is my upsert statement:

TileEntitiesTable.upsert(
    conflictColumn = TileEntitiesTable.uuid,
    
    insertBody = {
        val location = tileEntity.location
        
        it[uuid] = tileEntity.uuid
        it[world] = tileEntity.location.world!!.uid
        it[chunkX] = chunk.x
        it[chunkZ] = chunk.z
        it[x] = location.blockX
        it[y] = location.blockY
        it[z] = location.blockZ
        it[yaw] = tileEntity.armorStand.location.yaw
        it[type] = tileEntity.material.typeName
        it[data] = ExposedBlob(tileEntity.getData())
    },
    
    updateBody = {
        it[data] = ExposedBlob(tileEntity.getData())
    }
)

The above upsert statement generates this sql:

INSERT INTO tileEntities (chunkX, chunkZ, "data", "type", uuid, world, x, y, yaw, z) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("uuid") DO UPDATE SET "data"=EXCLUDED."data"

As you can see, the statement is invalid, there should be another question mark in the update statement after "data"=
This leads to an ArrayIndexOutOfBoundsException in CorePreparedStatement.java.

I fixed it in this commit: https://github.com/NichtStudioCode/exposed-upsert/commit/297be27e8ab2aad81c404676653f2d0320650271. This is obviously not a perfect solution, hence why I am not making a pull request.

Release to Maven Central

Hi,

the "manual" installation releases link doesn't work,
probably should be https://repo.panda-lang.org/#/releases/net/dzikoysk/exposed-upsert

Do you think of releasing this to central maven repository?

Move to com.reposilite package and groupId

Cleanup tests

Insert body breaks the order of fields

a) repository link at bottom of page https://androidrepo.com/repo/dzikoysk-exposed-upsert is still broken

b)
if I have non DAO exposed Tables, is your upsert supposed to work also?

I have DSL defined tables (non IdTables)

abstract class BaseTable(name: String) : Table(name) {
    val dtoModCount = long("dto_mod_count")
    val dtoOptimisticLockId = long("dto_optimistic_lock_id")
    val dtoCreationDate = timestamp("dto_creation_date")
    val dtoCreationUser = varchar("dto_creation_user", VARCHAR_MEDIUM)
    val dtoModDate = timestamp("dto_mod_date")
    val dtoModUser = varchar("dto_mod_user", VARCHAR_MEDIUM)
}

object IssuesTable : BaseTable(name = "ISSUES") {
    val key = varchar("key", VARCHAR_SMALL)
    val project = varchar("project", VARCHAR_SMALL)
    ...
    override val primaryKey = PrimaryKey(key, name = "PK_Issue_key")

    private fun upsertCommonFields(it: InsertStatement<Number>, table: IssuesTable, dto: Issue) {
        it[table.project] = dto.project
        ...

    fun upsert(dto: Issue) {
        IssuesTable.upsert(conflictColumn = IssuesTable.key,
            insertBody = {
                it[IssuesTable.key] = dto.key
                upsertCommonFields(it, this, dto)
                updateBaseDTOmetadata(it, this, dto)
            },
            updateBody = {
                upsertCommonFields(it, this, dto)
                updateBaseDTOmetadata(it, this, dto)
            }
        )
    }

insert works, but update totally confuses column names and the values to put into them.

Support for Multiple ON CONFLICT columns in SQLite

Adding support for multiple ON CONFLICT columns would be greatly appreciated.

Support default values

Unable to use column with reserved words such as "user" as conflict column.

I have a table with a column named user (which contains an id referring to my own users table). In Postgres, user is a reserved word, which means it's required to put quotes around it when using it in SQL.

I have the following code:

UsageInfos.upsert(
    conflictColumn = UsageInfos.user,
    insertBody = {
        it[user] = id
        it[other] = "Value"
    },
    updateBody = {
        it[other] = "Value"
    }
)

Which results in the following SQL:
INSERT INTO usageinfos ("other", "user") VALUES ('Value', 86) ON CONFLICT(user) DO UPDATE SET "other"='Value'

Because CONFLICT(user) is missing quotes, this results in an error.

The correct SQL should be:
INSERT INTO usageinfos ("other", "user") VALUES ('Value', 86) ON CONFLICT("user") DO UPDATE SET "other"='Value'