Enabling SQLite foreign key constraints

For quite a while, Wakapi didn't define foreign key constraints on database tables and then introduced them retroactively. While this works well for MySQL and Postgres, it can't easily be done for SQLite databases. Foreign key constraints are not strictly necessary for Wakapi to function, however, a couple of issues (#177, #277, #210, #99) arose from this and a few hacks were introduced. To retroactively enable your already existing SQLite database for foreign keys constraint checking and make proper use of cascading deletes, the easiest way is to let Wakapi create a new database and then copy over your old data.

Step 1: Make sure you're on Wakapi version 1.30.2 or higher

Step 2: Install SQLite3

# Debian, Ubuntu, ...:
sudo apt install sqlite3

# Fedora, RHEL
sudo dnf install sqlite

Step 3: Dump your old data

sqlite3 wakapi_db.db .dump | \
  sed -e 's/^CREATE.*//' | \
  sed -e 's/^INSERT INTO key_string_values.*//' | \
  sed '/^[[:space:]]*$/d' | \
  sed -e 's/^PRAGMA foreign_keys=OFF;/PRAGMA foreign_keys=ON;/' \
  > /tmp/wakapi_dump.sql

Step 4: Delete (rename) old database

mv wakapi_db.db wakapi_db.backup.db

Step 5: Restart Wakapi, let it automatically create a new database and stop it again

Step 6: Import the old data

sqlite3 wakapi_db.db < /tmp/wakapi_dump.sql