We use three tables. One table will hold the account information, the name of the account holder, the account type and the limit of the account. We will have to take care that the balance of the account doesn't fall below this limit. The second table will store the balance of the account. This table enforces a 1:1 relation to the first table with the account information.
Here are the SQL commands for creating the tables and filling them with sample data:
CREATE TABLE atm_accounts (
`acc_id` int(11) NOT NULL auto_increment,
`acc_owner` varchar(50) NOT NULL,
`acc_name` varchar(50) NOT NULL,
`acc_limit` decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (`acc_id`),
KEY `acc_limit` (`acc_limit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO atm_accounts VALUES (1, 'abc', 'check', -10000.00);
INSERT INTO atm_accounts VALUES (2, 'abc', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (3, 'def', 'check', -5000.00);
INSERT INTO atm_accounts VALUES (4, 'def', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (5, 'ghi', 'check', -3000.00);
INSERT INTO atm_accounts VALUES (6, 'ghi', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (7, 'jkl', 'check', -12000.00);
INSERT INTO atm_accounts VALUES (8, 'jkl', 'saving', 0.00);
CREATE TABLE atm_balances (
`acc_id` int(11) NOT NULL,
`balance` decimal(10,2) NOT NULL,
PRIMARY KEY (`acc_id`),
CONSTRAINT `atm_balances_ibfk_1` FOREIGN KEY (`acc_id`)
REFERENCES `atm_accounts` (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO atm_balances VALUES (1, 2000.00);
INSERT INTO atm_balances VALUES (2, 300.00);
INSERT INTO atm_balances VALUES (3, -2800.00);
INSERT INTO atm_balances VALUES (4, 4700.00);
INSERT INTO atm_balances VALUES (5, 3500.00);
INSERT INTO atm_balances VALUES (6, 900.00);
INSERT INTO atm_balances VALUES (7, 18000.00);
INSERT INTO atm_balances VALUES (8, 14300.00);
We could have store all of this data in a single table as well. Instead, we create a view to tie the tables together and make accessing the data easier:
CREATE VIEW v_accounts AS
select a.acc_id, a.acc_owner, a.acc_name, a.acc_limit, b.balance
from atm_accounts a inner join atm_balances b
on a.acc_id = b.acc_id
order by a.acc_id;
Now we can access the view v_accounts as if the data would have been stored in a single table.
The third table will be for logging. We want to keep track of all successful transactions. We will use this table to write another stored procedure, which makes it possible to undo transactions that have been successfully finished before.
In this table, we store not only the account ids (from both the debited account and credited account), but also the balances of the accounts before and after the transactions. Here's the structure of this table:
CREATE TABLE atm_log (
`id` int(11) NOT NULL auto_increment,
`account_from` int(11) NOT NULL,
`account_to` int(11) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`acc_from_balance_before` decimal(10,2) NOT NULL,
`acc_from_balance_after` decimal(10,2) NOT NULL,
`acc_to_balance_before` decimal(10,2) NOT NULL,
`acc_to_balance_after` decimal(10,2) NOT NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `account_from` (`account_from`),
KEY `account_to` (`account_to`),
CONSTRAINT `atm_log_ibfk_1` FOREIGN KEY (`account_from`)
REFERENCES `atm_accounts` (`acc_id`),
CONSTRAINT `atm_log_ibfk_2` FOREIGN KEY (`account_to`)
REFERENCES `atm_accounts` (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Well, the tables are done - we're ready to enter the most interesting part of this example: the stored procedures.
The first procedure will be the one to make the transfer between two accounts - we call it "xfer". Of course, we need parameters for the debited account and the credited account as well as the amount that we want to transfer. We add another parameter, that we call _withlimit (I'm using underscores to sign my variables to separate them from column names). A value of 1 will indicate that the account limits should be enforced, that's what we usually have to pass into the procedure. We need this, because in the undoTrans procedure that we'll write later, we will not enforce that limit, so we'll pass a value of 2 within the undoTrans procedure. The last parameter is an output parameter where we can store error messages (or a success message, if everything worked fine).
First we have to do some checks inside this procedure. First we verify the value of the _withlimit parameter which has to be 1 or 2. Then we verify that both (the debited and the credited) accounts exist and that they are not equal to each other. If _withlimit is set to 1, we finally verify, whether the transfer would cause the balance of the debited account to fall below the account limit, before we start the transaction, assuming that no error has occured doing these checks.
In the declaration part of the procedure, there's also an exit handler for the case that a SQL exception occurs. This declaration causes a rollback to be made, wherever this error happens. This ensures that the procedure can never succeed if any SQL error happens anywhere in the procedure.
Within the transaction, first the amount is deducted from the debited account. If not exactly one row has changed, an error variable (_errorvar) is set to indicate that something went wrong. Then the same happens with the credited account, of course we increase the balance of this account - then we check again. Finally we make an insertion into the logging table and check once more, whether exactly one row has been affected.
With this error variable we can decide whether to commit or to rollback this transaction. Finally we output the error message variable (_error) and the procedure is finished. Here's the code:
DELIMITER $$
DROP PROCEDURE IF EXISTS xfer$$
CREATE PROCEDURE xfer(IN _accfrom INT, IN _accto INT,
IN _amount DECIMAL(10, 2),
IN _withlimit INT, OUT _error VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
declare _acc_balance_from_before INT default 0;
declare _acc_balance_from_after INT default 0;
declare _acc_balance_to_before INT default 0;
declare _acc_balance_to_after INT default 0;
declare _idcount INT default 0;
declare _acc_limit DECIMAL(10, 2) default 0;
declare _rowcount INT default -1; -- temp variable for rowcount
declare _errorvar INT default 0; -- 0 = no error, else = error
declare exit handler for SQLEXCEPTION rollback;
set _error := 'SQL error';
if _withlimit != 1 and _withlimit != 2 then
set _errorvar := 1;
set _error := '_withlimit must be 1 or 2!';
end if;
if _accfrom = _accto then -- accounts are identical
set _errorvar := 2;
set _error := 'Accounts are identical!';
end if;
select count(*) as cnt from atm_accounts where acc_id = _accfrom
into _idcount;
if _idcount = 0 and _errorvar = 0 then
-- source account does not exist
set _errorvar := 3;
set _error := 'Source account does not exist!';
end if;
select count(*) as cnt from atm_accounts where acc_id = _accto
into _idcount;
if _idcount = 0 and _errorvar = 0 then
-- target account does not exist
set _errorvar := 4;
set _error := 'Target account does not exist!';
end if;
if _withlimit = 1 then
select balance
from v_accounts
where acc_id = _accfrom
into _acc_balance_from_before;
set _acc_balance_from_after :=
_acc_balance_from_before - _amount;
select acc_limit
from v_accounts
where acc_id = _accfrom
into _acc_limit;
select balance
from v_accounts
where acc_id = _accto
into _acc_balance_to_before;
set _acc_balance_to_after := _acc_balance_to_before + _amount;
if _acc_limit > _acc_balance_from_after and _errorvar = 0 then
-- balance below limit
set _errorvar := 5;
set _error := 'Transaction failed - Balance of source account
would fall below limit';
end if;
end if;
-- START TRANSACTION
if _errorvar = 0 then
start transaction;
update atm_balances set balance = balance - _amount
where acc_id = _accfrom;
select row_count() into _rowcount;
if _rowcount != 1 then -- SQL error
set _errorvar := 6;
end if;
update atm_balances set balance = balance + _amount
where acc_id = _accto;
select row_count() into _rowcount;
if _rowcount != 1 and _errorvar = 0 then -- SQL error
set _errorvar := 7;
end if;
insert into atm_log (account_from, account_to, amount,
acc_from_balance_before, acc_from_balance_after,
acc_to_balance_before, acc_to_balance_after) values
(_accfrom, _accto, _amount,
_acc_balance_from_before, _acc_balance_from_after,
_acc_balance_to_before, _acc_balance_to_after);
select row_count() into _rowcount;
if _rowcount != 1 and _errorvar = 0 then -- SQL error
set _errorvar := 8;
end if;
if _errorvar = 0 then
commit;
set _error := 'Transaction successful';
elseif _errorvar = 6 then
rollback;
set _error := 'Error deducting money from source account!';
elseif _errorvar = 7 then
rollback;
set _error := 'Error adding money to target account!';
elseif _errorvar = 8 then
rollback;
set _error := 'Error writing transaction to log!';
end if;
end if;
-- TRANSACTION FINISHED
select _error as Status;
END$$
DELIMITER ;
I have used SQL INVOKER as security option for these procedures. This option requires that the user who calls this procedure has all privileges (including the EXECUTE privilege) for all operation within this procedure. If we had used SQL DEFINER instead, the user would only need the EXECUTE privilege.
Here's an example how to use this procedure:
mysql> call xfer(3, 1, 500, 1, @a);
+------------------------+
| Status |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> select @a;
+------------------------+
| @a |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.00 sec)
mysql> select * from v_accounts;
+--------+-----------+----------+-----------+----------+
| acc_id | acc_owner | acc_name | acc_limit | balance |
+--------+-----------+----------+-----------+----------+
| 1 | abc | check | -10000.00 | 2500.00 |
| 2 | abc | saving | 0.00 | 300.00 |
| 3 | def | check | -5000.00 | -3300.00 |
| 4 | def | saving | 0.00 | 4700.00 |
| 5 | ghi | check | -3000.00 | 3500.00 |
| 6 | ghi | saving | 0.00 | 900.00 |
| 7 | jkl | check | -12000.00 | 18000.00 |
| 8 | jkl | saving | 0.00 | 14300.00 |
+--------+-----------+----------+-----------+----------+
8 rows in set (0.00 sec)
mysql>
Finally we write the procedure to undo all transactions between two given dates. Actually, we don't really undo them, instead we exchange debited and credited accounts of all procedures and perform the transaction again. The only difference is that we don't check the account limits this time, to make sure, all transactions are being executed with exchanged accounts.
Therefore, we declare a cursor that selects all transactions from the log between the "from date" and the "to date" which we pass as parameters. The order will be the timestamp (column ts) in descending order. As the cursor iterates through all rows, this procedure calls the xfer procedure, passing the parameter 2 for the _withlimit variable. Here's the code and an example how to use it:
DELIMITER $$
DROP PROCEDURE IF EXISTS undoTrans$$
CREATE PROCEDURE undoTrans(IN _datefrom DATETIME,
IN _dateto DATETIME,
OUT _error VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
declare finished INT default 0;
declare accfrom INT;
declare accto INT;
declare amt DECIMAL(10, 2);
declare cur_trans cursor for
select account_to as account_from, account_from as account_to,
amount
from atm_log where ts between _datefrom and _dateto
order by ts desc;
declare continue handler for not found set finished := 1;
declare exit handler for sqlexception rollback;
open cur_trans;
cur_loop: loop
fetch cur_trans into accfrom, accto, amt;
if finished = 1 then
leave cur_loop;
end if;
call xfer(accfrom, accto, amt, 2, @a);
end loop;
close cur_trans;
END$$
DELIMITER ;
Voilà - here's the example:
mysql> call undoTrans('2005-09-22', '2005-09-30', @a);
+------------------------+
| Status |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> select * from v_accounts;
+--------+-----------+----------+-----------+----------+
| acc_id | acc_owner | acc_name | acc_limit | balance |
+--------+-----------+----------+-----------+----------+
| 1 | abc | check | -10000.00 | 2000.00 |
| 2 | abc | saving | 0.00 | 300.00 |
| 3 | def | check | -5000.00 | -2800.00 |
| 4 | def | saving | 0.00 | 4700.00 |
| 5 | ghi | check | -3000.00 | 3500.00 |
| 6 | ghi | saving | 0.00 | 900.00 |
| 7 | jkl | check | -12000.00 | 18000.00 |
| 8 | jkl | saving | 0.00 | 14300.00 |
+--------+-----------+----------+-----------+----------+
8 rows in set (0.00 sec)
mysql> select account_from, account_to, amount from atm_log;
+--------------+------------+--------+
| account_from | account_to | amount |
+--------------+------------+--------+
| 3 | 1 | 500.00 |
| 1 | 3 | 500.00 |
+--------------+------------+--------+
2 rows in set (0.00 sec)
mysql>Enjoy ;-)!
No comments:
Post a Comment