[0.1 draft] [guide 15 · spec 15 pages]

spec/columns

Columns (column)

txtab

P column name=id type=int unique=1
P column name=email type=string required=1 len-max=254 example=ann@example.com
P column name=amount type=decimal format=0.00 unit=USD
P column name=customer_id type=long references=customers.id

column lines are optional and live in a table block. A table without them is valid (no schema); partial coverage is valid too — columns without a column line have no schema. Each described column has one P column line.

Binding to data

header=1 header=0
name MUST (column-missing-name, FAIL) MAY
index not used MUST (column-missing-index, FAIL)

header=1:

  • The table's data MUST start with a header row.
  • A stored or materialized column binds by name to the header cell equal to its title if present, otherwise to its name. The position of the column line carries no meaning; parsers MUST NOT bind by line order.
  • A header cell no column binds to has no schema.
  • A column that matches no header cell describes no data and is reported: column-title-header-mismatch or column-name-header-mismatch (WARN).
  • If several header cells hold the same text, the column binds to the first.
  • Virtual computed columns bind to no cell (Computed columns).

header=0: each column MUST have index (zero-based position); name is optional.

Two column lines for the same column: last wins (column-duplicate, WARN).

For storage=columns the binding is by file: <index>-<name>.col (archive.md).

Attributes

Identity

Attribute Meaning
name Column identifier. MUST match [A-Za-z_][A-Za-z0-9_]* (column-name-invalid, FAIL). Used in references, formulas, link templates, charts, .col file names and generated DDL.
title Display name: any text, quoted if it contains spaces. With header=1 it is what the header cell says (e.g. title="Сумма (руб.)").
description Free-text description (quoted)
index Zero-based position; MUST when header=0

Type and format

Attribute Meaning
type Data type, see below. SHOULD be set.
format Display/parse hint, e.g. format=0.00
example A made-up example value, in the column's type
type Values
string Text in the file's encoding
int 32-bit signed integer
long 64-bit signed integer
float 32-bit IEEE 754
double 64-bit IEEE 754
decimal Arbitrary-precision decimal
boolean true, false, 1, 0
date ISO 8601 date YYYY-MM-DD
time ISO 8601 time HH:MM:SS
datetime ISO 8601 date-time
uuid Textual UUID
binary Base64

example= MUST be made up. Tools MUST NOT copy values from the table's data into example=: metadata travels further than data (archive comments, previews, prompts) and would leak it.

Units and money (unit)

Form Meaning
unit=kg, unit=ms, unit=% Unit of measurement
unit=USD Money: an ISO 4217 currency code
unit=EUR/kg Per-unit: currency or unit per unit
unit=@cur Per row: the unit is the value of column cur in the same row
  • A unit is money when it — or the numerator of a fraction — is an ISO 4217 code: exactly three uppercase ASCII letters from the ISO 4217 list. Matching is case-sensitive (CUP is the Cuban peso, cup a cup).
  • @<column> MUST name a declared column of the same table (unit-unknown-column, FAIL).
  • Consumers MUST NOT sum, average or otherwise combine money values in different currencies as one number; with unit=@<column> they group by the unit column first.
  • unit= is descriptive: values are never converted.

Null, default, required

Attribute Meaning
required 1 — must not be null; 0 — nullable
default Schema/DDL default. Not applied on read.

An empty field, or one equal to the table's null marker, reads as null regardless of default; parsers MUST NOT substitute default. In generated DDL default becomes DEFAULT <value> (with required=1: NOT NULL DEFAULT <value>). A column with default= whose data contains nulls SHOULD be reported (default-with-nulls, WARN, never escalated).

Constraints

Attribute Meaning
min, max Value bounds (numeric or date)
len-min, len-max String length bounds
enum Allowed non-null values, |-separated (Enumerations)
pattern Regex the values match
regexp-dialect Dialect of pattern: ecmascript (default), pcre, posix-ere, re2
unique 1 — values are meant to be unique (a hint, not enforced)

Relations (references)

txtab

P table name=orders rows=2
P column name=customer_id type=long references=customers.id
  • references=<table>.<column> declares that the column refers to a column of another table in the same file. The value splits on the first ..
  • The target table MUST have a name=; it MAY come later in the file. References are resolved after the whole file is read.
  • Informational: values are not checked against the target. A target table or column that does not exist is references-unresolved (WARN).
  • Only single-column references. Composite keys are written as sql-post-data (sql.md).

