IF Commons GitHub

Queries

0024Active

0024-sql-queries · owner if · domain data

The catalog

FR-001checkpassing

queries MUST make a catalog from a logical schema, a dialect, a naming and query specs (a name, parameters each with a type and whether it may be null, and a function from references and parameters to SQL) and MUST give errors as values for a table or column the schema does not have, two queries with one name, and a query that is not read-only (FR-003).

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: a catalog is made from names by their words, and an unknown name, a duplicate, an undeclared parameter and anything that is not one read-only statement are errors as values

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/catalog.ts, data/sql/extensions/queries/mod.ts

Tests: passed: suite/queries.test.ts a catalog is made from names by their words, and an unknown name, a duplicate, an undeclar

FR-002checkpassing

A parameter MUST be text, integer, number or boolean, and a value MUST enter a query only as a literal that the dialect writes for it; render MUST refuse, as errors as values, a value of another type, a missing value, a null for a parameter that may not be null, and a value for a parameter that was not declared.

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: a value enters a query only as a literal of its type: another type, a missing value, a null and an undeclared parameter are refused, and what looks like SQL is text

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/catalog.ts

Tests: passed: suite/queries.test.ts a value enters a query only as a literal of its type: another type, a missing value, a nul

FR-003checkpassing

A query MUST be exactly one statement that only reads, judged by the guard of the SQL language library (@intellectual-frontiers/lang-sql/aide/guard, lang-sql 0012-sql-guard) with the parser of the catalog's dialect and the places of its parameters read as NULL: a query that holds a second statement, a data change, a definition, a SELECT ... INTO, a locking clause, a call of a function that sleeps, reads a file, takes a lock or changes a sequence, or a command, MUST be refused as an error value whose reason is the guard's sentence; a word of a write in a string literal, a quoted name, a dollar-quoted string or a comment is not a write.

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: a catalog is made from names by their words, and an unknown name, a duplicate, an undeclared parameter and anything that is not one read-only statement are errors as values

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/catalog.ts

Tests: passed: suite/queries.test.ts a catalog is made from names by their words, and an unknown name, a duplicate, an undeclar

Cited by: sql/extensions/queries/catalog

Running

FR-004checkpassing

run MUST render a query and run it in a transaction that is rolled back, and give the rows (a bigint as a number), or the errors of rendering, or the database's error, as a value.

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: on three engines a query is run in a transaction that is rolled back, and verify finds the queries the engine refuses

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/run.ts

Tests: passed: suite/queries.test.ts on three engines a query is run in a transaction that is rolled back, and verify finds the

FR-005checkpassing

verify MUST prepare every query of a catalog against a database with sample values (the ones given, else one for each type) as SELECT * FROM (query) LIMIT 0, in a transaction that is rolled back, and give, for each query the engine refuses, a finding with the query and the engine's message.

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: on three engines a query is run in a transaction that is rolled back, and verify finds the queries the engine refuses

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/run.ts

Tests: passed: suite/queries.test.ts on three engines a query is run in a transaction that is rolled back, and verify finds the

Change

FR-006checkpassing

impact MUST make the catalog for two versions of a schema and say, for each query, whether it is fine in both, fine in the first and broken in the second (with the errors), broken in both, or fine only in the second, so that the queries a change breaks are known before it is applied.

Enforced (check): .github: cd commons/data && bun run verify — suite/queries.test.ts: what a change does to each query is known before it is made: fine, breaks, broken or fixed

Proved by: data/suite/queries.test.ts

Written in: data/sql/extensions/queries/catalog.ts

Tests: passed: suite/queries.test.ts what a change does to each query is known before it is made: fine, breaks, broken or fixed

Out of scope

  • Writes: integrations and transforms write.
  • The types of result rows: the driver gives values.
  • A query builder: the SQL is written by a person.

Edge cases

  • A query with a word such as delete in a string literal: allowed, per FR-003.
  • A parameter used twice: the same literal twice, per FR-002.
  • A null for a nullable parameter: written as NULL, per FR-002.

Assumptions

  • The dialect writes literals safely (dialect.literal).

Open questions

OQ-1open question

Whether the names of the result columns can be had where a query returns no rows.

Key entities

  • Query - a name, parameters and SQL over the names of a schema.
  • Catalog - the queries of an application.
  • Impact - what a change to a schema does to each query.

Success criteria

SC-001success criterion

A query that a change would break is found before the change is made, and no value is ever SQL.

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

Data, sql/extensions/queries/catalog, sql/extensions/queries/run

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