gex/db/db.go

package db

import (
	"database/sql"
	"errors"
	"os"
	"reflect"

	"git.clearsky.net.au/cody/gex.git/utils"
	_ "github.com/mattn/go-sqlite3"
)

var Conn *sql.DB

func Connect(fp string) error {
	_, err := os.Stat(fp)
	if err != nil {
		utils.Err(err)
		os.Exit(1)
		return err
	}

	if Conn, err = sql.Open("sqlite3", fp); err != nil {
		utils.Err(err)
		os.Exit(1)
		return err
	}

	Conn.Exec(`
		PRAGMA journal_mode=ON
		PRAGMA journal_mode=WAL;
		PRAGMA synchronous=ON
		PRAGMA auto_vacuum=ON
		PRAGMA foreign_keys=ON
	`)

	return nil
}

func Get(model interface{}, query string, params ...any) error {
	if reflect.TypeOf(model).Kind() != reflect.Pointer {
		err := errors.New("model must be a pointer")
		utils.Err(err)
		return err
	}

	if reflect.TypeOf(model).Elem().Kind() != reflect.Struct {
		err := errors.New("model must be a struct")
		utils.Err(err)
		return err
	}

	p := []any{}
	p = append(p, params...)

	err := Conn.QueryRow(query, p...).Scan(StructPointers(model)...)
	if err != nil {
		err = errors.New("SQL " + err.Error())
		utils.Err(err)
		return err
	}
	return nil
}

func All(model interface{}, query string, params ...any) error {
	p := []any{}
	p = append(p, params...)
	if reflect.TypeOf(model).Kind() != reflect.Pointer {
		err := errors.New("db.All(): model must be a pointer")
		utils.Err(err)
		return err
	}

	slicePtr := reflect.ValueOf(model)

	sliceVal := slicePtr.Elem()

	if sliceVal.Kind() != reflect.Slice {
		err := errors.New("db.All(): model must be a slice of model")
		utils.Err(err)
		return err
	}

	modelType := sliceVal.Type().Elem()

	rows, err := Conn.Query(query, p...)
	if err != nil {
		err := errors.New("db.All(): SQL " + err.Error())
		utils.Err(err)
		return err
	}

	var set []reflect.Value
	for rows.Next() {
		tmpModel := reflect.New(modelType)

		err = rows.Scan(StructPointers(tmpModel.Interface())...)

		set = append(set, tmpModel.Elem())
	}
	if err != nil {
		utils.Err(err)
		return err
	}
	rows.Close()

	sliceVal.Set(reflect.Append(sliceVal, set...))
	return nil
}

func Close() {
	Conn.Close()
}

func StructPointers(s interface{}) []interface{} {
	model := reflect.ValueOf(s)
	modelElem := model.Elem()

	numFields := modelElem.NumField()

	var Ptrs []interface{}

	for i := 0; i < numFields; i++ {
		field := modelElem.Field(i)

		tag := modelElem.Type().Field(i).Tag

		if tag != "" {
			continue
		}

		// Merge

		if field.Kind() == reflect.Struct {
			Ptrs = append(Ptrs, StructPointers(field.Addr().Interface())...)
			continue
		}

		Ptrs = append(Ptrs, field.Addr().Interface())

	}

	return Ptrs
}