Inside a Store file

Save and reopen your work creates todos.pret, edits it and reopens it. This page opens that file to answer a few questions. What does Pret write in it, and where? Can another tool read it? Why does a crash or a second notebook not corrupt it? And what are the _pret_ tables next to your data?

What a Store holds

A Store holds three kinds of values, and the file saves each of them differently

Each edit of any of them is also appended to an edit history, in the same file.

A .pret file is a SQLite database.

Let's open the file of the saving tutorial, or create it, with one ordinary value and one DataFrame

from pret import DataFrame, create_store

store = create_store(
    lambda: {
        "title": "My todos",
        "todos": DataFrame(
            [
                {"todo_id": 1, "text": "My first todo", "done": True},
                {"todo_id": 2, "text": "My second todo", "done": False},
            ]
        ),
    },
    sync="todos.pret",
)

and list what it contains

import sqlite3

connection = sqlite3.connect("todos.pret")
query = "SELECT type, name FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite%'"
for kind, name in connection.execute(query + " ORDER BY type, name"):
    print(kind, name)

table _pret_acknowledgments
table _pret_changes
table _pret_dataframes
table _pret_revision_rows
table _pret_state
table data_todos_2e35342dffba
view todos

data_todos_… holds the rows of the todos DataFrame, and the todos view gives them a readable name. The _pret_ tables hold everything else. The next sections go through them in the order of the table above.

Ordinary values, one JSON document

_pret_state has a single row. Its value column holds the whole Store as one JSON document, except the rows of its DataFrames. Each DataFrame is replaced by a reference to its SQL table, {"__pret_dataframe_ref__": "<resource ID>"}, and the document also holds the Store metadata and ID

print(connection.execute("SELECT value FROM _pret_state").fetchone()[0])

{"data":{"title":"My todos","todos":{"__pret_dataframe_ref__":"815a4b92bb244bcca30d19b6a84c50d7"}},"metadata":{},"id":"22bc0d63-e6cf-42c7-8b86-6ae82af0c039"}

• data is the Store value, here the title and a reference to the todos DataFrame, whose rows are in the SQL table described in the next section
• metadata holds the values passed to save_store(..., metadata=...), read them back with load_store_snapshot(path, metadata=True)
• id identifies the Store, browser pages and other notebooks use it to follow its changes

Reading _pret_state and the DataFrame tables therefore gives the whole Store, as of the revision noted in _pret_state, see the note below. SQLite reads a single value with json_extract

connection.execute("SELECT json_extract(value, '$.data.title') FROM _pret_state").fetchone()

('My todos',)

Editable DataFrames, one SQL table each

Adding records, a pandas table or an Arrow table to a Store copies its rows into a new SQL table, and _pret_dataframes lists these tables

connection.execute("SELECT resource_id, table_name, created_revision FROM _pret_dataframes").fetchall()

[('815a4b92bb244bcca30d19b6a84c50d7', 'data_todos_2e35342dffba', 1)]

• resource_id is the ID used by references in _pret_state
• table_name is the SQL table, named after the Store key with a random suffix, so that renaming the key or adding another DataFrame under the same key does not clash with it
• arrow_schema, not shown, is the Arrow schema of the DataFrame, used to restore column types
• created_revision is the edit that created the table

The SQL table has one more column than the DataFrame, __pret_row_id, an integer that identifies each row even when the DataFrame has no unique column. Pret uses it to edit a row, undo an edit or follow a row across notebooks. To read the rows, use the view named after the Store key, which has the DataFrame columns only

import pandas as pd

pd.read_sql("SELECT * FROM todos", connection)

Each top-level DataFrame of the Store gets a view. If two keys refer to the same DataFrame, there are two views over one table, and an edit through either key changes the same rows

store["weekend"] = store["todos"]
connection.execute("SELECT name, sql FROM sqlite_master WHERE type = 'view'").fetchall()

[('todos', 'CREATE VIEW "todos" AS SELECT "todo_id","text","done" FROM "data_todos_2e35342dffba"'), ('weekend', 'CREATE VIEW "weekend" AS SELECT "todo_id","text","done" FROM "data_todos_2e35342dffba"')]

