#!/bin/bash

#
# 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
#

usage() {
	cat << EOF
$(basename "${0}") is the Database Test 2 (DBT-2) SQLite database builder.

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

Options:
  -d FILENAME    database FILENAME
  -f PATH        PATH to dataset files
  -g             generate data files
  -m SCHEME      database SCHEME [OPTIMIZED|ORIG], default OPTIMIZED
  --rows-per-commit ROWS
                 number of ROWS to load per transaction, default is to load all
                 data in a single transaction
  -v             verbose
  -w WAREHOUSES  number of WAREHOUSES to build, default: 1
  -?, --help     show this help, then exit

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

command_exec()
{
  if [ -n "$VERBOSE" ]; then
    echo "Executed command: $1"
  fi

  eval "$1"

  rc=$?
  if [ $rc -ne 0 ]; then
   echo "ERROR: rc=$rc"
   case $rc in
     127) echo "COMMAND NOT FOUND"
	  ;;
       *) echo "SCRIPT INTERRUPTED"
	  ;;
    esac
    exit 255
  fi
}

load_tables()
{

TABLES="customer district history item new_order order_line orders stock warehouse"

for TABLE in $TABLES ; do

  echo "Loading table $TABLE"

  if [ "$TABLE" == "orders" ]; then
    FN="order"
  else
    FN="$TABLE"
  fi

  command_exec "$SQLITE -separator \"	\" $DB_NAME \".import $DB_PATH/$FN.data $TABLE\""

done

}


create_tables()
{

if [ "$DB_SCHEME" == "OPTIMIZED" ]; then

CUSTOMER="CREATE TABLE customer (
  c_id int(11) NOT NULL default '0',
  c_d_id int(11) NOT NULL default '0',
  c_w_id int(11) NOT NULL default '0',
  c_first varchar(16) default NULL,
  c_middle char(2) default NULL,
  c_last varchar(16) default NULL,
  c_street_1 varchar(20) default NULL,
  c_street_2 varchar(20) default NULL,
  c_city varchar(20) default NULL,
  c_state char(2) default NULL,
  c_zip varchar(9) default NULL,
  c_phone varchar(16) default NULL,
  c_since timestamp NOT NULL,
  c_credit char(2) default NULL,
  c_credit_lim decimal(24,12) default NULL,
  c_discount double default NULL,
  c_balance decimal(24,12) default NULL,
  c_ytd_payment decimal(24,12) default NULL,
  c_payment_cnt double default NULL,
  c_delivery_cnt double default NULL,
  c_data text,
  PRIMARY KEY  (c_w_id,c_d_id,c_id)
)"

CUSTOMER_INDEX="CREATE INDEX c_w_id ON customer (c_w_id,c_d_id,c_last,c_first)"

DISTRICT="CREATE TABLE district (
  d_id int(11) NOT NULL default '0',
  d_w_id int(11) NOT NULL default '0',
  d_name varchar(10) default NULL,
  d_street_1 varchar(20) default NULL,
  d_street_2 varchar(20) default NULL,
  d_city varchar(20) default NULL,
  d_state char(2) default NULL,
  d_zip varchar(9) default NULL,
  d_tax double default NULL,
  d_ytd decimal(24,12) default NULL,
  d_next_o_id int(11) default NULL,
  PRIMARY KEY  (d_w_id,d_id)
)"

HISTORY="CREATE TABLE history (
  h_c_id int(11) default NULL,
  h_c_d_id int(11) default NULL,
  h_c_w_id int(11) default NULL,
  h_d_id int(11) default NULL,
  h_w_id int(11) default NULL,
  h_date timestamp NOT NULL,
  h_amount double default NULL,
  h_data varchar(24) default NULL
)"


ITEM="CREATE TABLE item (
  i_id int(11) NOT NULL default '0',
  i_im_id int(11) default NULL,
  i_name varchar(24) default NULL,
  i_price double default NULL,
  i_data varchar(50) default NULL,
  PRIMARY KEY  (i_id)
)"


NEW_ORDER="CREATE TABLE new_order (
  no_o_id int(11) NOT NULL default '0',
  no_d_id int(11) NOT NULL default '0',
  no_w_id int(11) NOT NULL default '0',
  PRIMARY KEY  (no_d_id,no_w_id,no_o_id)
)"

