-----------------------------------------------------------------------------
-- Copyright 2004 The Apache Software Foundation
-- 
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
-- 
-- http://www.apache.org/licenses/LICENSE-2.0
-- 
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- TURBINE_USER
-----------------------------------------------------------------------------
DROP TABLE TURBINE_USER CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_USER_SEQ;

CREATE TABLE TURBINE_USER
(
    USER_ID NUMBER NOT NULL,
    LOGIN_NAME VARCHAR2 (32) NOT NULL,
    PASSWORD_VALUE VARCHAR2 (32) NOT NULL,
    FIRST_NAME VARCHAR2 (99) NOT NULL,
    LAST_NAME VARCHAR2 (99) NOT NULL,
    EMAIL VARCHAR2 (99),
    CONFIRM_VALUE VARCHAR2 (99),
    MODIFIED DATE,
    CREATED DATE,
    LAST_LOGIN DATE,
    DISABLED CHAR (1),
    OBJECTDATA LONG RAW,
    PASSWORD_CHANGED DATE,
    CONSTRAINT TURBINE_USER_U_1 UNIQUE (LOGIN_NAME)
);

ALTER TABLE TURBINE_USER
    ADD CONSTRAINT TURBINE_USER_PK
PRIMARY KEY (USER_ID);


CREATE SEQUENCE TURBINE_USER_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
DROP TABLE TURBINE_ROLE CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_ROLE_SEQ;

CREATE TABLE TURBINE_ROLE
(
    ROLE_ID NUMBER NOT NULL,
    ROLE_NAME VARCHAR2 (99) NOT NULL,
    OBJECTDATA LONG RAW,
    CONSTRAINT TURBINE_ROLE_U_1 UNIQUE (ROLE_NAME)
);

ALTER TABLE TURBINE_ROLE
    ADD CONSTRAINT TURBINE_ROLE_PK
PRIMARY KEY (ROLE_ID);


CREATE SEQUENCE TURBINE_ROLE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
DROP TABLE TURBINE_GROUP CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_GROUP_SEQ;

CREATE TABLE TURBINE_GROUP
(
    GROUP_ID NUMBER NOT NULL,
    GROUP_NAME VARCHAR2 (99) NOT NULL,
    OBJECTDATA LONG RAW,
    CONSTRAINT TURBINE_GROUP_U_1 UNIQUE (GROUP_NAME)
);

ALTER TABLE TURBINE_GROUP
    ADD CONSTRAINT TURBINE_GROUP_PK
PRIMARY KEY (GROUP_ID);


CREATE SEQUENCE TURBINE_GROUP_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- TURBINE_PERMISSION
-----------------------------------------------------------------------------
DROP TABLE TURBINE_PERMISSION CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_PERMISSION_SEQ;

CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID NUMBER NOT NULL,
    PERMISSION_NAME VARCHAR2 (99) NOT NULL,
    OBJECTDATA LONG RAW,
    CONSTRAINT TURBINE_PERMISSION_U_1 UNIQUE (PERMISSION_NAME)
);

ALTER TABLE TURBINE_PERMISSION
    ADD CONSTRAINT TURBINE_PERMISSION_PK
PRIMARY KEY (PERMISSION_ID);


CREATE SEQUENCE TURBINE_PERMISSION_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
DROP TABLE TURBINE_ROLE_PERMISSION CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_ROLE_PERMISSION_SEQ;

CREATE TABLE TURBINE_ROLE_PERMISSION
(
    ROLE_ID NUMBER NOT NULL,
    PERMISSION_ID NUMBER NOT NULL
);

ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_PK
PRIMARY KEY (ROLE_ID,PERMISSION_ID);


CREATE SEQUENCE TURBINE_ROLE_PERMISSION_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- TURBINE_USER_GROUP_ROLE
-----------------------------------------------------------------------------
DROP TABLE TURBINE_USER_GROUP_ROLE CASCADE CONSTRAINTS;
DROP SEQUENCE TURBINE_USER_GROUP_ROLE_SEQ;

CREATE TABLE TURBINE_USER_GROUP_ROLE
(
    USER_ID NUMBER NOT NULL,
    GROUP_ID NUMBER NOT NULL,
    ROLE_ID NUMBER NOT NULL
);

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_PK
PRIMARY KEY (USER_ID,GROUP_ID,ROLE_ID);


CREATE SEQUENCE TURBINE_USER_GROUP_ROLE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;








ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE 
;

ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
    REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
ON DELETE CASCADE 
;



ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1 FOREIGN KEY (USER_ID)
    REFERENCES TURBINE_USER (USER_ID)
ON DELETE CASCADE 
;

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_2 FOREIGN KEY (GROUP_ID)
    REFERENCES TURBINE_GROUP (GROUP_ID)
ON DELETE CASCADE 
;

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE 
;



-----------------------------------------------------------------------------
-- JETSPEED_USER_PROFILE
-----------------------------------------------------------------------------
DROP TABLE JETSPEED_USER_PROFILE CASCADE CONSTRAINTS;
DROP SEQUENCE JETSPEED_USER_PROFILE_SEQ;

CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID NUMBER NOT NULL,
    USER_NAME VARCHAR2 (32) NOT NULL,
    MEDIA_TYPE VARCHAR2 (99),
    LANGUAGE VARCHAR2 (2),
    COUNTRY VARCHAR2 (2),
    PAGE VARCHAR2 (99),
    PROFILE LONG RAW,
    CONSTRAINT JETSPEED_USER_PROFILE_UNIQUE UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_USER_PROFILE
    ADD CONSTRAINT JETSPEED_USER_PROFILE_PK
PRIMARY KEY (PSML_ID);


CREATE SEQUENCE JETSPEED_USER_PROFILE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- JETSPEED_GROUP_PROFILE
-----------------------------------------------------------------------------
DROP TABLE JETSPEED_GROUP_PROFILE CASCADE CONSTRAINTS;
DROP SEQUENCE JETSPEED_GROUP_PROFILE_SEQ;

CREATE TABLE JETSPEED_GROUP_PROFILE
(
    PSML_ID NUMBER NOT NULL,
    GROUP_NAME VARCHAR2 (99) NOT NULL,
    MEDIA_TYPE VARCHAR2 (99),
    LANGUAGE VARCHAR2 (2),
    COUNTRY VARCHAR2 (2),
    PAGE VARCHAR2 (99),
    PROFILE LONG RAW,
    CONSTRAINT JETSPEED_GROUP_PROFILE_UNIQUE UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_GROUP_PROFILE
    ADD CONSTRAINT JETSPEED_GROUP_PROFILE_PK
PRIMARY KEY (PSML_ID);


CREATE SEQUENCE JETSPEED_GROUP_PROFILE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- JETSPEED_ROLE_PROFILE
-----------------------------------------------------------------------------
DROP TABLE JETSPEED_ROLE_PROFILE CASCADE CONSTRAINTS;
DROP SEQUENCE JETSPEED_ROLE_PROFILE_SEQ;

CREATE TABLE JETSPEED_ROLE_PROFILE
(
    PSML_ID NUMBER NOT NULL,
    ROLE_NAME VARCHAR2 (99) NOT NULL,
    MEDIA_TYPE VARCHAR2 (99),
    LANGUAGE VARCHAR2 (2),
    COUNTRY VARCHAR2 (2),
    PAGE VARCHAR2 (99),
    PROFILE LONG RAW,
    CONSTRAINT JETSPEED_ROLE_PROFILE_UNIQUE UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_ROLE_PROFILE
    ADD CONSTRAINT JETSPEED_ROLE_PROFILE_PK
PRIMARY KEY (PSML_ID);


CREATE SEQUENCE JETSPEED_ROLE_PROFILE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- COFFEES
-----------------------------------------------------------------------------
DROP TABLE COFFEES CASCADE CONSTRAINTS;
DROP SEQUENCE COFFEES_SEQ;

CREATE TABLE COFFEES
(
    COFFEE_ID NUMBER NOT NULL,
    COFFEE_NAME VARCHAR2 (50),
    SUPPLIER_ID NUMBER,
    PRICE FLOAT,
    SALES NUMBER,
    TOTAL NUMBER
);

ALTER TABLE COFFEES
    ADD CONSTRAINT COFFEES_PK
PRIMARY KEY (COFFEE_ID);


CREATE SEQUENCE COFFEES_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;




-----------------------------------------------------------------------------
-- EMAIL
-----------------------------------------------------------------------------
DROP TABLE EMAIL_INBOX CASCADE CONSTRAINTS;
DROP SEQUENCE EMAIL_INBOX_SEQ;  

CREATE TABLE EMAIL_INBOX
(
	EMAIL_INBOX_ID INTEGER NOT NULL,
	MESSAGE_ID VARCHAR(255),
	FILENAME VARCHAR(255),
	ATTACHMENT LONG RAW,
	READFLAG INTEGER
);

ALTER TABLE EMAIL_INBOX
    ADD CONSTRAINT EMAIL_INBOX
PRIMARY KEY (EMAIL_INBOX_ID);

CREATE SEQUENCE EMAIL_INBOX_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE;


-----------------------------------------------------------------------------
-- portlet
-----------------------------------------------------------------------------
DROP TABLE portlet CASCADE CONSTRAINTS;
DROP SEQUENCE portlet_SEQ;

CREATE TABLE portlet
(
    ID NUMBER (20, 0) NOT NULL,
    NAME VARCHAR2 (128) NOT NULL,
    HIDDEN NUMBER default 0,
    CLASSNAME VARCHAR2 (255),
    TYPE VARCHAR2 (32) NOT NULL,
    APPLICATION NUMBER default 0,
    PARENT VARCHAR2 (128),
    URL VARCHAR2 (255),
    CACHED_ON_URL NUMBER default 1,
    TITLE VARCHAR2 (128),
    DESCRIPTION VARCHAR2 (255),
    IMAGE VARCHAR2 (128),
    SECURITY VARCHAR2 (128),
    CONSTRAINT uk_portlet_name UNIQUE (NAME)
);

ALTER TABLE portlet
    ADD CONSTRAINT portlet_PK
PRIMARY KEY (ID);


CREATE SEQUENCE portlet_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- portlet_category
-----------------------------------------------------------------------------
DROP TABLE portlet_category CASCADE CONSTRAINTS;
DROP SEQUENCE portlet_category_SEQ;

CREATE TABLE portlet_category
(
    ID NUMBER (20, 0) NOT NULL,
    NAME VARCHAR2 (128) NOT NULL,
    GROUPE VARCHAR2 (128) NOT NULL,
    PORTLET_ID NUMBER (20, 0)
);

ALTER TABLE portlet_category
    ADD CONSTRAINT portlet_category_PK
PRIMARY KEY (ID);


CREATE SEQUENCE portlet_category_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- portlet_parameter
-----------------------------------------------------------------------------
DROP TABLE portlet_parameter CASCADE CONSTRAINTS;
DROP SEQUENCE portlet_parameter_SEQ;

CREATE TABLE portlet_parameter
(
    ID NUMBER (20, 0) NOT NULL,
    NAME VARCHAR2 (128) NOT NULL,
    VALUE VARCHAR2 (255) default '0',
    TYPE VARCHAR2 (64),
    HIDDEN NUMBER default 0,
    CACHED_ON_VALUE NUMBER default 1,
    CACHED_ON_NAME NUMBER default 1,
    TITLE VARCHAR2 (128),
    DESCRIPTION VARCHAR2 (255),
    IMAGE VARCHAR2 (128),
    PORTLET_ID NUMBER (20, 0),
    SECURITY VARCHAR2 (128)
);

ALTER TABLE portlet_parameter
    ADD CONSTRAINT portlet_parameter_PK
PRIMARY KEY (ID);


CREATE SEQUENCE portlet_parameter_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- portlet_mediatype
-----------------------------------------------------------------------------
DROP TABLE portlet_mediatype CASCADE CONSTRAINTS;
DROP SEQUENCE portlet_mediatype_SEQ;

CREATE TABLE portlet_mediatype
(
    ID NUMBER (20, 0) NOT NULL,
    NAME VARCHAR2 (128) NOT NULL,
    PORTLET_ID NUMBER (20, 0)
);

ALTER TABLE portlet_mediatype
    ADD CONSTRAINT portlet_mediatype_PK
PRIMARY KEY (ID);


CREATE SEQUENCE portlet_mediatype_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- security_entry
-----------------------------------------------------------------------------
DROP TABLE security_entry CASCADE CONSTRAINTS;
DROP SEQUENCE security_entry_SEQ;

CREATE TABLE security_entry
(
    ID NUMBER (20, 0) NOT NULL,
    NAME VARCHAR2 (128) NOT NULL,
    TITLE VARCHAR2 (128),
    DESCRIPTION VARCHAR2 (255),
    CONSTRAINT uk_constraint_name UNIQUE (NAME)
);

ALTER TABLE security_entry
    ADD CONSTRAINT security_entry_PK
PRIMARY KEY (ID);


CREATE SEQUENCE security_entry_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- security_access
-----------------------------------------------------------------------------
DROP TABLE security_access CASCADE CONSTRAINTS;
DROP SEQUENCE security_access_SEQ;

CREATE TABLE security_access
(
    ID NUMBER (20, 0) NOT NULL,
    ACTION VARCHAR2 (32) NOT NULL,
    ENTRY_ID NUMBER (20, 0)
);

ALTER TABLE security_access
    ADD CONSTRAINT security_access_PK
PRIMARY KEY (ID);


CREATE SEQUENCE security_access_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;

-----------------------------------------------------------------------------
-- security_allow
-----------------------------------------------------------------------------
DROP TABLE security_allow CASCADE CONSTRAINTS;
DROP SEQUENCE security_allow_SEQ;

CREATE TABLE security_allow
(
    ID NUMBER (20, 0) NOT NULL,
    ALLOW_TYPE VARCHAR2 (32) NOT NULL,
    ALLOW_VALUE VARCHAR2 (32) NOT NULL,
    ALLOW_GROUP VARCHAR2 (32),
    ACCESS_ID NUMBER (20, 0)
);

ALTER TABLE security_allow
    ADD CONSTRAINT security_allow_PK
PRIMARY KEY (ID);


CREATE SEQUENCE security_allow_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER;


ALTER TABLE portlet_category
    ADD CONSTRAINT portlet_category_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
ON DELETE CASCADE
;



ALTER TABLE portlet_parameter
    ADD CONSTRAINT portlet_parameter_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
ON DELETE CASCADE
;



ALTER TABLE portlet_mediatype
    ADD CONSTRAINT portlet_mediatype_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
ON DELETE CASCADE
;





ALTER TABLE security_access
    ADD CONSTRAINT security_access_FK_1 FOREIGN KEY (ENTRY_ID)
    REFERENCES security_entry (ID)
ON DELETE CASCADE
;



ALTER TABLE security_allow
    ADD CONSTRAINT security_allow_FK_1 FOREIGN KEY (ACCESS_ID)
    REFERENCES security_access (ID)
ON DELETE CASCADE
;


