-- ----------
-- tpcw_schema1_mysql.sql
--
--	MySQL specific database object definitions for the
--	PHP implementation of the TPC-W ECommerce benchmark v1.8.
--
-- Notes:
--
--	* Before changing any of the table definitions, be fully familiar
--	  with the restrictions of the TPC-W specification version 1.8.
--
--	* The specification explicitly mandates ACID properties for all
--	  tables. The defined foreign keys are not currently enforced by
--	  the PHP application code and must therefore remain functional.
--	  Due to the relatively low update activity, foreign keys are not
--	  considered a performance problem.
--
--	* The chosen data types are compatible with the requirements of
--	  item 1.4 of the specification. The use of integer ID's instead of
--	  bigint is known to result in a speedup of the bestseller and
--	  admin-confirm dialogs up to 10% at the cost of violating the
--	  specification.
-- ----------

-- ----------
-- COUNTRY
-- ----------
drop table if exists tpcw_country;
create table tpcw_country (
	co_id				integer,
	co_name				varchar(50),
	co_exchange			numeric(12,6),
	co_currency			varchar(18),
	
	primary key (co_id)
) type=InnoDB;

-- ----------
-- AUTHOR
-- ----------
drop table if exists tpcw_author;
create table tpcw_author (
	a_id				bigint,
	a_fname				varchar(20),
	a_lname				varchar(20),
	a_mname				varchar(20),
	a_dob				date,
	a_bio				varchar(500),

	primary key (a_id)
) type=InnoDB;

-- ----------
-- ADDRESS
-- ----------
drop table if exists tpcw_address;
create table tpcw_address (
	addr_id				bigint auto_increment,
	addr_street1		varchar(40),
	addr_street2		varchar(40),
	addr_city			varchar(30),
	addr_state			varchar(20),
	addr_zip			varchar(10),
	addr_co_id			integer,

	primary key (addr_id),
	key (addr_co_id),
	key (addr_street1), 
	foreign key (addr_co_id) references tpcw_country (co_id)
) type=InnoDB;

-- ----------
-- ITEM
-- ----------
drop table if exists tpcw_item;
create table tpcw_item (
	i_id				bigint,
	i_title				varchar(60),
	i_a_id				bigint,
	i_pub_date			date,
	i_publisher			varchar(60),
	i_subject			varchar(60),
	i_desc				varchar(500),
	i_related1			bigint,
	i_related2			bigint,
	i_related3			bigint,
	i_related4			bigint,
	i_related5			bigint,
	i_thumbnail			text,
	i_image				text,
	i_srp				numeric(17,2),
	i_cost				numeric(17,2),
	i_avail				date,
	i_stock				integer,
	i_isbn				char(13),
	i_page				integer,
	i_backing			varchar(15),
	i_dimension			varchar(25),

	primary key (i_id),
	key (i_subject),
	key (i_a_id),
	foreign key (i_a_id) references tpcw_author (a_id)
) type=InnoDB;

-- ----------
-- CUSTOMER
-- ----------
drop table if exists tpcw_customer;
create table tpcw_customer (
	c_id				bigint auto_increment,
	c_uname				varchar(20),
	c_passwd			varchar(20),
	c_fname				varchar(15),
	c_lname				varchar(15),
	c_addr_id			bigint,
	c_phone				varchar(16),
	c_email				varchar(50),
	c_since				date,
	c_last_visit		date,
	c_login				timestamp,
	c_expiration		timestamp,
	c_discount			numeric(5,2),
	c_balance			numeric(17,2),
	c_ytd_payment		numeric(17,2),
	c_birthdate			date,
	c_data				varchar(500),

	primary key (c_id),
	key (c_uname),
	key (c_addr_id),
	foreign key (c_addr_id) references tpcw_address (addr_id)
) type=InnoDB;

-- ----------
-- ORDERS
-- ----------
drop table if exists tpcw_orders;
create table tpcw_orders (
	o_id				bigint auto_increment,
	o_c_id				bigint,
	o_date				timestamp,
	o_sub_total			numeric(17,2),
	o_tax				numeric(17,2),
	o_total				numeric(17,2),
	o_ship_type			varchar(10),
	o_ship_date			timestamp,
	o_bill_addr_id		bigint,
	o_ship_addr_id		bigint,
	o_status			varchar(15),

	primary key (o_id),
	key (o_date),
	key (o_c_id),
	key (o_bill_addr_id),
	key (o_ship_addr_id),
	foreign key (o_c_id) references tpcw_customer (c_id),
	foreign key (o_bill_addr_id) references tpcw_address (addr_id),
	foreign key (o_ship_addr_id) references tpcw_address (addr_id)
) type=InnoDB;

-- ----------
-- ORDER_LINE
-- ----------
drop table if exists tpcw_order_line;
create table tpcw_order_line (
	ol_id				integer,
	ol_o_id				bigint,
	ol_i_id				bigint,
	ol_qty				integer,
	ol_discount			numeric(5,2),
	ol_comments			varchar(100),

	primary key (ol_id, ol_o_id),
	key (ol_o_id),
	key (ol_i_id),
	foreign key (ol_o_id) references tpcw_orders (o_id),
	foreign key (ol_i_id) references tpcw_item (i_id)
) type=InnoDB;

-- ----------
-- CC_XACTS
-- ----------
drop table if exists tpcw_cc_xacts;
create table tpcw_cc_xacts (
	cx_o_id				bigint,
	cx_type				varchar(10),
	cx_cc_num			numeric(16),
	cx_cc_name			varchar(31),
	cx_expiry			date,
	cx_auth_id			char(15),
	cx_xact_amt			numeric(17,2),
	cx_xact_date		timestamp,
	cx_co_id			integer,

	primary key (cx_o_id),
	key (cx_o_id),
	key (cx_co_id),
	foreign key (cx_o_id) references tpcw_orders (o_id),
	foreign key (cx_co_id) references tpcw_country (co_id)
) type=InnoDB;


-- ----------
-- SESSION
-- ----------
drop table if exists tpcw_session;
create table tpcw_session (
	id					varchar(80),
	ts					bigint,
	data				text,

	primary key (id)
) type=InnoDB;
