turbine-sapdb.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 
//

DROP SEQUENCE TURBINE_USER_SEQ

//

CREATE TABLE TURBINE_USER
(
    USER_ID INT NOT NULL  ,
    LOGIN_NAME VARCHAR (32) NOT NULL,
    PASSWORD_VALUE VARCHAR (32) NOT NULL,
    FIRST_NAME VARCHAR (99) NOT NULL,
    LAST_NAME VARCHAR (99) NOT NULL,
    EMAIL VARCHAR (99),
    CONFIRM_VALUE VARCHAR (99),
    MODIFIED TIMESTAMP,
    CREATED TIMESTAMP,
    LAST_LOGIN TIMESTAMP,
    DISABLED CHAR (1),
    OBJECTDATA LONG,
    PASSWORD_CHANGED TIMESTAMP,
    CONSTRAINT TURBINE_USER_U_1 UNIQUE (LOGIN_NAME),
    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 

//
DROP SEQUENCE TURBINE_ROLE_SEQ

//

CREATE TABLE TURBINE_ROLE
(
    ROLE_ID INT NOT NULL  ,
    ROLE_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA LONG,
    CONSTRAINT TURBINE_ROLE_U_1 UNIQUE (ROLE_NAME),
    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 

//
DROP SEQUENCE TURBINE_GROUP_SEQ

//

CREATE TABLE TURBINE_GROUP
(
    GROUP_ID INT NOT NULL   ,
    GROUP_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA LONG,
    CONSTRAINT TURBINE_GROUP_U_1 UNIQUE (GROUP_NAME),
    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 

//
DROP SEQUENCE TURBINE_PERMISSION_SEQ

//

CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID INT NOT NULL  ,
    PERMISSION_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA LONG,
    CONSTRAINT TURBINE_PERMISSION_U_1 UNIQUE (PERMISSION_NAME),
    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 

//
DROP SEQUENCE TURBINE_ROLE_PERMISSION_SEQ

//

CREATE TABLE TURBINE_ROLE_PERMISSION
(
    ROLE_ID INT NOT NULL,
    PERMISSION_ID INT NOT NULL,
    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 

//
DROP SEQUENCE TURBINE_USER_GROUP_ROLE_SEQ

//

CREATE TABLE TURBINE_USER_GROUP_ROLE
(
    USER_ID INT NOT NULL,
    GROUP_ID INT NOT NULL,
    ROLE_ID INT NOT NULL,
    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
    FOREIGN KEY TURBINE_ROLE_PERMISSION_FK_1 (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
ON DELETE CASCADE 


//

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


//



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


//

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


//

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


//




-----------------------------------------------------------------------------
-- JETSPEED_USER_PROFILE
-----------------------------------------------------------------------------
DROP TABLE JETSPEED_USER_PROFILE 

//
DROP SEQUENCE JETSPEED_USER_PROFILE_SEQ

//

CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID INT NOT NULL,
    USER_NAME VARCHAR (32) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE LONG,
    CONSTRAINT JETSPEED_USER_PROFILE_UNIQUE UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
  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 

//
DROP SEQUENCE JETSPEED_GROUP_PROFILE_SEQ

//

CREATE TABLE JETSPEED_GROUP_PROFILE
(
    PSML_ID INT NOT NULL,
    GROUP_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE LONG,
    CONSTRAINT JETSPEED_GROUP_PROFILE_UNIQUE UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
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 

//
DROP SEQUENCE JETSPEED_ROLE_PROFILE_SEQ

//

CREATE TABLE JETSPEED_ROLE_PROFILE
(
    PSML_ID INT NOT NULL,
    ROLE_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE LONG,
    CONSTRAINT JETSPEED_ROLE_PROFILE_UNIQUE UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
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 

//
DROP SEQUENCE COFFEES_SEQ

//

CREATE TABLE COFFEES
(
    COFFEE_ID INT NOT NULL,
    COFFEE_NAME VARCHAR (50),
    SUPPLIER_ID INT,
    PRICE FLOAT,
    SALES INT,
    TOTAL INT,
    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 

//
DROP SEQUENCE EMAIL_INBOX_SEQ

//

CREATE TABLE EMAIL_INBOX
(
	EMAIL_INBOX_ID INTEGER NOT NULL,
	MESSAGE_ID VARCHAR(255),
	FILENAME VARCHAR(255),
	ATTACHMENT LONG BYTE,
	READFLAG INTEGER,
	CONSTRAINT EMAIL_INBOX_PK
PRIMARY KEY (EMAIL_INBOX_ID)
)


//

CREATE SEQUENCE EMAIL_INBOX_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE ORDER

//