go.charczuk.com@v0.0.0-20240327042549-bc490516bd1a/projects/nodes/pkg/dbmodel/manager.go

/*

Copyright (c) 2023 - Present. Will Charczuk. All rights reserved.
Use of this source code is governed by a MIT license that can be found in the LICENSE file at the root of the repository.

*/

package dbmodel

import (
	"context"
	"fmt"

	"github.com/wcharczuk/go-incr"
	"go.charczuk.com/projects/nodes/pkg/types"
	"go.charczuk.com/sdk/collections"
	"go.charczuk.com/sdk/db"
	"go.charczuk.com/sdk/db/dbutil"
	"go.charczuk.com/sdk/iter"
	"go.charczuk.com/sdk/uuid"
)

type Manager struct {
	dbutil.BaseManager
}

var graphMeta = db.TypeMetaFor(Graph{})
var graphsForUserQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE user_id = $1`, db.ColumnNamesCSV(graphMeta.Columns()), Graph{}.TableName())
var nodesMeta = db.TypeMetaFor(Node{})
var nodesQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1`, db.ColumnNamesCSV(nodesMeta.Columns()), Node{}.TableName())
var nodeValuesMeta = db.TypeMetaFor(NodeValue{})
var nodeValuesQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1`, db.ColumnNamesCSV(nodeValuesMeta.Columns()), NodeValue{}.TableName())
var nodeValuesManyQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1 AND node_id = ANY($2)`, db.ColumnNamesCSV(nodeValuesMeta.Columns()), NodeValue{}.TableName())
var edgesMeta = db.TypeMetaFor(Edge{})
var edgesQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1`, db.ColumnNamesCSV(edgesMeta.Columns()), Edge{}.TableName())
var graphRecomputeHeapMeta = db.TypeMetaFor(GraphRecomputeHeap{})
var graphRecomputeHeapQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1`, db.ColumnNamesCSV(graphRecomputeHeapMeta.Columns()), GraphRecomputeHeap{}.TableName())
var setViewportExec = fmt.Sprintf(`UPDATE %s SET viewport_x = $1, viewport_y = $2, viewport_zoom = $3, updated_utc = current_timestamp WHERE id = $4`, Graph{}.TableName())
var graphLogsMeta = db.TypeMetaFor(GraphLogs{})
var graphLogsLatestQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE graph_id = $1 ORDER BY stabilization_num DESC LIMIT 1`, db.ColumnNamesCSV(graphLogsMeta.Columns()), GraphLogs{}.TableName())
var setNodeSetAtExec = fmt.Sprintf(`UPDATE %s SET set_at = (SELECT stabilization_num FROM graph WHERE id = node.graph_id LIMIT 1) WHERE id = $1`, Node{}.TableName())
var upsertRecomputeHeapExec = fmt.Sprintf(`INSERT INTO %s (graph_id, user_id, node_id) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING`, GraphRecomputeHeap{}.TableName())
var clearRecomputeHeapExec = fmt.Sprintf(`DELETE FROM %s WHERE graph_id = $1`, GraphRecomputeHeap{}.TableName())
var insertRecomputeHeapExec = fmt.Sprintf(`INSERT INTO %s (graph_id, user_id, node_id) SELECT $1, $2, unnest FROM unnest($3::uuid[])`, GraphRecomputeHeap{}.TableName())
var touchGraphExec = fmt.Sprintf(`UPDATE %s SET updated_utc = current_timestamp WHERE id = $1`, Graph{}.TableName())
var graphActiveForUserQuery = fmt.Sprintf(`SELECT %s FROM %s WHERE user_id = $1 ORDER BY updated_utc DESC LIMIT 1`, db.ColumnNamesCSV(graphMeta.Columns()), Graph{}.TableName())

// neverPatch are database column names (and by chance, json field names, but we care about column names) that users
// can _never_ mutate with patch endpoints for both graphs and nodes.
var neverPatch = collections.NewSet[string]([]string{"id", "graph_id", "node_id", "user_id", "created_utc"})

func (m Manager) Deserialize(ctx context.Context, graphID uuid.UUID, skipValues, skipRecomputeHeap bool) (output *types.GraphFull, err error) {
	var g Graph
	var ok bool
	if ok, err = m.Invoke(ctx).Get(&g, graphID); err != nil {
		return
	} else if !ok {
		return
	}
	output = new(types.GraphFull)
	output.Graph = TypeGraphFromGraph(g)

	var nodes []Node
	nodes, err = m.Nodes(ctx, g.ID)
	if err != nil {
		return
	}
	for _, n := range nodes {
		output.Nodes = append(output.Nodes, TypeNodeFromNode(n))
	}

	var edges []Edge
	edges, err = m.Edges(ctx, g.ID)
	if err != nil {
		return
	}
	for _, e := range edges {
		output.Edges = append(output.Edges, types.Edge{
			ParentID:       incr.Identifier(e.ParentID),
			ChildID:        incr.Identifier(e.ChildID),
			ChildInputName: e.ChildInputName,
		})
	}

	if !skipValues {
		var values []NodeValue
		values, err = m.NodeValues(ctx, g.ID)
		if err != nil {
			return
		}
		for _, v := range values {
			var parsedValue any
			parsedValue, err = v.ParsedValue()
			if err != nil {
				return
			}
			output.Values = append(output.Values, types.NodeValue{
				ID:        incr.Identifier(v.NodeID),
				ValueType: v.ValueType,
				Value:     parsedValue,
			})
		}
	}
	if !skipRecomputeHeap {
		var recomputeHeap []GraphRecomputeHeap
		recomputeHeap, err = m.RecomputeHeap(ctx, graphID)
		if err != nil {
			return
		}
		output.RecomputeHeap = iter.Apply(recomputeHeap, func(n GraphRecomputeHeap) incr.Identifier { return incr.Identifier(n.NodeID) })
	}
	return
}

func (m Manager) GraphsForUser(ctx context.Context, userID uuid.UUID) (output []Graph, err error) {
	err = m.Invoke(ctx, db.OptLabel("graphs_for_user")).Query(graphsForUserQuery, userID).OutMany(&output)
	return
}

func (m Manager) GraphActiveForUser(ctx context.Context, userID uuid.UUID) (output Graph, found bool, err error) {
	found, err = m.Invoke(ctx, db.OptLabel("graph_active")).Query(graphActiveForUserQuery, userID).Out(&output)
	return
}

func (m Manager) PatchGraph(ctx context.Context, graphID uuid.UUID, ps types.PatchSet) (found bool, err error) {
	var params []any
	var statement = fmt.Sprintf("UPDATE %s SET updated_utc = current_timestamp", Graph{}.TableName())
	for key, value := range ps {
		if !graphMeta.HasColumn(key) {
			continue
		}
		if neverPatch.Has(key) {
			continue
		}
		params = append(params, value)
		statement = statement + fmt.Sprintf("\n, %s = $%d", key, len(params))
	}
	if len(params) == 0 {
		return false, nil
	}
	params = append(params, graphID)
	statement = statement + fmt.Sprintf("\nWHERE id = $%d", len(params))
	return db.ExecAffectedAny(m.Invoke(ctx, db.OptLabel("patch_graph")).Exec(statement, params...))
}

func (m Manager) Nodes(ctx context.Context, graphID uuid.UUID) (output []Node, err error) {
	err = m.Invoke(ctx, db.OptLabel("nodes")).Query(nodesQuery, graphID).OutMany(&output)
	return
}

func (m Manager) NodeValues(ctx context.Context, graphID uuid.UUID, nodeIDs ...uuid.UUID) (output []NodeValue, err error) {
	if len(nodeIDs) > 0 {
		err = m.Invoke(ctx, db.OptLabel("graph_node_values_by_id")).Query(nodeValuesManyQuery, graphID, nodeIDs).OutMany(&output)
	} else {
		err = m.Invoke(ctx, db.OptLabel("graph_node_values_all")).Query(nodeValuesQuery, graphID).OutMany(&output)
	}
	return
}

func (m Manager) Edges(ctx context.Context, graphID uuid.UUID) (output []Edge, err error) {
	err = m.Invoke(ctx, db.OptLabel("graph_edges")).Query(edgesQuery, graphID).OutMany(&output)
	return
}

func (m Manager) RecomputeHeap(ctx context.Context, graphID uuid.UUID) (output []GraphRecomputeHeap, err error) {
	err = m.Invoke(ctx, db.OptLabel("graph_recompute_heap")).Query(graphRecomputeHeapQuery, graphID).OutMany(&output)
	return
}

func (m Manager) Graph(ctx context.Context, id uuid.UUID) (output Graph, found bool, err error) {
	found, err = m.Invoke(ctx).Get(&output, id)
	return
}

func (m Manager) Node(ctx context.Context, id uuid.UUID) (output Node, found bool, err error) {
	found, err = m.Invoke(ctx).Get(&output, id)
	return
}

func (m Manager) NodeValue(ctx context.Context, graphID, nodeID uuid.UUID) (output NodeValue, found bool, err error) {
	found, err = m.Invoke(ctx).Get(&output, graphID, nodeID)
	return
}

func (m Manager) SetNodeValues(ctx context.Context, graphID, userID uuid.UUID, values map[uuid.UUID]any) error {
	if len(values) == 0 {
		return nil
	}
	statement := `INSERT INTO node_value (graph_id, user_id, node_id, value_type, value) VALUES `
	params := []any{graphID, userID}
	var first = true
	for key, value := range values {
		if first {
			statement = statement + fmt.Sprintf("($1, $2, $%d, $%d, $%d)", len(params)+1, len(params)+2, len(params)+3)
			first = false
		} else {
			statement = statement + fmt.Sprintf(", ($1, $2, $%d, $%d, $%d)", len(params)+1, len(params)+2, len(params)+3)
			first = false
		}
		params = append(params, key, DetectValueType(value), db.JSON(value))
	}
	statement = statement + ` ON CONFLICT (graph_id, node_id) DO UPDATE SET value_type = excluded.value_type, value = excluded.value`
	return db.ExecErr(m.Invoke(ctx, db.OptLabel("set_node_values")).Exec(statement, params...))
}

func (m Manager) DeleteGraph(ctx context.Context, graphID uuid.UUID) (found bool, err error) {
	if _, err = m.Invoke(ctx, db.OptLabel("delete_graph_edges")).Exec(`DELETE FROM edge WHERE graph_id = $1`, graphID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_graph_node_values")).Exec(`DELETE FROM node_value WHERE graph_id = $1`, graphID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_graph_recompute_heap")).Exec(`DELETE FROM graph_recompute_heap WHERE graph_id = $1`, graphID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_graph_nodes")).Exec(`DELETE FROM node WHERE graph_id = $1`, graphID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_graph_logs")).Exec(`DELETE FROM graph_log WHERE graph_id = $1`, graphID); err != nil {
		return
	}
	return db.ExecAffectedAny(m.Invoke(ctx, db.OptLabel("delete_graph_graph")).Exec(`DELETE FROM graph WHERE id = $1`, graphID))
}

func (m Manager) DeleteNode(ctx context.Context, nodeID uuid.UUID) (found bool, err error) {
	if _, err = m.Invoke(ctx, db.OptLabel("delete_node_edges")).Exec(`DELETE FROM edge WHERE child_id = $1 OR parent_id = $1`, nodeID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_node_recompute_heap")).Exec(`DELETE FROM graph_recompute_heap WHERE node_id = $1`, nodeID); err != nil {
		return
	}
	if _, err = m.Invoke(ctx, db.OptLabel("delete_node_values")).Exec(`DELETE FROM node_value WHERE node_id = $1 `, nodeID); err != nil {
		return
	}
	return db.ExecAffectedAny(m.Invoke(ctx, db.OptLabel("delete_node_node")).Exec(`DELETE FROM node WHERE id = $1`, nodeID))
}

func (m Manager) DeleteEdge(ctx context.Context, parentID, childID uuid.UUID, childInputName string) (err error) {
	if _, err = m.Invoke(ctx, db.OptLabel("delete_edge")).Exec(`DELETE FROM edge WHERE parent_id = $1 AND child_id = $2 AND child_input_name = $3`, parentID, childID, childInputName); err != nil {
		return
	}
	return
}

func (m Manager) SetViewport(ctx context.Context, graphID uuid.UUID, viewport types.Viewport) (err error) {
	_, err = m.Invoke(ctx, db.OptLabel("set_viewport")).Exec(setViewportExec, viewport.X, viewport.Y, viewport.Zoom, graphID)
	return
}

// TouchGraph sets the updated time for the graph in the database.
//
// The updated time is used to order the graph in lists, so this will push
// the graph to be the most recently updated graph in those lists.
func (m Manager) TouchGraph(ctx context.Context, graphID uuid.UUID) (err error) {
	_, err = m.Invoke(ctx, db.OptLabel("touch_graph")).Exec(touchGraphExec, graphID)
	return
}

func (m Manager) GraphLogsLatest(ctx context.Context, graphID uuid.UUID) (output GraphLogs, found bool, err error) {
	found, err = m.Invoke(ctx, db.OptLabel("graph_logs_latest")).Query(graphLogsLatestQuery, graphID).Out(&output)
	return
}

func (m Manager) PatchNodes(ctx context.Context, graphID uuid.UUID, ps types.PatchSet) error {
	var params []any
	var statement = fmt.Sprintf("UPDATE %s SET", Node{}.TableName())
	var first = true
	for key, value := range ps {
		if !nodesMeta.HasColumn(key) {
			continue
		}
		if neverPatch.Has(key) {
			continue
		}
		params = append(params, value)
		if first {
			statement = statement + fmt.Sprintf("\n%s = $%d", key, len(params))
			first = false
		} else {
			statement = statement + fmt.Sprintf("\n, %s = $%d", key, len(params))
		}
	}
	if len(params) == 0 {
		return nil
	}
	params = append(params, graphID)
	statement = statement + fmt.Sprintf("\nWHERE graph_id = $%d", len(params))
	return db.ExecErr(m.Invoke(ctx, db.OptLabel("patch_nodes")).Exec(statement, params...))
}

func (m Manager) PatchNode(ctx context.Context, nodeID uuid.UUID, ps types.PatchSet) (bool, error) {
	var params []any
	var statement = fmt.Sprintf("UPDATE %s SET", Node{}.TableName())
	var first = true
	for key, value := range ps {
		if !nodesMeta.HasColumn(key) {
			continue
		}
		if neverPatch.Has(key) {
			continue
		}
		params = append(params, value)
		if first {
			statement = statement + fmt.Sprintf("\n%s = $%d", key, len(params))
			first = false
		} else {
			statement = statement + fmt.Sprintf("\n, %s = $%d", key, len(params))
		}
	}
	if len(params) == 0 {
		return false, nil
	}
	params = append(params, nodeID)
	statement = statement + fmt.Sprintf("\nWHERE id = $%d", len(params))
	return db.ExecAffectedAny(m.Invoke(ctx, db.OptLabel("patch_node")).Exec(statement, params...))
}

func (m Manager) MarkNodeStale(ctx context.Context, graphID, userID, nodeID uuid.UUID) (found bool, err error) {
	found, err = db.ExecAffectedAny(m.Invoke(ctx, db.OptLabel("mark_node_stale")).Exec(setNodeSetAtExec, nodeID))
	if err != nil {
		return
	}
	if !found {
		return
	}
	err = db.ExecErr(m.Invoke(ctx, db.OptLabel("upsert_recompute_heap")).Exec(upsertRecomputeHeapExec, graphID, userID, nodeID))
	return
}

func (m Manager) UpdateGraphPostStabilization(ctx context.Context, graphID uuid.UUID, stabilizationNum uint64) error {
	return db.ExecErr(m.Invoke(ctx, db.OptLabel("update_graph_post_stabilization")).Exec(`UPDATE graph SET stabilization_num = $2 WHERE id = $1`, graphID, stabilizationNum))
}

func (m Manager) SetRecomputeHeap(ctx context.Context, graphID, userID uuid.UUID, nodeIDs ...uuid.UUID) (err error) {
	err = db.ExecErr(m.Invoke(ctx, db.OptLabel("clear_recompute_heap")).Exec(clearRecomputeHeapExec, graphID))
	if err != nil {
		return
	}
	if len(nodeIDs) > 0 {
		err = db.ExecErr(m.Invoke(ctx, db.OptLabel("insert_recompute_heap")).Exec(insertRecomputeHeapExec, graphID, userID, nodeIDs))
	}
	return
}

func (m Manager) SetNodeHeights(ctx context.Context, graphID uuid.UUID, nodeHeights map[uuid.UUID]int) (err error) {
	if len(nodeHeights) == 0 {
		return nil
	}

	sql := `UPDATE node as n SET height = c.height FROM ( values `
	var params []any
	var first = true
	for nodeID, height := range nodeHeights {
		var comma string
		if first {
			first = false
		} else {
			comma = ","
		}
		sql = sql + fmt.Sprintf("\n%s($%d::uuid, $%d::bigint)", comma, len(params)+1, len(params)+2)
		params = append(params, nodeID, int64(height))
	}
	sql = sql + ") as c (node_id, height) WHERE c.node_id = n.id"
	err = db.ExecErr(m.Invoke(ctx, db.OptLabel("set_node_heights")).Exec(sql, params...))
	return
}

func (m Manager) SetNodeMetadata(ctx context.Context, graphID uuid.UUID, nodeMetadata map[uuid.UUID]Node) (err error) {
	if len(nodeMetadata) == 0 {
		return nil
	}

	sql := `UPDATE node as n SET set_at = c.set_at, changed_at = c.changed_at, recomputed_at = c.recomputed_at FROM ( values `
	var params []any
	var first = true
	for nodeID, meta := range nodeMetadata {
		var comma string
		if first {
			first = false
		} else {
			comma = ","
		}
		sql = sql + fmt.Sprintf("\n%s($%d::uuid, $%d::bigint, $%d::bigint, $%d::bigint)", comma, len(params)+1, len(params)+2, len(params)+3, len(params)+4)
		params = append(params, nodeID, meta.SetAt, meta.ChangedAt, meta.RecomputedAt)
	}
	sql = sql + ") as c (node_id, set_at, changed_at, recomputed_at) WHERE c.node_id = n.id"
	err = db.ExecErr(m.Invoke(ctx, db.OptLabel("set_node_metadata")).Exec(sql, params...))
	return
}