HammerDB Function Schema

hammerdb.sql

CREATE TABLE public.customer (c_id numeric(5,0) NOT NULL, c_d_id numeric(2,0) NOT NULL, c_w_id numeric(4,0) NOT NULL, c_first character varying(16), c_middle character(2), c_last character varying(16), c_street_1 character varying(20), c_street_2 character varying(20), c_city character varying(20), c_state character(2), c_zip character(9), c_phone character(16), c_since timestamp without time zone, c_credit character(2), c_credit_lim numeric(12,2), c_discount numeric(4,4), c_balance numeric(12,2), c_ytd_payment numeric(12,2), c_payment_cnt numeric(8,0), c_delivery_cnt numeric(8,0), c_data character varying(500)) WITH (fillfactor='50');
CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id) TABLESPACE pg_default ;       
ALTER TABLE public.customer ADD CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id);
CREATE TABLE public.district (d_id numeric(2,0) NOT NULL, d_w_id numeric(4,0) NOT NULL, d_ytd numeric(12,2), d_tax numeric(4,4), d_next_o_id numeric, d_name character varying(10), d_street_1 character varying(20), d_street_2 character varying(20), d_city character varying(20), d_state character(2), d_zip character(9)) WITH (fillfactor='10');
ALTER TABLE public.district ADD CONSTRAINT district_i1 PRIMARY KEY (d_w_id, d_id) WITH (fillfactor='100') ;        
CREATE TABLE public.history (h_c_id numeric, h_c_d_id numeric, h_c_w_id numeric, h_d_id numeric, h_w_id numeric, h_date timestamp without time zone, h_amount numeric(6,2), h_data character varying(24)) WITH (fillfactor='50')  ;       
CREATE TABLE public.warehouse (w_id numeric(4,0) NOT NULL, w_ytd numeric(12,2), w_tax numeric(4,4), w_name character varying(10), w_street_1 character varying(20), w_street_2 character varying(20), w_city character varying(20), w_state character(2), w_zip character(9)) WITH (fillfactor='10')  ;      
ALTER TABLE public.warehouse ADD CONSTRAINT warehouse_i1 PRIMARY KEY (w_id) WITH (fillfactor='100') ;        
CREATE TABLE public.stock (s_i_id numeric(6,0) NOT NULL, s_w_id numeric(4,0) NOT NULL, s_quantity numeric(6,0), s_dist_01 character(24), s_dist_02 character(24), s_dist_03 character(24), s_dist_04 character(24), s_dist_05 character(24), s_dist_06 character(24), s_dist_07 character(24), s_dist_08 character(24), s_dist_09 character(24), s_dist_10 character(24), s_ytd numeric(10,0), s_order_cnt numeric(6,0), s_remote_cnt numeric(6,0), s_data character varying(50)) WITH (fillfactor='50');         
ALTER TABLE public.stock ADD CONSTRAINT stock_i1 PRIMARY KEY (s_w_id, s_i_id);         
CREATE TABLE public.new_order (no_w_id numeric NOT NULL, no_d_id numeric NOT NULL, no_o_id numeric NOT NULL) WITH (fillfactor='50');        
ALTER TABLE public.new_order ADD CONSTRAINT new_order_i1 PRIMARY KEY (no_w_id, no_d_id, no_o_id);         
CREATE TABLE public.orders (o_id numeric NOT NULL, o_w_id numeric NOT NULL, o_d_id numeric NOT NULL, o_c_id numeric, o_carrier_id numeric, o_ol_cnt numeric, o_all_local numeric, o_entry_d timestamp without time zone) WITH (fillfactor='50');         
CREATE UNIQUE INDEX orders_i2 ON public.orders USING btree (o_w_id, o_d_id, o_c_id, o_id) TABLESPACE pg_default;
ALTER TABLE public.orders ADD CONSTRAINT orders_i1 PRIMARY KEY (o_w_id, o_d_id, o_id);         
CREATE TABLE public.order_line (ol_w_id numeric NOT NULL, ol_d_id numeric NOT NULL, ol_o_id numeric NOT NULL, ol_number numeric NOT NULL, ol_i_id numeric, ol_delivery_d timestamp without time zone, ol_amount numeric, ol_supply_w_id numeric, ol_quantity numeric, ol_dist_info character(24)) WITH (fillfactor='50');         
ALTER TABLE public.order_line ADD CONSTRAINT order_line_i1 PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number);      
CREATE TABLE public.item (i_id numeric(6,0) NOT NULL, i_im_id numeric, i_name character varying(24), i_price numeric(5,2), i_data character varying(50)) WITH (fillfactor='50');        
ALTER TABLE public.item ADD CONSTRAINT item_i1 PRIMARY KEY (i_id);

