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 la carpeta sql:

> md sql
> cd sql

Crea el projecte sql amb Gradle:

gradle init --package sql --project-name sql --java-version 21 --type kotlin-application --dsl kotlin --test-framework kotlintest --no-split-project --no-incubating --overwrite

Construeix el projecte i obre'l amb Idea:

> .\gradlew build
> idea .

Modifica el fitxer build.gradle.kts:

plugins {
    ...
    id("app.cash.sqldelight") version "2.0.2"
}

dependencies {

    ...
    implementation("app.cash.sqldelight:sqlite-driver:2.0.2")
    implementation("org.slf4j:slf4j-nop:1.7.36")
}

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

Verifica que la versió de sqlite és superior a la 3.38:

sqlite > select sqlite_version()

Esquema

Crea el fitxer app/src/main/sqldelight/sql/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.

Al crear el fitxer, IDEA et dirà si vols instal.lar el plugin SQLDelight:

A partir d'aquestes sentències SQL, la tasca Gradle generateSqlInterface genera al directori build/generated/:

> .\gradlew generateSqlInterface

Pots veure que ha partir del fitxer SQL s'han generat el fitxers: Database.kt, Dog.kt i DatabaseImpl.kt en el directori app/build/generated:

Aquesta tasca s'executa de manera automàtica:

1. Pel plugin SQLDelight quan edites un fitxer .sq
2. Quan excutes gradlew build.

Modifica el fitxer App.kt:

package sql

import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.sqlite.JdbcSqliteDriver

import sql.data.*

fun main() {

    // Construim un driver amb una connexió JDBC
    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:pets.db")
    
    // Creem l'esquema a la base de dades
    Database.Schema.create(driver)

    // Podem crear una instància de gos
    val trufa = Dog(1,"Trufa")
    println(trufa) // Dog(id=1, name=Trufa)
}

Al executar l'aplicació, a més d'imprimir el valor trufa, has creat el fitxer pets.db amb la taula Dog.

Obre la base de dades pets.db amb Sqlite:

> sqlite3.exe .\pets.db

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

sqlite> .quit

Consultes

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 SQLDeligh 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;

A continuació tens un exemple d'us de la classe DogQueries:

import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.sqlite.JdbcSqliteDriver

import sql.data.*

fun main() {

    // Borrem la base de dades per començar sempre de cero (només desenvolupament)
    Path("pets.db").deleteIfExists()

    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:pets.db")
    Database.Schema.create(driver)

    // Creem una instància de Database amb el driver
    val db = Database(driver)

    // Inserim unes dades de mostra
    db.dogQueries.insert("Pujol")
    db.dogQueries.insert("Ketzu")
    db.dogQueries.insert("Indy")

    // Consultem la taula gossos amb màxim 2 resultats
    db.dogQueries.select().executeAsList().forEach { dog -> println(dog) }
}

Pots executar el codi tants cops com vulguis, el resultat és:

Dog(id=1, name=Pujol)
Dog(id=2, name=Ketzu)
Dog(id=3, name=Indy)

El codi crea un objecte Database amb el driver corresponent, i té l'atribut dogQueries que té una instància de DogQueries.

La classe DogQueries té les funcions:

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.

Per exemple, pots modificar 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:

