-- Turbine, a Servlet Framework for building Dynamic Websites
--
-- For licensing and use, please see the LICENSE.txt in this directory.
--
-- This is a schema for IBM DB2 for
-- the concept of Users/Roles/Permissions
--
-- Please see the Html documentation for more information
-- on this SQL. Equivalent contributions of this for other
-- databases are welcome.
--
-- Commented Sequence Creation / Added ID_TABLE and JOBENTRY Tables
-- 
-- @author Jon S. Stevens
-- @author Brett McLaughlin
-- @author Martin Poeschl

DROP TABLE PERMISSION
;
CREATE TABLE PERMISSION (
  PERMISSIONID  INT            NOT NULL,
  PERMISSION    VARCHAR(99)   NOT NULL )
;
ALTER TABLE PERMISSION
 ADD CONSTRAINT PERMISSION_PK PRIMARY KEY (PERMISSIONID)
;
CREATE UNIQUE INDEX PERMISSION_NAME ON PERMISSION(PERMISSION)
;
DROP TABLE USERROLE
;
CREATE TABLE USERROLE  (
    ROLEID      INT     	   NOT NULL,
    ROLENAME    VARCHAR(99)     NOT NULL
)
;
ALTER TABLE USERROLE
 ADD CONSTRAINT ROLE_PK PRIMARY KEY (ROLEID)
;
CREATE UNIQUE INDEX ROLE_NAME ON USERROLE(ROLENAME)
;
DROP TABLE ROLEPERMISSION
;
CREATE TABLE ROLEPERMISSION  (
    ROLEID       INT           NOT NULL,
    PERMISSIONID INT           NOT NULL
)
;
ALTER TABLE ROLEPERMISSION
  ADD CONSTRAINT ROLEPERMISSION_PK PRIMARY KEY (ROLEID,PERMISSIONID)
;
DROP TABLE VISITORROLE
;
CREATE TABLE VISITORROLE  (
    VISITORID   INT         NOT NULL,
    ROLEID      INT         NOT NULL
)
;
ALTER TABLE VISITORROLE
 ADD CONSTRAINT VISITORROLE_PK PRIMARY KEY (VISITORID, ROLEID)
;
DROP TABLE JOBENTRY 
;
CREATE TABLE JOBENTRY  (
    OID	          INT 		  NOT NULL,
    MINUTE        INT  default -1 NOT NULL,
    HOUR          INT  default -1 NOT NULL,
    WEEKDAY       INT  default -1 NOT NULL,
    DAY_OF_MONTH  INT  default -1 NOT NULL,
    TASK          VARCHAR(99) NOT NULL,
    EMAIL         VARCHAR(99)
)
;
ALTER TABLE JOBENTRY
 ADD CONSTRAINT OID_PK PRIMARY KEY (OID)
;
CREATE UNIQUE INDEX JOBENTRY_NAME ON JOBENTRY(TASK)
;
