#!/bin/sh

#
# This file is released under the terms of the Artistic License.
# Please see the file LICENSE, included in this package, for details.
#
# Copyright The DBT-2 Authors
#

#
# If the SQL in funcs.c is updated, this file should be updated to match.
#

usage()
{
	cat << EOF
$(basename "${0}") is the Database Test 2 (DBT-2) PostgreSQL plan collector.

Usage:
  $(basename "${0}") [OPTION]

Options:
  -h ADDRESS, --db-host=ADDRESS
                 ADDRESS of database system
  -d NAME, --db-name=NAME
                 NAME of the database
  -l PORT, --db-port=PORT
                 database listening PORT number
  -V, --version  output version information, then exit
  -?, --help     show this help, then exit

Database Test 2 (DBT-2) project page: https://github.com/osdldbt/dbt2
EOF
}

explain() {
	SQL="EXPLAIN ${*}"
	eval "psql ${HOSTNAMEARG} ${PORTARG} -e -d ${DBT2DBNAME} -c \"${SQL}\"" \
			>> "${OUTPUT_FILE}" 2>&1
}

while [ "${#}" -gt 0 ] ; do
	case "${1}" in
	(-d?*)
		DBT2DBNAME="${1#*-d}"
		;;
	(-d | --db-name)
		shift
		DBT2DBNAME=${1}
		;;
	(--db-name=?*)
		DBT2DBNAME=${1#*--db-name=}
		;;
	(-h?*)
		HOSTNAMEARG="-h ${1#*-h}"
		;;
	(-h | --db-host)
		shift
		HOSTNAMEARG="-h ${1}"
		;;
	(--db-host=?*)
		HOSTNAMEARG="-h ${1#*--db-host=}"
		;;
	(-l?*)
		PORT="${1#*-l}"
		;;
	(-l | --db-port)
		shift
		PORT="${1}"
		;;
	(--db-port=?*)
		PORT="${1#*--db-port=}"
		;;
	(-o?*)
		OUTPUT_FILE="${1#*-o}"
		;;
	(-o)
		shift
		OUTPUT_FILE="${1}"
		;;
	(-\? | --help)
		usage
		exit 0
		;;
	(--* | -*)
		echo "$(basename "${0}"): invalid option -- '${1}'"
		echo "try \"$(basename "${0}") --help\" for more information."
		exit 1
		;;
	(*)
		break
		;;
	esac
	shift
done

if [ "${DBT2DBNAME}" = "" ]; then
	echo "ERROR: DBT2DBNAME not defined, use -d flag"
	exit 1
fi

if [ ! "${PORT}" = "" ]; then
	PORTARG="-p ${PORT}"
fi

if [ "${OUTPUT_FILE}" = "" ]; then
	echo "usage: $(basename "$0") -o <outfile>"
	exit 1
fi

{
	echo "--------------------"
	echo "Delivery Transaction"
	echo "--------------------"
} >> "${OUTPUT_FILE}"

explain "
SELECT no_o_id FROM new_order WHERE no_w_id = 1 AND no_d_id = 1;
"

explain "
DELETE FROM new_order WHERE no_o_id = 1 AND no_w_id = 1 AND no_d_id = 1;
"

explain "
SELECT o_c_id FROM orders WHERE o_id = 1 AND o_w_id = 1 AND o_d_id = 1;
"

explain "
UPDATE orders SET o_carrier_id = 1 WHERE o_id = 1 AND o_w_id = 1 AND o_d_id = 1;
"

explain "
UPDATE order_line SET ol_delivery_d = current_timestamp WHERE ol_o_id = 1 AND ol_w_id = 1 AND ol_d_id = 1;
"

explain "
SELECT SUM(ol_amount * ol_quantity) FROM order_line WHERE ol_o_id = 1 AND ol_w_id = 1 AND ol_d_id = 1;
"

explain "
UPDATE customer SET c_delivery_cnt = c_delivery_cnt + 1, c_balance = c_balance + 1 WHERE c_id = 1 AND c_w_id = 1 AND c_d_id = 1;
"

{
	echo "---------------------"
	echo "New-Order Transaction"
	echo "---------------------"
} >> "${OUTPUT_FILE}"

explain "
SELECT w_tax FROM warehouse WHERE w_id = 1;
"

explain "
SELECT d_tax, d_next_o_id FROM district WHERE d_w_id = 1 AND d_id = 1;
"

explain "
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 1 AND d_id = 1;
"

explain "
SELECT c_discount, c_last, c_credit FROM customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 1;
"

explain "
INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (-1, 1, 1);
"

explain "
INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all_local) VALUES (-1, 1, 1, 1, current_timestamp, NULL, 1, 1);
"