ORDER_LINE="CREATE TABLE order_line (
  ol_o_id int(11) NOT NULL default '0',
  ol_d_id int(11) NOT NULL default '0',
  ol_w_id int(11) NOT NULL default '0',
  ol_number int(11) NOT NULL default '0',
  ol_i_id int(11) default NULL,
  ol_supply_w_id int(11) default NULL,
  ol_delivery_d timestamp NOT NULL,
  ol_quantity double default NULL,
  ol_amount double default NULL,
  ol_dist_info varchar(24) default NULL,
  PRIMARY KEY  (ol_w_id,ol_d_id,ol_o_id,ol_number)
)"

ORDERS="CREATE TABLE orders (
  o_id int(11) NOT NULL default '0',
  o_d_id int(11) NOT NULL default '0',
  o_w_id int(11) NOT NULL default '0',
  o_c_id int(11) default NULL,
  o_entry_d timestamp NOT NULL,
  o_carrier_id int(11) default NULL,
  o_ol_cnt int(11) default NULL,
  o_all_local double default NULL,
  PRIMARY KEY  (o_w_id,o_d_id,o_id)
)"

ORDERS_INDEX="CREATE INDEX o_w_id ON orders (o_w_id,o_d_id,o_c_id,o_id)"

STOCK="CREATE TABLE stock (
  s_i_id int(11) NOT NULL default '0',
  s_w_id int(11) NOT NULL default '0',
  s_quantity double NOT NULL default '0',
  s_dist_01 varchar(24) default NULL,
  s_dist_02 varchar(24) default NULL,
  s_dist_03 varchar(24) default NULL,
  s_dist_04 varchar(24) default NULL,
  s_dist_05 varchar(24) default NULL,
  s_dist_06 varchar(24) default NULL,
  s_dist_07 varchar(24) default NULL,
  s_dist_08 varchar(24) default NULL,
  s_dist_09 varchar(24) default NULL,
  s_dist_10 varchar(24) default NULL,
  s_ytd decimal(16,8) default NULL,
  s_order_cnt double default NULL,
  s_remote_cnt double default NULL,
  s_data varchar(50) default NULL,
  PRIMARY KEY  (s_w_id,s_i_id)
)"

WAREHOUSE="CREATE TABLE warehouse (
  w_id int(11) NOT NULL default '0',
  w_name varchar(10) default NULL,
  w_street_1 varchar(20) default NULL,
  w_street_2 varchar(20) default NULL,
  w_city varchar(20) default NULL,
  w_state char(2) default NULL,
  w_zip varchar(9) default NULL,
  w_tax double default NULL,
  w_ytd decimal(24,12) default NULL,
  PRIMARY KEY  (w_id)
)"

INDEXES="CUSTOMER_INDEX ORDERS_INDEX"

else

WAREHOUSE="create table warehouse ( w_id int not null, 
  w_name varchar(10), 
  w_street_1 varchar(20), 
  w_street_2 varchar(20), 
  w_city varchar(20), 
  w_state char(2), 
  w_zip char(9), 
  w_tax real, 
  w_ytd numeric(24, 12), 
  constraint pk_warehouse primary key (w_id) 
)"


DISTRICT="create table district ( d_id int not null,
  d_w_id int not null,
  d_name varchar(10),
  d_street_1 varchar(20),
  d_street_2 varchar(20),
  d_city varchar(20),
  d_state char(2),
  d_zip char(9),
  d_tax real,
  d_ytd numeric(24, 12),
  d_next_o_id int,
  constraint pk_district primary key (d_w_id, d_id)
)"

CUSTOMER="create table customer ( c_id int not null, 
  c_d_id int not null, 
  c_w_id int not null, 
  c_first varchar(16), 
  c_middle char(2), 
  c_last varchar(16),   
  c_street_1 varchar(20),	       
  c_street_2 varchar(20), 
  c_city varchar(20), 
  c_state char(2),  
  c_zip char(9), 
  c_phone char(16), 
  c_since timestamp, 
  c_credit char(2), 
  c_credit_lim numeric(24, 12), 
  c_discount real, 
  c_balance numeric(24, 12), 
  c_ytd_payment numeric(24, 12), 
  c_payment_cnt real, 
  c_delivery_cnt real, 
  c_data text, 
  constraint pk_customer primary key (c_w_id, c_d_id, c_id)
)"

HISTORY="create table history ( h_c_id int, 
  h_c_d_id int, 
  h_c_w_id int, 
  h_d_id int, 
  h_w_id int, 
  h_date timestamp, 
  h_amount real, 
  h_data varchar(24)
)"

NEW_ORDER="create table new_order ( no_o_id int not null, 
  no_d_id int not null, 
  no_w_id int not null, 
  constraint pk_new_order primary key (no_o_id, no_d_id, no_w_id) 
)"

