--
-- ### Small logistic application sample
-- 

-- create required base tables
create table supplier ( primary snr int not null, sname string(30), prio int);
create table material ( primary mnr int not null, mname string(30), snr int);
create table orders (primary onr int not null, mname string(30), orderdate datetime, cnr int);
create table customer ( primary cnr int not null, cname string(30));
create table ids ( primary id string(30) not null, idval int);

-- add indexes
create index material_idx2 on material ( mname );
create index material_idx1 on material ( snr );
create index customer_idx1 on customer ( cname );
create index orders_idx1 on orders ( mname );

-- add foreign key constraints
alter table material add foreign key mat_fk ( snr ) references supplier ( snr );
alter table orders add foreign key ord_fk ( cnr ) references customer ( cnr );

-- insert base data
insert into supplier values ( 1, 'Time&Material', 3);
insert into supplier values ( 2, 'Cego AG', 5);
insert into supplier values ( 3, 'Moon Factory', 9);
insert into supplier values ( 4, 'McDagobert', 3);
insert into supplier values ( 11, 'HAL', 2);
insert into supplier values ( 12, 'Abraxas', 1);
insert into supplier values ( 13, 'ThunderStorm', 9);

insert into material values ( 1, 'screw', 11);
insert into material values ( 2, 'nail', 11);
insert into material values ( 3, 'hammer', 1);
insert into material values ( 4, 'saw', 2);
insert into material values ( 5, 'nail', 2);
insert into material values ( 6, 'knife', 1);
insert into material values ( 7, 'screw', 2);
insert into material values ( 8, 'saw', 11);
insert into material values ( 9, 'hammer', 11);

insert into customer values ( 23, 'John Smith');
insert into customer values ( 24, 'John Smith');
insert into customer values ( 25, 'John Smith');
insert into customer values ( 26, 'John Smith');


insert into orders values ( 4711, 'screw', sysdate, 23);
insert into orders values ( 4712, 'nail', sysdate, 23);
insert into orders values ( 4713, 'saw', sysdate, 23);
insert into orders values ( 4714, 'hammer', sysdate, 23 );
-- insert into orders values ( 4715, 'spoon', sysdate , 23 );
-- insert into orders values ( 4716, 'fork', sysdate , 23 );
-- insert into orders values ( 4717, 'plate', sysdate , 23 );
-- insert into orders values ( 4722, 'car', date('%Y-%m-%d %H:%M:%S %Z', '2007-07-07 13:33:12 CET'), 23);

insert into ids values ('ONR', 10000);
insert into ids values ('SNR', 10000);
insert into ids values ('MNR', 10000);
insert into ids values ('CNR', 10000);


-- view sample
create view matview as select sname as supname, mname as matname from material mat, supplier sup where sup.snr = mat.snr;


-- proc samples

@
create procedure nextId ( id in string(30) ) return int 
begin 

   var actId int;
   var res bool;

   cursor actIdCur as select idval from ids where id = :id;

   :actId = 0;

   if fetch actIdCur into ( :actId  ) = true
   then      
      close actIdCur;	
      :actId = :actId + 1;
      update ids set idval = :actId where id = :id;       	      
   end;

   return :actId;

end;
@

@
create procedure nextId2 ( id in string(30) ) return int 
begin 

   var idval int;

   update ids set idval = idval + 1 where id = :id return :idval = idval;
        
   return :idval;

end;
@

@
create procedure new_order (mname in string(30), cnr in int ) return int 
begin 
 
   var onr int;

   :onr = nextId2('ONR');
   insert into orders values ( :onr, :mname, sysdate, :cnr );

   return :onr;

end;
@


select * from matview;
select supname from matview mv;
select matname from matview mv;

select mnr from material where snr = 1;

:onr = call new_order('spoon', 23);

select * from orders;

:onr = call new_order('spoon', 24);
print :onr;
select * from orders;

:onr = call new_order('screw', 24);
:onr = call new_order('hammer', 26);

select count(*) from orders;


select * from material;
select * from orders;
select * from supplier;

list table;
list view;
list index;
list procedure;

select * from matview;

select sname from supplier sup where snr = ( select mat.snr from material mat);

select sname from supplier sup where snr in ( select mat.snr from material mat) ;

select sname from supplier sup where snr not in ( select mat.snr from material mat) ;

select mname from material mat, orders ord where mat.mname = ord.mname;

select sname from supplier sup where not exists
  ( select * from orders ord where not exists 
       ( select * from material mat where mat.snr = sup.snr and mat.mname = ord.mname ));





select 1, mname, ( select sname from supplier sup where sup.snr = mat.snr ) from material mat;

-- select sum( ( select prio from supplier sup where sup.snr = mat.snr ) )  from material mat;  
select sum ( ( select prio from supplier sup where sup.snr = mat.snr ) )  from material mat;  

desc view matview;
desc table orders;
desc index material_idx1;

desc table orders;
tableinfo orders;


drop view matview;

drop table material;
drop table orders;

drop table supplier;
drop table customer;
drop table ids;


drop procedure new_order;
drop procedure nextId;
drop procedure nextId2;


-- delete from supplier where snr = 12;
-- select * from material;
-- select * from supplier;
-- insert into material values ( 3, 'hammer', 1);
-- insert into material values ( 4, 'saw', 1);
-- insert into material values ( 8, 'saw', 2);

-- insert into orders values ( 4712, 'nail' , 24 );
-- insert into orders values ( 4713, 'saw' , 25 );
