Editing the Database

#Editing the Database

Brewtarget uses SQLite tables to manage its data. In order to ensure that when a user upgrades Brewtarget, he or she can still read the old database files, we must have a way to migrate those old databases to the new one. This article describes the process you must take as a developer to ensure a seamless user experience when you add, remove, and otherwise edit the database schema.

Change the Schema

The database schema is maintained with the DatabaseSchemaHelper class. Change the schema by editing DatabaseSchemaHelper::create(). In this document, I will show examples for adding an asdf field to the equipment table.

ex.

First, add a string for the new column's name to DatabaseSchemaHelper.h

...
static QString colEquipAbsorption;
static QString colEquipAsdf;
...

And initialize this in DatabaseSchemaHelper.cpp

...
QString DatabaseSchemaHelper::colEquipAbsorption("absorption");
QString DatabaseSchemaHelper::colEquipAsdf("asdf");
...

Then, edit the create() method

...
colEquipAbsorption + SEP + TYPEREAL + SEP + DEFAULT + " 1.085" + "," +
colEquipAsdf + SEP + TYPEREAL + SEP + DEFAULT + " 0.0" + "," +
  // Metadata
  deleted + "," +
...

Create a Migration

The purpose of a migration is to update an existing database to the current schema without destroying it. These are the commands that will transfer the users' old data. It should contain the commands necessary to make the exact same changes that you made to the schema without harming the rest of the database.

To do this, increase DatabaseSchemaHelper::dbVersion by 1. Assuming it was 4 before we edited the create() method, we will bump it to 5. Then, make a new case in DatabaseSchemaHelper::migrateNext() and execute queries to update an existing version 4 database to the new version 5 database.

ex. (DatabaseSchemaHelper::migrateNext())

...
   break;
case 4: // Upgrade 4->5

   ret &= q.exec(
      ALTERTABLE + SEP + tableEquipment + SEP +
      ADDCOLUMN + SEP + colEquipAsdf + SEP + TYPEREAL + DEFAULT + " 0.0"
     );

   break;
default:
   Brewtarget::logE(QString("Unknown version %1").arg(oldVersion));
   return false;

Test It

Make sure create() works properly by executing brewtarget with the --create-blank option:

$ brewtarget --create-blank blank.sqlite

Make sure the migration works by launching brewtarget normally. It should automatically migrate the database. If there are no errors, it should be good to go.