goext/sq/hasher.go

package sq

import (
	"context"
	"crypto/sha256"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"github.com/jmoiron/sqlx"
	"gogs.mikescher.com/BlackForestBytes/goext/langext"
	"os"
	"path/filepath"
	"strings"
)

// HashMattnSqliteSchema
// use if github.com/glebarez/go-sqlite
func HashMattnSqliteSchema(ctx context.Context, schemaStr string) (string, error) {
	dbdir := os.TempDir()
	dbfile1 := filepath.Join(dbdir, langext.MustHexUUID()+".sqlite3")

	err := os.MkdirAll(dbdir, os.ModePerm)
	if err != nil {
		return "", err
	}

	url := fmt.Sprintf("file:%s?_journal=%s&_timeout=%d&_fk=%s&_busy_timeout=%d", dbfile1, "DELETE", 1000, "true", 1000)

	xdb, err := sqlx.Open("sqlite3", url)
	if err != nil {
		return "", err
	}

	db := NewDB(xdb)

	_, err = db.Exec(ctx, schemaStr, PP{})
	if err != nil {
		return "", err
	}

	return HashSqliteDatabase(ctx, db)
}

// HashGoSqliteSchema
// use if mattn/go-sqlite3
func HashGoSqliteSchema(ctx context.Context, schemaStr string) (string, error) {
	dbdir := os.TempDir()
	dbfile1 := filepath.Join(dbdir, langext.MustHexUUID()+".sqlite3")

	err := os.MkdirAll(dbdir, os.ModePerm)
	if err != nil {
		return "", err
	}

	url := fmt.Sprintf("file:%s?_pragma=journal_mode(%s)&_pragma=timeout(%d)&_pragma=foreign_keys(%s)&_pragma=busy_timeout(%d)", dbfile1, "DELETE", 1000, "true", 1000)

	xdb, err := sqlx.Open("sqlite", url)
	if err != nil {
		return "", err
	}

	db := NewDB(xdb)

	_, err = db.Exec(ctx, schemaStr, PP{})
	if err != nil {
		return "", err
	}

	return HashSqliteDatabase(ctx, db)
}

func HashSqliteDatabase(ctx context.Context, db Queryable) (string, error) {
	ss, err := CreateSqliteDatabaseSchemaString(ctx, db)
	if err != nil {
		return "", err
	}

	cs := sha256.Sum256([]byte(ss))

	return hex.EncodeToString(cs[:]), nil
}

func CreateSqliteDatabaseSchemaString(ctx context.Context, db Queryable) (string, error) {

	type colInfo struct {
		Name       string  `db:"name"`
		Type       string  `db:"type"`
		NotNull    string  `db:"notnull"`
		Default    *string `db:"dflt_value"`
		PrimaryKey *string `db:"pk"`
	}

	type idxInfo struct {
		Name   string `json:"name"       db:"name"`
		Unique int    `json:"unique"     db:"unique"`
		Origin string `json:"origin"     db:"origin"`
		Patial int    `json:"partial"    db:"partial"`
	}

	type fkyInfo struct {
		TableDest string `json:"table_dest"  db:"table"`
		From      string `json:"from"        db:"from"`
		To        string `json:"to"          db:"to"`
		OnUpdate  string `json:"on_update"   db:"on_update"`
		OnDelete  string `json:"on_delete"   db:"on_delete"`
		Match     string `json:"match"       db:"match"`
	}

	type tabInfo struct {
		Name   string `json:"name"    db:"name"`
		Type   string `json:"type"    db:"type"`
		NumCol int    `json:"ncol"    db:"ncol"`
		Strict int    `json:"strict"  db:"strict"`

		ColumnInfo []colInfo `json:"-"`
		IndexInfo  []idxInfo `json:"-"`
		FKeyInfo   []fkyInfo `json:"-"`
	}

	rowsTableList, err := db.Query(ctx, "PRAGMA table_list;", PP{})
	if err != nil {
		return "", err
	}
	tableList, err := ScanAll[tabInfo](rowsTableList, SModeFast, Unsafe, true)
	if err != nil {
		return "", err
	}

	langext.SortBy(tableList, func(v tabInfo) string { return v.Name })

	result := make([]tabInfo, 0)

	for i, tab := range tableList {

		if strings.HasPrefix(tab.Name, "sqlite_") {
			continue
		}

		{

			rowsColumnList, err := db.Query(ctx, fmt.Sprintf("PRAGMA table_info(\"%s\");", tab.Name), PP{})
			if err != nil {
				return "", err
			}

			columnList, err := ScanAll[colInfo](rowsColumnList, SModeFast, Unsafe, true)
			if err != nil {
				return "", err
			}

			langext.SortBy(columnList, func(v colInfo) string { return v.Name })

			tableList[i].ColumnInfo = columnList
		}

		{
			rowsIdxList, err := db.Query(ctx, fmt.Sprintf("PRAGMA index_list(\"%s\");", tab.Name), PP{})
			if err != nil {
				return "", err
			}
			idxList, err := ScanAll[idxInfo](rowsIdxList, SModeFast, Unsafe, true)
			if err != nil {
				return "", err
			}

			langext.SortBy(idxList, func(v idxInfo) string { return v.Name })

			tableList[i].IndexInfo = idxList
		}

		{
			rowsIdxList, err := db.Query(ctx, fmt.Sprintf("PRAGMA foreign_key_list(\"%s\");", tab.Name), PP{})
			if err != nil {
				return "", err
			}
			fkyList, err := ScanAll[fkyInfo](rowsIdxList, SModeFast, Unsafe, true)
			if err != nil {
				return "", err
			}

			langext.SortBy(fkyList, func(v fkyInfo) string { return v.From })

			tableList[i].FKeyInfo = fkyList
		}

		result = append(result, tableList[i])
	}

	strBuilderResult := ""
	for _, vTab := range result {
		jbinTable, err := json.Marshal(vTab)
		if err != nil {
			return "", err
		}

		strBuilderResult += fmt.Sprintf("#TABLE: %s\n{\n", string(jbinTable))

		for _, vCol := range vTab.ColumnInfo {
			jbinColumn, err := json.Marshal(vCol)
			if err != nil {
				return "", err
			}

			strBuilderResult += fmt.Sprintf("    COLUMN: %s\n", string(jbinColumn))
		}

		for _, vIdx := range vTab.IndexInfo {
			jbinIndex, err := json.Marshal(vIdx)
			if err != nil {
				return "", err
			}

			strBuilderResult += fmt.Sprintf("    INDEX:  %s\n", string(jbinIndex))
		}

		for _, vFky := range vTab.FKeyInfo {
			jbinFKey, err := json.Marshal(vFky)
			if err != nil {
				return "", err
			}

			strBuilderResult += fmt.Sprintf("    FKEY:   %s\n", string(jbinFKey))
		}

		strBuilderResult += "}\n\n"
	}

	return strBuilderResult, nil
}