Repository: sentry Updated Branches: refs/heads/sentry-ha-redesign c94cfa6ed -> 774800343
SENTRY-1638: Update SQL script of MSentryPathChange table to add a column for notification ID (Kalyan Kalvagadda, reviewed by: Hao Hao, Alex Kolbasov and Na Li) UpdateD SQL script of MSentryPathChange table to add a column for notification ID. Tested it against derby, oracle, postgres, mysql and db2. Project: http://git-wip-us.apache.org/repos/asf/sentry/repo Commit: http://git-wip-us.apache.org/repos/asf/sentry/commit/77480034 Tree: http://git-wip-us.apache.org/repos/asf/sentry/tree/77480034 Diff: http://git-wip-us.apache.org/repos/asf/sentry/diff/77480034 Branch: refs/heads/sentry-ha-redesign Commit: 7748003430b94cc1b403690f9bb4d1e68d40ac86 Parents: c94cfa6 Author: Alexander Kolbasov <[email protected]> Authored: Thu Apr 13 14:52:45 2017 -0700 Committer: Alexander Kolbasov <[email protected]> Committed: Thu Apr 13 14:52:45 2017 -0700 ---------------------------------------------------------------------- .../provider/db/service/model/package.jdo | 1 + .../main/resources/007-SENTRY-1365.mysql.sql | 2 +- .../main/resources/008-SENTRY-1569.derby.sql | 4 ++++ .../main/resources/008-SENTRY-1569.mysql.sql | 4 ++++ .../main/resources/008-SENTRY-1569.oracle.sql | 3 +++ .../main/resources/008-SENTRY-1569.postgres.sql | 4 ++++ .../src/main/resources/sentry-db2-1.8.0.sql | 24 +++++++++++--------- .../src/main/resources/sentry-derby-1.8.0.sql | 4 +++- .../src/main/resources/sentry-mysql-1.8.0.sql | 4 ++++ .../src/main/resources/sentry-oracle-1.8.0.sql | 3 +++ .../main/resources/sentry-postgres-1.8.0.sql | 4 ++++ .../sentry-upgrade-db2-1.7.0-to-1.8.0.sql | 13 +++++++---- 12 files changed, 52 insertions(+), 18 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/java/org/apache/sentry/provider/db/service/model/package.jdo ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/java/org/apache/sentry/provider/db/service/model/package.jdo b/sentry-provider/sentry-provider-db/src/main/java/org/apache/sentry/provider/db/service/model/package.jdo index dfeb221..81a4c6e 100644 --- a/sentry-provider/sentry-provider-db/src/main/java/org/apache/sentry/provider/db/service/model/package.jdo +++ b/sentry-provider/sentry-provider-db/src/main/java/org/apache/sentry/provider/db/service/model/package.jdo @@ -288,6 +288,7 @@ </field> <field name="notificationID"> <column name="NOTIFICATION_ID" jdbc-type="BIGINT" allows-null="false"/> + <index name="NOTIFICATIONID" unique="true"/> </field> <field name ="pathChange"> <column name="PATH_CHANGE" length="4000" jdbc-type="VARCHAR" allows-null="false"/> http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/007-SENTRY-1365.mysql.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/007-SENTRY-1365.mysql.sql b/sentry-provider/sentry-provider-db/src/main/resources/007-SENTRY-1365.mysql.sql index c0973b8..1bcbffb 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/007-SENTRY-1365.mysql.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/007-SENTRY-1365.mysql.sql @@ -2,7 +2,7 @@ CREATE TABLE `AUTHZ_PATHS_MAPPING` ( `AUTHZ_OBJ_ID` BIGINT NOT NULL AUTO_INCREMENT, - `AUTHZ_OBJ_NAME` VARCHAR(384) BINARY NULL, + `AUTHZ_OBJ_NAME` VARCHAR(384) BINARY NOT NULL, `CREATE_TIME_MS` BIGINT NOT NULL, CONSTRAINT `AUTHZ_PATHS_MAPPING_PK` PRIMARY KEY (`AUTHZ_OBJ_ID`) ) ENGINE=INNODB; http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.derby.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.derby.sql b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.derby.sql index 84b7903..ba70715 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.derby.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.derby.sql @@ -12,8 +12,12 @@ ALTER TABLE SENTRY_PERM_CHANGE ADD CONSTRAINT SENTRY_PERM_CHANGE_PK PRIMARY KEY CREATE TABLE SENTRY_PATH_CHANGE ( CHANGE_ID BIGINT NOT NULL, + NOTIFICATION_ID BIGINT NOT NULL, CREATE_TIME_MS BIGINT NOT NULL, PATH_CHANGE VARCHAR(4000) NOT NULL ); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] ALTER TABLE SENTRY_PATH_CHANGE ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY (CHANGE_ID); + +CREATE UNIQUE INDEX NOTIFICATIONID ON SENTRY_PATH_CHANGE (NOTIFICATION_ID); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.mysql.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.mysql.sql b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.mysql.sql index 6986550..879e732 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.mysql.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.mysql.sql @@ -11,7 +11,11 @@ CREATE TABLE `SENTRY_PERM_CHANGE` CREATE TABLE `SENTRY_PATH_CHANGE` ( `CHANGE_ID` BIGINT NOT NULL, + `NOTIFICATION_ID` BIGINT NOT NULL, `CREATE_TIME_MS` BIGINT NOT NULL, `PATH_CHANGE` VARCHAR(4000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, CONSTRAINT `SENTRY_PATH_CHANGE_PK` PRIMARY KEY (`CHANGE_ID`) ) ENGINE=INNODB; + +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX `NOTIFICATIONID` ON `SENTRY_PATH_CHANGE` (`NOTIFICATION_ID`); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.oracle.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.oracle.sql b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.oracle.sql index 6a438ea..e83ab83 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.oracle.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.oracle.sql @@ -12,8 +12,11 @@ ALTER TABLE "SENTRY_PERM_CHANGE" ADD CONSTRAINT "SENTRY_PERM_CHANGE_PK" PRIMARY CREATE TABLE "SENTRY_PATH_CHANGE" ( "CHANGE_ID" NUMBER NOT NULL, + "NOTIFICATION_ID" NUMBER NOT NULL, "CREATE_TIME_MS" NUMBER NOT NULL, "PATH_CHANGE" VARCHAR2(4000) NOT NULL ); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX "NOTIFICATIONID" ON "SENTRY_PATH_CHANGE" ("NOTIFICATION_ID"); ALTER TABLE "SENTRY_PATH_CHANGE" ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY ("CHANGE_ID"); http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.postgres.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.postgres.sql b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.postgres.sql index a9b7e2c..c28099b 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.postgres.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/008-SENTRY-1569.postgres.sql @@ -11,7 +11,11 @@ CREATE TABLE "SENTRY_PERM_CHANGE" CREATE TABLE "SENTRY_PATH_CHANGE" ( "CHANGE_ID" bigint NOT NULL, + "NOTIFICATION_ID" bigint NOT NULL, "CREATE_TIME_MS" bigint NOT NULL, "PATH_CHANGE" VARCHAR(4000) NOT NULL, CONSTRAINT "SENTRY_PATH_CHANGE_PK" PRIMARY KEY ("CHANGE_ID") ); + +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX "NOTIFICATIONID" ON "SENTRY_PATH_CHANGE" ("NOTIFICATION_ID"); http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql index 037e6c3..841dcaa 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-1.8.0.sql @@ -207,22 +207,24 @@ ALTER TABLE AUTHZ_PATH FOREIGN KEY (AUTHZ_OBJ_ID) REFERENCES AUTHZ_PATHS_MAPPING (AUTHZ_OBJ_ID); -- Table SENTRY_PERM_CHANGE for classes [org.apache.sentry.provider.db.service.model.MSentryPermChange] -CREATE TABLE "SENTRY_PERM_CHANGE" +CREATE TABLE SENTRY_PERM_CHANGE ( - "CHANGE_ID" bigint NOT NULL, - "CREATE_TIME_MS" bigint NOT NULL, - "PERM_CHANGE" VARCHAR(4000) NOT NULL + CHANGE_ID bigint NOT NULL, + CREATE_TIME_MS bigint NOT NULL, + PERM_CHANGE VARCHAR(4000) NOT NULL ); -ALTER TABLE "SENTRY_PERM_CHANGE" ADD CONSTRAINT "SENTRY_PERM_CHANGE_PK" PRIMARY KEY ("CHANGE_ID"); +ALTER TABLE SENTRY_PERM_CHANGE ADD CONSTRAINT SENTRY_PERM_CHANGE_PK PRIMARY KEY (CHANGE_ID); -- Table SENTRY_PATH_CHANGE for classes [org.apache.sentry.provider.db.service.model.MSentryPathChange] -CREATE TABLE "SENTRY_PATH_CHANGE" +CREATE TABLE SENTRY_PATH_CHANGE ( - "CHANGE_ID" bigint NOT NULL, - "CREATE_TIME_MS" bigint NOT NULL, - "PATH_CHANGE" VARCHAR(4000) NOT NULL + CHANGE_ID bigint NOT NULL, + NOTIFICATION_ID bigint NOT NULL, + CREATE_TIME_MS bigint NOT NULL, + PATH_CHANGE VARCHAR(4000) NOT NULL ); -ALTER TABLE "SENTRY_PATH_CHANGE" ADD CONSTRAINT "SENTRY_PATH_CHANGE_PK" PRIMARY KEY ("CHANGE_ID"); -~ +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +ALTER TABLE SENTRY_PATH_CHANGE ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY (CHANGE_ID); +CREATE UNIQUE INDEX NOTIFICATIONID ON SENTRY_PATH_CHANGE (NOTIFICATION_ID); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql index 847c879..223835e 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-1.8.0.sql @@ -224,9 +224,11 @@ ALTER TABLE SENTRY_PERM_CHANGE ADD CONSTRAINT SENTRY_PERM_CHANGE_PK PRIMARY KEY CREATE TABLE SENTRY_PATH_CHANGE ( CHANGE_ID BIGINT NOT NULL, + NOTIFICATION_ID BIGINT NOT NULL, CREATE_TIME_MS BIGINT NOT NULL, PATH_CHANGE VARCHAR(4000) NOT NULL ); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] ALTER TABLE SENTRY_PATH_CHANGE ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY (CHANGE_ID); - +CREATE UNIQUE INDEX NOTIFICATIONID ON SENTRY_PATH_CHANGE (NOTIFICATION_ID); http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql index 78e318b..006d57b 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-1.8.0.sql @@ -261,7 +261,11 @@ CREATE TABLE `SENTRY_PERM_CHANGE` CREATE TABLE `SENTRY_PATH_CHANGE` ( `CHANGE_ID` BIGINT NOT NULL, + `NOTIFICATION_ID` BIGINT NOT NULL, `CREATE_TIME_MS` BIGINT NOT NULL, `PATH_CHANGE` VARCHAR(4000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, CONSTRAINT `SENTRY_PATH_CHANGE_PK` PRIMARY KEY (`CHANGE_ID`) ) ENGINE=INNODB; + +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX `NOTIFICATIONID` ON `SENTRY_PATH_CHANGE` (`NOTIFICATION_ID`); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql index e972bbf..20921ea 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-1.8.0.sql @@ -240,8 +240,11 @@ ALTER TABLE "SENTRY_PERM_CHANGE" ADD CONSTRAINT "SENTRY_PERM_CHANGE_PK" PRIMARY CREATE TABLE "SENTRY_PATH_CHANGE" ( "CHANGE_ID" NUMBER NOT NULL, + "NOTIFICATION_ID" NUMBER NOT NULL, "CREATE_TIME_MS" NUMBER NOT NULL, "PATH_CHANGE" VARCHAR2(4000) NOT NULL ); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX "NOTIFICATIONID" ON "SENTRY_PATH_CHANGE" ("NOTIFICATION_ID"); ALTER TABLE "SENTRY_PATH_CHANGE" ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY ("CHANGE_ID"); http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql index d2a4e0d..489ad66 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-1.8.0.sql @@ -249,9 +249,13 @@ CREATE TABLE "SENTRY_PERM_CHANGE" CREATE TABLE "SENTRY_PATH_CHANGE" ( "CHANGE_ID" bigint NOT NULL, + "NOTIFICATION_ID" bigint NOT NULL, "CREATE_TIME_MS" bigint NOT NULL, "PATH_CHANGE" VARCHAR(4000) NOT NULL, CONSTRAINT "SENTRY_PATH_CHANGE_PK" PRIMARY KEY ("CHANGE_ID") ); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +CREATE UNIQUE INDEX "NOTIFICATIONID" ON "SENTRY_PATH_CHANGE" ("NOTIFICATION_ID"); + COMMIT; http://git-wip-us.apache.org/repos/asf/sentry/blob/77480034/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql ---------------------------------------------------------------------- diff --git a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql index 7c3db17..ec50912 100644 --- a/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql +++ b/sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-1.7.0-to-1.8.0.sql @@ -63,14 +63,17 @@ CREATE TABLE "SENTRY_PERM_CHANGE" ALTER TABLE "SENTRY_PERM_CHANGE" ADD CONSTRAINT "SENTRY_PERM_CHANGE_PK" PRIMARY KEY ("CHANGE_ID"); -- Table `SENTRY_PATH_CHANGE` for classes [org.apache.sentry.provider.db.service.model.MSentryPathChange] -CREATE TABLE "SENTRY_PATH_CHANGE" +CREATE TABLE SENTRY_PATH_CHANGE ( - "CHANGE_ID" bigint NOT NULL, - "CREATE_TIME_MS" bigint NOT NULL, - "PATH_CHANGE" VARCHAR(4000) NOT NULL + CHANGE_ID bigint NOT NULL, + NOTIFICATION_ID bigint NOT NULL, + CREATE_TIME_MS bigint NOT NULL, + PATH_CHANGE VARCHAR(4000) NOT NULL ); -ALTER TABLE "SENTRY_PATH_CHANGE" ADD CONSTRAINT "SENTRY_PATH_CHANGE_PK" PRIMARY KEY ("CHANGE_ID"); +-- Constraints for table SENTRY_PATH_CHANGE for class [org.apache.sentry.provider.db.service.model.MSentryPathChange] +ALTER TABLE SENTRY_PATH_CHANGE ADD CONSTRAINT SENTRY_PATH_CHANGE_PK PRIMARY KEY (CHANGE_ID); +CREATE UNIQUE INDEX NOTIFICATIONID ON SENTRY_PATH_CHANGE (NOTIFICATION_ID); -- Version update UPDATE SENTRY_VERSION SET SCHEMA_VERSION='1.8.0', VERSION_COMMENT='Sentry release version 1.8.0' WHERE VER_ID=1;
