--
-- HIVE-417 Implement Indexing in Hive
--

CREATE TABLE "IDXS" (
  "INDEX_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "DEFERRED_REBUILD" CHAR(1) NOT NULL,
  "INDEX_HANDLER_CLASS" VARCHAR(256),
  "INDEX_NAME" VARCHAR(128),
  "INDEX_TBL_ID" BIGINT,
  "LAST_ACCESS_TIME" INTEGER NOT NULL,
  "ORIG_TBL_ID" BIGINT,
  "SD_ID" BIGINT);

ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1"
  FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2"
  FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3"
  FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK"
  PRIMARY KEY ("INDEX_ID");

ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK"
  CHECK (DEFERRED_REBUILD IN ('Y','N'));


CREATE TABLE "INDEX_PARAMS" (
  "INDEX_ID" BIGINT NOT NULL,
  "PARAM_KEY" VARCHAR(256) NOT NULL,
  "PARAM_VALUE" VARCHAR(767));

ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1"
  FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK"
  PRIMARY KEY ("INDEX_ID", "PARAM_KEY");

CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID");


--
-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs
--
CREATE TABLE "DATABASE_PARAMS" (
  "DB_ID" BIGINT NOT NULL,
  "PARAM_KEY" VARCHAR(180) NOT NULL,
  "PARAM_VALUE" VARCHAR(4000));

ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1"
  FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK"
  PRIMARY KEY ("DB_ID", "PARAM_KEY");

ALTER TABLE "DBS" DROP COLUMN "PARAMETERS";

--
-- HIVE-78 Authorization model for Hive
--

CREATE TABLE "DB_PRIVS" (
  "DB_GRANT_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "DB_ID" BIGINT,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "DB_PRIV" VARCHAR(128));

ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1"
  FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK"
  PRIMARY KEY ("DB_GRANT_ID");

CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" (
  "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  "DB_PRIV", "GRANTOR", "GRANTOR_TYPE");


CREATE TABLE "PART_COL_PRIVS" (
  "PART_COLUMN_GRANT_ID" BIGINT NOT NULL,
  "COLUMN_NAME" VARCHAR(128),
  "CREATE_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PART_ID" BIGINT,
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "PART_COL_PRIV" VARCHAR(128));

ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1"
  FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK"
  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");
  
CREATE TABLE "PART_PRIVS" (
  "PART_GRANT_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PART_ID" BIGINT,
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "PART_PRIV" VARCHAR(128));

ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1"
  FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK"
  PRIMARY KEY ("PART_GRANT_ID");

CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" (
  "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");


CREATE TABLE "ROLES" (
  "ROLE_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "OWNER_NAME" VARCHAR(128),
  "ROLE_NAME" VARCHAR(128));
  
ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK"
  PRIMARY KEY ("ROLE_ID");

CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME");


CREATE TABLE "ROLE_MAP" (
  "ROLE_GRANT_ID" BIGINT NOT NULL,
  "ADD_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "ROLE_ID" BIGINT);  

ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1"
  FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK"
  PRIMARY KEY ("ROLE_GRANT_ID");

CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" (
  "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE");


CREATE TABLE "TBL_COL_PRIVS" (
  "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL,
  "COLUMN_NAME" VARCHAR(128),
  "CREATE_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "TBL_COL_PRIV" VARCHAR(128),
  "TBL_ID" BIGINT);

ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1"
  FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK"
  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");

  
CREATE TABLE "TBL_PRIVS" (
  "TBL_GRANT_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "TBL_PRIV" VARCHAR(128),
  "TBL_ID" BIGINT);

ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1"
  FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK"
  PRIMARY KEY ("TBL_GRANT_ID");

CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" (
  "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");

  
CREATE TABLE "GLOBAL_PRIVS" (
  "USER_GRANT_ID" BIGINT NOT NULL,
  "CREATE_TIME" INTEGER NOT NULL,
  "GRANT_OPTION" SMALLINT NOT NULL,
  "GRANTOR" VARCHAR(128),
  "GRANTOR_TYPE" VARCHAR(128),
  "PRINCIPAL_NAME" VARCHAR(128),
  "PRINCIPAL_TYPE" VARCHAR(128),
  "USER_PRIV" VARCHAR(128));

ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK"
  PRIMARY KEY ("USER_GRANT_ID");

CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" (
  "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV",
  "GRANTOR", "GRANTOR_TYPE");
