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