/*#ifndef MKASADEMO_SQL_HEADER
**#define MKASADEMO_SQL_HEADER
**static char SCCS_ID[] = @(#) LIV  2001/08/01 11:26:44   mkasademo.sql \main\callpilot2.0\1 ~
** LIV  2001/08/01 11:26:44   mkasademo.sql \main\callpilot2.0\1 ~
** [liv/Database/nbdb0078]
**
**#endif
*/
//  Usage:	isql read makewsdb.sql
// 
//  This command file reloads a database that was unloaded using "unload".
// 
// 

SET OPTION Statistics = 3;
SET OPTION Date_order = 'YMD';


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Create userids and grant user permissions
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

GRANT CONNECT TO "DBA" IDENTIFIED BY "SQL";
GRANT RESOURCE, DBA, SCHEDULE TO "DBA";
commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Create tables
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE TABLE "DBA"."sales_order"
(
	"id"		integer NOT NULL default autoincrement,
	"cust_id"		integer NOT NULL,
	"order_date"		date NOT NULL,
	"fin_code_id"		char(2) NULL,
	"region"		char(7) NULL,
	"sales_rep"		integer NOT NULL,
	PRIMARY KEY ("id"),
);
CREATE TABLE "DBA"."sales_order_items"
(
	"id"		integer NOT NULL,
	"line_id"		smallint NOT NULL,
	"prod_id"		integer NOT NULL,
	"quantity"		integer NOT NULL,
	"ship_date"		date NOT NULL,
	PRIMARY KEY ("id", "line_id"),
);
CREATE TABLE "DBA"."contact"
(
	"id"		integer NOT NULL,
	"last_name"		char(15) NOT NULL,
	"first_name"		char(15) NOT NULL,
	"title"		char(2) NOT NULL,
	"street"		char(30) NOT NULL,
	"city"		char(20) NOT NULL,
	"state"		char(2) NOT NULL,
	"zip"		char(5) NOT NULL,
	"phone"		char(10) NULL,
	"fax"		char(10) NULL,
	PRIMARY KEY ("id"),
);
CREATE TABLE "DBA"."customer"
(
	"id"		integer NOT NULL default autoincrement,
	"fname"		char(15) NOT NULL,
	"lname"		char(20) NOT NULL,
	"address"		char(35) NOT NULL,
	"city"		char(20) NOT NULL,
	"state"		char(2) NOT NULL,
	"zip"		char(10) NOT NULL,
	"phone"		char(12) NOT NULL,
	"company_name"		char(35) NULL,
	PRIMARY KEY ("id"),
);
CREATE TABLE "DBA"."fin_code"
(
	"code"		char(2) NOT NULL,
	"type"		char(10) NOT NULL,
	"description"		char(50) NULL,
	PRIMARY KEY ("code"),
);
CREATE TABLE "DBA"."fin_data"
(
	"year"		char(4) NOT NULL,
	"quarter"		char(2) NOT NULL,
	"code"		char(2) NOT NULL,
	"amount"		numeric(9,0) NULL,
	PRIMARY KEY ("year", "quarter", "code"),
);
CREATE TABLE "DBA"."product"
(
	"id"		integer NOT NULL,
	"name"		char(15) NOT NULL,
	"description"		char(30) NOT NULL,
	"size"		char(18) NOT NULL,
	"color"		char(6) NOT NULL,
	"quantity"		integer NOT NULL,
	"unit_price"		numeric(15,2) NOT NULL,
	PRIMARY KEY ("id"),
);
CREATE TABLE "DBA"."department"
(
	"dept_id"		integer NOT NULL,
	"dept_name"		char(40) NOT NULL,
	"dept_head_id"		integer NULL,
	PRIMARY KEY ("dept_id"),
);
CREATE TABLE "DBA"."employee"
(
	"emp_id"		integer NOT NULL,
	"manager_id"		integer NULL,
	"emp_fname"		char(20) NOT NULL,
	"emp_lname"		char(20) NOT NULL,
	"dept_id"		integer NOT NULL,
	"street"		char(40) NOT NULL,
	"city"		char(20) NOT NULL,
	"state"		char(4) NOT NULL,
	"zip_code"		char(9) NOT NULL,
	"phone"		char(10) NULL,
	"status"		char(1) NULL,
	"ss_number"		char(11) NOT NULL,
	"salary"		numeric(20,3) NOT NULL,
	"start_date"		date NOT NULL,
	"termination_date"		date NULL,
	"birth_date"		date NULL,
	"bene_health_ins"		char(1) NULL,
	"bene_life_ins"		char(1) NULL,
	"bene_day_care"		char(1) NULL,
	"sex"		char(1) NULL,
	PRIMARY KEY ("emp_id"),
);
commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Reload data
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

