Updated Branches: refs/heads/trunk 91c9c5a96 -> 62a75007f
AMBARI-3705. Update the DDL scripts to support upgrade to Config groups. (Dmytro Sen via swagle) Project: http://git-wip-us.apache.org/repos/asf/incubator-ambari/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ambari/commit/62a75007 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ambari/tree/62a75007 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ambari/diff/62a75007 Branch: refs/heads/trunk Commit: 62a75007f4581e7fec86aa937af019372ee7ca5d Parents: 91c9c5a Author: Siddharth Wagle <swa...@hortonworks.com> Authored: Thu Nov 7 17:38:08 2013 -0800 Committer: Siddharth Wagle <swa...@hortonworks.com> Committed: Thu Nov 7 17:38:08 2013 -0800 ---------------------------------------------------------------------- .../ConfigGroupConfigMappingEntity.java | 2 +- .../main/resources/Ambari-DDL-Oracle-CREATE.sql | 9 ++++ .../resources/Ambari-DDL-Postgres-CREATE.sql | 8 +-- .../Ambari-DDL-Postgres-REMOTE-CREATE.sql | 6 +-- .../upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql | 18 +++++++ .../ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql | 54 ++++++++++++++------ 6 files changed, 74 insertions(+), 23 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/ConfigGroupConfigMappingEntity.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/ConfigGroupConfigMappingEntity.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/ConfigGroupConfigMappingEntity.java index cc0a89f..a92526a 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/ConfigGroupConfigMappingEntity.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/ConfigGroupConfigMappingEntity.java @@ -29,7 +29,7 @@ import javax.persistence.NamedQuery; import javax.persistence.Table; @Entity -@Table(name = "configgroupclusterconfigmapping") +@Table(name = "confgroupclusterconfigmapping") @IdClass(ConfigGroupConfigMappingEntityPK.class) @NamedQueries({ @NamedQuery(name = "configsByGroup", query = http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql index c8d0065..25d82ef 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql @@ -43,6 +43,9 @@ CREATE TABLE metainfo ("metainfo_key" VARCHAR2(255) NOT NULL, "metainfo_value" C CREATE TABLE ClusterHostMapping (cluster_id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, PRIMARY KEY (cluster_id, host_name)); CREATE TABLE user_roles (role_name VARCHAR2(255) NOT NULL, user_id NUMBER(10) NOT NULL, PRIMARY KEY (role_name, user_id)); CREATE TABLE ambari_sequences (sequence_name VARCHAR2(50) NOT NULL, value NUMBER(38) NULL, PRIMARY KEY (sequence_name)); +CREATE TABLE ambari.configgroup (group_id NUMBER(19), cluster_id NUMBER(19) NOT NULL, group_name VARCHAR2(255) NOT NULL, tag VARCHAR2(1024) NOT NULL, description VARCHAR2(1024), create_timestamp NUMBER(19) NOT NULL, PRIMARY KEY(group_id), UNIQUE(group_name)); +CREATE TABLE ambari.confgroupclusterconfigmapping (config_group_id NUMBER(19) NOT NULL, cluster_id NUMBER(19) NOT NULL, config_type VARCHAR2(255) NOT NULL, version_tag VARCHAR2(255) NOT NULL, user_name VARCHAR2(255) DEFAULT '_db', create_timestamp NUMBER(19) NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); +CREATE TABLE ambari.configgrouphostmapping (config_group_id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, PRIMARY KEY(config_group_id, host_name)); ALTER TABLE users ADD CONSTRAINT UNQ_users_0 UNIQUE (user_name, ldap_user); @@ -74,12 +77,18 @@ ALTER TABLE ClusterHostMapping ADD CONSTRAINT ClusterHostMapping_cluster_id FORE ALTER TABLE ClusterHostMapping ADD CONSTRAINT ClusterHostMapping_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); ALTER TABLE user_roles ADD CONSTRAINT FK_user_roles_user_id FOREIGN KEY (user_id) REFERENCES users (user_id); ALTER TABLE user_roles ADD CONSTRAINT FK_user_roles_role_name FOREIGN KEY (role_name) REFERENCES roles (role_name); +ALTER TABLE ambari.configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.confgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_configgroup_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.confgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); INSERT INTO ambari_sequences(sequence_name, value) values ('host_role_command_id_seq', 0); INSERT INTO ambari_sequences(sequence_name, value) values ('user_id_seq', 1); INSERT INTO ambari_sequences(sequence_name, value) values ('cluster_id_seq', 0); +INSERT INTO ambari_sequences(sequence_name, value) values ('configgroup_id_seq', 1); INSERT INTO metainfo("metainfo_key", "metainfo_value") values ('version', '${ambariVersion}'); insert into Roles(role_name) http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql index ff33085..509ecb5 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql @@ -113,8 +113,8 @@ GRANT ALL PRIVILEGES ON TABLE ambari.ambari_sequences TO :username; CREATE TABLE ambari.configgroup (group_id BIGINT, cluster_id BIGINT NOT NULL, group_name VARCHAR(255) NOT NULL, tag VARCHAR(1024) NOT NULL, description VARCHAR(1024), create_timestamp BIGINT NOT NULL, PRIMARY KEY(group_id), UNIQUE(group_name)); GRANT ALL PRIVILEGES ON TABLE ambari.configgroup TO :username; -CREATE TABLE ambari.configgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); -GRANT ALL PRIVILEGES ON TABLE ambari.configgroupclusterconfigmapping TO :username; +CREATE TABLE ambari.confgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); +GRANT ALL PRIVILEGES ON TABLE ambari.confgroupclusterconfigmapping TO :username; CREATE TABLE ambari.configgrouphostmapping (config_group_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, PRIMARY KEY(config_group_id, host_name)); GRANT ALL PRIVILEGES ON TABLE ambari.configgrouphostmapping TO :username; @@ -155,8 +155,8 @@ ALTER TABLE ambari.user_roles ADD CONSTRAINT FK_user_roles_role_name FOREIGN KEY ALTER TABLE ambari.hostconfigmapping ADD CONSTRAINT FK_hostconfigmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); ALTER TABLE ambari.hostconfigmapping ADD CONSTRAINT FK_hostconfigmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); ALTER TABLE ambari.configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); -ALTER TABLE ambari.configgroupclusterconfigmapping ADD CONSTRAINT FK_configgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); -ALTER TABLE ambari.configgroupclusterconfigmapping ADD CONSTRAINT FK_configgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_configgroup_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql index 7c88d2a..a216876 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql @@ -47,7 +47,7 @@ CREATE TABLE ambari.hostconfigmapping (cluster_id bigint NOT NULL, host_name VAR CREATE TABLE ambari.metainfo ("metainfo_key" VARCHAR(255), "metainfo_value" VARCHAR, PRIMARY KEY("metainfo_key")); CREATE TABLE ambari.ambari_sequences (sequence_name VARCHAR(255) PRIMARY KEY, "value" BIGINT NOT NULL); CREATE TABLE ambari.configgroup (group_id BIGINT, cluster_id BIGINT NOT NULL, group_name VARCHAR(255) NOT NULL, tag VARCHAR(1024) NOT NULL, description VARCHAR(1024), create_timestamp BIGINT NOT NULL, PRIMARY KEY(group_id), UNIQUE(group_name)); -CREATE TABLE ambari.configgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); +CREATE TABLE ambari.confgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); CREATE TABLE ambari.configgrouphostmapping (config_group_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, PRIMARY KEY(config_group_id, host_name)); ALTER TABLE ambari.clusterconfig ADD CONSTRAINT FK_clusterconfig_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); @@ -81,8 +81,8 @@ ALTER TABLE ambari.user_roles ADD CONSTRAINT FK_user_roles_role_name FOREIGN KEY ALTER TABLE ambari.hostconfigmapping ADD CONSTRAINT FK_hostconfigmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); ALTER TABLE ambari.hostconfigmapping ADD CONSTRAINT FK_hostconfigmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); ALTER TABLE ambari.configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); -ALTER TABLE ambari.configgroupclusterconfigmapping ADD CONSTRAINT FK_configgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); -ALTER TABLE ambari.configgroupclusterconfigmapping ADD CONSTRAINT FK_configgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_configgroup_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql b/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql index c16a689..2495bd7 100644 --- a/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql +++ b/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Oracle-UPGRADE.sql @@ -27,4 +27,22 @@ ALTER TABLE hostconfigmapping ADD (user_name VARCHAR2 (255) DEFAULT '_db'); --Upgrade version to current UPDATE metainfo SET "metainfo_key" = 'version', "metainfo_value" = '${ambariVersion}'; +INSERT INTO ambari_sequences(sequence_name, value) values ('configgroup_id_seq', 1); + +-- drop deprecated tables componentconfigmapping and hostcomponentconfigmapping +-- not required after Config Group implementation +--DROP TABLE componentconfigmapping; +--DROP TABLE hostcomponentconfigmapping; + +-- required for Config Group implementation +CREATE TABLE ambari.configgroup (group_id BIGINT, cluster_id BIGINT NOT NULL, group_name VARCHAR2(255) NOT NULL, tag VARCHAR2(1024) NOT NULL, description VARCHAR2(1024), create_timestamp BIGINT NOT NULL, PRIMARY KEY(group_id), UNIQUE(group_name)); +CREATE TABLE ambari.confgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR2(255) NOT NULL, version_tag VARCHAR2(255) NOT NULL, user_name VARCHAR2(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); +CREATE TABLE ambari.configgrouphostmapping (config_group_id BIGINT NOT NULL, host_name VARCHAR2(255) NOT NULL, PRIMARY KEY(config_group_id, host_name)); + +ALTER TABLE ambari.configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_configgroup_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); + commit; http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/62a75007/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql b/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql index 4a211f9..86dac17 100644 --- a/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql +++ b/ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql @@ -54,6 +54,7 @@ ALTER TABLE ambari.hosts -- Upgrade to 1.3.0 -- setting run-time search_path for :username +ALTER SCHEMA ambari OWNER TO :username; ALTER ROLE :username SET search_path to 'ambari'; --updating clusterstate table @@ -72,34 +73,36 @@ ALTER TABLE ambari.hostconfigmapping ADD CONSTRAINT FK_hostconfigmapping_host_na ALTER TABLE ambari.stage ADD COLUMN request_context VARCHAR(255); -- portability changes for MySQL/Oracle support -alter table ambari.hostcomponentdesiredconfigmapping rename to hcdesiredconfigmapping; -alter table ambari.users alter column user_id drop default; -alter table ambari.users alter column ldap_user type INTEGER using case when ldap_user=true then 1 else 0 END; +ALTER TABLE ambari.hostcomponentdesiredconfigmapping rename to hcdesiredconfigmapping; +ALTER TABLE ambari.users ALTER column user_id DROP DEFAULT; +ALTER TABLE ambari.users ALTER column ldap_user TYPE INTEGER USING CASE WHEN ldap_user=true THEN 1 ELSE 0 END; --creating ambari_sequences table instead of deprecated sequences CREATE TABLE ambari.ambari_sequences (sequence_name VARCHAR(255) PRIMARY KEY, "value" BIGINT NOT NULL); GRANT ALL PRIVILEGES ON TABLE ambari.ambari_sequences TO :username; -insert into ambari.ambari_sequences(sequence_name, "value") - select 'cluster_id_seq', nextval('ambari.clusters_cluster_id_seq') - union all - select 'user_id_seq', nextval('ambari.users_user_id_seq') - union all - select 'host_role_command_id_seq', coalesce((select max(task_id) from ambari.host_role_command), 1) + 50; +INSERT INTO ambari.ambari_sequences(sequence_name, "value") + SELECT 'cluster_id_seq', nextval('ambari.clusters_cluster_id_seq') + UNION ALL + SELECT 'user_id_seq', nextval('ambari.users_user_id_seq') + UNION ALL + SELECT 'host_role_command_id_seq', COALESCE((SELECT max(task_id) FROM ambari.host_role_command), 1) + 50 + UNION ALL + SELECT 'configgroup_id_seq', 1; -drop sequence ambari.host_role_command_task_id_seq; -drop sequence ambari.users_user_id_seq; -drop sequence ambari.clusters_cluster_id_seq; +DROP sequence ambari.host_role_command_task_id_seq; +DROP sequence ambari.users_user_id_seq; +DROP sequence ambari.clusters_cluster_id_seq; --updating metainfo table CREATE TABLE ambari.metainfo (metainfo_key VARCHAR(255), metainfo_value VARCHAR, PRIMARY KEY(metainfo_key)); -INSERT INTO ambari.metainfo (metainfo_key, metainfo_value) select 'version', '${ambariVersion}'; +INSERT INTO ambari.metainfo (metainfo_key, metainfo_value) SELECT 'version', '${ambariVersion}'; UPDATE ambari.metainfo SET metainfo_value = '${ambariVersion}' WHERE metainfo_key = 'version'; GRANT ALL PRIVILEGES ON TABLE ambari.metainfo TO :username; --replacing deprecated STOP_FAILED and START_FAILED states with INSTALLED -UPDATE ambari.hostcomponentstate SET current_state = 'INSTALLED' WHERE current_state like 'STOP_FAILED'; -UPDATE ambari.hostcomponentstate SET current_state = 'INSTALLED' WHERE current_state like 'START_FAILED'; +UPDATE ambari.hostcomponentstate SET current_state = 'INSTALLED' WHERE current_state LIKE 'STOP_FAILED'; +UPDATE ambari.hostcomponentstate SET current_state = 'INSTALLED' WHERE current_state LIKE 'START_FAILED'; --updating clusterconfigmapping table ALTER TABLE ambari.clusterconfigmapping @@ -107,3 +110,24 @@ ALTER TABLE ambari.clusterconfigmapping SELECT update_clusterconfigmapping(); GRANT ALL PRIVILEGES ON TABLE ambari.clusterconfigmapping TO :username; +-- drop deprecated tables componentconfigmapping and hostcomponentconfigmapping +-- not required after Config Group implementation +--DROP TABLE componentconfigmapping; +--DROP TABLE hostcomponentconfigmapping; + +-- required for Config Group implementation +CREATE TABLE ambari.configgroup (group_id BIGINT, cluster_id BIGINT NOT NULL, group_name VARCHAR(255) NOT NULL, tag VARCHAR(1024) NOT NULL, description VARCHAR(1024), create_timestamp BIGINT NOT NULL, PRIMARY KEY(group_id), UNIQUE(group_name)); +GRANT ALL PRIVILEGES ON TABLE ambari.configgroup TO :username; + +CREATE TABLE ambari.confgroupclusterconfigmapping (config_group_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, config_type VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, PRIMARY KEY(config_group_id, cluster_id, config_type)); +GRANT ALL PRIVILEGES ON TABLE ambari.confgroupclusterconfigmapping TO :username; + +CREATE TABLE ambari.configgrouphostmapping (config_group_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, PRIMARY KEY(config_group_id, host_name)); +GRANT ALL PRIVILEGES ON TABLE ambari.configgrouphostmapping TO :username; + +ALTER TABLE ambari.configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_config_tag FOREIGN KEY (version_tag, config_type, cluster_id) REFERENCES ambari.clusterconfig (version_tag, type_name, cluster_id); +ALTER TABLE ambari.confgroupclusterconfigmapping ADD CONSTRAINT FK_confgroupclusterconfigmapping_group_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_configgroup_id FOREIGN KEY (config_group_id) REFERENCES ambari.configgroup (group_id); +ALTER TABLE ambari.configgrouphostmapping ADD CONSTRAINT FK_configgrouphostmapping_host_name FOREIGN KEY (host_name) REFERENCES ambari.hosts (host_name); +
