exposed tricks

exposed-tricks.kt

# =========
# demo: https://github.com/JetBrains/Exposed/tree/master/src/test/kotlin/demo
# dml tests: https://github.com/JetBrains/Exposed/blob/master/src/test/kotlin/org/jetbrains/exposed/sql/tests/shared/DMLTests.kt

# ===== GIS

https://github.com/JetBrains/Exposed/issues/459

# === native

https://github.com/JetBrains/Exposed/issues/118

# ===== Op<T> ====
see: https://github.com/JetBrains/Exposed/blob/master/src/main/kotlin/org/jetbrains/exposed/sql/Op.kt
Op.TRUE
Op.FALSE
Op.build { ... }
List<Op>.compoundOr() # aka "anyOf" -> [].reduce("OR")
List<Op>.compoundAnd() # aka "allOf" -> [].reduce("AND")

    @Test
    fun testCompoundOp() {
        withCitiesAndUsers { cities, users, _ ->
            val allUsers = setOf(
                    "Andrey",
                    "Sergey",
                    "Eugene",
                    "Alex",
                    "Something"
            )
            val orOp = allUsers.map { Op.build { users.name eq it } }.compoundOr()
            val userNamesOr = users.select(orOp).map { it[users.name] }.toSet()
            assertEquals(allUsers, userNamesOr)

            val andOp = allUsers.map { Op.build { users.name eq it } }.compoundAnd()
            assertEquals(0, users.select(andOp).count())
        }
    }



# ==== postgres: DISTINCT ON =====
# see: https://github.com/JetBrains/Exposed/issues/500

class DistinctOn<T>(val expr: Column<T>) : Function<T>(expr.columnType) {
    override fun toSQL(queryBuilder: QueryBuilder) = "DISTINCT ON (${expr.toSQL(queryBuilder)}) ${expr.toSQL(queryBuilder)}"
}

# ===== one-to-many: left join, group by, count() =====
# see: https://github.com/JetBrains/Exposed/issues/482
StudentTable.leftJoin(PhoneTable). 
    slice(PhoneTable.number.count(), StudentTable.name). 
    selectAll().
    groupBy(StudentTable.id)

# ===== postgres: SELECT ... ORDER BY NULLS LAST =====
# unresolved: https://github.com/JetBrains/Exposed/issues/478
# SELECT * FROM table_name ORDER BY column_name DESC NULLS LAST;

# ===== postgres: CTE aka WITH ()
# unresolved: https://github.com/JetBrains/Exposed/issues/423
TransactionManager.current().exec(
            "WITH RECURSIVE t AS( " +
                "SELECT id,name,parent_id,ARRAY[ID] AS path,1 AS DEPTH FROM categories WHERE parent_id IS null " +
                "UNION ALL " +
                "SELECT c.id,c.name,c.parent_id,t.path || c.id,t.depth + 1 AS depth FROM categories c " +
                "JOIN t on c.parent_id=t.id" +
                ") " +
                "SELECT id,name,parent_id,path,depth FROM t"
        ) {
            ...
        }

# ==== custom enums ====
# see: https://github.com/JetBrains/Exposed/issues/416
inline fun <reified T: Enum<T>> Table.customEnumeration(name: String) =
    registerColumn<T>(name, object : ColumnType() {

        override fun sqlType(): String              = enumValues<T>().joinToString(",", prefix = "ENUM(", postfix = ")") { "'" + it.name + "'" }
        override fun valueFromDB(value: Any)        = enumValueOf<T>(value as String)
        override fun notNullValueToDB(value: Any)   = (value as T).name
    })

# ===== custom comparable columns ======
see: https://github.com/JetBrains/Exposed/issues/409
data class Token(val value: String) : Comparable<Token> {
    override fun compareTo(other: Token): Int = value.compareTo(other.value)
}
# OR
interface ComparableWrapper<T:Comparable<T>, E: ComparableWrapper<T, E>> : Comparable<E> {
    val value : T
    override fun compareTo(other: E): Int = value.compareTo(other.value)
} 
data class Token(override val value: String) : ComparableWrapper<String, Token>

# ===== subquery and alias #####
# https://github.com/JetBrains/Exposed/issues/404
val subQueryAlias = Table2.select { /* tricky subquery goes here */ }
val query = Join(Table1).join(subQueryAlias) { Table1.id eq subQueryAlias[Table2.id] }
query.map { resultRow -> 
    // bang! it's impossible to do because Table2 is under the alias in the resultRow
    Table2Entity.wrapRow(resultRow) 
}

