Updated Branches: refs/heads/trunk 55add73bd -> 098f5f2d9
AMBARI-3163. Cleanup ambari-server/src/main/resources/upgrade/ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql. (Dmitry Sen via odiachenko) Project: http://git-wip-us.apache.org/repos/asf/incubator-ambari/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ambari/commit/098f5f2d Tree: http://git-wip-us.apache.org/repos/asf/incubator-ambari/tree/098f5f2d Diff: http://git-wip-us.apache.org/repos/asf/incubator-ambari/diff/098f5f2d Branch: refs/heads/trunk Commit: 098f5f2d9c11d2554c394d538836ac31ba4229ef Parents: 55add73 Author: Oleksandr Diachenko <[email protected]> Authored: Wed Sep 11 18:37:59 2013 +0300 Committer: Oleksandr Diachenko <[email protected]> Committed: Wed Sep 11 18:37:59 2013 +0300 ---------------------------------------------------------------------- .../ddl/Ambari-DDL-Postgres-UPGRADE-1.3.0.sql | 85 ++++++++++---------- 1 file changed, 44 insertions(+), 41 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/098f5f2d/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 9d1277c..22ee297 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 @@ -17,38 +17,66 @@ -- \connect :dbname; +-- service to cluster level config mappings move. idempotent update +CREATE LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION update_clusterconfigmapping() + RETURNS void AS +$_$ +BEGIN + +IF NOT EXISTS ( + SELECT * + FROM pg_catalog.pg_tables + WHERE schemaname = 'ambari' + AND tablename = 'clusterconfigmapping' + ) + THEN + + CREATE TABLE ambari.clusterconfigmapping (cluster_id bigint NOT NULL, type_name VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, create_timestamp BIGINT NOT NULL, selected INTEGER NOT NULL DEFAULT 0, user_name VARCHAR(255) NOT NULL DEFAULT '_db', PRIMARY KEY (cluster_id, type_name, create_timestamp)); + ALTER TABLE ambari.clusterconfigmapping ADD CONSTRAINT FK_clusterconfigmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); + INSERT INTO ambari.clusterconfigmapping(cluster_id, type_name, version_tag, create_timestamp, selected) + (SELECT DISTINCT cluster_id, config_type, config_tag, cast(date_part('epoch', now()) as bigint), 1 + FROM ambari.serviceconfigmapping scm + WHERE timestamp = (SELECT max(timestamp) FROM ambari.serviceconfigmapping WHERE cluster_id = scm.cluster_id AND config_type = scm.config_type)); + DELETE FROM ambari.serviceconfigmapping; + +END IF; + +END; +$_$ LANGUAGE plpgsql; + -- Upgrade from 1.2.0 ALTER TABLE ambari.hosts ADD COLUMN ph_cpu_count INTEGER, ALTER COLUMN disks_info TYPE VARCHAR(10000); -- Upgrade to 1.3.0 + +-- setting run-time search_path for :username +ALTER ROLE :username SET search_path to 'ambari'; + +--updating clusterstate table ALTER TABLE ambari.clusterstate ADD COLUMN current_stack_version VARCHAR(255) NOT NULL; +--updating hostconfigmapping table ALTER TABLE ambari.hostconfigmapping ADD COLUMN user_name VARCHAR(255) NOT NULL DEFAULT '_db'; -ALTER TABLE ambari.clusterconfigmapping - ADD COLUMN user_name VARCHAR(255) NOT NULL DEFAULT '_db'; - CREATE TABLE ambari.hostconfigmapping (cluster_id bigint NOT NULL, host_name VARCHAR(255) NOT NULL, type_name VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, service_name VARCHAR(255), create_timestamp BIGINT NOT NULL, selected INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (cluster_id, host_name, type_name, create_timestamp)); GRANT ALL PRIVILEGES ON TABLE ambari.hostconfigmapping TO :username; 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 ROLE :username SET search_path to 'ambari'; - -ALTER SEQUENCE ambari.host_role_command_task_id_seq INCREMENT BY 50; -SELECT nextval('ambari.host_role_command_task_id_seq'); - -ALTER TABLE ambari.stage ADD COLUMN request_context VARCHAR(255);SELECT nextval('ambari.host_role_command_task_id_seq'); - +--updating stage table +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; +--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; @@ -57,50 +85,25 @@ insert into ambari.ambari_sequences(sequence_name, "value") union all select 'user_id_seq', nextval('ambari.users_user_id_seq') union all - select 'host_role_command_id_seq', nextval('ambari.host_role_command_task_id_seq'); + select 'host_role_command_id_seq', coalesce((select max(task_id) from ambari.host_role_command), 1) + 50; drop sequence ambari.host_role_command_task_id_seq; drop sequence ambari.users_user_id_seq; drop sequence ambari.clusters_cluster_id_seq; -CREATE LANGUAGE plpgsql; - +--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}'; 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'; --- service to cluster level config mappings move. idempotent update - -CREATE OR REPLACE FUNCTION update_clusterconfigmapping() - RETURNS void AS -$_$ -BEGIN - -IF NOT EXISTS ( - SELECT * - FROM pg_catalog.pg_tables - WHERE schemaname = 'ambari' - AND tablename = 'clusterconfigmapping' - ) - THEN - - CREATE TABLE ambari.clusterconfigmapping (cluster_id bigint NOT NULL, type_name VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, create_timestamp BIGINT NOT NULL, selected INTEGER NOT NULL DEFAULT 0, user_name VARCHAR(255) NOT NULL DEFAULT '_db', PRIMARY KEY (cluster_id, type_name, create_timestamp)); - ALTER TABLE ambari.clusterconfigmapping ADD CONSTRAINT FK_clusterconfigmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES ambari.clusters (cluster_id); - INSERT INTO ambari.clusterconfigmapping(cluster_id, type_name, version_tag, create_timestamp, selected) - (SELECT DISTINCT cluster_id, config_type, config_tag, cast(date_part('epoch', now()) as bigint), 1 - FROM ambari.serviceconfigmapping scm - WHERE timestamp = (SELECT max(timestamp) FROM ambari.serviceconfigmapping WHERE cluster_id = scm.cluster_id AND config_type = scm.config_type)); - DELETE FROM ambari.serviceconfigmapping; - -END IF; - -END; -$_$ LANGUAGE plpgsql; - +--updating clusterconfigmapping table +ALTER TABLE ambari.clusterconfigmapping + ADD COLUMN user_name VARCHAR(255) NOT NULL DEFAULT '_db'; SELECT update_clusterconfigmapping(); GRANT ALL PRIVILEGES ON TABLE ambari.clusterconfigmapping TO :username;
