Python Advanced Data Operations
2026-10-27: 34/34 scenario assertions and all eight baseline SQLite checkpoints plus four isolated mutation databases PASS on the qualified workspace runtime — RELEASE PENDING.
Shared School model, five-school fixture, expected results, coverage priorities, and dated receipt.
The excerpts below are synchronized from the complete executable application. Its imports, composition root and result-reporting helpers are in that file. Follow the shared setup and evidence contract before adapting these calls. Request current entity/action Assist, then scoped field Assist; keep generated domain libraries unchanged.
Analytic rows are dictionaries from execute_for_rows; the native group key is school_type. Version 0.3.1 reports a rejected stale update as missing authoritative persisted state; the example also checks that capacity remains 950 in SQLite. Continuous cursor execution and counted fallback observations require the recorded workspace repair: WORKSPACE PASS — RELEASE PENDING.
Run from the site checkout with the native prerequisites in the seven-runtime baseline:
node scripts/school-examples.mjs --suite advanced --language python --python-runtime ../teaql-python-pagination
Filtered pagination
Active schools with capacity at least 500 have total 3. Offset 1, limit 2 returns Riverside then West.
page = await (Q.schools_minimal().select_name().which_are_active()
.with_student_capacity_greater_than_or_equal_to(500)
.order_by_student_capacity_descending().order_by_id_ascending().offset(1).limit(2).count()
.comment("Page active schools by capacity").purpose("Review the School directory")
.execute_for_list(context))
report("pagination", {"names": [E.school(s).name().eval() for s in page], "total": page.total_count})
Two loaded relations
Riverside resolves to PRIMARY and Campus Learning Platform through selected child requests.
rows = await (Q.schools_minimal().select_name().with_name_is("Riverside Primary School")
.select_school_type_with(Q.school_types_minimal().select_code())
.select_platform_with(Q.platforms_minimal().select_name()).limit(2)
.comment("Load School type and platform").purpose("Review loaded School relations")
.execute_for_list(context))
assert len(rows) == 1
school = rows[0]
report("relations", {"school": E.school(school).name().eval(),
"type": E.school_type(E.school(school).school_type().eval()).code().eval(),
"platform": E.platform(E.school(school).platform().eval()).name().eval()})
Grouped analytics
PRIMARY: count 3, sum 1600, average 533.3333333333334; SECONDARY: count 2, sum 1700, average 850.
groups = await (Q.schools().group_by_school_type().count_as("count")
.sum_student_capacity_as("total").avg_student_capacity_as("average")
.limit(10)
.comment("Group School capacity by type").purpose("Verify provider aggregation")
.execute_for_rows(context))
report("groups", [{"type": int(r["school_type"]), "count": int(r["count"]),
"total": float(r["total"]), "average": float(r["average"])} for r in groups])
Relation metrics
SchoolType parents return the same counts and sums without loading their child lists.
cards = await (Q.school_types_minimal().select_code().count_schools_as("count")
.sum_student_capacity_of_schools_as("total", Q.schools()).order_by_code_ascending().limit(10)
.comment("Calculate SchoolType relation metrics").purpose("Verify provider relation aggregation")
.execute_for_rows(context))
report("relation-metrics", {r["code"]: {"count": int(r["count"]), "total": float(r["total"])} for r in cards})
Two Facets and zero buckets
Filtering to Riverside yields PRIMARY: 1 and SECONDARY: 0 with include-all, only PRIMARY: 1 with matched-only, and platform count 1.
async def facets(include_all):
return await (Q.schools_minimal().select_name().with_name_is("Riverside Primary School")
.facet_by_school_type_as("types", Q.school_types_minimal().select_code().count_schools_as("count"), include_all)
.facet_by_platform_as("platforms", Q.platforms_minimal().select_name().count_schools_as("count"), False)
.order_by_id_ascending().limit(2)
.comment("Facet the matching School set").purpose("Compare zero and matched buckets")
.execute_for_list(context))
all_rows, matched = await facets(True), await facets(False)
report("facets", {"includeAll": {r["code"]: int(r["count"]) for r in all_rows.facet("types")},
"matchedOnly": {r["code"]: int(r["count"]) for r in matched.facet("types")},
"platforms": {r["name"]: int(r["count"]) for r in all_rows.facet("platforms")},
"rows": [E.school(s).name().eval() for s in all_rows]})
Top-N for two parents
PRIMARY returns Riverside and West; SECONDARY returns Lakeside and Hill. The child limit applies independently to each parent.
types = await (Q.school_types_minimal().select_code()
.select_school_list_with(Q.schools_minimal().select_name()
.order_by_student_capacity_descending().order_by_id_ascending().limit(2))
.order_by_code_ascending().limit(10)
.comment("Load the two largest schools for each type").purpose("Verify Top-N across two parents")
.execute_for_list(context))
report("top-n", {E.school_type(t).code().eval(): [E.school_type(t).school_list().get(i).name().eval()
for i in range(E.school_type(t).school_list().size().eval())] for t in types})
Multiple pages and stable ties
Traverse all five Schools with page size 2 and a final empty page. Capacity descending, name ascending and ID ascending order the two capacity-500 rows as Hill then West. Every page, including the empty tail, reports total 5. This is offset paging, not a continuous cursor.
pages, totals = [], []
for offset in (0, 2, 4, 6):
page = await (Q.schools_minimal().select_name()
.order_by_student_capacity_descending().order_by_name_ascending().order_by_id_ascending().offset(offset).limit(2).count()
.comment("Traverse School pages including an empty tail").purpose("Verify stable page boundaries")
.execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page]); totals.append(page.total_count)
report("page-boundaries", {"pages": pages, "totals": totals})
No-match page
A filter matching no School returns an empty page and exact total 0, even with a nonzero offset.
page = await (Q.schools_minimal().select_name().with_name_is("No such School")
.order_by_student_capacity_descending().order_by_id_ascending().offset(1).limit(2).count()
.comment("Read a School filter with no matches").purpose("Verify empty page count")
.execute_for_list(context))
report("empty-page", {"names": [E.school(s).name().eval() for s in page], "total": page.total_count})
Facets with no matching rows
A no-match query retains both zero-count SchoolType buckets with include-all. Matched-only type and platform Facets are empty, and the result page is empty.
async def facets(include_all):
return await (Q.schools_minimal().select_name().with_name_is("No such School")
.facet_by_school_type_as("types", Q.school_types_minimal().select_code().count_schools_as("count"), include_all)
.facet_by_platform_as("platforms", Q.platforms_minimal().select_name().count_schools_as("count"), False)
.order_by_id_ascending().limit(2)
.comment("Facet the matching School set").purpose("Compare zero and matched buckets")
.execute_for_list(context))
all_rows, matched = await facets(True), await facets(False)
report("empty-facets", {"includeAll": {r["code"]: int(r["count"]) for r in all_rows.facet("types")},
"matchedOnly": {r["code"]: int(r["count"]) for r in matched.facet("types")},
"platforms": {r["name"]: int(r["count"]) for r in all_rows.facet("platforms")},
"rows": [E.school(s).name().eval() for s in all_rows]})
Positive, negative and nested relation predicates
Matching PRIMARY yields North, Riverside and West; excluding it yields Hill and Lakeside. A two-level School → SchoolType → Platform predicate returns the same three primary Schools. Predicates do not require loading the relation.
matching = await (Q.schools_minimal().select_name()
.with_school_type_matching(Q.school_types_minimal().with_code_is("PRIMARY"))
.order_by_name_ascending().limit(10).comment("Match School type by child request")
.purpose("Verify relation predicate semantics").execute_for_list(context))
excluding = await (Q.schools_minimal().select_name()
.without_school_type_matching(Q.school_types_minimal().with_code_is("PRIMARY"))
.order_by_name_ascending().limit(10).comment("Exclude School type by child request")
.purpose("Verify negative relation predicates").execute_for_list(context))
nested = await (Q.schools_minimal().select_name()
.with_school_type_matching(Q.school_types_minimal().with_code_is("PRIMARY")
.with_platform_matching(Q.platforms_minimal().with_name_is("Campus Learning Platform")))
.order_by_name_ascending().limit(10).comment("Match School through type and platform")
.purpose("Verify two-level relation predicates").execute_for_list(context))
report("relation-predicates", {"matching": [E.school(s).name().eval() for s in matching],
"excluding": [E.school(s).name().eval() for s in excluding], "nested": [E.school(s).name().eval() for s in nested]})
Filtered children and an empty parent bucket
Select only inactive child Schools. Both SchoolType parents remain present: PRIMARY has a loaded empty list and SECONDARY contains Hill. Filtering a selected child list must not filter out its parent.
types = await (Q.school_types_minimal().select_code()
.select_school_list_with(Q.schools_minimal().select_name().which_are_not_active()
.order_by_student_capacity_descending().order_by_id_ascending().limit(2))
.order_by_code_ascending().limit(10)
.comment("Load inactive Schools for each type").purpose("Preserve parents with empty child lists")
.execute_for_list(context))
report("filtered-children", {E.school_type(t).code().eval(): [E.school_type(t).school_list().get(i).name().eval()
for i in range(E.school_type(t).school_list().size().eval())] for t in types})
Filtered aggregates
Active Schools with capacity at least 500 produce PRIMARY: count 2, sum 1300, average 650; SECONDARY: count 1, sum 1200, average 1200. The aggregate request carries the filters itself.
groups = await (Q.schools().which_are_active().with_student_capacity_greater_than_or_equal_to(500).group_by_school_type().count_as("count")
.sum_student_capacity_as("total").avg_student_capacity_as("average")
.limit(10)
.comment("Aggregate active Schools with capacity at least 500").purpose("Verify provider aggregation")
.execute_for_rows(context))
report("filtered-groups", [{"type": int(r["school_type"]), "count": int(r["count"]),
"total": float(r["total"]), "average": float(r["average"])} for r in groups])
Reject blank query intent
Separate bounded queries supply a blank comment or a blank purpose. Both must reject execution with an intent-related diagnostic; a successful query or an unrelated error fails this case.
rejected = []
for comment, purpose in [(" ", "Verify blank comment rejection"), ("Check blank purpose rejection", " ")]:
denied = False
try:
await Q.schools_minimal().limit(1).comment(comment).purpose(purpose).execute_for_list(context)
except Exception as error:
assert any(word in str(error).lower() for word in ("comment", "purpose", "intent")), str(error)
denied = True
rejected.append(denied)
report("blank-intent", {"blankComment": rejected[0], "blankPurpose": rejected[1]})
Group analytics after deletion
After Riverside is updated and Hill is deleted, PRIMARY has count 3 and sum 1750; SECONDARY has count 1 and sum 1200. Tombstones must not inflate ordinary aggregates.
groups = await (Q.schools().group_by_school_type().count_as("count")
.sum_student_capacity_as("total").avg_student_capacity_as("average")
.limit(10)
.comment("Aggregate surviving Schools after deletion").purpose("Verify provider aggregation")
.execute_for_rows(context))
report("deleted-groups", [{"type": int(r["school_type"]), "count": int(r["count"]),
"total": float(r["total"]), "average": float(r["average"])} for r in groups])
Relation metrics after deletion
SchoolType relation counts and sums must agree with the surviving School rows: PRIMARY 3/1750, SECONDARY 1/1200.
cards = await (Q.school_types_minimal().select_code().count_schools_as("count")
.sum_student_capacity_of_schools_as("total", Q.schools()).order_by_code_ascending().limit(10)
.comment("Calculate relation metrics after School deletion").purpose("Verify provider relation aggregation")
.execute_for_rows(context))
report("deleted-metrics", {r["code"]: {"count": int(r["count"]), "total": float(r["total"])} for r in cards})
Facet totals after deletion
Ordinary type Facets count PRIMARY: 3 and SECONDARY: 1; platform count is 4. The two-row page is Lakeside then Riverside, so facet totals must ignore the page limit and exclude the tombstone.
async def facets(include_all):
return await (Q.schools_minimal().select_name()
.facet_by_school_type_as("types", Q.school_types_minimal().select_code().count_schools_as("count"), include_all)
.facet_by_platform_as("platforms", Q.platforms_minimal().select_name().count_schools_as("count"), False)
.order_by_student_capacity_descending().order_by_id_ascending().limit(2)
.comment("Facet the matching School set").purpose("Compare zero and matched buckets")
.execute_for_list(context))
all_rows, matched = await facets(True), await facets(False)
report("deleted-facets", {"includeAll": {r["code"]: int(r["count"]) for r in all_rows.facet("types")},
"matchedOnly": {r["code"]: int(r["count"]) for r in matched.facet("types")},
"platforms": {r["name"]: int(r["count"]) for r in all_rows.facet("platforms")},
"rows": [E.school(s).name().eval() for s in all_rows]})
Ordinary children after deletion
After restart, PRIMARY returns Riverside and West; SECONDARY returns only Lakeside. A deleted child cannot appear in an ordinary selected relation.
types = await (Q.school_types_minimal().select_code()
.select_school_list_with(Q.schools_minimal().select_name()
.order_by_student_capacity_descending().order_by_id_ascending().limit(2))
.order_by_code_ascending().limit(10)
.comment("Load surviving Schools for each type").purpose("Exclude tombstones from ordinary child lists")
.execute_for_list(context))
report("deleted-children", {E.school_type(t).code().eval(): [E.school_type(t).school_list().get(i).name().eval()
for i in range(E.school_type(t).school_list().size().eval())] for t in types})
Deleted-only children preserve live parents
Explicit deleted-only child selection yields an empty PRIMARY list and Hill under SECONDARY. Both live parents remain visible; the child version filter cannot change parent visibility.
types = await (Q.school_types_minimal().select_code()
.select_school_list_with(Q.schools_minimal().select_name().deleted_rows_only()
.order_by_student_capacity_descending().order_by_id_ascending().limit(2))
.order_by_code_ascending().limit(10)
.comment("Load retired Schools for each live type").purpose("Keep child deletion scope separate from parent visibility")
.execute_for_list(context))
report("deleted-child-tombstones", {E.school_type(t).code().eval(): [E.school_type(t).school_list().get(i).name().eval()
for i in range(E.school_type(t).school_list().size().eval())] for t in types})
Continuous pages in ascending ID order
Read offsets 0, 2 and 4 in one trusted context. The first page uses OFFSET; the next two report CURSOR_SEEK without duplicates or omissions.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(0, 0), (2, 0), (4, 0)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_id_ascending().offset(offset).limit(2).optimize_for_continuous_page_fetch_with("continuous-ascending", 60)
.comment("Browse School pagination").purpose("Verify continuous-ascending").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.continuous_page_plan())
report("continuous-ascending", {"pages": pages, "plans": plans})
Continuous pages in descending ID order
Traverse the same five Schools in reverse ID order and observe two CURSOR_SEEK plans.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(0, 0), (2, 0), (4, 0)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_id_descending().offset(offset).limit(2).optimize_for_continuous_page_fetch_with("continuous-descending", 60)
.comment("Browse School pagination").purpose("Verify continuous-descending").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.continuous_page_plan())
report("continuous-descending", {"pages": pages, "plans": plans})
Continuous cursor miss and recovery
Start at offset 2 without a cursor: observe CACHE_MISS, then reuse its boundary at offset 4.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(2, 0), (4, 0)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_id_ascending().offset(offset).limit(2).optimize_for_continuous_page_fetch_with("continuous-cache-miss", 60)
.comment("Browse School pagination").purpose("Verify continuous-cache-miss").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.continuous_page_plan())
report("continuous-cache-miss", {"pages": pages, "plans": plans})
Retained ID-set random page jumps
Build at offset 4, then jump to 0, 2 and 6. Preserve capacity/name/ID order, exact total 5 and ID_SET_HIT even for the empty tail.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(4, 0), (0, 0), (2, 0), (6, 0)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_student_capacity_descending().order_by_name_ascending().order_by_id_ascending().offset(offset).limit(2).optimize_pagination_with_id_set_config("id-set-jump", 60, 100).count()
.comment("Browse School pagination").purpose("Verify id-set-jump").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.id_set_plan())
totals.append(page.total_count); observed_counts.append(context.id_set_count()[0])
report("id-set-jump", {"pages": pages, "plans": plans, "totals": totals, "observedCounts": observed_counts})
Retained empty ID set
Run the same unmatched predicate twice. Retain the empty set, report total 0 and transition from ID_SET_BUILD to ID_SET_HIT.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(0, 100000), (2, 100000)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_student_capacity_descending().order_by_name_ascending().order_by_id_ascending().offset(offset).limit(2).optimize_pagination_with_id_set_config("id-set-empty", 60, 100).count()
.comment("Browse School pagination").purpose("Verify id-set-empty").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.id_set_plan())
totals.append(page.total_count); observed_counts.append(context.id_set_count()[0])
report("id-set-empty", {"pages": pages, "plans": plans, "totals": totals, "observedCounts": observed_counts})
ID-set safety-limit fallback
Set maxIds to 3 for five matching Schools. Observe LIMIT_EXCEEDED with a retained-count lower bound of 4; ordinary counted pagination still returns the correct page and exact total 5.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(2, 0)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_student_capacity_descending().order_by_name_ascending().order_by_id_ascending().offset(offset).limit(2).optimize_pagination_with_id_set_config("id-set-overflow", 60, 3).count()
.comment("Browse School pagination").purpose("Verify id-set-overflow").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.id_set_plan())
totals.append(page.total_count); observed_counts.append(context.id_set_count()[0])
report("id-set-overflow", {"pages": pages, "plans": plans, "totals": totals, "observedCounts": observed_counts})
ID-set predicate isolation
Use one namespace with capacity thresholds 500, 1000 and 500. Distinct predicates build separate sets; returning to 500 hits its original set with exact total 4.
pages, plans, totals, observed_counts = [], [], [], []
for offset, threshold in [(0, 500), (0, 1000), (0, 500)]:
page = await (Q.schools_minimal().select_name().with_student_capacity_greater_than_or_equal_to(threshold)
.order_by_student_capacity_descending().order_by_name_ascending().order_by_id_ascending().offset(offset).limit(2).optimize_pagination_with_id_set_config("id-set-isolation", 60, 100).count()
.comment("Browse School pagination").purpose("Verify id-set-isolation").execute_for_list(context))
pages.append([E.school(s).name().eval() for s in page])
plans.append(context.id_set_plan())
totals.append(page.total_count); observed_counts.append(context.id_set_count()[0])
report("id-set-isolation", {"pages": pages, "plans": plans, "totals": totals, "observedCounts": observed_counts})
Atomic Platform and School commit
The commit control changes Platform to Transactional Campus and Riverside to capacity 810;
both versions become 2. The runner clones the seeded database for each case.
Rust uses one typed transaction capability for both audited saves; other runtimes
save the loaded Platform graph and its School child.
rollback = os.environ["SCHOOL_SCENARIO"] == "transaction-rollback"
root = await (Q.platforms().select_school_list_with(Q.schools().with_name_is("Riverside Primary School").limit(2))
.limit(2).comment("Load the Platform and School transaction graph").purpose("Verify atomic graph writes").execute_for_one(context))
assert root is not None
child = root.school_list()[0]
if rollback:
winner = await load(context, "Riverside Primary School")
winner.update_student_capacity(801)
await winner.audit_as("Advance School before the stale graph").save(context)
root.update_name("Transactional Campus")
child.update_student_capacity(810)
print("SCHOOL_GRAPH_ATTEMPT", flush=True)
rejected = False
try:
await root.audit_as("Revise Platform and School atomically").save(context)
except Exception as error:
assert rollback and any(w in str(error).lower() for w in ("version", "optimistic", "conflict", "stale", "authoritative persisted state")), str(error)
rejected = True
report(os.environ["SCHOOL_SCENARIO"], {"rejected": rejected})
Multi-entity rollback
The same block first commits an independent School revision to capacity 801/version 2, then attempts the stale Platform/School graph. Require an optimistic conflict. The SQL trace must show Platform UPDATE before the failing School UPDATE. The read-only database check requires the original Platform name, version and update time, and keeps the independently committed School revision. No compensating writes are used.
Concurrent writes to one School
Two separate processes load version 1, write readiness records, then wait for the runner to release a shared barrier. Exactly one save commits and one reports an optimistic conflict. The final capacity must match the actual winner (811 or 822), at version 2. A locked/busy database error fails the test; it is never counted as an optimistic conflict.
target, worker, barrier = os.environ["SCHOOL_TARGET"], os.environ["SCHOOL_WORKER"], os.environ["SCHOOL_BARRIER"]
row = await load(context, target)
observed_version = E.school(row).version().eval()
with open(f"{barrier}/{worker}.ready", "w") as ready: json.dump({"observedVersion": observed_version}, ready)
deadline = asyncio.get_running_loop().time() + 30
while not os.path.exists(f"{barrier}/release"):
assert asyncio.get_running_loop().time() < deadline, "Barrier timeout"
await asyncio.sleep(0.01)
row.update_student_capacity(811 if worker == "a" else 822)
outcome, conflict_reason = "committed", ""
try:
await row.audit_as("Concurrent School capacity revision").save(context)
except Exception as error:
assert any(w in str(error).lower() for w in ("version", "optimistic", "conflict", "stale", "authoritative persisted state")), str(error)
outcome, conflict_reason = "conflict", str(error)
print("SCHOOL_WORKER " + json.dumps({"worker": worker, "target": target, "observedVersion": observed_version, "outcome": outcome, "conflictReason": conflict_reason}))
Concurrent writes to different Schools
The same worker block targets Riverside for worker a and North for worker b. Both must commit, leaving capacities 811 and 822 at version 2. All other rows, relations, identity and creation time must be unchanged.
Audited update and stale version
The complete program loads Riverside twice, changes the first copy from 800 to 950, and saves it with an audit reason. Its version must change from 1 to 2. Saving the independently loaded stale copy at 999 must fail; a fresh process must still read capacity 950 and version 2. The read-only SQLite checker verifies that no other School changed and that IDs and creation timestamps remain stable.
Soft deletion and restart
The program marks Hill for deletion and saves with an audit reason. Normal queries must return zero rows; after restart, the deleted-only query must return exactly one tombstone. SQLite must retain Hill at version -2. The published TypeScript 0.3.1 pair fails that final query in the earlier receipt. The qualified TypeScript workspace fix passes it; the expected value remains 1.
Scope of this check
These are local SQLite results for the exact recorded dependencies. They do not certify PostgreSQL/MySQL, sustained concurrency stress, browser or TFP execution, pagination TTL/invalidation, concurrent retained-set changes, provider/commit fault injection, the complete Null/NotLoaded projection contract, migrations, or deployment-specific access policies. See the prioritized remaining gaps. The older stale-write check uses sequential copies; the new concurrent cases use two synchronized native processes. This is not a sustained-write stress test. Runtime and generator release status remain separate in the receipt.