Strategies

Every query that arrives at Mori is classified, routed to a strategy, and executed against the appropriate backend(s). This page is the exhaustive reference for how each operation type is handled.

​

Query Classification

The classifier parses each incoming statement to extract:

• OpType — READ, WRITE, DDL, TRANSACTION, or OTHER
• SubType — specific operation (e.g., SELECT, INSERT, UPDATE, DELETE, ALTER, BEGIN)
• Tables — all tables referenced in the query
• Primary Keys — extractable PKs from WHERE clauses (e.g., WHERE id = 42)
• Feature flags — IsJoin, HasAggregate, HasSetOp, IsComplexRead, HasReturning, HasOnConflict, HasWindowFunc, HasDistinct, HasCursor, IsMetadataQuery

​

Classification by Engine

​

Statement Types (SQL Engines)

​

Unsupported Operations by Engine

​

Routing Decision Matrix

The router takes the classification output plus the current delta/tombstone/schema state and selects one of 12 execution strategies.

​

Strategies

​

Decision Table (SQL Engines)

​

Decision Table (Redis)

Redis uses the same strategy model, but classification is command-based rather than SQL-based.

​

Decision Table (Firestore)

Firestore classification is gRPC method-based.

---

​

Read Operations

​

Prod Direct

When a SELECT touches only tables with no local mutations, the query passes straight through to prod. Zero overhead beyond the proxy hop.

​

Merged Read (Single Table)

When a SELECT touches a table with delta or tombstone entries: PK injection: The proxy injects PK columns into the SELECT if they aren’t already present (needed for deduplication). They’re stripped from the final output. LIMIT over-fetching: Prod queries add delta_count + tombstone_count to the LIMIT to compensate for rows that will be filtered out.

​

Join Patch

When a SELECT joins tables and at least one has local mutations: Schema diff handling for JOINs: When joined tables have schema changes, dirty tables are materialized into temp tables and the JOIN is rewritten to reference them.

​

Aggregates

Aggregate queries (COUNT, SUM, AVG, MIN, MAX with GROUP BY) can’t be merged by simply combining prod and shadow results — aggregation must be re-applied on the combined data. Approach:

1. Build a row-level base query (strip aggregates, keep GROUP BY columns and source columns)
2. Run the base query through the merged read pipeline to get all rows
3. Materialize into a temp table on shadow
4. Re-aggregate in the proxy (in-memory grouping + function application)
5. Apply HAVING filters on re-aggregated results

Complex aggregates (array_agg, json_agg, string_agg, etc.) materialize into a temp table and re-execute the original query against it on shadow, leveraging the database’s native aggregate implementation.

​

Complex Reads

CTEs (WITH clause):

• Non-recursive CTEs: Materialize dirty CTEs independently into temp tables
• Recursive CTEs: Materialize dirty base tables, rewrite query to reference temp tables

Derived Tables (subqueries in FROM):

• Recursively rewrite subqueries, replacing dirty subquery results with temp tables

LATERAL Joins:

• Materialize referenced dirty base tables (not the LATERAL body itself)
• Preserve LATERAL semantics through table materialization

​

Set Operations

UNION, INTERSECT, EXCEPT are decomposed into leaf SELECT nodes. Each leaf runs through the merged read pipeline independently, then in-memory set operations are applied:

• UNION: Combine + deduplicate
• UNION ALL: Combine without dedup
• INTERSECT: Keep rows in both sets
• EXCEPT: Keep rows only in first set

​

Window Functions

Window functions require the complete dataset to compute correctly — they can’t be decomposed.

1. Build a base SELECT (same FROM/WHERE, no window functions)
2. Run base through merged read pipeline
3. Materialize result into temp table on shadow
4. Rewrite original query to read from temp table
5. Execute on shadow (leverages the database’s native window implementation)

​

Cursors

• DECLARE CURSOR: Classifies inner query; materializes if affected tables
• FETCH: Reads from cursor on the appropriate backend
• CLOSE: Closes cursor on both backends

---

​

Write Operations

All writes execute on shadow. Production is never modified.

​

INSERT

Simple INSERT (Shadow Write):

1. Enforce FK constraints (validate parent rows exist)
2. Execute INSERT on shadow
3. Capture RETURNING clause to extract inserted PKs for precise delta tracking
4. Add all inserted PKs to the Delta Map
5. Persist state

