write
Insert or replace database records and define SQLite DDL objects—tables, indexes, triggers, views—using JMD text documents.
Instructions
Write a record or define a DDL object (https://github.com/ostermeyer/jmd-spec).
Data (# Label): INSERT OR REPLACE one record.
# Order
id: 42
status: shipped
total: 149.99Bulk insert (# Label[]):
# Order[]
- id: 42
status: shipped
- id: 43
status: pendingSchema (#! Label): full SQLite DDL surface.
Per-column types: integer, float, string, boolean (int in SQLite). Per-column modifiers: readonly primary key (single-col); do not supply on insert optional NULL allowed = DEFAULT value (number, string, CURRENT_TIMESTAMP) a|b|c enum form — generates a CHECK (col IN (…))
#! Order
id: integer readonly
status: pending|shipped|cancelled
total: float = 0
customer_id: integerTable-level constraints as sub-sections:
primary-key[] composite PK (column names)
unique[] UNIQUE constraints (each entry one or more cols)
check[] raw CHECK expressions
references[] single-col FKs in form 'local: Table.foreign'
Inline DDL objects under #! Table (created atomically with the table):
Index[] name, columns, optional unique / where
Trigger[] name, when, event, body, optional condition
Top-level DDL-object documents:
#! Index
name: idx_order_status
table: Order
columns: status
unique: false
where: deleted_at IS NULL
#! Trigger
name: trg_log_orders
table: Order
when: AFTER # BEFORE | AFTER | INSTEAD OF
event: INSERT # INSERT | UPDATE [OF cols] | DELETE
condition: NEW.status = 'pending'
body: "INSERT INTO Log(msg) VALUES ('x');"
#! View
name: PendingOrders
select: "SELECT id FROM \\\"Order\\\" WHERE status='pending'"Virtual tables — using: <module> modifier on #! Table:
#! Notes
using: fts5
title: string
body: string
## unindexed[]
- memory_id
## options[]
- "tokenize = 'porter unicode61'"Existing tables: writes are additive — only new columns are added. Constraint or column-type changes need 'action: rebuild' frontmatter, which runs the SQLite table-rebuild dance atomically (data preserved over the column intersection; pre-existing dependent indexes and triggers must be redeclared inline in the rebuild document).
Frontmatter policy: observable tolerance. Frontmatter keys: action (= 'rebuild'), debug. Debug: 'debug: sql, timing, table'.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| document | Yes |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |