http://git-wip-us.apache.org/repos/asf/ambari/blob/be73d167/ambari-server/src/main/resources/Ambari-DDL-AzureDB-CREATE.sql ---------------------------------------------------------------------- diff --cc ambari-server/src/main/resources/Ambari-DDL-AzureDB-CREATE.sql index d13985d,0000000..b971cbc mode 100644,000000..100644 --- a/ambari-server/src/main/resources/Ambari-DDL-AzureDB-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-AzureDB-CREATE.sql @@@ -1,2175 -1,0 +1,2147 @@@ +/* +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License") you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +*/ + +/* +Schema population script for $(AMBARIDBNAME) + +Use this script in sqlcmd mode, setting the environment variables like this: +set AMBARIDBNAME=ambari + +sqlcmd -S localhost\SQLEXPRESS -i C:\app\ambari-server-1.3.0-SNAPSHOT\resources\Ambari-DDL-SQLServer-CREATE.sql +*/ + + +------create the database------ + +------create tables and grant privileges to db user--------- - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.stack') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('stack') AND type = 'U') +BEGIN +CREATE TABLE stack( + stack_id BIGINT NOT NULL, + stack_name VARCHAR(255) NOT NULL, + stack_version VARCHAR(255) NOT NULL, + CONSTRAINT PK_stack PRIMARY KEY CLUSTERED (stack_id), + CONSTRAINT UQ_stack UNIQUE (stack_name, stack_version)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.extension') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('extension') AND type = 'U') +BEGIN +CREATE TABLE extension( + extension_id BIGINT NOT NULL, + extension_name VARCHAR(255) NOT NULL, + extension_version VARCHAR(255) NOT NULL, + CONSTRAINT PK_extension PRIMARY KEY CLUSTERED (extension_id), + CONSTRAINT UQ_extension UNIQUE (extension_name, extension_version)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.extensionlink') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('extensionlink') AND type = 'U') +BEGIN +CREATE TABLE extensionlink( + link_id BIGINT NOT NULL, + stack_id BIGINT NOT NULL, + extension_id BIGINT NOT NULL, + CONSTRAINT PK_extensionlink PRIMARY KEY CLUSTERED (link_id), + CONSTRAINT FK_extensionlink_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id), + CONSTRAINT FK_extensionlink_extension_id FOREIGN KEY (extension_id) REFERENCES extension(extension_id), + CONSTRAINT UQ_extension_link UNIQUE (stack_id, extension_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminresourcetype') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminresourcetype') AND type = 'U') +BEGIN +CREATE TABLE adminresourcetype ( + resource_type_id INTEGER NOT NULL, + resource_type_name VARCHAR(255) NOT NULL, + CONSTRAINT PK_adminresourcetype PRIMARY KEY CLUSTERED (resource_type_id) + ) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminresource') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminresource') AND type = 'U') +BEGIN +CREATE TABLE adminresource ( + resource_id BIGINT NOT NULL, + resource_type_id INTEGER NOT NULL, + CONSTRAINT PK_adminresource PRIMARY KEY CLUSTERED (resource_id), + CONSTRAINT FK_resource_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.clusters') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('clusters') AND type = 'U') +BEGIN +CREATE TABLE clusters ( + cluster_id BIGINT NOT NULL, + resource_id BIGINT NOT NULL, + upgrade_id BIGINT, + cluster_info VARCHAR(255) NOT NULL, + cluster_name VARCHAR(100) NOT NULL UNIQUE, + provisioning_state VARCHAR(255) NOT NULL DEFAULT 'INIT', + security_type VARCHAR(32) NOT NULL DEFAULT 'NONE', + desired_cluster_state VARCHAR(255) NOT NULL, + desired_stack_id BIGINT NOT NULL, + CONSTRAINT PK_clusters PRIMARY KEY CLUSTERED (cluster_id), + CONSTRAINT FK_clusters_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id), + CONSTRAINT FK_clusters_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.clusterconfig') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('clusterconfig') AND type = 'U') +BEGIN +CREATE TABLE clusterconfig ( + config_id BIGINT NOT NULL, + version_tag VARCHAR(255) NOT NULL, + version BIGINT NOT NULL, + type_name VARCHAR(255) NOT NULL, + cluster_id BIGINT NOT NULL, + stack_id BIGINT NOT NULL, ++ selected SMALLINT NOT NULL DEFAULT 0, + config_data VARCHAR(MAX) NOT NULL, + config_attributes VARCHAR(MAX), + create_timestamp BIGINT NOT NULL, ++ unmapped SMALLINT NOT NULL DEFAULT 0, ++ selected_timestamp BIGINT NOT NULL DEFAULT 0, + CONSTRAINT PK_clusterconfig PRIMARY KEY CLUSTERED (config_id), + CONSTRAINT FK_clusterconfig_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id), + CONSTRAINT FK_clusterconfig_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id), + CONSTRAINT UQ_config_type_tag UNIQUE (cluster_id, type_name, version_tag), + CONSTRAINT UQ_config_type_version UNIQUE (cluster_id, type_name, version)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.serviceconfig') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('serviceconfig') AND type = 'U') +BEGIN +CREATE TABLE serviceconfig ( + service_config_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + service_name VARCHAR(255) NOT NULL, + version BIGINT NOT NULL, + create_timestamp BIGINT NOT NULL, + stack_id BIGINT NOT NULL, + user_name VARCHAR(255) NOT NULL DEFAULT '_db', + group_id BIGINT, + note VARCHAR(MAX), + CONSTRAINT PK_serviceconfig PRIMARY KEY CLUSTERED (service_config_id), + CONSTRAINT FK_serviceconfig_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id), + CONSTRAINT UQ_scv_service_version UNIQUE (cluster_id, service_name, version)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hosts') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hosts') AND type = 'U') +BEGIN +CREATE TABLE hosts ( + host_id BIGINT NOT NULL, + host_name VARCHAR(255) NOT NULL, + cpu_count INTEGER NOT NULL, + ph_cpu_count INTEGER, + cpu_info VARCHAR(255) NOT NULL, + discovery_status VARCHAR(2000) NOT NULL, + host_attributes VARCHAR(MAX) NOT NULL, + ipv4 VARCHAR(255), + ipv6 VARCHAR(255), + public_host_name VARCHAR(255), + last_registration_time BIGINT NOT NULL, + os_arch VARCHAR(255) NOT NULL, + os_info VARCHAR(1000) NOT NULL, + os_type VARCHAR(255) NOT NULL, + rack_info VARCHAR(255) NOT NULL, + total_mem BIGINT NOT NULL, + CONSTRAINT PK_hosts PRIMARY KEY CLUSTERED (host_id), + CONSTRAINT UQ_hosts_host_name UNIQUE (host_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.serviceconfighosts') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('serviceconfighosts') AND type = 'U') +BEGIN +CREATE TABLE serviceconfighosts ( + service_config_id BIGINT NOT NULL, + host_id BIGINT NOT NULL, + CONSTRAINT PK_serviceconfighosts PRIMARY KEY CLUSTERED (service_config_id, host_id), + CONSTRAINT FK_scvhosts_host_id FOREIGN KEY (host_id) REFERENCES hosts(host_id), + CONSTRAINT FK_scvhosts_scv FOREIGN KEY (service_config_id) REFERENCES serviceconfig(service_config_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.serviceconfigmapping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('serviceconfigmapping') AND type = 'U') +BEGIN +CREATE TABLE serviceconfigmapping ( + service_config_id BIGINT NOT NULL, + config_id BIGINT NOT NULL, + CONSTRAINT PK_serviceconfigmapping PRIMARY KEY CLUSTERED (service_config_id, config_id), + CONSTRAINT FK_scvm_config FOREIGN KEY (config_id) REFERENCES clusterconfig(config_id), + CONSTRAINT FK_scvm_scv FOREIGN KEY (service_config_id) REFERENCES serviceconfig(service_config_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.clusterconfigmapping') AND type = 'U') - BEGIN - CREATE TABLE clusterconfigmapping ( - cluster_id BIGINT NOT NULL, - type_name VARCHAR(255) NOT NULL, - version_tag VARCHAR(255) NOT NULL, - create_timestamp BIGINT NOT NULL, - selected INT NOT NULL DEFAULT 0, - user_name VARCHAR(255) NOT NULL DEFAULT '_db', - CONSTRAINT PK_clusterconfigmapping PRIMARY KEY CLUSTERED (cluster_id, type_name, create_timestamp ), - CONSTRAINT clusterconfigmappingcluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)) - END - - - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.clusterservices') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('clusterservices') AND type = 'U') +BEGIN +CREATE TABLE clusterservices ( + service_name VARCHAR(255) NOT NULL, + cluster_id BIGINT NOT NULL, + service_enabled INT NOT NULL, + CONSTRAINT PK_clusterservices PRIMARY KEY CLUSTERED (service_name, cluster_id), + CONSTRAINT FK_clusterservices_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.clusterstate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('clusterstate') AND type = 'U') +BEGIN +CREATE TABLE clusterstate ( + cluster_id BIGINT NOT NULL, + current_cluster_state VARCHAR(255) NOT NULL, + current_stack_id BIGINT NOT NULL, + CONSTRAINT PK_clusterstate PRIMARY KEY CLUSTERED (cluster_id), + CONSTRAINT FK_clusterstate_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id), + CONSTRAINT FK_cs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.repo_version') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('repo_version') AND type = 'U') +BEGIN +CREATE TABLE repo_version ( + repo_version_id BIGINT NOT NULL, + stack_id BIGINT NOT NULL, + version VARCHAR(255) NOT NULL, + display_name VARCHAR(128) NOT NULL, + repositories VARCHAR(MAX) NOT NULL, + repo_type VARCHAR(255) DEFAULT 'STANDARD' NOT NULL, ++ hidden SMALLINT NOT NULL DEFAULT 0, + version_url VARCHAR(1024), + version_xml VARCHAR(MAX), + version_xsd VARCHAR(512), + parent_id BIGINT, + CONSTRAINT PK_repo_version PRIMARY KEY CLUSTERED (repo_version_id), + CONSTRAINT FK_repoversion_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id), + CONSTRAINT UQ_repo_version_display_name UNIQUE (display_name), + CONSTRAINT UQ_repo_version_stack_id UNIQUE (stack_id, version)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.cluster_version') AND type = 'U') - BEGIN - CREATE TABLE cluster_version ( - id BIGINT NOT NULL, - cluster_id BIGINT NOT NULL, - repo_version_id BIGINT NOT NULL, - STATE VARCHAR(255) NOT NULL, - start_time BIGINT NOT NULL, - end_time BIGINT, - user_name VARCHAR(255), - CONSTRAINT PK_cluster_version PRIMARY KEY CLUSTERED (id), - CONSTRAINT FK_cluster_version_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id), - CONSTRAINT FK_cluster_version_repovers_id FOREIGN KEY (repo_version_id) REFERENCES repo_version (repo_version_id)) - END - - - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.servicecomponentdesiredstate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('servicecomponentdesiredstate') AND type = 'U') +BEGIN +CREATE TABLE servicecomponentdesiredstate ( + id BIGINT NOT NULL, + component_name VARCHAR(255) NOT NULL, + cluster_id BIGINT NOT NULL, - desired_stack_id BIGINT NOT NULL, - desired_version VARCHAR(255) NOT NULL DEFAULT 'UNKNOWN', ++ desired_repo_version_id BIGINT NOT NULL, + desired_state VARCHAR(255) NOT NULL, + service_name VARCHAR(255) NOT NULL, + recovery_enabled SMALLINT NOT NULL DEFAULT 0, ++ repo_state VARCHAR(255) NOT NULL DEFAULT 'NOT_REQUIRED', + CONSTRAINT pk_sc_desiredstate PRIMARY KEY (id), + CONSTRAINT UQ_scdesiredstate_name UNIQUE(component_name, service_name, cluster_id), - CONSTRAINT FK_scds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id), ++ CONSTRAINT FK_scds_desired_repo_id FOREIGN KEY (desired_repo_version_id) REFERENCES repo_version (repo_version_id), + CONSTRAINT srvccmponentdesiredstatesrvcnm FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostcomponentdesiredstate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostcomponentdesiredstate') AND type = 'U') +BEGIN +CREATE TABLE hostcomponentdesiredstate ( + id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + component_name VARCHAR(255) NOT NULL, - desired_stack_id BIGINT NOT NULL, + desired_state VARCHAR(255) NOT NULL, + host_id BIGINT NOT NULL, + service_name VARCHAR(255) NOT NULL, + admin_state VARCHAR(32), + maintenance_state VARCHAR(32) NOT NULL, - security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', + restart_required BIT NOT NULL DEFAULT 0, + CONSTRAINT PK_hostcomponentdesiredstate PRIMARY KEY CLUSTERED (id), - CONSTRAINT UQ_hcdesiredstate_name UNIQUE NONCLUSTERED (component_name, service_name, host_id, cluster_id), - CONSTRAINT FK_hcds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id), - CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, service_name, cluster_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id), - CONSTRAINT hstcmponentdesiredstatehstid FOREIGN KEY (host_id) REFERENCES hosts (host_id)) ++ CONSTRAINT UQ_hcdesiredstate_name UNIQUE (component_name, service_name, host_id, cluster_id), ++ CONSTRAINT FK_hcdesiredstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id), ++ CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, service_name, cluster_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id)) +END + + - - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostcomponentstate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostcomponentstate') AND type = 'U') +BEGIN +CREATE TABLE hostcomponentstate ( + id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + component_name VARCHAR(255) NOT NULL, + version VARCHAR(32) NOT NULL DEFAULT 'UNKNOWN', - current_stack_id BIGINT NOT NULL, + current_state VARCHAR(255) NOT NULL, + host_id BIGINT NOT NULL, + service_name VARCHAR(255) NOT NULL, + upgrade_state VARCHAR(32) NOT NULL DEFAULT 'NONE', - security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', + CONSTRAINT PK_hostcomponentstate PRIMARY KEY CLUSTERED (id), - CONSTRAINT FK_hcs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id), + CONSTRAINT FK_hostcomponentstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id), + CONSTRAINT hstcomponentstatecomponentname FOREIGN KEY (component_name, service_name, cluster_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id)) +END + + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_host_component_state') +BEGIN +CREATE NONCLUSTERED INDEX idx_host_component_state on hostcomponentstate(host_id, component_name, service_name, cluster_id) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hoststate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hoststate') AND type = 'U') +BEGIN +CREATE TABLE hoststate ( + agent_version VARCHAR(255) NOT NULL, + available_mem BIGINT NOT NULL, + current_state VARCHAR(255) NOT NULL, + health_status VARCHAR(255), + host_id BIGINT NOT NULL, + time_in_state BIGINT NOT NULL, + maintenance_state VARCHAR(512), + CONSTRAINT PK_hoststate PRIMARY KEY CLUSTERED (host_id), + CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.servicedesiredstate') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('servicedesiredstate') AND type = 'U') +BEGIN +CREATE TABLE servicedesiredstate ( + cluster_id BIGINT NOT NULL, + desired_host_role_mapping INTEGER NOT NULL, - desired_stack_id BIGINT NOT NULL, ++ desired_repo_version_id BIGINT NOT NULL, + desired_state VARCHAR(255) NOT NULL, + service_name VARCHAR(255) NOT NULL, + maintenance_state VARCHAR(32) NOT NULL, - security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', + credential_store_enabled SMALLINT NOT NULL DEFAULT 0, + CONSTRAINT PK_servicedesiredstate PRIMARY KEY CLUSTERED (cluster_id,service_name), - CONSTRAINT FK_sds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id), ++ CONSTRAINT FK_repo_version_id FOREIGN KEY (desired_repo_version_id) REFERENCES repo_version (repo_version_id), + CONSTRAINT servicedesiredstateservicename FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminprincipaltype') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminprincipaltype') AND type = 'U') +BEGIN +CREATE TABLE adminprincipaltype ( + principal_type_id INTEGER NOT NULL, + principal_type_name VARCHAR(255) NOT NULL, + CONSTRAINT PK_adminprincipaltype PRIMARY KEY CLUSTERED (principal_type_id) + ) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminprincipal') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminprincipal') AND type = 'U') +BEGIN +CREATE TABLE adminprincipal ( + principal_id BIGINT NOT NULL, + principal_type_id INTEGER NOT NULL, + CONSTRAINT PK_adminprincipal PRIMARY KEY CLUSTERED (principal_id), + CONSTRAINT FK_principal_principal_type_id FOREIGN KEY (principal_type_id) REFERENCES adminprincipaltype(principal_type_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.users') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('users') AND type = 'U') +BEGIN +CREATE TABLE users ( + user_id INTEGER, + principal_id BIGINT NOT NULL, + ldap_user INTEGER NOT NULL DEFAULT 0, + user_name VARCHAR(255) NOT NULL, + user_type VARCHAR(255) NOT NULL DEFAULT 'LOCAL', + create_time DATETIME DEFAULT GETDATE(), + user_password VARCHAR(255), + active INTEGER NOT NULL DEFAULT 1, + active_widget_layouts VARCHAR(1024) DEFAULT NULL, + CONSTRAINT PK_users PRIMARY KEY CLUSTERED (user_id), + CONSTRAINT FK_users_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id), + CONSTRAINT UNQ_users_0 UNIQUE (user_name, user_type)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.groups') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('groups') AND type = 'U') +BEGIN +CREATE TABLE groups ( + group_id INTEGER, + principal_id BIGINT NOT NULL, + group_name VARCHAR(255) NOT NULL, + ldap_group INTEGER NOT NULL DEFAULT 0, + group_type VARCHAR(255) NOT NULL DEFAULT 'LOCAL', + CONSTRAINT PK_groups PRIMARY KEY CLUSTERED (group_id), + CONSTRAINT FK_groups_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id), + CONSTRAINT UNQ_groups_0 UNIQUE (group_name, ldap_group)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.members') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('members') AND type = 'U') +BEGIN +CREATE TABLE members ( + member_id INTEGER, + group_id INTEGER NOT NULL, + user_id INTEGER NOT NULL, + CONSTRAINT PK_members PRIMARY KEY CLUSTERED (member_id), + CONSTRAINT FK_members_group_id FOREIGN KEY (group_id) REFERENCES groups (group_id), + CONSTRAINT FK_members_user_id FOREIGN KEY (user_id) REFERENCES users (user_id), + CONSTRAINT UNQ_members_0 UNIQUE (group_id, user_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.requestschedule') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('requestschedule') AND type = 'U') +BEGIN +CREATE TABLE requestschedule ( + schedule_id BIGINT, + cluster_id BIGINT NOT NULL, + description VARCHAR(255), + STATUS VARCHAR(255), + batch_separation_seconds SMALLINT, + batch_toleration_limit SMALLINT, + authenticated_user_id INTEGER, + create_user VARCHAR(255), + create_timestamp BIGINT, + update_user VARCHAR(255), + update_timestamp BIGINT, + minutes VARCHAR(10), + hours VARCHAR(10), + days_of_month VARCHAR(10), + month VARCHAR(10), + day_of_week VARCHAR(10), + yearToSchedule VARCHAR(10), + startTime VARCHAR(50), + endTime VARCHAR(50), + last_execution_status VARCHAR(255), + CONSTRAINT PK_requestschedule PRIMARY KEY CLUSTERED (schedule_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.request') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('request') AND type = 'U') +BEGIN +CREATE TABLE request ( + request_id BIGINT NOT NULL, + cluster_id BIGINT, + command_name VARCHAR(255), + create_time BIGINT NOT NULL, + end_time BIGINT NOT NULL, + exclusive_execution BIT NOT NULL DEFAULT 0, + inputs VARBINARY(MAX), + request_context VARCHAR(255), + request_type VARCHAR(255), + request_schedule_id BIGINT, + start_time BIGINT NOT NULL, - status VARCHAR(255), ++ status VARCHAR(255) NOT NULL DEFAULT 'PENDING', ++ display_status VARCHAR(255) NOT NULL DEFAULT 'PENDING', + cluster_host_info VARBINARY(MAX) NOT NULL, + CONSTRAINT PK_request PRIMARY KEY CLUSTERED (request_id), + CONSTRAINT FK_request_schedule_id FOREIGN KEY (request_schedule_id) REFERENCES requestschedule (schedule_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.stage') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('stage') AND type = 'U') +BEGIN +CREATE TABLE stage ( + stage_id BIGINT NOT NULL, + request_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + skippable SMALLINT DEFAULT 0 NOT NULL, + supports_auto_skip_failure SMALLINT DEFAULT 0 NOT NULL, + log_info VARCHAR(255) NOT NULL, + request_context VARCHAR(255), + command_params VARBINARY(MAX), + host_params VARBINARY(MAX), + command_execution_type VARCHAR(32) NOT NULL DEFAULT 'STAGE', ++ status VARCHAR(255) NOT NULL DEFAULT 'PENDING', ++ display_status VARCHAR(255) NOT NULL DEFAULT 'PENDING', + CONSTRAINT PK_stage PRIMARY KEY CLUSTERED (stage_id, request_id), + CONSTRAINT FK_stage_request_id FOREIGN KEY (request_id) REFERENCES request (request_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.host_role_command') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('host_role_command') AND type = 'U') +BEGIN +CREATE TABLE host_role_command ( + task_id BIGINT NOT NULL, + attempt_count SMALLINT NOT NULL, + retry_allowed SMALLINT DEFAULT 0 NOT NULL, + event VARCHAR(MAX) NOT NULL, + exitcode INTEGER NOT NULL, + host_id BIGINT, + last_attempt_time BIGINT NOT NULL, + request_id BIGINT NOT NULL, + role VARCHAR(255), + stage_id BIGINT NOT NULL, + start_time BIGINT NOT NULL, + original_start_time BIGINT NOT NULL, + end_time BIGINT, - status VARCHAR(255), ++ status VARCHAR(255) NOT NULL DEFAULT 'PENDING', + auto_skip_on_failure SMALLINT DEFAULT 0 NOT NULL, + std_error VARBINARY(max), + std_out VARBINARY(max), + output_log VARCHAR(255) NULL, + error_log VARCHAR(255) NULL, + structured_out VARBINARY(max), + role_command VARCHAR(255), + command_detail VARCHAR(255), + custom_command_name VARCHAR(255), + is_background SMALLINT DEFAULT 0 NOT NULL, ++ ops_display_name VARCHAR(255), + CONSTRAINT PK_host_role_command PRIMARY KEY CLUSTERED (task_id), + CONSTRAINT FK_host_role_command_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id), + CONSTRAINT FK_host_role_command_stage_id FOREIGN KEY (stage_id, request_id) REFERENCES stage (stage_id, request_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.execution_command') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('execution_command') AND type = 'U') +BEGIN +CREATE TABLE execution_command ( + command VARBINARY(MAX), + task_id BIGINT NOT NULL, + CONSTRAINT PK_execution_command PRIMARY KEY CLUSTERED (task_id), + CONSTRAINT FK_execution_command_task_id FOREIGN KEY (task_id) REFERENCES host_role_command (task_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.role_success_criteria') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('role_success_criteria') AND type = 'U') +BEGIN +CREATE TABLE role_success_criteria ( + ROLE VARCHAR(255) NOT NULL, + request_id BIGINT NOT NULL, + stage_id BIGINT NOT NULL, + success_factor FLOAT NOT NULL, + CONSTRAINT PK_role_success_criteria PRIMARY KEY CLUSTERED (ROLE, request_id, stage_id), + CONSTRAINT role_success_criteria_stage_id FOREIGN KEY (stage_id, request_id) REFERENCES stage (stage_id, request_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.requestresourcefilter') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('requestresourcefilter') AND type = 'U') +BEGIN +CREATE TABLE requestresourcefilter ( + filter_id BIGINT NOT NULL, + request_id BIGINT NOT NULL, + service_name VARCHAR(255), + component_name VARCHAR(255), + hosts VARBINARY(MAX), + CONSTRAINT PK_requestresourcefilter PRIMARY KEY CLUSTERED (filter_id), + CONSTRAINT FK_reqresfilter_req_id FOREIGN KEY (request_id) REFERENCES request (request_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.requestoperationlevel') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('requestoperationlevel') AND type = 'U') +BEGIN +CREATE TABLE requestoperationlevel ( + operation_level_id BIGINT NOT NULL, + request_id BIGINT NOT NULL, + level_name VARCHAR(255), + cluster_name VARCHAR(255), + service_name VARCHAR(255), + host_component_name VARCHAR(255), + host_id BIGINT NULL, -- unlike most host_id columns, this one allows NULLs because the request can be at the service level + CONSTRAINT PK_requestoperationlevel PRIMARY KEY CLUSTERED (operation_level_id), + CONSTRAINT FK_req_op_level_req_id FOREIGN KEY (request_id) REFERENCES request (request_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.ClusterHostMapping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('ClusterHostMapping') AND type = 'U') +BEGIN +CREATE TABLE ClusterHostMapping ( + cluster_id BIGINT NOT NULL, + host_id BIGINT NOT NULL, + CONSTRAINT PK_ClusterHostMapping PRIMARY KEY CLUSTERED (cluster_id, host_id), + CONSTRAINT FK_clhostmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id), + CONSTRAINT FK_clusterhostmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.key_value_store') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('key_value_store') AND type = 'U') +BEGIN +CREATE TABLE key_value_store ( + [key] VARCHAR(255), + [value] VARCHAR(MAX), + CONSTRAINT PK_key_value_store PRIMARY KEY CLUSTERED ([key]) + ) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostconfigmapping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostconfigmapping') AND type = 'U') +BEGIN +CREATE TABLE hostconfigmapping ( + cluster_id BIGINT NOT NULL, + host_id BIGINT 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, + user_name VARCHAR(255) NOT NULL DEFAULT '_db', + CONSTRAINT PK_hostconfigmapping PRIMARY KEY CLUSTERED (cluster_id, host_id, type_name, create_timestamp), + CONSTRAINT FK_hostconfmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id), + CONSTRAINT FK_hostconfmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.metainfo') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('metainfo') AND type = 'U') +BEGIN +CREATE TABLE metainfo ( + [metainfo_key] VARCHAR(255), + [metainfo_value] VARCHAR(255), + CONSTRAINT PK_metainfo PRIMARY KEY CLUSTERED ([metainfo_key]) + ) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.ambari_sequences') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('ambari_sequences') AND type = 'U') +BEGIN +CREATE TABLE ambari_sequences ( + sequence_name VARCHAR(255), + [sequence_value] BIGINT NOT NULL, + CONSTRAINT PK_ambari_sequences PRIMARY KEY (sequence_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.configgroup') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('configgroup') AND type = 'U') +BEGIN +CREATE TABLE 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, + service_name VARCHAR(255), + CONSTRAINT PK_configgroup PRIMARY KEY CLUSTERED (group_id), + CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.confgroupclusterconfigmapping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('confgroupclusterconfigmapping') AND type = 'U') +BEGIN +CREATE TABLE 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, + CONSTRAINT PK_confgroupclustercfgmapping PRIMARY KEY CLUSTERED (config_group_id, cluster_id, config_type), + CONSTRAINT FK_cgccm_gid FOREIGN KEY (config_group_id) REFERENCES configgroup (group_id), + CONSTRAINT FK_confg FOREIGN KEY (cluster_id, config_type, version_tag) REFERENCES clusterconfig (cluster_id, type_name, version_tag)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.configgrouphostmapping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('configgrouphostmapping') AND type = 'U') +BEGIN +CREATE TABLE configgrouphostmapping ( + config_group_id BIGINT NOT NULL, + host_id BIGINT NOT NULL, + CONSTRAINT PK_configgrouphostmapping PRIMARY KEY CLUSTERED (config_group_id, host_id), + CONSTRAINT FK_cghm_cgid FOREIGN KEY (config_group_id) REFERENCES configgroup (group_id), + CONSTRAINT FK_cghm_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.requestschedulebatchrequest') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('requestschedulebatchrequest') AND type = 'U') +BEGIN +CREATE TABLE requestschedulebatchrequest ( + schedule_id BIGINT, + batch_id BIGINT, + request_id BIGINT, + request_type VARCHAR(255), + request_uri VARCHAR(1024), + request_body VARBINARY(MAX), + request_status VARCHAR(255), + return_code SMALLINT, + return_message TEXT, + CONSTRAINT PK_requestschedulebatchrequest PRIMARY KEY CLUSTERED (schedule_id, batch_id), + CONSTRAINT FK_rsbatchrequest_schedule_id FOREIGN KEY (schedule_id) REFERENCES requestschedule (schedule_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.blueprint') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('blueprint') AND type = 'U') +BEGIN +CREATE TABLE blueprint ( + blueprint_name VARCHAR(255) NOT NULL, + stack_id BIGINT NOT NULL, + security_type VARCHAR(32) NOT NULL DEFAULT 'NONE', + security_descriptor_reference VARCHAR(255), + CONSTRAINT PK_blueprint PRIMARY KEY CLUSTERED (blueprint_name), + CONSTRAINT FK_blueprint_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostgroup') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostgroup') AND type = 'U') +BEGIN +CREATE TABLE hostgroup ( + blueprint_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + cardinality VARCHAR(255) NOT NULL, + CONSTRAINT PK_hostgroup PRIMARY KEY CLUSTERED (blueprint_name, NAME), + CONSTRAINT FK_hg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostgroup_component') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostgroup_component') AND type = 'U') +BEGIN +CREATE TABLE hostgroup_component ( + blueprint_name VARCHAR(255) NOT NULL, + hostgroup_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + provision_action VARCHAR(255), + CONSTRAINT PK_hostgroup_component PRIMARY KEY CLUSTERED (blueprint_name, hostgroup_name, NAME), + CONSTRAINT FK_hgc_blueprint_name FOREIGN KEY (blueprint_name, hostgroup_name) REFERENCES hostgroup (blueprint_name, name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.blueprint_configuration') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('blueprint_configuration') AND type = 'U') +BEGIN +CREATE TABLE blueprint_configuration ( + blueprint_name VARCHAR(255) NOT NULL, + type_name VARCHAR(255) NOT NULL, + config_data VARCHAR(MAX) NOT NULL, + config_attributes VARCHAR(MAX), + CONSTRAINT PK_blueprint_configuration PRIMARY KEY CLUSTERED (blueprint_name, type_name), + CONSTRAINT FK_cfg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.blueprint_setting') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('blueprint_setting') AND type = 'U') +BEGIN +CREATE TABLE blueprint_setting ( + id BIGINT NOT NULL, + blueprint_name VARCHAR(255) NOT NULL, + setting_name VARCHAR(255) NOT NULL, + setting_data TEXT NOT NULL, + CONSTRAINT PK_blueprint_setting PRIMARY KEY (id), + CONSTRAINT UQ_blueprint_setting_name UNIQUE(blueprint_name,setting_name), + CONSTRAINT FK_blueprint_setting_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name) + ) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.hostgroup_configuration') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('hostgroup_configuration') AND type = 'U') +BEGIN +CREATE TABLE hostgroup_configuration ( + blueprint_name VARCHAR(255) NOT NULL, + hostgroup_name VARCHAR(255) NOT NULL, + type_name VARCHAR(255) NOT NULL, + config_data VARCHAR(MAX) NOT NULL, + config_attributes VARCHAR(MAX), + CONSTRAINT PK_hostgroup_configuration PRIMARY KEY CLUSTERED (blueprint_name, hostgroup_name, type_name), + CONSTRAINT FK_hg_cfg_bp_hg_name FOREIGN KEY (blueprint_name, hostgroup_name) REFERENCES hostgroup (blueprint_name, name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewmain') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewmain') AND type = 'U') +BEGIN +CREATE TABLE viewmain ( + view_name VARCHAR(255) NOT NULL, + label VARCHAR(255), + description VARCHAR(2048), + version VARCHAR(255), + build VARCHAR(128), + resource_type_id INTEGER NOT NULL, + icon VARCHAR(255), + icon64 VARCHAR(255), + archive VARCHAR(255), + mask VARCHAR(255), + system_view BIT NOT NULL DEFAULT 0, + CONSTRAINT PK_viewmain PRIMARY KEY CLUSTERED (view_name), + CONSTRAINT FK_view_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id)) +END + + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewurl') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewurl') AND type = 'U') +BEGIN +CREATE table viewurl( + url_id BIGINT , + url_name VARCHAR(255) NOT NULL , + url_suffix VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (url_id) ++ CONSTRAINT PK_viewurl PRIMARY KEY CLUSTERED (url_id) +) +END + + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewinstance') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewinstance') AND type = 'U') +BEGIN +CREATE TABLE viewinstance ( + view_instance_id BIGINT, + resource_id BIGINT NOT NULL, + view_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + label VARCHAR(255), + description VARCHAR(2048), + visible CHAR(1), + icon VARCHAR(255), + icon64 VARCHAR(255), + xml_driven CHAR(1), + alter_names BIT NOT NULL DEFAULT 1, + cluster_handle BIGINT, + cluster_type VARCHAR(100) NOT NULL DEFAULT 'LOCAL_AMBARI', + short_url BIGINT, + CONSTRAINT PK_viewinstance PRIMARY KEY CLUSTERED (view_instance_id), + CONSTRAINT FK_instance_url_id FOREIGN KEY (short_url) REFERENCES viewurl(url_id), + CONSTRAINT FK_viewinst_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name), + CONSTRAINT FK_viewinstance_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id), + CONSTRAINT UQ_viewinstance_name UNIQUE (view_name, name), + CONSTRAINT UQ_viewinstance_name_id UNIQUE (view_instance_id, view_name, name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewinstancedata') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewinstancedata') AND type = 'U') +BEGIN +CREATE TABLE viewinstancedata ( + view_instance_id BIGINT, + view_name VARCHAR(255) NOT NULL, + view_instance_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + user_name VARCHAR(255) NOT NULL, + value VARCHAR(2000) NOT NULL, + CONSTRAINT PK_viewinstancedata PRIMARY KEY CLUSTERED (view_instance_id, NAME, user_name), + CONSTRAINT FK_viewinstdata_view_name FOREIGN KEY (view_instance_id, view_name, view_instance_name) REFERENCES viewinstance(view_instance_id, view_name, name)) +END + + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewinstanceproperty') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewinstanceproperty') AND type = 'U') +BEGIN +CREATE TABLE viewinstanceproperty ( + view_name VARCHAR(255) NOT NULL, + view_instance_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + value VARCHAR(2000), + CONSTRAINT PK_viewinstanceproperty PRIMARY KEY CLUSTERED (view_name, view_instance_name, NAME), + CONSTRAINT FK_viewinstprop_view_name FOREIGN KEY (view_name, view_instance_name) REFERENCES viewinstance(view_name, name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewparameter') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewparameter') AND type = 'U') +BEGIN +CREATE TABLE viewparameter ( + view_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + description VARCHAR(2048), + label VARCHAR(255), + placeholder VARCHAR(255), + default_value VARCHAR(2000), + cluster_config VARCHAR(255), + required CHAR(1), + masked CHAR(1), + CONSTRAINT PK_viewparameter PRIMARY KEY CLUSTERED (view_name, NAME), + CONSTRAINT FK_viewparam_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewresource') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewresource') AND type = 'U') +BEGIN +CREATE TABLE viewresource ( + view_name VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + plural_name VARCHAR(255), + id_property VARCHAR(255), + subResource_names VARCHAR(255), + provider VARCHAR(255), + service VARCHAR(255), + resource VARCHAR(255), + CONSTRAINT PK_viewresource PRIMARY KEY CLUSTERED (view_name, NAME), + CONSTRAINT FK_viewres_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.viewentity') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('viewentity') AND type = 'U') +BEGIN +CREATE TABLE viewentity ( + id BIGINT NOT NULL, + view_name VARCHAR(255) NOT NULL, + view_instance_name VARCHAR(255) NOT NULL, + class_name VARCHAR(255) NOT NULL, + id_property VARCHAR(255), + CONSTRAINT PK_viewentity PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_viewentity_view_name FOREIGN KEY (view_name, view_instance_name) REFERENCES viewinstance(view_name, name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminpermission') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminpermission') AND type = 'U') +BEGIN +CREATE TABLE adminpermission ( + permission_id BIGINT NOT NULL, + permission_name VARCHAR(255) NOT NULL, + resource_type_id INTEGER NOT NULL, + permission_label VARCHAR(255), + principal_id BIGINT NOT NULL, + sort_order SMALLINT NOT NULL DEFAULT 1, + CONSTRAINT PK_adminpermission PRIMARY KEY CLUSTERED (permission_id), + CONSTRAINT FK_permission_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id), + CONSTRAINT FK_permission_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id), + CONSTRAINT UQ_perm_name_resource_type_id UNIQUE (permission_name, resource_type_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.roleauthorization') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('roleauthorization') AND type = 'U') +BEGIN +CREATE TABLE roleauthorization ( + authorization_id VARCHAR(100) NOT NULL, + authorization_name VARCHAR(255) NOT NULL, + CONSTRAINT PK_roleauthorization PRIMARY KEY (authorization_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.permission_roleauthorization') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('permission_roleauthorization') AND type = 'U') +BEGIN +CREATE TABLE permission_roleauthorization ( + permission_id BIGINT NOT NULL, + authorization_id VARCHAR(100) NOT NULL, + CONSTRAINT PK_permsn_roleauthorization PRIMARY KEY (permission_id, authorization_id), + CONSTRAINT FK_permission_roleauth_aid FOREIGN KEY (authorization_id) REFERENCES roleauthorization(authorization_id), + CONSTRAINT FK_permission_roleauth_pid FOREIGN KEY (permission_id) REFERENCES adminpermission(permission_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.adminprivilege') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('adminprivilege') AND type = 'U') +BEGIN +CREATE TABLE adminprivilege ( + privilege_id BIGINT, + permission_id BIGINT NOT NULL, + resource_id BIGINT NOT NULL, + principal_id BIGINT NOT NULL, + CONSTRAINT PK_adminprivilege PRIMARY KEY CLUSTERED (privilege_id), + CONSTRAINT FK_privilege_permission_id FOREIGN KEY (permission_id) REFERENCES adminpermission(permission_id), + CONSTRAINT FK_privilege_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id), + CONSTRAINT FK_privilege_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.host_version') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('host_version') AND type = 'U') +BEGIN +CREATE TABLE host_version ( + id BIGINT NOT NULL, + repo_version_id BIGINT NOT NULL, + host_id BIGINT NOT NULL, + STATE VARCHAR(32) NOT NULL, + CONSTRAINT PK_host_version PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_host_version_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id), + CONSTRAINT FK_host_version_repovers_id FOREIGN KEY (repo_version_id) REFERENCES repo_version (repo_version_id), - CONSTRAINT UQ_host_repo UNIQUE(repo_version_id, host_id)) ++ CONSTRAINT UQ_host_repo UNIQUE(host_id, repo_version_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.artifact') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('artifact') AND type = 'U') +BEGIN +CREATE TABLE artifact ( + artifact_name VARCHAR(255) NOT NULL, + artifact_data TEXT NOT NULL, + foreign_keys VARCHAR(255) NOT NULL, + CONSTRAINT PK_artifact PRIMARY KEY CLUSTERED (artifact_name, foreign_keys) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.widget') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('widget') AND type = 'U') +BEGIN +CREATE TABLE widget ( + id BIGINT NOT NULL, + widget_name VARCHAR(255) NOT NULL, + widget_type VARCHAR(255) NOT NULL, + metrics TEXT, + time_created BIGINT NOT NULL, + author VARCHAR(255), + description VARCHAR(2048), + default_section_name VARCHAR(255), + scope VARCHAR(255), + widget_values VARCHAR(4000), + properties VARCHAR(4000), + cluster_id BIGINT NOT NULL, + CONSTRAINT PK_widget PRIMARY KEY CLUSTERED (id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.widget_layout') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('widget_layout') AND type = 'U') +BEGIN +CREATE TABLE widget_layout ( + id BIGINT NOT NULL, + layout_name VARCHAR(255) NOT NULL, + section_name VARCHAR(255) NOT NULL, + scope VARCHAR(255) NOT NULL, + user_name VARCHAR(255) NOT NULL, + display_name VARCHAR(255), + cluster_id BIGINT NOT NULL, + CONSTRAINT PK_widget_layout PRIMARY KEY CLUSTERED (id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.widget_layout_user_widget') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('widget_layout_user_widget') AND type = 'U') +BEGIN +CREATE TABLE widget_layout_user_widget ( + widget_layout_id BIGINT NOT NULL, + widget_id BIGINT NOT NULL, + widget_order smallint, + CONSTRAINT PK_widget_layout_user_widget PRIMARY KEY CLUSTERED (widget_layout_id, widget_id), + CONSTRAINT FK_widget_id FOREIGN KEY (widget_id) REFERENCES widget(id), + CONSTRAINT FK_widget_layout_id FOREIGN KEY (widget_layout_id) REFERENCES widget_layout(id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_request') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_request') AND type = 'U') +BEGIN +CREATE TABLE topology_request ( + id BIGINT NOT NULL, + action VARCHAR(255) NOT NULL, + cluster_id BIGINT NOT NULL, + bp_name VARCHAR(100) NOT NULL, + cluster_properties TEXT, + cluster_attributes TEXT, + description VARCHAR(1024), + provision_action VARCHAR(255), + CONSTRAINT PK_topology_request PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_topology_request_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_hostgroup') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_hostgroup') AND type = 'U') +BEGIN +CREATE TABLE topology_hostgroup ( + id BIGINT NOT NULL, + name VARCHAR(255) NOT NULL, + group_properties TEXT, + group_attributes TEXT, + request_id BIGINT NOT NULL, + CONSTRAINT PK_topology_hostgroup PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hostgroup_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_host_info') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_host_info') AND type = 'U') +BEGIN +CREATE TABLE topology_host_info ( + id BIGINT NOT NULL, + group_id BIGINT NOT NULL, + fqdn VARCHAR(255), + host_id BIGINT, + host_count INTEGER, + predicate VARCHAR(2048), + rack_info VARCHAR(255), + CONSTRAINT PK_topology_host_info PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hostinfo_group_id FOREIGN KEY (group_id) REFERENCES topology_hostgroup(id), + CONSTRAINT FK_hostinfo_host_id FOREIGN KEY (host_id) REFERENCES hosts(host_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_logical_request') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_logical_request') AND type = 'U') +BEGIN +CREATE TABLE topology_logical_request ( + id BIGINT NOT NULL, + request_id BIGINT NOT NULL, + description VARCHAR(1024), + CONSTRAINT PK_topology_logical_request PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_logicalreq_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_host_request') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_host_request') AND type = 'U') +BEGIN +CREATE TABLE topology_host_request ( + id BIGINT NOT NULL, + logical_request_id BIGINT NOT NULL, + group_id BIGINT NOT NULL, + stage_id BIGINT NOT NULL, + host_name VARCHAR(255), + CONSTRAINT PK_topology_host_request PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hostreq_group_id FOREIGN KEY (group_id) REFERENCES topology_hostgroup(id), + CONSTRAINT FK_hostreq_logicalreq_id FOREIGN KEY (logical_request_id) REFERENCES topology_logical_request(id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_host_task') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_host_task') AND type = 'U') +BEGIN +CREATE TABLE topology_host_task ( + id BIGINT NOT NULL, + host_request_id BIGINT NOT NULL, + type VARCHAR(255) NOT NULL, + CONSTRAINT PK_topology_host_task PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hosttask_req_id FOREIGN KEY (host_request_id) REFERENCES topology_host_request (id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.topology_logical_task') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('topology_logical_task') AND type = 'U') +BEGIN +CREATE TABLE topology_logical_task ( + id BIGINT NOT NULL, + host_task_id BIGINT NOT NULL, + physical_task_id BIGINT, + component VARCHAR(255) NOT NULL, + CONSTRAINT PK_topology_logical_task PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_ltask_hosttask_id FOREIGN KEY (host_task_id) REFERENCES topology_host_task (id), + CONSTRAINT FK_ltask_hrc_id FOREIGN KEY (physical_task_id) REFERENCES host_role_command (task_id)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.setting') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('setting') AND type = 'U') +BEGIN +CREATE TABLE setting ( + id BIGINT NOT NULL, + name VARCHAR(255) NOT NULL UNIQUE, + setting_type VARCHAR(255) NOT NULL, + content TEXT NOT NULL, + updated_by VARCHAR(255) NOT NULL DEFAULT '_db', + update_timestamp BIGINT NOT NULL, + CONSTRAINT PK_setting PRIMARY KEY (id) +) +END + + +-- Remote Cluster table + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.remoteambaricluster') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('remoteambaricluster') AND type = 'U') +BEGIN +CREATE TABLE remoteambaricluster( + cluster_id BIGINT NOT NULL, + name VARCHAR(255) NOT NULL, + username VARCHAR(255) NOT NULL, + url VARCHAR(255) NOT NULL, + password VARCHAR(255) NOT NULL, + CONSTRAINT PK_remote_ambari_cluster PRIMARY KEY (cluster_id), + CONSTRAINT UQ_remote_ambari_cluster UNIQUE (name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.remoteambariclusterservice') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('remoteambariclusterservice') AND type = 'U') +BEGIN +CREATE TABLE remoteambariclusterservice( + id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + service_name VARCHAR(255) NOT NULL, + CONSTRAINT PK_remote_ambari_service PRIMARY KEY (id), + CONSTRAINT FK_remote_ambari_cluster_id FOREIGN KEY (cluster_id) REFERENCES remoteambaricluster(cluster_id) +) +END + + +-- Remote Cluster table ends + +-- upgrade tables - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.upgrade') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('upgrade') AND type = 'U') +BEGIN +CREATE TABLE upgrade ( + upgrade_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + request_id BIGINT NOT NULL, - from_version VARCHAR(255) DEFAULT '' NOT NULL, - to_version VARCHAR(255) DEFAULT '' NOT NULL, + direction VARCHAR(255) DEFAULT 'UPGRADE' NOT NULL, ++ orchestration VARCHAR(255) DEFAULT 'STANDARD' NOT NULL, + upgrade_package VARCHAR(255) NOT NULL, + upgrade_type VARCHAR(32) NOT NULL, ++ repo_version_id BIGINT NOT NULL, + skip_failures BIT NOT NULL DEFAULT 0, + skip_sc_failures BIT NOT NULL DEFAULT 0, + downgrade_allowed BIT NOT NULL DEFAULT 1, ++ revert_allowed BIT NOT NULL DEFAULT 0, + suspended BIT DEFAULT 0 NOT NULL, + CONSTRAINT PK_upgrade PRIMARY KEY CLUSTERED (upgrade_id), + FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id), - FOREIGN KEY (request_id) REFERENCES request(request_id) ++ FOREIGN KEY (request_id) REFERENCES request(request_id), ++ FOREIGN KEY (repo_version_id) REFERENCES repo_version(repo_version_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.upgrade_group') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('upgrade_group') AND type = 'U') +BEGIN +CREATE TABLE upgrade_group ( + upgrade_group_id BIGINT NOT NULL, + upgrade_id BIGINT NOT NULL, + group_name VARCHAR(255) DEFAULT '' NOT NULL, + group_title VARCHAR(1024) DEFAULT '' NOT NULL, + CONSTRAINT PK_upgrade_group PRIMARY KEY CLUSTERED (upgrade_group_id), + FOREIGN KEY (upgrade_id) REFERENCES upgrade(upgrade_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.upgrade_item') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('upgrade_item') AND type = 'U') +BEGIN +CREATE TABLE upgrade_item ( + upgrade_item_id BIGINT NOT NULL, + upgrade_group_id BIGINT NOT NULL, + stage_id BIGINT NOT NULL, + state VARCHAR(255) DEFAULT 'NONE' NOT NULL, + hosts TEXT, + tasks TEXT, + item_text VARCHAR(1024), + CONSTRAINT PK_upgrade_item PRIMARY KEY CLUSTERED (upgrade_item_id), + FOREIGN KEY (upgrade_group_id) REFERENCES upgrade_group(upgrade_group_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.servicecomponent_history') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('upgrade_history') AND type = 'U') +BEGIN - CREATE TABLE servicecomponent_history( ++CREATE TABLE upgrade_history( + id BIGINT NOT NULL, - component_id BIGINT NOT NULL, + upgrade_id BIGINT NOT NULL, - from_stack_id BIGINT NOT NULL, - to_stack_id BIGINT NOT NULL, - CONSTRAINT PK_sc_history PRIMARY KEY (id), - CONSTRAINT FK_sc_history_component_id FOREIGN KEY (component_id) REFERENCES servicecomponentdesiredstate (id), - CONSTRAINT FK_sc_history_upgrade_id FOREIGN KEY (upgrade_id) REFERENCES upgrade (upgrade_id), - CONSTRAINT FK_sc_history_from_stack_id FOREIGN KEY (from_stack_id) REFERENCES stack (stack_id), - CONSTRAINT FK_sc_history_to_stack_id FOREIGN KEY (to_stack_id) REFERENCES stack (stack_id) ++ service_name VARCHAR(255) NOT NULL, ++ component_name VARCHAR(255) NOT NULL, ++ from_repo_version_id BIGINT NOT NULL, ++ target_repo_version_id BIGINT NOT NULL, ++ CONSTRAINT PK_upgrade_hist PRIMARY KEY (id), ++ CONSTRAINT FK_upgrade_hist_upgrade_id FOREIGN KEY (upgrade_id) REFERENCES upgrade (upgrade_id), ++ CONSTRAINT FK_upgrade_hist_from_repo FOREIGN KEY (from_repo_version_id) REFERENCES repo_version (repo_version_id), ++ CONSTRAINT FK_upgrade_hist_target_repo FOREIGN KEY (target_repo_version_id) REFERENCES repo_version (repo_version_id), ++ CONSTRAINT UQ_upgrade_hist UNIQUE (upgrade_id, component_name, service_name) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.servicecomponent_version') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('servicecomponent_version') AND type = 'U') +BEGIN +CREATE TABLE servicecomponent_version( + id BIGINT NOT NULL, + component_id BIGINT NOT NULL, + repo_version_id BIGINT NOT NULL, + state VARCHAR(32) NOT NULL, + user_name VARCHAR(255) NOT NULL, + CONSTRAINT PK_sc_version PRIMARY KEY (id), + CONSTRAINT FK_scv_component_id FOREIGN KEY (component_id) REFERENCES servicecomponentdesiredstate (id), + CONSTRAINT FK_scv_repo_version_id FOREIGN KEY (repo_version_id) REFERENCES repo_version (repo_version_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.ambari_operation_history') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('ambari_operation_history') AND type = 'U') +BEGIN +CREATE TABLE ambari_operation_history( + id BIGINT NOT NULL, + from_version VARCHAR(255) NOT NULL, + to_version VARCHAR(255) NOT NULL, + start_time BIGINT NOT NULL, + end_time BIGINT, + operation_type VARCHAR(255) NOT NULL, + comments TEXT, + CONSTRAINT PK_ambari_operation_history PRIMARY KEY (id) +) +END + + + +-- tasks indices -- +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_stage_request_id') +BEGIN +CREATE INDEX idx_stage_request_id ON stage (request_id) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_hrc_request_id') +BEGIN +CREATE INDEX idx_hrc_request_id ON host_role_command (request_id) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_hrc_status_role') +BEGIN +CREATE INDEX idx_hrc_status_role ON host_role_command (status, role) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_rsc_request_id') +BEGIN +CREATE INDEX idx_rsc_request_id ON role_success_criteria (request_id) +END + + + +-- altering tables by creating unique constraints---------- +--------altering tables to add constraints---------- + +-- altering tables by creating foreign keys---------- +-- Note, Oracle has a limitation of 32 chars in the FK name, and we should use the same FK name in all DB types. +IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('FK_clusters_upgrade_id') AND type = 'F') +BEGIN +ALTER TABLE clusters ADD CONSTRAINT FK_clusters_upgrade_id FOREIGN KEY (upgrade_id) REFERENCES upgrade (upgrade_id) +END + + +-- Kerberos - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.kerberos_principal') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('kerberos_principal') AND type = 'U') +BEGIN +CREATE TABLE kerberos_principal ( + principal_name VARCHAR(255) NOT NULL, + is_service SMALLINT NOT NULL DEFAULT 1, + cached_keytab_path VARCHAR(255), + CONSTRAINT PK_kerberos_principal PRIMARY KEY CLUSTERED (principal_name) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.kerberos_principal_host') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('kerberos_principal_host') AND type = 'U') +BEGIN +CREATE TABLE kerberos_principal_host ( + principal_name VARCHAR(255) NOT NULL, + host_id BIGINT NOT NULL, + CONSTRAINT PK_kerberos_principal_host PRIMARY KEY CLUSTERED (principal_name, host_id), + CONSTRAINT FK_krb_pr_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id), + CONSTRAINT FK_krb_pr_host_principalname FOREIGN KEY (principal_name) REFERENCES kerberos_principal (principal_name)) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.kerberos_descriptor') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('kerberos_descriptor') AND type = 'U') +BEGIN +CREATE TABLE kerberos_descriptor +( + kerberos_descriptor_name VARCHAR(255) NOT NULL, + kerberos_descriptor VARCHAR(MAX) NOT NULL, + CONSTRAINT PK_kerberos_descriptor PRIMARY KEY (kerberos_descriptor_name) +) +END + + +-- Kerberos (end) + +-- Alerting Framework - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_definition') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_definition') AND type = 'U') +BEGIN +CREATE TABLE alert_definition ( + definition_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + definition_name VARCHAR(255) NOT NULL, + service_name VARCHAR(255) NOT NULL, + component_name VARCHAR(255), + scope VARCHAR(255) DEFAULT 'ANY' NOT NULL, + label VARCHAR(255), + help_url VARCHAR(512), + description TEXT, + enabled SMALLINT DEFAULT 1 NOT NULL, + schedule_interval INTEGER NOT NULL, + source_type VARCHAR(255) NOT NULL, + alert_source TEXT NOT NULL, + hash VARCHAR(64) NOT NULL, + ignore_host SMALLINT DEFAULT 0 NOT NULL, + repeat_tolerance INTEGER DEFAULT 1 NOT NULL, + repeat_tolerance_enabled SMALLINT DEFAULT 0 NOT NULL, + CONSTRAINT PK_alert_definition PRIMARY KEY CLUSTERED (definition_id), + FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id), + CONSTRAINT uni_alert_def_name UNIQUE(cluster_id,definition_name) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_history') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_history') AND type = 'U') +BEGIN +CREATE TABLE alert_history ( + alert_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + alert_definition_id BIGINT NOT NULL, + service_name VARCHAR(255) NOT NULL, + component_name VARCHAR(255), + host_name VARCHAR(255), + alert_instance VARCHAR(255), + alert_timestamp BIGINT NOT NULL, + alert_label VARCHAR(1024), + alert_state VARCHAR(255) NOT NULL, + alert_text TEXT, + CONSTRAINT PK_alert_history PRIMARY KEY CLUSTERED (alert_id), + FOREIGN KEY (alert_definition_id) REFERENCES alert_definition(definition_id), + FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_current') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_current') AND type = 'U') +BEGIN +CREATE TABLE alert_current ( + alert_id BIGINT NOT NULL, + definition_id BIGINT NOT NULL, + history_id BIGINT NOT NULL UNIQUE, + maintenance_state VARCHAR(255) NOT NULL, + original_timestamp BIGINT NOT NULL, + latest_timestamp BIGINT NOT NULL, + latest_text TEXT, + occurrences BIGINT NOT NULL DEFAULT 1, + firmness VARCHAR(255) NOT NULL DEFAULT 'HARD', + CONSTRAINT PK_alert_current PRIMARY KEY CLUSTERED (alert_id), + FOREIGN KEY (definition_id) REFERENCES alert_definition(definition_id), + FOREIGN KEY (history_id) REFERENCES alert_history(alert_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_group') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_group') AND type = 'U') +BEGIN +CREATE TABLE alert_group ( + group_id BIGINT NOT NULL, + cluster_id BIGINT NOT NULL, + group_name VARCHAR(255) NOT NULL, + is_default SMALLINT NOT NULL DEFAULT 0, + service_name VARCHAR(255), + CONSTRAINT PK_alert_group PRIMARY KEY CLUSTERED (group_id), + CONSTRAINT uni_alert_group_name UNIQUE(cluster_id,group_name) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_target') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_target') AND type = 'U') +BEGIN +CREATE TABLE alert_target ( + target_id BIGINT NOT NULL, + target_name VARCHAR(255) NOT NULL UNIQUE, + notification_type VARCHAR(64) NOT NULL, + properties TEXT, + description VARCHAR(1024), + is_global SMALLINT NOT NULL DEFAULT 0, + is_enabled SMALLINT NOT NULL DEFAULT 1, + CONSTRAINT PK_alert_target PRIMARY KEY CLUSTERED (target_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_target_states') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_target_states') AND type = 'U') +BEGIN +CREATE TABLE alert_target_states ( + target_id BIGINT NOT NULL, + alert_state VARCHAR(255) NOT NULL, + FOREIGN KEY (target_id) REFERENCES alert_target(target_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_group_target') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_group_target') AND type = 'U') +BEGIN +CREATE TABLE alert_group_target ( + group_id BIGINT NOT NULL, + target_id BIGINT NOT NULL, + CONSTRAINT PK_alert_group_target PRIMARY KEY CLUSTERED (group_id, target_id), + FOREIGN KEY (group_id) REFERENCES alert_group(group_id), + FOREIGN KEY (target_id) REFERENCES alert_target(target_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_grouping') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_grouping') AND type = 'U') +BEGIN +CREATE TABLE alert_grouping ( + definition_id BIGINT NOT NULL, + group_id BIGINT NOT NULL, + CONSTRAINT PK_alert_grouping PRIMARY KEY CLUSTERED (group_id, definition_id), + FOREIGN KEY (definition_id) REFERENCES alert_definition(definition_id), + FOREIGN KEY (group_id) REFERENCES alert_group(group_id) +) +END + + - IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.alert_notice') AND type = 'U') ++IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('alert_notice') AND type = 'U') +BEGIN +CREATE TABLE alert_notice ( + notification_id BIGINT NOT NULL, + target_id BIGINT NOT NULL, + history_id BIGINT NOT NULL, + notify_state VARCHAR(255) NOT NULL, + uuid VARCHAR(64) NOT NULL UNIQUE, + CONSTRAINT PK_alert_notice PRIMARY KEY CLUSTERED (notification_id), + FOREIGN KEY (target_id) REFERENCES alert_target(target_id), + FOREIGN KEY (history_id) REFERENCES alert_history(alert_id) +) +END + + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_history_def_id') +BEGIN +CREATE INDEX idx_alert_history_def_id on alert_history(alert_definition_id) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_history_service') +BEGIN +CREATE INDEX idx_alert_history_service on alert_history(service_name) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_history_host') +BEGIN +CREATE INDEX idx_alert_history_host on alert_history(host_name) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_history_time') +BEGIN +CREATE INDEX idx_alert_history_time on alert_history(alert_timestamp) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_history_state') +BEGIN +CREATE INDEX idx_alert_history_state on alert_history(alert_state) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_group_name') +BEGIN +CREATE INDEX idx_alert_group_name on alert_group(group_name) +END + +IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_alert_notice_state') +BEGIN +CREATE INDEX idx_alert_notice_state on alert_notice(notify_state) +END + + +---------inserting some data----------- +BEGIN TRANSACTION + DELETE metainfo; + DELETE adminprivilege; + DELETE permission_roleauthorization; + DELETE roleauthorization; + DELETE adminpermission; + DELETE users; + DELETE adminprincipal; + DELETE adminprincipaltype; + DELETE adminresource; + DELETE adminresourcetype; + DELETE ambari_sequences; + INSERT INTO ambari_sequences (sequence_name, [sequence_value]) + VALUES + ('cluster_id_seq', 1), + ('host_id_seq', 0), + ('user_id_seq', 2), + ('group_id_seq', 1), + ('member_id_seq', 1), + ('host_role_command_id_seq', 1), + ('configgroup_id_seq', 1), + ('requestschedule_id_seq', 1), + ('resourcefilter_id_seq', 1), + ('viewentity_id_seq', 0), + ('operation_level_id_seq', 1), + ('view_instance_id_seq', 1), + ('resource_type_id_seq', 4), + ('resource_id_seq', 2), + ('principal_type_id_seq', 8), + ('principal_id_seq', 13), + ('permission_id_seq', 7), + ('privilege_id_seq', 1), + ('alert_definition_id_seq', 0), + ('alert_group_id_seq', 0), + ('alert_target_id_seq', 0), + ('alert_history_id_seq', 0), + ('alert_notice_id_seq', 0), + ('alert_current_id_seq', 0), + ('config_id_seq', 11), + ('repo_version_id_seq', 0), - ('cluster_version_id_seq', 0), + ('host_version_id_seq', 0), + ('service_config_id_seq', 1), + ('upgrade_id_seq', 0), + ('upgrade_group_id_seq', 0), + ('widget_id_seq', 0), + ('widget_layout_id_seq', 0), + ('upgrade_item_id_seq', 0), + ('stack_id_seq', 0), + ('extension_id_seq', 0), + ('link_id_seq', 0), + ('topology_host_info_id_seq', 0), + ('topology_host_request_id_seq', 0), + ('topology_host_task_id_seq', 0), + ('topology_logical_request_id_seq', 0), + ('topology_logical_task_id_seq', 0), + ('topology_request_id_seq', 0), + ('topology_host_group_id_seq', 0), + ('setting_id_seq', 0), + ('hostcomponentstate_id_seq', 0), + ('servicecomponentdesiredstate_id_seq', 0), - ('servicecomponent_history_id_seq', 0), ++ ('upgrade_history_id_seq', 0), + ('blueprint_setting_id_seq', 0), + ('ambari_operation_history_id_seq', 0), + ('remote_cluster_id_seq', 0), + ('remote_cluster_service_id_seq', 0), + ('servicecomponent_version_id_seq', 0), + ('hostcomponentdesiredstate_id_seq', 0) + + insert into adminresourcetype (resource_type_id, resource_type_name) + values + (1, 'AMBARI'), + (2, 'CLUSTER'), + (3, 'VIEW') + + insert into adminresource (resource_id, resource_type_id) + select 1, 1 + + insert into adminprincipaltype (principal_type_id, principal_type_name) + values + (1, 'USER'), + (2, 'GROUP'), + (8, 'ROLE') + + insert into adminprincipal (principal_id, principal_type_id) + values + (1, 1), + (7, 8), + (8, 8), + (9, 8), + (10, 8), + (11, 8), + (12, 8), + (13, 8) + + insert into users(user_id, principal_id, user_name, user_password) + select 1, 1, 'admin','538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00' + + insert into adminpermission(permission_id, permission_name, resource_type_id, permission_label, principal_id, sort_order) + values + (1, 'AMBARI.ADMINISTRATOR', 1, 'Ambari Administrator', 7, 1), + (2, 'CLUSTER.USER', 2, 'Cluster User', 8, 6), + (3, 'CLUSTER.ADMINISTRATOR', 2, 'Cluster Administrator', 9, 2), + (4, 'VIEW.USER', 3, 'View User', 10, 7), + (5, 'CLUSTER.OPERATOR', 2, 'Cluster Operator', 11, 3), + (6, 'SERVICE.ADMINISTRATOR', 2, 'Service Administrator', 12, 4), + (7, 'SERVICE.OPERATOR', 2, 'Service Operator', 13, 5) + + INSERT INTO roleauthorization(authorization_id, authorization_name) + SELECT 'VIEW.USE', 'Use View' UNION ALL + SELECT 'SERVICE.VIEW_METRICS', 'View metrics' UNION ALL + SELECT 'SERVICE.VIEW_STATUS_INFO', 'View status information' UNION ALL + SELECT 'SERVICE.VIEW_CONFIGS', 'View configurations' UNION ALL + SELECT 'SERVICE.COMPARE_CONFIGS', 'Compare configurations' UNION ALL + SELECT 'SERVICE.VIEW_ALERTS', 'View service-level alerts' UNION ALL + SELECT 'SERVICE.START_STOP', 'Start/Stop/Restart Service' UNION ALL + SELECT 'SERVICE.DECOMMISSION_RECOMMISSION', 'Decommission/recommission' UNION ALL + SELECT 'SERVICE.RUN_SERVICE_CHECK', 'Run service checks' UNION ALL + SELECT 'SERVICE.TOGGLE_MAINTENANCE', 'Turn on/off maintenance mode' UNION ALL + SELECT 'SERVICE.RUN_CUSTOM_COMMAND', 'Perform service-specific tasks' UNION ALL + SELECT 'SERVICE.MODIFY_CONFIGS', 'Modify configurations' UNION ALL + SELECT 'SERVICE.MANAGE_ALERTS', 'Manage service-level alerts' UNION ALL + SELECT 'SERVICE.MANAGE_CONFIG_GROUPS', 'Manage configuration groups' UNION ALL + SELECT 'SERVICE.MOVE', 'Move service to another host' UNION ALL + SELECT 'SERVICE.ENABLE_HA', 'Enable HA' UNION ALL + SELECT 'SERVICE.TOGGLE_ALERTS', 'Enable/disable service-level alerts' UNION ALL + SELECT 'SERVICE.ADD_DELETE_SERVICES', 'Add/delete services' UNION ALL + SELECT 'SERVICE.VIEW_OPERATIONAL_LOGS', 'View service operational logs' UNION ALL + SELECT 'SERVICE.SET_SERVICE_USERS_GROUPS', 'Set service users and groups' UNION ALL + SELECT 'SERVICE.MANAGE_AUTO_START', 'Manage service auto-start' UNION ALL + SELECT 'HOST.VIEW_METRICS', 'View metrics' UNION ALL + SELECT 'HOST.VIEW_STATUS_INFO', 'View status information' UNION ALL + SELECT 'HOST.VIEW_CONFIGS', 'View configuration' UNION ALL + SELECT 'HOST.TOGGLE_MAINTENANCE', 'Turn on/off maintenance mode' UNION ALL + SELECT 'HOST.ADD_DELETE_COMPONENTS', 'Install components' UNION ALL + SELECT 'HOST.ADD_DELETE_HOSTS', 'Add/Delete hosts' UNION ALL + SELECT 'CLUSTER.VIEW_METRICS', 'View metrics' UNION ALL + SELECT 'CLUSTER.VIEW_STATUS_INFO', 'View status information' UNION ALL + SELECT 'CLUSTER.VIEW_CONFIGS', 'View configuration' UNION ALL + SELECT 'CLUSTER.VIEW_STACK_DETAILS', 'View stack version details' UNION ALL + SELECT 'CLUSTER.VIEW_ALERTS', 'View cluster-level alerts' UNION ALL + SELECT 'CLUSTER.MANAGE_CREDENTIALS', 'Manage external credentials' UNION ALL + SELECT 'CLUSTER.MODIFY_CONFIGS', 'Modify cluster configurations' UNION ALL + SELECT 'CLUSTER.MANAGE_ALERTS', 'Manage cluster-level alerts' UNION ALL + SELECT 'CLUSTER.MANAGE_USER_PERSISTED_DATA', 'Manage cluster-level user persisted data' UNION ALL + SELECT 'CLUSTER.TOGGLE_ALERTS', 'Enable/disable cluster-level alerts' UNION ALL + SELECT 'CLUSTER.MANAGE_CONFIG_GROUPS', 'Manage cluster config groups' UNION ALL + SELECT 'CLUSTER.TOGGLE_KERBEROS', 'Enable/disable Kerberos' UNION ALL + SELECT 'CLUSTER.UPGRADE_DOWNGRADE_STACK', 'Upgrade/downgrade stack' UNION ALL + SELECT 'CLUSTER.RUN_CUSTOM_COMMAND', 'Perform custom cluster-level actions' UNION ALL + SELECT 'CLUSTER.MANAGE_AUTO_START', 'Manage service auto-start configuration' UNION ALL + SELECT 'CLUSTER.MANAGE_ALERT_NOTIFICATIONS', 'Manage alert notifications configuration' UNION ALL + SELECT 'AMBARI.ADD_DELETE_CLUSTERS', 'Create new clusters' UNION ALL + SELECT 'AMBARI.RENAME_CLUSTER', 'Rename clusters' UNION ALL + SELECT 'AMBARI.MANAGE_SETTINGS', 'Manage settings' UNION ALL + SELECT 'AMBARI.MANAGE_USERS', 'Manage users' UNION ALL + SELECT 'AMBARI.MANAGE_GROUPS', 'Manage groups' UNION ALL + SELECT 'AMBARI.MANAGE_VIEWS', 'Manage Ambari Views' UNION ALL + SELECT 'AMBARI.ASSIGN_ROLES', 'Assign roles' UNION ALL + SELECT 'AMBARI.MANAGE_STACK_VERSIONS', 'Manage stack versions' UNION ALL + SELECT 'AMBARI.EDIT_STACK_REPOS', 'Edit stack repository URLs' UNION ALL + SELECT 'AMBARI.RUN_CUSTOM_COMMAND', 'Perform custom administrative actions' + + -- Set authorizations for View User role + INSERT INTO permission_roleauthorization(permission_id, authorization_id) + SELECT permission_id, 'VIEW.USE' FROM adminpermission WHERE permission_name='VIEW.USER' + + -- Set authorizations for Cluster User role + INSERT INTO permission_roleauthorization(permission_id, authorization_id) + SELECT permission_id, 'SERVICE.VIEW_METRICS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'SERVICE.COMPARE_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_ALERTS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'HOST.VIEW_METRICS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'HOST.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'HOST.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_METRICS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STACK_DETAILS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_ALERTS' FROM adminpermission WHERE permission_name='CLUSTER.USER' UNION ALL + SELECT permission_id, 'CLUSTER.MANAGE_USER_PERSISTED_DATA' FROM adminpermission WHERE permission_name='CLUSTER.USER' + + -- Set authorizations for Service Operator role + INSERT INTO permission_roleauthorization(permission_id, authorization_id) + SELECT permission_id, 'SERVICE.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.COMPARE_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_ALERTS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.START_STOP' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.DECOMMISSION_RECOMMISSION' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_SERVICE_CHECK' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.TOGGLE_MAINTENANCE' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_CUSTOM_COMMAND' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STACK_DETAILS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_ALERTS' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' UNION ALL + SELECT permission_id, 'CLUSTER.MANAGE_USER_PERSISTED_DATA' FROM adminpermission WHERE permission_name='SERVICE.OPERATOR' + + -- Set authorizations for Service Administrator role + INSERT INTO permission_roleauthorization(permission_id, authorization_id) + SELECT permission_id, 'SERVICE.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.COMPARE_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_ALERTS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.START_STOP' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.DECOMMISSION_RECOMMISSION' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_SERVICE_CHECK' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.TOGGLE_MAINTENANCE' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_CUSTOM_COMMAND' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.MODIFY_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.MANAGE_CONFIG_GROUPS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_OPERATIONAL_LOGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'SERVICE.MANAGE_AUTO_START' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_METRICS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_STACK_DETAILS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.MANAGE_CONFIG_GROUPS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.VIEW_ALERTS' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' UNION ALL + SELECT permission_id, 'CLUSTER.MANAGE_USER_PERSISTED_DATA' FROM adminpermission WHERE permission_name='SERVICE.ADMINISTRATOR' + + -- Set authorizations for Cluster Operator role + INSERT INTO permission_roleauthorization(permission_id, authorization_id) + SELECT permission_id, 'SERVICE.VIEW_METRICS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.COMPARE_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_ALERTS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.START_STOP' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.DECOMMISSION_RECOMMISSION' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_SERVICE_CHECK' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.TOGGLE_MAINTENANCE' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.RUN_CUSTOM_COMMAND' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.MODIFY_CONFIGS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.MANAGE_CONFIG_GROUPS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.MOVE' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.ENABLE_HA' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.VIEW_OPERATIONAL_LOGS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'SERVICE.MANAGE_AUTO_START' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_METRICS' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id, 'HOST.VIEW_STATUS_INFO' FROM adminpermission WHERE permission_name='CLUSTER.OPERATOR' UNION ALL + SELECT permission_id
<TRUNCATED>
