Confluxys

← Back to Home

Minecraft

Minecraft as an Abstraction Layer over an RDBMS

Abstract

Claim: Vanilla Minecraft implements a coherent data system whose entities, storage, execution model, and migration pipeline correspond to standard RDBMS constructs. It is not a DBMS and does not expose SQL, but it satisfies the core mapping: world state ↔ relational data; tick loop ↔ serial scheduler; NBT/registries ↔ schema; DataFixerUpper ↔ migrations; redstone/events ↔ triggers; hoppers/golems ↔ ETL jobs. This thesis formalizes the mapping, identifies gaps, and specifies additions required to expose first-class query semantics. The recent addition of the Copper Golem (item sorter) further narrows the gap.

1) Data Model

2) Physical Storage

3) Execution and Concurrency

4) Constraints and Invariants

5) Query, Indexing, and Triggers (Exposed Mechanics)

6) ETL, Workflows, and Background Jobs

7) Schema and Migration

8) Reliability Characteristics

9) Formal Relational Mapping (Canonical Tables)

Illustrative logical schema:

sql-- Spatial partitions
CREATE TABLE chunk (
  dimension INTEGER,
  cx INTEGER, cz INTEGER,
  PRIMARY KEY (dimension, cx, cz)
);

-- Block entities (sparse)
CREATE TABLE block_entity (
  dimension INTEGER, x INTEGER, y INTEGER, z INTEGER,
  type TEXT, nbt JSON,             -- NBT serialized to JSON in this abstraction
  PRIMARY KEY (dimension, x, y, z),
  FOREIGN KEY (dimension, cx, cz) REFERENCES chunk(dimension, cx, cz)
    GENERATED ALWAYS AS ( (x>>4) AS cx, (z>>4) AS cz ) -- conceptual
);

-- Entities
CREATE TABLE entity (
  uuid TEXT PRIMARY KEY,
  dimension INTEGER, x REAL, y REAL, z REAL,
  type TEXT, nbt JSON
);

-- Inventories (containers or entities)
CREATE TABLE inventory (
  owner_type TEXT, owner_id TEXT, slot INTEGER,
  item_id TEXT, count INTEGER, item_nbt JSON,
  PRIMARY KEY (owner_type, owner_id, slot)
);

-- Catalogs
CREATE TABLE registry_item (kind TEXT, id TEXT PRIMARY KEY, props JSON);
CREATE TABLE tag_membership (tag TEXT, id TEXT, PRIMARY KEY (tag, id));

Selectors and comparators map to:

Redstone comparator on a chest corresponds to:

A hopper move is an INSERT … ON CONFLICT DO UPDATE with capacity checks.

10) Optimization Surrogates

11) Missing Capabilities vs RDBMS

12) Exposing Queries with Minimal Additions (Vanilla-compatible design)

13) Practical Consequences

14) Conclusion

Minecraft is a consistent, operational abstraction of an RDBMS:

It omits SQL, ACID across multi-chunk scopes, and a cost-based planner. Adding cataloged containers, a request API, and agent routing persistence would expose first-class query semantics while preserving survival gameplay constraints. The Copper Golem's introduction as an item sorter exemplifies this potential.

The following is a SQL query that achieves the same result as a Copper Golem sorting items into labeled chests:

BEGIN IMMEDIATE;

-- Staging for calculated moves
DROP TABLE IF EXISTS temp.tmp_moves;
CREATE TEMP TABLE temp.tmp_moves (
  item_id  INTEGER NOT NULL,
  chest_id INTEGER NOT NULL,
  qty      INTEGER NOT NULL CHECK (qty > 0)
);

