turbine-mysql.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_PERMISSION
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID integer NOT NULL IDENTITY,
    PERMISSION_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BINARY,
    PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_NAME)
);


    
-----------------------------------------------------------------------------
-- TURBINE_ROLE
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_ROLE
(
    ROLE_ID integer NOT NULL IDENTITY,
    ROLE_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BINARY,
    PRIMARY KEY(ROLE_ID),
    UNIQUE (ROLE_NAME)
);


    
-----------------------------------------------------------------------------
-- TURBINE_GROUP
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_GROUP
(
    GROUP_ID integer NOT NULL IDENTITY,
    GROUP_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA BINARY,
    PRIMARY KEY(GROUP_ID),
    UNIQUE (GROUP_NAME)
);


    
-----------------------------------------------------------------------------
-- TURBINE_ROLE_PERMISSION
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_ROLE_PERMISSION
(
    ROLE_ID integer NOT NULL,
    PERMISSION_ID integer NOT NULL,
    PRIMARY KEY(ROLE_ID,PERMISSION_ID)
);

CREATE  INDEX ROLE_PERMISSION_INDEX ON TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID);
    
-----------------------------------------------------------------------------
-- TURBINE_USER
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_USER
(
    USER_ID integer NOT NULL 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 CHAR,
    OBJECTDATA BINARY,
    PRIMARY KEY(USER_ID),
    UNIQUE (LOGIN_NAME)
);


    
-----------------------------------------------------------------------------
-- 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,
    PRIMARY KEY(USER_ID,GROUP_ID,ROLE_ID)
);

CREATE  INDEX USER_GROUP_ROLE_INDEX ON TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID);
    
-----------------------------------------------------------------------------
-- TURBINE_SCHEDULED_JOB
-----------------------------------------------------------------------------
CREATE TABLE TURBINE_SCHEDULED_JOB
(
    JOB_ID integer NOT NULL,
    SECOND integer default -1 NOT NULL,
    MINUTE integer default -1 NOT NULL,
    HOUR integer default -1 NOT NULL,
    WEEK_DAY integer default -1 NOT NULL,
    DAY_OF_MONTH integer default -1 NOT NULL,
    TASK VARCHAR (99) NOT NULL,
    EMAIL VARCHAR (99),
    PRIMARY KEY(JOB_ID)
);


-----------------------------------------------------------------------------
-- COFFEES
-----------------------------------------------------------------------------
CREATE TABLE COFFEES
(
    COFFEE_ID integer IDENTITY,
    COFFEE_NAME VARCHAR (50),
    SUPPLIER_ID integer,
    PRICE FLOAT,
    SALES integer,
    TOTAL integer
);


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

CREATE TABLE EMAIL_INBOX (
  EMAIL_INBOX_ID integer(11) NOT NULL auto_increment,
  MESSAGE_ID varchar(255) default NULL,
  FILENAME varchar(255) default NULL,
  ATTACHMENT longblob,
  READFLAG int(11) default NULL,
  PRIMARY KEY  (EMAIL_INBOX_ID)
);