Module sql 

The canonical pimdir SQL, inlined verbatim from the spec so the crate is self-contained. Kept in sync with pimdir/migrations/ and pimdir/queries/, where the source of truth is.

A store keeps one shared item per logical thing (its flags, body and summary) and one binding per source that syncs it (that source’s last agreed base). A single-source store is the degenerate case of one binding per item; a two-source store keeps two.

Constants

ADJUST_REFCOUNT

Adjusts one object’s refcount by a signed delta; the hash’s primary key makes this an indexed point update.

ALL

Every statement in this module, paired with its constant name.

BACKFILL_SHARED_OBJECT

Gives every binding written before shared_object existed the item’s own body as its agreement point, once the column has been added (spec §6, the draft allowance).

BUMP_ATTEMPTS

Records a failed apply attempt without parking: the retry path, the reference park_action with a NULL error.

BUMP_GENERATION

The owner’s handle-space reset marker (spec §12): run in the same transaction as the rebuild it records.

BUMP_NEXT_SEQ

Hands out, and advances, the store-global next public id via RETURNING. The counter only ever increases, so a seq is never reused. Run only when the message has no id yet.

CANCEL_ACTION

One queue row removed by request rather than by application, pending or parked (spec §15.5): a queued item withdrawn, or a performed intent acknowledged by the process that carried it out. The same delete as DELETE_ACTION, named apart because the trigger is a request. It releases the row’s object_hash pin, so it runs in one transaction with the refcount settle.

CLAIM_ACTION

Deletes the row an owner is about to apply, and reports whether it was still there.

COUNT_ITEMS

Counts a collection’s live items (tombstones excluded).

COUNT_RETAINED

Counts a collection’s retained items, the counterpart of COUNT_ITEMS; rides the items_retained partial index.

COUNT_RETAINED_BEFORE

What a purge with this cutoff would retire, and what its bodies weigh: the preview a confirmation prints, PURGE_RETAINED_BEFORE being the act.

DANGLING_BINDINGS

The bindings whose item is gone: the one dangling row a repair can clear, since nothing can read it (DELETE_DANGLING_BINDINGS).

DANGLING_ITEM_OBJECTS

The items whose body is not indexed. Reported, never repaired: the item is still the item.

DANGLING_QUEUE_OBJECTS

The queue rows whose body is not indexed. Reported, never repaired: the row is still an intent somebody expressed.

DELETE_BINDING

Deletes one source’s binding of an item.

DELETE_DANGLING_BINDINGS

Deletes the bindings whose item is gone, the one dangling row a repair can clear without guessing: a binding with no item is unreachable, where an item with no object row still holds the item.

DELETE_GARBAGE_OBJECTS

Drops the unreferenced object rows inside the collector’s transaction; their blobs are unlinked after the commit, so a crash leaves at worst an orphan blob.

DELETE_ITEM_BINDINGS

Deletes every binding of one item, for the retire path: the row survives, but no source holds it, so no base does either. A retained row carrying no binding is the persisted form of “the removal has finished propagating” (spec §11).

ENQUEUE_ACTION

A producer’s append. Runs after ENSURE_COLLECTION, in one transaction with the STORE_OBJECT upsert when the payload references a body (spec §15.1).

ENSURE_COLLECTION

Creates a collection row if it does not exist yet, leaving an existing one untouched (the kind is declared separately by SET_COLLECTION_KIND).

ENSURE_INDEXES

Every index the schema grew after version 1 was first published, as one idempotent batch: a store written by an earlier draft has the tables but not these, and an index is not something a reader can do without.

GET_ITEM

Fetches one live item by its public id (seq), the client-facing key.

HANDLE_FOR_LINK

One source’s handle for an item, which its binding’s primary key answers directly: the lookup a queued action needs to name the placement it edits.

INSERT_BINDING

Inserts one item’s binding for one source (the new-binding path; UPDATE_BINDING handles an existing one).

INSERT_ITEM

Inserts one item row (the new-placement path; UPDATE_ITEM handles an existing one).

ITEM_BINDINGS

Every source’s binding of one item: the handle it is bound to, the base the last sync agreed on, and the conflict it is stuck on. The read behind item show, which names one item and can afford to say everything about it.

LINK_FOR_HANDLE

The link id one source’s handle is bound to, for a batch that drops a placement: a drop names a handle, and the hub is keyed by link id.

