单店点餐与多商户外卖平台有什么区别？从订单隔离与退款归属核对

import sqlite3

DB = sqlite3.connect(":memory:")
DB.execute("PRAGMA foreign_keys=ON")
DB.executescript("""
CREATE TABLE merchants (id INTEGER PRIMARY KEY);
CREATE TABLE orders (
  merchant_id INTEGER NOT NULL,
  order_id TEXT NOT NULL,
  amount_cent INTEGER NOT NULL CHECK(amount_cent >= 0),
  PRIMARY KEY(merchant_id, order_id),
  FOREIGN KEY(merchant_id) REFERENCES merchants(id)
);
CREATE TABLE refunds (
  merchant_id INTEGER NOT NULL,
  refund_id TEXT NOT NULL,
  order_id TEXT NOT NULL,
  PRIMARY KEY(merchant_id, refund_id),
  FOREIGN KEY(merchant_id, order_id)
    REFERENCES orders(merchant_id, order_id)
);
INSERT INTO merchants VALUES (1), (2);
INSERT INTO orders VALUES (1, 'same', 1000), (2, 'same', 2200),
                          (2, 'b-only', 3300);
""")

def order_for(authenticated_merchant, order_id):
    # authenticated_merchant comes from a verified server-side session.
    row = DB.execute(
        "SELECT amount_cent FROM orders WHERE merchant_id=? AND order_id=?",
        (authenticated_merchant, order_id),
    ).fetchone()
    if row is None:
        raise LookupError("not found in merchant scope")
    return row[0]

def create_refund(authenticated_merchant, refund_id, order_id):
    with DB:
        DB.execute(
            "INSERT INTO refunds VALUES (?, ?, ?)",
            (authenticated_merchant, refund_id, order_id),
        )

assert order_for(1, 'same') == 1000
assert order_for(2, 'same') == 2200
try:
    order_for(1, 'b-only')
    raise AssertionError("cross-merchant read allowed")
except LookupError:
    pass
create_refund(1, 'r1', 'same')
try:
    create_refund(1, 'r2', 'b-only')
    raise AssertionError("cross-merchant reference allowed")
except sqlite3.IntegrityError:
    pass
assert DB.execute("SELECT COUNT(*) FROM refunds").fetchone()[0] == 1
assert DB.execute("PRAGMA foreign_keys").fetchone()[0] == 1
# A query without merchant scope returns both merchants' rows: a counterexample.
assert len(DB.execute("SELECT * FROM orders WHERE order_id='same'").fetchall()) == 2
print("7 checks passed: scoped read, denial, composite reference, counterexample")

7 checks passed: scoped read, denial, composite reference, counterexample