README.md

LiteSQLite

A thin wrapper library for android.database.sqlite.SQLiteOpenHelper, providing enhanced database operation capabilities through the use of Kotlin Coroutines.

Usage

val liteSQLite = LiteSQLite(context, "TestDatabase.db", 1)

liteSQLite.execSQL(
    """
    DROP TABLE IF EXISTS user;
    """,
    """
    CREATE TABLE user (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        gender TEXT NOT NULL
    )            
    """,
    """INSERT INTO user (id, name, gender) VALUES (0, 'John Doe', 'Male');""",
    """INSERT INTO user (id, name, gender) VALUES (1, 'Sam Smith', 'Male');""",
    """INSERT INTO user (id, name, gender) VALUES (2, 'Jane Doe', 'Female');""",
    """INSERT INTO user (id, name, gender) VALUES (3, 'Emma Brown', 'Female');"""
)

data class User(val id: Int, val name: String, val gender: String)

val records = liteSQLite.rawQuery("SELECT * FROM user ORDER BY id", null) { cursor ->
    User(
        id = cursor["id"] ?: 0,
        name = cursor["name"] ?: "",
        gender = cursor["gender"] ?: "",
    )    
}

CursorExtension.kt

import android.database.Cursor

inline operator fun <reified T> Cursor.get(column: String): T? {
    val columnIndex = getColumnIndex(column)
    if (columnIndex == -1) {
        // Column not found
        return null
    }

    return when (T::class) {
        Int::class -> getInt(columnIndex)
        String::class -> getString(columnIndex)
        Double::class -> getDouble(columnIndex)
        Float::class -> getFloat(columnIndex)
        Short::class -> getShort(columnIndex)
        ByteArray::class -> getBlob(columnIndex)
        else -> null
    } as T?
}

LiteSQLite.kt

import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext

/**
 * - This database helper class can be used to complement another OR mapper library.
 * - It does not take care of database migration.
 * - Don't forget to specify the database version you are using with your OR mapper.
 */
class LiteSQLite(
    context: Context,
    databaseName: String,
    databaseVersion: Int
) :
    SQLiteOpenHelper(
        context, databaseName,
        null,
        databaseVersion
    ) {

    override fun onCreate(db: SQLiteDatabase?) {
        // Do nothing
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // Do nothing
    }

    /**
     * Executes a raw SQL query and returns a list of entity.
     *
     * @param sql the SQL query to execute
     * @param selectionArgs the arguments to replace placeholders in the SQL query
     * @param mapper the function that maps raw data to an entity class instance
     * @return a List<T>
     */
    suspend fun <T> rawQuery(
        sql: String,
        selectionArgs: List<String>? = null,
        mapper: (Cursor) -> T
    ): List<T> = withContext(Dispatchers.IO) {
        val cursor = readableDatabase.rawQuery(sql, selectionArgs?.toTypedArray())

        val records = mutableListOf<T>()
        while (cursor.moveToNext()) {
            records.add(mapper(cursor))
        }
        cursor.close()

        records
    }

    suspend fun execSQL(
        vararg sql: String
    ) = withContext(Dispatchers.IO) {
        sql.forEach {
            writableDatabase.execSQL(it)
        }
    }

    suspend fun execSQL(
        sql: String,
        bindArgs: List<String>
    ) = withContext(Dispatchers.IO) {
        writableDatabase.execSQL(sql, bindArgs.toTypedArray())
    }
}