-- 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 Oracle 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

-- run this in the SID you want to use.

DROP TABLE PERMISSION
/
CREATE TABLE PERMISSION (
  PERMISSIONID  INT            NOT NULL,
  PERMISSION    VARCHAR2(99)   NOT NULL )
/
ALTER TABLE PERMISSION
 ADD CONSTRAINT PERMISSION_PK PRIMARY KEY (PERMISSIONID)
USING INDEX
/
CREATE UNIQUE INDEX PERMISSION_NAME ON PERMISSION(PERMISSION)
/
DROP TABLE USERROLE
/
CREATE TABLE USERROLE  (
    ROLEID      INT     	   NOT NULL,
    ROLENAME    VARCHAR2(99)     NOT NULL
)
/
ALTER TABLE USERROLE
 ADD CONSTRAINT ROLE_PK PRIMARY KEY (ROLEID)
USING INDEX
/
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)
USING INDEX
/
DROP TABLE VISITOR
/
CREATE TABLE VISITOR  (
    VISITORID   INT          NOT NULL,
    LOGINID     VARCHAR2(32) NOT NULL,
    PASSWORD_VALUE    VARCHAR2(32) NOT NULL,
    FIRST_NAME  VARCHAR2(99) NOT NULL,
    LAST_NAME   VARCHAR2(99) NOT NULL,
    ADDRESS1    VARCHAR2(32),
    ADDRESS2    VARCHAR2(32),
    CITY        VARCHAR2(32),
    STATE       VARCHAR2(32),
    POSTALCODE  VARCHAR2(32),
    COUNTRY     VARCHAR2(32),
    CITIZENSHIP VARCHAR2(32),
    PHONE       VARCHAR2(32),
    ALTPHONE    VARCHAR2(32),
    FAX         VARCHAR2(32),
    CELL        VARCHAR2(32),
    PAGER       VARCHAR2(32),
    EMAIL       VARCHAR2(99),
    MODIFIED    DATE,
    CREATED     DATE,
    LASTLOGIN   DATE,
    OBJECTDATA  LONG RAW
)
/
alter table Visitor add CONFIRM_VALUE varchar2(99)
/
ALTER TABLE VISITOR
 ADD CONSTRAINT VISITOR_PK PRIMARY KEY (VISITORID)
USING INDEX
/
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)
USING INDEX
/
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          VARCHAR2(99) NOT NULL,
    EMAIL         VARCHAR2(99)
)
/
ALTER TABLE JOBENTRY
 ADD CONSTRAINT OID_PK PRIMARY KEY (OID)
USING INDEX
/
CREATE UNIQUE INDEX JOBENTRY_NAME ON JOBENTRY(TASK)
/