Deleting weekend would only drop its view. Renaming a key renames its view, the table keeps its name. DataFrames nested deeper in the Store get no view. Neither do keys that only differ in case from another key, since SQLite names ignore case, keys starting with sqlite_ or _pret_, and keys already used by another table.

Source-backed DataFrames, a reference only

A Spark DataFrame or an Ibis table describes a query, and its result can be too large to copy. Pret keeps it as a query, so the file only holds a reference. This example needs pip install 'ibis-framework[duckdb]'

import ibis

source = ibis.memtable({"todo_id": [3], "text": ["My third todo"]})
store["remote"] = DataFrame(source)
print(connection.execute("SELECT json_extract(value, '$.data.remote') FROM _pret_state").fetchone())
print(connection.execute("SELECT count(*) FROM _pret_dataframes").fetchone())

('{"__pret_dataframe_ref__":"db68ecd432b74fa0a91a1a123f60a8bb"}',)
(1,)

_pret_state has a reference, but _pret_dataframes has no table behind it. The database connection and the query live in Python only, so reopening the file gives back the bare reference instead of a DataFrame

from pret import load_store

reopened = load_store("todos.pret")
reopened["remote"]

{'__pret_dataframe_ref__': 'db68ecd432b74fa0a91a1a123f60a8bb'}

The application must assign the query again, reopened["remote"] = DataFrame(source). To keep the rows themselves, make an editable copy with save_store, which runs the query and saves its result as an editable table, see DataFrames.

The same goes for application code. Metanno saves the name and parameters of the recipe that built an interface in the Store metadata, but the recipe's Python code and environment must be available when reopening.

Edit history

Each saved change gets a revision number, 1 for the creation of the Store, then one per change. A change is one assignment, one DataFrame edit, one widget edit or one transact block. Pret saves everything a change does in one SQLite transaction, so the file never contains half of it. If the change fails, SQLite discards its writes and Pret restores the previous values in Python.

Let's change the title and a row in one transaction, then read the last revision

from pret.store import transact

with transact(store):
    store["title"] = "Todos for Sunday"
    store["todos"].update(identity={"todo_id": 1}, values={"done": False})

connection.execute(
    "SELECT revision, kind, origin, ops, inverse FROM _pret_changes ORDER BY revision DESC LIMIT 1"
).fetchone()

(4, 'edit', 'kernel-0bb8ee63dc224a38aafd1fb81a923917', '[{"op":"set","path":["data","title"],"value":"Todos for Sunday"}]', '[{"op":"set","path":["data","title"],"value":"My todos"}]')

Each row of _pret_changes describes one revision

• ops lists the changes to the ordinary values, as set, insert and delete operations on paths of the JSON document, and inverse lists the operations that cancel them
• changeset holds the old and new values of the changed rows, in SQLite's changeset format
• origin names who made the change, this Python Store (kernel-…) or a browser page, kind tells whether the revision created the Store, applied an edit or undid one

Two smaller tables complete the history. _pret_revision_rows lists the row and columns each revision changed. When a browser sends an edit based on an older revision, Pret rejects it only if a later revision changed the same cell, or inserted or deleted the row. Undo also uses it to cancel the edit of one browser page while keeping the cells another page changed since.

_pret_acknowledgments keeps the reply sent for each request. If a browser loses its connection before receiving the reply, it sends the same request again, and Pret returns the saved reply instead of applying the edit twice.

This history is how several notebooks share one file. Each one checks the file every 0.2 seconds, and before each change, for revisions it has not applied yet, and applies their ops to its ordinary values. DataFrame rows need no copy, every notebook reads the same SQL tables. What happens when you edit a Store follows an edit from the browser to the file and to other pages.

Files from older Pret versions

Store files record their format number in PRAGMA user_version, 1 for this version. Files in the earlier binary format are converted to SQLite the first time create_store or load_store opens them, and the original is kept next to it as name.legacy. Converting them needs pip install 'pret[legacy]'. Close other notebooks using the file before opening it with this version, they would keep writing to the original. load_store_snapshot reads these files without converting them.