sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.4.0-to-1.5.0.sql

-- SENTRY-327
ALTER TABLE SENTRY_DB_PRIVILEGE ADD WITH_GRANT_OPTION CHAR(1) NOT NULL;

-- SENTRY-339
DROP INDEX SENTRYPRIVILEGENAME;
CREATE UNIQUE INDEX SENTRYPRIVILEGENAME ON SENTRY_DB_PRIVILEGE ("SERVER_NAME",DB_NAME,"TABLE_NAME",URI,"ACTION",WITH_GRANT_OPTION);
ALTER TABLE SENTRY_DB_PRIVILEGE DROP PRIVILEGE_NAME;

-- SENTRY-380
ALTER TABLE `SENTRY_DB_PRIVILEGE` DROP `GRANTOR_PRINCIPAL`;
ALTER TABLE `SENTRY_ROLE` DROP `GRANTOR_PRINCIPAL`;
ALTER TABLE `SENTRY_GROUP` DROP `GRANTOR_PRINCIPAL`;

ALTER TABLE `SENTRY_ROLE_DB_PRIVILEGE_MAP` ADD `GRANTOR_PRINCIPAL` VARCHAR(128);
ALTER TABLE `SENTRY_ROLE_GROUP_MAP` ADD `GRANTOR_PRINCIPAL` VARCHAR(128);

-- SENTRY-74
ALTER TABLE `SENTRY_DB_PRIVILEGE` ADD `COLUMN_NAME` CHAR(4000);
DROP INDEX SENTRYPRIVILEGENAME;
CREATE UNIQUE INDEX SENTRYPRIVILEGENAME ON SENTRY_DB_PRIVILEGE ("SERVER_NAME",DB_NAME,"TABLE_NAME","COLUMN_NAME",URI,"ACTION",WITH_GRANT_OPTION);

-- SENTRY-398
CREATE TABLE SENTRY_GM_PRIVILEGE
(
    GM_PRIVILEGE_ID BIGINT NOT NULL,
    "ACTION" VARCHAR(40),
    COMPONENT_NAME VARCHAR(400),
    CREATE_TIME BIGINT NOT NULL,
    WITH_GRANT_OPTION CHAR(1),
    RESOURCE_NAME_0 VARCHAR(400),
    RESOURCE_NAME_1 VARCHAR(400),
    RESOURCE_NAME_2 VARCHAR(400),
    RESOURCE_NAME_3 VARCHAR(400),
    RESOURCE_TYPE_0 VARCHAR(400),
    RESOURCE_TYPE_1 VARCHAR(400),
    RESOURCE_TYPE_2 VARCHAR(400),
    RESOURCE_TYPE_3 VARCHAR(400),
    "SCOPE" VARCHAR(40),
    SERVICE_NAME VARCHAR(400)
);
ALTER TABLE SENTRY_GM_PRIVILEGE ADD CONSTRAINT SENTRY_GM_PRIVILEGE_PK PRIMARY KEY (GM_PRIVILEGE_ID);

CREATE UNIQUE INDEX GM_PRIVILEGE_INDEX ON SENTRY_GM_PRIVILEGE (COMPONENT_NAME,SERVICE_NAME,RESOURCE_NAME_0,RESOURCE_TYPE_0,RESOURCE_NAME_1,RESOURCE_TYPE_1,RESOURCE_NAME_2,RESOURCE_TYPE_2,RESOURCE_NAME_3,RESOURCE_TYPE_3,"ACTION",WITH_GRANT_OPTION);

CREATE TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP
(
    ROLE_ID BIGINT NOT NULL,
    GM_PRIVILEGE_ID BIGINT NOT NULL
);
ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_PK PRIMARY KEY (ROLE_ID,GM_PRIVILEGE_ID);

CREATE INDEX SENTRY_ROLE_GM_PRIVILEGE_MAP_N50 ON SENTRY_ROLE_GM_PRIVILEGE_MAP (ROLE_ID);

CREATE INDEX SENTRY_ROLE_GM_PRIVILEGE_MAP_N49 ON SENTRY_ROLE_GM_PRIVILEGE_MAP (GM_PRIVILEGE_ID);

ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK2 FOREIGN KEY (GM_PRIVILEGE_ID) REFERENCES SENTRY_GM_PRIVILEGE (GM_PRIVILEGE_ID);

ALTER TABLE SENTRY_ROLE_GM_PRIVILEGE_MAP ADD CONSTRAINT SENTRY_ROLE_GM_PRIVILEGE_MAP_FK1 FOREIGN KEY (ROLE_ID) REFERENCES SENTRY_ROLE (ROLE_ID);

-- Version update
UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.5.0', VERSION_COMMENT='Sentry release version 1.5.0' WHERE VER_ID=1;