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