Add simple database framework

1 files changed, 125 insertions, 0 deletions

diff --git a/db.go b/db.go
new file mode 100644
index 0000000..d8648b0
--- /dev/null
+++ b/db.go
@@ -0,0 +1,125 @@
+package libmkwebpage
+
+import (
+	"database/sql"
+	"embed"
+	"errors"
+	"fmt"
+	"io"
+	"io/fs"
+	"os"
+	"path"
+	"slices"
+	"strconv"
+	"strings"
+)
+
+type migrations struct {
+	staticRoot  embed.FS
+	dynamicRoot fs.FS
+}
+
+func (w *Webpage) NewMigrations(static embed.FS, dynamic fs.FS) *Webpage {
+	w.migrations = migrations{staticRoot: static, dynamicRoot: dynamic}
+	return w
+}
+func (w *Webpage) getMigrationRoot() fs.FS {
+	if w.isDynamic() {
+		return w.migrations.dynamicRoot
+	}
+	return w.migrations.staticRoot
+}
+
+func (w *Webpage) ConnectDb() (*sql.DB, error) {
+	db, err := sql.Open("sqlite3", "./test.db")
+	if err != nil {
+		return nil, err
+	}
+
+	var dir fs.ReadDirFile
+	potenriallyDir, err := w.getMigrationRoot().Open("db")
+	if err != nil {
+		return nil, err
+	}
+	if foo, ok := potenriallyDir.(fs.ReadDirFile); !ok {
+		dir = foo
+	} else {
+		return nil, errors.New("db directory is not a directory")
+	}
+	files, err := dir.ReadDir(0)
+	if err != nil {
+		return nil, err
+	}
+
+	err = db.Ping()
+	if err != nil {
+		return nil, err
+	}
+
+	tx, err := db.Begin()
+	if err != nil {
+		return nil, err
+	}
+	defer tx.Rollback()
+
+	var migration_level int
+	rows := tx.QueryRow("SELECT level FROM __MIGRATIONLEVEL")
+	fmt.Fprintln(os.Stderr, *rows)
+	if rows.Scan(&migration_level) != nil {
+		fmt.Fprintln(os.Stderr, "Creating Migration-Metadata")
+		_, err := tx.Exec("CREATE TABLE __MIGRATIONLEVEL (level INTEGER PRIMARY KEY)")
+		if err != nil {
+			return nil, err
+		}
+		_, err = tx.Exec("INSERT INTO __MIGRATIONLEVEL(level) VALUES (0)")
+		if err != nil {
+			return nil, err
+		}
+		migration_level = 0
+	}
+	slices.SortFunc(files, func(a, b fs.DirEntry) int {
+		if a.Name() < b.Name() {
+			return -1
+		}
+		if a.Name() > b.Name() {
+			return 1
+		}
+		return 0
+	})
+	for _, file := range files {
+		if !file.Type().IsRegular() {
+			continue
+		}
+		id, err := strconv.Atoi(strings.Split(file.Name(), "-")[0])
+		if err != nil {
+			return nil, err
+		}
+		if migration_level < id {
+			fmt.Fprintln(os.Stderr, "Running Migration nr", id)
+			migration, err := w.getMigrationRoot().Open(path.Join("db", file.Name()))
+			if err != nil {
+				return nil, err
+			}
+			migrationContent, err := io.ReadAll(migration)
+			if err != nil {
+				return nil, err
+			}
+
+			_, err = tx.Exec(string(migrationContent))
+			if err != nil {
+				return nil, err
+			}
+			_, err = tx.Exec("UPDATE __MIGRATIONLEVEL SET level = ?", id)
+			if err != nil {
+				return nil, err
+			}
+		}
+
+	}
+	err = tx.Commit()
+	if err != nil {
+		return nil, err
+	}
+
+	return db, nil
+}