db4free.net blog: September 2005

This example tries to simulate an ATM transfer system using MySQL Stored Procedures. We will also use one view and a cursor in this example.

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 ;-)!

Hi folks,

let me first introduce myself. I'm a 29 year old self employed software and web developer with a focus on database applications, living in the north of Austria in a region called "Waldviertel", best translated as "wood quarter".

In June this year, me and two friends and colleagues of mine, Natascha and Jürgen, started a project called "db4free.net". We rented a server, created a web application and set up a MySQL 5 database server, to give people MySQL 5 database accounts for free. Now, three months later, we are about to welcome our 1,000th user.

The reason for doing all this is that MySQL is a great OpenSource product that's simple to work with on one hand, but still very powerful on the other. It's available for all common platforms and for most (even commercial) cases you can use it for free. Only if an application using MySQL is being distributed with another licence than MySQL's own GPL, a commercial licence is required.

MySQL 5 which has just entered Release Candidate stage introduces a whole bunch of new features. Some people might say that they are nothing new - other database systems have implemented things like Stored Procedured, Functions, Views, Triggers a.s.o. a long time ago and MySQL has been far behind them. Of course, that's a fact I can't deny. But still, MySQL has remained the world's most popular database server - for a good reason. There's a strong community behind this product and you are never alone if you need assistance in solving a problem.

Now, what is this blog all about? OpenSource and being a part of an OpenSource product's community (somebody described a MySQL community member as "someone who does work on MySQL related things beyond the call of duty") is very much about giving. I have been working with MySQL a lot and I did a lot of testing with the new features of MySQL 5, mainly with stored procedures. My contribution shall be to share solutions to common problems that I have solved with MySQL (mostly MySQL 5) with other people. In this blog, you will find many useful examples that you can easily adopt to your own needs or simply use them to learn about MySQL's techniques.

Of course, this is not the only blog about this topic. There are two other great blogs related to MySQL 5 that I recommand you to visit: Andrew Gilfrin's blog and Roland Bouman's blog. Also take a look at Andrew's MySQLDevelopment.com site. At these sites, together with this one, you'll find a great starting point to dig into the depths of MySQL 5.

If you need even more information about MySQL 5, get the book Pro MySQL 5 from Michael Kruckenberg and Jay Pipes.

db4free.net blog

Friday, September 30, 2005

1,000th user at db4free.net

I finally did it!

Tuesday, September 27, 2005

INSERT ... ON DUPLICATE KEY UPDATE

ATM transfer simulation

Menu management with stored procedures (without Nested Set Model)

Monday, September 26, 2005

About me and this blog

About Me

Links

Blog Archive