explain "
SELECT i_price, i_name, i_data FROM item WHERE i_id = 1;
"

explain "
SELECT s_quantity, s_dist_01, s_data FROM stock WHERE s_i_id = 1 AND s_w_id = 1;
"

explain "
UPDATE stock SET s_quantity = s_quantity - 10 WHERE s_i_id = 1 AND s_w_id = 1;
"

explain "
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_delivery_d, ol_quantity, ol_amount, ol_dist_info) VALUES (-1, 1, 1, 1, 1, 1, NULL, 1, 1.0, 'hello kitty');
"

{
	echo "------------------------"
	echo "Order-Status Transaction"
	echo "------------------------"
} >> "${OUTPUT_FILE}"

explain "
SELECT c_id FROM customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'BARBARBAR' ORDER BY c_first ASC;
"

explain "
SELECT c_first, c_middle, c_last, c_balance FROM customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 1;
"

explain "
SELECT o_id, o_carrier_id, o_entry_d, o_ol_cnt FROM orders WHERE o_w_id = 1 AND o_d_id = 1 AND o_c_id = 1 ORDER BY o_id DESC;
"

explain "
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM order_line WHERE ol_w_id = 1 AND ol_d_id = 1 AND ol_o_id = 1;
"

{
	echo "-------------------"
	echo "Payment Transaction"
	echo "-------------------"
} >> "${OUTPUT_FILE}"

explain "
SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM warehouse WHERE w_id = 1;
"

explain "
UPDATE warehouse SET w_ytd = w_ytd + 1.0 WHERE w_id = 1;
"

explain "
SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM district WHERE d_id = 1 AND d_w_id = 1;
"

explain "
UPDATE district SET d_ytd = d_ytd + 1.0 WHERE d_id = 1 AND d_w_id = 1;
"

explain "
SELECT c_id FROM customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'BARBARBAR' ORDER BY c_first ASC;
"

explain "
SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance, c_data, c_ytd_payment FROM customer WHERE c_w_id = 1 AND c_d_id = 1 AND c_id = 1;
"

explain "
UPDATE customer SET c_balance = c_balance - 1.0, c_ytd_payment = c_ytd_payment + 1 WHERE c_id = 1 AND c_w_id = 1 AND c_d_id = 1;
"

explain "
UPDATE customer SET c_balance = c_balance - 1.0, c_ytd_payment = c_ytd_payment + 1, c_data = 'hello dogger' WHERE c_id = 1 AND c_w_id
 = 1 AND c_d_id = 1;
"

explain "
INSERT INTO history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES (1, 1, 1, 1, 1, current_timestamp, 1.0, 'ab    cd');
"

{
	echo "-----------------------"
	echo "Stock-Level Transaction"
	echo "-----------------------"
} >> "${OUTPUT_FILE}"

explain "
SELECT d_next_o_id FROM district WHERE d_w_id = 1 AND d_id = 1;
"

explain "
SELECT count(*) FROM order_line, stock, district WHERE d_id = 1 AND d_w_id = 1 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 15 AND ol_o_id BETWEEN (1) AND (20);
"