ORDERS="create table orders ( o_id int not null, 
  o_d_id int not null, 
  o_w_id int not null, 
  o_c_id int,
  o_entry_d timestamp,
  o_carrier_id int,
  o_ol_cnt int,
  o_all_local real,
  constraint pk_orders primary key (o_w_id, o_d_id, o_id)
)"

ORDER_LINE="create table order_line ( ol_o_id int not null,
  ol_d_id int not null,
  ol_w_id int not null,
  ol_number int not null,
  ol_i_id int,
  ol_supply_w_id int,
  ol_delivery_d timestamp,
  ol_quantity real,
  ol_amount real, 
  ol_dist_info varchar(24),
  constraint pk_order_line primary key (ol_w_id, ol_d_id, ol_o_id, ol_number)
)"

ITEM="create table item ( i_id int not null,
  i_im_id int,
  i_name varchar(24), 
  i_price real, 
  i_data varchar(50), 
  constraint pk_item primary key (i_id) 
)"

STOCK="create table stock ( s_i_id int not null, 
  s_w_id int not null, 
  s_quantity real, 
  s_dist_01 varchar(24), 
  s_dist_02 varchar(24), 
  s_dist_03 varchar(24), 
  s_dist_04 varchar(24), 
  s_dist_05 varchar(24), 
  s_dist_06 varchar(24), 
  s_dist_07 varchar(24), 
  s_dist_08 varchar(24), 
  s_dist_09 varchar(24), 
  s_dist_10 varchar(24), 
  s_ytd numeric(16, 8), 
  s_order_cnt real, 
  s_remote_cnt real, 
  s_data varchar(50), 
  constraint pk_stock primary key (s_w_id, s_i_id, s_quantity) 
)"
fi

TABLES="STOCK ITEM ORDER_LINE ORDERS NEW_ORDER HISTORY CUSTOMER DISTRICT WAREHOUSE"

for TABLE in $TABLES ; do

  echo "Creating table $TABLE"
  command_exec "$SQLITE $DB_NAME  \"\$$TABLE\""

done

for INDEX in $INDEXES ; do

  echo "Creating secondary index $INDEX"
  command_exec "$SQLITE $DB_NAME  \"\$$INDEX\""

done

}

#DEFAULTs

DIR=`dirname ${0}`

LOCAL=""
VERBOSE=""
DB_PATH=""
DB_NAME=""

SQLITE="sqlite3"
DB_NAME="./dbt2.sqlite"
DB_PATH=${DBDATA}
DB_SCHEME="OPTIMIZED"
GENERATE_DATAFILE=0
ROWS_PER_COMMIT=0
WAREHOUSES=1

while [ "${#}" -gt 0 ] ; do
	case "${1}" in
	(-d)
		shift
		DB_NAME="${1}"
		;;
	(-f)
		shift
		DB_PATH="${1}"
		;;
	(-g)
		GENERATE_DATAFILE=1
		;;
	(-m)
		shift
		DB_SCHEME="${1}"
		;;
	(--rows-per-commit)
		shift
		ROWS_PER_COMMIT="${1}"
		;;
	(--rows-per-commit=?*)
		ROWS_PER_COMMIT="${1#*--rows-per-commit=}"
		;;
	(-v)
		VERBOSE=1
		;;
	(-w)
		shift
		WAREHOUSES="${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

# Check parameters.
if [ "$DB_PATH" == "" ]; then
  usage "specify path where dataset txt files are located - using -f <absolute path>"
  exit 1
fi

mkdir -p $DB_PATH
if [ ! -d "$DB_PATH" ]; then
  usage "Directory '$DB_PATH' not exists. Please specify
       correct path to data files using -f <absolute path>"
  exit 1
fi

if [ "$DB_SCHEME" != "OPTIMIZED" -a "$DB_SCHEME" != "ORIG" ]; then
  usage "$DB_SCHEME. Please specifey correct database scheme [OPTIMIZED|ORIG]"
  exit 1
fi

if [ ${GENERATE_DATAFILE} -eq 1 ]; then
	#
	# Generate data files.
	#
	dbt2 datagen -d "${DB_PATH}" -w "${WAREHOUSES}" --mysql || exit 1
fi

echo ""
echo "Loading of DBT2 dataset located in $DB_PATH to database $DB_NAME."
echo ""
echo "DB_SCHEME:      $DB_SCHEME"

if [ -f "$DB_NAME" ]; then
	command_exec "rm \"$DB_NAME\" "
fi

# Create tables
echo ""
create_tables

# Load tables
echo ""
load_tables


