upgrade-0.7.0.mysql.sql 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
  1. --
  2. -- HIVE-417 Implement Indexing in Hive
  3. --
  4. CREATE TABLE IF NOT EXISTS `IDXS` (
  5. `INDEX_ID` bigint(20) NOT NULL,
  6. `CREATE_TIME` int(11) NOT NULL,
  7. `DEFERRED_REBUILD` bit(1) NOT NULL,
  8. `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  9. `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  10. `INDEX_TBL_ID` bigint(20) DEFAULT NULL,
  11. `LAST_ACCESS_TIME` int(11) NOT NULL,
  12. `ORIG_TBL_ID` bigint(20) DEFAULT NULL,
  13. `SD_ID` bigint(20) DEFAULT NULL,
  14. PRIMARY KEY (`INDEX_ID`),
  15. UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`),
  16. KEY `IDXS_FK1` (`SD_ID`),
  17. KEY `IDXS_FK2` (`INDEX_TBL_ID`),
  18. KEY `IDXS_FK3` (`ORIG_TBL_ID`),
  19. CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
  20. CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
  21. CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  23. CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` (
  24. `INDEX_ID` bigint(20) NOT NULL,
  25. `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  26. `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  27. PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`),
  28. CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`)
  29. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  30. --
  31. -- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs
  32. --
  33. CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` (
  34. `DB_ID` bigint(20) NOT NULL,
  35. `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  36. `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  37. PRIMARY KEY (`DB_ID`,`PARAM_KEY`),
  38. CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
  39. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  40. ALTER TABLE `DBS` DROP COLUMN `PARAMETERS`;
  41. --
  42. -- HIVE-78 Authorization model for Hive
  43. --
  44. CREATE TABLE IF NOT EXISTS `DB_PRIVS` (
  45. `DB_GRANT_ID` bigint(20) NOT NULL,
  46. `CREATE_TIME` int(11) NOT NULL,
  47. `DB_ID` bigint(20) DEFAULT NULL,
  48. `GRANT_OPTION` smallint(6) NOT NULL,
  49. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  50. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  51. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  52. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  53. `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  54. PRIMARY KEY (`DB_GRANT_ID`),
  55. UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
  56. CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
  57. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  58. CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` (
  59. `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL,
  60. `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  61. `CREATE_TIME` int(11) NOT NULL,
  62. `GRANT_OPTION` smallint(6) NOT NULL,
  63. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  64. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  65. `PART_ID` bigint(20) DEFAULT NULL,
  66. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  67. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  68. `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  69. PRIMARY KEY (`PART_COLUMN_GRANT_ID`),
  70. KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
  71. CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
  72. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  73. CREATE TABLE IF NOT EXISTS `PART_PRIVS` (
  74. `PART_GRANT_ID` bigint(20) NOT NULL,
  75. `CREATE_TIME` int(11) NOT NULL,
  76. `GRANT_OPTION` smallint(6) NOT NULL,
  77. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  78. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  79. `PART_ID` bigint(20) DEFAULT NULL,
  80. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  81. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  82. `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  83. PRIMARY KEY (`PART_GRANT_ID`),
  84. KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
  85. CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
  86. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  87. CREATE TABLE IF NOT EXISTS `ROLES` (
  88. `ROLE_ID` bigint(20) NOT NULL,
  89. `CREATE_TIME` int(11) NOT NULL,
  90. `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  91. `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  92. PRIMARY KEY (`ROLE_ID`),
  93. UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`)
  94. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  95. CREATE TABLE IF NOT EXISTS `ROLE_MAP` (
  96. `ROLE_GRANT_ID` bigint(20) NOT NULL,
  97. `ADD_TIME` int(11) NOT NULL,
  98. `GRANT_OPTION` smallint(6) NOT NULL,
  99. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  100. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  101. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  102. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  103. `ROLE_ID` bigint(20) DEFAULT NULL,
  104. PRIMARY KEY (`ROLE_GRANT_ID`),
  105. UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`),
  106. CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`)
  107. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  108. CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` (
  109. `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL,
  110. `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  111. `CREATE_TIME` int(11) NOT NULL,
  112. `GRANT_OPTION` smallint(6) NOT NULL,
  113. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  114. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  115. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  116. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  117. `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  118. `TBL_ID` bigint(20) DEFAULT NULL,
  119. PRIMARY KEY (`TBL_COLUMN_GRANT_ID`),
  120. KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
  121. CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
  122. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  123. CREATE TABLE IF NOT EXISTS `TBL_PRIVS` (
  124. `TBL_GRANT_ID` bigint(20) NOT NULL,
  125. `CREATE_TIME` int(11) NOT NULL,
  126. `GRANT_OPTION` smallint(6) NOT NULL,
  127. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  128. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  129. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  130. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  131. `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  132. `TBL_ID` bigint(20) DEFAULT NULL,
  133. PRIMARY KEY (`TBL_GRANT_ID`),
  134. KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
  135. CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
  136. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  137. CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` (
  138. `USER_GRANT_ID` bigint(20) NOT NULL,
  139. `CREATE_TIME` int(11) NOT NULL,
  140. `GRANT_OPTION` smallint(6) NOT NULL,
  141. `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  142. `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  143. `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  144. `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  145. `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  146. PRIMARY KEY (`USER_GRANT_ID`),
  147. UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`)
  148. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;