# mssql: call stored procedure
see: https://github.com/JetBrains/Exposed/issues/390
transaction {
    exec("exec dbo.Foo @p = 1) {
        sequence {
        while (it.next()) {
            yield(it.getString("name"))
        }
    }
}
# ^^ FAILES: A result set was generated for update. --> similar to postgres CTE issue?         
# Solution? ...         
fun <T : Any> Transaction.execSp(stmt: String, transform: (ResultSet) -> T): T? {
        if (stmt.isEmpty()) return null

        return exec(object : Statement<T>(StatementType.SELECT, emptyList()) {
            override fun PreparedStatement.executeInternal(transaction: Transaction): T? {
                executeQuery()
                return resultSet?.use { transform(it) }
            }

            override fun prepareSQL(transaction: Transaction): String = stmt
            override fun arguments(): Iterable<Iterable<Pair<ColumnType, Any?>>> = emptyList()
        })
    }
         
# ===== postgres: UPDATE ... RETURNING ====
# see: https://github.com/JetBrains/Exposed/issues/351
# unresolved
         
# ==== postgres: date() column type - wrong conversion? ====
# see: https://github.com/JetBrains/Exposed/issues/319
# see: https://github.com/JetBrains/Exposed/issues/318
# unresolved
         
# ==== graphql: ideas ====
# see: https://github.com/JetBrains/Exposed/issues/316 
         
# ==== transaction in autoCommitMode ===
# see: https://github.com/JetBrains/Exposed/issues/306
# see: https://github.com/seratch/kotliquery  
fun <T> execAutoCommit(statement: Transaction.() -> T) = transaction {
    connection.autoCommit = true
    statement()
}         

# ==== get unique columns from table ####
see: https://github.com/JetBrains/Exposed/issues/296
val uniqueIdxCols = table.indices.filter { it.unique }.flatMap { it.columns.toList() }
val uniqueCols = columns.filter { it.indexInPK != null || it in uniqueIdxCols} 
         
# ==== StatementInterceptor =====
# see: https://github.com/JetBrains/Exposed/issues/265
object SafeInterceptor : StatementInterceptor {
    override fun beforeExecution(transaction: Transaction, context: StatementContext) {
        (context.statement as? Query)?.let { q ->
            if (q.targets.contains(FooTable) )
                q.adjustWhere { this?.and (FooTable.deleted eq false) ?: FooTable.deleted eq false }
        }
    }

    override fun afterExecution(transaction: Transaction, contexts: List<StatementContext>, executedStatement: PreparedStatement) {
        
    }

}

fun safeTransaction(body: Transaction.() -> Unit ) {
    transaction { 
        monitor.register(SafeInterceptor)
        body()
    }
}

# ==== string escaping: watch out for stringLiteral =====
see: https://github.com/JetBrains/Exposed/issues/264  
# Exposed uses PreparedStatement, but also escapes a string values before sets it to a statement. Besides, there are some places (like stringLiteral) where string will be placed at SQL in-line what can cause SQL-injection if not escaped properly.         
         
# ==== transactionmanager: ThreadLocal, Explicit, Coroutines ====
# see: https://github.com/JetBrains/Exposed/issues/255
fun currentTransactionContext(): CoroutineContext {
    val manager = TransactionManager.manager as? ThreadLocalTransactionManager
    return manager?.currentOrNull()?.let {
        manager.threadLocal.asContextElement(it)
    } ?: EmptyCoroutineContext
}

// Usage:
launch(Dispatchers.Default + currentTransactionContext()) {
    // Do something that has access to outer's existing transaction, if it was in one.
}   
         
# ==== get all tablenames in proper case ====
# see: https://github.com/JetBrains/Exposed/issues/219
override fun allTablesNames(): List<String> {
        val result = ArrayList<String>()
        val tr = TransactionManager.current()
        val resultSet = tr.db.metadata.getTables(null, tr.connection.schema, "%", arrayOf("TABLE"))

        while (resultSet.next()) {
            result.add(resultSet.getString("TABLE_NAME").inProperCase)
        }
        return result
    } 
         
# ==== predicates: op<Boolean> - adhoc =====
https://github.com/JetBrains/Exposed/issues/195
val op = object : Op<Boolean>() {
		override fun toSQL(queryBuilder : QueryBuilder) : String
			= ids.joinToString(" OR ") { "(id = $it)" }
	} 
AndOp(op, otherTable.isActive neq true)    
	 
# ==== UPSERT / insertOrUpdate / batchInsert ====	 
# see: https://github.com/JetBrains/Exposed/issues/167
# see: https://github.com/JetBrains/Exposed/issues/186	
# see: https://github.com/JetBrains/Exposed/issues/129	 
	 
# ===== reference to toher schemas ===
# see: https://github.com/JetBrains/Exposed/issues/145	
	 
# ===== jsonb / jsonkey ======
https://github.com/JetBrains/Exposed/issues/127
https://gist.github.com/quangIO/a623b5caa53c703e252d858f7a806919
https://gist.github.com/boonshift/65edda0782137d2b3825f2ddc1d93fe3
https://gist.github.com/boonshift/79be440eb1b90119205b41c839924135
https://gist.github.com/boonshift/aa7f777800a65f6c8fcba9972005b792
	 
val customers = Customers.select { Customers.misc.json<Int>(JsonKey("salary")) eq 5555 }
	 
# === prepared statements ? ======	
https://github.com/JetBrains/Exposed/issues/116	 

# ===== like =====
https://github.com/JetBrains/Exposed/issues/64
var results = MyObject.find { Object.myText like "%stringInText%" }
	 
# ===== composite keys ====
https://github.com/JetBrains/Exposed/issues/43
https://github.com/JetBrains/Exposed/issues/239
https://github.com/JetBrains/Exposed/issues/353	
	 
object SomeTable : Table() {
    val col1 = integer("col1").primaryKey(0) // case 2 - composite PK
    val col2 = integer("col2").primaryKey(1) // case 2 - composite PK
    init {
        index(true, col1, col2) // case 1 - Unique index
    }
}	 


# ====== postgres: GREATEST(colA, colB, ...colN) =====
		    
            val foo: GreatestInstant<Instant?> =GreatestInstant(
                    TableA.createdAt, // not nullable
                    TableB.modifiedAt, // not nullable
                    TableB.deletedAt // nullable
            )		    
		    
class GreatestInstant<T : Instant?>(
    vararg val expr: Column<out T>
) : Function<T>(InstantColumnType(true)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append("GREATEST(")
        expr.toList().appendTo { +it }
        append(")")
    }
}