Working with SQLite Database

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "MyApp.db"
        private const val DATABASE_VERSION = 1
    }

    override fun onCreate(db: SQLiteDatabase) {
        val CREATE_TABLE = """
            CREATE TABLE users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE
            )
        """.trimIndent()
        db.execSQL(CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE users ADD COLUMN phone TEXT")
        }
    }
}

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

val db = dbHelper.writableDatabase
val values = ContentValues().apply {
    put("name", "John Doe")
    put("email", "john@example.com")
    put("age", 30)
}
val newRowId = db.insert("users", null, values)

val db = dbHelper.readableDatabase
val cursor = db.query(
    "users",
    arrayOf("id", "name", "email"),
    null,
    null,
    null,
    null,
    "name ASC"
)

while (cursor.moveToNext()) {
    val id = cursor.getInt(0)
    val name = cursor.getString(1)
    val email = cursor.getString(2)
    Log.d("User", "ID: $id, Name: $name, Email: $email")
}
cursor.close()

val db = dbHelper.writableDatabase
val values = ContentValues().apply {
    put("email", "john.doe@example.com")
}
val rowsAffected = db.update(
    "users",
    values,
    "id = ?",
    arrayOf("1")
)

val db = dbHelper.writableDatabase
val rowsDeleted = db.delete(
    "users",
    "id = ?",
    arrayOf("1")
)

val contentValues = ContentValues().apply {
    put("name", "Alice")
    put("age", 30)
    put("salary", 60000.0)
}

val db = dbHelper.writableDatabase
val newRowId = db.insert("employees", null, contentValues)

val contentValues = ContentValues().apply {
    put("salary", 65000.0)
}

val db = dbHelper.writableDatabase
val rowsAffected = db.update(
    "employees",
    contentValues,
    "name = ?",
    arrayOf("Alice")
)

val db = helper.readableDatabase
val cursor = db.query(
    "users",
    arrayOf("id", "name", "email"),
    null,
    null,
    null,
    null,
    "name ASC"
)
while (cursor.moveToNext()) {
    val id = cursor.getInt(0)
    val name = cursor.getString(1)
    val email = cursor.getString(2)
    println("User: $name, Email: $email")
}
cursor.close()

val db = helper.readableDatabase
val sql = "SELECT * FROM users WHERE age > ? AND city = ?"
val cursor = db.rawQuery(sql, arrayOf("25", "New York"))
while (cursor.moveToNext()) {
    val name = cursor.getString(cursor.getColumnIndex("name"))
    println("Name: $name")
}
cursor.close()

val db = helper.readableDatabase
val sql = "SELECT COUNT(*) FROM users WHERE email LIKE ?"
val cursor = db.rawQuery(sql, arrayOf("%.com"))
if (cursor.moveToFirst()) {
    val count = cursor.getInt(0)
    println("Total users with .com email: $count")
}
cursor.close()

val cursor: Cursor = db.query(
    "users",
    arrayOf("id", "name", "email"),
    null,
    null,
    null,
    null,
    "name ASC"
)

while (cursor.moveToNext()) {
    val id = cursor.getInt(cursor.getColumnIndex("id"))
    val name = cursor.getString(cursor.getColumnIndex("name"))
    val email = cursor.getString(cursor.getColumnIndex("email"))

    println("User: $id, $name, $email")
}

cursor.close()

val columnIndex = cursor.getColumnIndex("name")
val name = cursor.getString(columnIndex)

val nameIndex = cursor.getColumnIndexOrThrow("name")
val emailIndex = cursor.getColumnIndexOrThrow("email")

val name = cursor.getString(nameIndex)
val email = cursor.getString(emailIndex)

val db = databaseHelper.writableDatabase

db.beginTransaction()
try {
    val insert1 = ContentValues().apply {
        put("name", "John")
        put("age", 30)
    }
    db.insert("users", null, insert1)

    val insert2 = ContentValues().apply {
        put("name", "Jane")
        put("age", 25)
    }
    db.insert("users", null, insert2)

    db.setTransactionSuccessful()
} catch (e: Exception) {
    // Handle exception
} finally {
    db.endTransaction()
}

val db = databaseHelper.writableDatabase

db.beginTransaction()
try {
    // Insert user
    val userValues = ContentValues().apply {
        put("name", "Alice")
        put("email", "alice@example.com")
    }
    val userId = db.insert("users", null, userValues)

    // Insert address within same transaction
    val addressValues = ContentValues().apply {
        put("userId", userId)
        put("street", "123 Main St")
        put("city", "Anytown")
    }
    db.insert("addresses", null, addressValues)

    db.setTransactionSuccessful()
} catch (e: Exception) {
    // Handle exception
} finally {
    db.endTransaction()
}

@Database(
    entities = [User::class],
    version = 2,
    autoMigrations = [
        AutoMigration(
            from = 1,
            to = 2
        )
    ]
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE user ADD COLUMN last_login INTEGER")
    }
}

@Database(
    entities = [User::class],
    version = 2,
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao

    companion object {
        val DATABASE_NAME = "app_database"
    }
}

CREATE INDEX idx_users_email ON users (email);

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

val db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.CREATE_IF_NECESSARY)
// Perform database operations
db.close() // Closes the database connection