model: Add initial SQL migration

This provides the basic table structure to the database.

2 files changed, 38 insertions, 0 deletions

diff --git a/model/database.go b/model/database.go
index ed9a985..8ce1897 100644
--- a/model/database.go
+++ b/model/database.go
@@ -2,6 +2,7 @@ package model
 
 import (
 	"database/sql"
+	"embed"
 	"log"
 	"path/filepath"
 
@@ -10,6 +11,18 @@ import (
 
 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 InitDatabase() {
 	var err error
 
@@ -24,6 +37,17 @@ func InitDatabase() {
 	if err != nil {
 		log.Fatal(err)
 	}
+
+	for _, v := range []string{`migrate`} {
+		query, err := GetSql(v)
+		if err != nil {
+			log.Fatal(err)
+		}
+		_, err = db.Exec(query)
+		if err != nil {
+			log.Fatal(err)
+		}
+	}
 }
 
 func CloseDatabase() {
diff --git a/model/sql/migrate.sql b/model/sql/migrate.sql
new file mode 100644
index 0000000..6ca4d0f
--- /dev/null
+++ b/model/sql/migrate.sql
@@ -0,0 +1,14 @@
+CREATE TABLE 'recipes' (
+	'id' integer PRIMARY KEY AUTOINCREMENT,
+	'title' text DEFAULT '',
+	'portions' text DEFAULT '',  -- FIXME has to be integer
+	'url' text DEFAULT '',
+	'notes' text DEFAULT ''
+);
+
+CREATE TABLE 'recipe_steps' (
+	'id' integer PRIMARY KEY,
+	'recipe_id' integer DEFAULT NULL,
+	'rank' integer DEFAULT NULL,
+	'text' text DEFAULT ''
+);