-- ----------
-- tpcw_schema1_pgsql.sql
--
--  PostgreSQL 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 at the cost of violating
--    the specification.
-- ----------

-- ----------
-- COUNTRY
-- ----------
create table tpcw_country (
	co_id				integer,
	co_name				varchar(50),
	co_exchange			numeric(12,6),
	co_currency			varchar(18)
);

-- ----------
-- 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)
);

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

-- ----------
-- 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)
);

-- ----------
-- CUSTOMER
-- ----------
create table tpcw_customer (
	c_id				bigint,
	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)
);

-- ----------
-- ORDERS
-- ----------
create table tpcw_orders (
	o_id				bigint,
	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)
);

-- ----------
-- 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)
);

-- ----------
-- 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
);


-- ----------
-- C_ID_SEQ
-- ----------
create sequence tpcw_c_id_seq;


-- ----------
-- ADDR_ID_SEQ
-- ----------
create sequence tpcw_addr_id_seq;


-- ----------
-- O_ID_SEQ
-- ----------
create sequence tpcw_o_id_seq;


-- ----------
-- SESSION
-- ----------
set session authorization apache;
create table tpcw_session (
	id					text,
	ts					bigint,
	data				text,

	primary key (id)
);
set session authorization default;


