SQL data layer

What BOLT_SQL is

includes/BOLT_SQL.php is the relational companion to the flat-file data store. Where BOLT_DB handles JSON snapshots and secured records, BOLT_SQL handles the data that — in Bolt's own words — "still belongs in the database." It is one self-contained, static class that does three jobs:

• Connects to MySQL through a single lazy PDO connection.
• Reads and writes via prepared statements, with every table and column name validated against your schema (the core SQL-injection defense).
• Manages tables by ingesting a JSON schema spec — creating and altering them on demand.

It is a focused port of the Paradigm CMS data stack, collapsed into one class — without Paradigm's row-level security. Every call is a trusted server-side call. Load it with require_once (the page and API front controllers already do, so it is available app-wide):

require_once ROOT_DIR . '/includes/BOLT_SQL.php';

Configuration

Credentials live in config/database.php, which returns an array — the same server-side-only pattern as config/users.php (the whole config/ directory is denied to the web by config/.htaccess):

// config/database.php
return [
    'host'     => '127.0.0.1',
    'name'     => 'bolt',     // the database must already exist
    'user'     => 'root',
    'password' => 'root',
    'port'     => 8889,       // MAMP's MySQL; the standard port is 3306
    'socket'   => null,       // e.g. '/Applications/MAMP/tmp/mysql/mysql.sock'
    'prefix'   => 'bolt_',    // table-name prefix -> bolt_notes
];

Credentials resolve in order: BOLT_SQL::configure([...]) → config/database.php → getenv('BOLT_DB_*'). BOLT_SQL creates and alters tables, not the database itself — the target database must exist. It connects lazily (nothing happens until the first query) and never die()s: on a connection failure it logs and returns the standard error array.

Defining an object

Each object is described by a JSON spec. A core object lives at config/objects/{object}.json; an extension's object lives at extensions/{slug}/config/objects/{object}.json. A spec has infoKeys (real table columns) and metaKeys (key/value rows in a companion _meta table; slug "*" means "any key"):

{
    "infoKeys": [
        { "slug": "id",      "type": "int(11)" },
        { "slug": "title",   "type": "varchar(255)" },
        { "slug": "body",    "type": "text" },
        { "slug": "status",  "type": "varchar(100)", "allowedValues": ["active", "archived"] },
        { "slug": "config",  "type": "json" },
        { "slug": "created", "type": "int(11)", "index": true }
    ],
    "metaKeys": [
        { "slug": "reminderCount", "type": "int(11)" },
        { "slug": "visibility",    "type": "varchar(50)", "allowedValues": ["public", "private"] },
        { "slug": "*",             "type": "text" }
    ]
}

Honored keys are type, index, and allowedValues; anything else is ignored. An id column is always created as INT PRIMARY KEY AUTO_INCREMENT whether or not you list it. Meta values always store as text, so the default metaKey is { "slug": "*", "type": "text" } — declare a meta key explicitly only when you want to validate or cast it.

Object references and table names

Every method takes a $slug in one of two forms:

• "note" — a core object (config/objects/note.json).
• "event-registration:invoice" — an object owned by an extension, using the same slug:name syntax as the rest of Bolt.

The table name includes the prefix and (for an extension object) the extension slug, so two extensions can ship an invoice object without colliding. The slug's dashes become underscores in the table name (Bolt slugs allow -; SQL identifiers do not), while the extension directory keeps its dashes:

Syncing the schema

sync() turns specs into tables. Call it with no argument to sync every spec (core plus active extensions), or with a slug to sync one. It creates a missing table, adds new columns and indices to an existing one, never drops a column (so removing a key from the spec preserves its data), and is idempotent — re-syncing an unchanged spec issues no statements.

BOLT_SQL::sync('note');   // create/alter bolt_notes + bolt_note_meta
BOLT_SQL::sync();          // sync every object spec on the install

Reading and writing

The static methods below are the full surface. Each is also exposed as a bare global helper for convenience — get_items(), get_item(), create_object(), update_object(), update_object_if(), delete_object(), count_objects(), get_info() — which simply forward to the class.

