goext/sq/hasher.go

230 lines
5.3 KiB
Go
Raw Permalink Normal View History

2023-05-28 19:41:24 +02:00
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"
)
2023-12-29 18:06:45 +01:00
// HashMattnSqliteSchema
// use if github.com/glebarez/go-sqlite
func HashMattnSqliteSchema(ctx context.Context, schemaStr string) (string, error) {
2023-05-28 19:41:24 +02:00
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
}
2023-05-28 19:53:30 +02:00
return HashSqliteDatabase(ctx, db)
}
2023-12-29 18:06:45 +01:00
// 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)
}
2023-07-27 17:00:53 +02:00
func HashSqliteDatabase(ctx context.Context, db Queryable) (string, error) {
2023-05-28 19:41:24 +02:00
ss, err := CreateSqliteDatabaseSchemaString(ctx, db)
if err != nil {
return "", err
}
cs := sha256.Sum256([]byte(ss))
return hex.EncodeToString(cs[:]), nil
}
2023-07-27 17:00:53 +02:00
func CreateSqliteDatabaseSchemaString(ctx context.Context, db Queryable) (string, error) {
2023-05-28 19:41:24 +02:00
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
}
2023-12-29 19:25:36 +01:00
tableList, err := ScanAll[tabInfo](ctx, db, rowsTableList, SModeFast, Unsafe, true)
2023-05-28 19:41:24 +02:00
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
}
2023-12-29 19:25:36 +01:00
columnList, err := ScanAll[colInfo](ctx, db, rowsColumnList, SModeFast, Unsafe, true)
2023-05-28 19:41:24 +02:00
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
}
2023-12-29 19:25:36 +01:00
idxList, err := ScanAll[idxInfo](ctx, db, rowsIdxList, SModeFast, Unsafe, true)
2023-05-28 19:41:24 +02:00
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
}
2023-12-29 19:25:36 +01:00
fkyList, err := ScanAll[fkyInfo](ctx, db, rowsIdxList, SModeFast, Unsafe, true)
2023-05-28 19:41:24 +02:00
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
}