# Run this script in MySQL as:
# mysql -u root - p mydb < myscript.sql
# Enter Password: ****
    
--------------------------------------------------------------------------
-- TURBINE_PERMISSION
--------------------------------------------------------------------------

CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID INTEGER NOT NULL AUTO_INCREMENT,
    PERMISSION_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA MEDIUMBLOB,
    PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_NAME)
);

    
--------------------------------------------------------------------------
-- TURBINE_ROLE
--------------------------------------------------------------------------

CREATE TABLE TURBINE_ROLE
(
    ROLE_ID INTEGER NOT NULL AUTO_INCREMENT,
    ROLE_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA MEDIUMBLOB,
    PRIMARY KEY(ROLE_ID),
    UNIQUE (ROLE_NAME)
);

    
--------------------------------------------------------------------------
-- TURBINE_GROUP
--------------------------------------------------------------------------

CREATE TABLE TURBINE_GROUP
(
    GROUP_ID INTEGER NOT NULL AUTO_INCREMENT,
    GROUP_NAME VARCHAR (99) NOT NULL,
    OBJECTDATA MEDIUMBLOB,
    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),
    FOREIGN KEY (ROLE_ID) REFERENCES TURBINE_ROLE (ROLE_ID),
    FOREIGN KEY (PERMISSION_ID) REFERENCES TURBINE_PERMISSION (PERMISSION_ID)
);

    
--------------------------------------------------------------------------
-- 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),
    FOREIGN KEY (USER_ID) REFERENCES TURBINE_USER (USER_ID),
    FOREIGN KEY (GROUP_ID) REFERENCES TURBINE_GROUP (GROUP_ID),
    FOREIGN KEY (ROLE_ID) REFERENCES TURBINE_USER_ROLE (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),
    PROPERTY MEDIUMBLOB,
    PRIMARY KEY(JOB_ID)
);

-----------------------------------------------------------------------------
-- ID_TABLE
-----------------------------------------------------------------------------


CREATE TABLE ID_TABLE
(
    ID_TABLE_ID INT NOT NULL,
    TABLE_NAME VARCHAR (255) NOT NULL,
    NEXT_ID INT,
    QUANTITY INT,
    PRIMARY KEY(ID_TABLE_ID),
    UNIQUE (TABLE_NAME)
);


# -----------------------------------------------------------------------
# JETSPEED_USER_PROFILE
# -----------------------------------------------------------------------

CREATE TABLE JETSPEED_USER_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    USER_NAME VARCHAR (32) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE MEDIUMBLOB NOT NULL,
    PRIMARY KEY(PSML_ID),
    UNIQUE (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
    INDEX JETSPEED_USER_PROFILE_INDEX (USER_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
   
);

# -----------------------------------------------------------------------
# JETSPEED_GROUP_PROFILE
# -----------------------------------------------------------------------

CREATE TABLE JETSPEED_GROUP_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    GROUP_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE MEDIUMBLOB NOT NULL,
    PRIMARY KEY(PSML_ID),
    UNIQUE (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
    INDEX JETSPEED_GROUP_PROFILE_INDEX (GROUP_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
   
);

# -----------------------------------------------------------------------
# JETSPEED_ROLE_PROFILE
# -----------------------------------------------------------------------

CREATE TABLE JETSPEED_ROLE_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    ROLE_NAME VARCHAR (99) NOT NULL,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE MEDIUMBLOB NOT NULL,
    PRIMARY KEY(PSML_ID),
    UNIQUE (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
    INDEX JETSPEED_ROLE_PROFILE_INDEX (ROLE_NAME, MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
   
);

# -----------------------------------------------------------------------
# JETSPEED_ANON_PROFILE
# -----------------------------------------------------------------------

CREATE TABLE JETSPEED_ANON_PROFILE
(
    PSML_ID INTEGER NOT NULL AUTO_INCREMENT,
    MEDIA_TYPE VARCHAR (99),
    LANGUAGE VARCHAR (2),
    COUNTRY VARCHAR (2),
    PAGE VARCHAR (99),
    PROFILE MEDIUMBLOB NOT NULL,
    PRIMARY KEY(PSML_ID),
    UNIQUE (MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE),
    INDEX JETSPEED_ANON_PROFILE_INDEX (MEDIA_TYPE, LANGUAGE, COUNTRY, PAGE)
   
);
  
  
  
  

INSERT INTO TURBINE_PERMISSION VALUES(1,'view',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(2,'customize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(3,'maximize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(4,'minimize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(5,'personalize',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(6,'info',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(7,'close',NULL);
INSERT INTO TURBINE_PERMISSION VALUES(8,'detach',NULL);
INSERT INTO TURBINE_ROLE VALUES(1,'user',NULL);
INSERT INTO TURBINE_ROLE VALUES(2,'admin',NULL);
INSERT INTO TURBINE_GROUP VALUES(1,'Jetspeed',NULL);
INSERT INTO TURBINE_GROUP VALUES(2,'apache',NULL);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,1);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,2);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,3);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,4);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,5);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(1,6);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,1);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,2);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,3);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,4);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,5);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,6);
INSERT INTO TURBINE_ROLE_PERMISSION VALUES(2,7);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(0,1,1);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,1);
INSERT INTO TURBINE_USER_GROUP_ROLE VALUES(1,1,2);
INSERT INTO ID_TABLE VALUES(1,'TURBINE_PERMISSION',100,10);
INSERT INTO ID_TABLE VALUES(2,'TURBINE_ROLE',120,10);
INSERT INTO ID_TABLE VALUES(3,'TURBINE_GROUP',100,10);
INSERT INTO ID_TABLE VALUES(4,'TURBINE_USER',120,10);
INSERT INTO ID_TABLE VALUES(5,'TURBINE_SCHEDULED_JOB',100,10);
INSERT INTO ID_TABLE VALUES(6,'TURBINE_ROLE_PERMISSION',100,10);
INSERT INTO ID_TABLE VALUES(7,'TURBINE_USER_GROUP_ROLE',100,10);
INSERT INTO ID_TABLE VALUES(8,'JETSPEED_USER_PROFILE',120,10);
INSERT INTO ID_TABLE VALUES(9,'JETSPEED_GROUP_PROFILE',120,10);
INSERT INTO ID_TABLE VALUES(10,'JETSPEED_ROLE_PROFILE',120,10);
INSERT INTO ID_TABLE VALUES(11,'JETSPEED_ANON_PROFILE',120,10);