Semantics

Attribute Meaning
role Analytical role: id, dimension, measure, time
agg Aggregation hint for role=measure: sum, avg, min, max, none
order Whether the data is sorted by this column: none, asc, desc
separator Sub-value separator inside one value (multi-value cells)
link URL template that makes each value a link (notes.md)
formula, materialized Computed column (below)

Unknown attributes MUST be ignored (column-unknown-attribute, WARN) and preserved on round-trip; custom attributes SHOULD start with x-. A malformed attribute is column-malformed-attribute (FAIL).

Enumerations

txtab

P column name=status type=string enum=pending|completed|cancelled
  • Values are read in the column's type (type=int enum=1|2|3).
  • enum constrains non-null values; null is additionally allowed unless required=1.
  • Quoting follows the tokenizer: enum="pending|in progress|done". Values MUST NOT contain |.
  • With separator, enum applies to each sub-value. With pattern, a value satisfies both.

Analytical role

role describes what a column is for in analysis, independently of type. Advisory, not validated.

role Meaning Typical operations
id Identifies a row or entity; not for arithmetic count, distinct, join
dimension Category group by, filter, count distinct
measure Numeric fact sum, avg, min, max
time Time axis group by period, range, trend

agg declares a measure's additivity; a measure without agg defaults to sum; agg on a non-measure is ignored.

agg Additivity Example
sum additive revenue, quantity
avg semi-additive: not summed across time balance, price, temperature
min / max by extremum only high/low quotes
none non-additive ratio, percentage, rating

A single column with role=id (or, if none, a single column with unique=1) is the table's id column, used by key= in note and link (notes.md).

Computed columns (formula)

txtab

P column name=total type=decimal formula="price * quantity"
P column name=full_name type=string formula="concat(first_name, ' ', last_name)"
Attribute Meaning
formula Defines the column as computed from other columns of the same row
materialized 1 — values are also stored as real data; absent or 0 — virtual (default)
Virtual Materialized
Values in the data none an ordinary field in every record (or a .col file)
Position none: no header cell, no field wherever the materialize operation put it
formula= kept kept
  • A computed column MUST NOT carry index= (formula-index-forbidden, FAIL) and is addressed by name only, so formula= requires header=1 (formula-requires-header, FAIL).
  • materialized=1 without matching data, or data present while materialized is absent/0: computed-materialized-mismatch (FAIL).
  • default= or required= on a computed column is ignored (computed-default-ignored, WARN).
  • A materialized value that may no longer match its formula SHOULD be reported (computed-stale, WARN, never escalated).

Formula language

One portable expression language, evaluated by tools — no SQL dialect:

  • Operands: stored column names, number literals, 'string' literals, true, false, null.
  • Operators: + - * / %, unary -, = <> < <= > >=, and or not, parentheses. No || — use concat(...).
  • Functions: abs round floor ceil coalesce nullif least greatest length lower upper trim substr concat.
  • case when … then … [else …] end.

Anything outside this grammar is not a formula=; write it as a vendor-specific generated column in sql-pre-data.

Dependencies

A formula MUST reference only stored columns of the same table — never another computed column.

Condition Code Severity
References another computed column formula-references-computed FAIL
References an unknown column formula-unknown-reference FAIL
Does not parse formula-parse-error FAIL

Arity

A virtual computed column has no value slot: it is excluded from record arity, from agg-* arity, from columns= and from .col files. A materialized one counts like any stored column everywhere.

Materialize and dematerialize

Explicit tool operations (txtab column materialize <name>, txtab column dematerialize <name>), never done by a reader:

  • Inline table — rewritten in place (or to a new file): add (remove) the field in every record and the header cell, set (clear) materialized=1, adjust columns= if declared, recompute or drop agg-* for that column. rows= is unchanged.
  • reference= table (rows) — the referenced file MUST NOT be modified. Materializing produces a new file in which the table is inline (no reference=) and carries the new column.
  • storage=columns table — add (remove) the column's .col file(s), set (clear) materialized=1, adjust columns=.

sql-pre-data / sql-post-data lines are never edited by these operations; keeping existing DDL in sync is the author's job. A validator MAY report ddl-column-mismatch (WARN) when it can tell they disagree.

spec/columns.md on GitHub