Taula de continguts

SQLDelight és un plugin que genera tot el codi necessari per gestionar una base de dades a partir de sentències SQL.

Introducció

SQLDelight verifica els teus esquemes, sentències i migracions en temps de compilació i afegeix funcions a la IDE com autocompletar i refactoritzar de tal manera que facilita escriure i matenir codi SQL.

En aquest enllaç tens el projecte de consulta: https://gitlab.com/xtec/kotlin/sqldelight

Entorn de treball

Crea un nou projecte compose-navigation amb amb Kotlin Multiplatform wizard.

Configurar el projecte

Per configurar el projecte has de modificar el fitxer composeApp/build.gradle.kts.

Si tens algun dubte pots mirar aquest fitxer: build.gradle.kts

Modifica la secció plugins:

plugins {
    id("app.cash.sqldelight") version "2.0.2"
    kotlin("plugin.serialization") version "2.1.0"
    // ...
}

Modifica la secció sourceSets:

 androidMain.dependencies {
    implementation("app.cash.sqldelight:android-driver:2.0.2")
    // ...
}
commonMain.dependencies {
    implementation("org.jetbrains.androidx.navigation:navigation-compose:2.8.0-alpha10")
    implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.7.3")
    implementation("org.slf4j:slf4j-nop:1.7.36")
    // ...
}
desktopMain.dependencies {
    implementation("app.cash.sqldelight:sqlite-driver:2.0.2")
    // ...
}

Afegeix la configuració de sqldelight:

sqldelight {
    databases {
        create("Database") {
            packageName.set("dev.xtec.data")
            dialect("app.cash.sqldelight:sqlite-3-38-dialect:2.0.2")
        }
    }
}

Crea una esquema

https://sqldelight.github.io/sqldelight/2.0.2/multiplatform_sqlite/

A continuació defineix l'esquem de la taula Dog.

Crea el fitxer composeApp/src/commonMain/sqldelight/dev/xtec/data/Dog.sq:

CREATE TABLE IF NOT EXISTS Dog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

Aquest fitxer crea una taula, però també pots crear indexs o afegir registres que han d'estar per defecte a la taula.

La tasca Gradle composeApp:generateSqlInterface utilitza les sentències SQL dels fitxer *.sq per generar de manera automàtica un conjunt de fitxers Kotlin amb tota la lògica d'accés a la base de dades en el directori composeApp/build/generated/:

> .\gradlew composeApp:generateSqlInterface

Pots veure que ha partir del fitxer SQL s'han generat el fitxers: Database.kt, Dog.kt i DatabaseImpl.kt.

Aquesta tasca s'executa de manera automàtic quan executes executes l'aplicació.

Configura el driver

Modifica el fitxer App.kt

package dev.xtec

import androidx.compose.material.MaterialTheme
import androidx.compose.material.Text
import androidx.compose.runtime.*
import app.cash.sqldelight.db.SqlDriver
import dev.xtec.data.Database

// Paràmtres de configuració del driver
object DatabaseConfig {
    val name: String = "pets.db"
    val development: Boolean = true
}

@Composable
fun App(sqlDriver: SqlDriver) {

    val database = Database(sqlDriver)

    // Crea l'esquema a la base de dades
    Database.Schema.create(sqlDriver)

    MaterialTheme {
        Text("TODO")
    }
}

A continuació has de crear el driver en els diferents ...

composeApp/src/androidMain/kotlin/dev/xtec/MainActivity.kt

// ...

class MainActivity : ComponentActivity() {

        // ...

        if (DatabaseConfig.development) {
            this.applicationContext.deleteDatabase(DatabaseConfig.name)
        }

        val driver = AndroidSqliteDriver(Database.Schema, this.applicationContext, DatabaseConfig.name)

        setContent {
            App(driver)
        }
    }
}

composeApp/src/desktopMain/kotlin/dev/xtec/MainActivity.kt

// ...
fun main() = application {
    
    if (DatabaseConfig.development) {
        Path(DatabaseConfig.name).deleteIfExists()
    }

    val driver = JdbcSqliteDriver("jdbc:sqlite:${DatabaseConfig.name}")

    Window(
        onCloseRequest = ::exitApplication,
        title = "sqldelight",
    ) {
        App(driver)
    }
}

