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