-- 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 VISITOR
;
CREATE TABLE VISITOR  (
    VISITORID   INT          NOT NULL,
    LOGINID     VARCHAR(32) NOT NULL,
    PASSWORD_VALUE    VARCHAR(32) NOT NULL,
    FIRST_NAME  VARCHAR(99) NOT NULL,
    LAST_NAME   VARCHAR(99) NOT NULL,
    ADDRESS1    VARCHAR(32),
    ADDRESS2    VARCHAR(32),
    CITY        VARCHAR(32),
    STATE       VARCHAR(32),
    POSTALCODE  VARCHAR(32),
    COUNTRY     VARCHAR(32),
    CITIZENSHIP VARCHAR(32),
    PHONE       VARCHAR(32),
    ALTPHONE    VARCHAR(32),
    FAX         VARCHAR(32),
    CELL        VARCHAR(32),
    PAGER       VARCHAR(32),
    EMAIL       VARCHAR(99),
    MODIFIED    DATE,
    CREATED     DATE,
    LASTLOGIN   DATE,
    OBJECTDATA  VARCHAR(255) ) --DBCLOB)
;
alter table Visitor add CONFIRM_VALUE VARCHAR(99)
;
ALTER TABLE VISITOR
  ADD CONSTRAINT VISITOR_PK PRIMARY KEY (VISITORID)
;
CREATE UNIQUE INDEX VISITOR_LOGINID ON VISITOR(LOGINID)
;
INSERT INTO VISITOR
       (VISITORID,
        LOGINID,
        PASSWORD_VALUE,
        FIRST_NAME,
        LAST_NAME)
VALUES (1,
        'turbine',
        'turbine',
        'system',
        'adminstrator' )
;
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)
;