INPUT INTO "DBA"."sales_order"
	FROM wdata\sales_o.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."sales_order_items"
	FROM wdata\sales_oi.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."contact"
	FROM wdata\contact.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."customer"
	FROM wdata\customer.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."fin_code"
	FROM wdata\fin_code.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."fin_data"
	FROM wdata\fin_data.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."product"
	FROM wdata\product.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."department"
	FROM wdata\dept.dat
	FORMAT ASCII
	BY ORDER;

INPUT INTO "DBA"."employee"
	FROM wdata\employee.dat
	FORMAT ASCII
	BY ORDER;

commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Add foreign keys definitions
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


ALTER TABLE "DBA"."sales_order"
	ADD FOREIGN KEY "ky_so_employee_id" ("sales_rep") REFERENCES "DBA"."employee" ("emp_id");

ALTER TABLE "DBA"."sales_order"
	ADD FOREIGN KEY "ky_so_fincode" ("fin_code_id") REFERENCES "DBA"."fin_code" ("code") on delete set null;

ALTER TABLE "DBA"."sales_order"
	ADD FOREIGN KEY "ky_so_customer" ("cust_id") REFERENCES "DBA"."customer" ("id");
CREATE INDEX "ix_sales_cust" ON "DBA"."sales_order"
(
	"cust_id" ASC
);

ALTER TABLE "DBA"."sales_order_items"
	ADD FOREIGN KEY "ky_prod_id" ("prod_id") REFERENCES "DBA"."product" ("id");

ALTER TABLE "DBA"."sales_order_items"
	ADD FOREIGN KEY "id_fk" ("id") REFERENCES "DBA"."sales_order" ("id") on delete cascade;
CREATE INDEX "ix_item_prod" ON "DBA"."sales_order_items"
(
	"prod_id" ASC
);
CREATE INDEX "ix_cust_name" ON "DBA"."customer"
(
	"lname" ASC,
	"fname" ASC
);

ALTER TABLE "DBA"."fin_data"
	ADD FOREIGN KEY "ky_code_data" ("code") REFERENCES "DBA"."fin_code" ("code") on delete cascade;
CREATE INDEX "fin_data_idx" ON "DBA"."fin_data"
(
	"code" ASC
);
CREATE INDEX "ix_prod_name" ON "DBA"."product"
(
	"name" ASC);
CREATE INDEX "ix_prod_desc" ON "DBA"."product"
(
	"description" ASC);
CREATE INDEX "ix_prod_size" ON "DBA"."product"
(
	"size" ASC);
CREATE INDEX "ix_prod_color" ON "DBA"."product"
(
	"color" ASC
);

ALTER TABLE "DBA"."department"
	ADD FOREIGN KEY "ky_dept_head" ("dept_head_id") REFERENCES "DBA"."employee" ("emp_id") on delete set null;

ALTER TABLE "DBA"."employee"
	ADD FOREIGN KEY "ky_dept_id" ("dept_id") REFERENCES "DBA"."department" ("dept_id");
commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Create views
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Set option values
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


SET OPTION Statistics =;
SET OPTION Date_order =;


// 
// SQL Option Statements for user 
// 

SET OPTION "DBA"."Wait_for_commit" = 'off';
SET OPTION "DBA"."Statistics" = '3';
SET OPTION "DBA"."Date_order" = 'YMD';


// 
// SQL Option Statements for user 
// 

SET OPTION "PUBLIC"."Blocking" = 'On';
SET OPTION "PUBLIC"."Checkpoint_time" = '60';
SET OPTION "PUBLIC"."Conversion_error" = 'On';
// SET OPTION "PUBLIC"."Timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS';
// SET OPTION "PUBLIC"."Time_format" = 'HH:NN:SS.SSS';
// SET OPTION "PUBLIC"."Date_format" = 'YYYY-MM-DD';
// SET OPTION "PUBLIC"."Date_order" = 'YMD';
SET OPTION "PUBLIC"."Isolation_level" = '0';
SET OPTION "PUBLIC"."Precision" = '30';
SET OPTION "PUBLIC"."Recovery_time" = '2';
SET OPTION "PUBLIC"."Row_counts" = 'Off';
SET OPTION "PUBLIC"."Scale" = '6';
SET OPTION "PUBLIC"."Thread_count" = '0';
SET OPTION "PUBLIC"."Wait_for_commit" = 'Off';
SET OPTION "PUBLIC"."Auto_commit" = 'Off';
SET OPTION "PUBLIC"."Auto_refetch" = 'On';
SET OPTION "PUBLIC"."Bell" = 'On';
SET OPTION "PUBLIC"."Commit_on_exit" = 'On';
SET OPTION "PUBLIC"."Echo" = 'On';
SET OPTION "PUBLIC"."Headings" = 'On';
SET OPTION "PUBLIC"."Input_format" = 'ASCII';
SET OPTION "PUBLIC"."ISQL_log" = '';
SET OPTION "PUBLIC"."NULLS" = '(NULL)';
SET OPTION "PUBLIC"."On_error" = 'Prompt';
SET OPTION "PUBLIC"."Output_format" = 'ASCII';
SET OPTION "PUBLIC"."Output_length" = '0';
SET OPTION "PUBLIC"."Screen_format" = 'Text';
SET OPTION "PUBLIC"."Statistics" = '3';
SET OPTION "PUBLIC"."Truncation_length" = '30';
SET OPTION "PUBLIC"."Command_delimiter" = ';';
commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Create procedures
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CONNECT "DBA" IDENTIFIED BY "SQL";

