/*-----------------------------------------------------------------------------*/
/*-- 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                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER')
BEGIN
     DECLARE @reftable_1 nvarchar(60), @constraintname_1 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 = 'TURBINE_USER'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_1, @constraintname_1
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1)
       FETCH NEXT from refcursor into @reftable_1, @constraintname_1
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_USER
END


CREATE TABLE TURBINE_USER
(
            USER_ID INT NOT NULL IDENTITY (1,1),
            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) NULL,
            CONFIRM_VALUE VARCHAR (99) NULL,
            MODIFIED DATETIME NULL,
            CREATED DATETIME NULL,
            LAST_LOGIN DATETIME NULL,
            DISABLED CHAR (1) NULL,
            OBJECTDATA BINARY(7132) NULL,
            PASSWORD_CHANGED DATETIME NULL,

    CONSTRAINT TURBINE_USER_PK PRIMARY KEY(USER_ID),
    UNIQUE (LOGIN_NAME));





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

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE')
BEGIN
     DECLARE @reftable_2 nvarchar(60), @constraintname_2 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 = 'TURBINE_ROLE'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_2, @constraintname_2
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2)
       FETCH NEXT from refcursor into @reftable_2, @constraintname_2
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_ROLE
END


CREATE TABLE TURBINE_ROLE
(
            ROLE_ID INT NOT NULL IDENTITY (1,1),
            ROLE_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA BINARY(7132) NULL,

    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
     DECLARE @reftable_3 nvarchar(60), @constraintname_3 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 = 'TURBINE_GROUP'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_3, @constraintname_3
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3)
       FETCH NEXT from refcursor into @reftable_3, @constraintname_3
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_GROUP
END


CREATE TABLE TURBINE_GROUP
(
            GROUP_ID INT NOT NULL IDENTITY (1,1),
            GROUP_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA BINARY(7132) NULL,

    CONSTRAINT TURBINE_GROUP_PK PRIMARY KEY(GROUP_ID),
    UNIQUE (GROUP_NAME));





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

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_PERMISSION')
BEGIN
     DECLARE @reftable_4 nvarchar(60), @constraintname_4 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 = 'TURBINE_PERMISSION'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_4, @constraintname_4
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
       FETCH NEXT from refcursor into @reftable_4, @constraintname_4
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_PERMISSION
END


CREATE TABLE TURBINE_PERMISSION
(
            PERMISSION_ID INT NOT NULL IDENTITY (1,1),
            PERMISSION_NAME VARCHAR (99) NOT NULL,
            OBJECTDATA BINARY(7132) NULL,

    CONSTRAINT TURBINE_PERMISSION_PK PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_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;
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;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_ROLE_PERMISSION')
BEGIN
     DECLARE @reftable_5 nvarchar(60), @constraintname_5 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 = 'TURBINE_ROLE_PERMISSION'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_5, @constraintname_5
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
       FETCH NEXT from refcursor into @reftable_5, @constraintname_5
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_ROLE_PERMISSION
END


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





/* ---------------------------------------------------------------------- */
/* 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;
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;
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;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TURBINE_USER_GROUP_ROLE')
BEGIN
     DECLARE @reftable_6 nvarchar(60), @constraintname_6 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 = 'TURBINE_USER_GROUP_ROLE'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_6, @constraintname_6
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
       FETCH NEXT from refcursor into @reftable_6, @constraintname_6
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE TURBINE_USER_GROUP_ROLE
END


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





/* ---------------------------------------------------------------------- */
/* 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)
)


/* ---------------------------------------------------------------------- */
/* portlet                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'portlet')
BEGIN
     DECLARE @reftable_1 nvarchar(60), @constraintname_1 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 = 'portlet'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_1, @constraintname_1
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1)
       FETCH NEXT from refcursor into @reftable_1, @constraintname_1
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE portlet
END
;

CREATE TABLE portlet
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    NAME VARCHAR (128) NOT NULL,
                    HIDDEN INT default 0 NULL,
                    CLASSNAME VARCHAR (255) NULL,
                    TYPE VARCHAR (32) NOT NULL,
                    APPLICATION INT default 0 NULL,
                    PARENT VARCHAR (128) NULL,
                    URL VARCHAR (255) NULL,
                    CACHED_ON_URL INT default 1 NULL,
                    TITLE VARCHAR (128) NULL,
                    DESCRIPTION VARCHAR (255) NULL,
                    IMAGE VARCHAR (128) NULL,
                    SECURITY VARCHAR (128) NULL,

    CONSTRAINT portlet_PK PRIMARY KEY(ID),
    UNIQUE (NAME));





/* ---------------------------------------------------------------------- */
/* portlet_category                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='portlet_category_FK_1')
    ALTER TABLE portlet_category DROP CONSTRAINT portlet_category_FK_1;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'portlet_category')
BEGIN
     DECLARE @reftable_2 nvarchar(60), @constraintname_2 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 = 'portlet_category'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_2, @constraintname_2
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2)
       FETCH NEXT from refcursor into @reftable_2, @constraintname_2
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE portlet_category
END
;

CREATE TABLE portlet_category
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    NAME VARCHAR (128) NOT NULL,
                    GROUPE VARCHAR (128) NOT NULL,
                    PORTLET_ID BIGINT NULL,

    CONSTRAINT portlet_category_PK PRIMARY KEY(ID));





/* ---------------------------------------------------------------------- */
/* portlet_parameter                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='portlet_parameter_FK_1')
    ALTER TABLE portlet_parameter DROP CONSTRAINT portlet_parameter_FK_1;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'portlet_parameter')
BEGIN
     DECLARE @reftable_3 nvarchar(60), @constraintname_3 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 = 'portlet_parameter'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_3, @constraintname_3
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3)
       FETCH NEXT from refcursor into @reftable_3, @constraintname_3
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE portlet_parameter
END
;

CREATE TABLE portlet_parameter
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    NAME VARCHAR (128) NOT NULL,
                    VALUE VARCHAR (255) default '0' NULL,
                    TYPE VARCHAR (64) NULL,
                    HIDDEN INT default 0 NULL,
                    CACHED_ON_VALUE INT default 1 NULL,
                    CACHED_ON_NAME INT default 1 NULL,
                    TITLE VARCHAR (128) NULL,
                    DESCRIPTION VARCHAR (255) NULL,
                    IMAGE VARCHAR (128) NULL,
                    PORTLET_ID BIGINT NULL,
                    SECURITY VARCHAR (128) NULL,

    CONSTRAINT portlet_parameter_PK PRIMARY KEY(ID));





/* ---------------------------------------------------------------------- */
/* portlet_mediatype                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='portlet_mediatype_FK_1')
    ALTER TABLE portlet_mediatype DROP CONSTRAINT portlet_mediatype_FK_1;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'portlet_mediatype')
BEGIN
     DECLARE @reftable_4 nvarchar(60), @constraintname_4 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 = 'portlet_mediatype'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_4, @constraintname_4
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
       FETCH NEXT from refcursor into @reftable_4, @constraintname_4
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE portlet_mediatype
END
;

CREATE TABLE portlet_mediatype
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    NAME VARCHAR (128) NOT NULL,
                    PORTLET_ID BIGINT NULL,

    CONSTRAINT portlet_mediatype_PK PRIMARY KEY(ID));





/* ---------------------------------------------------------------------- */
/* security_entry                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'security_entry')
BEGIN
     DECLARE @reftable_5 nvarchar(60), @constraintname_5 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 = 'security_entry'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_5, @constraintname_5
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
       FETCH NEXT from refcursor into @reftable_5, @constraintname_5
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE security_entry
END
;

CREATE TABLE security_entry
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    NAME VARCHAR (128) NOT NULL,
                    TITLE VARCHAR (128) NULL,
                    DESCRIPTION VARCHAR (255) NULL,

    CONSTRAINT security_entry_PK PRIMARY KEY(ID),
    UNIQUE (NAME));





/* ---------------------------------------------------------------------- */
/* security_access                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='security_access_FK_1')
    ALTER TABLE security_access DROP CONSTRAINT security_access_FK_1;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'security_access')
BEGIN
     DECLARE @reftable_6 nvarchar(60), @constraintname_6 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 = 'security_access'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_6, @constraintname_6
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
       FETCH NEXT from refcursor into @reftable_6, @constraintname_6
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE security_access
END
;

CREATE TABLE security_access
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    ACTION VARCHAR (32) NOT NULL,
                    ENTRY_ID BIGINT NULL,

    CONSTRAINT security_access_PK PRIMARY KEY(ID));





/* ---------------------------------------------------------------------- */
/* security_allow                                                      */
/* ---------------------------------------------------------------------- */

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='security_allow_FK_1')
    ALTER TABLE security_allow DROP CONSTRAINT security_allow_FK_1;
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'security_allow')
BEGIN
     DECLARE @reftable_7 nvarchar(60), @constraintname_7 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 = 'security_allow'
     OPEN refcursor
     FETCH NEXT from refcursor into @reftable_7, @constraintname_7
     while @@FETCH_STATUS = 0
     BEGIN
       exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7)
       FETCH NEXT from refcursor into @reftable_7, @constraintname_7
     END
     CLOSE refcursor
     DEALLOCATE refcursor
     DROP TABLE security_allow
