harn-stdlib 0.8.79

/**
 * std/postgres/query - small SQL ergonomics over std/postgres.
 *
 * SQL remains the source of truth. These helpers normalize query records,
 * build `$n` parameter lists from readable SQL templates, and centralize
 * explicit SQL-structure fragments for the cases Postgres cannot bind.
 */
import "std/postgres"

type PgQueryMode = "one" | "many" | "exec"

type PgSqlFragment = {
  _type: "std/postgres/query.sql_fragment.v1",
  __harn_postgres_query_trusted_fragment: bool,
  sql: string,
}

type PgNamedQuery = {
  name: string,
  mode: PgQueryMode,
  sql: string,
  params: list<any>,
  options?: dict,
}

type PgQuery = {name?: string, mode?: PgQueryMode, sql: string, params?: list<any>, options?: dict}

let __QUERY_MODES = ["one", "many", "exec"]

let __IDENTIFIER_PATTERN = "^[A-Za-z_][A-Za-z0-9_]*$"

let __SQL_FRAGMENT_TYPE = "std/postgres/query.sql_fragment.v1"

fn __sql_fragment(sql: string) -> PgSqlFragment {
  if type_of(sql) != "string" {
    throw "std/postgres/query: SQL fragment must be a string"
  }
  return {_type: __SQL_FRAGMENT_TYPE, __harn_postgres_query_trusted_fragment: true, sql: sql}
}

fn __is_sql_fragment(value) -> bool {
  return type_of(value) == "dict"
    && value?._type == __SQL_FRAGMENT_TYPE
    && value?.__harn_postgres_query_trusted_fragment
}

fn __sql_fragment_text(value) -> string {
  let fragment_sql = value?.sql
  if type_of(fragment_sql) != "string" {
    throw "std/postgres/query: SQL fragment sql must be a string"
  }
  return fragment_sql
}

fn __query_name(query) -> string {
  if type_of(query) != "dict" {
    return ""
  }
  let name = query?.name
  if name == nil {
    return ""
  }
  if type_of(name) != "string" {
    throw "std/postgres/query: query name must be a string"
  }
  return name
}

fn __query_error_prefix(query) -> string {
  let name = __query_name(query)
  if name == "" {
    return "std/postgres/query: "
  }
  return "std/postgres/query `" + name + "`: "
}

fn __normalize_query(query, expected_mode = nil) {
  if type_of(query) != "dict" {
    throw "std/postgres/query: query must be a dict"
  }
  let sql = query?.sql
  if type_of(sql) != "string" || trim(sql) == "" {
    throw __query_error_prefix(query) + "sql must be a non-empty string"
  }
  let params = query?.params ?? []
  if type_of(params) != "list" {
    throw __query_error_prefix(query) + "params must be a list"
  }
  let mode = query?.mode ?? expected_mode
  if mode != nil && !contains(__QUERY_MODES, mode) {
    throw __query_error_prefix(query) + "mode must be one of one, many, exec"
  }
  if expected_mode != nil && query?.mode != nil && query.mode != expected_mode {
    throw __query_error_prefix(query) + "mode " + query.mode + " cannot run through " + expected_mode
  }
  let options = query?.options
  if options != nil && type_of(options) != "dict" {
    throw __query_error_prefix(query) + "options must be a dict"
  }
  return {name: __query_name(query), mode: mode, sql: sql, params: params, options: options}
}

fn __with_query_name(query, body) {
  try {
    return body()
  } catch (error) {
    if query.name == "" {
      throw error
    }
    throw __query_error_prefix(query) + to_string(error)
  }
}

fn __find_placeholder_end(chars: list<string>, start: int) -> int {
  var idx = start
  while idx < len(chars) {
    if chars[idx] == "}" {
      return idx
    }
    idx = idx + 1
  }
  return -1
}

fn __render_placeholder(key: string, value, params: list<any>, positions: dict) {
  if __is_sql_fragment(value) {
    return {sql: __sql_fragment_text(value), params: params, positions: positions}
  }
  let existing = positions[key]
  if existing != nil {
    return {sql: "$" + to_string(existing), params: params, positions: positions}
  }
  let next = len(params) + 1
  return {sql: "$" + to_string(next), params: params.push(value), positions: positions + {[key]: next}}
}