SELECT create_distributed_table('customer', 'c_w_id');
SELECT create_distributed_table('district', 'd_w_id');
SELECT create_distributed_table('history', 'h_w_id');
SELECT create_distributed_table('warehouse', 'w_id');
SELECT create_distributed_table('stock', 's_w_id');
SELECT create_distributed_table('new_order', 'no_w_id');
SELECT create_distributed_table('orders', 'o_w_id');
SELECT create_distributed_table('order_line', 'ol_w_id');
SELECT create_reference_table('item');


CREATE OR REPLACE FUNCTION public.neword(no_w_id integer, no_max_w_id integer, no_d_id integer, no_c_id integer, no_o_ol_cnt integer, INOUT no_c_discount numeric, INOUT no_c_last character varying, INOUT no_c_credit character varying, INOUT no_d_tax numeric, INOUT no_w_tax numeric, INOUT no_d_next_o_id integer, tstamp timestamp without time zone)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
no_ol_supply_w_id       INTEGER;
no_ol_i_id              NUMERIC;
no_ol_quantity          NUMERIC;
no_o_all_local          INTEGER;
o_id                    INTEGER;
no_i_name               VARCHAR(24);
no_i_price              NUMERIC(5,2);
no_i_data               VARCHAR(50);
no_s_quantity           NUMERIC(6);
no_ol_amount            NUMERIC(6,2);
no_s_dist_01            CHAR(24);
no_s_dist_02            CHAR(24);
no_s_dist_03            CHAR(24);
no_s_dist_04            CHAR(24);
no_s_dist_05            CHAR(24);
no_s_dist_06            CHAR(24);
no_s_dist_07            CHAR(24);
no_s_dist_08            CHAR(24);
no_s_dist_09            CHAR(24);
no_s_dist_10            CHAR(24);
no_ol_dist_info         CHAR(24);
no_s_data               VARCHAR(50);
x                       NUMERIC;
rbk                     NUMERIC;
BEGIN
--assignment below added due to error in appendix code
no_o_all_local := 0;
SELECT c_discount, c_last, c_credit, w_tax
INTO no_c_discount, no_c_last, no_c_credit, no_w_tax
FROM customer, warehouse
WHERE warehouse.w_id = no_w_id AND customer.c_w_id = no_w_id AND
customer.c_d_id = no_d_id AND customer.c_id = no_c_id;
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id, d_tax INTO no_d_next_o_id, no_d_tax;
o_id := no_d_next_o_id;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (o_id, no_d_id, no_w_id, no_c_id, current_timestamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (o_id, no_d_id, no_w_id);
--#2.4.1.4
rbk := round(DBMS_RANDOM(1,100));
--#2.4.1.5
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
IF ((loop_counter = no_o_ol_cnt) AND (rbk = 1))
THEN
no_ol_i_id := 100001;
ELSE
no_ol_i_id := round(DBMS_RANDOM(1,100000));
END IF;
--#2.4.1.5.2
x := round(DBMS_RANDOM(1,100));
IF ( x > 1 )
THEN
no_ol_supply_w_id := no_w_id;
ELSE
no_ol_supply_w_id := no_w_id;
--no_all_local is actually used before this point so following not beneficial
no_o_all_local := 0;
WHILE ((no_ol_supply_w_id = no_w_id) AND (no_max_w_id != 1))
LOOP
no_ol_supply_w_id := round(DBMS_RANDOM(1,no_max_w_id));
END LOOP;
END IF;
--#2.4.1.5.3
no_ol_quantity := round(DBMS_RANDOM(1,10));
SELECT i_price, i_name, i_data INTO no_i_price, no_i_name, no_i_data
FROM item WHERE i_id = no_ol_i_id;
SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
INTO no_s_quantity, no_s_data, no_s_dist_01, no_s_dist_02, no_s_dist_03, no_s_dist_04, no_s_dist_05, no_s_dist_06, no_s_dist_07, no_s_dist_08, no_s_dist_09, no_s_dist_10 FROM stock WHERE s_i_id = no_ol_i_id AND s_w_id = no_ol_supply_w_id;
IF ( no_s_quantity > no_ol_quantity )
THEN
no_s_quantity := ( no_s_quantity - no_ol_quantity );
ELSE
no_s_quantity := ( no_s_quantity - no_ol_quantity + 91 );
END IF;
UPDATE stock SET s_quantity = no_s_quantity
WHERE s_i_id = no_ol_i_id
AND s_w_id = no_ol_supply_w_id;

no_ol_amount := (  no_ol_quantity * no_i_price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) );

