tscli.c

Mysql for Windows最新源码

"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"							AND C_ID = c_id;"
"		c_more_data := CONCAT("
"				' ', TO_CHAR(BINARY_TO_NUMBER(c_id)),"
"				' ', c_d_id,"
"				' ', TO_CHAR(BINARY_TO_NUMBER(c_w_id)),"
"				' ', d_id,"
"				' ', TO_CHAR(BINARY_TO_NUMBER(w_id)),"
"				TO_CHAR(amount),"
"				TO_CHAR(h_date),"
"				' ', h_data);"
""
"		more_len := LENGTH(c_more_data);"
"		data_len := LENGTH(c_data);"
"		"
"		IF (more_len + data_len > 500) THEN"
"			data_len := 500 - more_len;"
"		END IF;"
"		"
"		c_data := CONCAT(c_more_data, SUBSTR(c_data, 0, data_len));"
"						"
"		UPDATE CUSTOMER SET C_BALANCE = c_balance,"
"				C_PAYMENT_CNT = C_PAYMENT_CNT + 1,"
"				C_YTD_PAYMENT = C_YTD_PAYMENT + amount,"
"				C_DATA = c_data"
"			WHERE CURRENT OF customer_cursor;"
"	ELSE"
"		UPDATE CUSTOMER SET C_BALANCE = c_balance,"
"				C_PAYMENT_CNT = C_PAYMENT_CNT + 1,"
"				C_YTD_PAYMENT = C_YTD_PAYMENT + amount"
"			WHERE CURRENT OF customer_cursor;"
"	END IF;"
""
"	CLOSE customer_cursor;"
"	"
"	INSERT INTO HISTORY VALUES (c_d_id, c_w_id, c_id, d_id, w_id,"
"						h_date, amount, h_data);"
"	COMMIT WORK;"
""
"	END;"

	;

	str4 = ut_str_catenate(str1, str2);
	payment_str = ut_str_catenate(str4, str3);

	/*-----------------------------------------------------------*/

	str1 =
	
"	PROCEDURE ORDER_STATUS (c_w_id IN CHAR) IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	d_id CHAR;"
"	namecnt INT;"
"	c_d_id CHAR;"
"	c_id CHAR;"
"	c_last CHAR;"
"	c_first CHAR;"
"	c_middle CHAR;"
"	c_balance INT;"
"	byname INT;"
"	o_id INT;"
"	o_carrier_id CHAR;"
"	o_entry_d INT;"
"	ol_i_id CHAR;"
"	ol_supply_w_id CHAR;"
"	ol_quantity INT;"
"	ol_amount INT;"
"	ol_delivery_d INT;"
""
"	DECLARE CURSOR orders_cursor IS"
"		SELECT O_ID, O_CARRIER_ID, O_ENTRY_D"
"			FROM ORDERS"
"			WHERE O_W_ID = c_w_id AND O_D_ID = c_d_id"
"							AND O_C_ID = c_id"
"			ORDER BY O_ID DESC;"
""
"	DECLARE CURSOR order_line_cursor IS"
"		SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT,"
"							OL_DELIVERY_D"
"			FROM ORDER_LINE"
"			WHERE OL_W_ID = c_w_id AND OL_D_ID = c_d_id"
"							AND OL_O_ID = o_id;"
"	DECLARE CURSOR customer_by_name_cursor IS"
"		SELECT C_ID"
"			FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"				AND C_LAST = c_last"
"			ORDER BY C_FIRST ASC;"
"	BEGIN"
""
" 	n_items := 200;"
" 	n_warehouses := 1;"
" 	n_districts := 10;"
" 	n_customers := 20;"
""
"	byname := RND(1, 100);"
""
	;

	str2 =