// Create — info and meta together; the split follows the schema.
$result = create_object('note', [
    'title'         => 'Ship the docs',
    'status'        => 'active',
    'config'        => ['pinned' => true],   // a json column
    'reminderCount' => 3,                      // a meta key
]);
$note = $result['data'];   // includes 'id'

// Read one, then a filtered list.
$one  = get_item('note', $note['id']);
$open = get_items('note', [
    'conditions' => [ [ ['status', '=', 'active'] ] ],
    'orderBy'    => ['created' => 'DESC'],
    'limit'      => 20,
    'with_meta'  => true,   // merge meta keys into each row
]);

update_object('note', $note['id'], ['status' => 'archived']);
delete_object('note', $note['id']);

Writes return the standard envelope on failure — ['success' => false, 'errors' => [['message' => '...']]] — matching the rest of the Bolt API. Database errors are caught and logged; the caller never sees raw SQL.

Atomic updates (compare-and-set)

update_if() runs a single guarded UPDATE … SET … WHERE id = ? AND <guard> and returns the number of rows it changed. It is the safe way to hand a row to exactly one worker under concurrency: a return of 1 means you won the claim; 0 means another caller got there first (or the guard no longer matched). Info columns only, and the new values must actually change the row (MySQL reports rows changed, so a no-op SET returns 0):

// Claim a queued job so only one concurrent worker can take it.
$claimed = update_object_if('job', $id,
    ['status' => 'queued'],     // guard: only while still queued
    ['status' => 'sending']     // set
);
if ($claimed === 1) {
    // We own this row — safe to process it exactly once.
}

Fetching specific properties

get_info() pulls just the properties you name for a single object, without reading the whole row. Pass one key for a single value, or an array of keys for an ordered key => value map. Info columns and meta keys mix freely; each value is cast exactly as a normal read would cast it, and any key that is unknown or has no stored value falls back to $default:

// One key -> its value (or the default when absent).
$status = get_info('note', $id, 'status');                // 'active'
$count  = get_info('note', $id, 'reminderCount', 0);      // 3  — a meta key, cast to int

// An array of keys -> an ordered key => value map covering every key.
$fields = get_info('note', $id, ['title', 'status', 'reminderCount', 'visibility']);
// [
//     'title'         => 'Ship the docs',
//     'status'        => 'active',
//     'reminderCount' => 3,          // meta, cast to int
//     'visibility'    => 'public',   // meta
// ]

// Missing or unknown keys still appear, set to $default.
$some = get_info('note', $id, ['title', 'nope'], null);
// ['title' => 'Ship the docs', 'nope' => null]

The array form resolves the whole set in at most two queries — one for the requested info columns, one for the requested meta keys — no matter how many keys you pass.

Conditions

A query's conditions is a list of groups. Conditions within a group are OR'd; groups are AND'd together. Each condition is [column, operator, value]:

// (status = 'active' OR status = 'archived') AND (author_id = 5)
get_items('note', ['conditions' => [
    [ ['status', '=', 'active'], ['status', '=', 'archived'] ],
    [ ['author_id', '=', 5] ],
]]);

Operators: =, !=, <>, <, >, <=, >=, LIKE, NOT LIKE, IN, NOT IN, IS, IS NOT. IN/NOT IN take an array; IS/IS NOT test NULL. Other $props: orderBy (['col' => 'ASC'|'DESC']), limit (default 500), offset, dataToSelect (specific columns), and with_meta. With no conditions a query returns all rows up to limit — bounded, never an unbounded dump.

Meta keys work in conditions and orderBy too, not just info columns. A condition on a meta key compiles to a correlated EXISTS against the _meta table — the key name and value are bound, so it stays injection-safe — and ordered operators (<, >, <=, >=) plus meta orderBy CAST the text-stored value to the key's declared type, so numeric and date comparisons are real rather than lexical. Because a meta key has no NULL of its own, IS/IS NOT test whether the meta row is absent vs. present, and !=/NOT IN/NOT LIKE match only rows that actually have the key with a differing value (the same way a NULL info column behaves under those operators).

