/*** Using ADTs in SQLJ ***/
SET ECHO ON;
/**
Consider two types, person and address, and a typed table for 
person objects, that are created in the database using the following 
SQL script.
**/

/*** Clean up ***/
DROP TABLE EMPLOYEES
/
DROP TABLE PERSONS
/
DROP TABLE projects
/
DROP TABLE participants
/
DROP TYPE PHONE_ARRAY FORCE
/
DROP TYPE PHONE_TAB FORCE
/
DROP TYPE PERSON FORCE
/
DROP TYPE ADDRESS FORCE
/
DROP TYPE moduletbl_t FORCE
/
DROP TYPE module_t FORCE
/
DROP TYPE participant_t FORCE
/

/*** Create an address ADT ***/
CREATE TYPE address AS OBJECT
( 
  street        VARCHAR(60),
  city          VARCHAR(30),
  state         CHAR(2),
  zip_code      CHAR(5)
)
/
show errors 

/*** Create a person ADT containing an embedded Address ADT ***/
CREATE TYPE person AS OBJECT
( 
  name    VARCHAR(30),
  ssn     NUMBER,
  addr    address
)
/
show errors 

/*** Create a typed table for person objects ***/
CREATE TABLE persons OF person
/
show errors 
CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30)
/
show errors 
CREATE TYPE participant_t AS OBJECT (
  empno   NUMBER(4),
  ename   VARCHAR2(20),
  job     VARCHAR2(12),
  mgr     NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  deptno   NUMBER(2)) 
/
show errors 
CREATE TYPE module_t  AS OBJECT (
  module_id  NUMBER(4),
  module_name VARCHAR2(20), 
  module_owner REF participant_t , 
  module_start_date DATE, 
  module_duration NUMBER )
/
show errors 
create TYPE moduletbl_t AS TABLE OF module_t;
/
show errors 

/*** Create a relational table with two columns that are REFs 
     to person objects, as well as a column which is an Address ADT. ***/

CREATE TABLE  employees
( empnumber            INTEGER PRIMARY KEY,
  person_data     REF  person,
  manager         REF  person,
  office_addr          address,
  salary               NUMBER,
  phone_nums           phone_array
)
/
CREATE TABLE projects (
  id NUMBER(4),
  name VARCHAR(30),
  owner REF participant_t,
  start_date DATE,
  duration NUMBER(3),
  modules  moduletbl_t  ) NESTED TABLE modules STORE AS modules_tab ;

CREATE TABLE participants  OF participant_t ;

/*** Now let's put in some sample data
     Insert 2 objects into the persons typed table ***/

INSERT INTO persons VALUES (
            person('Wolfgang Amadeus Mozart', 123456,
	      address('Am Berg 100', 'Salzburg', 'AU','10424')))
/
INSERT INTO persons VALUES (
	    person('Ludwig van Beethoven', 234567,
	    address('Rheinallee', 'Bonn', 'DE', '69234')))
/

/** Put a row in the employees table **/

INSERT INTO employees (empnumber, office_addr, salary, phone_nums) VALUES 
            (1001,
             address('500 Oracle Parkway', 'Redwood City', 'CA', '94065'),
             50000,
             phone_array('(408) 555-1212', '(650) 555-9999'));
/

/** Set the manager and person REFs for the employee **/

UPDATE employees 
  SET manager =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
/

UPDATE employees 
  SET person_data =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
/

/* now we insert data into the PARTICIPANTS and PROJECTS tables */
INSERT INTO participants VALUES (
participant_T(7369,'ALAN SMITH','ANALYST',7902,to_date('17-12-1980','dd-mm-yyyy'),800,20)) ;
INSERT INTO participants VALUES (
participant_t(7499,'ALLEN TOWNSEND','ANALYST',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,30));
INSERT INTO participants VALUES (
participant_t(7521,'DAVID WARD','MANAGER',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,30));
INSERT INTO participants VALUES (
participant_t(7566,'MATHEW JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,20));
INSERT INTO participants VALUES (
participant_t(7654,'JOE MARTIN','MANAGER',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,30));
INSERT INTO participants VALUES (
participant_t(7698,'PAUL JONES','Director',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,30));
INSERT INTO participants VALUES (
participant_t(7782,'WILLIAM CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,10));
INSERT INTO participants VALUES (
participant_t(7788,'SCOTT MANDELSON','ANALYST',7566,to_date('13-JUL-87','dd-mm-yy')-85,3000,20));
INSERT INTO participants VALUES (
participant_t(7839,'TOM KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,10));
INSERT INTO participants VALUES (
participant_t(7844,'MARY TURNER','SR MANAGER',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,30));
INSERT INTO participants VALUES (
participant_t(7876,'JULIE ADAMS','SR ANALYST',7788,to_date('13-JUL-87', 'dd-mm-yy')-51,1100,20));
INSERT INTO participants VALUES (
participant_t(7900,'PAMELA JAMES','SR ANALYST',7698,to_date('3-12-1981','dd-mm-yyyy'),950,30));
INSERT INTO participants VALUES (
participant_t(7902,'ANDY FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,20));
INSERT INTO participants VALUES (
participant_t(7934,'CHRIS MILLER','SR ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,10));


INSERT INTO projects VALUES ( 101, 'Emarald', null, '10-JAN-98',  300, 
       moduletbl_t( module_t ( 1011 , 'Market Analysis', null, '01-JAN-98', 100),
                     module_t ( 1012 , 'Forecast', null, '05-FEB-98',20) ,
                     module_t ( 1013 , 'Advertisement', null, '15-MAR-98', 50),
                     module_t ( 1014 , 'Preview', null, '15-MAR-98',44),
                     module_t ( 1015 , 'Release', null,'12-MAY-98',34) ) ) ;

update projects set owner=(select ref(p) from participants p where p.empno = 7839) where id=101 ;

update the ( select modules from projects a where a.id = 101 )  
set  module_owner = ( select ref(p) from participants p where p.empno = 7844) where module_id = 1011 ;
update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7934) where module_id = 1012 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7902) where module_id = 1013 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7876) where module_id = 1014 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7788) where module_id = 1015 ;

INSERT INTO projects VALUES ( 500, 'Diamond', null, '15-FEB-98', 555, 
       moduletbl_t ( module_t ( 5001 , 'Manufacturing', null, '01-MAR-98', 120),
                     module_t ( 5002 , 'Production', null, '01-APR-98',100),
                     module_t ( 5003 , 'Materials', null, '01-MAY-98',200) ,
                     module_t ( 5004 , 'Marketing', null, '01-JUN-98',10) ,
                     module_t ( 5005 , 'Materials', null, '15-FEB-99',50),
                     module_t ( 5006 , 'Finance ', null, '16-FEB-99',12),
                     module_t ( 5007 , 'Budgets', null, '10-MAR-99',45))) ;

update projects set owner=(select ref(p) from participants p where p.empno = 7698) where id=500 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7369) where module_id = 5001 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7499) where module_id = 5002 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7521) where module_id = 5004 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7566) where module_id = 5005 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7654) where module_id = 5007 ;

COMMIT
/
QUIT
