系統設計

1type Line = { orderId: number; itemId: number; qty: number; price: number };
2type DocLine = { itemId: number; name: string; qty: number; price: number };
3type DocOrder = { id: number; created: number; lines: DocLine[] };
4type Item = { name: string; price: number; stock: number };
5type Want = { itemId: number; qty: number };
6 
7// orders(id, user_id, created), order_lines(order_id, item_id, qty, price),
8// items(id, name, price, stock), INDEX orders(user_id, created)
9type Sql = { byUser: Map<number, number[]>; lines: Map<number, Line[]>; items: Map<number, Item> };
10// users/{id} = [ { id, created, lines: [ { itemId, name, qty, price } ] } ]
11type Docs = { users: Map<number, DocOrder[]>; items: Map<number, Item> };
12// orders_by_user: PRIMARY KEY ((user_id), created DESC)
13type Wide = { partitions: Map<number, DocOrder[]>; items: Map<number, Item> };
14 
15// SELECT ... FROM orders JOIN order_lines JOIN items
16//   WHERE user_id = ? ORDER BY created DESC LIMIT ?
17function recentSql(db: Sql, userId: number, limit: number): number[] {
18  const ids = (db.byUser.get(userId) ?? []).slice(-limit).reverse();
19  for (const id of ids) for (const l of db.lines.get(id) ?? []) db.items.get(l.itemId);
20  return ids;
21}
22 
23// One read, but it brings back every order the user ever placed.
24function recentDoc(db: Docs, userId: number, limit: number): number[] {
25  const orders = db.users.get(userId) ?? [];
26  return [...orders].sort((a, b) => b.created - a.created).slice(0, limit).map((o) => o.id);
27}
28 
29// The partition is stored newest first: read its first rows.
30function recentWide(db: Wide, userId: number, limit: number): number[] {
31  return (db.partitions.get(userId) ?? []).slice(0, limit).map((o) => o.id);
32}
33 
34// UPDATE items SET name = ? WHERE id = ?
35function renameSql(db: Sql, itemId: number, name: string): number {
36  db.items.get(itemId)!.name = name;
37  return 1;
38}
39 
40// The name was copied into orders: rewrite every document that has it.
41function renameDoc(db: Docs, itemId: number, name: string): number {
42  db.items.get(itemId)!.name = name;
43  let written = 1;
44  for (const orders of db.users.values()) {
45    const hits = orders.flatMap((o) => o.lines).filter((l) => l.itemId === itemId);
46    hits.forEach((l) => (l.name = name));
47    if (hits.length > 0) written++;
48  }
49  return written;
50}
51 
52// Same in every partition, row by row.
53function renameWide(db: Wide, itemId: number, name: string): number {
54  db.items.get(itemId)!.name = name;
55  let written = 1;
56  for (const rows of db.partitions.values()) for (const row of rows) {
57    const hits = row.lines.filter((l) => l.itemId === itemId);
58    hits.forEach((l) => (l.name = name));
59    if (hits.length > 0) written++;
60  }
61  return written;
62}
63 
64// SELECT item_id, SUM(qty * price) FROM order_lines GROUP BY item_id
65function revenueSql(db: Sql): Map<number, number> {
66  const totals = new Map<number, number>();
67  for (const lines of db.lines.values()) for (const l of lines) {
68    totals.set(l.itemId, (totals.get(l.itemId) ?? 0) + l.qty * l.price);
69  }
70  return totals;
71}
72 
73// No GROUP BY across partitions: read them all and add up here.
74function revenueScan(groups: Iterable<DocOrder[]>): Map<number, number> {
75  const totals = new Map<number, number>();
76  for (const orders of groups) for (const o of orders) for (const l of o.lines) {
77    totals.set(l.itemId, (totals.get(l.itemId) ?? 0) + l.qty * l.price);
78  }
79  return totals;
80}
81 
82// BEGIN; check stock; INSERT order, lines; UPDATE stock; COMMIT.
83function placeOrderSql(db: Sql, userId: number, orderId: number, want: Want[]): boolean {
84  if (!want.every((w) => db.items.get(w.itemId)!.stock >= w.qty)) return false;
85  db.byUser.get(userId)!.push(orderId);
86  db.lines.set(orderId, want.map((w) => ({ orderId, itemId: w.itemId, qty: w.qty, price: db.items.get(w.itemId)!.price })));
87  for (const w of want) db.items.get(w.itemId)!.stock -= w.qty;
88  return true;
89}
90 
91// The order is on the user's shard and the stock on the items' shards:
92// keeping them in step takes a multi-document transaction, or a saga.
93function placeOrderDoc(db: Docs, userId: number, orderId: number, want: Want[]): boolean {
94  if (!want.every((w) => db.items.get(w.itemId)!.stock >= w.qty)) return false;
95  const lines = want.map((w) => ({ itemId: w.itemId, name: db.items.get(w.itemId)!.name, qty: w.qty, price: db.items.get(w.itemId)!.price }));
96  db.users.get(userId)!.push({ id: orderId, created: orderId, lines });
97  for (const w of want) db.items.get(w.itemId)!.stock -= w.qty;
98  return true;
99}
100 
101// A batch inside one partition is atomic; the stock rows are elsewhere.
102function placeOrderWide(db: Wide, userId: number, orderId: number, want: Want[]): boolean {
103  if (!want.every((w) => db.items.get(w.itemId)!.stock >= w.qty)) return false;
104  const lines = want.map((w) => ({ itemId: w.itemId, name: db.items.get(w.itemId)!.name, qty: w.qty, price: db.items.get(w.itemId)!.price }));
105  db.partitions.get(userId)!.unshift({ id: orderId, created: orderId, lines });
106  for (const w of want) db.items.get(w.itemId)!.stock -= w.qty;
107  return true;
108}