SQLite Transaction - in a bank

• Setup Bank accounts with some initial money.
• Move some money from one account to another - two separate steps. Worked.
• Move some money from one account to another - two separate steps - stop in the middle. Failed.
• Move some money from one account to another - Transaction - stop in the middle. Worked. (money stayed where it was)
• Remove bank

CREATE TABLE bank (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL
);

INSERT INTO bank (name, balance) VALUES ("Jane", 0);
INSERT INTO bank (name, balance) VALUES ("Mary", 1000);
INSERT INTO bank (name, balance) VALUES ("Ann", 1000);

SELECT * FROM bank;
SELECT "Total", SUM(balance) FROM bank;
SELECT "-----";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";

BEGIN TRANSACTION;
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";
COMMIT;

sqlite3 bank.db < setup_bank.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < transfer.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < without_transaction.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < with_transaction.sql
sqlite3 bank.db < show.sql

rm -f bank.db

Jane|0
Mary|1000
Ann|1000
Total|2000
-----
Jane|100
Mary|900
Ann|1000
Total|2000
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----

• TODO: loading a large CSV file into the database and running queries.
• TODO: creating a multi-tabe database, dumping it and then loading it and running queries against it.
• TODO: FOREIGN KEY - cascading deletition?