upgrade-0.7.0.derby.sql 6.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235
  1. --
  2. -- HIVE-417 Implement Indexing in Hive
  3. --
  4. CREATE TABLE "IDXS" (
  5. "INDEX_ID" BIGINT NOT NULL,
  6. "CREATE_TIME" INTEGER NOT NULL,
  7. "DEFERRED_REBUILD" CHAR(1) NOT NULL,
  8. "INDEX_HANDLER_CLASS" VARCHAR(256),
  9. "INDEX_NAME" VARCHAR(128),
  10. "INDEX_TBL_ID" BIGINT,
  11. "LAST_ACCESS_TIME" INTEGER NOT NULL,
  12. "ORIG_TBL_ID" BIGINT,
  13. "SD_ID" BIGINT);
  14. ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1"
  15. FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID")
  16. ON DELETE NO ACTION ON UPDATE NO ACTION;
  17. ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2"
  18. FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  19. ON DELETE NO ACTION ON UPDATE NO ACTION;
  20. ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3"
  21. FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  22. ON DELETE NO ACTION ON UPDATE NO ACTION;
  23. ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK"
  24. PRIMARY KEY ("INDEX_ID");
  25. ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK"
  26. CHECK (DEFERRED_REBUILD IN ('Y','N'));
  27. CREATE TABLE "INDEX_PARAMS" (
  28. "INDEX_ID" BIGINT NOT NULL,
  29. "PARAM_KEY" VARCHAR(256) NOT NULL,
  30. "PARAM_VALUE" VARCHAR(767));
  31. ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1"
  32. FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID")
  33. ON DELETE NO ACTION ON UPDATE NO ACTION;
  34. ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK"
  35. PRIMARY KEY ("INDEX_ID", "PARAM_KEY");
  36. CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID");
  37. --
  38. -- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs
  39. --
  40. CREATE TABLE "DATABASE_PARAMS" (
  41. "DB_ID" BIGINT NOT NULL,
  42. "PARAM_KEY" VARCHAR(180) NOT NULL,
  43. "PARAM_VALUE" VARCHAR(4000));
  44. ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1"
  45. FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
  46. ON DELETE NO ACTION ON UPDATE NO ACTION;
  47. ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK"
  48. PRIMARY KEY ("DB_ID", "PARAM_KEY");
  49. ALTER TABLE "DBS" DROP COLUMN "PARAMETERS";
  50. --
  51. -- HIVE-78 Authorization model for Hive
  52. --
  53. CREATE TABLE "DB_PRIVS" (
  54. "DB_GRANT_ID" BIGINT NOT NULL,
  55. "CREATE_TIME" INTEGER NOT NULL,
  56. "DB_ID" BIGINT,
  57. "GRANT_OPTION" SMALLINT NOT NULL,
  58. "GRANTOR" VARCHAR(128),
  59. "GRANTOR_TYPE" VARCHAR(128),
  60. "PRINCIPAL_NAME" VARCHAR(128),
  61. "PRINCIPAL_TYPE" VARCHAR(128),
  62. "DB_PRIV" VARCHAR(128));
  63. ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1"
  64. FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
  65. ON DELETE NO ACTION ON UPDATE NO ACTION;
  66. ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK"
  67. PRIMARY KEY ("DB_GRANT_ID");
  68. CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" (
  69. "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  70. "DB_PRIV", "GRANTOR", "GRANTOR_TYPE");
  71. CREATE TABLE "PART_COL_PRIVS" (
  72. "PART_COLUMN_GRANT_ID" BIGINT NOT NULL,
  73. "COLUMN_NAME" VARCHAR(128),
  74. "CREATE_TIME" INTEGER NOT NULL,
  75. "GRANT_OPTION" SMALLINT NOT NULL,
  76. "GRANTOR" VARCHAR(128),
  77. "GRANTOR_TYPE" VARCHAR(128),
  78. "PART_ID" BIGINT,
  79. "PRINCIPAL_NAME" VARCHAR(128),
  80. "PRINCIPAL_TYPE" VARCHAR(128),
  81. "PART_COL_PRIV" VARCHAR(128));
  82. ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1"
  83. FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
  84. ON DELETE NO ACTION ON UPDATE NO ACTION;
  85. ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK"
  86. PRIMARY KEY ("PART_COLUMN_GRANT_ID");
  87. CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" (
  88. "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  89. "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
  90. CREATE TABLE "PART_PRIVS" (
  91. "PART_GRANT_ID" BIGINT NOT NULL,
  92. "CREATE_TIME" INTEGER NOT NULL,
  93. "GRANT_OPTION" SMALLINT NOT NULL,
  94. "GRANTOR" VARCHAR(128),
  95. "GRANTOR_TYPE" VARCHAR(128),
  96. "PART_ID" BIGINT,
  97. "PRINCIPAL_NAME" VARCHAR(128),
  98. "PRINCIPAL_TYPE" VARCHAR(128),
  99. "PART_PRIV" VARCHAR(128));
  100. ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1"
  101. FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
  102. ON DELETE NO ACTION ON UPDATE NO ACTION;
  103. ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK"
  104. PRIMARY KEY ("PART_GRANT_ID");
  105. CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" (
  106. "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  107. "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");
  108. CREATE TABLE "ROLES" (
  109. "ROLE_ID" BIGINT NOT NULL,
  110. "CREATE_TIME" INTEGER NOT NULL,
  111. "OWNER_NAME" VARCHAR(128),
  112. "ROLE_NAME" VARCHAR(128));
  113. ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK"
  114. PRIMARY KEY ("ROLE_ID");
  115. CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME");
  116. CREATE TABLE "ROLE_MAP" (
  117. "ROLE_GRANT_ID" BIGINT NOT NULL,
  118. "ADD_TIME" INTEGER NOT NULL,
  119. "GRANT_OPTION" SMALLINT NOT NULL,
  120. "GRANTOR" VARCHAR(128),
  121. "GRANTOR_TYPE" VARCHAR(128),
  122. "PRINCIPAL_NAME" VARCHAR(128),
  123. "PRINCIPAL_TYPE" VARCHAR(128),
  124. "ROLE_ID" BIGINT);
  125. ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1"
  126. FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID")
  127. ON DELETE NO ACTION ON UPDATE NO ACTION;
  128. ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK"
  129. PRIMARY KEY ("ROLE_GRANT_ID");
  130. CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" (
  131. "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE");
  132. CREATE TABLE "TBL_COL_PRIVS" (
  133. "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL,
  134. "COLUMN_NAME" VARCHAR(128),
  135. "CREATE_TIME" INTEGER NOT NULL,
  136. "GRANT_OPTION" SMALLINT NOT NULL,
  137. "GRANTOR" VARCHAR(128),
  138. "GRANTOR_TYPE" VARCHAR(128),
  139. "PRINCIPAL_NAME" VARCHAR(128),
  140. "PRINCIPAL_TYPE" VARCHAR(128),
  141. "TBL_COL_PRIV" VARCHAR(128),
  142. "TBL_ID" BIGINT);
  143. ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1"
  144. FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  145. ON DELETE NO ACTION ON UPDATE NO ACTION;
  146. ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK"
  147. PRIMARY KEY ("TBL_COLUMN_GRANT_ID");
  148. CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" (
  149. "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  150. "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
  151. CREATE TABLE "TBL_PRIVS" (
  152. "TBL_GRANT_ID" BIGINT NOT NULL,
  153. "CREATE_TIME" INTEGER NOT NULL,
  154. "GRANT_OPTION" SMALLINT NOT NULL,
  155. "GRANTOR" VARCHAR(128),
  156. "GRANTOR_TYPE" VARCHAR(128),
  157. "PRINCIPAL_NAME" VARCHAR(128),
  158. "PRINCIPAL_TYPE" VARCHAR(128),
  159. "TBL_PRIV" VARCHAR(128),
  160. "TBL_ID" BIGINT);
  161. ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1"
  162. FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
  163. ON DELETE NO ACTION ON UPDATE NO ACTION;
  164. ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK"
  165. PRIMARY KEY ("TBL_GRANT_ID");
  166. CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" (
  167. "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
  168. "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");
  169. CREATE TABLE "GLOBAL_PRIVS" (
  170. "USER_GRANT_ID" BIGINT NOT NULL,
  171. "CREATE_TIME" INTEGER NOT NULL,
  172. "GRANT_OPTION" SMALLINT NOT NULL,
  173. "GRANTOR" VARCHAR(128),
  174. "GRANTOR_TYPE" VARCHAR(128),
  175. "PRINCIPAL_NAME" VARCHAR(128),
  176. "PRINCIPAL_TYPE" VARCHAR(128),
  177. "USER_PRIV" VARCHAR(128));
  178. ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK"
  179. PRIMARY KEY ("USER_GRANT_ID");
  180. CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" (
  181. "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV",
  182. "GRANTOR", "GRANTOR_TYPE");