Database Driver Interface

/**
 * Maintains connections to an underlying SQL database and provides APIs for managing 
 * transactions and executing SQL statements
 */
interface SqlDriver : Closeable {
    /**
     * Execute a SQL statement and evaluate its result set using the given block
     * @param identifier An opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
     * @param sql The SQL string to be executed
     * @param mapper A lambda called with the cursor when the statement is executed successfully
     * @param parameters The number of bindable parameters sql contains
     * @param binders A lambda called before execution to bind any parameters to the SQL statement
     * @returns The generic result of the mapper lambda
     */
    fun <R> executeQuery(
        identifier: Int?,
        sql: String,
        mapper: (SqlCursor) -> QueryResult<R>,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)? = null
    ): QueryResult<R>
    
    /**
     * Execute a SQL statement
     * @param identifier An opaque, unique value for driver-side caching of prepared statements. If null, a fresh statement is required
     * @param sql The SQL string to be executed
     * @param parameters The number of bindable parameters sql contains
     * @param binders A lambda called before execution to bind any parameters to the SQL statement
     * @returns The number of rows updated for INSERT/DELETE/UPDATE, or 0 for other SQL statements
     */
    fun execute(
        identifier: Int?,
        sql: String,
        parameters: Int,
        binders: (SqlPreparedStatement.() -> Unit)? = null
    ): QueryResult<Long>
    
    /**
     * Start a new Transaction on the database
     */
    fun newTransaction(): QueryResult<Transacter.Transaction>
    
    /**
     * The currently open Transaction on the database
     */
    fun currentTransaction(): Transacter.Transaction?
    
    /**
     * Register a listener for query result changes
     */
    fun addListener(vararg queryKeys: String, listener: Query.Listener)
    
    /**
     * Remove a listener for query result changes
     */
    fun removeListener(vararg queryKeys: String, listener: Query.Listener)
    
    /**
     * Notify listeners that query results have changed
     */
    fun notifyListeners(vararg queryKeys: String)
}

import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.db.QueryResult

// Execute a query with result mapping
val users: List<User> = driver.executeQuery(
    identifier = 1,
    sql = "SELECT id, name, email FROM users WHERE active = ?",
    mapper = { cursor ->
        val results = mutableListOf<User>()
        while (cursor.next().value) {
            results.add(User(
                id = cursor.getLong(0)!!,
                name = cursor.getString(1)!!,
                email = cursor.getString(2)!!
            ))
        }
        QueryResult.Value(results)
    },
    parameters = 1
) { bindBoolean(1, true) }.value

// Execute an update statement
val rowsAffected: Long = driver.execute(
    identifier = 2,
    sql = "UPDATE users SET last_login = ? WHERE id = ?",
    parameters = 2
) {
    bindLong(1, System.currentTimeMillis())
    bindLong(2, userId)
}.value

// Transaction management
val transaction = driver.newTransaction().value
try {
    // Perform operations within transaction
    driver.execute(null, "INSERT INTO users (name) VALUES (?)", 1) {
        bindString(1, "New User")
    }
    transaction.endTransaction(successful = true)
} catch (e: Exception) {
    transaction.endTransaction(successful = false)
    throw e
}

/**
 * Represents a SQL result set which can be iterated through with next(). 
 * Initially the cursor will not point to any row, and calling next() once will iterate to the first row.
 */
interface SqlCursor {
    /**
     * Move to the next row in the result set
     * @returns true if the cursor successfully moved to a new row, false if there was no row to iterate to
     */
    fun next(): QueryResult<Boolean>
    
    /**
     * @param index Column index (0-based)
     * @returns The string or null value of column index for the current row of the result set
     */
    fun getString(index: Int): String?
    
    /**
     * @param index Column index (0-based)
     * @returns The long or null value of column index for the current row of the result set
     */
    fun getLong(index: Int): Long?
    
    /**
     * @param index Column index (0-based)
     * @returns The bytes or null value of column index for the current row of the result set
     */
    fun getBytes(index: Int): ByteArray?
    
    /**
     * @param index Column index (0-based)
     * @returns The double or null value of column index for the current row of the result set
     */
    fun getDouble(index: Int): Double?
    
    /**
     * @param index Column index (0-based)
     * @returns The boolean or null value of column index for the current row of the result set
     */
    fun getBoolean(index: Int): Boolean?
}

import app.cash.sqldelight.db.SqlCursor
import app.cash.sqldelight.db.QueryResult