END
;

CREATE TABLE security_allow
(
                    ID BIGINT NOT NULL IDENTITY (1,1),
                    ALLOW_TYPE VARCHAR (32) NOT NULL,
                    ALLOW_VALUE VARCHAR (32) NOT NULL,
                    ALLOW_GROUP VARCHAR (32) NULL,
                    ACCESS_ID BIGINT NULL,

    CONSTRAINT security_allow_PK PRIMARY KEY(ID));





/* ---------------------------------------------------------------------- */
/* security_allow                                                      */
/* ---------------------------------------------------------------------- */




/* ---------------------------------------------------------------------- */
/* portlet                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE portlet_category
    ADD CONSTRAINT portlet_category_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
    ON DELETE CASCADE 
END    
;




/* ---------------------------------------------------------------------- */
/* portlet_category                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE portlet_parameter
    ADD CONSTRAINT portlet_parameter_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
    ON DELETE CASCADE 
END    
;




/* ---------------------------------------------------------------------- */
/* portlet_parameter                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE portlet_mediatype
    ADD CONSTRAINT portlet_mediatype_FK_1 FOREIGN KEY (PORTLET_ID)
    REFERENCES portlet (ID)
    ON DELETE CASCADE 
END    
;




/* ---------------------------------------------------------------------- */
/* portlet_mediatype                                                      */
/* ---------------------------------------------------------------------- */




/* ---------------------------------------------------------------------- */
/* security_entry                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE security_access
    ADD CONSTRAINT security_access_FK_1 FOREIGN KEY (ENTRY_ID)
    REFERENCES security_entry (ID)
    ON DELETE CASCADE 
END    
;




/* ---------------------------------------------------------------------- */
/* security_access                                                      */
/* ---------------------------------------------------------------------- */

BEGIN
ALTER TABLE security_allow
    ADD CONSTRAINT security_allow_FK_1 FOREIGN KEY (ACCESS_ID)
    REFERENCES security_access (ID)
    ON DELETE CASCADE 
END    
;



