metastore/scripts/upgrade/postgres/005-HIVE-417.postgres.sql

SELECT '< HIVE-417 Implement Indexing in Hive >';

--
-- Table: IDXS
--
CREATE TABLE "IDXS" (
  "INDEX_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "DEFERRED_REBUILD" bit(1) 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;