dtoro
d2950dd09d
Deleted 8 genuinely unused sqlc queries (no inline equivalent): - UpsertCurrentRelationship, ListEntitiesCapped, ListEntityStatus, UpdateSignalState, InsertClassification, InsertFeedback, InsertSkill, UpsertCurrentRelationship — all had zero call sites. Migrated 9 inline raw SQL sites to use sqlc queries: - GetOntology (impl.go): ListEntityTypes, ListRelationshipTypes, ListLifecycleDefs — replaces 3 raw pool.Query blocks with typed sqlcgen calls, eliminating manual row scanning. - EndRelationship (phase3.go): EndCurrentRelationship — replaces tx.Exec with sqlcgen.New(tx).EndCurrentRelationship. - checkPrecondition (impl.go): GetEntityStatus — replaces tx.QueryRow + manual Scan with sqlcgen.New(tx).GetEntityStatus. - GetEntityRelations (impl.go): ListEntityRelations — replaces raw pool.Query + scanRelationships helper (now deleted). - GetGraph (impl.go): ListGraphEdges — replaces raw pool.Query + scanRelationships. - resolveEntityID (impl.go): GetEntityBySlug/GetEntityByID — replaces raw pool.QueryRow + Scan. - createApproval (mcp/server.go): InsertApproval — replaces raw pool.Exec with sqlcgen.InsertApproval. Deleted scanRelationships helper (was only used by the two migrated graph queries above). Regenerated sqlcgen — also picks up stale model updates (AgentSession, SessionPlanStep, SessionQuestion, etc. from recent migrations). Documented the carve-out in .agents/dev/CONTRIBUTING.md §SQL conventions: sqlc is the default; raw pool.Query/Exec is reserved for LISTEN/NOTIFY, dynamic WHERE builders, blast_radius(), and COPY. go vet, build, httpapi/mcp/db tests all pass. -383/+170 lines.
50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
-- Entity read + mutation queries (API paths). Aliased `e` throughout to
|
|
-- avoid ambiguity with joined tables.
|
|
|
|
-- name: GetEntityByID :one
|
|
SELECT e.* FROM entities e WHERE e.id = $1;
|
|
|
|
-- name: GetEntityBySlug :one
|
|
SELECT e.* FROM entities e WHERE e.slug = $1;
|
|
|
|
-- name: ListEntities :many
|
|
WITH RECURSIVE tt AS (
|
|
SELECT name FROM entity_types WHERE sqlc.narg('type')::text IS NULL OR name = sqlc.narg('type')
|
|
UNION
|
|
SELECT et.name FROM entity_types et JOIN tt ON et.parent_type = tt.name
|
|
WHERE sqlc.narg('type')::text IS NOT NULL
|
|
)
|
|
SELECT e.* FROM entities e
|
|
JOIN entity_types et ON et.name = e.type
|
|
WHERE e.type IN (SELECT name FROM tt)
|
|
AND (sqlc.narg('state')::text IS NULL OR e.state = sqlc.narg('state'))
|
|
AND (sqlc.narg('domain')::text IS NULL OR et.domain = sqlc.narg('domain'))
|
|
AND (sqlc.narg('layer')::text IS NULL OR et.layer = sqlc.narg('layer'))
|
|
AND (sqlc.narg('q')::text IS NULL
|
|
OR e.slug ILIKE '%'||sqlc.narg('q')||'%'
|
|
OR e.name ILIKE '%'||sqlc.narg('q')||'%')
|
|
AND (sqlc.narg('cursor')::text IS NULL OR e.slug > sqlc.narg('cursor'))
|
|
ORDER BY e.slug
|
|
LIMIT sqlc.arg('lim');
|
|
|
|
-- name: InsertEntity :one
|
|
INSERT INTO entities (id, slug, type, name, state, attributes)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING *;
|
|
|
|
-- name: UpdateEntity :one
|
|
UPDATE entities SET
|
|
name = COALESCE(sqlc.narg('name'), name),
|
|
state = COALESCE(sqlc.narg('state'), state),
|
|
attributes = COALESCE(sqlc.narg('attributes'), attributes),
|
|
maintenance_until = CASE WHEN sqlc.arg('set_maintenance')::bool
|
|
THEN sqlc.narg('maintenance_until') ELSE maintenance_until END,
|
|
version = version + 1,
|
|
updated_at = now()
|
|
WHERE id = sqlc.arg('id') AND version = sqlc.arg('version')
|
|
RETURNING *;
|
|
|
|
-- blast_radius(): the recursive-CTE traversal function's TABLE return type
|
|
-- is opaque to sqlc's analyzer — that one query stays hand-written pgx in
|
|
-- internal/httpapi (see impl.go).
|