LIST_ACCOUNTS

The accounts owning at least one collection. A store knows an account only through its collections (spec §9.2), so this is not a configured roster.

LIST_COLLECTIONS

Lists every collection with its display metadata and generation, ordered by sort_order then id, the ones carrying no sort order coming last.

LIST_COLLECTIONS_BY_ACCOUNT

One account’s collections, the filter axis of a merged view. IS so binding NULL selects the collections of a single-account store.

LIST_CONFLICTED_BINDINGS

The bindings waiting for a decision, across an account’s collections: what each one is, and the three bodies a resolver merges.

LIST_GARBAGE_OBJECTS

Lists the objects nothing references any more: what the collector takes, and never a write’s business, since the batch that attaches a body may not be the one that indexed it (spec §5).

LIST_ITEMS_PAGE

A keyset page of a collection’s live items in link-id order. :after is the exclusive lower bound on link_id, the empty string starting from the beginning since a link_id is never empty; rides the items primary key, with no extra index.

LIST_ITEMS_PAGE_ASC

A keyset page of a collection’s live items in the kind’s own ascending order (spec §9.3): A to Z for contacts, earliest first for mail and calendars.

LIST_ITEMS_PAGE_DESC

The same page descending: newest first for mail and calendars, Z to A for contacts.

LIST_LINK_PLACEMENTS

Every live placement of one identity, with the collection and account it sits in (spec §9.2). The store reports where a link id occurs and takes no position on whether the placements are one thing: a mail view lists them, a contact view may offer to merge them, off these rows.

LIST_OBJECT_HASHES

Every hash the index holds. For the diagnosis that has to visit every row anyway, never for the collector, which asks about the file in front of it with OBJECT_EXISTS rather than holding the whole index in memory.

LIST_OBJECT_PLACEMENTS

The same on the dedup axis, by body rather than identity, so it pairs placements two servers gave different link ids.

LIST_QUEUED_COLLECTIONS

The collections with pending work, for the owner’s drain loop.

LIST_RETAINED_PAGE

A keyset page of a collection’s retained items, joined to the body size the row still pins (NULL when unhydrated): the trash listing beside LIST_ITEMS_PAGE, and the only read that returns them.

LIST_SOURCES

The distinct source names the store has synced, across all collections, so a client discovers which source to attribute writes to.

LIVE_BYTES

The bytes held by objects at least one live item binds. An object a live and a retained item share counts here, since purging the retained one frees nothing.

LIVE_ITEM_FOR_LINK

Whether a collection holds a live item under a link id: the collision check a queued add runs before staging.

LOAD_ACCOUNT

Reads a collection’s owning account.

LOAD_ACTION_ROW

One queue row’s spent attempts and pinned body, for a caller acting on a row by id: cancelling it, acknowledging an intent it performed out of band, or recording a failure.

LOAD_BINDINGS

Loads every per-source binding of a collection: the stored base (handle, flags, object, revision) each sync merges against.

LOAD_BINDINGS_BY_LINK

The same rows, narrowed to the link ids one write batch touches: the binding half of LOAD_ITEMS_BY_LINK.

LOAD_CHECKPOINT

Reads one source’s sync checkpoint for a collection.

LOAD_CONFLICT

Reads a collection’s conflict policy.

LOAD_GENERATION

A collection’s handle-space epoch, so a reader derives epoch-dependent protocol values (an IMAP UIDVALIDITY) from the store alone.

LOAD_ITEMS

Loads a whole collection for the sync seam: every item, tombstones included, unpaginated and unordered.

LOAD_ITEMS_BY_LINK

The same rows, narrowed to the link ids one write batch touches (spec §14).

LOAD_KIND

Reads a collection’s declared kind.

LOAD_PARKED_ACTIONS

The parked actions, for status surfaces and operator repair.

LOAD_PENDING_ACTIONS

The owner’s drain: a collection’s pending (non-parked) actions, in append order. A reader runs the same statement to overlay pending actions on its item projection (read-your-writes, spec §15.4).

LOOKUP_OBJECTS

Resolves the object hash currently bound to each of the given link ids (passed as a JSON array), skipping the ones carrying no body.

MIGRATION_0001

Schema version 1 (migrations/0001_init.sql), the whole draft schema including the action queue and collection generations. Applied to a fresh database; the caller sets PRAGMA user_version = 1 on success.

