1. Failures/Rollbacks

blabla

Voorbereidende CREATE statements (Dit is SQLite syntax!) Zie SQLite manual:

DROP TABLE IF EXISTS student;
CREATE TABLE student(
    studnr INT NOT NULL PRIMARY KEY,
    naam VARCHAR(200) NOT NULL,
    voornaam VARCHAR(200),
    goedbezig BOOL
);
DROP TABLE IF EXISTS log;
CREATE TABLE log(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    date DATETIME DEFAULT CURRENT_TIMESTAMP,
    foreign_id INT NOT NULL,
    msg TEXT
);
INSERT INTO student(studnr, naam, voornaam, goedbezig) VALUES (123, 'Trekhaak', 'Jaak', 0);
INSERT INTO student(studnr, naam, voornaam, goedbezig) VALUES (456, 'Peeters', 'Jos', 0);
INSERT INTO student(studnr, naam, voornaam, goedbezig) VALUES (890, 'Dongmans', 'Ding', 1);

1. System failure simulatie

1.1 In SQLite met DB Browser

Gegeven een aantal SQL statements, waarvan niet alle statements kloppen, maar die wel allemaal bij elkaar horen als één atomaire transactie. Dat betekent dat als er één van die statements misloopt, de rest teruggedraait zou moeten worden. Het spreekt voor zich dat zonder speciale handelingen, zoals het beheren van transacties, dit niet gebeurt. Een eenvoudig voorbeeld demonstreert dit.

UPDATE student SET voornaam = 'Jaqueline' WHERE studnr = 123;
INSERT INTO oeitiskapot;
INSERT INTO log(foreign_id, msg) VALUES (123, 'Voornaam vergissing');
INSERT INTO student(studnr, naam, voornaam, goedbezig) VALUES (445, 'Klakmans', 'Jef', 1);
INSERT INTO log(foreign_id, msg) VALUES (445, 'Nieuwe student registratie');

Plak dit in de “Execute SQL” tab van de SQLite DB Browser. Het resultaat is een foutboodschap:

near ";": syntax error: 
INSERT INTO oeitiskapot;

Maar: het eerste UPDATE statement, voor de foute regel, is wel uitgevoerd:

Oefeningen

  1. Probeer bovenstaande voorbeeld zelf uit in de SQLite DB Browser. Als je jezelf ervan verzekerd hebt dat inderdaad het eerste UPDATE statement wordt uitgevoerd, terwijl wij dat in één ACID blok willen, ga dan over naar de volgende oefening.
  2. In SQLite is het starten van een transactie erg eenvoudig: zie SQLite transaction tutorials van tutorialspoint.com. BEGIN; en COMMIT; zijn voldoende. Probeer dit uit in bovenstaande voorbeeld om er voor te zorgen dat de voornaam van Jaak niet wordt gewijzigd. Om met een “clean slate” te herbeginnen kan je gewoon de voorbereidende SQL code copy/pasten en opnieuw uitvoeren. Merk op dat dit nog steeds het ongewenst effect heeft dat de student zijn/haar naam wordt gewijzigd. We moeten expliciet zelf ROLLBACK; aanroepen.
  3. Probeer een nieuwe student toe te voegen: eentje met studentennummer, en eentje zonder. Dat tweede kan in principe niet door de NOT NULL constraint. Wrap beide statements in een transactie.

Let Op: Het zou kunnen dat SQLite de volgende fout geeft: cannot start a transaction within a transaction: BEGIN;. Queries die geplakt worden in het “execute SQL” scherm worden meestal (onzichtbaar, achter de schermen) gewrapped in transacties. Stop de huidige transactie door COMMIT; uit te voeren met de knop “execute single SQL line”.

1.2 In SQLite met Java/JDBC

Bovenstaande failure fenomeen stellen we ook vast als we niet rechtstreeks in de DB explorer onze queries uitvoeren, maar via Java, met behulp van de sqlite-jdbc drivers. JDBC, of “Java DataBase Connection”, is een tussenlaag die het mogelijk maakt om SQL uit te voeren op eender welke SQL server. sqlite-jdbc zorgt voor de SQLite-specifieke vertaling. De interface die wij gebruiken om te programmeren zit in de JDK zelf onder de packages java.sql.

Enkele belangrijke statements:

  1. Een connectie naar een database vastleggen: var connection = DriverManager.getConnection("jdbc:sqlite:mydb.db");
  2. Een SELECT query uitvoeren: var s = connection.createStatement(); var result = s.executeQuery("..."); var cell = result.getString("column");
  3. Een INSERT/UPDATE/… query uitvoeren (die de structuur of inhoud van de database wijzigt): var s = connection.createStatement(); s.executeUpdate("...");

Het volgende voorbeeld opent een verbinding naar een DB, maakt een tabel aan, voegt een record toe, en telt het aantal records:

private lateinit connection: Connection
fun createDb() {
    connection = DriverManager.getConnection("jdbc:sqlite:mydb.db").apply {
        setAutoCommit(false)
    }
    // note that this requires "stdlib-jdk7" as kotlin runtime dependency
    connection.createStatement().use {
      it.executeUpdate("CREATE TABLE mijntabel(nr INT); INSERT INTO mijntabel(nr) VALUES(1);")  
    }
}
fun verifyDbContents() {
    connection.createStatement().use {
        val result = it.executeQuery("SELECT COUNT(*) FROM mijntabel;")
        assert result.getInt(0) == 1
    }
}
private Connection connection;
public void createDb() throws SQLException {
    connection = DriverManager.getConnection("jdbc:sqlite:mydb.db");
    connection.setAutoCommit(false);
    var s = connection.createStatement();
    s.executeUpdate("CREATE TABLE mijntabel(nr INT); INSERT INTO mijntabel(nr) VALUES(1);")
    s.close();
}
public void verifyDbContents() throws SQLException {
    var s = connection.createStatement();
    var result = s.executeQuery("SELECT COUNT(*) FROM mijntabel;");
    var count = result.getInt(0);
    s.close();

    assert count == 1;
}

