(Local Database)

Deprecated section

SQL delight is for now no more compatible with the new default WASM template for WebApp application.

If you still want to use it you can revert to the old Js(IR) template.

Notice that for now this is the only Web target compatible database library for KMP

SQLDelight generates typesafe Kotlin APIs from your SQL statements. It verifies your schema, statements, and migrations at compile-time and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple.

SQLDelight understands your existing SQL schema.

CREATE TABLE hockey_player (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  number INTEGER NOT NULL
);

It generates typesafe code for any labeled SQL statements.

Warning

Be carefull with SQL Delight , the project and his dependancies just move from com.squareup.sqldelight.* to app.cash.sqldelight.*

Pay attention also with beta, alpha version of Android studio that could produce bugs on gradle task management for code generation of SQL Delight databases.

🧪 Add sqldelight db to your quizz

Refer to the multiplatform implementation of SQLDelight in official Github pages 👉 https://cashapp.github.io/sqldelight/2.0.0/multiplatform_sqlite/

Add the correct dependancies to the project

plugins {
...
    id("app.cash.sqldelight") version "2.0.0"
}
...
 sourceSets {
        val commonMain by getting {
            dependencies {
              ...
                implementation("app.cash.sqldelight:runtime:2.0.0")
                implementation("app.cash.sqldelight:coroutines-extensions:2.0.0")
                implementation("org.jetbrains.kotlinx:kotlinx-datetime:0.4.1")

            }
        }
        val androidMain by getting {
            dependencies {
               ...
                implementation("app.cash.sqldelight:android-driver:2.0.0")

            }
        }
    ...
        val iosMain by creating {
         ...
            dependencies {
                ...
                implementation("app.cash.sqldelight:native-driver:2.0.0")
            }
        }
        val desktopMain by getting {
            dependencies {
               ...
                implementation("app.cash.sqldelight:sqlite-driver:2.0.0")
            }
        }
        ...

If there is no network and it's the first time launch of the app : handle and error
if there is no network and you have db datas : return on the flow the db data
if there is network and db data are younger than 5 min : return on the flow the db data
if there is network and db data are older than 5 min : retourn on the flow the network data and reset db data

🎯 Solutions

QuizDatabase.sq (ressources of commonMain)*

CREATE TABLE update_time (
     timestamprequest INTEGER
);

INSERT INTO update_time(timestamprequest) VALUES (0);

CREATE TABLE questions (
    id INTEGER PRIMARY KEY,
    label TEXT NOT NULL,
    correctAnswerId INTEGER  NOT NULL
 );


 CREATE TABLE answers (
    id INTEGER NOT NULL,
    label TEXT NOT NULL,
    question_id INTEGER NOT NULL,
    PRIMARY KEY (id, question_id),
    FOREIGN KEY (question_id)
      REFERENCES questions (id)
          ON UPDATE CASCADE
          ON DELETE CASCADE
 );



 selectUpdateTimestamp:
 SELECT *
 FROM update_time;

 insertTimeStamp:
 INSERT INTO update_time(timestamprequest)
 VALUES (:timestamp);

 deleteTimeStamp:
 DELETE FROM update_time;

 deleteQuestions:
 DELETE FROM questions;

 deleteAnswers:
 DELETE FROM answers;


 selectAllQuestionsWithAnswers:
 SELECT *
 FROM questions
 INNER JOIN answers ON questions.id = answers.question_id;

 insertQuestion:
 INSERT INTO questions(id, label,correctAnswerId)
 VALUES (?, ?, ?);

 insertAnswer:
 INSERT INTO answers(id, label,question_id)
 VALUES (?, ?, ?);

network/QuizDB.kt (commonMain)

package network


import app.cash.sqldelight.async.coroutines.awaitAsList
import app.cash.sqldelight.async.coroutines.awaitAsOneOrNull
import app.cash.sqldelight.db.SqlDriver
import com.myapplication.common.cache.Database
import kotlinx.coroutines.CoroutineScope
import network.data.Answer
import network.data.Question

class QuizDbDataSource(private val sqlDriver: SqlDriver, private val coroutineScope: CoroutineScope) {

    private  var database=Database(sqlDriver)
    private  var quizQueries=database.quizDatabaseQueries


    suspend fun getUpdateTimeStamp():Long = quizQueries.selectUpdateTimestamp().awaitAsOneOrNull()?.timestamprequest ?: 0L


    suspend fun setUpdateTimeStamp(timeStamp:Long)  {
        quizQueries.deleteTimeStamp()
        quizQueries.insertTimeStamp(timeStamp)
    }

     suspend fun getAllQuestions(): List<Question> {
         return quizQueries.selectAllQuestionsWithAnswers().awaitAsList()

             .groupBy {it.question_id }
             .map { (questionId, rowList) ->

             Question(
                 id = questionId,
                 label = rowList.first().label,
                 correctAnswerId = rowList.first().correctAnswerId,
                 answers = rowList.map { answer ->
                     Answer(
                         id = answer.id_,
                         label = answer.label_
                     )
                 }
             )
         }
     }



    suspend fun insertQuestions(questions:List<Question>) {
        quizQueries.deleteQuestions();
        quizQueries.deleteAnswers()
        questions.forEach {question ->
            quizQueries.insertQuestion(question.id, question.label, question.correctAnswerId)
            question.answers.forEach {answer ->
                quizQueries.insertAnswer(answer.id,answer.label,question.id)
            }
        }
    }
}

QuizRepository.kt

package network

import app.cash.sqldelight.db.SqlDriver
import kotlinx.coroutines.CoroutineScope
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.flow.MutableStateFlow
import kotlinx.coroutines.flow.update
import kotlinx.coroutines.launch
import kotlinx.datetime.Clock
import network.data.Question


class QuizRepository(sqlDriver: SqlDriver)  {

    private val mockDataSource = MockDataSource()
    private val quizAPI = QuizApiDatasource()
    private val coroutineScope = CoroutineScope(Dispatchers.Main)
    private var quizDB = QuizDbDataSource(sqlDriver,coroutineScope)

    private var _questionState=  MutableStateFlow(listOf<Question>())
    var questionState = _questionState

    init {
        updateQuiz()
    }

    private suspend fun fetchQuiz(): List<Question> = quizAPI.getAllQuestions().questions

    private suspend fun fetchAndStoreQuiz(): List<Question>{
        val questions  = fetchQuiz()
        quizDB.insertQuestions(questions)
        quizDB.setUpdateTimeStamp(Clock.System.now().epochSeconds)
        return questions
    }
    private fun updateQuiz(){


        coroutineScope.launch {
            _questionState.update {
                try {
                    val lastRequest = quizDB.getUpdateTimeStamp()
                    if(lastRequest == 0L || lastRequest - Clock.System.now().epochSeconds > 300000){
                        fetchAndStoreQuiz()
                    }else{
                        quizDB.getAllQuestions()
                    }
                } catch (e: NullPointerException) {
                    fetchAndStoreQuiz()
                } catch (e: Exception) {
                    e.printStackTrace()
                    mockDataSource.generateDummyQuestionsList()
                }

            }
        }
    }
}

More databases options

For not using SQLight ORM, you can use Realm kotlin or KStore

✅ If everything is fine, go to the next chapter →

(Local Database)

🧪 Add sqldelight db to your quizz

Add the correct dependancies to the project

Create the native SQL driver factory and use it for creating the DB with `actual`/`expect` kotlin keywords

Read carefully the modelisation UML below

Create you SQLDelight model 'QuizDatabase.sq'

Create your Database datasource by generating insert and update suspending functions

Update your repository by instanciating your database

🎯 Solutions

📖 Further reading