http://git-wip-us.apache.org/repos/asf/ambari/blob/346dfe7e/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql index a3ea10d..c85ae46 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql @@ -33,8 +33,20 @@ CREATE TABLE stack( stack_id BIGINT NOT NULL, stack_name VARCHAR(255) NOT NULL, stack_version VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (stack_id) -); + CONSTRAINT PK_stack PRIMARY KEY CLUSTERED (stack_id), + CONSTRAINT unq_stack UNIQUE (stack_name, stack_version)); + +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) + ); + +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)); CREATE TABLE clusters ( cluster_id BIGINT NOT NULL, @@ -46,8 +58,9 @@ CREATE TABLE clusters ( security_type VARCHAR(32) NOT NULL DEFAULT 'NONE', desired_cluster_state VARCHAR(255) NOT NULL, desired_stack_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (cluster_id) - ); + 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)); CREATE TABLE clusterconfig ( config_id BIGINT NOT NULL, @@ -59,8 +72,11 @@ CREATE TABLE clusterconfig ( config_data VARCHAR(MAX) NOT NULL, config_attributes VARCHAR(MAX), create_timestamp BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (config_id) - ); + 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)); CREATE TABLE serviceconfig ( service_config_id BIGINT NOT NULL, @@ -72,20 +88,43 @@ CREATE TABLE serviceconfig ( user_name VARCHAR(255) NOT NULL DEFAULT '_db', group_id BIGINT, note VARCHAR(MAX), - PRIMARY KEY CLUSTERED (service_config_id) - ); + 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)); + +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)); CREATE TABLE serviceconfighosts ( service_config_id BIGINT NOT NULL, host_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (service_config_id, host_id) - ); + 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)); CREATE TABLE serviceconfigmapping ( service_config_id BIGINT NOT NULL, config_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (service_config_id, config_id) - ); + 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)); CREATE TABLE clusterconfigmapping ( cluster_id BIGINT NOT NULL, @@ -94,22 +133,39 @@ CREATE TABLE clusterconfigmapping ( create_timestamp BIGINT NOT NULL, selected INT NOT NULL DEFAULT 0, user_name VARCHAR(255) NOT NULL DEFAULT '_db', - PRIMARY KEY CLUSTERED (cluster_id, type_name, create_timestamp ) - ); + CONSTRAINT PK_clusterconfigmapping PRIMARY KEY CLUSTERED (cluster_id, type_name, create_timestamp ), + CONSTRAINT clusterconfigmappingcluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)); CREATE TABLE clusterservices ( service_name VARCHAR(255) NOT NULL, cluster_id BIGINT NOT NULL, service_enabled INT NOT NULL, - PRIMARY KEY CLUSTERED (service_name, cluster_id) - ); + CONSTRAINT PK_clusterservices PRIMARY KEY CLUSTERED (service_name, cluster_id), + CONSTRAINT FK_clusterservices_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)); CREATE TABLE clusterstate ( cluster_id BIGINT NOT NULL, current_cluster_state VARCHAR(255) NOT NULL, current_stack_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (cluster_id) - ); + 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)); + +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, + 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)); CREATE TABLE cluster_version ( id BIGINT NOT NULL, @@ -119,8 +175,23 @@ CREATE TABLE cluster_version ( start_time BIGINT NOT NULL, end_time BIGINT, user_name VARCHAR(255), - PRIMARY KEY CLUSTERED (id) - ); + 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)); + +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_state VARCHAR(255) NOT NULL, + service_name VARCHAR(255) NOT NULL, + recovery_enabled SMALLINT NOT NULL DEFAULT 0, + CONSTRAINT pk_sc_desiredstate PRIMARY KEY (id), + CONSTRAINT unq_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 srvccmponentdesiredstatesrvcnm FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id)); CREATE TABLE hostcomponentdesiredstate ( cluster_id BIGINT NOT NULL, @@ -133,8 +204,10 @@ CREATE TABLE hostcomponentdesiredstate ( maintenance_state VARCHAR(32) NOT NULL, security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', restart_required BIT NOT NULL DEFAULT 0, - PRIMARY KEY CLUSTERED (cluster_id, component_name, host_id, service_name) -); + CONSTRAINT PK_hostcomponentdesiredstate PRIMARY KEY CLUSTERED (cluster_id, component_name, host_id, service_name), + 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)); CREATE TABLE hostcomponentstate ( id BIGINT NOT NULL, @@ -147,30 +220,13 @@ CREATE TABLE hostcomponentstate ( service_name VARCHAR(255) NOT NULL, upgrade_state VARCHAR(32) NOT NULL DEFAULT 'NONE', security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', - PRIMARY KEY CLUSTERED (id) -); + 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)); CREATE NONCLUSTERED INDEX idx_host_component_state on hostcomponentstate(host_id, component_name, service_name, cluster_id); -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, - PRIMARY KEY CLUSTERED (host_id)); - CREATE TABLE hoststate ( agent_version VARCHAR(255) NOT NULL, available_mem BIGINT NOT NULL, @@ -179,19 +235,8 @@ CREATE TABLE hoststate ( host_id BIGINT NOT NULL, time_in_state BIGINT NOT NULL, maintenance_state VARCHAR(512), - PRIMARY KEY CLUSTERED (host_id)); - -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_state VARCHAR(255) NOT NULL, - service_name VARCHAR(255) NOT NULL, - CONSTRAINT pk_sc_desiredstate PRIMARY KEY (id), - CONSTRAINT unq_scdesiredstate_name UNIQUE(component_name, service_name, cluster_id) -); + CONSTRAINT PK_hoststate PRIMARY KEY CLUSTERED (host_id), + CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id)); CREATE TABLE servicedesiredstate ( cluster_id BIGINT NOT NULL, @@ -201,9 +246,22 @@ CREATE TABLE servicedesiredstate ( service_name VARCHAR(255) NOT NULL, maintenance_state VARCHAR(32) NOT NULL, security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', - PRIMARY KEY CLUSTERED (cluster_id,service_name) + 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 servicedesiredstateservicename FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id)); + +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) ); +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)); + CREATE TABLE users ( user_id INTEGER, principal_id BIGINT NOT NULL, @@ -214,29 +272,79 @@ CREATE TABLE users ( user_password VARCHAR(255), active INTEGER NOT NULL DEFAULT 1, active_widget_layouts VARCHAR(1024) DEFAULT NULL, - PRIMARY KEY CLUSTERED (user_id) - ); + 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)); CREATE TABLE groups ( group_id INTEGER, principal_id BIGINT NOT NULL, group_name VARCHAR(255) NOT NULL, ldap_group INTEGER NOT NULL DEFAULT 0, - PRIMARY KEY CLUSTERED (group_id) - ); + 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)); CREATE TABLE members ( member_id INTEGER, group_id INTEGER NOT NULL, user_id INTEGER NOT NULL, - PRIMARY KEY CLUSTERED (member_id) - ); + 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)); -CREATE TABLE execution_command ( - command VARBINARY(MAX), - task_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (task_id) - ); +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, + 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)); + +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), + CONSTRAINT PK_request PRIMARY KEY CLUSTERED (request_id), + CONSTRAINT FK_request_schedule_id FOREIGN KEY (request_schedule_id) REFERENCES requestschedule (schedule_id)); + +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), + cluster_host_info VARBINARY(MAX) NOT NULL, + command_params VARBINARY(MAX), + host_params VARBINARY(MAX), + CONSTRAINT PK_stage PRIMARY KEY CLUSTERED (stage_id, request_id), + CONSTRAINT FK_stage_request_id FOREIGN KEY (request_id) REFERENCES request (request_id)); CREATE TABLE host_role_command ( task_id BIGINT NOT NULL, @@ -262,53 +370,23 @@ CREATE TABLE host_role_command ( role_command VARCHAR(255), command_detail VARCHAR(255), custom_command_name VARCHAR(255), - PRIMARY KEY CLUSTERED (task_id) - ); + 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)); + +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)); 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, - PRIMARY KEY CLUSTERED ( - ROLE, - request_id, - stage_id - ) - ); - -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), - cluster_host_info VARBINARY(MAX) NOT NULL, - command_params VARBINARY(MAX), - host_params VARBINARY(MAX), - PRIMARY KEY CLUSTERED ( - stage_id, - request_id - ) - ); - -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), - PRIMARY KEY CLUSTERED (request_id) - ); + 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)); CREATE TABLE requestresourcefilter ( filter_id BIGINT NOT NULL, @@ -316,8 +394,8 @@ CREATE TABLE requestresourcefilter ( service_name VARCHAR(255), component_name VARCHAR(255), hosts VARBINARY(MAX), - PRIMARY KEY CLUSTERED (filter_id) - ); + CONSTRAINT PK_requestresourcefilter PRIMARY KEY CLUSTERED (filter_id), + CONSTRAINT FK_reqresfilter_req_id FOREIGN KEY (request_id) REFERENCES request (request_id)); CREATE TABLE requestoperationlevel ( operation_level_id BIGINT NOT NULL, @@ -327,18 +405,20 @@ CREATE TABLE requestoperationlevel ( 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 - PRIMARY KEY CLUSTERED (operation_level_id) - ); + CONSTRAINT PK_requestoperationlevel PRIMARY KEY CLUSTERED (operation_level_id), + CONSTRAINT FK_req_op_level_req_id FOREIGN KEY (request_id) REFERENCES request (request_id)); CREATE TABLE ClusterHostMapping ( cluster_id BIGINT NOT NULL, host_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (cluster_id, host_id)); + 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)); CREATE TABLE key_value_store ( [key] VARCHAR(255), [value] VARCHAR(MAX), - PRIMARY KEY CLUSTERED ([key]) + CONSTRAINT PK_key_value_store PRIMARY KEY CLUSTERED ([key]) ); CREATE TABLE hostconfigmapping ( @@ -350,24 +430,20 @@ CREATE TABLE hostconfigmapping ( create_timestamp BIGINT NOT NULL, selected INTEGER NOT NULL DEFAULT 0, user_name VARCHAR(255) NOT NULL DEFAULT '_db', - PRIMARY KEY CLUSTERED ( - cluster_id, - host_id, - type_name, - create_timestamp - ) - ); + 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)); CREATE TABLE metainfo ( [metainfo_key] VARCHAR(255), [metainfo_value] VARCHAR(255), - PRIMARY KEY CLUSTERED ([metainfo_key]) + CONSTRAINT PK_metainfo PRIMARY KEY CLUSTERED ([metainfo_key]) ); CREATE TABLE ambari_sequences ( - sequence_name VARCHAR(255) PRIMARY KEY, - [sequence_value] BIGINT NOT NULL - ); + sequence_name VARCHAR(255), + [sequence_value] BIGINT NOT NULL, + CONSTRAINT PK_ambari_sequences PRIMARY KEY (sequence_name)); CREATE TABLE configgroup ( group_id BIGINT, @@ -377,8 +453,8 @@ CREATE TABLE configgroup ( description VARCHAR(1024), create_timestamp BIGINT NOT NULL, service_name VARCHAR(255), - PRIMARY KEY CLUSTERED (group_id) - ); + CONSTRAINT PK_configgroup PRIMARY KEY CLUSTERED (group_id), + CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)); CREATE TABLE confgroupclusterconfigmapping ( config_group_id BIGINT NOT NULL, @@ -387,44 +463,16 @@ CREATE TABLE confgroupclusterconfigmapping ( version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', create_timestamp BIGINT NOT NULL, - PRIMARY KEY CLUSTERED ( - config_group_id, - cluster_id, - config_type - ) - ); + 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)); CREATE TABLE configgrouphostmapping ( config_group_id BIGINT NOT NULL, host_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED ( - config_group_id, - host_id - ) - ); - -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, - 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), - PRIMARY KEY CLUSTERED (schedule_id) - ); + 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)); CREATE TABLE requestschedulebatchrequest ( schedule_id BIGINT, @@ -436,52 +484,39 @@ CREATE TABLE requestschedulebatchrequest ( request_status VARCHAR(255), return_code SMALLINT, return_message TEXT, - PRIMARY KEY CLUSTERED ( - schedule_id, - batch_id - ) - ); + CONSTRAINT PK_requestschedulebatchrequest PRIMARY KEY CLUSTERED (schedule_id, batch_id), + CONSTRAINT FK_rsbatchrequest_schedule_id FOREIGN KEY (schedule_id) REFERENCES requestschedule (schedule_id)); 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), - PRIMARY KEY CLUSTERED (blueprint_name) - ); + CONSTRAINT PK_blueprint PRIMARY KEY CLUSTERED (blueprint_name), + CONSTRAINT FK_blueprint_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id)); CREATE TABLE hostgroup ( blueprint_name VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, cardinality VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED ( - blueprint_name, - NAME - ) - ); + CONSTRAINT PK_hostgroup PRIMARY KEY CLUSTERED (blueprint_name, NAME), + CONSTRAINT FK_hg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name)); 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), - PRIMARY KEY CLUSTERED ( - blueprint_name, - hostgroup_name, - NAME - ) - ); + 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)); 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), - PRIMARY KEY CLUSTERED ( - blueprint_name, - type_name - ) - ); + CONSTRAINT PK_blueprint_configuration PRIMARY KEY CLUSTERED (blueprint_name, type_name), + CONSTRAINT FK_cfg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name)); CREATE TABLE blueprint_setting ( id BIGINT NOT NULL, @@ -499,12 +534,8 @@ CREATE TABLE hostgroup_configuration ( type_name VARCHAR(255) NOT NULL, config_data VARCHAR(MAX) NOT NULL, config_attributes VARCHAR(MAX), - PRIMARY KEY CLUSTERED ( - blueprint_name, - hostgroup_name, - type_name - ) - ); + 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)); CREATE TABLE viewmain ( view_name VARCHAR(255) NOT NULL, @@ -518,22 +549,8 @@ CREATE TABLE viewmain ( archive VARCHAR(255), mask VARCHAR(255), system_view BIT NOT NULL DEFAULT 0, - PRIMARY KEY CLUSTERED (view_name) - ); - -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, - PRIMARY KEY CLUSTERED ( - view_instance_id, - NAME, - user_name - ) - ); + CONSTRAINT PK_viewmain PRIMARY KEY CLUSTERED (view_name), + CONSTRAINT FK_view_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id)); CREATE TABLE viewinstance ( view_instance_id BIGINT, @@ -549,20 +566,29 @@ CREATE TABLE viewinstance ( alter_names BIT NOT NULL DEFAULT 1, cluster_handle VARCHAR(255), short_url VARCHAR (255), - PRIMARY KEY CLUSTERED (view_instance_id) - ); + CONSTRAINT PK_viewinstance PRIMARY KEY CLUSTERED (view_instance_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)); + +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)); CREATE TABLE viewinstanceproperty ( view_name VARCHAR(255) NOT NULL, view_instance_name VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, value VARCHAR(2000), - PRIMARY KEY CLUSTERED ( - view_name, - view_instance_name, - NAME - ) - ); + 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)); CREATE TABLE viewparameter ( view_name VARCHAR(255) NOT NULL, @@ -574,11 +600,8 @@ CREATE TABLE viewparameter ( cluster_config VARCHAR(255), required CHAR(1), masked CHAR(1), - PRIMARY KEY CLUSTERED ( - view_name, - NAME - ) - ); + CONSTRAINT PK_viewparameter PRIMARY KEY CLUSTERED (view_name, NAME), + CONSTRAINT FK_viewparam_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name)); CREATE TABLE viewresource ( view_name VARCHAR(255) NOT NULL, @@ -589,11 +612,8 @@ CREATE TABLE viewresource ( provider VARCHAR(255), service VARCHAR(255), resource VARCHAR(255), - PRIMARY KEY CLUSTERED ( - view_name, - NAME - ) - ); + CONSTRAINT PK_viewresource PRIMARY KEY CLUSTERED (view_name, NAME), + CONSTRAINT FK_viewres_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name)); CREATE TABLE viewentity ( id BIGINT NOT NULL, @@ -601,32 +621,8 @@ CREATE TABLE viewentity ( view_instance_name VARCHAR(255) NOT NULL, class_name VARCHAR(255) NOT NULL, id_property VARCHAR(255), - PRIMARY KEY CLUSTERED (id) - ); - -CREATE TABLE adminresourcetype ( - resource_type_id INTEGER NOT NULL, - resource_type_name VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (resource_type_id) - ); - -CREATE TABLE adminresource ( - resource_id BIGINT NOT NULL, - resource_type_id INTEGER NOT NULL, - PRIMARY KEY CLUSTERED (resource_id) - ); - -CREATE TABLE adminprincipaltype ( - principal_type_id INTEGER NOT NULL, - principal_type_name VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (principal_type_id) - ); - -CREATE TABLE adminprincipal ( - principal_id BIGINT NOT NULL, - principal_type_id INTEGER NOT NULL, - PRIMARY KEY CLUSTERED (principal_id) - ); + CONSTRAINT PK_viewentity PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_viewentity_view_name FOREIGN KEY (view_name, view_instance_name) REFERENCES viewinstance(view_name, name)); CREATE TABLE adminpermission ( permission_id BIGINT NOT NULL, @@ -634,58 +630,47 @@ CREATE TABLE adminpermission ( resource_type_id INTEGER NOT NULL, permission_label VARCHAR(255), sort_order SMALLINT NOT NULL DEFAULT 1, - PRIMARY KEY CLUSTERED (permission_id) - ); + 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 UQ_perm_name_resource_type_id UNIQUE (permission_name, resource_type_id)); CREATE TABLE roleauthorization ( authorization_id VARCHAR(100) NOT NULL, authorization_name VARCHAR(255) NOT NULL, - PRIMARY KEY(authorization_id)); + CONSTRAINT PK_roleauthorization PRIMARY KEY (authorization_id)); CREATE TABLE permission_roleauthorization ( permission_id BIGINT NOT NULL, authorization_id VARCHAR(100) NOT NULL, - PRIMARY KEY(permission_id, authorization_id)); + 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)); CREATE TABLE adminprivilege ( privilege_id BIGINT, permission_id BIGINT NOT NULL, resource_id BIGINT NOT NULL, principal_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (privilege_id) - ); + 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)); CREATE TABLE host_version ( id BIGINT NOT NULL, repo_version_id BIGINT NOT NULL, host_id BIGINT NOT NULL, STATE VARCHAR(32) NOT NULL, - PRIMARY KEY CLUSTERED (id) - ); - -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, - version_url VARCHAR(1024), - version_xml VARCHAR(MAX), - version_xsd VARCHAR(512), - parent_id BIGINT, - PRIMARY KEY CLUSTERED (repo_version_id) - ); + 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)); CREATE TABLE artifact ( artifact_name VARCHAR(255) NOT NULL, artifact_data TEXT NOT NULL, foreign_keys VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED ( - artifact_name, - foreign_keys - ) - ); + CONSTRAINT PK_artifact PRIMARY KEY CLUSTERED (artifact_name, foreign_keys) +); CREATE TABLE widget ( id BIGINT NOT NULL, @@ -700,7 +685,7 @@ CREATE TABLE widget ( widget_values VARCHAR(4000), properties VARCHAR(4000), cluster_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (id) + CONSTRAINT PK_widget PRIMARY KEY CLUSTERED (id) ); CREATE TABLE widget_layout ( @@ -711,15 +696,16 @@ CREATE TABLE widget_layout ( user_name VARCHAR(255) NOT NULL, display_name VARCHAR(255), cluster_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (id) + CONSTRAINT PK_widget_layout PRIMARY KEY CLUSTERED (id) ); CREATE TABLE widget_layout_user_widget ( widget_layout_id BIGINT NOT NULL, widget_id BIGINT NOT NULL, widget_order smallint, - PRIMARY KEY CLUSTERED (widget_layout_id, widget_id) -); + 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)); CREATE TABLE topology_request ( id BIGINT NOT NULL, @@ -729,8 +715,8 @@ CREATE TABLE topology_request ( cluster_properties TEXT, cluster_attributes TEXT, description VARCHAR(1024), - PRIMARY KEY CLUSTERED (id) -); + CONSTRAINT PK_topology_request PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_topology_request_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id)); CREATE TABLE topology_hostgroup ( id BIGINT NOT NULL, @@ -738,8 +724,8 @@ CREATE TABLE topology_hostgroup ( group_properties TEXT, group_attributes TEXT, request_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (id) -); + CONSTRAINT PK_topology_hostgroup PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hostgroup_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id)); CREATE TABLE topology_host_info ( id BIGINT NOT NULL, @@ -749,15 +735,16 @@ CREATE TABLE topology_host_info ( host_count INTEGER, predicate VARCHAR(2048), rack_info VARCHAR(255), - PRIMARY KEY CLUSTERED (id) -); + 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)); CREATE TABLE topology_logical_request ( id BIGINT NOT NULL, request_id BIGINT NOT NULL, description VARCHAR(1024), - PRIMARY KEY CLUSTERED (id) -); + CONSTRAINT PK_topology_logical_request PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_logicalreq_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id)); CREATE TABLE topology_host_request ( id BIGINT NOT NULL, @@ -765,23 +752,25 @@ CREATE TABLE topology_host_request ( group_id BIGINT NOT NULL, stage_id BIGINT NOT NULL, host_name VARCHAR(255), - PRIMARY KEY CLUSTERED (id) -); + 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)); CREATE TABLE topology_host_task ( id BIGINT NOT NULL, host_request_id BIGINT NOT NULL, type VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (id) -); + CONSTRAINT PK_topology_host_task PRIMARY KEY CLUSTERED (id), + CONSTRAINT FK_hosttask_req_id FOREIGN KEY (host_request_id) REFERENCES topology_host_request (id)); CREATE TABLE topology_logical_task ( id BIGINT NOT NULL, host_task_id BIGINT NOT NULL, physical_task_id BIGINT, component VARCHAR(255) NOT NULL, - PRIMARY KEY CLUSTERED (id) -); + 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)); CREATE TABLE setting ( id BIGINT NOT NULL, @@ -790,7 +779,7 @@ CREATE TABLE setting ( content TEXT NOT NULL, updated_by VARCHAR(255) NOT NULL DEFAULT '_db', update_timestamp BIGINT NOT NULL, - PRIMARY KEY (id) + CONSTRAINT PK_setting PRIMARY KEY (id) ); @@ -808,7 +797,7 @@ CREATE TABLE upgrade ( skip_sc_failures BIT NOT NULL DEFAULT 0, downgrade_allowed BIT NOT NULL DEFAULT 1, suspended BIT DEFAULT 0 NOT NULL, - PRIMARY KEY CLUSTERED (upgrade_id), + CONSTRAINT PK_upgrade PRIMARY KEY CLUSTERED (upgrade_id), FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id), FOREIGN KEY (request_id) REFERENCES request(request_id) ); @@ -818,7 +807,7 @@ CREATE TABLE upgrade_group ( upgrade_id BIGINT NOT NULL, group_name VARCHAR(255) DEFAULT '' NOT NULL, group_title VARCHAR(1024) DEFAULT '' NOT NULL, - PRIMARY KEY CLUSTERED (upgrade_group_id), + CONSTRAINT PK_upgrade_group PRIMARY KEY CLUSTERED (upgrade_group_id), FOREIGN KEY (upgrade_id) REFERENCES upgrade(upgrade_id) ); @@ -830,7 +819,7 @@ CREATE TABLE upgrade_item ( hosts TEXT, tasks TEXT, item_text VARCHAR(1024), - PRIMARY KEY CLUSTERED (upgrade_item_id), + CONSTRAINT PK_upgrade_item PRIMARY KEY CLUSTERED (upgrade_item_id), FOREIGN KEY (upgrade_group_id) REFERENCES upgrade_group(upgrade_group_id) ); @@ -857,131 +846,33 @@ CREATE INDEX idx_rsc_request_id ON role_success_criteria (request_id); -- altering tables by creating unique constraints---------- --------altering tables to add constraints---------- -ALTER TABLE users ADD CONSTRAINT UNQ_users_0 UNIQUE (user_name, user_type); -ALTER TABLE groups ADD CONSTRAINT UNQ_groups_0 UNIQUE (group_name, ldap_group); -ALTER TABLE members ADD CONSTRAINT UNQ_members_0 UNIQUE (group_id, user_id); -ALTER TABLE clusterconfig ADD CONSTRAINT UQ_config_type_tag UNIQUE (cluster_id, type_name, version_tag); -ALTER TABLE clusterconfig ADD CONSTRAINT UQ_config_type_version UNIQUE (cluster_id, type_name, version); -ALTER TABLE hosts ADD CONSTRAINT UQ_hosts_host_name UNIQUE (host_name); -ALTER TABLE viewinstance ADD CONSTRAINT UQ_viewinstance_name UNIQUE (view_name, name); -ALTER TABLE viewinstance ADD CONSTRAINT UQ_viewinstance_name_id UNIQUE (view_instance_id, view_name, name); -ALTER TABLE serviceconfig ADD CONSTRAINT UQ_scv_service_version UNIQUE (cluster_id, service_name, version); -ALTER TABLE adminpermission ADD CONSTRAINT UQ_perm_name_resource_type_id UNIQUE (permission_name, resource_type_id); -ALTER TABLE repo_version ADD CONSTRAINT UQ_repo_version_display_name UNIQUE (display_name); -ALTER TABLE repo_version ADD CONSTRAINT UQ_repo_version_stack_id UNIQUE (stack_id, version); -ALTER TABLE stack ADD CONSTRAINT unq_stack UNIQUE (stack_name, stack_version); -- 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. -ALTER TABLE members ADD CONSTRAINT FK_members_group_id FOREIGN KEY (group_id) REFERENCES groups (group_id); -ALTER TABLE members ADD CONSTRAINT FK_members_user_id FOREIGN KEY (user_id) REFERENCES users (user_id); ALTER TABLE clusters ADD CONSTRAINT FK_clusters_upgrade_id FOREIGN KEY (upgrade_id) REFERENCES upgrade (upgrade_id); -ALTER TABLE clusterconfig ADD CONSTRAINT FK_clusterconfig_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE clusterservices ADD CONSTRAINT FK_clusterservices_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE clusterconfigmapping ADD CONSTRAINT clusterconfigmappingcluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE clusterstate ADD CONSTRAINT FK_clusterstate_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE cluster_version ADD CONSTRAINT FK_cluster_version_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE cluster_version ADD CONSTRAINT FK_cluster_version_repovers_id FOREIGN KEY (repo_version_id) REFERENCES repo_version (repo_version_id); -ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmponentdesiredstatehstid FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, service_name, cluster_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id); -ALTER TABLE hostcomponentstate ADD CONSTRAINT hstcomponentstatecomponentname FOREIGN KEY (component_name, service_name, cluster_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id); -ALTER TABLE hostcomponentstate ADD CONSTRAINT FK_hostcomponentstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE host_version ADD CONSTRAINT FK_host_version_repovers_id FOREIGN KEY (repo_version_id) REFERENCES repo_version (repo_version_id); -ALTER TABLE servicecomponentdesiredstate ADD CONSTRAINT srvccmponentdesiredstatesrvcnm FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id); -ALTER TABLE servicedesiredstate ADD CONSTRAINT servicedesiredstateservicename FOREIGN KEY (service_name, cluster_id) REFERENCES clusterservices (service_name, cluster_id); -ALTER TABLE execution_command ADD CONSTRAINT FK_execution_command_task_id FOREIGN KEY (task_id) REFERENCES host_role_command (task_id); -ALTER TABLE host_role_command ADD CONSTRAINT FK_host_role_command_stage_id FOREIGN KEY (stage_id, request_id) REFERENCES stage (stage_id, request_id); -ALTER TABLE host_role_command ADD CONSTRAINT FK_host_role_command_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE role_success_criteria ADD CONSTRAINT role_success_criteria_stage_id FOREIGN KEY (stage_id, request_id) REFERENCES stage (stage_id, request_id); -ALTER TABLE stage ADD CONSTRAINT FK_stage_request_id FOREIGN KEY (request_id) REFERENCES request (request_id); -ALTER TABLE request ADD CONSTRAINT FK_request_schedule_id FOREIGN KEY (request_schedule_id) REFERENCES requestschedule (schedule_id); -ALTER TABLE ClusterHostMapping ADD CONSTRAINT FK_clhostmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE ClusterHostMapping ADD CONSTRAINT FK_clusterhostmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE hostconfigmapping ADD CONSTRAINT FK_hostconfmapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE hostconfigmapping ADD CONSTRAINT FK_hostconfmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE confgroupclusterconfigmapping ADD CONSTRAINT FK_confg FOREIGN KEY (cluster_id, config_type, version_tag) REFERENCES clusterconfig (cluster_id, type_name, version_tag); -ALTER TABLE confgroupclusterconfigmapping ADD CONSTRAINT FK_cgccm_gid FOREIGN KEY (config_group_id) REFERENCES configgroup (group_id); -ALTER TABLE configgrouphostmapping ADD CONSTRAINT FK_cghm_cgid FOREIGN KEY (config_group_id) REFERENCES configgroup (group_id); -ALTER TABLE configgrouphostmapping ADD CONSTRAINT FK_cghm_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE requestschedulebatchrequest ADD CONSTRAINT FK_rsbatchrequest_schedule_id FOREIGN KEY (schedule_id) REFERENCES requestschedule (schedule_id); -ALTER TABLE hostgroup ADD CONSTRAINT FK_hg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name); -ALTER TABLE hostgroup_component ADD CONSTRAINT FK_hgc_blueprint_name FOREIGN KEY (blueprint_name, hostgroup_name) REFERENCES hostgroup (blueprint_name, name); -ALTER TABLE blueprint_configuration ADD CONSTRAINT FK_cfg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name); -ALTER TABLE hostgroup_configuration ADD CONSTRAINT FK_hg_cfg_bp_hg_name FOREIGN KEY (blueprint_name, hostgroup_name) REFERENCES hostgroup (blueprint_name, name); -ALTER TABLE requestresourcefilter ADD CONSTRAINT FK_reqresfilter_req_id FOREIGN KEY (request_id) REFERENCES request (request_id); -ALTER TABLE requestoperationlevel ADD CONSTRAINT FK_req_op_level_req_id FOREIGN KEY (request_id) REFERENCES request (request_id); -ALTER TABLE viewparameter ADD CONSTRAINT FK_viewparam_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name); -ALTER TABLE viewresource ADD CONSTRAINT FK_viewres_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name); -ALTER TABLE viewinstance ADD CONSTRAINT FK_viewinst_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name); -ALTER TABLE viewinstanceproperty ADD CONSTRAINT FK_viewinstprop_view_name FOREIGN KEY (view_name, view_instance_name) REFERENCES viewinstance(view_name, name); -ALTER TABLE viewinstancedata ADD CONSTRAINT FK_viewinstdata_view_name FOREIGN KEY (view_instance_id, view_name, view_instance_name) REFERENCES viewinstance(view_instance_id, view_name, name); -ALTER TABLE viewentity ADD CONSTRAINT FK_viewentity_view_name FOREIGN KEY (view_name, view_instance_name) REFERENCES viewinstance(view_name, name); -ALTER TABLE adminresource ADD CONSTRAINT FK_resource_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id); -ALTER TABLE adminprincipal ADD CONSTRAINT FK_principal_principal_type_id FOREIGN KEY (principal_type_id) REFERENCES adminprincipaltype(principal_type_id); -ALTER TABLE adminpermission ADD CONSTRAINT FK_permission_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id); -ALTER TABLE permission_roleauthorization ADD CONSTRAINT FK_permission_roleauth_pid FOREIGN KEY (permission_id) REFERENCES adminpermission(permission_id); -ALTER TABLE permission_roleauthorization ADD CONSTRAINT FK_permission_roleauth_aid FOREIGN KEY (authorization_id) REFERENCES roleauthorization(authorization_id); -ALTER TABLE adminprivilege ADD CONSTRAINT FK_privilege_permission_id FOREIGN KEY (permission_id) REFERENCES adminpermission(permission_id); -ALTER TABLE adminprivilege ADD CONSTRAINT FK_privilege_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id); -ALTER TABLE viewmain ADD CONSTRAINT FK_view_resource_type_id FOREIGN KEY (resource_type_id) REFERENCES adminresourcetype(resource_type_id); -ALTER TABLE viewinstance ADD CONSTRAINT FK_viewinstance_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id); -ALTER TABLE adminprivilege ADD CONSTRAINT FK_privilege_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id); -ALTER TABLE users ADD CONSTRAINT FK_users_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id); -ALTER TABLE groups ADD CONSTRAINT FK_groups_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id); -ALTER TABLE serviceconfigmapping ADD CONSTRAINT FK_scvm_scv FOREIGN KEY (service_config_id) REFERENCES serviceconfig(service_config_id); -ALTER TABLE serviceconfigmapping ADD CONSTRAINT FK_scvm_config FOREIGN KEY (config_id) REFERENCES clusterconfig(config_id); -ALTER TABLE serviceconfighosts ADD CONSTRAINT FK_scvhosts_scv FOREIGN KEY (service_config_id) REFERENCES serviceconfig(service_config_id); -ALTER TABLE serviceconfighosts ADD CONSTRAINT FK_scvhosts_host_id FOREIGN KEY (host_id) REFERENCES hosts(host_id); -ALTER TABLE clusters ADD CONSTRAINT FK_clusters_resource_id FOREIGN KEY (resource_id) REFERENCES adminresource(resource_id); -ALTER TABLE widget_layout_user_widget ADD CONSTRAINT FK_widget_layout_id FOREIGN KEY (widget_layout_id) REFERENCES widget_layout(id); -ALTER TABLE widget_layout_user_widget ADD CONSTRAINT FK_widget_id FOREIGN KEY (widget_id) REFERENCES widget(id); -ALTER TABLE topology_request ADD CONSTRAINT FK_topology_request_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id); -ALTER TABLE topology_hostgroup ADD CONSTRAINT FK_hostgroup_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id); -ALTER TABLE topology_host_info ADD CONSTRAINT FK_hostinfo_group_id FOREIGN KEY (group_id) REFERENCES topology_hostgroup(id); -ALTER TABLE topology_host_info ADD CONSTRAINT FK_hostinfo_host_id FOREIGN KEY (host_id) REFERENCES hosts(host_id); -ALTER TABLE topology_logical_request ADD CONSTRAINT FK_logicalreq_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id); -ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_logicalreq_id FOREIGN KEY (logical_request_id) REFERENCES topology_logical_request(id); -ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_group_id FOREIGN KEY (group_id) REFERENCES topology_hostgroup(id); -ALTER TABLE topology_host_task ADD CONSTRAINT FK_hosttask_req_id FOREIGN KEY (host_request_id) REFERENCES topology_host_request (id); -ALTER TABLE topology_logical_task ADD CONSTRAINT FK_ltask_hosttask_id FOREIGN KEY (host_task_id) REFERENCES topology_host_task (id); -ALTER TABLE topology_logical_task ADD CONSTRAINT FK_ltask_hrc_id FOREIGN KEY (physical_task_id) REFERENCES host_role_command (task_id); -ALTER TABLE clusters ADD CONSTRAINT FK_clusters_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id); -ALTER TABLE clusterconfig ADD CONSTRAINT FK_clusterconfig_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id); -ALTER TABLE serviceconfig ADD CONSTRAINT FK_serviceconfig_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id); -ALTER TABLE clusterstate ADD CONSTRAINT FK_cs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id); -ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT FK_hcds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id); -ALTER TABLE hostcomponentstate ADD CONSTRAINT FK_hcs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id); -ALTER TABLE servicecomponentdesiredstate ADD CONSTRAINT FK_scds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id); -ALTER TABLE servicedesiredstate ADD CONSTRAINT FK_sds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id); -ALTER TABLE blueprint ADD CONSTRAINT FK_blueprint_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id); -ALTER TABLE repo_version ADD CONSTRAINT FK_repoversion_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id); -- Kerberos CREATE TABLE kerberos_principal ( principal_name VARCHAR(255) NOT NULL, is_service SMALLINT NOT NULL DEFAULT 1, cached_keytab_path VARCHAR(255), - PRIMARY KEY CLUSTERED (principal_name) + CONSTRAINT PK_kerberos_principal PRIMARY KEY CLUSTERED (principal_name) ); CREATE TABLE kerberos_principal_host ( principal_name VARCHAR(255) NOT NULL, host_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (principal_name, host_id) -); + 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)); CREATE TABLE kerberos_descriptor ( kerberos_descriptor_name VARCHAR(255) NOT NULL, kerberos_descriptor VARCHAR(MAX) NOT NULL, - PRIMARY KEY (kerberos_descriptor_name) + CONSTRAINT PK_kerberos_descriptor PRIMARY KEY (kerberos_descriptor_name) ); -ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_id); -ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_principalname FOREIGN KEY (principal_name) REFERENCES kerberos_principal (principal_name); -- Kerberos (end) -- Alerting Framework @@ -1003,7 +894,7 @@ CREATE TABLE alert_definition ( ignore_host SMALLINT DEFAULT 0 NOT NULL, repeat_tolerance INTEGER DEFAULT 1 NOT NULL, repeat_tolerance_enabled SMALLINT DEFAULT 0 NOT NULL, - PRIMARY KEY CLUSTERED (definition_id), + 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) ); @@ -1020,7 +911,7 @@ CREATE TABLE alert_history ( alert_label VARCHAR(1024), alert_state VARCHAR(255) NOT NULL, alert_text TEXT, - PRIMARY KEY CLUSTERED (alert_id), + 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) ); @@ -1035,7 +926,7 @@ CREATE TABLE alert_current ( latest_text TEXT, occurrences BIGINT NOT NULL DEFAULT 1, firmness VARCHAR(255) NOT NULL DEFAULT 'HARD', - PRIMARY KEY CLUSTERED (alert_id), + 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) ); @@ -1046,7 +937,7 @@ CREATE TABLE alert_group ( group_name VARCHAR(255) NOT NULL, is_default SMALLINT NOT NULL DEFAULT 0, service_name VARCHAR(255), - PRIMARY KEY CLUSTERED (group_id), + CONSTRAINT PK_alert_group PRIMARY KEY CLUSTERED (group_id), CONSTRAINT uni_alert_group_name UNIQUE(cluster_id,group_name) ); @@ -1057,7 +948,7 @@ CREATE TABLE alert_target ( properties TEXT, description VARCHAR(1024), is_global SMALLINT NOT NULL DEFAULT 0, - PRIMARY KEY CLUSTERED (target_id) + CONSTRAINT PK_alert_target PRIMARY KEY CLUSTERED (target_id) ); CREATE TABLE alert_target_states ( @@ -1069,7 +960,7 @@ CREATE TABLE alert_target_states ( CREATE TABLE alert_group_target ( group_id BIGINT NOT NULL, target_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (group_id, target_id), + 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) ); @@ -1077,7 +968,7 @@ CREATE TABLE alert_group_target ( CREATE TABLE alert_grouping ( definition_id BIGINT NOT NULL, group_id BIGINT NOT NULL, - PRIMARY KEY CLUSTERED (group_id, definition_id), + 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) ); @@ -1088,7 +979,7 @@ CREATE TABLE alert_notice ( history_id BIGINT NOT NULL, notify_state VARCHAR(255) NOT NULL, uuid VARCHAR(64) NOT NULL UNIQUE, - PRIMARY KEY CLUSTERED (notification_id), + 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) ); @@ -1605,7 +1496,7 @@ create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP); -- ambari log4j DDL CREATE TABLE workflow ( - workflowId varchar(255) PRIMARY KEY CLUSTERED, + workflowId varchar(255), workflowName varchar(255), parentWorkflowId varchar(255), workflowContext TEXT, userName varchar(255), @@ -1613,6 +1504,7 @@ CREATE TABLE workflow ( numJobsTotal INTEGER, numJobsCompleted INTEGER, inputBytes BIGINT, outputBytes BIGINT, duration BIGINT, + CONSTRAINT PK_workflow PRIMARY KEY CLUSTERED (workflowId), FOREIGN KEY (parentWorkflowId) REFERENCES workflow (workflowId) ); @@ -1628,7 +1520,7 @@ CREATE TABLE job ( mapsRuntime BIGINT, reducesRuntime BIGINT, mapCounters TEXT, reduceCounters TEXT, jobCounters TEXT, inputBytes BIGINT, outputBytes BIGINT, - PRIMARY KEY CLUSTERED (jobId), + CONSTRAINT PK_job PRIMARY KEY CLUSTERED (jobId), FOREIGN KEY (workflowId) REFERENCES workflow (workflowId) ); @@ -1638,7 +1530,7 @@ CREATE TABLE task ( taskType varchar(255), splits varchar(2000), startTime BIGINT, finishTime BIGINT, status TEXT, error TEXT, counters TEXT, failedAttempt TEXT, - PRIMARY KEY CLUSTERED (taskId), + CONSTRAINT PK_task PRIMARY KEY CLUSTERED (taskId), FOREIGN KEY (jobId) REFERENCES job (jobId) ); @@ -1652,7 +1544,7 @@ CREATE TABLE taskAttempt ( locality TEXT, avataar TEXT, status TEXT, error TEXT, counters TEXT, inputBytes BIGINT, outputBytes BIGINT, - PRIMARY KEY CLUSTERED (taskAttemptId), + CONSTRAINT PK_taskAttempt PRIMARY KEY CLUSTERED (taskAttemptId), FOREIGN KEY (jobId) REFERENCES job (jobId), FOREIGN KEY (taskId) REFERENCES task (taskId) );
http://git-wip-us.apache.org/repos/asf/ambari/blob/346dfe7e/ambari-server/src/test/java/org/apache/ambari/server/orm/db/DDLTestUtils.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/test/java/org/apache/ambari/server/orm/db/DDLTestUtils.java b/ambari-server/src/test/java/org/apache/ambari/server/orm/db/DDLTestUtils.java new file mode 100644 index 0000000..7d0e289 --- /dev/null +++ b/ambari-server/src/test/java/org/apache/ambari/server/orm/db/DDLTestUtils.java @@ -0,0 +1,525 @@ +/* + * 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. + */ +package org.apache.ambari.server.orm.db; + +import com.google.common.base.*; +import com.google.common.base.Optional; +import com.google.common.cache.CacheBuilder; +import com.google.common.cache.CacheLoader; +import com.google.common.cache.LoadingCache; +import com.google.common.collect.*; +import com.google.common.io.Resources; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +import java.net.URL; +import java.util.*; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * <p> + * Utility to parse DDL scripts for verification and comparision. Parsing is not complete, only things relevant for + * the unit tests are considered, e.g. the column data types are not captured as they may differ across different SQL + * dialects. + * </p> + * <p> + * Parsing is mostly done by regexp matches, so the parser has limited capabilities. The following known restrictions apply: + * </p> + * <ul> + * <li>Multiple statements in a single line are not supported, e.g: CREATE TABLE X(...); CREATE TABLE Y(...)</li> + * <li>Mutiple definitions in the same line within a create statement is supported though, e.g: name varchar(255), id bigint, ...</li> + * <li>Statements must be terminated by ;</li> + * </ul> + * + */ +public class DDLTestUtils { + private static final Logger LOG = LoggerFactory.getLogger(DDLTestUtils.class); + + // These patterns are used during the initial line by line parsing of a DDL. + // The patterns help + // 1. filter out irrelevant lines (comment, empty line, go/commit), + // 2. recognize statement starters (create table, alter table, create index) + // 3. recognize statement terminators (;) + private static final Pattern CommentLine = Pattern.compile("^\\s*--.*"); + private static final Pattern EmptyLine = Pattern.compile("^\\s*$"); + private static final Pattern CommitLine = Pattern.compile("^\\s*(go|commit).*$"); + private static final Pattern CreateTable = Pattern.compile("^\\s*create\\s+table.*$"); + private static final Pattern AlterTable = Pattern.compile("^\\s*alter\\s+table.*$"); + private static final Pattern CreateIndex = Pattern.compile("^\\s*create\\s+index.*$"); + private static final Pattern EndStatement = Pattern.compile("(.*\\;)\\s*$"); + + // These patterns are used to match column/constraint definitons in a create table statement + // to capture the table name + private static final Pattern TableName = Pattern.compile("^\\s*create table\\s+([\\w\\.\\_]+).*$"); + // to capture the name and columns in a primary key + private static final Pattern PK = Pattern.compile("^.*constraint\\s+([\\w\\.\\_]+)\\s+primary\\s+key\\s*\\(([^\\)]+)\\).*$"); + // to capture the name and columns in a clusterd primary key + private static final Pattern PKClustered = Pattern.compile("^.*constraint\\s+([\\w\\.\\_]+)\\s+primary\\s+key\\s+clustered\\s*\\(([^\\)]+)\\).*$"); + // to capture the name and columns in a unique constraint + private static final Pattern UQ = Pattern.compile("^.*constraint\\s+([\\w\\.\\_]+)\\s+unique\\s*\\(([^\\)]+)\\).*$"); + // to capture the name and columns and the referred columnd in a foreign key + private static final Pattern FK = Pattern.compile("^.*constraint\\s+([\\w\\.\\_]+)\\s+foreign\\s+key\\s*\\(([^\\)]*)\\)\\s*references\\s+([\\w\\_\\.]+)\\s*\\(([^\\)]+)\\).*$"); + // to capture the name of a columns + private static final Pattern Col = Pattern.compile("^\\s*([\\`\\\"\\[\\]\\w\\.\\_]+)\\s+.*$"); + // to capture column lists within the create table statement, such as the column list in "primary key (name, id)" + // in such lists commas are replaced with a | so that we can reliably use comma as a definition separator within the create table statement + private static final Pattern InnerList = Pattern.compile("(\\([^\\(^\\)]+\\))"); + + // These patterns represent Unnamed constraints + private static final Pattern UnnamedPK = Pattern.compile("^\\s*primary\\s+key[\\sclustered]*\\(([^\\)]+)\\).*$"); // e.g: primary key [clustered] (name) + private static final Pattern UnnamedUQ = Pattern.compile("^\\s*unique\\s*\\(([^\\)]+)\\).*$"); // e.g: unique (name) + private static final Pattern UnnamedFK = Pattern.compile("^\\s*foreign\\s+key\\s*\\(([^\\)]+)\\)\\s*references\\s+([\\w\\_\\.]+)\\s*\\(([^\\)]+)\\).*$"); // e.g: foreign key (name) references other_table(name) + private static final Pattern PKColumn = Pattern.compile("^.*[\\w\\.\\_]+\\s.*primary\\s+key[\\sclustered\\,\\;\\)]*$"); // e.g: name varchar(255) not null primary key [clustered] + private static final Pattern UQColumn = Pattern.compile("^\\s*[\\w\\.\\_]+\\s.*unique[\\s\\;\\,\\)]*$"); // e.g: name varchar(255) not null unique + private static final List<Pattern> CheckedUnnamedConstraints = ImmutableList.of(UnnamedPK); + private static final List<Pattern> UncheckedUnnamedConstraints = ImmutableList.of(UnnamedUQ, UnnamedFK, PKColumn, UQColumn); + + private static final LoadingCache<String, DDL> ddlCache = CacheBuilder.newBuilder().build( + new CacheLoader<String, DDL>() { + @Override public DDL load(String key) throws Exception { return loadDdl(key); } + } + ); + + /** + * List of supported databases. + */ + public static final List<String> DATABASES = ImmutableList.of( + "Derby", + "MySQL", + "Oracle", + "Postgres", + "Postgres-EMBEDDED", + "SQLAnywhere", + "SQLServer"); + + /** + * Loads and parses the DDL for a specific database type (e.g. Postgres). Already loaded DDL's are cached. + * @param dbType One of the supported databases @see #DATABASES + * @return the parsed DDL + * @throws Exception if exception occurs during loading/parsing + */ + public static DDL getDdl(String dbType) throws Exception{ + return ddlCache.get(dbType); + } + + private static URL getDdlUrl(String dbType) { + return Resources.getResource("Ambari-DDL-" + dbType + "-CREATE.sql"); + } + + private static List<String> loadFile(String dbType) throws Exception { + List<String> lines = Resources.readLines(getDdlUrl(dbType), Charsets.UTF_8); + List<String> replaced = new ArrayList<>(lines.size()); + for (String line: lines) { replaced.add(line.toLowerCase()); } + return replaced; + } + + /** + * Groups the load DDL file into statements. Currently CREATE TABLE's, ALTER TABLE's and CREATE INDEX-es + * are considered statements. Multiple statements in a single line are not supported. Comments, empty lines and + * GO / COMMIT commands are discarded. + * + * @param ddlFile the loaded DDL file (as list of strings) + * @return a list of strings containing the statements + */ + private static List<String> groupStatements(List<String> ddlFile) { + List<String> statements = new ArrayList<>(); + Optional<ArrayList<String>> currentStmt = Optional.absent(); + for (String line: ddlFile) { + // These lines should be skipped + if (CommentLine.matcher(line).matches() || + EmptyLine.matcher(line).matches() || + CommitLine.matcher(line).matches()); + // These lines indicate the start of a CREATE TABLE / ALTER TABLE / CREATE INDEX statement + else if (CreateTable.matcher(line).matches() || + AlterTable.matcher(line).matches() || + CreateIndex.matcher(line).matches()) { + // Prepare to collect subsequent lines as part of the new statement + if(currentStmt.isPresent()) throw new IllegalStateException( + "Unfinished statement: " + currentStmt.get() + "\nnew statement: " +line); + currentStmt = Optional.of(new ArrayList<String>()); + currentStmt.get().add(stripComment(line)); + // If the statement is a one liner, close it right away + if (line.contains(";")) { + statements.add(Joiner.on(' ').join(currentStmt.get())); + currentStmt = Optional.absent(); + } + } + // Process terminating line (containing ;): add to the current statement and close current statement + else if (currentStmt.isPresent() && EndStatement.matcher(line).matches()) { + currentStmt.get().add(stripComment(line)); + statements.add(Joiner.on(' ').join(currentStmt.get())); + currentStmt = Optional.absent(); + } + // Collect all other lines as part of the current statement + else if (currentStmt.isPresent()) { + currentStmt.get().add(stripComment(line)); + } + } + return statements; + } + + private static String stripComment(String line) { + return line.contains("--") ? line.substring(0, line.indexOf("--")) : line; + } + + private static Collection<String> toColumns(String cols) { + List<String> columns = new ArrayList<>(); + for (String col: Splitter.on('|').split(cols)) { + columns.add( stripPrefixQuotationAndBrackets(col.trim())); + } + return columns; + } + + /** + * Strips out quotation characters ('"[]) and schema prefixes from identifiers + * (table / columns / constraint names) + * @param input an identifier + * @return the important part of the identifier + */ + private static String stripPrefixQuotationAndBrackets(String input) { + String output = input.replaceAll("[\\`\\\"\\[\\]]", "").replaceAll("[^\\.]*\\.", ""); + return output; + } + + private static Optional<String> firstMatchingGroup(Pattern p, String s) { + Matcher m = p.matcher(s); + if (m.matches()) { + return Optional.of(m.group(1)); + } + else { + return Optional.absent(); + } + } + + private static Map<String, Table> parseTableDefs(List<String> statements) { + // Find all CREATE TABLE statements + List<String> createTables = new ArrayList<>(); + for (String stmt: statements) { + if (stmt.matches(".*create\\s+table.*")) { + String content = stmt.substring(stmt.indexOf('(') + 1, stmt.lastIndexOf(')')); + // Replace , with | within PK/FK/UQ definitions so that we will be able to partition column/constraint definitions by , + Matcher m = InnerList.matcher(content); + while (m.find()) { + String innerList = m.group(); + stmt = stmt.replace(innerList, innerList.replaceAll("\\,", "|")); + } + createTables.add(stmt); + } + } + List<Table> tables = new ArrayList<>(); + // Parse CREATE TABLE statements + for(String ct: createTables) { + String tableName = stripPrefixQuotationAndBrackets(firstMatchingGroup(TableName, ct).get()); + List<String> columns = new ArrayList<>(); + Optional<SimpleConstraint> pk = Optional.absent(); + List<FKConstraint> fks = new ArrayList<>(); + List<SimpleConstraint> uqs = new ArrayList<>(); + final String innerPart = ct.substring(ct.indexOf('(') + 1, ct.lastIndexOf(')')); + for (String definition: Splitter.on(',').split(innerPart)) { + definition = definition.trim(); + assertNounnamedConstraint(tableName, definition); + Matcher pkMatcher = PK.matcher(definition); + Matcher pkClustMatcher = PKClustered.matcher(definition); + Matcher unnamedPkMatcher = UnnamedPK.matcher(definition); + Matcher pkColumnMatcher = PKColumn.matcher(definition); + Matcher fkMatcher = FK.matcher(definition); + Matcher uqMatcher = UQ.matcher(definition); + Matcher unnamedFkMatcher = UnnamedFK.matcher(definition); + Matcher unnamedUqMatcher = UnnamedUQ.matcher(definition); + Matcher uqColumnMatcher = UQColumn.matcher(definition); + Matcher colMatcher = Col.matcher(definition); + if (pkMatcher.matches()) { + pk = Optional.of(Constraint.pk(pkMatcher.group(1),toColumns(pkMatcher.group(2)))); + } else if (pkMatcher.matches()) { + pk = Optional.of(Constraint.pk(stripPrefixQuotationAndBrackets(pkMatcher.group(1)),toColumns(pkMatcher.group(2)))); + } else if (pkClustMatcher.matches()) { + pk = Optional.of(Constraint.pk(stripPrefixQuotationAndBrackets(pkClustMatcher.group(1)),toColumns(pkClustMatcher.group(2)))); + } else if (unnamedPkMatcher.matches()) { + pk = Optional.of(Constraint.pk("<default>",toColumns(unnamedPkMatcher.group(1)))); + } else if (fkMatcher.matches()) { + fks.add(Constraint.fk(fkMatcher.group(1), toColumns(fkMatcher.group(2)), stripPrefixQuotationAndBrackets(fkMatcher.group(3)), toColumns(fkMatcher.group(4)))); + } else if (unnamedFkMatcher.matches()) { + fks.add(Constraint.fk("<default>", toColumns(unnamedFkMatcher.group(1)), stripPrefixQuotationAndBrackets(unnamedFkMatcher.group(2)), toColumns(unnamedFkMatcher.group(3)))); + } else if (uqMatcher.matches()) { + uqs.add(Constraint.uq(stripPrefixQuotationAndBrackets(uqMatcher.group(1)),toColumns(uqMatcher.group(2)))); + } else if (unnamedUqMatcher.matches()) { + uqs.add(Constraint.uq("<default>", toColumns(unnamedUqMatcher.group(1)))); + } else if (colMatcher.matches()) { + String colName = stripPrefixQuotationAndBrackets(colMatcher.group(1)); + columns.add(colName); + // column definitions can include PK/UQ declaration, e.g: x integer not null primary key + if (pkColumnMatcher.matches()) { + pk = Optional.of(Constraint.pk("<default>", Collections.singleton(colName))); + } else if (uqColumnMatcher.matches()) { + uqs.add(Constraint.uq("<default>", Collections.singleton(colName))); + } + } else { + LOG.warn("Unexpected definition: {}, context: {}", definition, ct); + } + } + if (columns.isEmpty()) { + throw new IllegalStateException("No columns found in table " + tableName); + } + checkDupes("columns of table " + tableName, columns); + tables.add(new Table(tableName, + ImmutableSet.copyOf(columns), + pk, + ImmutableSet.copyOf(fks), + ImmutableSet.copyOf(uqs))); + } + Map<String, Table> tableMap = Maps.newHashMap(); + for(Table t: tables) { + if (tableMap.containsKey(t.name)) throw new IllegalStateException("Duplicate table definition: " + t.name); + tableMap.put(t.name, t); + } + return tableMap; + } + + private static void checkDupes(String objectName, List<? extends Object> items) { + Set<Object> set = Sets.newHashSet(items); + if (set.size() < items.size()) { + throw new IllegalStateException(String.format("Duplicates found in %s: %s", objectName, Iterables.toString(items))); + } + } + + /** + * Currently we only fail on unnamed primary keys. + * @param tableName + * @param definition + */ + private static void assertNounnamedConstraint(String tableName, String definition) { + if (tableName.contains("qrtz")) { + LOG.debug("Skipp checking quartz table: {}", tableName); + } + else { + for (Pattern unnamedConstraint: CheckedUnnamedConstraints) { + if (unnamedConstraint.matcher(definition).matches()) { + throw new IllegalStateException( + String.format("Found invalid (unnamed) constraint in table %s: %s", tableName, definition)); + } + } + for (Pattern unnamedConstraint: UncheckedUnnamedConstraints) { + if (unnamedConstraint.matcher(definition).matches()) { + LOG.info("Found unnamed constraint in table {}: {}", tableName, definition); + } + } + } + } + + private static DDL loadDdl(String dbType) throws Exception { + List<String> lines = loadFile(dbType); + List<String> statements = groupStatements(lines); + Map<String, Table> tables = parseTableDefs(statements); + List<String> alterTables = new ArrayList<>(); + for (String stmt: statements) { + if (stmt.matches(".*alter\\s+table.*")) alterTables.add(stmt); + } + return new DDL(dbType, tables, alterTables); + } + +} + +/** + * Represents a DDL + */ +class DDL { + final String dbType; + final Map<String, Table> tables; + final List<String> alterTables; + + Set<String> tableNames() { return tables.keySet(); } + + DDL(String dbType, Map<String, Table> tables, List<String> alterTables) { + this.dbType = dbType; + this.tables = tables; + this.alterTables = alterTables; + } +} + +/** + * Represents a datbase table + */ +class Table { + final String name; + final ImmutableSet<String> columns; + final Optional<SimpleConstraint> primaryKey; + final ImmutableSet<FKConstraint> foreignKeys; + final ImmutableSet<SimpleConstraint> uniqueConstraints; + + Table(String name, Set<String> columns, Optional<SimpleConstraint> primaryKey, Set<FKConstraint> foreignKeys, Set<SimpleConstraint> uniqueConstraints) { + this.name = name; + this.columns = + (columns instanceof ImmutableSet) ? (ImmutableSet<String>)columns : ImmutableSet.copyOf(columns); + this.primaryKey = primaryKey; + this.foreignKeys = + (foreignKeys instanceof ImmutableSet) ? (ImmutableSet<FKConstraint>)foreignKeys : ImmutableSet.copyOf(foreignKeys); + this.uniqueConstraints = + (uniqueConstraints instanceof ImmutableSet) ? (ImmutableSet<SimpleConstraint>) uniqueConstraints : ImmutableSet.copyOf(uniqueConstraints); + } + + @Override + public String toString() { + return String.format("TABLE name: %s, columns: %s, pk: %s, fks: %s, uqs: %s", + name, Iterables.toString(columns), primaryKey, Iterables.toString(foreignKeys), Iterables.toString(uniqueConstraints)); + } +} + +/** + * Represents a constraint. + */ +abstract class Constraint<ContentType> { + abstract String name(); + abstract ContentType content(); + + static SimpleConstraint pk(String name, Collection<String> columns) { + Preconditions.checkArgument(!columns.isEmpty(), "Columns must not be empty."); + return new SimpleConstraint(name, "PK", columns); + } + + static SimpleConstraint uq(String name, Collection<String> columns) { + Preconditions.checkArgument(!columns.isEmpty(), "Columns must not be empty."); + return new SimpleConstraint(name, "PK", columns); + } + + static FKConstraint fk(String name, Collection<String> columns, String referredTableName, Collection<String> referredColumns) { + Preconditions.checkArgument(!columns.isEmpty(), "Columns must not be empty."); + Preconditions.checkArgument(!referredColumns.isEmpty(), "Referred columns must not be empty."); + return new FKConstraint(name, columns, referredTableName, referredColumns); + } + +} + +/** + * Represents a simple constraint (PK/UQ) + */ +class SimpleConstraint extends Constraint<Set<String>> { + final String name; + final String type; + final ImmutableSet<String> columns; // These have favorable equals/hashcode semantics + + SimpleConstraint(String name, String type, Collection<String> columns) { + this.name = name; + this.type = type; + this.columns = (columns instanceof ImmutableSet) ? (ImmutableSet<String>) columns : ImmutableSet.copyOf(columns); + } + + public String name() { + return name; + } + + public ImmutableSet<String> content() { return columns; } + + @Override public String toString() { + return String.format("%s %s [%s]", type, name, Joiner.on(',').join(columns)); + } + + @Override + public boolean equals(Object o) { + if (this == o) return true; + if (o == null || getClass() != o.getClass()) return false; + SimpleConstraint that = (SimpleConstraint) o; + if (!name.equals(that.name)) return false; + if (!type.equals(that.type)) return false; + return columns.equals(that.columns); + } + + @Override + public int hashCode() { + int result = name.hashCode(); + result = 31 * result + type.hashCode(); + result = 31 * result + columns.hashCode(); + return result; + } + +} + +class FKConstraintContent { + final ImmutableSet<String> columns; // These have favorable equals/hashcode semantics + final String referredTable; + final ImmutableSet<String> referredColumns; // These have favorable equals/hashcode semantics + + public FKConstraintContent(Collection<String> columns, String referredTable, Collection<String> referredColumns) { + this.columns = columns instanceof ImmutableSet ? (ImmutableSet<String>)columns : ImmutableSet.copyOf(columns); + this.referredTable = referredTable; + this.referredColumns = referredColumns instanceof ImmutableSet ? (ImmutableSet<String>)referredColumns : + ImmutableSet.copyOf(referredColumns); + } + + @Override + public boolean equals(Object o) { + if (this == o) return true; + if (o == null || getClass() != o.getClass()) return false; + FKConstraintContent that = (FKConstraintContent) o; + if (!columns.equals(that.columns)) return false; + if (!referredTable.equals(that.referredTable)) return false; + return referredColumns.equals(that.referredColumns); + + } + + @Override + public int hashCode() { + int result = columns.hashCode(); + result = 31 * result + referredTable.hashCode(); + result = 31 * result + referredColumns.hashCode(); + return result; + } + + @Override public String toString() { + return String.format("[%s] --> %s [%s]", Joiner.on(',').join(columns), referredTable, Joiner.on(',').join(referredColumns)); + } + +} + +class FKConstraint extends Constraint<FKConstraintContent> { + final String name; + final FKConstraintContent content; + + FKConstraint(String name, Collection<String> columns, String referredTable, Collection<String> referredColumns) { + this.name = name; + this.content = new FKConstraintContent(columns, referredTable, referredColumns); + } + + public String name() { + return name; + } + + public FKConstraintContent content() { + return content; + } + + @Override public String toString() { + return String.format("FK name:%s content: %s", name, content); + } + + @Override + public boolean equals(Object o) { + if (this == o) return true; + if (o == null || getClass() != o.getClass()) return false; + FKConstraint that = (FKConstraint) o; + if (!name.equals(that.name)) return false; + return content.equals(that.content); + } + + @Override + public int hashCode() { + int result = name.hashCode(); + result = 31 * result + content.hashCode(); + return result; + } +}