turbine-db2.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;

CREATE TABLE TURBINE_USER
(
            USER_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            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 VARCHAR (1),
            OBJECTDATA CHAR(1) FOR BIT DATA CHAR(n) FOR BIT DATA,
            PASSWORD_CHANGED TIMESTAMP,
    UNIQUE (LOGIN_NAME)
);

ALTER TABLE TURBINE_USER
    ADD PRIMARY KEY (USER_ID);




-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_ROLE;

CREATE TABLE TURBINE_ROLE
(
            ROLE_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            ROLE_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (ROLE_NAME)
);

ALTER TABLE TURBINE_ROLE
    ADD PRIMARY KEY (ROLE_ID);




-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
drop table TURBINE_GROUP;

CREATE TABLE TURBINE_GROUP
(
            GROUP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            GROUP_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (GROUP_NAME)
);

ALTER TABLE TURBINE_GROUP
    ADD PRIMARY KEY (GROUP_ID);




-----------------------------------------------------------------------------
-- TURBINE_PERMISSION
-----------------------------------------------------------------------------
drop table TURBINE_PERMISSION;

CREATE TABLE TURBINE_PERMISSION
(
            PERMISSION_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            PERMISSION_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (PERMISSION_NAME)
);

ALTER TABLE TURBINE_PERMISSION
    ADD PRIMARY KEY (PERMISSION_ID);




-----------------------------------------------------------------------------
-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
drop table TURBINE_ROLE_PERMISSION;

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

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

ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE__FK_1 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
;
ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE__FK_2 FOREIGN KEY (PERMISSION_ID)
    REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
;



-----------------------------------------------------------------------------
-- TURBINE_USER_GROUP_ROLE
-----------------------------------------------------------------------------
drop table TURBINE_USER_GROUP_ROLE;

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

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

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER__FK_1 FOREIGN KEY (USER_ID)
    REFERENCES TURBINE_USER (USER_ID)
;
ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER__FK_2 FOREIGN KEY (GROUP_ID)
    REFERENCES TURBINE_GROUP (GROUP_ID)
;
ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER__FK_3 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
;

-----------------------------------------------------------------------------
-- JETSPEED_USER_PROFILE
-----------------------------------------------------------------------------
drop table JETSPEED_USER_PROFILE;

CREATE TABLE JETSPEED_USER_PROFILE
(
            PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            USER_NAME VARCHAR (32) NOT NULL,
            MEDIA_TYPE VARCHAR (99),
            LANGUAGE VARCHAR (2),
            COUNTRY VARCHAR (2),
            PAGE VARCHAR (99),
            PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_USER_PROFILE
    ADD PRIMARY KEY (PSML_ID);




-----------------------------------------------------------------------------
-- JETSPEED_GROUP_PROFILE
-----------------------------------------------------------------------------
drop table JETSPEED_GROUP_PROFILE;

CREATE TABLE JETSPEED_GROUP_PROFILE
(
            PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            GROUP_NAME VARCHAR (99) NOT NULL,
            MEDIA_TYPE VARCHAR (99),
            LANGUAGE VARCHAR (2),
            COUNTRY VARCHAR (2),
            PAGE VARCHAR (99),
            PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_GROUP_PROFILE
    ADD PRIMARY KEY (PSML_ID);




-----------------------------------------------------------------------------
-- JETSPEED_ROLE_PROFILE
-----------------------------------------------------------------------------
drop table JETSPEED_ROLE_PROFILE;

CREATE TABLE JETSPEED_ROLE_PROFILE
(
            PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            ROLE_NAME VARCHAR (99) NOT NULL,
            MEDIA_TYPE VARCHAR (99),
            LANGUAGE VARCHAR (2),
            COUNTRY VARCHAR (2),
            PAGE VARCHAR (99),
            PROFILE CHAR(99) FOR BIT DATA CHAR(n) FOR BIT DATA,
    UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
);

ALTER TABLE JETSPEED_ROLE_PROFILE
    ADD PRIMARY KEY (PSML_ID);


-----------------------------------------------------------------------------
-- COFFEES
-----------------------------------------------------------------------------
drop table COFFEES;

CREATE TABLE COFFEES
(
            COFFEE_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
            COFFEE_NAME VARCHAR (50),
            SUPPLIER_ID INTEGER,
            PRICE FLOAT,
            SALES INTEGER,
            TOTAL INTEGER
);

ALTER TABLE COFFEES
    ADD PRIMARY KEY (COFFEE_ID);



-----------------------------------------------------------------------------
-- EMAIL
-----------------------------------------------------------------------------

drop table EMAIL_INBOX;

CREATE TABLE EMAIL_INBOX (
  EMAIL_INBOX_ID INTEGER(11) NOT NULL AUTO_INCREMENT,
  MESSAGE_ID VARCHAR(255),
  FILENAME VARCHAR(255),
  ATTACHMENT BLOB,
  READFLAG INTEGER(11)
);

ALTER TABLE EMAIL_INBOX
    ADD PRIMARY KEY (EMAIL_INBOX_ID);