CREATE TABLE CMS_USERS (
    USER_ID VARCHAR(36) BINARY NOT NULL,
    USER_NAME VARCHAR(128) BINARY NOT NULL,
    USER_PASSWORD VARCHAR(64) BINARY NOT NULL,
    USER_FIRSTNAME VARCHAR(128) NOT NULL,
    USER_LASTNAME VARCHAR(128) NOT NULL,
    USER_EMAIL VARCHAR(128) NOT NULL,
    USER_LASTLOGIN BIGINT NOT NULL,
    USER_FLAGS INT NOT NULL,
    USER_OU VARCHAR(128),
    USER_DATECREATED BIGINT NOT NULL,
    PRIMARY KEY    (USER_ID), 
    UNIQUE INDEX USER_FQN_IDX (USER_OU, USER_NAME),
    INDEX USER_NAME_IDX (USER_NAME),
    INDEX USER_OU_IDX (USER_OU)
);

CREATE TABLE CMS_USERDATA (
    USER_ID VARCHAR(36) BINARY NOT NULL,
    DATA_KEY VARCHAR(255) BINARY NOT NULL,
    DATA_VALUE BLOB,
    DATA_TYPE VARCHAR(128) BINARY NOT NULL,
    PRIMARY KEY (USER_ID, DATA_KEY),
    INDEX USERDATA_USER_IDX (USER_ID),
    INDEX USERDATA_DATA_IDX (DATA_KEY)
);

CREATE TABLE CMS_HISTORY_PRINCIPALS (
    PRINCIPAL_ID VARCHAR(36) BINARY NOT NULL,
    PRINCIPAL_NAME VARCHAR(128) BINARY NOT NULL,
    PRINCIPAL_DESCRIPTION VARCHAR(255) NOT NULL,
    PRINCIPAL_OU VARCHAR(128),
    PRINCIPAL_EMAIL VARCHAR(128) NOT NULL,
    PRINCIPAL_TYPE VARCHAR(5) NOT NULL,
    PRINCIPAL_USERDELETED VARCHAR(36) BINARY NOT NULL,
    PRINCIPAL_DATEDELETED BIGINT NOT NULL,
    PRIMARY KEY (PRINCIPAL_ID)
);

CREATE TABLE CMS_GROUPS (
    GROUP_ID VARCHAR(36) BINARY NOT NULL,
    PARENT_GROUP_ID VARCHAR(36) BINARY NOT NULL,
    GROUP_NAME VARCHAR(128) BINARY NOT NULL,
    GROUP_DESCRIPTION VARCHAR(255) NOT NULL,
    GROUP_FLAGS INT NOT NULL,
    GROUP_OU VARCHAR(128),
    PRIMARY KEY (GROUP_ID),
    UNIQUE INDEX GROUP_FQN_IDX (GROUP_OU, GROUP_NAME),
    INDEX GROUP_NAME_IDX (GROUP_NAME),
    INDEX GROUP_OU_IDX (GROUP_OU),
    INDEX PARENT_GROUP_ID_IDX (PARENT_GROUP_ID)
);

CREATE TABLE CMS_GROUPUSERS (
    GROUP_ID VARCHAR(36) BINARY NOT NULL,
    USER_ID VARCHAR(36) BINARY NOT NULL,
    GROUPUSER_FLAGS INT NOT NULL,
    PRIMARY KEY (GROUP_ID, USER_ID),
    INDEX GROUP_ID_IDX (GROUP_ID),
    INDEX USER_ID_IDX (USER_ID)
);

CREATE TABLE CMS_PROJECTS (
    PROJECT_ID VARCHAR(36) NOT NULL,
    PROJECT_NAME VARCHAR(200) BINARY NOT NULL,
    PROJECT_DESCRIPTION VARCHAR(255) NOT NULL,
    PROJECT_FLAGS INT NOT NULL,
    PROJECT_TYPE INT NOT NULL,
    USER_ID VARCHAR(36) BINARY NOT NULL,
    GROUP_ID VARCHAR(36) BINARY NOT NULL, 
    MANAGERGROUP_ID VARCHAR(36) BINARY NOT NULL,
    DATE_CREATED BIGINT NOT NULL,
    PROJECT_OU VARCHAR(128) NOT NULL,
    PRIMARY KEY (PROJECT_ID), 
    UNIQUE INDEX PROJECT_NAME_DATE_CREATED_IDX (PROJECT_OU, PROJECT_NAME, DATE_CREATED),
    INDEX PROJECT_FLAGS_IDX (PROJECT_FLAGS),
    INDEX PROJECT_GROUP_ID_IDX (GROUP_ID),
    INDEX PROJECT_MANAGERGROUP_ID_IDX (MANAGERGROUP_ID),
    INDEX PROJECT_OU_NAME_IDX (PROJECT_OU, PROJECT_NAME), 
    INDEX PROJECT_NAME_IDX (PROJECT_NAME),
    INDEX PROJECT_OU_IDX (PROJECT_OU),
    INDEX PROJECT_USER_ID_IDX (USER_ID)
);

CREATE TABLE CMS_HISTORY_PROJECTS (
    PROJECT_ID VARCHAR(36) NOT NULL,
    PROJECT_NAME VARCHAR(255) BINARY NOT NULL,
    PROJECT_DESCRIPTION VARCHAR(255) NOT NULL,
    PROJECT_TYPE INT NOT NULL,
    USER_ID VARCHAR(36) BINARY NOT NULL,
    GROUP_ID VARCHAR(36) BINARY NOT NULL,
    MANAGERGROUP_ID VARCHAR(36) BINARY NOT NULL,
    DATE_CREATED BIGINT NOT NULL,    
    PUBLISH_TAG INT NOT NULL,
    PROJECT_PUBLISHDATE BIGINT,
    PROJECT_PUBLISHED_BY VARCHAR(36) BINARY NOT NULL,
    PROJECT_OU VARCHAR(128) BINARY NOT NULL,
    PRIMARY KEY (PUBLISH_TAG)
);

