/*

Name:		Task_demo

Date:		April 13 2005

Purpose:	Create and populate objects for silver bullet test. 

*/

drop table users;
create table users(
userid		number,
uname	varchar2(30) not null,
upwd	varchar2(15) not null,
cdate	date not null);

alter table users 
add constraint pk_users
primary key(userid);

drop sequence userseq;
create sequence userseq;

create or replace trigger trg_bi_users
before insert on users
for each row
begin
	select userseq.nextval into :new.userid from dual;
	if :new.cdate is null then
		:new.cdate := sysdate;
	end if;
end;
/

show errors trigger trg_bi_users;

drop table tasks;
create table tasks(
tk_id		number,
tk_name		varchar2(30),
tk_status	number,
tk_activity	varchar2(20),
tk_wfid		number,
tk_userid	number);

alter table tasks 
add constraint pk_tasks
primary key(tk_id);

drop sequence taskseq;
create sequence taskseq;

create or replace trigger trg_bi_tasks
before insert on tasks
for each row
begin
	select taskseq.nextval,'Task '||taskseq.nextval into :new.tk_id,:new.tk_name from dual;
end;
/

show errors trigger trg_bi_tasks;

create index idx_tasks_status on tasks(tk_status);
create index idx_tasks_wfid on tasks(tk_wfid);
create index idx_tasks_activity on tasks(tk_activity);

drop table workflow;
create table workflow(
wf_id		number,
wf_docid	number not null,
wf_status	number not null);

drop sequence wfseq;
create sequence wfseq;

alter table workflow
add constraint pk_workflow
primary key(wf_id);

create or replace trigger trg_bi_workflow
before insert on workflow
for each row
begin
	select wfseq.nextval into :new.wf_id from dual;
end;
/

show errors trigger trg_bi_workflow;

create index idx_workflow_doc_id on workflow(wf_docid);
create index idx_workflow_status on workflow(wf_status);

drop table docs;
create table docs(
docid		number,
doc_transno	number,
doc_line#	number,
doc_extref	varchar2(255));

drop sequence docseq;
create sequence docseq;

alter table docs
add constraint pk_docs
primary key(docid);

create or replace trigger trg_bi_docs
before insert on docs
for each row
begin
	select docseq.nextval into :new.docid from dual;
end;
/

show errors trigger trg_bi_docs;

Create index idx_docs_transacts on docs(doc_transno,doc_line#);
create index idx_docs_extref on docs(doc_extref);

drop table trans;
create table trans(
transid			number,
transno			varchar2(12) not null,
line#			number not null,
company			varchar2(2) not null,
treatment_code	number not null,
transdate		date);

drop sequence transseq;
create sequence transseq;

alter table trans
add constraint pk_trans
primary key(transid);

create or replace trigger trg_bi_trans
before insert on trans
for each row
begin
	select transseq.nextval into :new.transid from dual;
	if :new.transdate is null then
		:new.transdate := sysdate;
	end if;
end;
/

show errors trigger trg_bi_trans;

create index idx_trans_transacts on trans(transno,line#);
create index idx_trans_treat on trans(treatment_code);
create index idx_trans_extref on trans(extref
/* 
pl/sql to populate tables 
users table - 250 users
*/

begin
	for i in 1..250 loop
		insert into users(uname,upwd)
		select 'User '||i,'Password'||i from dual;
	end loop;
end;
/

commit;

/*
Transactions
same transaction no may have upto 5 lines. 
each transaction line item may have a different treatment code
100,000 transactions circa 300k records
*/
declare
	l_lines	number;
begin
	for i in 1..100000 loop
	l_lines := round(dbms_random.value(1,5),0);
		for j in 1..l_lines loop
			insert into trans(transno,line#,company,treatment_code)
			select i,j,'C1',round(dbms_random.value(1,16),0) from dual;
		end loop;
	end loop;
end;
/

commit;

/* 

Transactions documents
ext ref is in format 'C1;TREATCODE;TRANSNO;LINENO'
only for some treatment codes

*/

insert into docs (doc_transno,doc_line#,doc_extref)
select transno,line#,'C1;'||treatment_code||';'||transno||';'||line#
from trans
where treatment_code in(3,5,7,9);

commit;

/* 
workflow status
only for docs with a status code of 3,5,7
status could be 2,4,8,16 (ie bitmap). 
*/
 
insert into workflow (wf_docid,wf_status)
select docid,power(2,round(dbms_random.value(1,4),0))
from docs;

commit;

/*
Tasks:
Again bitmap status field
Activity one of 'APPROVE','ORDER','QUOTE'
Task name generated by trigger. 

*/
insert into tasks(tk_status,tk_activity,tk_wfid)
select 
	power(2,round(dbms_random.value(1,4),0)),
	decode(round(dbms_random.value(1,4),0),1,'QUOTE',2,'APPROVE',3,'ORDER',4,'COMPLETE'),
	wf_id
from workflow;

commit;

/* 

Now create the single table used in the simple demo

*/

drop table t1;
create table t1 (c1 number, c2 varchar2(128));
insert into t1 select object_id, object_name from all_objects;
insert into t1 select c1,c2 from t1;
insert into t1 select c1,c2 from t1;
insert into t1 select c1,c2 from t1;
insert into t1 select c1,c2 from t1;


-- skew the data
update t1 set c1 = 1
where c1 > 40000;
-- Create an index
-- CREATE INDEX t1_c1_idx on t1(c1);
COMMIT;
/*

Make sure we have stats on the new objects */

exec dbms_stats.gather_schema_stats(user,cascade=>true);