Mirror synced data in your database

Your application often keeps its own table of events, in the shape its screens and queries need. This script keeps such a table up to date after every sync, from the change feed. It works the same with any adapter.

import sqlite3

from calendaring_sync import FeedEntryKind, RecordState

SCHEMA = """
CREATE TABLE IF NOT EXISTS events (
    local_key INTEGER PRIMARY KEY,
    scope TEXT NOT NULL,
    content BLOB NOT NULL
);
CREATE TABLE IF NOT EXISTS feed_cursor (
    id INTEGER PRIMARY KEY CHECK (id = 0),
    cursor INTEGER NOT NULL
);
"""


def mirror(replica, db):
    """Bring the events table up to date with the replica's change feed."""
    row = db.execute("SELECT cursor FROM feed_cursor").fetchone()
    cursor = row[0] if row else 0
    while entries := replica.read_feed(after=cursor):
        with db:
            for entry in entries:
                apply(replica, db, entry)
            cursor = entries[-1].number
            db.execute(
                "INSERT INTO feed_cursor VALUES (0, ?) ON CONFLICT(id) DO UPDATE SET cursor = ?",
                (cursor, cursor),
            )


def apply(replica, db, entry):
    """Bring the row of one feed entry's record up to date."""
    if entry.kind is FeedEntryKind.SCOPE_DROPPED:
        db.execute("DELETE FROM events WHERE scope = ?", (entry.scope,))
        return
    record = replica.record(entry.local_key)
    if record is None or record.state is RecordState.REMOVED or record.content is None:
        db.execute("DELETE FROM events WHERE local_key = ?", (entry.local_key,))
    else:
        db.execute(
            "INSERT INTO events VALUES (?, ?, ?) ON CONFLICT(local_key) DO UPDATE SET content = ?",
            (record.local_key, record.scope, record.content, record.content),
        )


db = sqlite3.connect("app.db")
db.executescript(SCHEMA)
adapter.sync()
mirror(replica, db)

The table and the cursor live in one database, and each page of the feed updates both in one transaction. So a crash leaves the table matching the cursor, and the next run continues from there: no entry is handled twice, and none is skipped.

apply reads the record each entry is about, since the record holds its latest synced copy, however many entries there were since the last run. A record that’s removed, or that was purged meanwhile, loses its row, and a dropped scope loses every row. Your own edits write no feed entries until the server has them, so the table holds each record’s synced copy. Read Record.edit as well where your application shows edits that aren’t sent yet.

When the feed was trimmed past your cursor, read_feed() raises TrimmedFeedError: rebuild the table from records(), as Read the change feed shows.