composeApp/src/wasmJsMain/kotlin/dev/xtec/MainActivity.kt

// ...
@OptIn(ExperimentalComposeUiApi::class)
fun main() {
    ComposeViewport(document.body!!) {
        //App()
    }
}

Executa l'aplicació

Executa l'aplicació "Desktop"

Pots veure que a l'executar l'aplicació s'ha creat el fitxer composeApp/pets.db amb la taula Dog.

Obre la base de dades pets.db amb Sqlite:

> sqlite3.exe .\composeApp\pets.db

sqlite> .schema Dog
CREATE TABLE Dog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

sqlite> .quit

Consulta

A més de l'esquema, també pots generar funcions a partir de sentències SQL.

L'únic que has de fer és anotar la sentència SQL del fitxer .sq per tal de que SQLDelight generi la funció corresponent.

Per exemple, pots fer que SQLDelight generi la classe DogQueries amb les funcions select() i insert():

CREATE TABLE IF NOT EXISTS Dog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

insert:
INSERT INTO Dog(name) VALUES (?);

select:
SELECT * FROM Dog;

Modifica el fitxer App.kt:

@Composable
fun App(sqlDriver: SqlDriver) {

    val database = Database(sqlDriver)
    Database.Schema.create(sqlDriver)
    if (DatabaseConfig.development) {
        insertDevelopmentData(database)
    }

    MaterialTheme {
        DogListScreen(database)
    }
}

// Inserim unes dades de mostra
fun insertDevelopmentData(db: Database) {

    // Database té l'atribut `dogQueries` de tipus `DogQueries`.
    db.dogQueries.insert("Pujol")
    db.dogQueries.insert("Ketzu")
    db.dogQueries.insert("Indy")
}

@Composable
fun DogListScreen(db: Database) {

    // Consultem la taula de gossos
    val dogs = db.dogQueries.select().executeAsList()

    LazyColumn { items(dogs) { dog -> Text(dog.name) } }
}

L'objecte Query<Dog> té la funció executeAsList() que torna una llista amb tots els resultats de la consulta.

Executa l'aplicació Desktop: pots veure que una llista amb els gossos.

Android

Executa l'aplicació Android.

Obre la base de dades en el Database Inspector:

1. Des de la barra del menú, selecciona View > Tool Windows > App Inspection.
2. Selecciona el Database Inspector tab.
3. Les bases de dades de l'aplicació que s'està executant apareixen en el panell Databases
4. Expandeix els nodes de la base de dades pets.db.

Fes doble-clic a Dog per veure tots els gossos:

Recorda que la base de dades està en el dispositiu, no en el teu Windows!

Tens més informació en aquest enllaç: Debug your database with the Database Inspector

Arguments

Si una sentència SQL té arguments, la funció que es genera tindrà també els arguments que necessita la sentència SQL.

En el nostre exemple tenim la sentència SQL "insert":

insert:
INSERT INTO Dog(name) VALUES (?);

La sentència INSERT espera un valor per l'atribute name.

Per tant, la funció Kotlin que es genera té un paràmetre name de tipus String:

DogQueries.insert(name: String)

Normalment SqlDelight pot deduir el nom dels paràmetres, però si no pot, o vull utilitzar un altre nom, puc especificar el nom que s'ha d'utlitzar.

Modifica la senèntica SELECT per limitar el número de resulats amb LIMIT i fer un "offset" dels resultats amb OFFSET.

En aquest cas, com que SQLDelight no pot deduïr els noms els indiques de manera expressa amb nom :limit i :offset:

select:
SELECT * FROM Dog LIMIT :limit OFFSET :offset;

Com que has modificar el fitxer Dog.sq, ara la funció select té paràmetre i has d'adaptar el codi:

@Composable
fun DogListScreen(db: Database) {
    val dogs = db.dogQueries.select(0,10).executeAsList()

    LazyColumn { items(dogs) { dog -> Text(dog.name) } }
}

One

Afegeix una consulta selectById que torni un gos mitjançant la id:

Solució

selectById:
SELECT * FROM Dog WHERE id = ?;

Crea la funció DogScreen.

