SELSimple Expression Language
GitHub

SQL pipelines

SQL conditions push a rule into a WHERE clause. This page goes further: a whole data-processing pipeline — filter, join, group, sort, project — written once in SEL, answered by the database where it can be and in memory where it cannot, with the same rows either way.

Relations and pipelines

In memory, a table is a list of records in the context, and a pipeline is a chain of ordinary functions over it:

sel
ORDERS = LIST(RECORD("id", 1, "status", "paid", "total", 120.00), RECORD("id", 2, "status", "open", "total", 80.00), RECORD("id", 3, "status", "paid", "total", 45.50)); ORDERS .> FILTER(_["status"] $== "paid") .> SORT_BY(_["total"]) .> MAP(_["id"]) .> JOIN(",")  => 3,1

For the database, the application describes each table once as a relation binding — its name, an alias, and a typed binding per field — and the very same program becomes a statement. translate_statement renders a pipeline over relations as one SELECT: FILTER is a WHERE, LINK an INNER JOIN, BUCKET a GROUP BY (with a FILTER after it a HAVING), MAP the select list, the sorts an ORDER BY, TAKE and DROP a LIMIT and OFFSET.

Three kinds of plan

Not every pipeline can be SQL. plan_hybrid looks at the whole pipeline and answers how much of it the database can take:

Plan The database runs Memory runs
pure_sql the whole pipeline, as one statement nothing
hybrid the longest prefix it can answer exactly the rest, over the rows the prefix returned
pure_memory nothing the planner can use — at most, the application loads the tables the whole pipeline

A plan also names the physical tables it reads (source_tables), so an application can pick a connection or check a grant before running anything. Planning needs the bindings and a dialect name, never a connection.

Running a plan

execute_hybrid(plan, runner, context) runs a plan of any kind. The runner is the application's: a function that takes a statement with ? placeholders and the values for them, and returns the rows as a SEL list of records. For a hybrid plan the rows are handed to the in-memory remainder as _INPUT; for a pure-memory plan nothing reaches the runner, and the program runs over context — which must then hold the tables it reads.

Each host's examples share one small runner, over PostgreSQL, MariaDB and SQLite, with every column coming back as text — SEL's numbers are text, so a NUMERIC(10,2) arrives as "12.50" with its scale, and a floating-point column is refused:

python
def connect(dialect):
    if dialect == 'sqlite':
        return sqlite3.connect(ENV('SEL_DB_SQLITE_FILE'))
    common = dict(host=ENV('SEL_DB_HOST', '127.0.0.1'), user=ENV('SEL_DB_USER'),
                  password=ENV('SEL_DB_PASSWORD'))
    if dialect == 'postgresql':
        import psycopg
        return psycopg.connect(port=int(ENV('SEL_DB_POSTGRESQL_PORT')),
                               dbname=ENV('SEL_DB_NAME'), autocommit=True, **common)
    if dialect == 'mariadb':
        import pymysql
        return pymysql.connect(port=int(ENV('SEL_DB_MARIADB_PORT')),
                               database=ENV('SEL_DB_NAME'), autocommit=True, **common)
    raise ValueError(f'no runner for {dialect}')


def placeholders(sql, backslash_escapes):
    out, quote, i = [], None, 0
    while i < len(sql):
        c = sql[i]
        if quote:
            if c == '\\' and quote == "'" and backslash_escapes:
                out.append(sql[i:i + 2])
                i += 2
                continue
            if c == quote:
                quote = None
        elif c in "'\"`":
            quote = c
        elif c == '?':
            c = '%s'
        out.append('%%' if c == '%' else c)
        i += 1
    return ''.join(out)


def query(conn, sql, params=()):
    if not isinstance(conn, sqlite3.Connection):
        sql = placeholders(sql, backslash_escapes=not hasattr(conn, 'pgconn'))
    cur = conn.cursor()
    cur.execute(sql, [None if p.is_null() else p.as_text() for p in params])
    names = [d[0] for d in cur.description]
    rows = Value.none()
    for n, row in enumerate(cur.fetchall(), 1):
        record = Value.none()
        for name, cell in zip(names, row):
            record.set(name, Value.null() if cell is None else Value.text(_text(cell)))
        rows.set(str(n), record)
    return rows


def _text(cell):
    if isinstance(cell, float):
        raise TypeError('a float reached SEL; declare the column DECIMAL or TEXT')
    return format(cell, 'f') if isinstance(cell, Decimal) else str(cell)


def runner(conn):
    return lambda sql, params: query(conn, sql, params)