// Manual cursor navigation
val result = driver.executeQuery(
    identifier = null,
    sql = "SELECT id, name, age, active FROM users",
    mapper = { cursor ->
        val users = mutableListOf<User>()
        while (cursor.next().value) {
            val user = User(
                id = cursor.getLong(0) ?: 0L,
                name = cursor.getString(1) ?: "",
                age = cursor.getLong(2)?.toInt() ?: 0,
                active = cursor.getBoolean(3) ?: false
            )
            users.add(user)
        }
        QueryResult.Value(users)
    },
    parameters = 0
).value

// Single row processing
val user = driver.executeQuery(
    identifier = null,
    sql = "SELECT * FROM users WHERE id = ?",
    mapper = { cursor ->
        if (cursor.next().value) {
            QueryResult.Value(User(
                id = cursor.getLong(0)!!,
                name = cursor.getString(1)!!,
                email = cursor.getString(2)!!
            ))
        } else {
            QueryResult.Value(null)
        }
    },
    parameters = 1
) { bindLong(1, userId) }.value

// Aggregation using cursor
val statistics = driver.executeQuery(
    identifier = null,
    sql = "SELECT COUNT(*), AVG(age), MAX(age) FROM users",
    mapper = { cursor ->
        if (cursor.next().value) {
            QueryResult.Value(UserStatistics(
                count = cursor.getLong(0) ?: 0,
                averageAge = cursor.getDouble(1) ?: 0.0,
                maxAge = cursor.getLong(2) ?: 0
            ))
        } else {
            QueryResult.Value(UserStatistics(0, 0.0, 0))
        }
    },
    parameters = 0
).value

/**
 * Represents a SQL statement that has been prepared by a driver to be executed.
 * This type is not thread safe unless otherwise specified by the driver emitting these.
 * Prepared statements should not be cached by client code. Drivers can implement caching 
 * by using the integer identifier passed to SqlDriver.execute or SqlDriver.executeQuery.
 */
interface SqlPreparedStatement {
    /**
     * Bind bytes to the underlying statement at index
     */
    fun bindBytes(index: Int, bytes: ByteArray?)
    
    /**
     * Bind long to the underlying statement at index
     */
    fun bindLong(index: Int, long: Long?)
    
    /**
     * Bind double to the underlying statement at index
     */
    fun bindDouble(index: Int, double: Double?)
    
    /**
     * Bind string to the underlying statement at index
     */
    fun bindString(index: Int, string: String?)
    
    /**
     * Bind boolean to the underlying statement at index
     */
    fun bindBoolean(index: Int, boolean: Boolean?)
}

import app.cash.sqldelight.db.SqlPreparedStatement

// Complex parameter binding
val rowsUpdated = driver.execute(
    identifier = 10,
    sql = """
        UPDATE users 
        SET name = ?, email = ?, age = ?, active = ?, profile_data = ?, updated_at = ?
        WHERE id = ?
    """.trimIndent(),
    parameters = 7
) {
    bindString(1, user.name)
    bindString(2, user.email) 
    bindLong(3, user.age.toLong())
    bindBoolean(4, user.active)
    bindBytes(5, user.profileData)
    bindLong(6, System.currentTimeMillis())
    bindLong(7, user.id)
}.value

// Handling null values
driver.execute(
    identifier = 11,
    sql = "INSERT INTO profiles (user_id, bio, avatar_url) VALUES (?, ?, ?)",
    parameters = 3
) {
    bindLong(1, userId)
    bindString(2, profile.bio)  // Can be null
    bindString(3, profile.avatarUrl)  // Can be null
}

// Batch operations with different parameters
val userIds = listOf(1L, 2L, 3L)
userIds.forEach { userId ->
    driver.execute(
        identifier = 12,
        sql = "UPDATE users SET last_seen = ? WHERE id = ?",
        parameters = 2
    ) {
        bindLong(1, System.currentTimeMillis())
        bindLong(2, userId)
    }
}

/**
 * The returned value is the result of a database query or other database operation.
 * This interface enables drivers to be based on non-blocking APIs where the result 
 * can be obtained using the suspending await method.
 */
sealed interface QueryResult<T> {
    /**
     * Get the result value immediately. Throws IllegalStateException for async drivers.
     */
    val value: T
    
    /**
     * Get the result value using suspension (works for both sync and async drivers)
     */
    suspend fun await(): T
    
    /**
     * Immediate/synchronous result
     */
    value class Value<T>(override val value: T) : QueryResult<T> {
        override suspend fun await() = value
    }
    
    /**
     * Asynchronous result requiring await()
     * Note: Accessing the .value property throws IllegalStateException for async results
     */
    value class AsyncValue<T>(private val getter: suspend () -> T) : QueryResult<T> {
        override val value: T get() = throw IllegalStateException("Cannot get async value synchronously, use await() instead")
        override suspend fun await() = getter()
    }
    
    companion object {
        /**
         * A QueryResult representation of a Kotlin Unit for convenience
         * Equivalent to QueryResult.Value(Unit)
         */
        val Unit = Value(kotlin.Unit)
    }
}