"	IF (byname <= 60) THEN"
"		d_id := TO_BINARY(47 + RND(1, n_districts), 1);	"
""
"		c_d_id := d_id;"
""
"		c_last := CONCAT('NAME', TO_CHAR(RND(1, n_customers) / 3));"
""
"		SELECT COUNT(*) INTO namecnt"
"			FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"							AND C_LAST = c_last;"
"		OPEN customer_by_name_cursor;"
""
"		/* PRINTF('Order status trx: Customer name ', c_last,"
"						' namecount ', namecnt); */"
"		FOR i IN 1 .. (namecnt + 1) / 2 LOOP"
"			FETCH customer_by_name_cursor INTO c_id;"
"		END LOOP;"
"		/* ASSERT(NOT (customer_by_name_cursor % NOTFOUND)); */"
""
"		CLOSE customer_by_name_cursor;"
"	ELSE"
"		c_d_id := TO_BINARY(47 + RND(1, n_districts), 1);"
"		c_id := TO_BINARY(RND(1, n_customers), 3);"
"	END IF;"
""
"	SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST INTO c_balance, c_first,"
"							c_middle, c_last"
"		FROM CUSTOMER"
"		WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id AND C_ID = c_id;"
""
"	OPEN orders_cursor;"
""
"	FETCH orders_cursor INTO o_id, o_carrier_id, o_entry_d;"
""
"	IF (orders_cursor % NOTFOUND) THEN"
"		PRINTF('Order status trx: customer has no order');"
"		CLOSE orders_cursor;"
""
"		COMMIT WORK;"
""
"		RETURN;"
"	END IF;"
""
"	CLOSE orders_cursor;"
""
"	OPEN order_line_cursor;"
""
"	FOR i IN 0 .. 15 LOOP"
"		FETCH order_line_cursor INTO ol_i_id, ol_supply_w_id,"
"						ol_quantity, ol_amount,"
"						ol_delivery_d;"
""
"		IF (order_line_cursor % NOTFOUND) THEN"
"			CLOSE order_line_cursor;"
""
"			COMMIT WORK;"
""
"			RETURN;"
"		END IF;"
"	END LOOP;"
"	ASSERT(0 = 1);"
"	"
"	END;"
	;

	order_status_str = ut_str_catenate(str1, str2);
	/*-----------------------------------------------------------*/

	str1 =
	
"	PROCEDURE DELIVERY (w_id IN CHAR) IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	d_id CHAR;"
"	c_id CHAR;"
"	o_id INT;"
"	o_carrier_id INT;"
"	ol_delivery_d INT;"
"	ol_total INT;"
""
"	DECLARE CURSOR new_order_cursor IS"
"		SELECT NO_O_ID"
"			FROM NEW_ORDER"
"			WHERE NO_W_ID = w_id AND NO_D_ID = d_id"
"			ORDER BY NO_O_ID ASC;"
""
"	DECLARE CURSOR orders_cursor IS"
"		SELECT O_C_ID"
"			FROM ORDERS"
"			WHERE O_W_ID = w_id AND O_D_ID = d_id"
"							AND O_ID = o_id"
"			FOR UPDATE;"
"	BEGIN"
""
" 	n_items := 200;"
" 	n_warehouses := 1;"
" 	n_districts := 10;"
" 	n_customers := 20;"
""
"	o_carrier_id := RND(1, 10);"
"	ol_delivery_d := SYSDATE();"

	;

	str2 =