IF no_d_id = 1
THEN
no_ol_dist_info := no_s_dist_01;

ELSIF no_d_id = 2
THEN
no_ol_dist_info := no_s_dist_02;

ELSIF no_d_id = 3
THEN
no_ol_dist_info := no_s_dist_03;

ELSIF no_d_id = 4
THEN
no_ol_dist_info := no_s_dist_04;

ELSIF no_d_id = 5
THEN
no_ol_dist_info := no_s_dist_05;

ELSIF no_d_id = 6
THEN
no_ol_dist_info := no_s_dist_06;

ELSIF no_d_id = 7
THEN
no_ol_dist_info := no_s_dist_07;

ELSIF no_d_id = 8
THEN
no_ol_dist_info := no_s_dist_08;

ELSIF no_d_id = 9
THEN
no_ol_dist_info := no_s_dist_09;

ELSIF no_d_id = 10
THEN
no_ol_dist_info := no_s_dist_10;
END IF;

INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
VALUES (o_id, no_d_id, no_w_id, loop_counter, no_ol_i_id, no_ol_supply_w_id, no_ol_quantity, no_ol_amount, no_ol_dist_info);

END LOOP;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END;
$procedure$
;




CREATE OR REPLACE FUNCTION public.payment(p_w_id integer, p_d_id integer, p_c_w_id integer, p_c_d_id integer, byname integer, p_h_amount numeric, INOUT p_c_credit character, INOUT p_c_last character varying, INOUT p_c_id numeric, INOUT p_w_street_1 character varying, INOUT p_w_street_2 character varying, INOUT p_w_city character varying, INOUT p_w_state character, INOUT p_w_zip character, INOUT p_d_street_1 character varying, INOUT p_d_street_2 character varying, INOUT p_d_city character varying, INOUT p_d_state character, INOUT p_d_zip character, INOUT p_c_first character varying, INOUT p_c_middle character, INOUT p_c_street_1 character varying, INOUT p_c_street_2 character varying, INOUT p_c_city character varying, INOUT p_c_state character, INOUT p_c_zip character, INOUT p_c_phone character, INOUT p_c_since timestamp without time zone, INOUT p_c_credit_lim numeric, INOUT p_c_discount numeric, INOUT p_c_balance numeric, INOUT p_c_data character varying, tstamp timestamp without time zone)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
namecnt INTEGER;
p_d_name VARCHAR(11);
p_w_name VARCHAR(11);
p_c_new_data VARCHAR(500);
h_data VARCHAR(30);
c_byname CURSOR FOR
SELECT c_first, c_middle, c_id,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_last = p_c_last
ORDER BY c_first;
BEGIN
UPDATE warehouse SET w_ytd = w_ytd + p_h_amount
WHERE w_id = p_w_id;
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
INTO p_w_street_1, p_w_street_2, p_w_city, p_w_state, p_w_zip, p_w_name
FROM warehouse
WHERE w_id = p_w_id;
UPDATE district SET d_ytd = d_ytd + p_h_amount
WHERE d_w_id = p_w_id AND d_id = p_d_id;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
INTO p_d_street_1, p_d_street_2, p_d_city, p_d_state, p_d_zip, p_d_name
FROM district
WHERE d_w_id = p_w_id AND d_id = p_d_id;
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = p_c_last AND c_d_id = p_c_d_id AND c_w_id = p_c_w_id;
OPEN c_byname;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
FOR loop_counter IN 0 .. cast((namecnt/2) AS INTEGER)
LOOP
FETCH c_byname
INTO p_c_first, p_c_middle, p_c_id, p_c_street_1, p_c_street_2, p_c_city,
p_c_state, p_c_zip, p_c_phone, p_c_credit, p_c_credit_lim, p_c_discount, p_c_balance, p_c_since;
END LOOP;
CLOSE c_byname;
ELSE
SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
INTO p_c_first, p_c_middle, p_c_last,
p_c_street_1, p_c_street_2, p_c_city, p_c_state, p_c_zip,
p_c_phone, p_c_credit, p_c_credit_lim,
p_c_discount, p_c_balance, p_c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
END IF;
p_c_balance := ( p_c_balance + p_h_amount );
IF p_c_credit = 'BC' 
THEN
 SELECT c_data INTO p_c_data
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
h_data := p_w_name || ' ' || p_d_name;
p_c_new_data := (p_c_id || ' ' || p_c_d_id || ' ' || p_c_w_id || ' ' || p_d_id || ' ' || p_w_id || ' ' || TO_CHAR(p_h_amount,'9999.99') || TO_CHAR(tstamp,'YYYYMMDDHH24MISS') || h_data);
p_c_new_data := substr(CONCAT(p_c_new_data,p_c_data),1,500-(LENGTH(p_c_new_data)));
UPDATE customer
SET c_balance = p_c_balance, c_data = p_c_new_data
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
ELSE
UPDATE customer SET c_balance = p_c_balance
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
END IF;
h_data := p_w_name || ' ' || p_d_name;
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id,
h_w_id, h_date, h_amount, h_data)
VALUES (p_c_d_id, p_c_w_id, p_c_id, p_d_id,
p_w_id, tstamp, p_h_amount, h_data);
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; 
$procedure$
;