MINTED_KEYS

How many minted keys (spec §9, dup:<hint>#<handle>) each collection holds: the second copy of an identity a source hands over twice, filed as an item of its own.

OBJECT_EXISTS

Whether the index still holds a body: the collector’s question about the one file in front of it, asked on the primary key (spec §5).

OBJECT_SIZE

One object’s stored size.

OBJECT_STATS

How many objects are indexed and what they weigh.

PARK_ACTION

A permanently failing action: recorded and skipped, visible to operators instead of blocking the collection’s queue.

PURGE_ITEM

Purges one retained item by its public id: the only true delete. Its bindings cascade, and the body it released is unlinked by the collector once nothing else references it. Guarded on retained_at, so a purge can never take a live item.

PURGE_RETAINED_BEFORE

The time-based sweep: every item retired strictly before :cutoff (RFC 3339), so one retained exactly at that instant is kept. Store-wide, since how long to keep is the owner’s policy. The cutoff is the caller’s parameter, not the store’s clock, so the boundary is deterministic even though the stamps are SQLite’s.

RECOMPUTE_REFCOUNTS

Recomputes every object’s refcount from the five columns that pin one (spec §7): an item’s body, an item’s conflict copy, a source’s stored base, a binding’s diverging remote body and a pending queue action’s body.

REFCOUNT_DRIFT

The objects whose stored refcount disagrees with the five pointer columns that justify it: the read RECOMPUTE_REFCOUNTS settles.

RELEASE_PINS

Releases one reference from each of the given hashes (a JSON array), the set-based form of ADJUST_REFCOUNT at -1.

RENAME_COLLECTION

Gives a collection a new id, carrying its whole contents with it: every foreign key onto collections(id) is ON UPDATE CASCADE, so the items, bindings, sources, queue rows and child collections follow in the same statement (spec §14).

RESHAPED_INDEXES

Indexes an earlier draft created under the same name over different columns, as (name, the columns it must hold now).

RETAINED_BYTES

The store-wide size of the bodies retention is holding, each distinct object counted once. An upper bound on what a purge reclaims: an object a live item also points at keeps a reference and survives.

RETAINED_ITEM

The retained row holding a link id, if any: its public id and the objects it pins, which revive releases and purge reclaims.

RETAIN_ITEM

Retires one item: it stands exactly where a hard-deleting store would have issued its delete. The row keeps its object_hash, so the body keeps its reference and its blob survives the sweep. SQLite stamps the instant itself, so no clock is plumbed through the crate; a purge’s cutoff is by contrast the caller’s parameter.

REVIVE_ITEM

Revives a retained row: the link id is back, from a source-side resurrection or a client add, so it stops being retained instead of conflicting on the primary key. The caller adopts the new content with UPDATE_ITEM in the same transaction, and the row keeps its seq.

SEQ_BY_LINK

Resolves an item’s public id (seq) from its internal link_id, the inverse of GET_ITEM, for a consumer that just staged an add.

SEQ_FOR_LINK_ANY

The message’s existing public id, if any placement of this link_id already has one (in any collection), so all placements of a message share one id.

SET_COLLECTION_ACCOUNT

Regroups a collection under another account, or out of one with NULL. Safe at any time: the account partitions no identifier (spec §9.2), so the move leaves seqs, link ids and objects alone.

SET_COLLECTION_KIND

Declares (or re-declares) a collection’s kind, creating the row if the collection is not known yet. Updates the kind alone, so a collection never changes account as a side effect of a sync declaring its media type.

SET_CONFLICT

Stores a collection’s conflict policy.

SET_SORT_KEY

Restates one item’s ordering key, for a re-projection over items already stored: a store written before its kind had a convention, one whose convention changed, or a consumer whose sync engine does not carry the key inline (spec §9.3). Not the ordinary write path.

STORE_OBJECT

Indexes an object by its content hash at refcount 0; re-storing a known hash only refreshes its size, since the count belongs to ADJUST_REFCOUNT.

UPDATE_BINDING

Updates one existing binding’s columns in place (its primary key (collection, link_id, source) is unchanged).

UPDATE_ITEM

Updates one existing item’s columns in place (the diffed-save path; the primary key (collection, link_id) is unchanged).

UPSERT_CHECKPOINT

Writes one source’s sync checkpoint for a collection, replacing the previous one.

VERSION

The current schema version.