metastore/scripts/upgrade/postgres/upgrade-0.6.0-to-0.7.0.postgres.sql

SELECT 'Upgrading MetaStore schema from 0.6.0 to 0.7.0';

-- 005-HIVE-417.postgres.sql
SELECT '< HIVE-417 Implement Indexing in Hive >';
CREATE TABLE "IDXS" (
  "INDEX_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "DEFERRED_REBUILD" boolean NOT NULL,
  "INDEX_HANDLER_CLASS" character varying(256) DEFAULT NULL,
  "INDEX_NAME" character varying(128) DEFAULT NULL,
  "INDEX_TBL_ID" bigint DEFAULT NULL,
  "LAST_ACCESS_TIME" bigint NOT NULL,
  "ORIG_TBL_ID" bigint DEFAULT NULL,
  "SD_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("INDEX_ID"),
  CONSTRAINT "UNIQUEINDEX" UNIQUE ("INDEX_NAME", "ORIG_TBL_ID")
);
CREATE INDEX "IDXS_FK1" on "IDXS" ("SD_ID");
CREATE INDEX "IDXS_FK2" on "IDXS" ("INDEX_TBL_ID");
CREATE INDEX "IDXS_FK3" on "IDXS" ("ORIG_TBL_ID");

--
-- Table: INDEX_PARAMS
--
CREATE TABLE "INDEX_PARAMS" (
  "INDEX_ID" bigint NOT NULL,
  "PARAM_KEY" character varying(256) NOT NULL,
  "PARAM_VALUE" character varying(767) DEFAULT NULL,
  PRIMARY KEY ("INDEX_ID", "PARAM_KEY")
);

--
-- Foreign Key Definitions
--

ALTER TABLE "IDXS" ADD FOREIGN KEY ("SD_ID")
  REFERENCES "SDS" ("SD_ID") DEFERRABLE;

ALTER TABLE "IDXS" ADD FOREIGN KEY ("INDEX_TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "IDXS" ADD FOREIGN KEY ("ORIG_TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "INDEX_PARAMS" ADD FOREIGN KEY ("INDEX_ID")
  REFERENCES "IDXS" ("INDEX_ID") DEFERRABLE;

  -- 006-HIVE-1823.postgres.sql
SELECT '< HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs >';

CREATE TABLE "DATABASE_PARAMS" (
  "DB_ID" bigint NOT NULL,
  "PARAM_KEY" character varying(180) NOT NULL,
  "PARAM_VALUE" character varying(4000) DEFAULT NULL,
  PRIMARY KEY ("DB_ID", "PARAM_KEY")
);

--
-- Foreign Key Definitions
--

ALTER TABLE "DATABASE_PARAMS" ADD FOREIGN KEY ("DB_ID")
  REFERENCES "DBS" ("DB_ID") DEFERRABLE;
  
  
-- 007-HIVE-78.postgres.sql
SELECT '< HIVE-78 Authorization model for Hive >';

CREATE TABLE "ROLES" (
  "ROLE_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "OWNER_NAME" character varying(128) DEFAULT NULL,
  "ROLE_NAME" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("ROLE_ID"),
  CONSTRAINT "ROLEENTITYINDEX" UNIQUE ("ROLE_NAME")
);

--
-- Table: ROLE_MAP
--
CREATE TABLE "ROLE_MAP" (
  "ROLE_GRANT_ID" bigint NOT NULL,
  "ADD_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "ROLE_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("ROLE_GRANT_ID"),
  CONSTRAINT "USERROLEMAPINDEX" UNIQUE ("PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: GLOBAL_PRIVS
--
CREATE TABLE "GLOBAL_PRIVS" (
  "USER_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "USER_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("USER_GRANT_ID"),
  CONSTRAINT "GLOBALPRIVILEGEINDEX" UNIQUE ("PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: DB_PRIVS
--
CREATE TABLE "DB_PRIVS" (
  "DB_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "DB_ID" bigint DEFAULT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "DB_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("DB_GRANT_ID"),
  CONSTRAINT "DBPRIVILEGEINDEX" UNIQUE ("DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "DB_PRIV", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: TBL_PRIVS
--
CREATE TABLE "TBL_PRIVS" (
  "TBL_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "TBL_PRIV" character varying(128) DEFAULT NULL,
  "TBL_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("TBL_GRANT_ID")
);
CREATE INDEX "TABLEPRIVILEGEINDEX" on "TBL_PRIVS" ("TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: TBL_COL_PRIVS
--
CREATE TABLE "TBL_COL_PRIVS" (
  "TBL_COLUMN_GRANT_ID" bigint NOT NULL,
  "COLUMN_NAME" character varying(128) DEFAULT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "TBL_COL_PRIV" character varying(128) DEFAULT NULL,
  "TBL_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("TBL_COLUMN_GRANT_ID")
);
CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" on "TBL_COL_PRIVS" ("TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: PART_PRIVS
--
CREATE TABLE "PART_PRIVS" (
  "PART_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PART_ID" bigint DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "PART_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("PART_GRANT_ID")
);
CREATE INDEX "PARTPRIVILEGEINDEX" on "PART_PRIVS" ("PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: PART_COL_PRIVS
--
CREATE TABLE "PART_COL_PRIVS" (
  "PART_COLUMN_GRANT_ID" bigint NOT NULL,
  "COLUMN_NAME" character varying(128) DEFAULT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PART_ID" bigint DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "PART_COL_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("PART_COLUMN_GRANT_ID")
);
CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" on "PART_COL_PRIVS" ("PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Foreign Key Definitions
--

ALTER TABLE "ROLE_MAP" ADD FOREIGN KEY ("ROLE_ID")
  REFERENCES "ROLES" ("ROLE_ID") DEFERRABLE;

ALTER TABLE "DB_PRIVS" ADD FOREIGN KEY ("DB_ID")
  REFERENCES "DBS" ("DB_ID") DEFERRABLE;

ALTER TABLE "TBL_PRIVS" ADD FOREIGN KEY ("TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "TBL_COL_PRIVS" ADD FOREIGN KEY ("TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "PART_PRIVS" ADD FOREIGN KEY ("PART_ID")
  REFERENCES "PARTITIONS" ("PART_ID") DEFERRABLE;

ALTER TABLE "PART_COL_PRIVS" ADD FOREIGN KEY ("PART_ID")
  REFERENCES "PARTITIONS" ("PART_ID") DEFERRABLE;
  
SELECT 'Finished upgrading MetaStore schema from 0.6.0 to 0.7.0';