tscli.c

Mysql for Windows最新源码

"		ol_i_id := TO_BINARY(RND(1, n_items), 3);"
""
"		ol_supply_w_id := c_w_id;"
""
"		ol_quantity := RND(1, 10);"
""
"		SELECT I_PRICE, I_NAME, I_DATA INTO i_price, i_name, i_data"
"		FROM ITEM"
"			WHERE I_ID = ol_i_id;"
""
"		IF (SQL % NOTFOUND) THEN"
"			PRINTF('Rolling back');"
"			ROLLBACK WORK;"
""
"			RETURN;"
"		END IF;"
""
"		OPEN stock_cursor;"
""
"		FETCH stock_cursor INTO 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;"
""
"		IF (s_quantity >= ol_quantity + 10) THEN"
"			s_quantity := s_quantity - ol_quantity;"
"		ELSE"
"			s_quantity := (s_quantity + 91) - ol_quantity;"
"		END IF;"
""
"		UPDATE STOCK SET S_QUANTITY = s_quantity,"
"				S_YTD = S_YTD + ol_quantity,"
"				S_ORDER_CNT = S_ORDER_CNT + 1"
"			WHERE CURRENT OF stock_cursor;"
""
"		IF (ol_supply_w_id <> c_w_id) THEN"
""
"			o_all_local := '0';"
"			PRINTF('Remote order ',"
"				BINARY_TO_NUMBER(ol_supply_w_id), ' ',"
"				BINARY_TO_NUMBER(c_w_id));"
""
"			UPDATE STOCK SET S_REMOTE_CNT = S_REMOTE_CNT + 1"
"				WHERE CURRENT OF stock_cursor;"
"		END IF;"
""
"		CLOSE stock_cursor;"
""
"		IF ((INSTR(i_data, 'ORIGINAL') > 0)"
"				OR (INSTR(s_data, 'ORIGINAL') > 0)) THEN"
"			REPLSTR(bg, 'B', i - 1, 1);"
"		END IF;"
""
"		ol_amount := ol_quantity * i_price;"
""
"		total := total + ol_amount;"
	;
	str4 =
"		IF (c_d_id = '0') THEN"
"			ol_dist_info := s_dist_01;"
"		ELSIF (c_d_id = '1') THEN"
"			ol_dist_info := s_dist_02;"
"		ELSIF (c_d_id = '2') THEN"
"			ol_dist_info := s_dist_03;"
"		ELSIF (c_d_id = '3') THEN"
"			ol_dist_info := s_dist_04;"
"		ELSIF (c_d_id = '4') THEN"
"			ol_dist_info := s_dist_05;"
"		ELSIF (c_d_id = '5') THEN"
"			ol_dist_info := s_dist_06;"
"		ELSIF (c_d_id = '6') THEN"
"			ol_dist_info := s_dist_07;"
"		ELSIF (c_d_id = '7') THEN"
"			ol_dist_info := s_dist_08;"
"		ELSIF (c_d_id = '8') THEN"
"			ol_dist_info := s_dist_09;"
"		ELSIF (c_d_id = '9') THEN"
"			ol_dist_info := s_dist_10;"
"		END IF;"
""
"		INSERT INTO ORDER_LINE VALUES (o_id, c_d_id, c_w_id,"
"					TO_BINARY(i, 1), ol_i_id,"
"					ol_supply_w_id, NULL, ol_quantity,"
"					ol_amount, ol_dist_info); "
"	END LOOP;"
""
"	total := (((total * (10000 + w_tax + d_tax)) / 10000)"
"			  * (10000 - c_discount)) / 10000;"
""
"	o_entry_d := SYSDATE();"
""
"	INSERT INTO ORDERS VALUES (o_id, c_d_id, c_w_id, c_id, o_entry_d,"
"					NULL, o_ol_cnt, o_all_local);"
"	INSERT INTO NEW_ORDER VALUES (o_id, c_d_id, c_w_id);"
""
"	/* PRINTF('Inserted order lines:');"
"	ROW_PRINTF"
"		SELECT * FROM ORDER_LINE WHERE OL_O_ID = o_id AND"
"						OL_D_ID = c_d_id"
"						AND OL_W_ID = c_w_id; */"
" 	/* COMMIT WORK; */"
" 	END;"
	;

	str5 = ut_str_catenate(str1, str2);
	str6 = ut_str_catenate(str3, str4);

	str = ut_str_catenate(str5, str6);
	
	new_order_query = pars_sql(str);

	mem_free(str);
	mem_free(str5);
	mem_free(str6);

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

	str1 =
	