SET TEMPORARY OPTION Command_delimiter = ';;';

create procedure
sp_retrieve_contacts()
result(id integer,last_name char(15),first_name char(15),title char(2),street char(30),city char(20),state char(2),zip char(5),phone char(10),fax char(10))
begin
  select id,last_name,first_name,title,street,city,state,zip,phone,fax
    from contact order by contact.id asc
end;;


create procedure
sp_product_info(inout prod_id integer)
result(id integer,name char(15),description char(30),size char(18),color char(6),quantity integer,unit_price decimal(15,2))
begin
  select id,name,description,size,color,quantity,unit_price
    from product where id=prod_id
end;;


create procedure
sp_customer_list()
result(id integer,company_name char(35))
begin
  select id,company_name from customer
end;;


create procedure
sp_contacts(in action char(1),in contact_id integer,in contact_old_id integer,in contact_last_name char(15),in contact_first_name char(15),in contact_title char(2),in contact_street char(30),in contact_city char(20),in contact_state char(2),in contact_zip char(5),in contact_phone char(10),in contact_fax char(10))
begin
  case action when 'I' then
    insert into contact(id,last_name,first_name,title,street,city,state,zip,
      phone,fax) values(contact_id,contact_last_name,contact_first_name,
      contact_title,contact_street,contact_city,contact_state,contact_zip,
      contact_phone,contact_fax) when 'U' then
    update contact set contact.id=contact_id,contact.last_name=contact_last_name,
      contact.first_name=contact_first_name,contact.title=contact_title,
      contact.street=contact_street,contact.city=contact_city,contact.state=contact_state,
      contact.zip=contact_zip,contact.phone=contact_phone,contact.fax=contact_fax
      where contact.id=contact_old_id when 'D' then
    delete from contact where contact.id=contact_old_id
  end case
end;;


create procedure
sp_sales_order_items(in ord_id integer,in product integer)
result(line_id integer,prod_id integer,quantity integer,ship_date date)
begin
  select line_id,prod_id,quantity,ship_date from sales_order_items where id
    =ord_id
end;;


create procedure
sp_sales_order(in customer_id integer,in product_id integer)
result(id integer,order_date date,fin_code_id char(2),region char(7),sales_rep integer)
begin
  select s.id,s.order_date,s.fin_code_id,s.region,s.sales_rep
    from sales_order as s,sales_order_items as i where s.cust_id=customer_id and i.prod_id
    =product_id and s.id=i.id
end;;


create procedure
sp_customer_products(inout customer_id integer)
result(id integer,quantity_ordered integer)
begin
  select product.id,sum(sales_order_items.quantity) from product
    ,sales_order_items,sales_order where sales_order.cust_id=customer_id
    and sales_order.id=sales_order_items.id and sales_order_items.prod_id=product.id
    group by product.id
end;;

SET TEMPORARY OPTION Command_delimiter = ';';;

CONNECT "DBA" IDENTIFIED BY "SQL";

commit work;


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Create triggers
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CONNECT "DBA" IDENTIFIED BY "SQL";

SET TEMPORARY OPTION Command_delimiter = ';;';

create trigger
tr_manager before update of dept_head_id on department
referencing old as old_dept new as new_dept
for each row
begin
  update employee set employee.manager_id=new_dept.dept_head_id
    where employee.dept_id=old_dept.dept_id
end;;


create trigger
tr_dept_id after update of dept_id on department
referencing old as old_dept new as new_dept
for each row
begin
  update employee set employee.dept_id=new_dept.dept_id where employee.dept_id
    =old_dept.dept_id
end;;

SET TEMPORARY OPTION Command_delimiter = ';';;

CONNECT "DBA" IDENTIFIED BY "SQL";

commit work;