Exposed Framework

import org.jetbrains.exposed.sql.Table

object ProdukTable : Table("produk") {
    val id = integer("id").autoIncrement()
    val nama = varchar("nama", 100)
    val harga = integer("harga")
    val aktif = bool("aktif").default(true)

    override val primaryKey = PrimaryKey(id, name = "PK_Produk")
}

transaction {
    SchemaUtils.create(ProdukTable)

    ProdukTable.insert {
        it[nama] = "Kopi Susu"
        it[harga] = 18000
        it[aktif] = true
    }

    ProdukTable.selectAll().forEach { row ->
        println("${row[ProdukTable.nama]}: ${row[ProdukTable.harga]}")
    }
}

ProdukTable.select {
    (ProdukTable.harga gt 10000) and (ProdukTable.aktif eq true)
}.forEach { /* ... */ }

// exposed_framework.kt
// Mendemonstrasikan Exposed DSL dari JetBrains: Table, select, insert, dan
// transaction. Pada kode produksi, Exposed memproses DSL menjadi SQL.
// Di sini kita memodelkan DSL Exposed secara ringkas (tanpa dependency Exposed
// penuh) sambil memakai SQLite via JDBC untuk eksekusi nyata.

import java.sql.Connection
import java.sql.DriverManager

// Exposed-style Column reference
class Column<T>(val table: Table, val name: String, val sqlType: String) {
    override fun toString(): String = "${table.tableName}.$name"
    infix fun eq(value: T): Op = Op.Eq(this, value)
    infix fun gt(value: T): Op = Op.Gt(this, value)
    infix fun lt(value: T): Op = Op.Lt(this, value)
}

// Expression tree (mirip Exposed Op<Boolean>)
sealed class Op {
    data class Eq<T>(val col: Column<T>, val value: T) : Op()
    data class Gt<T>(val col: Column<T>, val value: T) : Op()
    data class Lt<T>(val col: Column<T>, val value: T) : Op()
    data class And(val left: Op, val right: Op) : Op()
    data class Or(val left: Op, val right: Op) : Op()

    fun toSql(): String = when (this) {
        is Eq<*> -> "${col.name} = ${sqlValue(value)}"
        is Gt<*> -> "${col.name} > ${sqlValue(value)}"
        is Lt<*> -> "${col.name} < ${sqlValue(value)}"
        is And -> "${left.toSql()} AND ${right.toSql()}"
        is Or -> "(${left.toSql()} OR ${right.toSql()})"
    }

    private fun sqlValue(v: Any?): String = when (v) {
        is String -> "'$v'"
        is Boolean -> if (v) "1" else "0"
        null -> "NULL"
        else -> v.toString()
    }
}

infix fun Op.and(other: Op): Op = Op.And(this, other)
infix fun Op.or(other: Op): Op = Op.Or(this, other)

// Exposed-style Table DSL
abstract class Table(val tableName: String) {
    val columns = mutableListOf<Column<*>>()

    // Type-safe column builders
    fun integer(name: String): Column<Int> = Column<Int>(this, name, "INTEGER").also { columns.add(it) }
    fun varchar(name: String): Column<String> = Column<String>(this, name, "VARCHAR(255)").also { columns.add(it) }
    fun bool(name: String): Column<Boolean> = Column<Boolean>(this, name, "BOOLEAN").also { columns.add(it) }

    fun ddl(): String {
        val cols = columns.joinToString(",\n  ") { "${it.name} ${it.sqlType}" }
        return "CREATE TABLE IF NOT EXISTS $tableName (\n  $cols\n)"
    }
}

// Demo entity table (mirip Exposed DAO)
object ProdukTable : Table("produk") {
    val id = integer("id")
    val nama = varchar("nama")
    val harga = integer("harga")
    val aktif = bool("aktif")
}

// Transaction wrapper (mirip transaction { })
class Transaction(private val conn: Connection) : AutoCloseable {
    init { conn.autoCommit = false }

    override fun close() {
        // Auto-closeable; do nothing (caller should commit/rollback explicitly)
    }

    // Insert DSL: insert { it[nama] = "..." }
    fun insert(table: Table, values: Map<Column<*>, Any?>) {
        val cols = values.keys.joinToString(", ") { it.name }
        val vals = values.values.joinToString(", ") {
            when (it) {
                is String -> "'$it'"
                is Boolean -> if (it) "1" else "0"
                null -> "NULL"
                else -> it.toString()
            }
        }
        conn.prepareStatement("INSERT INTO ${table.tableName} ($cols) VALUES ($vals)").use {
            it.executeUpdate()
        }
    }

    // Select DSL: select(ProdukTable.nama).where { ProdukTable.harga gt 10000 }
    fun select(table: Table, where: Op? = null): List<Map<String, Any?>> {
        val cols = table.columns.joinToString(", ") { it.name }
        val sql = "SELECT $cols FROM ${table.tableName}" + (where?.let { " WHERE ${it.toSql()}" } ?: "")
        val result = mutableListOf<Map<String, Any?>>()
        conn.prepareStatement(sql).use { ps ->
            ps.executeQuery().use { rs ->
                val md = rs.metaData
                while (rs.next()) {
                    val row = (1..md.columnCount).associate { i ->
                        md.getColumnName(i) to rs.getObject(i)
                    }
                    result.add(row)
                }
            }
        }
        return result
    }

