ROOMを使わずにSQLiteを扱う、Androidアプリ開発

package com.apppppp.supermemo2.data

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


const val SQL_CREATE_ENTRIES = """
CREATE TABLE `memo` (
  `id` INTEGER PRIMARY KEY,
  `parent_id` INTEGER,
  `title` TEXT DEFAULT NULL,
  `done_flg` INTEGER DEFAULT 0
);
"""

const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS memo"

class DbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_ENTRIES)
    }
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
//        db.execSQL(SQL_DELETE_ENTRIES)
//        onCreate(db)
        if (oldVersion < 2) {
            // バージョン1から2へのアップグレード処理
            val addCreatedColumn = "ALTER TABLE memo ADD COLUMN `created` TEXT DEFAULT NULL"
            val addModifiedColumn = "ALTER TABLE memo ADD COLUMN `modified` TEXT DEFAULT NULL"
            db.execSQL(addCreatedColumn)
            db.execSQL(addModifiedColumn)
        }
    }
    override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        onUpgrade(db, oldVersion, newVersion)
    }
    companion object {
        const val DATABASE_VERSION = 2
        const val DATABASE_NAME = "SuperMemo2.db"
    }
}

class MemoRepository(private val context: Context) {

    private val dbHelper: DbHelper = DbHelper(context)
    val db = dbHelper.writableDatabase

    /**
     * メモを新規作成
     *
     * @param title メモのタイトル
     * @param parentId 親メモのID
     * @return 作成したメモ
     */
    fun insertMemo(title: String, parentId:Int? = null): Memo {
        val now = LocalDateTime.now()
        val formattedDate = now.formatToString() // "2023-01-01T12:00:00" など

        val values = ContentValues().apply {
            put("parent_id", parentId)
            put("title", title)
            put("done_flg", 0)
            put("created", formattedDate)
        }
        // 新しい行を挿入し、その行のIDを返す
        val newRowId = db.insert("memo", null, values)

        return Memo(
            id = newRowId.toInt(),
            parentId = parentId,
            title = title,
            doneFlg = false
        )
    }

    /**
     * parentIdのdoneFlgを更新
     */
    fun updateDoneFlg(memoId: Int, doneFlg:Boolean) {
        val now = LocalDateTime.now()
        val formattedDate = now.formatToString() // "2023-01-01T12:00:00" など

        val values = ContentValues().apply {
            put("done_flg", if (doneFlg) 1 else 0)
            put("modified", formattedDate)
        }
        db.update("memo", values, "id = ?", arrayOf(memoId.toString()))
    }

    /**
     * parentIdに一致するメモを取得
     *
     * @param parentId 親メモのID
     * @return parentIdに一致するメモのリスト
     */
    fun searchMemos(parentId: Int?): List<Memo> {
        val memos = mutableListOf<Memo>()

        // parentIdがnullかどうかに応じたSQLクエリ
        val sql = if (parentId == null) {
            "SELECT * FROM memo WHERE parent_id IS NULL ORDER BY created ASC"
        } else {
            "SELECT * FROM memo WHERE parent_id = ? ORDER BY created ASC"
        }

        // rawQueryメソッドを使用してSQLクエリを実行
        val cursor = if (parentId == null) {
            db.rawQuery(sql, null)
        } else {
            db.rawQuery(sql, arrayOf(parentId.toString()))
        }

        with(cursor) {
            while (moveToNext()) {
                val id = getInt(getColumnIndexOrThrow("id"))
                val obtainedParentId = if (!isNull(getColumnIndexOrThrow("parent_id"))) getInt(getColumnIndexOrThrow("parent_id")) else null
                val title = getString(getColumnIndexOrThrow("title"))
                val doneFlg = getInt(getColumnIndexOrThrow("done_flg")) != 0

                memos.add(Memo(id, obtainedParentId, title, doneFlg))
            }
            close()
        }

        return memos
    }

    /**
     * dbを閉じる
     */
    fun close() {
        dbHelper.close()
    }
}

object AppContainer {
    private var repository: MemoRepository? = null

    fun initialize(context: Context) {
        repository = MemoRepository(context)
    }

    private val uiStateRepository: UiStateRepository by lazy {
        UiStateRepository()
    }

    val viewModel by lazy {
        if (repository == null) throw IllegalStateException("Repository must be initialized")
        MemoViewModel(repository!!, uiStateRepository)
    }
}

class MainActivity : ComponentActivity() {
    private lateinit var memoRepository: MemoRepository

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        AppContainer.initialize(applicationContext)
        val viewModel = AppContainer.viewModel

        setContent {
            AppTheme {
                MainApp(viewModel)
            }
        }
    }

    override fun onDestroy() {
        memoRepository.close()
        super.onDestroy()
    }
}

class MemoViewModel(
    private val dbMemoRepos: MemoRepository,
    private val uiStateRepository: UiStateRepository
) : ViewModel() {

    val uiState: StateFlow<UiState> = uiStateRepository.uiState

    init {
        Log.d("mopi", "MemoViewModel init")
        setParentMemos()
    }

    /**
     * 親メモのuiStateを更新
     */
    fun setParentMemos() {
        viewModelScope.launch {
            val memos = dbMemoRepos.searchMemos()
            ...
        }
    }

    /**
     * リポジトリへ新規メモを追加
     */
    fun insertMemo(title: String, parentId: Int? = null) {
        viewModelScope.launch {
            val newMemo = dbMemoRepos.insertMemo(title, parentId)
        }
    }

    // メモのチェックフラグを更新
    fun updateMemoCheckedState(memoId: Int, checked: Boolean) {
        viewModelScope.launch {
            dbMemoRepos.updateDoneFlg(memoId, checked)
            uiStateRepository.setChildMemoDoneFlg(memoId, checked)
        }
    }

    ...

}