text: -- Upgrade MetaStore schema from 3.2.0 to 4.0.0
  -- HIVE-19416
  ALTER TABLE "APP"."TBLS" ADD WRITE_ID bigint DEFAULT 0;
  ALTER TABLE "APP"."PARTITIONS" ADD WRITE_ID bigint DEFAULT 0;
  
  -- HIVE-20793
  ALTER TABLE "APP"."WM_RESOURCEPLAN" ADD NS VARCHAR(128);
  UPDATE "APP"."WM_RESOURCEPLAN" SET NS = 'default' WHERE NS IS NULL;
  DROP INDEX "APP"."UNIQUE_WM_RESOURCEPLAN";
  CREATE UNIQUE INDEX "APP"."UNIQUE_WM_RESOURCEPLAN" ON "APP"."WM_RESOURCEPLAN" ("NS", "NAME");
  
  -- HIVE-21063
  CREATE UNIQUE INDEX "APP"."NOTIFICATION_LOG_EVENT_ID" ON "APP"."NOTIFICATION_LOG" ("EVENT_ID");
  
  -- HIVE-22046 (DEFAULT HIVE)
  ALTER TABLE "APP"."TAB_COL_STATS" ADD ENGINE VARCHAR(128);
  UPDATE "APP"."TAB_COL_STATS" SET ENGINE = 'hive' WHERE ENGINE IS NULL;
  ALTER TABLE "APP"."PART_COL_STATS" ADD ENGINE VARCHAR(128);
  UPDATE "APP"."PART_COL_STATS" SET ENGINE = 'hive' WHERE ENGINE IS NULL;
  
  CREATE TABLE "APP"."SCHEDULED_QUERIES" (
    "SCHEDULED_QUERY_ID" bigint primary key not null,
    "SCHEDULE_NAME" varchar(256) not null,
    "ENABLED" CHAR(1) NOT NULL DEFAULT 'N',
    "CLUSTER_NAMESPACE" varchar(256) not null,
    "USER" varchar(128) not null,
    "SCHEDULE" varchar(256) not null,
    "QUERY" varchar(4000) not null,
    "NEXT_EXECUTION" integer not null
  );
  
  CREATE INDEX NEXTEXECUTIONINDEX ON APP.SCHEDULED_QUERIES (ENABLED,CLUSTER_NAMESPACE,NEXT_EXECUTION);
  CREATE UNIQUE INDEX UNIQUE_SCHEDULED_QUERIES_NAME ON APP.SCHEDULED_QUERIES (SCHEDULE_NAME,CLUSTER_NAMESPACE);
  
  CREATE TABLE APP.SCHEDULED_EXECUTIONS (
          SCHEDULED_EXECUTION_ID bigint primary key not null,
          EXECUTOR_QUERY_ID VARCHAR(256),
          SCHEDULED_QUERY_ID bigint not null,
          START_TIME integer not null,
          END_TIME INTEGER,
          LAST_UPDATE_TIME INTEGER,
          ERROR_MESSAGE VARCHAR(2000),
          STATE VARCHAR(256),
          CONSTRAINT SCHEDULED_EXECUTIONS_SCHQ_FK FOREIGN KEY (SCHEDULED_QUERY_ID) REFERENCES APP.SCHEDULED_QUERIES(SCHEDULED_QUERY_ID) ON DELETE CASCADE
  );
  
  CREATE INDEX LASTUPDATETIMEINDEX ON APP.SCHEDULED_EXECUTIONS (LAST_UPDATE_TIME);
  CREATE INDEX SCHEDULED_EXECUTIONS_SCHQID ON APP.SCHEDULED_EXECUTIONS (SCHEDULED_QUERY_ID);
  CREATE UNIQUE INDEX SCHEDULED_EXECUTIONS_UNIQUE_ID ON APP.SCHEDULED_EXECUTIONS (SCHEDULED_EXECUTION_ID);
  
  -- HIVE-22729
  ALTER TABLE COMPACTION_QUEUE ADD CQ_ERROR_MESSAGE clob;
  ALTER TABLE COMPLETED_COMPACTIONS ADD CC_ERROR_MESSAGE clob;
  
  -- HIVE-22728
  ALTER TABLE "APP"."KEY_CONSTRAINTS" DROP CONSTRAINT "CONSTRAINTS_PK";
  ALTER TABLE "APP"."KEY_CONSTRAINTS" ADD CONSTRAINT "CONSTRAINTS_PK" PRIMARY KEY ("PARENT_TBL_ID", "CONSTRAINT_NAME", "POSITION");
  
  -- HIVE-21487
  CREATE INDEX COMPLETED_COMPACTIONS_RES ON COMPLETED_COMPACTIONS (CC_DATABASE,CC_TABLE,CC_PARTITION);
  -- HIVE-22872
  ALTER TABLE "SCHEDULED_QUERIES" ADD "ACTIVE_EXECUTION_ID" bigint;
  
  -- This needs to be the last thing done.  Insert any changes above this line.
  UPDATE "APP".VERSION SET SCHEMA_VERSION='4.0.0', VERSION_COMMENT='Hive release version 4.0.0' where VER_ID=1;