一起发生的写入

机器翻译

本页由英文原文机器翻译而来，尚未经过母语审校，欢迎在 GitHub 上提出修改。内容如有出入，以英文原文为准。

任务： 在两个账户之间转账，确保钱绝不会从一个账户扣除却没有存入另一个账户。

# Recipe: several writes that happen together or not at all.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/bank.db

日志表拒绝超过 1000 的转账——最后一个测试利用这一点让第三次写入失败：

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);

-- Every transfer is logged; one transfer moves at most 1000.
CREATE TABLE transfers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_id INTEGER NOT NULL REFERENCES accounts (id),
    to_id INTEGER NOT NULL REFERENCES accounts (id),
    amount INTEGER NOT NULL CHECK (amount > 0 AND amount <= 1000)
);

INSERT INTO accounts (id, owner, balance) VALUES (1, 'Ana', 1500), (2, 'Bruno', 200);

页面检查它能解释的情况（余额不足），并用提示消息（flash）说明。三次写入放在 q:transaction 中：只要其中任何一次失败，之前的写入就会回滚，页面带着错误停止。

<q:component name="Bank">
  <q:action name="transfer" method="POST">
    <q:param name="amount" type="integer" required="true" min="1" />

    <q:query name="source" datasource="db">
      SELECT balance FROM accounts WHERE id = 1
    </q:query>
    <q:if condition="source.balance &lt; amount">
      <q:redirect url="/" flash="Not enough money in Ana's account." flashType="error" />
    </q:if>

    <!-- The debit, the credit and the log line commit together. If one of
         them fails, the ones before it are undone and the page stops with
         the error: no money appears or disappears. -->
    <q:transaction datasource="db">
      <q:query name="debit">
        UPDATE accounts SET balance = balance - :amount WHERE id = 1
        <q:param name="amount" value="{amount}" type="integer" />
      </q:query>
      <q:query name="credit">
        UPDATE accounts SET balance = balance + :amount WHERE id = 2
        <q:param name="amount" value="{amount}" type="integer" />
      </q:query>
      <q:query name="logged">
        INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, :amount)
        <q:param name="amount" value="{amount}" type="integer" />
      </q:query>
    </q:transaction>

    <q:redirect url="/" flash="Moved {amount} from Ana to Bruno." />
  </q:action>

  <q:query name="accounts" datasource="db">
    SELECT owner, balance FROM accounts ORDER BY id
  </q:query>

  <ui:window title="Bank">
    <q:if condition="flash">
      <ui:alert variant="info">{flash}</ui:alert>
    </q:if>
    <q:loop query="accounts">
      <ui:text>{accounts.owner}: {accounts.balance}</ui:text>
    </q:loop>
    <ui:form on-submit="transfer">
      <ui:input bind="amount" placeholder="Amount" />
      <ui:button variant="primary">Move from Ana to Bruno</ui:button>
    </ui:form>
  </ui:window>
</q:component>

<q:test name="a transfer moves the money and logs it" page="/">
  <test:submit action="transfer" amount="300" />
  <test:expect redirect="/" flash="Moved 300 from Ana to Bruno." />
  <test:expect table="accounts" count="1" where="owner = 'Ana' AND balance = 1200" />
  <test:expect table="accounts" count="1" where="owner = 'Bruno' AND balance = 500" />
  <test:expect table="transfers" count="1" />
</q:test>

<q:test name="the page refuses what the account cannot pay" page="/">
  <test:submit action="transfer" amount="5000" />
  <test:expect flash="Not enough money in Ana's account." />
  <test:expect table="transfers" count="0" />
</q:test>

<q:test name="when the last write fails, the first two are undone" page="/">
  <!-- 1200 passes the balance check, but the log refuses more than 1000:
       the debit and the credit already ran, and are rolled back. -->
  <test:submit action="transfer" amount="1200" />
  <test:expect status="500" />
  <test:expect table="accounts" count="1" where="owner = 'Ana' AND balance = 1500" />
  <test:expect table="accounts" count="1" where="owner = 'Bruno' AND balance = 200" />
  <test:expect table="transfers" count="0" />
</q:test>

tests/transfer.test.q
  PASS  a transfer moves the money and logs it
  PASS  the page refuses what the account cannot pay
  PASS  when the last write fails, the first two are undone
3 passed, 0 failed

参见 DB-4。