-- Plan moves: allocate inbox quantities into candidate chests without exceeding capacity
INSERT INTO temp.tmp_moves (item_id, chest_id, qty)
WITH
  i AS (
    SELECT item_id, quantity AS inbox_qty
    FROM inbox_items
    WHERE quantity > 0
  ),
  cf AS (
    SELECT
      c.chest_id,
      c.capacity - COALESCE(SUM(ci.quantity), 0) AS free,
      CASE WHEN COUNT(ci.item_id) = 0 THEN 1 ELSE 0 END AS is_empty
    FROM chests c
    LEFT JOIN chest_items ci ON ci.chest_id = c.chest_id
    GROUP BY c.chest_id
    HAVING free > 0
  ),
  -- chests that already hold the item (preferred)
  has_item AS (
    SELECT DISTINCT
      i.item_id,
      cf.chest_id,
      cf.free,
      0 AS grp
    FROM i
    JOIN cf
    JOIN chest_items ci
      ON ci.chest_id = cf.chest_id
     AND ci.item_id  = i.item_id
  ),
  -- if NONE hold the item, fallback to truly empty chests
  fallback AS (
    SELECT
      i.item_id,
      cf.chest_id,
      cf.free,
      1 AS grp
    FROM i
    JOIN cf ON cf.is_empty = 1
    WHERE NOT EXISTS (
      SELECT 1 FROM chest_items x
      WHERE x.item_id = i.item_id
    )
  ),
  candidates AS (
    SELECT * FROM has_item
    UNION ALL
    SELECT * FROM fallback
  ),
  alloc AS (
    SELECT
      c.item_id,
      c.chest_id,
      c.free,
      it.inbox_qty,
      SUM(c.free) OVER (
        PARTITION BY c.item_id
        ORDER BY c.grp, c.chest_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
      ) AS cum_prev
    FROM candidates c
    JOIN i it USING (item_id)
  ),
  moves AS (
    SELECT
      item_id,
      chest_id,
      CASE
        WHEN inbox_qty - COALESCE(cum_prev, 0) <= 0
             THEN 0
        WHEN inbox_qty - COALESCE(cum_prev, 0) >= free
             THEN free
        ELSE inbox_qty - COALESCE(cum_prev, 0)
      END AS qty
    FROM alloc
  )
SELECT item_id, chest_id, qty
FROM moves
WHERE qty > 0;

-- Apply moves to destination chests (UPSERT)
INSERT INTO chest_items (chest_id, item_id, quantity)
SELECT chest_id, item_id, qty
FROM temp.tmp_moves
ON CONFLICT (chest_id, item_id) DO UPDATE
SET quantity = quantity + excluded.quantity;

-- Deduct from inbox
UPDATE inbox_items AS ib
SET quantity = quantity - (
  SELECT COALESCE(SUM(m.qty), 0)
  FROM temp.tmp_moves m
  WHERE m.item_id = ib.item_id
)
WHERE ib.item_id IN (SELECT item_id FROM temp.tmp_moves);

-- Cleanup empty inbox rows
DELETE FROM inbox_items WHERE quantity <= 0;

DROP TABLE IF EXISTS temp.tmp_moves;

COMMIT;

Supplement: “If u put a dropper that connects both chests he just works forever” --kittenself 9/25

1. Phenomenon (game-level)

2. RDBMS mapping

3. Dynamics (formal)

Let r\_g = average items/tick moved by J1 (A→B).  
Let r\_d = average items/tick injected by T1 (B→A).

4. Bug or feature?

5. Operational risks (both worlds)

6. Control patterns (how to make it sane)

7. When to keep the loop (legitimate uses)

8. When to break the loop (most cases)

9. Minimal SQL sketch (for contrast):

-- BAD: feedback trigger (B->A) creates infinite work for J1
CREATE TRIGGER b_to_a_feedback
AFTER INSERT ON B
BEGIN
  INSERT INTO A(item_id, qty)
  VALUES (NEW.item_id, NEW.qty);
END;

-- BETTER: guard recursion (only when A is below threshold)
CREATE TRIGGER b_to_a_feedback_guarded
AFTER INSERT ON B
WHEN (SELECT COALESCE(SUM(qty),0) FROM A) = 0
BEGIN
  INSERT INTO A(item_id, qty) VALUES (NEW.item_id, NEW.qty);
END;