--
-- 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");
