عقود تطبيقات مضمّنة مرنة

PRAGMA foreign_keys=ON;
CREATE TABLE customer(id INTEGER PRIMARY KEY) STRICT;
CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customer(id)) STRICT;
INSERT INTO customer VALUES(1);
INSERT INTO purchase VALUES(10,1);
PRAGMA user_version=1;
PRAGMA foreign_keys;
PRAGMA user_version;
PRAGMA foreign_key_check;
PRAGMA quick_check;

foreign_keys
1

user_version
1

table|rowid|parent|fkid
(no rows)

quick_check
ok

يعرض فرض المفاتيح الأجنبية وuser_version القيمة 1، ولا يعيد فحص العلاقات صفوفاً، ويعيد الفحص البنيوي المصطنع ok.

import sqlite3
from contextlib import closing

def reserve(db, request_key, quantity):
    if type(quantity) is not int or quantity <= 0:
        raise ValueError("quantity")
    db.execute("BEGIN IMMEDIATE")
    try:
        prior = db.execute("SELECT quantity,result FROM request_log WHERE request_key=?", (request_key,)).fetchone()
        if prior is not None:
            if prior[0] != quantity:
                raise ValueError("request payload differs")
            db.execute("COMMIT")
            return prior[1]
        row = db.execute("UPDATE stock SET quantity=quantity-? WHERE id=1 AND quantity>=? RETURNING quantity",
                         (quantity, quantity)).fetchone()
        if row is None:
            raise ValueError("insufficient stock")
        db.execute("INSERT INTO request_log VALUES(?,?,?)", (request_key, quantity, row[0]))
        db.execute("COMMIT")
        return row[0]
    except Exception:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.executescript("CREATE TABLE stock(id INTEGER PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity>=0)); INSERT INTO stock VALUES(1,5); CREATE TABLE request_log(request_key TEXT PRIMARY KEY NOT NULL,quantity INTEGER NOT NULL,result INTEGER NOT NULL);")
    print("first", reserve(db, "training-1", 2))
    print("replay", reserve(db, "training-1", 2))
    try:
        reserve(db, "training-1", 3)
    except ValueError:
        print("conflict rejected")
    print("stock", db.execute("SELECT quantity FROM stock").fetchone()[0])
    print("requests", db.execute("SELECT COUNT(*) FROM request_log").fetchone()[0])

first 3
replay 3
conflict rejected
stock 3
requests 1

ينخفض المخزون مرة واحدة من 5 إلى 3. تعيد المحاولة المطابقة 3، وتُرفض الحمولة المختلفة، ويخزن صف طلب واحد فقط.