1"""Chapter 8 practice: the bookstore as relational tables (sqlite3) vs documents
2(Level 3 Build), and the 'two separate commits' bug (Level 4 Debug).
3Standard library only. Run: python3 ch08_sql_nosql.py"""
4import copy
5import sqlite3
6
7SCHEMA = """
8CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
9CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL,
10 author_id INTEGER NOT NULL REFERENCES authors(id),
11 price INTEGER NOT NULL CHECK (price >= 0),
12 stock INTEGER NOT NULL CHECK (stock >= 0));
13CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
14CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id));
15CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id),
16 book_id INTEGER NOT NULL REFERENCES books(id),
17 qty INTEGER NOT NULL CHECK (qty > 0),
18 price INTEGER NOT NULL,
19 PRIMARY KEY (order_id, book_id));
20"""
21
22
23def make_db():
24 db = sqlite3.connect(":memory:")
25 db.execute("PRAGMA foreign_keys = ON")
26 db.executescript(SCHEMA)
27 db.executemany("INSERT INTO authors VALUES (?, ?)", [(1, "R. Rao"), (2, "M. Iyer")])
28 db.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?)", [
29 (42, "Designing Systems", 1, 499, 10),
30 (43, "Queues in Practice", 1, 399, 5),
31 (44, "Caching for Humans", 2, 299, 0),
32 ])
33 db.execute("INSERT INTO users VALUES (7, 'asha@example.com')")
34 db.commit()
35 return db
36
37
38def place_order(db, user_id, items):
39 """items: [(book_id, qty)]. All-or-nothing: the order is saved and stock is reduced
40 together, or nothing changes at all."""
41 with db: # one transaction
42 order_id = db.execute("INSERT INTO orders (user_id) VALUES (?)", (user_id,)).lastrowid
43 for book_id, qty in items:
44 price = db.execute("SELECT price FROM books WHERE id = ?", (book_id,)).fetchone()[0]
45 db.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)", (order_id, book_id, qty, price))
46 db.execute("UPDATE books SET stock = stock - ? WHERE id = ?", (qty, book_id))
47 # CHECK (stock >= 0) makes this fail if there isn't enough, rolling everything back
48 return order_id
49
50
51def order_total(db, order_id):
52 row = db.execute("""
53 SELECT SUM(oi.qty * oi.price)
54 FROM order_items oi JOIN orders o ON o.id = oi.order_id
55 WHERE o.id = ?""", (order_id,)).fetchone()
56 return row[0]
57
58
59def books_by_author(db, author_name):
60 return [r[0] for r in db.execute("""
61 SELECT b.title FROM books b JOIN authors a ON a.id = b.author_id
62 WHERE a.name = ? ORDER BY b.id""", (author_name,))]
63
64
65# ---- The same data as documents (denormalized) ---------------------------------------
66
67def order_documents(n_orders):
68 """Each order document embeds a copy of the author's name, the way a document store
69 would to avoid joins when showing an order."""
70 template = {"user": {"id": 7, "email": "asha@example.com"},
71 "items": [{"book_id": 42, "title": "Designing Systems",
72 "author": "R. Rao", "qty": 1, "price": 499}]}
73 return [dict(copy.deepcopy(template), _id=i) for i in range(n_orders)]
74
75
76def rename_author_in_documents(docs, old, new):
77 touched = 0
78 for d in docs:
79 for item in d["items"]:
80 if item["author"] == old:
81 item["author"] = new
82 touched += 1
83 return touched
84
85
86# ---- Level 4: two separate commits -------------------------------------------------------
87
88def buggy_place_order(db, user_id, book_id, qty, crash_before_stock=False):
89 order_id = db.execute("INSERT INTO orders (user_id) VALUES (?)", (user_id,)).lastrowid
90 price = db.execute("SELECT price FROM books WHERE id = ?", (book_id,)).fetchone()[0]
91 db.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)", (order_id, book_id, qty, price))
92 db.commit() # BUG: the order is saved on its own...
93 if crash_before_stock:
94 raise RuntimeError("server crashed") # ...so a crash here leaves stock untouched
95 db.execute("UPDATE books SET stock = stock - ? WHERE id = ?", (qty, book_id))
96 db.commit()
97 return order_id
98
99
100def run_checks():
101 db = make_db()
102 oid = place_order(db, 7, [(42, 2), (43, 1)])
103 assert order_total(db, oid) == 2 * 499 + 399
104 assert db.execute("SELECT stock FROM books WHERE id = 42").fetchone()[0] == 8
105 assert books_by_author(db, "R. Rao") == ["Designing Systems", "Queues in Practice"]
106
107 # Atomicity: book 44 has no stock, so the whole order is rolled back, including book 42.
108 try:
109 place_order(db, 7, [(42, 1), (44, 1)])
110 raise AssertionError("should have failed")
111 except sqlite3.IntegrityError:
112 pass
113 assert db.execute("SELECT stock FROM books WHERE id = 42").fetchone()[0] == 8
114 assert db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 1
115
116 # Constraints: a duplicate email and an order for a missing user are rejected.
117 for sql in ("INSERT INTO users VALUES (8, 'asha@example.com')",
118 "INSERT INTO orders (user_id) VALUES (999)"):
119 try:
120 db.execute(sql)
121 raise AssertionError("constraint not enforced: " + sql)
122 except sqlite3.IntegrityError:
123 pass
124
125 # Renaming an author: 1 row in SQL vs every embedded copy in documents.
126 with db:
127 changed = db.execute("UPDATE authors SET name = 'R. Rao-Menon' WHERE id = 1").rowcount
128 assert changed == 1
129 docs = order_documents(10_000)
130 assert rename_author_in_documents(docs, "R. Rao", "R. Rao-Menon") == 10_000
131
132 # Level 4
133 db2 = make_db()
134 try:
135 buggy_place_order(db2, 7, 42, 3, crash_before_stock=True)
136 except RuntimeError:
137 pass
138 orders = db2.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
139 stock = db2.execute("SELECT stock FROM books WHERE id = 42").fetchone()[0]
140 assert orders == 1 and stock == 10 # an order exists but stock never went down
141
142 print("All checks pass. Buggy version after a crash:", orders, "order saved, stock still", stock)
143
144
145if __name__ == "__main__":
146 run_checks()