```kt
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.sqlite.JdbcSqliteDriver

import sql.data.*

fun main() {

    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:pets.db")
    Database.Schema.create(driver)

    // Creem una instància de Database amb el driver
    val db = Database(driver)

    // Mirem si la taula està buida
    if (db.dogQueries.select(1,0).executeAsList().isEmpty()) {
        db.dogQueries.insert("Pujol")
        db.dogQueries.insert("Ketzu")
        db.dogQueries.insert("Indy")
    }

    // Consultem la taula gossos amb màxim 2 resultats
    db.dogQueries.select(5,0).executeAsList().forEach { dog -> println(dog) }
}

Llista

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

És molt útil si la llista és curta i anem ha utilitzar tots els resultats de la llista.

// Consultem la taula gossos amb màxim 2 resultats
db.dogQueries.select(2,0).executeAsList().forEach { dog -> println(dog) }

One

Si estas segur que la consulta només ha de tornar un únic resultat pots utilitzar la funció executeAsOne:

val query = db.dogQueries.select()
query.executeAsOne()

Però el nostre select retorna més d'un resultat i ...

Exception in thread "main" java.lang.IllegalStateException: 
ResultSet returned more than 1 row for Dog.sq:select
...

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

Solució

selectById:
SELECT * FROM Dog WHERE id = ?;

Executem el codi amb la nova consulta:

val query = db.dogQueries.selectById(2000)
query.executeAsOne()

I ara es produeix un NullPointerException perquè no existeix cap gos amb id 2000:

Exception in thread "main" java.lang.NullPointerException: 
ResultSet returned null for Dog.sq:selectById

Per utlitzar la funció executeAsOne() has d'estar segur que la consulta torna un, i només un resultat.

En altre cas és millor utilitzar la funció executeAsOneOrNull:

val query = db.dogQueries.selectById(2000)
val dog: Dog? = query.executeAsOneOrNull()
if (dog == null) {
  println("Not found")
} else {
  println(dog.name)
}

Ajuda: https://kotlinlang.org/docs/null-safety.html

El mètode insert retorna un objecte Query amb el resultat de la sentència INSERT INTO ....

Si vols obetnir el registre resultant (en aquest cas amb la id):

val maverick = db.dogQueries.insert("Maverick").executeAsOne()
println(maverick)

Si executes el codi el resultat és:

Dog(id=5, name=Maverick)

Cursor

Però si la llista és molt llarga, i vols processar molts elements de la llista és millor utilitzar un cursor:

val query = db.dogQueries.select(Long.MAX_VALUE,0)
query.execute { cursor ->
  while (cursor.next().value) {
    val dog = query.mapper(cursor)
    println(dog)
  }
  QueryResult.Unit
}

La manera més fàcil d'accedir a les dades, és mitjançant la funció mapper que forma part de las classe Query<Dog>, i que transforma el registre al que apunta el cursor a un objecte Dog.

Però si vols, pots accedir als elements de registre de manera posicional:

val query = db.dogQueries.select()
query.execute { cursor ->
  while (cursor.next().value) {
    println(cursor.getString(1))
  }
  QueryResult.Unit
}

No tens seguretat a nivell de tipus, però és molt més eficient si no necessites crear l'objecte!

I el que es evident, no cal que recorris tots els elements de la llista!

 // Consultem la taula gossos amb màxim 2 resultats
val query = db.dogQueries.select(Long.MAX_VALUE,0)
query.execute { cursor ->
    while (cursor.next().value) {
       val dog = query.mapper(cursor)
       println(dog)
       if (dog.name == "Ketzu") {
           break
       }
    }
    QueryResult.Unit
}

Listener

Quan estas processant un select en un entorn concurrent és possible que s'afegeixin o es borrin registres.

Pots registrar un "listener":

val query = db.dogQueries.select(Long.MAX_VALUE,0);
query.addListener {
    app.cash.sqldelight.Query.Listener { println("queryResultsChanged") }
}

però només té sentint en un entorn concurrent ...

TODO primer l'alumne ha de saber com s'executa en entorn concurrent.

Insert

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: Veterinari

Borra la taula Dog:

> sqlite3 drop table Dog;

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.

Transaccions

Si vols executar més d'una sentència dins una transacció has d'utlitzar la funció transaction():

db.transaction {
    val eva = db.ownerQueries.insert("Eva").executeAsOne()
    val milú = db.dogQueries.insert("Milú", null, null, eva.id).executeAsOne()
    println(milú)
}

El resultat és:

Dog(id=7, name=Milú, number=null, breed=null, owner=3)

Per tornar el valor des de una transacció, utilitza la funció transactionWithResult:

val dogs = db.transactionWithResult {
    db.dogQueries.select(5,0).executeAsList()
}
println(dogs)

Rollback

Si durant l'execució del codi es produeix una execpció, automàticamen es farà un "rollback" de la transació.

  try {
        db.transaction {
            db.dogQueries.insert("Kokoro", null, null, null)
            throw Exception("Ups!")
        }
    } catch (e: Exception) {
        println("No hi a gossos: ${db.dogQueries.select(5, 0).executeAsList().isEmpty()}")
    }

Si vols pots fer un "rollback" de la transacció en qualsevol moment (no es produeix cap excepció):

db.transaction {
    db.dogQueries.insert("Kokoro", null, null, null)
    rollback()
}
println("No hi a gossos: ${db.dogQueries.select(5, 0).executeAsList().isEmpty()}"

Però si la transacció retorna un valor, has de retornar un valor amb el rollback:

val dogs: Dog? = db.transactionWithResult {
    db.dogQueries.insert("Kokoro", null, null, null)
    rollback(null)
}

Callbacks

Pots registrar "callbacks" que s'executarn un con la transacció ha finalitzar o s'ha fet un "rollback":

val dog: Dog? = db.transactionWithResult {

    afterRollback { println("No s'ha afegit Kokoro") }
    afterCommit { println("S'ha afegit Kokoro") }

    db.dogQueries.insert("Kokoro", null, null, null)
    rollback(null)
}

Grouping Statements

TODO

You can group multiple SQL statements together to be executed at once inside a transaction:

Tipus

https://sqldelight.github.io/sqldelight/2.0.2/jvm_sqlite/types/

Activitat: Chinook

En l'activitat SQL - Relació vas utilitzar la base de dades "Chinook".

Entorn de treball:

• Borra els fitxers .sq (o crea un nou projecte)
• Afageix la base de dades chinook.sq a l'arrel del projecte
• Crea els fitxers .sq que corresponen a les taules de la base de dades (amb .showtables pots veure els esquemes)

A continuació has de crear vàries consultes com les de l'activitat anterior i les de l'activitat SQL - Relació

Album.sq

CREATE TABLE IF NOT EXISTS Album (
    AlbumId INTEGER PRIMARY KEY,
    Title TEXT,
    ArtistId INTEGER
);

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

App.kt

import app.cash.sqldelight.db.*
import app.cash.sqldelight.driver.jdbc.sqlite.JdbcSqliteDriver

import sql.data.*

fun main() {
    
    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:chinook.sqlite")
    val db = Database(driver)

    db.albumQueries.select(6, 0).executeAsList().forEach(::println)
}

TODO

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