fn __render_sql_template(template: string, values: dict) {
  if type_of(template) != "string" || trim(template) == "" {
    throw "std/postgres/query: SQL template must be a non-empty string"
  }
  if type_of(values) != "dict" {
    throw "std/postgres/query: SQL template values must be a dict"
  }
  let template_chars = chars(template)
  var idx = 0
  var out = ""
  var params = []
  var positions = {}
  while idx < len(template_chars) {
    let char = template_chars[idx]
    if char == "{" {
      if idx + 1 < len(template_chars) && template_chars[idx + 1] == "{" {
        out = out + "{"
        idx = idx + 2
        continue
      }
      let end = __find_placeholder_end(template_chars, idx + 1)
      if end < 0 {
        throw "std/postgres/query: unclosed `{` in SQL template"
      }
      let key = trim(join(template_chars[idx + 1:end], ""))
      if key == "" {
        throw "std/postgres/query: empty SQL template placeholder"
      }
      if regex_match(__IDENTIFIER_PATTERN, key) == nil {
        throw "std/postgres/query: invalid SQL template placeholder `" + key + "`"
      }
      if !contains(values.keys(), key) {
        throw "std/postgres/query: missing SQL template value `" + key + "`"
      }
      let rendered = __render_placeholder(key, values[key], params, positions)
      out = out + rendered.sql
      params = rendered.params
      positions = rendered.positions
      idx = end + 1
      continue
    }
    if char == "}" {
      if idx + 1 < len(template_chars) && template_chars[idx + 1] == "}" {
        out = out + "}"
        idx = idx + 2
        continue
      }
      throw "std/postgres/query: unmatched `}` in SQL template"
    }
    out = out + char
    idx = idx + 1
  }
  return {sql: out, params: params}
}

/**
 * Build a serializable named-query record.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: named("list_receipts", "many", sql, [tenant_id])
 */
pub fn named(name: string, mode: PgQueryMode, sql: string, params: list<any> = []) -> PgNamedQuery {
  return __normalize_query({name: name, mode: mode, sql: sql, params: params})
}

/**
 * Build a parameterized query record from a SQL template.
 *
 * `{name}` placeholders become `$1`, `$2`, ... bind parameters. Repeated
 * placeholders reuse the first parameter index. Use `{{` and `}}` for literal
 * braces, and pass `ident(...)`, `ident_path(...)`, or `unsafe_sql(...)` only
 * for source-controlled SQL structure.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: sql("SELECT * FROM receipts WHERE tenant_id = {tenant_id}", {tenant_id: tenant_id})
 */
pub fn sql(template: string, values: dict = {}, options = nil) -> PgQuery {
  let rendered = __render_sql_template(template, values)
  return __normalize_query({sql: rendered.sql, params: rendered.params, options: options})
}

/**
 * Build a named parameterized query record from a SQL template.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: named_sql("list_receipts", "many", "SELECT * FROM receipts WHERE tenant_id = {tenant_id}", {tenant_id: tenant_id})
 */
pub fn named_sql(
  name: string,
  mode: PgQueryMode,
  template: string,
  values: dict = {},
  options = nil,
) -> PgNamedQuery {
  let rendered = __render_sql_template(template, values)
  return __normalize_query(
    {name: name, mode: mode, sql: rendered.sql, params: rendered.params, options: options},
  )
}

/**
 * Run a query that returns zero or one row.
 *
 * @effects: [postgres]
 * @allocation: heap
 * @errors: validation, postgres
 * @api_stability: experimental
 * @example: one(db, {name: "get_receipt", sql: sql, params: [id]})
 */
pub fn one(handle, query: PgQuery) {
  let q = __normalize_query(query, "one")
  return __with_query_name(q, { -> pg_query_one(handle, q.sql, q.params, q.options) })
}

/**
 * Run a query that returns a list of rows.
 *
 * @effects: [postgres]
 * @allocation: heap
 * @errors: validation, postgres
 * @api_stability: experimental
 * @example: many(db, {name: "list_receipts", sql: sql, params: [tenant_id]})
 */
pub fn many(handle, query: PgQuery) -> list {
  let q = __normalize_query(query, "many")
  return __with_query_name(q, { -> pg_query(handle, q.sql, q.params, q.options) })
}

/**
 * Execute a statement and return the underlying PgExecuteResult.
 *
 * @effects: [postgres]
 * @allocation: heap
 * @errors: validation, postgres
 * @api_stability: experimental
 * @example: exec(db, {name: "mark_read", sql: sql, params: [id, status]})
 */
pub fn exec(handle, query: PgQuery) -> PgExecuteResult {
  let q = __normalize_query(query, "exec")
  if q.options != nil {
    throw __query_error_prefix(q) + "options are only supported by one and many"
  }
  return __with_query_name(q, { -> pg_execute(handle, q.sql, q.params) })
}

