turbine-sybase.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                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_PERMISSION')
BEGIN
	DROP TABLE TURBINE_PERMISSION
END
GO

CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID INT NOT NULL,
    PERMISSION_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA CHAR(10),
    CONSTRAINT TURBINE_PERMISSION_PK PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_NAME)
);



    
/* ---------------------------------------------------------------------- */
/* TURBINE_ROLE                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE')
BEGIN
	DROP TABLE TURBINE_ROLE
END
GO

CREATE TABLE TURBINE_ROLE
(
    ROLE_ID INT NOT NULL,
    ROLE_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA CHAR(10),
    CONSTRAINT TURBINE_ROLE_PK PRIMARY KEY(ROLE_ID),
    UNIQUE (ROLE_NAME)
);



    
/* ---------------------------------------------------------------------- */
/* TURBINE_GROUP                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_GROUP')
BEGIN
	DROP TABLE TURBINE_GROUP
END
GO

CREATE TABLE TURBINE_GROUP
(
    GROUP_ID INT NOT NULL,
    GROUP_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA CHAR(10),
    CONSTRAINT TURBINE_GROUP_PK PRIMARY KEY(GROUP_ID),
    UNIQUE (GROUP_NAME)
);



    
/* ---------------------------------------------------------------------- */
/* TURBINE_ROLE_PERMISSION                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_ROLE_PERMISSION_FK_1')
    ALTER TABLE TURBINE_ROLE_PERMISSION DROP CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_ROLE_PERMISSION_FK_2')
    ALTER TABLE TURBINE_ROLE_PERMISSION DROP CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE_PERMISSION')
BEGIN
	DROP TABLE TURBINE_ROLE_PERMISSION
END
GO

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

ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
GO

ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
    REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
GO


CREATE  INDEX ROLE_PERMISSION_INDEX ON TURBINE_ROLE_PERMISSION (ROLE_ID, PERMISSION_ID);

    
/* ---------------------------------------------------------------------- */
/* TURBINE_USER                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER')
BEGIN
	DROP TABLE TURBINE_USER
END
GO

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 VARCHAR(1),
    OBJECTDATA CHAR(10),
    CONSTRAINT TURBINE_USER_PK PRIMARY KEY(USER_ID),
    UNIQUE (LOGIN_NAME)
);



    
/* ---------------------------------------------------------------------- */
/* TURBINE_USER_GROUP_ROLE                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_1')
    ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_2')
    ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_2
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='TURBINE_USER_GROUP_ROLE_FK_3')
    ALTER TABLE TURBINE_USER_GROUP_ROLE DROP CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER_GROUP_ROLE')
BEGIN
	DROP TABLE TURBINE_USER_GROUP_ROLE
END
GO

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

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_1 FOREIGN KEY (USER_ID)
    REFERENCES TURBINE_USER (USER_ID)
GO

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_2 FOREIGN KEY (GROUP_ID)
    REFERENCES TURBINE_GROUP (GROUP_ID)
GO

ALTER TABLE TURBINE_USER_GROUP_ROLE
    ADD CONSTRAINT TURBINE_USER_GROUP_ROLE_FK_3 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_USER_ROLE (ROLE_ID)
GO


CREATE  INDEX USER_GROUP_ROLE_INDEX ON TURBINE_USER_GROUP_ROLE (USER_ID, GROUP_ID, ROLE_ID);

    
/* ---------------------------------------------------------------------- */
/* TURBINE_SCHEDULED_JOB                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_SCHEDULED_JOB')
BEGIN
	DROP TABLE TURBINE_SCHEDULED_JOB
END
GO

CREATE TABLE TURBINE_SCHEDULED_JOB
(
    JOB_ID INT NOT NULL,
    SECOND INT default -1 NOT NULL,
    MINUTE INT default -1 NOT NULL,
    HOUR INT default -1 NOT NULL,
    WEEK_DAY INT default -1 NOT NULL,
    DAY_OF_MONTH INT default -1 NOT NULL,
    TASK VARCHAR (99) NOT NULL,
    EMAIL VARCHAR (99),
    CONSTRAINT TURBINE_SCHEDULED_JOB_PK PRIMARY KEY(JOB_ID)
);


/* ---------------------------------------------------------------------- */
/* COFFEES                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'COFFEES')
BEGIN
    DROP TABLE COFFEES
END
;

CREATE TABLE COFFEES
(
    COFFEE_ID INT NOT NULL,
    COFFEE_NAME VARCHAR (50) NULL,
    SUPPLIER_ID INT NULL,
    PRICE FLOAT NULL,
    SALES INT NULL,
    TOTAL INT NULL,
    CONSTRAINT COFFEES_PK PRIMARY KEY(COFFEE_ID)
);

/* ---------------------------------------------------------------------- */
/* EMAIL                                                                  */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'EMAIL_INBOX')
BEGIN
    DROP TABLE EMAIL_INBOX
END
;


CREATE TABLE EMAIL_INBOX (
  EMAIL_INBOX_ID INT NOT NULL AUTO_INCREMENT,
  MESSAGE_ID VARCHAR(255),
  FILENAME VARCHAR(255),
  ATTACHMENT IMAGE,
  READFLAG INTEGER(11),
  CONSTRAINT EMAIL_INBOX_PK PRIMARY KEY(EMAIL_INBOX_ID)
);