Two things differ by host, and the runners show both:

  • Placeholders. SEL emits ?. PDO, SQLite and the Node MariaDB driver take ? as it is; libpq, node-postgres and postmodern want $1, $2, …; psycopg and PyMySQL want %s; libmariadb's text protocol and cl-mysql take no parameters at all, so the values are escaped into the statement the way PDO's emulated prepares do it. Each runner finds the placeholders with a scan that skips quoted text, because a translated statement can contain a ? inside a literal — the numeric guard's regular expression has one.
  • Synchronous runners. execute_hybrid calls the runner synchronously in every host. Node's database drivers are asynchronous, so the JavaScript runner runs the plan's one statement first and hands executeHybrid a function that returns the rows it already has.

Where the planner splits, and why

The planner pushes a step down only when the database's answer is exactly SEL's. Most of what keeps a step in memory is one of these:

  • No SQL spelling. SPLIT and RGROUPS yield lists; CRC32 has no PostgreSQL spelling; a regex has none on SQLite; a host function has none until the application gives it one. The split lands before the step.
  • Rows SQL does not have. After a LINK, SEL's row holds each side under its binder as well as the promoted fields; SQL's row has only the fields. So a joined row is only a split point once a MAP (or SELECT_COLS, or a projected BUCKET) has said which fields to keep — project before the split.
  • Groups SQL cannot see into. Inside a BUCKET projection SQL can count and sum the group, nothing else; a projection that sorts, filters or picks within the group keeps the bucket in memory, over rows the database filtered and joined.
  • Identity. Grouping and de-duplication compare values by SEL's identity — exact bytes, and 1 differs from 1.0. A database compares by its collation and its numeric equality. When a value computed in SQL could be merged or split differently, the planner keeps the computation in memory (CANON and text-literal IF branches are the ways a rule proves identity).
  • Helper variables. A pipeline may be written through helper assignments (X = ORDERS; X .> TAKE(1)); literal helpers are inlined, others are evaluated once, in memory, before the steps that read them.

A pure-memory plan still reports the tables it reads, and an application that wants the database's data but not its opinion loads them — which is what the in-memory example does.

The example data

Six small datasets — one per shape of schema, and one for the application's own functions — each in the database the brief asks of it and each with its own page. The seeds are in the example directories; tools/check-usage.sh loads them into throwaway servers and runs every example in every host against them.

Page Schema Database What it shows
Star schema a sales fact table and date, store and product dimensions PostgreSQL joins to dimensions and a GROUP BY in one statement; a per-group "best of" computed in memory over joined rows
Entity–attribute–value entities and (entity, name, value) rows, every value text SQLite EXISTS subqueries per attribute; counting by value in SQL; pivoting and numeric tests on text in memory
Third normal form categories, products, customers, orders, order lines PostgreSQL a three-table join and aggregate in SQL; an application-defined hash (CRC32 cohorts) in memory
Unnormalised one wide export table with repeated customers and tags in one column MariaDB grouping in SQL; normalisation and de-duplication kept out of MariaDB's collation; splitting a list column in memory
Your own functions a shop with VAT rates, and the application's functions spelled as PostgreSQL stored functions PostgreSQL a list argument, SQL and PL/pgSQL functions inside joins and aggregates, a list-returning function kept in memory, strict mode
Complex, in memory a support desk: teams, customers, SLAs, tickets, events PostgreSQL, then no database at all a report no database can take a share of: SQL loads, SEL computes — and the same report over generated data

Every example prints, for each pipeline, the plan, the tables it reads, the SQL, the rows — and whether those rows are the rows the same program computes in memory over the same tables. They always are; that line is how the examples check themselves.

Limits worth knowing

The planner refuses rather than guesses, so each of these is a pipeline that runs correctly in memory, not a wrong answer — but knowing them helps a pipeline stay in SQL:

  • A LINK's left key must be a field of the pipeline's first relation: chain joins from a hub table (LINES .> LINK(ORDERS …) .> LINK(PRODUCTS …)), not along a path (… .> LINK(CATEGORIES, X, K, X["category_id"] …) where category_id came from the second relation).
  • A group key of several fields (BUCKET(RECORD("a", …, "b", …), proj)) groups in SQL, but its parts cannot be projected from _K; group by one field, or project the parts in memory.
  • A sort by the text group key directly after a BUCKET over a LINK is refused; sort by an aggregate, or let that last sort run in memory.

The binding constructors, the dialects, the output modes and every refusal code are in the SQL reference; the planner's full contract is its design document.

View this page's Markdown on GitHub