KG_QUERY.hdbprocedure

-- KG_QUERY.hdbprocedure
-- ============================================================
-- WHY DEFINER
--   HANA Cloud KGE uses per-graph ACL: the first runtime user that executes a
--   SPARQL UPDATE becomes the graph owner.  Subsequent runtime users — even
--   those with the same system-level SPARQL QUERY privilege — may be affected.
--
--   By declaring SQL SECURITY DEFINER, the procedure body runs as the HDI
--   container's object-owner (#OO) regardless of which binding invokes it.
--   The object-owner identity is stable across all bindings and deploys.
--
--   Memory: hana_sparql_per_graph_acl_creator_owns
--   Spec:   docs/superpowers/specs/2026-06-22-kg-sparql-definer-procedures-design.md
--   Issue:  #533
--
-- DISPATCH TABLE
--   :query_name  'NEIGHBORHOOD'        – concepts/tutorials adjacent to a tutorial
--   :query_name  'PATH_BETWEEN'        – (Phase 2 stub) shortest path between two tutorials
--   :query_name  'CONCEPTS_FOR_USER'   – (Phase 2 stub) concepts learned by a user
--   :query_name  'EXPLORE_GRAPH_BULK'  – (Phase 3, #446) all edges in the graph as
--                                        (s, p, o, sName, oName) rows for /graph/explore-data
--   :query_name  <anything else>       – SIGNAL KG_UNKNOWN_QUERY (10005)
--
-- PARAMETERS
--   query_name        – one of the three names above
--   p1                – per-query: NEIGHBORHOOD=full tutorial IRI,
--                       PATH_BETWEEN=from-tutorial IRI, CONCEPTS_FOR_USER=user UUID
--   p2                – PATH_BETWEEN only: to-tutorial IRI; NULL for others
--   p3                – reserved for future use; pass NULL
--   override_graph_iri – when non-NULL, replaces the hardcoded production graph IRI.
--                       Used by hybrid tests to target a TEST graph without touching
--                       production data.  NULL → prod default (tutorials-v2).
--   response          – OUT: SPARQL JSON result body (NCLOB)
--   headers           – OUT: HTTP response headers from HANA SPARQL engine
--
-- ERROR CODES (user-defined range 10000–19999)
--   10005  KG_UNKNOWN_QUERY        – :query_name not in dispatch table
--   10006  KG_INVALID_TUTORIAL_IRI – IRI not matching expected pattern
--   10007  KG_INVALID_USER_ID      – UUID not matching UUID pattern
--
-- HDI SCHEMA-LOCAL REFERENCE
--   HDI refuses to compile DEFINER-security procedures that cross schema
--   boundaries (e.g. CALL SYS.SPARQL_EXECUTE). The workaround is a synonym:
--   db/src/SYS_SPARQL_EXECUTE.hdbsynonym maps the local name
--   SYS_SPARQL_EXECUTE → SYS.SPARQL_EXECUTE.  No change to runtime behaviour.
--
-- QA NOTE
--   db-qa/src/procedures/KG_QUERY.hdbprocedure contains a stub with the same
--   signature but a SIGNAL KG_NOT_AVAILABLE_ON_QA body (see spec §"QA channel").
--
-- SPARQL SOURCES
--   The three SPARQL bodies are copied verbatim from srv/lib/kg-queries.js
--   (NEIGHBORHOOD_QUERY line 237, PATH_BETWEEN_QUERY line 284,
--   CONCEPTS_FOR_USER_QUERY line 299).  The $SLUG / $FROM_SLUG / $TO_SLUG /
--   $USER_ID placeholders are rewritten as SQL string-concat with the
--   corresponding :pN parameter.  The hardcoded FROM <...> line is replaced
--   by the from_clause variable (COALESCE on override_graph_iri).
-- ============================================================

PROCEDURE KG_QUERY (
  IN  query_name        NVARCHAR(50),
  IN  p1                NVARCHAR(500),
  IN  p2                NVARCHAR(500),
  IN  p3                NVARCHAR(500),
  IN  override_graph_iri NVARCHAR(500),
  OUT response          NCLOB,
  OUT headers           NVARCHAR(5000)
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
BEGIN
  -- Error condition declarations.
  -- HANA Cloud SQLScript SIGNAL syntax: DECLARE <name> CONDITION FOR SQL_ERROR_CODE <n>
  -- The SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT form does NOT compile on HANA Cloud.
  DECLARE KG_UNKNOWN_QUERY        CONDITION FOR SQL_ERROR_CODE 10005;
  DECLARE KG_INVALID_TUTORIAL_IRI CONDITION FOR SQL_ERROR_CODE 10006;
  DECLARE KG_INVALID_USER_ID      CONDITION FOR SQL_ERROR_CODE 10007;

  DECLARE sparql     NCLOB;
  DECLARE from_clause NVARCHAR(600);

  -- Build the FROM clause.  When override_graph_iri is NULL (normal production
  -- call), we use the hardcoded production graph IRI that MUST stay in sync
  -- with srv/lib/kg-graph-rebuild.js DEFAULT_GRAPH_IRI.  When non-NULL (hybrid
  -- tests, admin probe), the caller-supplied IRI is used instead.
  from_clause := 'FROM <' ||
    COALESCE(:override_graph_iri, 'https://developers.sap.com/kg/tutorials-v3') ||
    '>';

  -- Dispatch on query_name using IF/ELSEIF chain.
  -- HANA SQLScript supports CASE as an expression but the statement form with
  -- multiple statements per branch is less portable; IF/ELSEIF is unambiguous.

  IF :query_name = 'NEIGHBORHOOD' THEN

    -- Validate :p1 as a well-formed tutorial IRI.
    -- Pattern: https://developers.sap.com/kg/tutorial/<slug>
    -- where <slug> is 1–80 lowercase alphanumeric chars and hyphens.
    -- JS caller checks err.code === 10006.
    IF :p1 IS NULL OR
       NOT (:p1 LIKE_REGEXPR '^https://developers\.sap\.com/kg/tutorial/[a-z0-9-]{1,80}$') THEN
      SIGNAL KG_INVALID_TUTORIAL_IRI;
    END IF;

    -- NEIGHBORHOOD_QUERY — copied verbatim from srv/lib/kg-queries.js line 237.
    -- $SLUG placeholder → :p1 (the full tutorial IRI, received verbatim from caller).
    -- FROM <hardcoded> → from_clause (COALESCE override).
    -- SPARQL double-quoted strings ("teaches", "prerequisitesOf", etc.) do not
    -- conflict with SQL single-quote string delimiters.
    --
    -- Per-arm LIMITs (kg-widget-ux-polish, 2026-06-30): the original SPARQL
    -- shared a single `LIMIT 60` across all four UNION arms. With small
    -- published-concept counts (~10) the math worked out. Once DEV hit 110
    -- published concepts, the whatToLearnNext arm — which scales as
    -- O(concepts × :requires × :teaches) — alone filled the entire 60-row
    -- budget, starving teaches/prereqsOf/sharedConcepts to zero rows. The
    -- handler then saw `teaches: []` and the widget collapsed.
    --
    -- Fix: each arm is wrapped in a `{ SELECT ... LIMIT n }` subquery so the
    -- expensive whatToLearnNext arm can't starve the cheap teaches arm. The
    -- JS-side rankNeighborhood caps each bucket at TOP_N = 10 after dedup,
    -- so 15/15/15/30 gives the ranker headroom without bloating payloads.
    --
    -- Per-arm projection scoping: SPARQL requires each inner SELECT to
    -- project only variables visible in its own WHERE. The teaches arm is
    -- the only one that binds ?targetLabel (via `kg:name`) and a weight
    -- literal — the other three arms project only what they actually
    -- bind. The outer DISTINCT SELECT fills in UNBOUND for variables a
    -- given branch doesn't supply; the JS-side rankNeighborhood falls
    -- back to DEFAULT_WEIGHT and a slug-as-label heuristic for those.
    sparql :=
      'PREFIX kg: <https://developers.sap.com/kg/>' || CHAR(10) ||
      '' || CHAR(10) ||
      'SELECT DISTINCT ?type ?targetSlug ?targetLabel ?weight' || CHAR(10) ||
      from_clause || CHAR(10) ||
      'WHERE {' || CHAR(10) ||
      '  {' || CHAR(10) ||
      '    SELECT ?type ?targetSlug ?targetLabel ?weight WHERE {' || CHAR(10) ||
      '      # teaches: concepts the input tutorial directly teaches' || CHAR(10) ||
      '      <' || :p1 || '> kg:teaches ?concept .' || CHAR(10) ||
      '      ?concept kg:slug ?targetSlug ; kg:name ?targetLabel .' || CHAR(10) ||
      '      BIND("teaches" AS ?type) BIND(1.0 AS ?weight)' || CHAR(10) ||
      '    } LIMIT 15' || CHAR(10) ||
      '  } UNION {' || CHAR(10) ||
      '    SELECT ?type ?targetSlug ?weight WHERE {' || CHAR(10) ||
      '      # prerequisitesOf: tutorials teaching concepts the input tutorial requires' || CHAR(10) ||
      '      <' || :p1 || '> kg:teaches ?concept .' || CHAR(10) ||
      '      ?concept kg:requires ?prereq .' || CHAR(10) ||
      '      ?prereqTut kg:teaches ?prereq .' || CHAR(10) ||
      '      FILTER(?prereqTut != <' || :p1 || '>)' || CHAR(10) ||
      '      BIND(REPLACE(STR(?prereqTut), "https://developers.sap.com/kg/tutorial/", "") AS ?targetSlug)' || CHAR(10) ||
      '      BIND("prerequisitesOf" AS ?type) BIND(0.9 AS ?weight)' || CHAR(10) ||
      '    } LIMIT 15' || CHAR(10) ||
      '  } UNION {' || CHAR(10) ||
      '    SELECT ?type ?targetSlug WHERE {' || CHAR(10) ||
      '      # sharedConcepts: other tutorials teaching the same concepts' || CHAR(10) ||
      '      <' || :p1 || '> kg:teaches ?sharedConcept .' || CHAR(10) ||
      '      ?other kg:teaches ?sharedConcept .' || CHAR(10) ||
      '      FILTER(?other != <' || :p1 || '>)' || CHAR(10) ||
      '      BIND(REPLACE(STR(?other), "https://developers.sap.com/kg/tutorial/", "") AS ?targetSlug)' || CHAR(10) ||
      '      BIND("sharedConcepts" AS ?type)' || CHAR(10) ||
      '    } LIMIT 15' || CHAR(10) ||
      '  } UNION {' || CHAR(10) ||
      '    SELECT ?type ?targetSlug WHERE {' || CHAR(10) ||
      '      # whatToLearnNext: tutorials teaching concepts that require what the input teaches' || CHAR(10) ||
      '      <' || :p1 || '> kg:teaches ?known .' || CHAR(10) ||
      '      ?advanced kg:requires ?known .' || CHAR(10) ||
      '      ?nextTut kg:teaches ?advanced .' || CHAR(10) ||
      '      FILTER(?nextTut != <' || :p1 || '>)' || CHAR(10) ||
      '      BIND(REPLACE(STR(?nextTut), "https://developers.sap.com/kg/tutorial/", "") AS ?targetSlug)' || CHAR(10) ||
      '      BIND("whatToLearnNext" AS ?type)' || CHAR(10) ||
      '    } LIMIT 30' || CHAR(10) ||
      '  }' || CHAR(10) ||
      '}';

  ELSEIF :query_name = 'PATH_BETWEEN' THEN

    -- Validate :p1 (from-tutorial IRI) and :p2 (to-tutorial IRI).
    -- Both must match the tutorial IRI pattern.
    IF :p1 IS NULL OR
       NOT (:p1 LIKE_REGEXPR '^https://developers\.sap\.com/kg/tutorial/[a-z0-9-]{1,80}$') OR
       :p2 IS NULL OR
       NOT (:p2 LIKE_REGEXPR '^https://developers\.sap\.com/kg/tutorial/[a-z0-9-]{1,80}$') THEN
      SIGNAL KG_INVALID_TUTORIAL_IRI;
    END IF;

    -- PATH_BETWEEN: three-arm UNION SPARQL.
    -- See docs/superpowers/specs/2026-06-22-issue-445-joule-pathbetween-design.md
    -- for the rationale (sparse kg:requires graph; coverage-fallback design).
    --
    -- NOTE: :p2 (toSlug IRI) is VALIDATED by the block above (defense-in-depth)
    -- but is INTENTIONALLY NOT referenced in the SPARQL body. JS-layer
    -- post-processing does the toSlug match. This produces graceful fallback
    -- ('closest topical neighbors') when no exact path to toSlug exists,
    -- rather than the 'no path found' answer the issue body originally
    -- proposed. See spec § "Layer 1 — KG_QUERY procedure: PATH_BETWEEN branch".
    --
    -- HANA KGE does NOT support {n,m} counted-range property paths (Task 0
    -- spike confirmed 'Unsupported functionality: Path repeat range'). We use
    -- + (plus closure, one-or-more hops) instead. Depth bounded by LIMIT 10
    -- and the 5s wall-clock timeout enforced by kgQuery()'s withTimeout wrapper.
    sparql :=
      'PREFIX kg: <https://developers.sap.com/kg/>' || CHAR(10) ||
      'SELECT ?b ?pathType ?pathTypeRank ?hopCount' || CHAR(10) ||
      from_clause || CHAR(10) ||
      'WHERE {' || CHAR(10) ||
      '  {' || CHAR(10) ||
      '    # ARM 1: Prerequisite chain (preferred when data supports it)' || CHAR(10) ||
      '    <' || :p1 || '> kg:teaches ?c1 .' || CHAR(10) ||
      '    ?c1 (^kg:requires)+ ?cN .' || CHAR(10) ||
      '    ?b kg:teaches ?cN .' || CHAR(10) ||
      '    FILTER(?b != <' || :p1 || '>)' || CHAR(10) ||
      '    BIND("PREREQ" AS ?pathType)' || CHAR(10) ||
      '    BIND(1 AS ?pathTypeRank)' || CHAR(10) ||
      '    BIND(0 AS ?hopCount)' || CHAR(10) ||
      '  } UNION {' || CHAR(10) ||
      '    # ARM 2: Co-completion adjacency (behavioral signal, dense)' || CHAR(10) ||
      '    <' || :p1 || '> (kg:coCompletedWith)+ ?b .' || CHAR(10) ||
      '    FILTER(?b != <' || :p1 || '>)' || CHAR(10) ||
      '    BIND("CO_COMPLETED" AS ?pathType)' || CHAR(10) ||
      '    BIND(2 AS ?pathTypeRank)' || CHAR(10) ||
      '    BIND(0 AS ?hopCount)' || CHAR(10) ||
      '  } UNION {' || CHAR(10) ||
      '    # ARM 3: Shared-concept proximity (semantic, always-on)' || CHAR(10) ||
      '    <' || :p1 || '> kg:teaches ?c .' || CHAR(10) ||
      '    ?b kg:teaches ?c .' || CHAR(10) ||
      '    FILTER(?b != <' || :p1 || '>)' || CHAR(10) ||
      '    BIND("SHARED_CONCEPT" AS ?pathType)' || CHAR(10) ||
      '    BIND(3 AS ?pathTypeRank)' || CHAR(10) ||
      '    BIND(0 AS ?hopCount)' || CHAR(10) ||
      '  }' || CHAR(10) ||
      '}' || CHAR(10) ||
      'ORDER BY ?pathTypeRank' || CHAR(10) ||
      'LIMIT 10';

  ELSEIF :query_name = 'CONCEPTS_FOR_USER' THEN

    -- Validate :p1 as a UUID (lowercase hex digits with hyphens, 8-4-4-4-12 format).
    -- JS caller checks err.code === 10007.
    IF :p1 IS NULL OR
       NOT (:p1 LIKE_REGEXPR '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$') THEN
      SIGNAL KG_INVALID_USER_ID;
    END IF;

    -- CONCEPTS_FOR_USER_QUERY — copied verbatim from srv/lib/kg-queries.js line 299.
    -- Phase 2 stub: returns LIMIT 0 (no rows). $USER_ID → :p1.
    -- FROM <hardcoded> → from_clause.
    sparql :=
      'PREFIX kg: <https://developers.sap.com/kg/>' || CHAR(10) ||
      '' || CHAR(10) ||
      '# Phase 2 stub: PR 5 declares; PR 6+ implements.' || CHAR(10) ||
      '# Will be: SELECT concepts taught by tutorials completed by ' || :p1 || '.' || CHAR(10) ||
      'SELECT ?placeholder' || CHAR(10) ||
      from_clause || CHAR(10) ||
      'WHERE { ?placeholder ?p ?o }' || CHAR(10) ||
      'LIMIT 0';

  ELSEIF :query_name = 'EXPLORE_GRAPH_BULK' THEN

    -- EXPLORE_GRAPH_BULK — return all (subject, predicate, object) triples
    -- in the named graph that participate in entity-to-entity edges, with
    -- the optional kg:name literal for the subject and object resolved into
    -- the same row when available.
    --
    -- WHY: the /graph/explore-data endpoint (issue #446 PR 4/9) needs the
    -- complete graph as JSON for client-side Sigma.js rendering. The
    -- projection in srv/lib/kg-projection.js emits only Concepts with an
    -- explicit rdf:type; Tutorials/Missions/Groups/Products/Categories/Tags
    -- carry no type triple — their entity-type is encoded in the IRI prefix
    -- (e.g. .../tutorial/X). The JS helper (srv/lib/kg-explore-data.js)
    -- derives the type from the IRI prefix; this query just returns
    -- (s, p, o) with optional names.
    --
    -- Filter: skip the literal-emitting predicates (kg:slug, kg:name) and
    -- the rdf:type triple — those carry no graph-shape information that the
    -- explore UI needs. Keep only the 13 edge predicates (9 core +
    -- kg:presents / kg:aboutTutorial for Devtoberfest sessions, #2311 +
    -- kg:inTrack / kg:presentedBy for TechEd sessions, #2312).
    --
    -- p1/p2/p3 are unused for this query name. No validation needed beyond
    -- the dispatch — the body has no caller-supplied substitution.
    sparql :=
      'PREFIX kg: <https://developers.sap.com/kg/>' || CHAR(10) ||
      'PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>' || CHAR(10) ||
      'SELECT ?s ?p ?o ?sName ?oName' || CHAR(10) ||
      from_clause || CHAR(10) ||
      'WHERE {' || CHAR(10) ||
      '  ?s ?p ?o .' || CHAR(10) ||
      '  FILTER(?p IN (' || CHAR(10) ||
      '    kg:teaches, kg:requires, kg:relatedTo, kg:extends,' || CHAR(10) ||
      '    kg:partOf, kg:taggedWith, kg:aboutProduct,' || CHAR(10) ||
      '    kg:inCategory, kg:coCompletedWith,' || CHAR(10) ||
      '    kg:presents, kg:aboutTutorial,' || CHAR(10) ||
      '    kg:inTrack, kg:presentedBy' || CHAR(10) ||
      '  ))' || CHAR(10) ||
      '  OPTIONAL { ?s kg:name ?sName }' || CHAR(10) ||
      '  OPTIONAL { ?o kg:name ?oName }' || CHAR(10) ||
      '}';

  ELSE

    -- Unknown query name — signal before any SPARQL is assembled.
    SIGNAL KG_UNKNOWN_QUERY;

  END IF;

  -- Request JSON response format from the HANA SPARQL engine. Without this
  -- Accept header, SPARQL_EXECUTE defaults to XML (application/sparql-results+xml)
  -- and every JS caller's JSON.parse(response) throws silently — the
  -- per-query parser falls through to `return []`, so the explore graph
  -- shows zero nodes and the tutorial sidebar shows zero neighborhood concepts.
  -- The bug shipped because no test exercised the live SPARQL_EXECUTE call
  -- (hybrid tests mock kgQuery; unit tests pre-supply a JSON response string).
  -- Diagnosed 2026-06-28: KG_QUERY('EXPLORE_GRAPH_BULK') returned a valid XML
  -- body of 36 091 triples that parseExploreBindings silently dropped.
  CALL SYS_SPARQL_EXECUTE(:sparql, 'Accept: application/sparql-results+json', response, headers);
END;