This is an automated email from the ASF dual-hosted git repository.
dkuzmenko pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new b8f243bfcf5 HIVE-26324: Add one-row-table constraints on
NOTIFICATION_SEQUENCE table (Sourabh Badhya, reviewed by Denys Kuzmenko)
b8f243bfcf5 is described below
commit b8f243bfcf558d6257b62b4f4d0961eb85b3fce5
Author: Sourabh Badhya <[email protected]>
AuthorDate: Fri Jun 17 13:26:58 2022 +0530
HIVE-26324: Add one-row-table constraints on NOTIFICATION_SEQUENCE table
(Sourabh Badhya, reviewed by Denys Kuzmenko)
Closes #3369
---
.../src/main/sql/derby/hive-schema-4.0.0-alpha-2.derby.sql | 2 ++
.../sql/derby/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.derby.sql | 3 +++
.../src/main/sql/mssql/hive-schema-4.0.0-alpha-2.mssql.sql | 2 ++
.../sql/mssql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mssql.sql | 3 +++
.../src/main/sql/mysql/hive-schema-4.0.0-alpha-2.mysql.sql | 4 +++-
.../sql/mysql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mysql.sql | 3 +++
.../src/main/sql/oracle/hive-schema-4.0.0-alpha-2.oracle.sql | 2 ++
.../sql/oracle/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.oracle.sql | 3 +++
.../src/main/sql/postgres/hive-schema-4.0.0-alpha-2.postgres.sql | 8 ++++++++
.../postgres/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.postgres.sql | 3 +++
10 files changed, 32 insertions(+), 1 deletion(-)
diff --git
a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-alpha-2.derby.sql
b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-alpha-2.derby.sql
index 949c144c86b..2b622d21821 100644
---
a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-alpha-2.derby.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-alpha-2.derby.sql
@@ -405,6 +405,8 @@ ALTER TABLE "APP"."NOTIFICATION_LOG" ADD CONSTRAINT
"NOTIFICATION_LOG_PK" PRIMAR
ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT
"NOTIFICATION_SEQUENCE_PK" PRIMARY KEY ("NNI_ID");
+ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT"
CHECK (NNI_ID = 1);
+
ALTER TABLE "APP"."KEY_CONSTRAINTS" ADD CONSTRAINT "CONSTRAINTS_PK" PRIMARY
KEY ("PARENT_TBL_ID", "CONSTRAINT_NAME", "POSITION");
ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_KEY_PK"
PRIMARY KEY ("PROPERTY_KEY");
diff --git
a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.derby.sql
b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.derby.sql
index bf17bd33380..5fdb1718e65 100644
---
a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.derby.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.derby.sql
@@ -5,5 +5,8 @@ ALTER TABLE COMPLETED_COMPACTIONS ADD CC_NEXT_TXN_ID bigint;
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_TXN_ID bigint;
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_COMMIT_TIME bigint;
+-- HIVE-26324
+ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT"
CHECK (NNI_ID = 1);
+
-- This needs to be the last thing done. Insert any changes above this line.
UPDATE "APP".VERSION SET SCHEMA_VERSION='4.0.0-alpha-2', VERSION_COMMENT='Hive
release version 4.0.0-alpha-2' where VER_ID=1;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.0.0-alpha-2.mssql.sql
b/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.0.0-alpha-2.mssql.sql
index b12ab46fc4c..69de0ca5214 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.0.0-alpha-2.mssql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mssql/hive-schema-4.0.0-alpha-2.mssql.sql
@@ -667,6 +667,8 @@ CREATE TABLE NOTIFICATION_SEQUENCE
ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT NOTIFICATION_SEQUENCE_PK
PRIMARY KEY (NNI_ID);
+ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1);
+
INSERT INTO NOTIFICATION_SEQUENCE (NNI_ID, NEXT_EVENT_ID) SELECT 1,1 WHERE NOT
EXISTS (SELECT NEXT_EVENT_ID FROM NOTIFICATION_SEQUENCE);
-- Tables to manage resource plans.
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mssql.sql
b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mssql.sql
index 7dd16b77dfe..d521dcec2cf 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mssql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mssql.sql
@@ -5,6 +5,9 @@ ALTER TABLE COMPLETED_COMPACTIONS ADD CC_NEXT_TXN_ID bigint
NULL;
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_TXN_ID bigint NULL;
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_COMMIT_TIME bigint NULL;
+-- HIVE-26324
+ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1);
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.0.0-alpha-2', VERSION_COMMENT='Hive
release version 4.0.0-alpha-2' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.0.0-alpha-1 to
4.0.0-alpha-2' AS MESSAGE;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-alpha-2.mysql.sql
b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-alpha-2.mysql.sql
index 2bbe7b1445b..0e97a89f4a9 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-alpha-2.mysql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-alpha-2.mysql.sql
@@ -892,7 +892,9 @@ CREATE TABLE IF NOT EXISTS `NOTIFICATION_SEQUENCE`
PRIMARY KEY (`NNI_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-INSERT INTO `NOTIFICATION_SEQUENCE` (`NNI_ID`, `NEXT_EVENT_ID`) SELECT * from
(select 1 as `NNI_ID`, 1 as `NOTIFICATION_SEQUENCE`) a WHERE (SELECT COUNT(*)
FROM `NOTIFICATION_SEQUENCE`) = 0;
+ALTER TABLE `NOTIFICATION_SEQUENCE` MODIFY COLUMN `NNI_ID` BIGINT(20)
GENERATED ALWAYS AS (1) STORED NOT NULL;
+
+INSERT INTO `NOTIFICATION_SEQUENCE` (`NEXT_EVENT_ID`) SELECT * from (select 1
as `NOTIFICATION_SEQUENCE`) a WHERE (SELECT COUNT(*) FROM
`NOTIFICATION_SEQUENCE`) = 0;
CREATE TABLE IF NOT EXISTS `KEY_CONSTRAINTS`
(
diff --git
a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mysql.sql
b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mysql.sql
index 09eace89b49..882d17f4b9e 100644
---
a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mysql.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.mysql.sql
@@ -5,6 +5,9 @@ ALTER TABLE `COMPLETED_COMPACTIONS` ADD COLUMN `CC_NEXT_TXN_ID`
bigint;
ALTER TABLE `COMPLETED_COMPACTIONS` ADD COLUMN `CC_TXN_ID` bigint;
ALTER TABLE `COMPLETED_COMPACTIONS` ADD COLUMN `CC_COMMIT_TIME` bigint;
+-- HIVE-26324
+ALTER TABLE `NOTIFICATION_SEQUENCE` MODIFY COLUMN `NNI_ID` INT GENERATED
ALWAYS AS (1) STORED NOT NULL;
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.0.0-alpha-2', VERSION_COMMENT='Hive
release version 4.0.0-alpha-2' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.0.0-alpha-1 to
4.0.0-alpha-2' AS MESSAGE;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.0.0-alpha-2.oracle.sql
b/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.0.0-alpha-2.oracle.sql
index 29d6f0ffddf..b92c0bebb3d 100644
---
a/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.0.0-alpha-2.oracle.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/oracle/hive-schema-4.0.0-alpha-2.oracle.sql
@@ -672,6 +672,8 @@ CREATE TABLE NOTIFICATION_SEQUENCE
ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT NOTIFICATION_SEQUENCE_PK
PRIMARY KEY (NNI_ID);
+ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1);
+
INSERT INTO NOTIFICATION_SEQUENCE (NNI_ID, NEXT_EVENT_ID) SELECT 1,1 FROM DUAL
WHERE NOT EXISTS ( SELECT NEXT_EVENT_ID FROM NOTIFICATION_SEQUENCE);
-- Tables to manage resource plans.
diff --git
a/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.oracle.sql
b/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.oracle.sql
index c0fd8f231d3..f694afefe10 100644
---
a/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.oracle.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.oracle.sql
@@ -5,6 +5,9 @@ ALTER TABLE COMPLETED_COMPACTIONS ADD CC_NEXT_TXN_ID NUMBER(19);
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_TXN_ID NUMBER(19);
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_COMMIT_TIME NUMBER(19);
+-- HIVE-26324
+ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK
(NNI_ID = 1);
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.0.0-alpha-2', VERSION_COMMENT='Hive
release version 4.0.0-alpha-2' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 4.0.0-alpha-1 to
4.0.0-alpha-2' AS Status from dual;
diff --git
a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-alpha-2.postgres.sql
b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-alpha-2.postgres.sql
index b72b8d5ca0f..29538cfe3d7 100644
---
a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-alpha-2.postgres.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-alpha-2.postgres.sql
@@ -846,6 +846,14 @@ ALTER TABLE ONLY "INDEX_PARAMS"
ADD CONSTRAINT "INDEX_PARAMS_pkey" PRIMARY KEY ("INDEX_ID", "PARAM_KEY");
+--
+-- Name: ONE_ROW_CONSTRAINT; Type: CONSTRAINT; Schema: public; Owner:
hiveuser; Tablespace:
+--
+
+ALTER TABLE "NOTIFICATION_SEQUENCE"
+ ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK ("NNI_ID" = 1);
+
+
--
-- Name: NUCLEUS_TABLES_pkey; Type: CONSTRAINT; Schema: public; Owner:
hiveuser; Tablespace:
--
diff --git
a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.postgres.sql
b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.postgres.sql
index 2dad36782f3..09699b81116 100644
---
a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.postgres.sql
+++
b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.postgres.sql
@@ -5,6 +5,9 @@ ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_NEXT_TXN_ID" bigint;
ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_TXN_ID" bigint;
ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_COMMIT_TIME" bigint;
+-- HIVE-26324
+ALTER TABLE "NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK
("NNI_ID" = 1);
+
-- These lines need to be last. Insert any changes above.
UPDATE "VERSION" SET "SCHEMA_VERSION"='4.0.0-alpha-2', "VERSION_COMMENT"='Hive
release version 4.0.0-alpha-2' where "VER_ID"=1;
SELECT 'Finished upgrading MetaStore schema from 4.0.0-alpha-1 to
4.0.0-alpha-2';