@Composable
fun DogScreen(db: Database, dogId: Long) {

    val dog = db.dogQueries.selectById(dogId).executeAsOne()
    Text(dog.name)
}

Com que la funció selectById nomé torna un result pots executar la consulta amb executeAsOne().

Si no estas segur de si tornarà un únic resultat o null, utilitza la funció executeAsOneOrNull().

Disseny

Al dissenya una aplicació has de separar la lògica de presentació de la d'accés a dades.

Refactoritza la funció DogScreen i crea la funció de presentació DogView.

@Composable
fun DogScreen(controller: NavController, db: Database, route: DogRoute) {

    val dog = db.dogQueries.selectById(route.id).executeAsOne()

    Column {
        Nav(controller)
        DogView(dog)
    }
}

@Composable
fun DogView(dog: Dog) {
    Text(dog.name)
}

Important. El @Preview de moment no funciona, però s'ha de fer així

Insert with return

Des de la versió 3.35.0 (2021-03-12)

A vegades quan fas un INSERT vols que la consulta retorni la fila que s'ha creat perquè hi ha dades que crea la base de dades, com és el cas de la clau primari subrogada.

Afegeix la consulta insertWithReturn al fitxer Dog.sq:

insertWithReturn:
INSERT INTO Dog(name) VALUES (?) RETURNING *;

Observació. RETURNING només està disponible a partir de la versió 3.35.0.

Registra un gos a la base de dades:

 val idefix = db.dogQueries.insertWithReturn("Idefix").executeAsOne()
println(idefix)

A diferència de la funció insert, amb la funció insertWithReturn has d'executar la consulta.

El resultat és el gos amb el valor de l'atribut id generat per la base de dades.

Dog(id=6, name=Idefix)

Activitat

1.- Afegeix la barra de navegació

2.- Fes que la llista de gossos sigui clicable i et porti al gos.

Activitat

A continuació tens un disseny ampliat:

classDiagram
direction LR

class Dog {
  id integer primary key
  name text not null
  number text
}

class Breed {
  id integer primary key
  name text not null
  description text not null
}

class Owner {
  id integer primary key
  name text not null
}

Dog --> "NUL" Breed
Dog --> "NUL" Owner

Observacions Un gos pot no tenir owner (està abandonat), no tenir número de registre al REIAC (number) o no ser de raça (breed).

https://www.sqlite.org/foreignkeys.html

Escriu els fitxers Breed.sq, Dog.sq i Owner.sq

Solució

CREATE TABLE IF NOT EXISTS Dog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    number TEXT,
    breed INTEGER,
    owner INTEGER,
    FOREIGN KEY (breed) REFERENCES Breed(id),
    FOREIGN KEY (owner) REFERENCES Owner(id)
);

insert:
INSERT INTO Dog(name, number, breed, owner) VALUES (?, ?,?,?) RETURNING *;

select:
SELECT * FROM Dog LIMIT :limit OFFSET :offset;

selectById:
SELECT * FROM Dog WHERE id = ?;

...

Modifica el fitxer App.kt:

1. Omple la base de dades amb unes quantes races que han de constar per defecte a la taula Breed.

2. Registra alguns gossos, alguns amb propietari, altres no, alguns de raça, altres no.

3. Pensa en algunes consultes que ha de tenir la teva aplicació, modifica els fitxers sq correspoenents, i prova en codi que funciona.

4. Implementa el mètode update i delete en alguna taula i prova en codi que funciona.

Mai has de dissenyar consultes genèriques perqué si!!

Cada consulta ha d'estar pensada i implica avaluar la necessitat de crear els índexs corresponents a la base de dades.

TODO

• https://medium.com/@santimattius/instant-search-using-sqldelight-and-flows-in-kmp-1c70bc17fdeb
• https://funkymuse.dev/posts/sql-delight-kmp/
• https://www.jetbrains.com/help/kotlin-multiplatform-dev/multiplatform-ktor-sqldelight.html

Configuració driver:

• https://sqldelight.github.io/sqldelight/2.0.2/jvm_sqlite/foreign_keys/
• https://stackoverflow.com/questions/65425352/sqldelight-slow-performance-compared-to-room