"	FOR i IN 1 .. n_districts LOOP"
""
"		d_id := TO_BINARY(47 + i, 1);"
""
"		OPEN new_order_cursor;"
""
"		FETCH new_order_cursor INTO o_id;"
""
"		IF (new_order_cursor % NOTFOUND) THEN"
"			/* PRINTF('No order to deliver'); */"
""
"			CLOSE new_order_cursor;"
"		ELSE"
"			CLOSE new_order_cursor;"
""
"			DELETE FROM NEW_ORDER"
"				WHERE NO_W_ID = w_id AND NO_D_ID = d_id"
"						AND NO_O_ID = o_id;"
"			OPEN orders_cursor;"
""
"			FETCH orders_cursor INTO c_id;"
""
"			UPDATE ORDERS SET O_CARRIER_ID = o_carrier_id"
"				WHERE CURRENT OF orders_cursor;"
""
"			CLOSE orders_cursor;"
""
"			UPDATE ORDER_LINE SET OL_DELIVERY_D = ol_delivery_d"
"				WHERE OL_W_ID = w_id AND OL_D_ID = d_id"
"						AND OL_O_ID = o_id;"
""
"			SELECT SUM(OL_AMOUNT) INTO ol_total"
"				FROM ORDER_LINE"
"				WHERE OL_W_ID = w_id AND OL_D_ID = d_id"
"						AND OL_O_ID = o_id;"
""
"			UPDATE CUSTOMER SET C_BALANCE = C_BALANCE - ol_total"
"				WHERE C_W_ID = w_id AND C_D_ID = d_id"
"						AND C_ID = c_id;"
"		END IF;"
"	END LOOP;"
"	COMMIT WORK;"
""
"	"
"	END;"
	;

	delivery_str = ut_str_catenate(str1, str2);

	/*-----------------------------------------------------------*/

	str =
	
"	PROCEDURE STOCK_LEVEL (w_id IN CHAR) IS"
""
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	d_id CHAR;"
"	o_id INT;"
"	stock_count INT;"
"	threshold INT;"
""
"	BEGIN"
""
" 	n_items := 200;"
" 	n_warehouses := 1;"
" 	n_districts := 10;"
" 	n_customers := 20;"
""
"	d_id := TO_BINARY(47 + 4, 1);"
""
"	threshold := RND(10, 20);"
""
"	SELECT D_NEXT_O_ID INTO o_id"
"		FROM DISTRICT"
"		WHERE D_W_ID = w_id AND D_ID = d_id;"
""
"	/* NOTE: COUNT(DISTINCT ...) not implemented yet: if we used a hash"
"	table, the DISTINCT operation should take at most 15 % more time */"
""
"	SELECT COUNT(*) INTO stock_count"
"		FROM ORDER_LINE, STOCK"
"		WHERE OL_W_ID = w_id AND OL_D_ID = d_id"
"			AND OL_O_ID >= o_id - 10 AND OL_O_ID < o_id"
"			AND S_W_ID = w_id AND S_I_ID = OL_I_ID"
"			AND S_QUANTITY < threshold"
"		CONSISTENT READ;"
"	/* PRINTF(stock_count, ' items under threshold ', threshold); */"
"	COMMIT WORK;"
""	
"	END;"
	;

	stock_level_str = str;

	/*-----------------------------------------------------------*/

	str =
	