// reminderCount is a meta key typed int — compared and sorted numerically
$busy = get_items('note', [
    'conditions' => [ [ ['reminderCount', '>=', 3] ] ],
    'orderBy'    => ['reminderCount' => 'DESC'],
]);

Performance. Each meta key in a condition or orderBy adds one correlated subquery that runs per candidate row. That is fine for modest result sets, but for a filter or sort you run on a hot path, promote the key to an infoKey — a real, indexable column — rather than leaving it in meta. The _meta table is indexed on parent_id only; a composite (parent_id, meta_key) index speeds these subqueries up, but it applies to newly-created meta tables — existing ones keep their current indices.

Validation on write

Before any insert or update — for both info columns and meta keys — BOLT_SQL enforces two declarative constraints from the schema, aborting the whole write (no partial rows) on the first failure:

• type — the value must be compatible with the declared type. Numeric, boolean, date, and time types reject incompatible input; varchar/text/json are permissive (any string or JSON). So a meta key typed decimal(10,2) rejects alpha text even though it stores as text.
• allowedValues — when the spec lists a non-empty set, the value must be one of them.

create_object('note', ['title' => 'x', 'status' => 'draft']);
// ['success' => false, 'errors' => [['message' => "Invalid value for 'status'. Allowed: active, archived."]]]

create_object('note', ['title' => 'x', 'reminderCount' => 'soon']);
// ['success' => false, 'errors' => [['message' => "Invalid value for 'reminderCount': expected type int(11)."]]]

To constrain a meta key, declare it explicitly in metaKeys (as reminderCount and visibility are above). An explicit meta key wins over the "*" wildcard, which is the unconstrained text fallback.

Type casting on read

Everything in the database comes back as a string, which is rarely what you want. BOLT_SQL casts each value — info columns and meta — back to the PHP type its schema implies, so reads mirror the shape you declared:

So a meta key typed int reads back as an int even though it stores as text, and a json column round-trips to an array. Exact types stay strings: decimal and numeric are returned as their precise string form (for example a decimal(10,2) of 5 reads as "5.00") so money keeps full precision — format it for display, and cast deliberately for arithmetic. null always passes through.

When you pass with_meta, meta is merged into each row but can never shadow an info column: a real column always wins over a same-named meta value, even one excluded from the dataToSelect list.

Trust model and security

BOLT_SQL guarantees SQL-injection safety: every value is bound through a prepared statement, and every identifier — table, column, order-by, select, operator — is validated against the object's schema or a strict whitelist. A meta key named in a condition or orderBy compiles to a correlated subquery with the key name bound as a parameter and any CAST target drawn from a fixed type whitelist, so it extends that guarantee rather than weakening it. A condition on a column that is neither a real column nor a declared meta key yields an empty result, not a malformed query.

It deliberately does not do access control and does not HTML-escape output — the same trust model as BOLT_DB, where the security perimeter is the caller:

• Never wire unvalidated client input into $slug (the object identifier).
• Authorization belongs in the calling endpoint, not the store.
• Escape values at the output layer; BOLT_SQL returns them as-is (auto-decoding JSON and casting types).

Putting it together

Define config/objects/note.json (above), point config/database.php at a database that exists, then:

require_once ROOT_DIR . '/includes/BOLT_SQL.php';

BOLT_SQL::sync('note');                       // bolt_notes + bolt_note_meta

$created = create_object('note', [
    'title'      => 'First note',
    'status'     => 'active',
    'config'     => ['color' => 'blue'],
    'visibility' => 'public',
]);

$id = $created['data']['id'];
$note = get_item('note', $id);                // ['id' => 1, 'config' => ['color' => 'blue'], ...]
$active = count_objects('note', ['conditions' => [ [ ['status', '=', 'active'] ] ]]);

See the data store for the flat-file side of Bolt's persistence, and extensions for packaging an object (and its config/objects/ spec) outside core.