Gradle dependency: laatste versie van sqlite-jdbc in mvnrepository.com.

Problemen met je JDK versie en Gradle versies? Raadpleeg de Gradle Compatibiility Matrix. Gradle 6.7 of hoger ondersteunt JDK15. Gradle 7.3 of hoger ondersteunt JDK17. Let op met syntax wijzigingen bij Gradle 7+!
Je Gradle versie verhogen kan door de URL in gradle/gradlew.properties te wijzigen.

Merk op dat SQLException een checked exception is die je constant moet meespelen in de method signature of expliciet moet opvangen. Het probleem van een try { } catch { } finally { } block is dat in de finally je ook geen close() kan uitvoeren zonder opnieuw een try block te openen… Inception!

Vergelijk de Kotlin implementatie met de standaard Java versie. Als het aan komt op efficiënt gebruik van oudere Java APIs zoals de SQL methodes, is Kotlin véél eenvoudiger, zowel in gebruik als in leesbaarheid. Hier maken we handig gebruik van de use extension om geen close() te moeten oproepen, en with {} om de connectionManager in te stellen zonder telkens connection. te moeten herhalen. Nog een voordeel: er is geen verschil tussen checked en unchecked exceptions.
Het loont dus om ook voor dit vak te opteren voor Kotlin—maar het is niet verplicht.

Het connection.close() statement moet er voor zorgen dat voor elke request de connection netjes wordt afgesloten. Een database heeft meestal een connection pool van x aantel beschikbare connections, bijvoorbeeld 5. Als een connection per request niet wordt gesloten, heeft de voglende bezoeker van onze website geen enkele kans om zijn zoekquery te lanceren, omdat de database dan zegt dat alle connecties zijn opgebruikt!

De setAutoCommit(false) regel is nodig omdat in JDBC standaard elke SQL statement aanschouwd wordt als een onafhankelijke transactie, die automatisch wordt gecommit:

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)

Zie JDBC Basics in Oracle docs: https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

Begeleidende video:

Oefeningen

  1. Maak een nieuw Gradle project aan en connecteer naar je SQLite database. Merk op dat, bij connectionstring "jdbc:sqlite:sample.db", automatisch een lege .db file wordt aangemaakt indien de database niet bestaat. Probeer met behulp van executeUpdate() en executeQuery() bovenstaande system failure te veroorzaken. Je kan de “foute SQL” (met “oeitiskapot”) gewoon in een string in java copy/pasten. executeUpdate() kan verschillende statements tegelijkertijd verwerken. Verifieer dat de naam foutief toch wordt gewijzigd met een SELECT() nadat je de fout hebt opgevangen in een try { } block.
  2. Het probleem is op te lossen met één welgeplaatste regel: connection.rollback(). De vraag is echter: waar plaatsen we die? En ja, rollback() throwt ook de checked SQLException… Verifieer of je oplossing werkt door de naam na de rollback terug op te halen en te vergelijken met de juiste waarde: “Jaak”.

De DROP TABLE IF EXISTS statements kan je in je project in een aparte SQL file bewaren en als een String inlezen, om in één keer te laten uitvoeren na het openen van de connectie:

fun initTables() {
    val sql = SomeClass::class.java.getResource("dbcreate.sql").readText()
    println(sql)

    connection.createStatement().use {
      it.executeUpdate(sql)
    }
}
private void initTables() throws Exception {
    var sql = new String(Files.readAllBytes(Paths.get(getClass().getResource("dbcreate.sql").toURI())));
    System.out.println(sql);

    var s = connection.createStatement();
    s.executeUpdate(sql);
    s.close();
}

De verwachte fout (met de ongeldige SQL regel) die SQLite doorgeeft aan Java genereert de volgende stacktrace:

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ";": syntax error)
    at org.sqlite.core.DB.newSQLException(DB.java:1010)
    at org.sqlite.core.DB.newSQLException(DB.java:1022)
    at org.sqlite.core.DB.throwex(DB.java:987)
    at org.sqlite.core.NativeDB._exec_utf8(Native Method)
    at org.sqlite.core.NativeDB._exec(NativeDB.java:94)
    at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:109)
    at SQLiteTransactionTest.doStuff(SQLiteTransactionTest.java:54)
    at SQLiteMain.main(SQLiteMain.java:7)

Denkvragen

  • De SQLite website beschrijft in detail hoe ze omgaan met “atomic commits” om aan de ACID regels te voldoen. Lees dit na op https://sqlite.org/atomiccommit.html Op welke manier gebruiken zij een rollback journal? Hoe is dat gelinkt aan de logfile van 14.2.3 op p.435?
  • JDBC is vrij rudimentair, en het is vrij omslachtig om simpele statements te committen vanwege boilerplate code. Hoe zou je dit probleem verminderen door middel van enkele refactorings? Anders gezegd: welke patronen herken je (zie SES 2de bach.) en hoe kan je gebruik maken van die patronen om herhalende boilerplate code te verminderen?