/**
 * Run a named query according to its `mode`.
 *
 * @effects: [postgres]
 * @allocation: heap
 * @errors: validation, postgres
 * @api_stability: experimental
 * @example: run(db, named("list_receipts", "many", sql, [tenant_id]))
 */
pub fn run(handle, query: PgNamedQuery) {
  let q = __normalize_query(query)
  if q.mode == nil {
    throw __query_error_prefix(q) + "mode is required"
  }
  if q.mode == "one" {
    return one(handle, q)
  }
  if q.mode == "many" {
    return many(handle, q)
  }
  return exec(handle, q)
}

/**
 * Validate and return a static SQL identifier for projection helpers.
 *
 * @effects: []
 * @allocation: stack-only
 * @errors: validation
 * @api_stability: experimental
 * @example: identifier("created_at")
 */
pub fn identifier(name: string) -> string {
  if type_of(name) != "string" || trim(name) == "" {
    throw "std/postgres/query: SQL identifier must be a non-empty string"
  }
  if regex_match(__IDENTIFIER_PATTERN, name) == nil {
    throw "std/postgres/query: unsafe SQL identifier `" + to_string(name) + "`"
  }
  return name
}

/**
 * Quote one SQL identifier part with PostgreSQL double-quote escaping.
 *
 * Dynamic values still belong in template placeholders; quoted identifiers are
 * only for SQL structure such as table or column names.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: quote_identifier("receipt events")
 */
pub fn quote_identifier(name: string) -> string {
  if type_of(name) != "string" || trim(name) == "" {
    throw "std/postgres/query: SQL identifier must be a non-empty string"
  }
  return "\"" + replace(name, "\"", "\"\"") + "\""
}

/**
 * Render a quoted SQL identifier fragment for use inside `sql(...)`.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: sql("SELECT {column} FROM receipts", {column: ident("created_at")})
 */
pub fn ident(name: string) -> PgSqlFragment {
  return __sql_fragment(quote_identifier(name))
}

/**
 * Render a dotted quoted SQL identifier path for use inside `sql(...)`.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: ident_path(["app", "receipts"])
 */
pub fn ident_path(parts: list<string>) -> PgSqlFragment {
  if type_of(parts) != "list" || len(parts) == 0 {
    throw "std/postgres/query: SQL identifier path must be a non-empty list"
  }
  var quoted = []
  for part in parts {
    quoted = quoted.push(quote_identifier(part))
  }
  return __sql_fragment(join(quoted, "."))
}

/**
 * Mark a source-controlled SQL fragment for insertion into `sql(...)`.
 *
 * This bypasses parameter binding and must never wrap user input. Prefer
 * normal placeholders for values and `ident(...)` / `ident_path(...)` for
 * identifiers.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: unsafe_sql("COUNT(*)")
 */
pub fn unsafe_sql(fragment: string) -> PgSqlFragment {
  return __sql_fragment(fragment)
}

/**
 * Render `SELECT a, b, c` from already-static projection fragments.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: select_clause([uuid_text("id"), "payload"])
 */
pub fn select_clause(fragments: list<string>) -> string {
  if type_of(fragments) != "list" {
    throw "std/postgres/query: select_clause requires a list of fragments"
  }
  if len(fragments) == 0 {
    throw "std/postgres/query: select_clause requires at least one fragment"
  }
  return "SELECT " + join(fragments, ", ")
}

/**
 * Render `column::text AS column` for UUID columns.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: uuid_text("id")
 */
pub fn uuid_text(name: string) -> string {
  let ident = identifier(name)
  return ident + "::text AS " + ident
}

/**
 * Render `to_json(column)#>>'{}' AS column` for timestamp columns.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: timestamptz_json("created_at")
 */
pub fn timestamptz_json(name: string) -> string {
  let ident = identifier(name)
  return "to_json(" + ident + ")#>>'{}' AS " + ident
}

/**
 * Render a nullable timestamp projection that preserves nulls as null.
 *
 * @effects: []
 * @allocation: heap
 * @errors: validation
 * @api_stability: experimental
 * @example: nullable_timestamptz_json("finished_at")
 */
pub fn nullable_timestamptz_json(name: string) -> string {
  let ident = identifier(name)
  return "CASE WHEN "
    + ident
    + " IS NULL THEN NULL ELSE to_json("
    + ident
    + ")#>>'{}' END AS "
    + ident
}