CREATE TABLE CMS_PROJECTRESOURCES (
    PROJECT_ID VARCHAR(36) NOT NULL,
    RESOURCE_PATH BLOB NOT NULL,
    PRIMARY KEY (PROJECT_ID, RESOURCE_PATH(255)),
    INDEX RESOURCE_PATH_IDX (RESOURCE_PATH(255))
);

CREATE TABLE CMS_HISTORY_PROJECTRESOURCES (
    PUBLISH_TAG INT NOT NULL,
    PROJECT_ID VARCHAR(36) NOT NULL,
    RESOURCE_PATH BLOB NOT NULL,
    PRIMARY KEY (PUBLISH_TAG, PROJECT_ID, RESOURCE_PATH(255))
);

CREATE TABLE CMS_OFFLINE_PROPERTYDEF (
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL, 
    PROPERTYDEF_NAME VARCHAR(128) BINARY NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE INDEX PROPERTYDEF_NAME_IDX (PROPERTYDEF_NAME)
);

CREATE TABLE CMS_ONLINE_PROPERTYDEF (
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL, 
    PROPERTYDEF_NAME VARCHAR(128) BINARY NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE INDEX PROPERTYDEF_NAME_IDX (PROPERTYDEF_NAME)
);
                                        
CREATE TABLE CMS_HISTORY_PROPERTYDEF (
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL, 
    PROPERTYDEF_NAME VARCHAR(128) BINARY NOT NULL,
    PROPERTYDEF_TYPE INT NOT NULL,
    PRIMARY KEY (PROPERTYDEF_ID), 
    UNIQUE INDEX PROPERTYDEF_NAME_IDX (PROPERTYDEF_NAME)
);

CREATE TABLE CMS_OFFLINE_PROPERTIES (
    PROPERTY_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE BLOB NOT NULL,
    PRIMARY KEY (PROPERTY_ID),
    INDEX PROPERTYDEF_ID_IDX (PROPERTYDEF_ID),
    INDEX PROPERTY_MAPPING_ID_IDX (PROPERTY_MAPPING_ID),    
    UNIQUE INDEX PROPERTYDEF_ID_MAPPING_ID_IDX (PROPERTYDEF_ID, PROPERTY_MAPPING_ID)
);

CREATE TABLE CMS_ONLINE_PROPERTIES (
    PROPERTY_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE BLOB NOT NULL,
    PRIMARY KEY(PROPERTY_ID),
    INDEX PROPERTYDEF_ID_IDX (PROPERTYDEF_ID),
    INDEX PROPERTY_MAPPING_ID_IDX (PROPERTY_MAPPING_ID),    
    UNIQUE INDEX PROPERTYDEF_ID_MAPPING_ID_IDX (PROPERTYDEF_ID, PROPERTY_MAPPING_ID)
);
                                                                              
CREATE TABLE CMS_HISTORY_PROPERTIES (
    STRUCTURE_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTYDEF_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_ID VARCHAR(36) BINARY NOT NULL,
    PROPERTY_MAPPING_TYPE INT NOT NULL,
    PROPERTY_VALUE BLOB NOT NULL,
    PUBLISH_TAG INT,
    PRIMARY KEY (STRUCTURE_ID,PROPERTYDEF_ID,PROPERTY_MAPPING_TYPE,PUBLISH_TAG),
    INDEX PROPERTYDEF_ID_IDX (PROPERTYDEF_ID),
    INDEX PROPERTY_MAPPING_ID_IDX (PROPERTY_MAPPING_ID),    
    INDEX PROPERTYDEF_ID_MAPPING_ID_IDX (PROPERTYDEF_ID, PROPERTY_MAPPING_ID),
    INDEX VERSION_IDX (STRUCTURE_ID,PUBLISH_TAG)
);

CREATE TABLE CMS_ONLINE_ACCESSCONTROL (
    RESOURCE_ID VARCHAR(36) BINARY NOT NULL,
    PRINCIPAL_ID VARCHAR(36) BINARY NOT NULL,
    ACCESS_ALLOWED INT,
    ACCESS_DENIED INT,
    ACCESS_FLAGS INT,
    PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID),
    INDEX PRINCIPAL_ID_IDX (PRINCIPAL_ID),
    INDEX RESOURCE_ID_IDX (RESOURCE_ID)
);

CREATE TABLE CMS_OFFLINE_ACCESSCONTROL (
    RESOURCE_ID VARCHAR(36) BINARY NOT NULL,
    PRINCIPAL_ID VARCHAR(36) BINARY NOT NULL,
    ACCESS_ALLOWED INT,
    ACCESS_DENIED INT,
    ACCESS_FLAGS INT,
    PRIMARY KEY (RESOURCE_ID, PRINCIPAL_ID),
    INDEX PRINCIPAL_ID_IDX (PRINCIPAL_ID),
    INDEX RESOURCE_ID_IDX (RESOURCE_ID)
);

CREATE TABLE CMS_PUBLISH_HISTORY (
    HISTORY_ID VARCHAR(36) BINARY NOT NULL,
    PUBLISH_TAG INT NOT NULL,
    STRUCTURE_ID VARCHAR(36) BINARY NOT NULL,
    RESOURCE_ID VARCHAR(36) BINARY NOT NULL,
    RESOURCE_PATH BLOB NOT NULL,
    RESOURCE_STATE INT NOT NULL,
    RESOURCE_TYPE INT NOT NULL,