From 1904f50084660ce587a67d49939fd0734fd8a582 Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Sun, 13 Oct 2024 20:34:29 +0200
Subject: model: Handle schema versions internally by integers

This references the database schemas known from the past with integer
values internally. 0 is used for an empty database and 1 for what was
used in ceres version 0.4.0.

The old approach to write the `git describe` output into the database
worked well for released versions but was problematic during
development. A numerical and separate database schema versioning is
easier to handle.
---
 model/database.go | 127 +++++++++++++++++++++++++-----------------------------
 1 file changed, 58 insertions(+), 69 deletions(-)

diff --git a/model/database.go b/model/database.go
index d3fc115..7490f7b 100644
--- a/model/database.go
+++ b/model/database.go
@@ -2,6 +2,7 @@ package model
 
 import (
 	"database/sql"
+	"fmt"
 	"log"
 
 	_ "github.com/mattn/go-sqlite3"
@@ -46,35 +47,30 @@ func (db *DB) Transaction(f func(*sql.Tx) error) error {
 	return tx.Commit()
 }
 
-func (db *DB) IsEmpty() bool {
+func (db *DB) IsEmpty(tx *sql.Tx) (bool, error) {
 	var number int
 
-	_ = db.Transaction(func(tx *sql.Tx) error {
-		cmd := `SELECT COUNT(*) FROM sqlite_master WHERE type='table'`
-		rows, err := tx.Query(cmd)
-		if err != nil {
-			log.Fatal(err)
-		}
-		defer rows.Close()
-
-		if !rows.Next() {
-			log.Fatalf("No rows on request of database table number")
-		}
+	cmd := `SELECT COUNT(*) FROM sqlite_master WHERE type='table'`
+	rows, err := tx.Query(cmd)
+	if err != nil {
+		return false, fmt.Errorf("Select call failed: %w", err)
+	}
+	defer rows.Close()
 
-		err = rows.Scan(&number)
-		if err != nil {
-			log.Fatalf("Failed to scan number of database tables to integer")
-		}
+	if !rows.Next() {
+		return false, fmt.Errorf("Result set is empty")
+	}
 
-		return nil
-	})
+	err = rows.Scan(&number)
+	if err != nil {
+		return false, fmt.Errorf("Failed to scan numerical value: %w", err)
+	}
 
-	return number == 0
+	return number == 0, nil
 }
 
-func (db *DB) setupMinimal(execVersion string) error {
-	return db.Transaction(func(tx *sql.Tx) error {
-		cmd := `
+func (db *DB) setupMinimal(tx *sql.Tx, execVersion string) error {
+	cmd := `
 CREATE TABLE metadata (
 	key   TEXT PRIMARY KEY,
 	value TEXT
@@ -84,66 +80,59 @@ INSERT INTO metadata
 VALUES
 	('version', ?);
 `
-		_, err := tx.Exec(cmd, execVersion)
-		return err
-	})
+	_, err := tx.Exec(cmd, execVersion)
+	return err
 }
 
-func (db *DB) Version() string {
-	var version string
-
-	_ = db.Transaction(func(tx *sql.Tx) error {
-		rows, err := tx.Query(`SELECT value FROM metadata WHERE key='version';`)
-		if err != nil {
-			log.Fatal(err)
-		}
-		defer rows.Close()
+func (db *DB) SchemaVersion(tx *sql.Tx) (int, error) {
+	empty, err := db.IsEmpty(tx)
+	if err != nil {
+		return 0, fmt.Errorf("Failed to check if DB is empty: %w", err)
+	}
 
-		if !rows.Next() {
-			log.Fatalf("No rows on request of database version")
-		}
+	if empty {
+		return 0, nil
+	}
 
-		err = rows.Scan(&version)
-		if err != nil {
-			log.Fatalf("Failed to scan database version to string")
-		}
+	rows, err := tx.Query(`SELECT value FROM metadata WHERE key='version';`)
+	if err != nil {
+		return 0, fmt.Errorf("Select call failed: %w", err)
+	}
+	defer rows.Close()
 
-		return nil
-	})
+	if rows.Next() {
+		return 1, nil // version field was only present in one schema version
+	}
 
-	return version
+	return 0, fmt.Errorf("Unknown schema version")
 }
 
-func (db *DB) Migrate(execVersion string) {
-	err := db.Transaction(func(tx *sql.Tx) error {
-		if db.IsEmpty() {
-			log.Println("Starting with empty database")
-			err := db.setupMinimal(execVersion)
+func (db *DB) Migrate(execVersion string) error {
+	return db.Transaction(func(tx *sql.Tx) error {
+		for {
+			schema, err := db.SchemaVersion(tx)
 			if err != nil {
-				log.Fatalf("Failed to setup minimal database schema: %v", err)
+				return fmt.Errorf("Failed to get DB schema version: %w", err)
 			}
-
-			log.Println("Executing initial migration")
-			err = migrations.Migration001(tx)
-			if err != nil {
-				return err
+			switch schema {
+			case 0:
+				log.Println("Starting with empty database")
+				err := db.setupMinimal(tx, execVersion)
+				if err != nil {
+					return fmt.Errorf("Failed to setup minimal database schema: %w", err)
+				}
+				log.Println("Executing initial migration")
+				err = migrations.Migration001(tx)
+				if err != nil {
+					return err
+				}
+			case 1:
+				return nil
+			default:
+				return fmt.Errorf("Cannot migrate database to a matching schema version")
 			}
 		}
-
-		dbVersion := db.Version()
-		if dbVersion != execVersion {
-			log.Fatalf(
-				"Database version '%s' does not match executable version '%s'",
-				dbVersion,
-				execVersion,
-			)
-		}
-
-		return nil
 	})
-	if err != nil {
-		log.Fatalf("Fatal: Database migration failed")
-	}
 }
 
 func (db *DB) CreateExamples() {
-- 
cgit v1.2.3-70-g09d2