Conditional SQL

Conditional SQL

A route's sql can be a list of rules that picks the base query from the request's parameters. Use it when one URL needs different queries, for example ?count=true returning a histogram instead of rows. The chosen query then gets the route's query params exactly as a plain sql: string would.

Example: histogram or rows

routes:
  - route: detections
    sql:
      - when: count                     # ?count=<truthy>: species histogram
        then:
          sql: >
            SELECT detections.common_name, detections.scientific_name,
                   COUNT(*) AS count
            FROM [[detections]]
          sql_append: GROUP BY detections.common_name, detections.scientific_name
      - else: SELECT [[detections]].* FROM [[detections]]   # otherwise: rows
    query_params:
      - recording:
          sql: "[[detections]].recording_id = {{recording}}"
          multivalue_sql: "[[detections]].recording_id IN {{recording}}"
      - limit:
          sql_append: LIMIT {{limit}}   # applies to both queries

GET /db/detections?recording=1&count=true&limit=10
# SELECT detections.common_name, detections.scientific_name, COUNT(*) AS count
#   FROM '...' AS detections WHERE detections.recording_id = '1'
#   GROUP BY detections.common_name, detections.scientific_name LIMIT 10

GET /db/detections?recording=1
# SELECT detections.* FROM '...' AS detections WHERE detections.recording_id = '1'

The query param filter lands in the WHERE clause of whichever query is chosen, and the branch's GROUP BY comes before the route's LIMIT. Neither sql_append alone (it only adds trailing clauses) nor a second route (the path is the same) can do this.

How rules are evaluated

Rules are checked top to bottom and the first match wins. Each rule's result (an SQL node) is one of:

• a SQL string
• a leaf {sql, sql_append}: base SQL plus clauses added after the WHERE clause (sql_append may be a string or a list)
• another rule list (nesting)

A rule can look at path variables, query string values, and cookies (as cookies.<name>, for cookies allowed by the database's cookies setting). Query param defaults are not seen by rules.

Rule forms

sql:
  - when: count                 # fires when `count` is truthy (same as equals: _truthy)
    then: <node>

  - when: mode
    equals: 'true'              # fires when mode == "true" (case-insensitive)
    then: <node>

  - when: format                # value map: a node per value
    match:
      species: <node>           # ?format=species
      recording: <node>         # ?format=recording
      _truthy: <node>           # any other truthy value
      _default: <node>          # any other present value

  - when: [count, recording]    # list: fires when ALL are truthy
    then: <node>

  - when: [count, recording]    # list + positional equals
    equals: [_truthy, _absent]
    then: <node>

  - when: [count, mode]         # list + positional case list
    match:
      - values: [_any, x]       # mode == x, count anything
        then: <node>
      - values: [_truthy, _absent]
        then: <node>
      - default: <node>

  - else: <node>                # default; a trailing bare string works too

Form	Fires when
when: p + then	p is truthy
when: p + equals: <spec> + then	p matches the spec
when: p + match: {spec: node, ...}	some key matches (see precedence below)
when: [p, q] + then	every listed param is truthy
when: [p, q] + equals: [spec, spec] + then	each param matches the spec in its position
when: [p, q] + match: [cases]	the first case whose values all match, or a default case
else: <node>, or a bare string	always (use it last)
no_match: <response>	always: returns an error (see below)

Value specs

Spec	Matches when the param...
a literal ('4', 'true', species)	is present and equals it, ignoring case and surrounding spaces
_truthy	is present and not one of "", 0, false, no, off, null, none (case-insensitive)
_falsy	is present and one of those values
_present / _absent	is / isn't in the request
_any	always (a wildcard for one position in a list)
_default	in a value map: any present value not matched by another key

So ?count=true, ?count=1 and ?count=yes are truthy; ?count=0, ?count=false and ?count= are not.

In a single-param match map, key order doesn't matter. The precedence is: exact literal, then _truthy/_falsy, then _present, then _default. An absent param matches only _absent; if the map has no _absent, the rule doesn't fire and evaluation moves to the next rule. In a positional case list, cases are tried strictly top to bottom.

YAML reads unquoted true, 4 and similar keys as booleans and numbers; they still match here because values are compared as lowercase text, but quoting them ('true', '4') is clearer.

Nesting

Any node can itself be a rule list:

sql:
  - when: site
    match:
      '4':
        - when: count
          then: <sql for site 4 with count>
        - else: <sql for site 4>
      _default: <sql for other sites>
  - else: <base sql>

Once a rule fires, its node is final: if a nested list matches nothing and has no default, the request gets the no-match error. Evaluation does not go back to the outer list.

No match

If no rule matches and there is no default (else, a trailing bare string, or a _default/default catch-all), the request returns:

400 {"error": "No matching query configuration for the given parameters", "http_status": 400}

Put a no_match rule last to customize it. Its value is the response body; http_status (default 400) sets the status. A plain string becomes {"error": "<string>", "http_status": 400}.

sql:
  - when: recording
    then: SELECT [[detections]].* FROM [[detections]] WHERE recording_id = {{recording}}
  - no_match:
      error: recording is required, or pass count=true for a histogram
      http_status: 400

Working with query params

The chosen node is a normal base query:

1. Query params that return early (response, conditional responses, required) are checked first.
2. The rules choose the base SQL and any branch sql_append.
3. Query param sql fragments join the base query's top-level WHERE (before a trailing GROUP BY/ORDER BY in it; see Query Parameters). Keeping GROUP BY/ORDER BY in the branch's sql_append still reads most clearly.
4. The branch's sql_append clauses come next, then the query params' sql_append clauses.

Branch sql is checked like any route SQL: {{variables}} are bound values, and a branch can be a "[[named_query]]". Branch sql_append follows the same rules as query param sql_append: values written into it must pass the allowed-character check (see How values reach the database). Startup checks cover every branch.