import app.cash.sqldelight.db.QueryResult
import kotlinx.coroutines.runBlocking

// Working with synchronous results
val syncResult: QueryResult<List<User>> = syncDriver.executeQuery(...)
val users: List<User> = syncResult.value  // Immediate access

// Working with asynchronous results
val asyncResult: QueryResult<List<User>> = asyncDriver.executeQuery(...)
val users: List<User> = runBlocking { asyncResult.await() }  // Suspended access

// Generic handling that works with both
suspend fun handleResult(result: QueryResult<List<User>>): List<User> {
    return result.await()  // Works for both sync and async
}

// Creating custom results
fun createSuccessResult(): QueryResult<String> {
    return QueryResult.Value("Operation completed")
}

fun createAsyncResult(): QueryResult<String> {
    return QueryResult.AsyncValue {
        delay(100)
        "Async operation completed"
    }
}

// Using the Unit convenience constant
fun executeVoidOperation(): QueryResult<Unit> {
    driver.execute(...)
    return QueryResult.Unit
}

import app.cash.sqldelight.Query

// Register listeners for table changes
val userListener = Query.Listener { 
    println("User data changed")
    refreshUserUI()
}

val profileListener = Query.Listener {
    println("Profile data changed") 
    refreshProfileUI()
}

// Register for specific tables
driver.addListener("users", listener = userListener)
driver.addListener("profiles", "user_settings", listener = profileListener)

// Make changes that trigger notifications
driver.execute(
    identifier = null,
    sql = "INSERT INTO users (name) VALUES (?)",
    parameters = 1
) { bindString(1, "New User") }

// This will trigger userListener
driver.notifyListeners("users")

// Remove listeners when no longer needed
driver.removeListener("users", listener = userListener)
driver.removeListener("profiles", "user_settings", listener = profileListener)

// Listener for multiple tables
val multiTableListener = Query.Listener {
    refreshEntireUI()
}

driver.addListener("users", "profiles", "settings", listener = multiTableListener)

/**
 * Exception thrown when an optimistic lock fails during database operations
 * Typically used in scenarios where concurrent modifications are detected
 */
class OptimisticLockException(
    message: String?, 
    cause: Throwable? = null
) : IllegalStateException(message, cause)

import app.cash.sqldelight.db.OptimisticLockException

// Handle optimistic locking in concurrent scenarios
try {
    database.transaction {
        val user = userQueries.selectById(userId).executeAsOne()
        val updatedUser = user.copy(
            version = user.version + 1,
            lastModified = System.currentTimeMillis()
        )
        
        val rowsAffected = userQueries.updateWithVersion(
            id = userId,
            version = user.version, // Check old version
            newVersion = updatedUser.version,
            lastModified = updatedUser.lastModified
        ).executeAsOne()
        
        if (rowsAffected == 0L) {
            throw OptimisticLockException("User was modified by another transaction")
        }
    }
} catch (e: OptimisticLockException) {
    // Handle concurrent modification
    println("Update failed due to concurrent modification: ${e.message}")
    // Retry logic or user notification
}

/**
 * A type that can be closed
 * Platform-specific implementations:
 * - JVM: actual typealias Closeable = java.io.Closeable
 * - Native/JS: Platform-specific implementations
 */
expect interface Closeable {
    /**
     * Close any resources backed by this object
     */
    fun close()
}

/**
 * Run body on the receiver and call Closeable.close before returning or throwing
 * Ensures proper resource cleanup using try-with-resources pattern
 */
expect inline fun <T : Closeable?, R> T.use(body: (T) -> R): R

import app.cash.sqldelight.db.Closeable

// Automatic resource cleanup
driver.use { database ->
    val users = database.executeQuery(
        identifier = null,
        sql = "SELECT * FROM users",
        mapper = { cursor -> /* ... */ },
        parameters = 0
    ).value
    
    processUsers(users)
    // driver.close() is called automatically even if an exception occurs
}

// Manual resource management
val driver = createSqlDriver()
try {
    val result = driver.executeQuery(...)
    processResult(result)
} finally {
    driver.close()
}

// Connection pooling scenario
class DatabaseManager : Closeable {
    private val drivers = mutableListOf<SqlDriver>()
    
    fun getDriver(): SqlDriver {
        return drivers.firstOrNull() ?: createNewDriver().also { drivers.add(it) }
    }
    
    override fun close() {
        drivers.forEach { it.close() }
        drivers.clear()
    }
}

// Using with connection pooling
DatabaseManager().use { manager ->
    val driver = manager.getDriver()
    val users = driver.executeQuery(...)
    // All drivers are closed when manager is closed
}