"	PROCEDURE TPC_CONSISTENCY () IS"
""
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	n_orders INT;"
"	n_new_orders INT;"
"	n_order_lines INT;"
"	n_history INT;"
"	sum_order_quant INT;"
"	sum_stock_quant INT;"
"	n_delivered INT;"
"	n INT;"
"	n_new_order_lines INT;"
"	n_customers_d INT;"
"	n_regions INT;"
"	n_nations INT;"
"	n_suppliers INT;"
"	n_orders_d INT;"
"	n_lineitems INT;"
""
"	BEGIN"
""
"	PRINTF('TPC-C consistency check begins');"
""
"	SELECT COUNT(*) INTO n_warehouses"
"		FROM WAREHOUSE;"
"	SELECT COUNT(*) INTO n_items"
"		FROM ITEM;"
"	SELECT COUNT(*) INTO n_customers"
"		FROM CUSTOMER;"
"	SELECT COUNT(*) INTO n_districts"
"		FROM DISTRICT;"
"	SELECT COUNT(*) INTO n_orders"
"		FROM ORDERS;"	
"	SELECT COUNT(*) INTO n_new_orders"
"		FROM NEW_ORDER;"	
"	SELECT COUNT(*) INTO n_order_lines"
"		FROM ORDER_LINE;"	
"	SELECT COUNT(*) INTO n_history"
"		FROM HISTORY;"	
""
"	PRINTF('N warehouses ', n_warehouses);"
""
"	PRINTF('N items ', n_items, ' : ', n_items / n_warehouses,"
"							' per warehouse');"
"	PRINTF('N districts ', n_districts, ' : ', n_districts / n_warehouses,"
"							' per warehouse');"
"	PRINTF('N customers ', n_customers, ' : ', n_customers / n_districts,"
"							' per district');"
"	PRINTF('N orders ', n_orders, ' : ', n_orders / n_customers,"
"							' per customer');"
"	PRINTF('N new orders ', n_new_orders, ' : ',"
"				n_new_orders / n_customers, ' per customer');"
"	PRINTF('N order lines ', n_order_lines, ' : ',"
"				n_order_lines / n_orders, ' per order');"
"	PRINTF('N history ', n_history, ' : ',"
"				n_history / n_customers, ' per customer');"
"	SELECT COUNT(*) INTO n_delivered"
"		FROM ORDER_LINE"
"		WHERE OL_DELIVERY_D < NULL;"
""
"	PRINTF('N delivered order lines ', n_delivered);"
""
"	SELECT COUNT(*) INTO n_new_order_lines"
"		FROM NEW_ORDER, ORDER_LINE"
"		WHERE NO_O_ID = OL_O_ID AND NO_D_ID = OL_D_ID"
"						AND NO_W_ID = OL_W_ID;"
"	PRINTF('N new order lines ', n_new_order_lines);"
""
"	SELECT COUNT(*) INTO n"
"		FROM NEW_ORDER, ORDER_LINE"
"		WHERE NO_O_ID = OL_O_ID AND NO_D_ID = OL_D_ID"
"			AND NO_W_ID = OL_W_ID AND OL_DELIVERY_D < NULL;"
"	PRINTF('Assertion 1');"
"	ASSERT(n = 0);"
""
"	SELECT COUNT(*) INTO n"
"		FROM NEW_ORDER, ORDER_LINE"
"		WHERE NO_O_ID = OL_O_ID AND NO_D_ID = OL_D_ID"
"			AND NO_W_ID = OL_W_ID AND OL_DELIVERY_D = NULL;"
""				
"	PRINTF('Assertion 2');"
"	ASSERT(n = n_new_order_lines);"
"	PRINTF('Assertion 2B');"
"	ASSERT(n_delivered + n_new_order_lines = n_order_lines);"
""
"	PRINTF('Assertion 3');"
"	/* ASSERT(n_orders <= n_history); */"
"	PRINTF('Assertion 4');"
"	ASSERT(n_order_lines <= 15 * n_orders);"
"	PRINTF('Assertion 5');"
"	ASSERT(n_order_lines >= 5 * n_orders);"
"	PRINTF('Assertion 6');"
"	ASSERT(n_new_orders <= n_orders);"
""
"	SELECT SUM(OL_QUANTITY) INTO sum_order_quant"
"		FROM ORDER_LINE;"
"	SELECT SUM(S_QUANTITY) INTO sum_stock_quant"
"		FROM STOCK;"
"	PRINTF('Sum order quant ', sum_order_quant, ' sum stock quant ',"
"							sum_stock_quant);"
""
"	PRINTF('Assertion 7');"
"	ASSERT(((sum_stock_quant + sum_order_quant) / 91) * 91"
"				= sum_stock_quant + sum_order_quant);"
"	COMMIT WORK;"
"	PRINTF('TPC-C consistency check passed');"
""
"	PRINTF('TPC-D consistency check begins');"
""
"	SELECT COUNT(*) INTO n_customers_d"
"		FROM CUSTOMER_D"
"		CONSISTENT READ;"
"	SELECT COUNT(*) INTO n_nations"
"		FROM NATION"
"		CONSISTENT READ;"
"	SELECT COUNT(*) INTO n_regions"
"		FROM REGION"
"		CONSISTENT READ;"
"	SELECT COUNT(*) INTO n_suppliers"
"		FROM SUPPLIER"
"		CONSISTENT READ;"
"	SELECT COUNT(*) INTO n_orders_d"
"		FROM ORDERS_D"	
"		CONSISTENT READ;"
"	SELECT COUNT(*) INTO n_lineitems"
"		FROM LINEITEM"	
"		CONSISTENT READ;"
""
"	PRINTF('N customers TPC-D ', n_customers_d);"
""
"	PRINTF('N nations ', n_nations);"
"	PRINTF('N regions ', n_regions);"
""
"	PRINTF('N suppliers ', n_suppliers);"
"	PRINTF('N orders TPC-D ', n_orders_d);"
""
"	PRINTF('N lineitems ', n_lineitems, ' : ',"
"				n_lineitems / n_orders_d, ' per order');"
"	SELECT COUNT(*) INTO n"
"		FROM NATION, NATION_2"
"		WHERE N_NAME = N2_NAME"
"		CONSISTENT READ;"
""
"	PRINTF('Assertion D1');"
"	ASSERT(n = n_nations);"
""
"	SELECT COUNT(*) INTO n"
"		FROM NATION, REGION"
"		WHERE N_REGIONKEY = R_REGIONKEY"
"		CONSISTENT READ;"
""
"	PRINTF('Assertion D2');"
"	ASSERT(n = n_nations);"
""
"	SELECT COUNT(*) INTO n"
"		FROM ORDERS_D, CUSTOMER_D"
"		WHERE O_CUSTKEY = C_CUSTKEY"
"		CONSISTENT READ;"
""
"	PRINTF('Assertion D3');"
"	ASSERT(n = n_orders_d);"
""
"	SELECT COUNT(*) INTO n"
"		FROM LINEITEM, SUPPLIER"
"		WHERE L_SUPPKEY = S_SUPPKEY"
"		CONSISTENT READ;"
""
"	PRINTF('Assertion D4');"
"	ASSERT(n = n_lineitems);"
""
"	SELECT COUNT(*) INTO n"
"		FROM ORDERS_D"
"		WHERE O_ORDERDATE >= 0"
"			AND O_ORDERDATE <= 2500"
"		CONSISTENT READ;"
""
"	PRINTF('Assertion D5');"
"	ASSERT(n = n_orders_d);"
""
"	COMMIT WORK;"
"	PRINTF('TPC-D consistency check passed');"
""
"	END;"
	;

	consistency_str = str;

	/*-----------------------------------------------------------*/
	str =
	
