SQLite WAL and Witty

This week was spent drafting and planning the REST API. But before delving into that, there is something that could be useful when developing future applications with Witty. As mentioned in a previous post, we were encountering stability issues with the application. In particular, there was a possibility of encountering an error during simultaneous reading and writing to the database which would then crash the application as an exception is thrown.

Fatal Error: Database is locked

Since database access is an integral part of the application, it would require a lot of rewriting to catch these exceptions hence supporting the decision to rewrite the entire application. Although we still intend to rewrite a new version, there is a partial solution that we intend to use for now to patch the release version of the application while the new version is still in development. To solve this problem, one can enable Write-Ahead Logging (WAL) mode in SQLite3. This replaces the default rollback journaling method which is prone to locks (especially on the slower Raspberry Pi) when reading and writing simultaneously. While this allows the database to be read and written simultaneously, writing simultaneously still does not work so it is not a complete solution unless we catch all exceptions when writing to the database.

Since deployment day, Dr. Shawn gave a lot of ideas to further improve the functionality of the application. Feedback from the previous deployment was also well so everything is all in the clear the make the next version better than before. Working on the REST API was a bit slow though since I kept changing my ideas on deciding the best way to set up the entry points and protocols to make it easy for the front-end to interface with the back-end. After an initial draft, Dr. Shawn advised to set up a 1 to 1 mapping of the database and entry points which is more semantically correct so I made some amendments to the initial draft. The drafted API is now done and I hope that the API is more or less finalised so that I can focus on actually building the code for the API.

Witty Tip of the Day: To enable WAL in SQLite3, run the SQL command PRAGMA journal_mode = “WAL”. An example code snippet for enabling it in Wt is given below:

Wt::Dbo::backend::Sqlite3 sqlite3(“sample-database.db”);
Wt::Dbo::Session session;
session.setConnection(sqlite3);
…
session.mapClass<Users>(“users”);
…
session.createTables();
sqlite3.executeSql(“PRAGMA journal_mode = \”WAL\””);