Change the Table: Migrations in Java

The shop wants to give products a discount, and the table products has no column for it. A change to the structure of the tables is a migration. ALTER TABLE changes a table that already holds rows, and a DEFAULT fills the new column in for every one of them.

A migration must happen exactly once: the app starts again and again, and a database that already has the column must not get it twice. Some statements are safe to repeat, others are not:

db.execute("CREATE TABLE IF NOT EXISTS notes (text TEXT NOT NULL)");  // the 2nd time: does nothing
db.execute("ALTER TABLE customers ADD COLUMN phone TEXT");  // the 2nd time: error, duplicate column name

So the database keeps a small table of its own, schema_version, with one row per change made. Spring makes your controller once, when the app starts, and its constructor calls migrate(). It looks for the row of each change: if the row is missing, it makes the change and records it. After that, every product answers with its discount:

GET /products/1  →  200  {"id": 1, "name": "Mug", "price": 12, "category": "kitchen", "stock": 10, "discount": 0}
POST /products  {"name": "Vase", "price": 18, "category": "home"}  →  201  {"id": 9}
GET /products/9  →  200  {"id": 9, "name": "Vase", "price": 18, "category": "home", "stock": 0, "discount": 0}
GET /products?sort=price&order=desc&per_page=2  →  200  {"products": [{"id": 8, "name": "Gift card", …, "discount": 0}, {"id": 3, "name": "Lamp", …, "discount": 0}], "total": 9, "next_page": 2}

Your task: write a method void migrate() and call it from the constructor. It creates schema_version if needed and, only if version 1 isn't recorded yet, adds the column discount (a whole number, 0 by default) to products and records version 1.

Assignment
Follow the Coding Tutorial to write migrate(), call it from the constructor, and add the discount column exactly once.

Hint
queryForList answers an empty list when no row matches: done.isEmpty() is your sign that change 1 wasn't made yet. Record the version inside the same if as the ALTER TABLE; db.update saves it at once.