turbine-oracle.sql

jetspeed源代码

-----------------------------------------------------------------------------
-- 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 CONTRAINTS;
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;