From ed19b82335345833c5b8f5446237d559a3657a35 Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Sun, 13 Oct 2024 14:08:26 +0200
Subject: Introduce xengineering.eu/ceres/model/migrations

This new package is only for database migrations. All data for Ceres
should be stored inside the SQLite3 database. Thus migrations can always
be executed with functions with the following signature.

    func(tx *sql.Tx) error

Those migration functions should be stored inside the new package.
Bigger SQL code can be stored in *.sql files for better syntax
highlighting. This code is embedded into the final Go executable by
using the embed package.
---
 model/database.go                 | 21 +++------------------
 model/migrations/migration001.go  | 15 +++++++++++++++
 model/migrations/migration001.sql | 29 +++++++++++++++++++++++++++++
 model/sql/migrate.sql             | 29 -----------------------------
 4 files changed, 47 insertions(+), 47 deletions(-)
 create mode 100644 model/migrations/migration001.go
 create mode 100644 model/migrations/migration001.sql
 delete mode 100644 model/sql/migrate.sql

diff --git a/model/database.go b/model/database.go
index f5fd67a..4740899 100644
--- a/model/database.go
+++ b/model/database.go
@@ -2,26 +2,15 @@ package model
 
 import (
 	"database/sql"
-	"embed"
 	"log"
 
 	_ "github.com/mattn/go-sqlite3"
+
+	"xengineering.eu/ceres/model/migrations"
 )
 
 var db *sql.DB
 
-//go:embed sql/*.sql
-var sqlCode embed.FS
-
-func GetSql(code string) (string, error) {
-	query, err := sqlCode.ReadFile("sql/" + code + ".sql")
-	if err != nil {
-		return "", err
-	}
-
-	return string(query), nil
-}
-
 func ConnectDatabase(path string) {
 	var err error
 
@@ -123,11 +112,7 @@ func MigrateDatabase(execVersion string) {
 			}
 
 			log.Println("Executing initial migration")
-			query, err := GetSql(`migrate`)
-			if err != nil {
-				log.Fatal(err)
-			}
-			_, err = tx.Exec(query)
+			err = migrations.Migration001(tx)
 			if err != nil {
 				return err
 			}
diff --git a/model/migrations/migration001.go b/model/migrations/migration001.go
new file mode 100644
index 0000000..11f062f
--- /dev/null
+++ b/model/migrations/migration001.go
@@ -0,0 +1,15 @@
+package migrations
+
+import (
+	"database/sql"
+	_ "embed"
+)
+
+//go:embed migration001.sql
+var migration001sql string
+
+func Migration001(tx *sql.Tx) error {
+	_, err := tx.Exec(migration001sql)
+
+	return err
+}
diff --git a/model/migrations/migration001.sql b/model/migrations/migration001.sql
new file mode 100644
index 0000000..46f9fc6
--- /dev/null
+++ b/model/migrations/migration001.sql
@@ -0,0 +1,29 @@
+PRAGMA foreign_keys = ON;
+
+CREATE TABLE recipes (
+	id           INTEGER PRIMARY KEY,
+	title        TEXT    NOT NULL,
+	portions     INTEGER NOT NULL,
+	url          TEXT    NOT NULL,
+	notes        TEXT    NOT NULL,
+	created      INTEGER NOT NULL,     -- Unix time stamp
+	last_changed INTEGER NOT NULL      -- Unix time stamp
+);
+
+CREATE TABLE steps (
+	id      INTEGER PRIMARY KEY,
+	'index' INTEGER NOT NULL,
+	text    TEXT    NOT NULL,
+	recipe  INTEGER NOT NULL,
+	FOREIGN KEY(recipe) REFERENCES recipes(id)
+);
+
+CREATE TABLE ingredients (
+	id      INTEGER PRIMARY KEY,
+	'index' INTEGER NOT NULL,
+	amount  REAL    NOT NULL,
+	unit    TEXT    NOT NULL,
+	'type'  TEXT    NOT NULL,
+	step    INTEGER NOT NULL,
+	FOREIGN KEY(step) REFERENCES steps(id)
+);
diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
deleted file mode 100644
index 46f9fc6..0000000
--- a/model/sql/migrate.sql
+++ /dev/null
@@ -1,29 +0,0 @@
-PRAGMA foreign_keys = ON;
-
-CREATE TABLE recipes (
-	id           INTEGER PRIMARY KEY,
-	title        TEXT    NOT NULL,
-	portions     INTEGER NOT NULL,
-	url          TEXT    NOT NULL,
-	notes        TEXT    NOT NULL,
-	created      INTEGER NOT NULL,     -- Unix time stamp
-	last_changed INTEGER NOT NULL      -- Unix time stamp
-);
-
-CREATE TABLE steps (
-	id      INTEGER PRIMARY KEY,
-	'index' INTEGER NOT NULL,
-	text    TEXT    NOT NULL,
-	recipe  INTEGER NOT NULL,
-	FOREIGN KEY(recipe) REFERENCES recipes(id)
-);
-
-CREATE TABLE ingredients (
-	id      INTEGER PRIMARY KEY,
-	'index' INTEGER NOT NULL,
-	amount  REAL    NOT NULL,
-	unit    TEXT    NOT NULL,
-	'type'  TEXT    NOT NULL,
-	step    INTEGER NOT NULL,
-	FOREIGN KEY(step) REFERENCES steps(id)
-);
-- 
cgit v1.2.3-70-g09d2