"	PROCEDURE PAYMENT () IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	w_id CHAR;"
"	w_street_1 CHAR;"
"	w_street_2 CHAR;"
"	w_city CHAR;"
"	w_state CHAR;"
"	w_zip CHAR;"
"	w_name CHAR;"
"	d_id CHAR;"
"	d_street_1 CHAR;"
"	d_street_2 CHAR;"
"	d_city CHAR;"
"	d_state CHAR;"
"	d_zip CHAR;"
"	d_name CHAR;"
"	c_w_id CHAR;"
"	c_d_id CHAR;"
"	c_street_1 CHAR;"
"	c_street_2 CHAR;"
"	c_city CHAR;"
"	c_state CHAR;"
"	c_zip CHAR;"
"	c_id CHAR;"
"	c_last CHAR;"
"	c_first CHAR;"
"	c_middle CHAR;"
"	c_phone CHAR;"
"	c_credit CHAR;"
"	c_credit_lim INT;"
"	c_discount INT;"
"	c_balance INT;"
"	c_since INT;"
"	c_data CHAR;"
"	byname INT;"
"	namecnt INT;"
"	amount INT;"
"	h_data CHAR;"
"	h_date INT;"
"	c_more_data CHAR;"
"	more_len INT;"
"	data_len INT;"
""
"	DECLARE CURSOR warehouse_cursor IS"
"		SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME"
"		FROM WAREHOUSE"
"			WHERE W_ID = w_id"
"			FOR UPDATE;"
""
"	DECLARE CURSOR district_cursor IS"
"		SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME"
"		FROM DISTRICT"
"			WHERE D_W_ID = w_id AND D_ID = d_id"
"			FOR UPDATE;"
""
"	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;"
""
"	DECLARE CURSOR customer_cursor IS"
"		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"
"		FROM CUSTOMER"
"			WHERE C_W_ID = c_w_id AND C_D_ID = c_d_id"
"							AND C_ID = c_id"
"			FOR UPDATE;"
	;

	str2 =
	
"	BEGIN"
""
" 	n_items := 200;"
" 	n_warehouses := 1;"
" 	n_districts := 10;"
" 	n_customers := 200;"
""
"	byname := RND(1, 100);"
"	amount := RND(1, 1000);"
"	h_date := SYSDATE();"
"	w_id := TO_BINARY(RND(1, n_warehouses), 2);"
"	d_id := TO_BINARY(47 + RND(1, n_districts), 1);"
"	c_w_id := TO_BINARY(RND(1, n_warehouses), 2);"
"	c_d_id := TO_BINARY(47 + RND(1, n_districts), 1);"
""
"	OPEN warehouse_cursor;"
""
"	FETCH warehouse_cursor INTO w_street_1, w_street_2, w_city, w_state,"
"							w_zip, w_name;"
"	UPDATE WAREHOUSE SET W_YTD = W_YTD + amount"
"		WHERE CURRENT OF warehouse_cursor;"
""
"	CLOSE warehouse_cursor;"
""
"	OPEN district_cursor;"
""
"	FETCH district_cursor INTO d_street_1, d_street_2, d_city, d_state,"
"							d_zip, d_name;"
"	UPDATE DISTRICT SET D_YTD = D_YTD + amount"
"		WHERE CURRENT OF district_cursor;"
""
"	CLOSE district_cursor;"
""
"	IF (byname <= 60) THEN"
"		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;"
"		/* PRINTF('Payment trx: Customer name ', c_last,"
"						' namecount ', namecnt); */"
"		OPEN customer_by_name_cursor;"
""
"		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_id := TO_BINARY(RND(1, n_customers), 3);"
"	END IF;"

	;
	str3 =
""
"	/* PRINTF('Payment for customer ', BINARY_TO_NUMBER(c_w_id), ' ',"
"				c_d_id, ' ', BINARY_TO_NUMBER(c_id)); */"
"	OPEN customer_cursor;"
""
"	FETCH customer_cursor INTO 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;"
"	c_balance := c_balance - amount;"
""
"	h_data := CONCAT(w_name, '    ', d_name);"
"	"
"	IF (c_credit = 'BC') THEN"
"		/* PRINTF('Bad customer pays'); */"
""
"		SELECT C_DATA INTO c_data"
"		FROM CUSTOMER"
"			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);
	str = ut_str_catenate(str4, str3);
	
	payment_query = pars_sql(str);

	mem_free(str);
	mem_free(str4);

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

	str1 =
	
"	PROCEDURE ORDER_STATUS () IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	d_id CHAR;"
"	namecnt INT;"
"	c_w_id CHAR;"
"	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 := 200;"
""
"	c_w_id := TO_BINARY(RND(1, n_warehouses), 2);"
"	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;"
	;

	str = ut_str_catenate(str1, str2);
	
	order_status_query = pars_sql(str);

	mem_free(str);

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

	str1 =
	
"	PROCEDURE DELIVERY () IS"
""
" 	i INT;"
" 	n_items INT;"
" 	n_warehouses INT;"
" 	n_districts INT;"
"	n_customers INT;"
"	d_id CHAR;"
"	w_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 := 200;"
""
"	w_id := TO_BINARY(RND(1, n_warehouses), 2);"
"	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;"
"			/* PRINTF('Order to deliver'); */"
""
"			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;"
	;

	str = ut_str_catenate(str1, str2);
	
	delivery_query = pars_sql(str);

	mem_free(str);

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

	/* NOTE: COUNT(DISTINCT ...) not implemented yet */

	str =