CREATE OR REPLACE FUNCTION public.dbms_random(integer, integer)
 RETURNS integer
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    start_int ALIAS FOR $1;
    end_int ALIAS FOR $2;
BEGIN
    RETURN trunc(random() * (end_int-start_int) + start_int);
END;
$function$
;



CREATE OR REPLACE FUNCTION public.ostat(os_w_id integer, os_d_id integer, INOUT os_c_id integer, byname integer, INOUT os_c_last character varying, INOUT os_c_first character varying, INOUT os_c_middle character varying, INOUT os_c_balance numeric, INOUT os_o_id integer, INOUT os_entdate timestamp without time zone, INOUT os_o_carrier_id integer, INOUT os_c_line text DEFAULT ''::text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
out_os_c_id INTEGER;
out_os_c_last VARCHAR;
os_c_first VARCHAR;
os_c_middle VARCHAR;
os_c_balance NUMERIC;
os_o_id INTEGER;
os_entdate TIMESTAMP;
os_o_carrier_id INTEGER;
os_ol RECORD;
namecnt INTEGER;
c_name CURSOR FOR
SELECT c_balance, c_first, c_middle, c_id
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id
ORDER BY c_first;
c_line CURSOR FOR
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d
FROM order_line
WHERE ol_o_id = os_o_id AND ol_d_id = os_d_id AND ol_w_id = os_w_id;
BEGIN
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
OPEN c_name;
FOR loop_counter IN 0 .. cast((namecnt/2) AS INTEGER)
LOOP
FETCH c_name 
INTO os_c_balance, os_c_first, os_c_middle, os_c_id;
END LOOP;
close c_name;
ELSE
SELECT c_balance, c_first, c_middle, c_last
INTO os_c_balance, os_c_first, os_c_middle, os_c_last
FROM customer
WHERE c_id = os_c_id AND c_d_id = os_d_id AND c_w_id = os_w_id;
END IF;
SELECT o_id, o_carrier_id, o_entry_d 
INTO os_o_id, os_o_carrier_id, os_entdate
FROM
(SELECT o_id, o_carrier_id, o_entry_d
FROM orders where o_d_id = os_d_id AND o_w_id = os_w_id and o_c_id=os_c_id
ORDER BY o_id DESC) AS SUBQUERY
LIMIT 1;
IF NOT FOUND THEN
RAISE NOTICE 'No orders for customer';
RETURN;
END IF;
OPEN c_line;
LOOP
FETCH c_line INTO os_ol;
EXIT WHEN NOT FOUND;
os_c_line := os_c_line || ',' || os_ol.ol_i_id || ',' || os_ol.ol_supply_w_id || ',' || os_ol.ol_quantity || ',' || os_ol.ol_amount || ',' || os_ol.ol_delivery_d;
END LOOP;
close c_line;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; 
$procedure$
;



CREATE OR REPLACE FUNCTION public.slev(st_w_id integer, st_d_id integer, threshold integer, INOUT stock_count integer)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
st_o_id NUMERIC; 
BEGIN
SELECT d_next_o_id INTO st_o_id
FROM district
WHERE d_w_id=st_w_id AND d_id=st_d_id;
SELECT COUNT(DISTINCT (s_i_id)) INTO stock_count
FROM order_line, stock
WHERE ol_w_id = st_w_id AND
ol_d_id = st_d_id AND (ol_o_id < st_o_id) AND
ol_o_id >= (st_o_id - 20) AND s_w_id = st_w_id AND
s_i_id = ol_i_id AND s_quantity < threshold;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; 
$procedure$
;



CREATE OR REPLACE FUNCTION DELIVERY (INTEGER, INTEGER) RETURNS INTEGER AS '
DECLARE
d_w_id		ALIAS FOR $1;	
d_o_carrier_id  ALIAS FOR $2;	
d_d_id	       	INTEGER;
d_c_id	       	NUMERIC;
d_no_o_id		INTEGER;
d_ol_total		NUMERIC;
loop_counter		INTEGER;
BEGIN
FOR loop_counter IN 1 .. 10
LOOP
d_d_id := loop_counter;
SELECT no_o_id INTO d_no_o_id FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id ORDER BY no_o_id ASC LIMIT 1;
DELETE FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id AND no_o_id = d_no_o_id;
SELECT o_c_id INTO d_c_id FROM orders
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
 UPDATE orders SET o_carrier_id = d_o_carrier_id
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE order_line SET ol_delivery_d = current_timestamp
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id AND
ol_w_id = d_w_id;
SELECT SUM(ol_amount) INTO d_ol_total
FROM order_line
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id
AND ol_w_id = d_w_id;
UPDATE customer SET c_balance = c_balance + d_ol_total
WHERE c_id = d_c_id AND c_d_id = d_d_id AND
c_w_id = d_w_id;
END LOOP;
RETURN 1;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; 
' LANGUAGE 'plpgsql';

queries.sql

SELECT create_distributed_function('public.dbms_random (integer, integer)');
SELECT create_distributed_function('public.delivery (integer, integer)', '$1', colocate_with := 'warehouse');
SELECT create_distributed_function('public.neword ( integer, integer, integer, integer, integer, integer)', '$1', colocate_with := 'warehouse');
SELECT create_distributed_function('public.payment ( integer, integer, integer, integer, numeric, integer, numeric, character varying, character varying, numeric)', '$1', colocate_with := 'warehouse');
SELECT create_distributed_function('public.slev ( integer, integer, integer)', '$1', colocate_with := 'warehouse');
SELECT create_distributed_function('public.ostat ( integer, integer, integer, integer, character varying)', '$1', colocate_with := 'warehouse');

sample.sql

select neword(92,200,1,393,8,0);
select payment(71,3,192,10,1376,1,2100,'BAROUGHTATION','0',0);
select slev(30,5,19);
select * from ostat(67,6,1615,0,'') as (ol_i_id NUMERIC,  ol_supply_w_id NUMERIC, ol_quantity NUMERIC, ol_amount NUMERIC, ol_delivery_d TIMESTAMP,  out_os_c_id INTEGER, out_os_c_
last VARCHAR, os_c_first VARCHAR, os_c_middle VARCHAR, os_c_balance NUMERIC, os_o_id INTEGER, os_entdate TIMESTAMP, os_o_carrier_id INTEGER);
select delivery(92,7);