turbine-sqlserver.sql

jetspeed源代码

    CONSTRAINT TURBINE_USER_GROUP_ROLE_PK PRIMARY KEY(USER_ID,GROUP_ID,ROLE_ID));





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




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




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




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




/* ---------------------------------------------------------------------- */
/* TURBINE_PERMISSION                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_1 FOREIGN KEY (ROLE_ID)
    REFERENCES TURBINE_ROLE (ROLE_ID)
    ON DELETE CASCADE 
END    
;

BEGIN
ALTER TABLE TURBINE_ROLE_PERMISSION
    ADD CONSTRAINT TURBINE_ROLE_PERMISSION_FK_2 FOREIGN KEY (PERMISSION_ID)
    REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
    ON DELETE CASCADE 
END    
;




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

BEGIN
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 
END    
;

BEGIN
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 
END    
;

BEGIN
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 
END    
;



/* ---------------------------------------------------------------------- */
/* JETSPEED_USER_PROFILE                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_USER_PROFILE')
BEGIN
     DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60)
     DECLARE refcursor CURSOR FOR
     select reftables.name tablename, cons.name constraintname
      from sysobjects tables,
           sysobjects reftables,
           sysobjects cons,
           sysreferences ref
       where tables.id = ref.rkeyid
         and cons.id = ref.constid
         and reftables.id = ref.fkeyid
         and tables.name = 'JETSPEED_USER_PROFILE'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_11, @constraintname_11
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_11+' drop constraint '+@constraintname_11)
       FETCH NEXT from refcursor into @reftable_11, @constraintname_11
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE JETSPEED_USER_PROFILE
END


CREATE TABLE JETSPEED_USER_PROFILE
(
            PSML_ID INT NOT NULL IDENTITY (1,1),
            USER_NAME VARCHAR (32) NOT NULL,
            MEDIA_TYPE VARCHAR (99) NULL,
            LANGUAGE VARCHAR (2) NULL,
            COUNTRY VARCHAR (2) NULL,
            PAGE VARCHAR (99) NULL,
            PROFILE BINARY NULL,

    CONSTRAINT JETSPEED_USER_PROFILE_PK PRIMARY KEY(PSML_ID),
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE));





/* ---------------------------------------------------------------------- */
/* JETSPEED_GROUP_PROFILE                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_GROUP_PROFILE')
BEGIN
     DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60)
     DECLARE refcursor CURSOR FOR
     select reftables.name tablename, cons.name constraintname
      from sysobjects tables,
           sysobjects reftables,
           sysobjects cons,
           sysreferences ref
       where tables.id = ref.rkeyid
         and cons.id = ref.constid
         and reftables.id = ref.fkeyid
         and tables.name = 'JETSPEED_GROUP_PROFILE'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_12, @constraintname_12
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_12+' drop constraint '+@constraintname_12)
       FETCH NEXT from refcursor into @reftable_12, @constraintname_2
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE JETSPEED_GROUP_PROFILE
END


CREATE TABLE JETSPEED_GROUP_PROFILE
(
            PSML_ID INT NOT NULL IDENTITY (1,1),
            GROUP_NAME VARCHAR (99) NOT NULL,
            MEDIA_TYPE VARCHAR (99) NULL,
            LANGUAGE VARCHAR (2) NULL,
            COUNTRY VARCHAR (2) NULL,
            PAGE VARCHAR (99) NULL,
            PROFILE BINARY NULL,

    CONSTRAINT JETSPEED_GROUP_PROFILE_PK PRIMARY KEY(PSML_ID),
    UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE));





/* ---------------------------------------------------------------------- */
/* JETSPEED_ROLE_PROFILE                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'JETSPEED_ROLE_PROFILE')
BEGIN
     DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60)
     DECLARE refcursor CURSOR FOR
     select reftables.name tablename, cons.name constraintname
      from sysobjects tables,
           sysobjects reftables,
           sysobjects cons,
           sysreferences ref
       where tables.id = ref.rkeyid
         and cons.id = ref.constid
         and reftables.id = ref.fkeyid
         and tables.name = 'JETSPEED_ROLE_PROFILE'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_13, @constraintname_13
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_13+' drop constraint '+@constraintname_13)
       FETCH NEXT from refcursor into @reftable_13, @constraintname_13
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE JETSPEED_ROLE_PROFILE
END


CREATE TABLE JETSPEED_ROLE_PROFILE
(
            PSML_ID INT NOT NULL IDENTITY (1,1),
            ROLE_NAME VARCHAR (99) NOT NULL,
            MEDIA_TYPE VARCHAR (99) NULL,
            LANGUAGE VARCHAR (2) NULL,
            COUNTRY VARCHAR (2) NULL,
            PAGE VARCHAR (99) NULL,	
            PROFILE BINARY NULL,

    CONSTRAINT JETSPEED_ROLE_PROFILE_PK PRIMARY KEY(PSML_ID),
    UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE));


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

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'COFFEES')
BEGIN
     DECLARE @reftable_21 nvarchar(60), @constraintname_21 nvarchar(60)
     DECLARE refcursor CURSOR FOR
     select reftables.name tablename, cons.name constraintname
      from sysobjects tables,
           sysobjects reftables,
           sysobjects cons,
           sysreferences ref
       where tables.id = ref.rkeyid
         and cons.id = ref.constid
         and reftables.id = ref.fkeyid
         and tables.name = 'COFFEES'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_21, @constraintname_21
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_21+' drop constraint '+@constraintname_21)
       FETCH NEXT from refcursor into @reftable_21, @constraintname_21
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE COFFEES
END


CREATE TABLE COFFEES
(
    COFFEE_ID INT NOT NULL IDENTITY (1,1),
    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                                                                  */
/* ---------------------------------------------------------------------- */
DROP TABLE EMAIL_INBOX;

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