
CREATE OR REPLACE PACKAGE mysql AS
last_insert_id NUMBER(11);
END;
/

CREATE TABLE MYDAS (
 ID number(8)  NOT NULL 
,dsn varchar2(60) NOT NULL
,password varchar2(32) NOT NULL
,created date default TO_DATE('01-JAN-0001-BC','DD-MON-YYYY-BC') NOT NULL
,updated date default TO_DATE('01-JAN-0001-BC','DD-MON-YYYY-BC') NOT NULL
,email varchar2(255) NOT NULL
,constraint pk_MYDAS PRIMARY KEY  (ID) 
) ; 

create index MYDAS_email on MYDAS (email) ; 
create unique index MYDAS_dsn on MYDAS (dsn) ; 

CREATE SEQUENCE MYDAS_seq
increment by 1
start with 1
nocycle;

CREATE OR REPLACE TRIGGER MYDAS_tri 
  BEFORE INSERT ON MYDAS FOR EACH ROW
BEGIN
  SELECT MYDAS_seq.NEXTVAL INTO :new.ID FROM DUAL;
   mysql.last_insert_id := :new.id;
END;
/



CREATE TABLE ORCL_SESSION (
 ID number(20)  NOT NULL 
,firstsession_ID number(20)  default '0' NOT NULL
,starttime date default TO_DATE('01-JAN-0001-BC','DD-MON-YYYY-BC') NOT NULL
,endtime date default TO_DATE('01-JAN-0001-BC','DD-MON-YYYY-BC') NOT NULL
,pages number(8)  default '0' NOT NULL
,startpage varchar2(255) NOT NULL
,endpage varchar2(255) default '' 
,CONSTRAINT PK_session PRIMARY KEY(ID)
) ; 
create index SESSION_firstsession on ORCL_SESSION (firstsession_ID) ; 
create index SESSION_starttime on ORCL_SESSION (starttime) ; 
create index SESSION_endtime on ORCL_SESSION (endtime) ; 
create index SESSION_startpage on ORCL_SESSION (startpage) ; 
create index SESSION_endpage on ORCL_SESSION (endpage) ; 


CREATE SEQUENCE SESSION_seq
increment by 1
start with 1
nocycle;

CREATE OR REPLACE TRIGGER SESSION_tri 
  BEFORE INSERT ON ORCL_SESSION FOR EACH ROW
BEGIN
  SELECT SESSION_seq.NEXTVAL INTO :new.ID FROM DUAL;
   mysql.last_insert_id := :new.id;
END;
/



CREATE TABLE USERDATATYPE (
 ID number  NOT NULL 
,name varchar2(32) NOT NULL
,description clob NOT NULL
,CONSTRAINT PK_USERDATATYPE PRIMARY KEY(ID)
) ; 


CREATE SEQUENCE USERDATATYPE_seq
increment by 1
start with 1
nocycle;

CREATE OR REPLACE TRIGGER USERDATATYPE_tri 
  BEFORE INSERT ON USERDATATYPE FOR EACH ROW
BEGIN
  SELECT USERDATATYPE_seq.NEXTVAL INTO :new.ID FROM DUAL;
   mysql.last_insert_id := :new.id;
END;
/


CREATE TABLE USERDATA (
 session_ID number(20)  default '0' NOT NULL
,userdatatype_ID number  default '0' NOT NULL
,value clob 
,updated date default SYSDATE NOT NULL
,constraint PK_USERDATA PRIMARY KEY  (session_ID,userdatatype_ID)
,CONSTRAINT fk_userdata_session FOREIGN KEY (session_id) REFERENCES orcl_session(id)
,CONSTRAINT fk_userdata_userdatatype FOREIGN KEY (userdatatype_id) REFERENCES userdatatype(id)
) ; 






