1################################################## 2# Author: Jeb 3# Date: 2007/04 4# Purpose: To create a tpcb database, tables and 5# stored procedures to load the database 6# and run transactions against the DB 7################################################## 8--disable_warnings 9DROP DATABASE IF EXISTS tpcb; 10--enable_warnings 11CREATE DATABASE tpcb; 12 13--echo 14CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), 15 filler CHAR(255), PRIMARY KEY(id)); 16--echo 17CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), 18 PRIMARY KEY(bid)); 19--echo 20CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), 21 PRIMARY KEY(tid)); 22--echo 23CREATE TABLE tpcb.history (id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT, 24 tid INT, bid INT, amount DECIMAL(10,2), 25 tdate DATETIME, teller CHAR(20), uuidf LONGBLOB, 26 filler CHAR(80),PRIMARY KEY (id)); 27 28--echo 29--echo --- Create stored procedures & functions --- 30--echo 31 32--disable_query_log 33delimiter |; 34CREATE PROCEDURE tpcb.load() 35BEGIN 36 DECLARE acct INT DEFAULT 100; 37 DECLARE brch INT DEFAULT 10; 38 DECLARE tell INT DEFAULT 100; 39 DECLARE tmp INT DEFAULT 10; 40 WHILE brch > 0 DO 41 SET tmp = 100; 42 WHILE tmp > 0 DO 43 INSERT INTO tpcb.account VALUES (acct, brch, 0.0, "FRESH ACCOUNT"); 44 SET acct = acct - 1; 45 SET tmp = tmp -1; 46 END WHILE; 47 INSERT INTO tpcb.branch VALUES (brch, 0.0, "FRESH BRANCH"); 48 SET brch = brch - 1; 49 END WHILE; 50 WHILE tell > 0 DO 51 INSERT INTO tpcb.teller VALUES (tell, 0.0, "FRESH TELLER"); 52 SET tell = tell - 1; 53 END WHILE; 54END| 55 56CREATE FUNCTION tpcb.account_id () RETURNS INT 57BEGIN 58 DECLARE num INT; 59 DECLARE ran INT; 60 SELECT RAND() * 10 INTO ran; 61 IF (ran < 5) 62 THEN 63 SELECT RAND() * 10 INTO num; 64 ELSE 65 SELECT RAND() * 100 INTO num; 66 END IF; 67 IF (num < 1) 68 THEN 69 RETURN 1; 70 END IF; 71 RETURN num; 72END| 73 74CREATE FUNCTION tpcb.teller_id () RETURNS INT 75BEGIN 76 DECLARE num INT; 77 DECLARE ran INT; 78 SELECT RAND() * 10 INTO ran; 79 IF (ran < 5) 80 THEN 81 SELECT RAND() * 10 INTO num; 82 ELSE 83 SELECT RAND() * 100 INTO num; 84 END IF; 85 IF (num < 1) 86 THEN 87 RETURN 1; 88 END IF; 89 RETURN num; 90END| 91 92CREATE PROCEDURE tpcb.trans(in format varchar(3)) 93BEGIN 94 DECLARE acct INT DEFAULT 0; 95 DECLARE brch INT DEFAULT 0; 96 DECLARE tell INT DEFAULT 0; 97 DECLARE bal DECIMAL(10,2) DEFAULT 0.0; 98 DECLARE amount DECIMAL(10,2) DEFAULT 1.00; 99 DECLARE test INT DEFAULT 0; 100 DECLARE bbal DECIMAL(10,2) DEFAULT 0.0; 101 DECLARE tbal DECIMAL(10,2) DEFAULT 0.0; 102 DECLARE local_uuid VARCHAR(255); 103 DECLARE local_user VARCHAR(255); 104 DECLARE local_time TIMESTAMP; 105 106 SELECT RAND() * 10 INTO test; 107 SELECT tpcb.account_id() INTO acct; 108 SELECT tpcb.teller_id() INTO tell; 109 110 SELECT account.balance INTO bal FROM tpcb.account WHERE id = acct; 111 SELECT account.bid INTO brch FROM tpcb.account WHERE id = acct; 112 SELECT teller.balance INTO tbal FROM tpcb.teller WHERE tid = tell; 113 SELECT branch.balance INTO bbal FROM tpcb.branch WHERE bid = brch; 114 115 IF (test < 5) 116 THEN 117 SET bal = bal + amount; 118 SET bbal = bbal + amount; 119 SET tbal = tbal + amount; 120 UPDATE tpcb.account SET balance = bal, filler = 'account updated' 121 WHERE id = acct; 122 UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' 123 WHERE bid = brch; 124 UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' 125 WHERE tid = tell; 126 ELSE 127 SET bal = bal - amount; 128 SET bbal = bbal - amount; 129 SET tbal = tbal - amount; 130 UPDATE tpcb.account SET balance = bal, filler = 'account updated' 131 WHERE id = acct; 132 UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' 133 WHERE bid = brch; 134 UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' 135 WHERE tid = tell; 136 END IF; 137 138 IF (format = 'SBR') 139 THEN 140 SET local_uuid=UUID(); 141 SET local_user=USER(); 142 SET local_time= NOW(); 143 INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, local_time,local_user, 144 local_uuid,'completed trans'); 145 ELSE 146 INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(), 147 UUID(),'completed trans'); 148 END IF; 149END| 150delimiter ;| 151--enable_query_log 152--echo 153--echo *** Stored Procedures Created *** 154--echo 155 156