Shadow’s offset sequences assign PKs in the local range (e.g., 10,000,001+), guaranteed to not collide with prod. Upsert — INSERT … ON CONFLICT (Hydrate and Write):

1. Detect conflict key columns
2. Check prod for rows matching conflict keys
3. Hydrate matching prod rows into shadow
4. Execute upsert on shadow
5. Track all affected rows (hydrated + newly inserted) in Delta Map

MySQL-specific: MySQL lacks RETURNING. INSERT PK extraction uses a 3-tier approach: (1) extract from VALUES clause, (2) use LAST_INSERT_ID(), (3) fall back to information_schema PK lookup. MySQL-specific: INSERT ... ON DUPLICATE KEY UPDATE and REPLACE INTO route through the hydrate-and-write path.

​

UPDATE

Point Update (PK extractable from WHERE, e.g., WHERE id = 42):

1. Enforce FK constraints on SET values
2. Check if row exists in shadow — if not, hydrate from prod
3. Execute UPDATE on shadow
4. Add affected PK to Delta Map

Bulk Update (PK not extractable, e.g., WHERE status = 'pending'):

1. Query prod for all matching rows (with schema rewriting for prod compatibility)
2. Hydrate all matching rows into shadow
3. Rewrite UPDATE WHERE clause with concrete PK values
4. Execute on shadow
5. Track all affected PKs in Delta Map

Cross-table Updates (UPDATE … FROM):

• Extract subquery predicates from FROM clause
• Hydrate referenced tables before applying the update

​

DELETE

Point Delete (PK extractable):

1. Enforce FK RESTRICT/NO ACTION constraints (reject if child rows exist)
2. Execute DELETE on shadow
3. Add tombstone for each affected PK
4. Enforce FK CASCADE (recursively tombstone/delete child rows)
5. Return correct row count (including prod-only rows)

Bulk Delete (PK not extractable):

1. Pre-query prod for matching PKs
2. Compute total count: shadow actual deletes + prod-only matches
3. Tombstone all matching PKs
4. Execute DELETE on shadow

DELETE … RETURNING: Hydrates from prod before deletion to synthesize return data.

​

TRUNCATE

1. Execute on shadow
2. Mark table as fully shadowed in Schema Registry — all subsequent reads skip prod entirely
3. Clear all tombstones for the table
4. CASCADE: recursively mark FK-related tables as fully shadowed

---

​

DDL Handling

DDL operations execute on shadow only. Production schema is never modified.

1. Strip FK constraints from DDL before shadow execution (the proxy enforces FKs instead)
2. Extract and store FK metadata in Schema Registry
3. Execute modified DDL on shadow
4. Update Schema Registry with the structural change

---

​

Transaction Management

​

Lifecycle

Prod uses REPEATABLE READ isolation to ensure consistent reads across the transaction — the merged read always sees a stable prod snapshot.

​

Engine-Specific Transaction Behavior

​

Staged Deltas

Within a transaction, all delta/tombstone additions are staged — held in a per-transaction buffer. This prevents phantom deltas from failed transactions: if a transaction rolls back, the Delta Map and Tombstone Set remain unchanged. Autocommit mode (implicit single-statement transactions) applies deltas immediately — no staging needed.

​

Savepoints

Savepoints are fully supported (SQL engines) with stack-based snapshot management:

• SAVEPOINT name: Capture current delta/tombstone/schema/insert-count state, push onto stack
• RELEASE SAVEPOINT name: Pop snapshot from stack (changes persist)
• ROLLBACK TO SAVEPOINT name: Restore state from snapshot, keep savepoint on stack for re-use

---

​

Foreign Key Enforcement

​

Why FKs are Stripped

Shadow starts empty. INSERTs referencing prod rows via foreign key would fail because the referenced row doesn’t exist in shadow. The proxy enforces referential integrity instead.

​

Parent Row Validation (INSERT/UPDATE)

Validation order (fast-path first):

1. Delta map check: If parent PK in delta → exists in shadow
2. Tombstone check: If parent PK tombstoned → REJECT
3. Shadow query: Check shadow for parent row
4. Prod query: Fall back to prod if not found in shadow

Supports multi-column (composite) foreign keys.

​

Referential Action Enforcement (DELETE)

• RESTRICT / NO ACTION: Reject parent delete if child rows exist (checks shadow + prod)
• CASCADE: Tombstone child rows when parent deleted, recursively cascade
• SET NULL / SET DEFAULT: Falls through to shadow enforcement

---

​

Known Limitations