"	PROCEDURE TPC_D_QUERY_5 (startday IN INT, endday IN INT) IS"
""
"	revenue INT;"
"	r_name CHAR;"
""
"	BEGIN"
""
"	r_name := CONCAT('Region', TO_CHAR(3), '               ');"
""
"	/* The last join to NATION_2 corresponds to calculating"
"	GROUP BY N_NAME in the original TPC-D query. It should take"
"	approximately the same amount of CPU time as GROUP BY. */"
""
"	SELECT SUM((L_EXTENDEDPRICE * (100 - L_DISCOUNT)) / 100)"
"					INTO revenue"
"		FROM REGION, ORDERS_D, CUSTOMER_D, NATION,"
"					LINEITEM, SUPPLIER, NATION_2"
"		WHERE R_NAME = r_name"
"			AND O_ORDERDATE >= startday"
"			AND O_ORDERDATE < endday"
"			AND O_CUSTKEY = C_CUSTKEY"
"			AND C_NATIONKEY = N_NATIONKEY"
"			AND N_REGIONKEY = R_REGIONKEY"
"			AND O_ORDERKEY = L_ORDERKEY"
"			AND L_SUPPKEY = S_SUPPKEY"
"			AND S_NATIONKEY = C_NATIONKEY"
"			AND N_NAME = N2_NAME"
"		CONSISTENT READ;"
""
"	PRINTF('Startdate ', startday, '; enddate ', endday,"
"						': revenue ', revenue);"
"	COMMIT WORK;"
""	
"	END;"
	;

	query_5_str = str;
	/*-----------------------------------------------------------*/
	str =
	
"	PROCEDURE ROLLBACK_QUERY () IS"
""
"	BEGIN"