Support for database sequences?

[tzhao11]

e.g. select MYSEQUENCE.nextval from DUAL for Oracle and nextval('mysequence') for Postgres?

The API could be like

// define a sequence object (use DatabaseSequence to avoid name conflict with Kotlin Sequence)
object MySequence : DatabaseSequence("mysequence")

// query the next value of the sequence
database.from(Dual).select(MySequence.nextval())

[tzhao11]

For now I'm using the following extension for Oracle:

fun DatabaseSequence.nextval() = SequenceExpression(SequenceExpressionType.NEXTVAL, this)

open class DatabaseSequence(val sequenceName: String)

enum class SequenceExpressionType(private val value: String) {
    CURRVAL("currval"),
    NEXTVAL("nextval");

    override fun toString(): String {
        return value
    }
}

data class SequenceExpression(
    val type: SequenceExpressionType,
    val databaseSequence: DatabaseSequence,
    override val sqlType: SqlType<Long> = LongSqlType,
    override val isLeafNode: Boolean = true,
    override val extraProperties: Map<String, Any> = emptyMap()
) : ScalarExpression<Long>()

class CustomSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int) : OracleFormatter(database, beautifySql, indentSize) {
    override fun visitUnknown(expr: SqlExpression): SqlExpression {
        return if (expr is SequenceExpression) {
            write("${expr.databaseSequence.sequenceName}.${expr.type} ")

            expr
        } else {
            super.visitUnknown(expr)
        }
    }
}

object Dual: Table<Nothing>("dual") {
    val dummy = varchar("dummy")
}

Usage:

object MySequence : DatabaseSequence("MYSEQUENCE")

database.from(Dual)
            .select(MySequence.nextval())
            .map { println(it.getLong(1)) }

This generates the following SQL:

select MYSEQUENCE.nextval from DUAL