################################################## # Author: Jeb # Date: 2007/04 # Purpose: To create a tpcb database, tables and # stored procedures to load the database # and run transactions against the DB ################################################## CREATE DATABASE tpcb; --echo CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), filler CHAR(255), PRIMARY KEY(id)); --echo CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(bid)); --echo CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(tid)); --echo CREATE TABLE tpcb.history (id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT, tid INT, bid INT, amount DECIMAL(10,2), tdate DATETIME, teller CHAR(20), uuidf LONGBLOB, filler CHAR(80),PRIMARY KEY (id)); --echo --echo --- Create stored procedures & functions --- --echo --disable_query_log delimiter |; CREATE PROCEDURE tpcb.load() BEGIN DECLARE acct INT DEFAULT 100; DECLARE brch INT DEFAULT 10; DECLARE tell INT DEFAULT 100; DECLARE tmp INT DEFAULT 10; WHILE brch > 0 DO SET tmp = 100; WHILE tmp > 0 DO INSERT INTO tpcb.account VALUES (acct, brch, 0.0, "FRESH ACCOUNT"); SET acct = acct - 1; SET tmp = tmp -1; END WHILE; INSERT INTO tpcb.branch VALUES (brch, 0.0, "FRESH BRANCH"); SET brch = brch - 1; END WHILE; WHILE tell > 0 DO INSERT INTO tpcb.teller VALUES (tell, 0.0, "FRESH TELLER"); SET tell = tell - 1; END WHILE; END| CREATE FUNCTION tpcb.account_id () RETURNS INT BEGIN DECLARE num INT; DECLARE ran INT; SELECT RAND() * 10 INTO ran; IF (ran < 5) THEN SELECT RAND() * 10 INTO num; ELSE SELECT RAND() * 100 INTO num; END IF; IF (num < 1) THEN RETURN 1; END IF; RETURN num; END| CREATE FUNCTION tpcb.teller_id () RETURNS INT BEGIN DECLARE num INT; DECLARE ran INT; SELECT RAND() * 10 INTO ran; IF (ran < 5) THEN SELECT RAND() * 10 INTO num; ELSE SELECT RAND() * 100 INTO num; END IF; IF (num < 1) THEN RETURN 1; END IF; RETURN num; END| CREATE PROCEDURE tpcb.trans() BEGIN DECLARE acct INT DEFAULT 0; DECLARE brch INT DEFAULT 0; DECLARE tell INT DEFAULT 0; DECLARE bal DECIMAL(10,2) DEFAULT 0.0; DECLARE amount DECIMAL(10,2) DEFAULT 1.00; DECLARE test INT DEFAULT 0; DECLARE bbal DECIMAL(10,2) DEFAULT 0.0; DECLARE tbal DECIMAL(10,2) DEFAULT 0.0; DECLARE local_uuid VARCHAR(255); DECLARE local_user VARCHAR(255); DECLARE local_time TIMESTAMP; SELECT RAND() * 10 INTO test; SELECT tpcb.account_id() INTO acct; SELECT tpcb.teller_id() INTO tell; SELECT account.balance INTO bal FROM tpcb.account WHERE id = acct; SELECT account.bid INTO brch FROM tpcb.account WHERE id = acct; SELECT teller.balance INTO tbal FROM tpcb.teller WHERE tid = tell; SELECT branch.balance INTO bbal FROM tpcb.branch WHERE bid = brch; START TRANSACTION; IF (test < 5) THEN SET bal = bal + amount; SET bbal = bbal + amount; SET tbal = tbal + amount; UPDATE tpcb.account SET balance = bal, filler = 'account updated' WHERE id = acct; UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' WHERE bid = brch; UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' WHERE tid = tell; ELSE SET bal = bal - amount; SET bbal = bbal - amount; SET tbal = tbal - amount; UPDATE tpcb.account SET balance = bal, filler = 'account updated' WHERE id = acct; UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' WHERE bid = brch; UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' WHERE tid = tell; END IF; IF (@RPL_SYS_MODE = 'SBR') THEN SET local_uuid=UUID(); SET local_user=USER(); SET local_time= NOW(); INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, local_time,local_user, local_uuid,'completed trans'); ELSE INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(), UUID(),'completed trans'); END IF; COMMIT; END| delimiter ;| --enable_query_log --echo --echo *** Stored Procedures Created *** --echo