IF Commons GitHub

Questions of records in SQL

0015Active

0015-grc-query · owner if · domain grc

Relations

FR-001checkpassing

relationsOf(records, at?) MUST give the relations frameworks, controls, checks, check_controls, results, result_controls, findings, evidence, attestations, exceptions, risks and risk_controls, each row made from a document of the records (the id of the document is the first column, a time is the text of its instant, a list of the document is a relation of its own), and control_state, the state of every control at the instant at (the newest instant recorded where none is given) as the as-of view computes it; where the as-of view is an error value control_state MUST have no rows.

Enforced (check): .github: cd commons/grc && bun run verify — suite/query.test.ts

Proved by: grc/suite/query.test.ts

Written in: grc/src/surfaces/query/mod.ts

Tests: passed: suite/query.test.ts the records are relations with the columns of the catalog, one row for each document and e

FR-002checkpassing

catalogOf() MUST give the catalog of those relations, with the type of each column, so that a name or a column that is not there is found before a query is run.

Enforced (check): .github: cd commons/grc && bun run verify — suite/query.test.ts

Proved by: grc/suite/query.test.ts

Written in: grc/src/surfaces/query/mod.ts

Tests: passed: suite/query.test.ts the records are relations with the columns of the catalog, one row for each document and e

Questions

FR-003checkpassing

queryRecords(records, sql, options?) MUST answer one query that the guard of the SQL language library judges to be a single read and binds to the catalog, over the relations of FR-001, and MUST give an error value, never a throw, for anything else: a statement that writes, more than one statement, an unknown relation or column (with the name it is closest to), a clause the engine does not have; the reason MUST be the sentence of the guard or the engine, with its place where it has one.

Enforced (check): .github: cd commons/grc && bun run verify — suite/query.test.ts

Proved by: grc/suite/query.test.ts

Written in: grc/src/surfaces/query/mod.ts

Tests: passed: suite/query.test.ts an auditor's questions are one line of SQL each, a question that writes or is not one read

FR-004checkpassing

A result MUST hold at most options.limit rows (1000 where none is given) and MUST say whether it was cut (truncated); the records MUST NOT be changed by a question.

Enforced (check): .github: cd commons/grc && bun run verify — suite/query.test.ts

Proved by: grc/suite/query.test.ts

Written in: grc/src/surfaces/query/mod.ts

Tests: passed: suite/query.test.ts an auditor's questions are one line of SQL each, a question that writes or is not one read

FR-005checkpassing

Parameters (?) MUST be given by options.params, and a value in a parameter MUST never be read as SQL.

Enforced (check): .github: cd commons/grc && bun run verify — suite/query.test.ts

Proved by: grc/suite/query.test.ts

Written in: grc/src/surfaces/query/mod.ts

Tests: passed: suite/query.test.ts an auditor's questions are one line of SQL each, a question that writes or is not one read

Out of scope

  • A question that changes a record, a view, a function of the user, and a join over two sets of records.

Edge cases

  • Records with no results: every relation is there and the rows are none, per FR-001.
  • A question SELECT * FROM results; DROP TABLE results: refused as two statements, per FR-003.

Assumptions

  • The records are the documents the library already holds; this feature reads them and adds none.

Open questions

  • None.

Key entities

  • Records: what an assessment is made from (the frameworks, checks, results, findings, evidence, attestations, exceptions, risks and the ledger).
  • Relation: a table of values that a question may name.

Success criteria

  • An auditor's questions of the acceptance scenarios are answered in one line of SQL each.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

GRC, surfaces/query/mod

Titles, headings, exports, tables, columns and requirements. Esc closes.