tools: Create standalone program for 0.4 migration0.3.x-to-0.4.y

1 files changed, 541 insertions, 0 deletions

diff --git a/tools/migration_0.3_to_0.4.go b/tools/migration_0.3_to_0.4.go
new file mode 100644
index 0000000..9060294
--- /dev/null
+++ b/tools/migration_0.3_to_0.4.go
@@ -0,0 +1,541 @@
+package main
+
+import (
+	"encoding/json"
+	"fmt"
+	"log"
+	"os"
+	"path/filepath"
+	"database/sql"
+	"strconv"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+var db *sql.DB
+
+func main() {
+	if len(os.Args) != 2 {
+		log.Fatal("Please provide exactly two args")
+	}
+
+	storagePath := os.Args[1]
+	recipes := filepath.Join(storagePath, "recipes")
+
+	data := make([]Recipe, 0)
+
+	err := filepath.Walk(recipes, func(path string, info os.FileInfo, err error) error {
+		if err != nil {
+			return err
+		}
+		if !info.IsDir() {
+			data = append(data, parse(path))
+		}
+		return nil
+	})
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	log.Printf("Parsed %d recipes", len(data))
+
+	storage := NewStorage(storagePath)
+	if !storage.Exists() {
+		storage.Create()
+	}
+	log.Printf("Storage directory: %s\n", storage.Path)
+
+	ConnectDatabase(filepath.Join(storage.Path, "ceres.sqlite3"))
+	defer DisconnectDatabase()
+	MigrateDatabase("0.4.0")
+
+	for _, v := range data {
+		err = Transaction(v.Create)
+		if err != nil {
+			log.Fatal(err)
+		}
+	}
+}
+
+func parse(path string) Recipe {
+	dir := filepath.Dir(path)
+	id := filepath.Base(dir)
+	data, err := os.ReadFile(path)
+	if err != nil {
+		log.Fatal(err)
+	}
+	r := Recipe{}
+	json.Unmarshal(data, &r)
+
+	r.Id = id
+
+	info, err := os.Stat(path)
+	if err != nil {
+		log.Fatal(err)
+	}
+	r.Created = fmt.Sprintf("%d", info.ModTime().Unix())
+
+	r.LastChanged = r.Created
+
+//	if r.Id == "82" {
+//		b, err := json.MarshalIndent(r, "", "    ")
+//		if err != nil {
+//			log.Fatal(err)
+//		}
+//		os.Stdout.Write(b)
+//	}
+
+	return r
+}
+
+type Recipe struct {
+	Id          string
+	Title       string `json:"Title"`
+	Portions    int `json:"Portions"`
+	Url         string `json:"Url"`
+	Notes       string
+	Created     string
+	LastChanged string
+	Steps       []Step `json:"Steps"`
+}
+
+type Step struct {
+	Id          string
+	Index       string
+	Text        string `json:"text"`
+	Recipe      string
+	Ingredients []Ingredient `json:"ingredients"`
+}
+
+type Ingredient struct {
+	Id     string
+	Index  string
+	Amount float64 `json:"amount"`
+	Unit   string  `json:"unit"`
+	Type   string  `json:"type"`
+	Step   string
+}
+
+type Storage struct {
+	Path string
+}
+
+func NewStorage(path string) Storage {
+	return Storage{Path: path}
+}
+
+func (s Storage) Exists() bool {
+	_, err := os.Stat(s.Path)
+	return !os.IsNotExist(err)
+}
+
+func (s Storage) Create() {
+	err := os.Mkdir(s.Path, 0750)
+	if err != nil {
+		log.Fatal(err)
+	}
+}
+
+func ConnectDatabase(path string) {
+	var err error
+
+	db, err = sql.Open("sqlite3", path)
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	err = db.Ping()
+	if err != nil {
+		log.Fatal(err)
+	}
+}
+
+func MigrateDatabase(execVersion string) {
+	err := Transaction(func(tx *sql.Tx) error {
+		if isDatabaseEmpty(tx) {
+			log.Println("Starting with empty database")
+			err := setupMinimalDatabase(tx, execVersion)
+			if err != nil {
+				log.Fatalf("Failed to setup minimal database schema: %v", err)
+			}
+
+			log.Println("Executing initial migration")
+			query := `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)
+);`
+			_, err = tx.Exec(query)
+			if err != nil {
+				return err
+			}
+		}
+
+		dbVersion := getDatabaseVersion(tx)
+		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 Transaction(f func(*sql.Tx) error) error {
+	tx, err := db.Begin()
+	if err != nil {
+		log.Printf("Failed to start database transaction: %v", err)
+		return err
+	}
+	defer func() {
+		if tx.Rollback() == nil {
+			log.Println("Rolled back transaction")
+		}
+	}()
+
+	err = f(tx)
+	if err != nil {
+		log.Printf("Failed transaction: %v", err)
+		return err
+	}
+
+	return tx.Commit()
+}
+
+func isDatabaseEmpty(tx *sql.Tx) bool {
+	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")
+	}
+
+	var number int
+	err = rows.Scan(&number)
+	if err != nil {
+		log.Fatalf("Failed to scan number of database tables to integer")
+	}
+
+	return number == 0
+}
+
+func setupMinimalDatabase(tx *sql.Tx, execVersion string) error {
+	cmd := `
+CREATE TABLE metadata (
+	key   TEXT PRIMARY KEY,
+	value TEXT
+);
+INSERT INTO metadata
+	(key, value)
+VALUES
+	('version', ?);
+`
+	_, err := tx.Exec(cmd, execVersion)
+	return err
+}
+
+func getDatabaseVersion(tx *sql.Tx) string {
+	rows, err := tx.Query(`SELECT value FROM metadata WHERE key='version';`)
+	if err != nil {
+		log.Fatal(err)
+	}
+	defer rows.Close()
+
+	if !rows.Next() {
+		log.Fatalf("No rows on request of database version")
+	}
+
+	var version string
+	err = rows.Scan(&version)
+	if err != nil {
+		log.Fatalf("Failed to scan database version to string")
+	}
+
+	return version
+}
+
+func DisconnectDatabase() {
+	var err error = db.Close()
+	if err != nil {
+		log.Printf("Failed to close database: %v\n", err)
+	} else {
+		log.Println("Closed database")
+	}
+}
+
+func (r *Recipe) Create(tx *sql.Tx) error {
+	err := r.Validate()
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+INSERT INTO recipes
+    (id, title, portions, url, notes, created, last_changed)
+VALUES
+    (?, ?, ?, ?, ?, ?, ?)
+`
+
+	var portions string
+	if r.Portions != 0 {
+		portions = fmt.Sprintf("%v", r.Portions)
+	}
+
+	result, err := tx.Exec(cmd, r.Id, r.Title, portions, r.Url, r.Notes, r.Created,
+		r.LastChanged)
+	if err != nil {
+		return err
+	}
+
+	id, err := result.LastInsertId()
+	if err != nil {
+		return err
+	}
+
+	r.Id = fmt.Sprint(id)
+
+	for i := range r.Steps {
+		r.Steps[i].Recipe = r.Id
+		r.Steps[i].Index = fmt.Sprint(i)
+		err = r.Steps[i].Create(tx)
+		if err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func (r *Recipe) Validate() error {
+	var err error
+
+	if r.Id != "" {
+		err = isPositiveOrZeroInt(r.Id)
+		if err != nil {
+			return fmt.Errorf("Invalid recipe ID: %w", err)
+		}
+	}
+
+	if r.Title == "" {
+		return fmt.Errorf("Recipes must have a title")
+	}
+
+	if r.Portions < 0 {
+		return fmt.Errorf("Invalid recipe portions: %d", r.Portions)
+	}
+
+	err = isInt(r.Created)
+	if err != nil {
+		return fmt.Errorf("Invalid creation time stamp: %w", err)
+	}
+
+	err = isInt(r.LastChanged)
+	if err != nil {
+		return fmt.Errorf("Invalid last changed time stamp: %w", err)
+	}
+
+	return nil
+}
+
+func isInt(s string) error {
+	_, err := strconv.Atoi(s)
+	if err != nil {
+		return fmt.Errorf("'%s' cannot be casted to integer", s)
+	}
+
+	return nil
+}
+
+func isPositiveOrZeroInt(s string) error {
+	i, err := strconv.Atoi(s)
+	if err != nil {
+		return fmt.Errorf("'%s' cannot be casted to integer", s)
+	}
+
+	if i < 0 {
+		return fmt.Errorf("'%s' is negative", s)
+	}
+
+	return nil
+}
+
+func isPositiveOrZeroFloat(s string) error {
+	f, err := strconv.ParseFloat(s, 32)
+	if err != nil {
+		return fmt.Errorf("'%s' cannot be casted to floating point number", s)
+	}
+
+	if f < 0 {
+		return fmt.Errorf("'%s' is negative", s)
+	}
+
+	return nil
+}
+
+func (s *Step) Validate() error {
+	var err error
+
+	if s.Id != "" {
+		err = isPositiveOrZeroInt(s.Id)
+		if err != nil {
+			return fmt.Errorf("Invalid step ID: %w", err)
+		}
+	}
+
+	if s.Index != "" {
+		err = isPositiveOrZeroInt(s.Index)
+		if err != nil {
+			return fmt.Errorf("Invalid step index: %w", err)
+		}
+	}
+
+	if s.Text == "" {
+		return fmt.Errorf("Step text must not be empty")
+	}
+
+	err = isPositiveOrZeroInt(s.Recipe)
+	if err != nil {
+		return fmt.Errorf("Step does not reference a valid recipe ID: %w", err)
+	}
+
+	return nil
+}
+
+func (s *Step) Create(tx *sql.Tx) error {
+	if s.Id != "" {
+		return fmt.Errorf("Cannot create step if ID is given")
+	}
+
+	err := s.Validate()
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+INSERT INTO steps
+    ('index', text, recipe)
+VALUES
+    (?, ?, ?)
+`
+
+	result, err := tx.Exec(cmd, s.Index, s.Text, s.Recipe)
+	if err != nil {
+		return err
+	}
+
+	id, err := result.LastInsertId()
+	if err != nil {
+		return err
+	}
+
+	s.Id = fmt.Sprint(id)
+
+	for i := range s.Ingredients {
+		s.Ingredients[i].Step = s.Id
+		s.Ingredients[i].Index = fmt.Sprint(i)
+		err = s.Ingredients[i].Create(tx)
+		if err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
+func (i *Ingredient) Validate() error {
+	var err error
+
+	if i.Id != "" {
+		err = isPositiveOrZeroInt(i.Id)
+		if err != nil {
+			return fmt.Errorf("Invalid ingredient ID: %w", err)
+		}
+	}
+
+	err = isPositiveOrZeroInt(i.Index)
+	if err != nil {
+		return fmt.Errorf("Invalid ingredient index: %w", err)
+	}
+
+	if i.Amount < 0 {
+		return fmt.Errorf("Invalid ingredient amount: %v", i.Amount)
+	}
+
+	err = isPositiveOrZeroInt(i.Step)
+	if err != nil {
+		return fmt.Errorf("Ingredient does not reference a valid step ID: %w", err)
+	}
+
+	return nil
+}
+
+func (i *Ingredient) Create(tx *sql.Tx) error {
+	if i.Id != "" {
+		return fmt.Errorf("Cannot create ingredient if ID is given")
+	}
+
+	err := i.Validate()
+	if err != nil {
+		return err
+	}
+
+	cmd := `
+INSERT INTO ingredients
+    ('index', amount, unit, 'type', step)
+VALUES
+    (?, ?, ?, ?, ?)
+`
+
+	var amount string
+	if i.Amount != 0.0 {
+		amount = fmt.Sprintf("%v", i.Amount)
+	}
+
+	result, err := tx.Exec(cmd, i.Index, amount, i.Unit, i.Type, i.Step)
+	if err != nil {
+		return err
+	}
+
+	id, err := result.LastInsertId()
+	if err != nil {
+		return err
+	}
+
+	i.Id = fmt.Sprint(id)
+
+	return nil
+}