SQLite Helper

Setup SQLiteOpenHelper

open class DatabaseHelper(private val context: Context) :
    SQLiteOpenHelper(context, "app.db", null, 6) {
    val tableName = "questions"
    val cId = "id"
    val cTitle = "title"
    val cDescription = "description"
    val cSolved = "solved"

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            """
            CREATE TABLE $tableName (
                $cId TEXT PRIMARY KEY,
                $cTitle TEXT,
                $cDescription TEXT,
                $cSolved NUMERIC
            )                """.trimIndent()
        )    }
    override fun onUpgrade(
        db: SQLiteDatabase,
        p1: Int,
        p2: Int
    ) {
        db.execSQL(
            """
            DROP TABLE IF EXISTS $tableName
        """.trimIndent()
        )        onCreate(db)
    }}

Table Creation

CREATE TABLE IF NOT EXISTS table_name (

    -- ╔══════════════════════════════════════╗
    -- ║              TYPES                   ║
    -- ╚══════════════════════════════════════╝

    col1    TEXT,       -- String, UUID, date
    col2    INTEGER,    -- Int, Long, Boolean (0/1)
    col3    REAL,       -- Float, Double
    col4    BLOB,       -- ByteArray, binary
    col5    NUMERIC,    -- auto picks INTEGER or REAL


    -- ╔══════════════════════════════════════╗
    -- ║           PRIMARY KEYS               ║
    -- ╚══════════════════════════════════════╝

    id      INTEGER PRIMARY KEY AUTOINCREMENT,  -- auto int (implied NOT NULL + UNIQUE)
    id      TEXT    PRIMARY KEY,                -- manual UUID  (implied NOT NULL + UNIQUE)


    -- ╔══════════════════════════════════════╗
    -- ║            CONSTRAINTS               ║
    -- ╚══════════════════════════════════════╝

    col     TEXT    NOT NULL,                   -- required, no null allowed
    col     TEXT    UNIQUE,                     -- no duplicate values
    col     TEXT    NOT NULL UNIQUE,            -- required + no duplicates
    col     INTEGER DEFAULT 0,                  -- fallback if not provided
    col     INTEGER NOT NULL DEFAULT 0,         -- required with fallback
    col     TEXT    NOT NULL DEFAULT '',        -- required with empty string fallback


    -- ╔══════════════════════════════════════╗
    -- ║          CHECK CONSTRAINTS           ║
    -- ╚══════════════════════════════════════╝

    age     INTEGER CHECK(age >= 0),                            -- must be positive
    rating  REAL    CHECK(rating BETWEEN 0 AND 5),             -- range check
    status  TEXT    CHECK(status IN ('open','closed','pending')), -- enum-like


    -- ╔══════════════════════════════════════╗
    -- ║            FOREIGN KEYS              ║
    -- ╚══════════════════════════════════════╝

    user_id TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),                 -- basic link
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,   -- delete child when parent deleted
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,  -- null child when parent deleted
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,  -- block delete if child exists


    -- ╔══════════════════════════════════════╗
    -- ║         COMPOSITE PRIMARY KEY        ║
    -- ╚══════════════════════════════════════╝

    PRIMARY KEY (user_id, question_id)          -- must go at the end
)

CRUD operation

• use writableDatabase for db except Read

Create

• insert method (return row_id if the operation failed return -1L)

Note: row_id is a id for sqlite, not the primary key define in schema. row_id will be the schema id when the id is define with INTEGER PRIMARY KEY

val values = ContentValues().apply {
    put(cId, UUID.randomUUID().toString())
    put(cTitle, title)
    put(cDescription, description)
    put(cSolved, 0)
}
db.insert(tableName,null,values)

• raw query

db.execSQL(
    """
    INSERT INTO $tableName ($cId, $cTitle, $cDescription, $cSolved)
    VALUES (?,?,?,?);
    """.trimIndent(),
    arrayOf(UUID.randomUUID().toString(), title, description, 0)
)

Read

• query method

val cursor = db.query(
  TABLE_NAME, // table
  null, // columns — null = SELECT *
  null, // WHERE
  null, // WHERE args
  null, // GROUP BY
  null, // HAVING
  null, // ORDER BY
  null // LIMIT
)

• raw query

val cursor = db.rawQuery(
    """
    SELECT * FROM $tableName
""".trimIndent(),
  null
)

• example of cursor read each row

questions.clear()
while (it.moveToNext()) {
    questions.add(
    Question(
            id = it.getString(it.getColumnIndexOrThrow(cId)),
        title = it.getString(it.getColumnIndexOrThrow(cTitle)),
          description = it.getString(it.getColumnIndexOrThrow(cDescription)),
            solved = it.getInt(it.getColumnIndexOrThrow(cSolved)) == 1
        )
    )
    }
it.close()

Update

• update method (return number of rows affected)

val values = ContentValues().apply {
  put("title", newTitle)
  put("content", newContent)
}

val selection =
val selectionArgs = arrayOf(id.toString())

return db.update("NotesTable", values, "id = ?", selectionArgs)

• raw query

db.execSQL(
    """
    UPDATE $tableName
    SET $cSolved = ${if (solved) 1 else 0}
    WHERE $cId = ?;
""".trimIndent(),
  arrayOf(id)
)

Delete

• delete method (return number of rows affected)

val rows = db.delete(
  TABLE_NAME, // table
  "$C_ID = ?", // WHERE clause — null = delete all
  arrayOf(id) // WHERE args
)

• raw query

db.execSQL(
    """
    DELETE FROM $tableName WHERE $cId = ?;
""".trimIndent(),
  arrayOf(id)
)