    fun commit() { conn.commit() }
    fun rollback() { conn.rollback() }
}

fun main() {
    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite:file::memory:?cache=shared"
    val keeper: Connection = DriverManager.getConnection(url)

    println("=== Exposed DSL: Table definition ===")
    println(ProdukTable.ddl())
    println("Columns: ${ProdukTable.columns.map { it.name }}")

    println("\n=== Execute DDL + Insert via transaction ===")
    DriverManager.getConnection(url).use { conn ->
        conn.createStatement().use { it.execute(ProdukTable.ddl()) }
        Transaction(conn).use { tx ->
            tx.insert(ProdukTable, mapOf(
                ProdukTable.id to 1,
                ProdukTable.nama to "Kopi Susu",
                ProdukTable.harga to 18000,
                ProdukTable.aktif to true
            ))
            tx.insert(ProdukTable, mapOf(
                ProdukTable.id to 2,
                ProdukTable.nama to "Es Teh Manis",
                ProdukTable.harga to 8000,
                ProdukTable.aktif to true
            ))
            tx.insert(ProdukTable, mapOf(
                ProdukTable.id to 3,
                ProdukTable.nama to "Nasi Goreng",
                ProdukTable.harga to 25000,
                ProdukTable.aktif to false
            ))
            tx.commit()
        }
    }

    println("\n=== Select all ===")
    DriverManager.getConnection(url).use { conn ->
        Transaction(conn).use { tx ->
            tx.select(ProdukTable).forEach { row ->
                println("  $row")
            }
        }
    }

    println("\n=== Select with WHERE: harga > 10000 ===")
    DriverManager.getConnection(url).use { conn ->
        Transaction(conn).use { tx ->
            tx.select(ProdukTable, where = ProdukTable.harga gt 10000).forEach { row ->
                println("  ${row["nama"]} (Rp${row["harga"]})")
            }
        }
    }

    println("\n=== Select with WHERE: aktif = true AND harga < 20000 ===")
    DriverManager.getConnection(url).use { conn ->
        Transaction(conn).use { tx ->
            val op = (ProdukTable.aktif eq true) and (ProdukTable.harga lt 20000)
            tx.select(ProdukTable, where = op).forEach { row ->
                println("  ${row["nama"]} (Rp${row["harga"]}, aktif=${row["aktif"]})")
            }
        }
    }

    println("\n=== Comparison: Exposed vs raw SQL ===")
    println("| Operasi       | Exposed DSL                          | Raw SQL                          |")
    println("|---------------|--------------------------------------|----------------------------------|")
    println("| Select filter  | tx.select(T, where = T.harga gt 10000) | SELECT ... WHERE harga > 10000 |")
    println("| Insert        | tx.insert(T, mapOf(T.x to v))        | INSERT INTO T(x) VALUES (?)      |")
    println("| Type-safe     | Ya (column type = Column<Int>)       | Tidak (string concat berisiko)   |")

    println("\n=== Exposed transaction pattern ===")
    println("transaction(db) { /* body */ } membungkus blok dalam transaksi;")
    println("bila body throw exception, transaksi otomatis rollback.")
    keeper.close()
}

=== Exposed DSL: Table definition ===
CREATE TABLE IF NOT EXISTS produk (
  id INTEGER,
  nama VARCHAR(255),
  harga INTEGER,
  aktif BOOLEAN
)
Columns: [id, nama, harga, aktif]

=== Execute DDL + Insert via transaction ===

=== Select all ===
  {id=1, nama=Kopi Susu, harga=18000, aktif=1}
  {id=2, nama=Es Teh Manis, harga=8000, aktif=1}
  {id=3, nama=Nasi Goreng, harga=25000, aktif=0}

=== Select with WHERE: harga > 10000 ===
  Kopi Susu (Rp18000)
  Nasi Goreng (Rp25000)

=== Select with WHERE: aktif = true AND harga < 20000 ===
  Kopi Susu (Rp18000, aktif=1)
  Es Teh Manis (Rp8000, aktif=1)

=== Comparison: Exposed vs raw SQL ===
| Operasi       | Exposed DSL                          | Raw SQL                          |
|---------------|--------------------------------------|----------------------------------|
| Select filter  | tx.select(T, where = T.harga gt 10000) | SELECT ... WHERE harga > 10000 |
| Insert        | tx.insert(T, mapOf(T.x to v))        | INSERT INTO T(x) VALUES (?)      |
| Type-safe     | Ya (column type = Column<Int>)       | Tidak (string concat berisiko)   |

=== Exposed transaction pattern ===
transaction(db) { /* body */ } membungkus blok dalam transaksi;
bila body throw exception, transaksi otomatis rollback.

Database.connect(
    url = "jdbc:postgresql://db.example.com:5432/pesanan",
    driver = "org.postgresql.Driver",
    user = "appuser",
    password = System.getenv("DB_PASSWORD")
)