You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ambari.apache.org by st...@apache.org on 2016/04/21 15:51:20 UTC
[7/8] ambari git commit: AMBARI-15915. SQL constraints: Inline
constraints and name them in CREATE table. (Balazs Bence Sari via stoader)
http://git-wip-us.apache.org/repos/asf/ambari/blob/346dfe7e/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql
index 2b214c4..b0264f2 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql
@@ -31,8 +31,19 @@ CREATE TABLE stack(
stack_id BIGINT NOT NULL,
stack_name VARCHAR(100) NOT NULL,
stack_version VARCHAR(100) NOT NULL,
- PRIMARY KEY (stack_id)
-);
+ CONSTRAINT PK_stack PRIMARY KEY (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 (resource_type_id));
+
+CREATE TABLE adminresource (
+ resource_id BIGINT NOT NULL,
+ resource_type_id INTEGER NOT NULL,
+ CONSTRAINT PK_adminresource PRIMARY KEY (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,
@@ -44,8 +55,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 (cluster_id)
-);
+ CONSTRAINT PK_clusters PRIMARY KEY (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,
@@ -57,8 +69,11 @@ CREATE TABLE clusterconfig (
config_data LONGTEXT NOT NULL,
config_attributes LONGTEXT,
create_timestamp BIGINT NOT NULL,
- PRIMARY KEY (config_id)
-);
+ CONSTRAINT PK_clusterconfig PRIMARY KEY (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,
@@ -70,31 +85,74 @@ CREATE TABLE serviceconfig (
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
group_id BIGINT,
note LONGTEXT,
- PRIMARY KEY (service_config_id)
-);
+ CONSTRAINT PK_serviceconfig PRIMARY KEY (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,
+ cpu_info VARCHAR(255) NOT NULL,
+ discovery_status VARCHAR(2000) NOT NULL,
+ host_attributes LONGTEXT NOT NULL,
+ ipv4 VARCHAR(255),
+ ipv6 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,
+ ph_cpu_count INTEGER,
+ public_host_name VARCHAR(255),
+ rack_info VARCHAR(255) NOT NULL,
+ total_mem BIGINT NOT NULL,
+ CONSTRAINT PK_hosts PRIMARY KEY (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(service_config_id, host_id));
+ CONSTRAINT PK_serviceconfighosts PRIMARY KEY (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(service_config_id, config_id));
+ CONSTRAINT PK_serviceconfigmapping PRIMARY KEY (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 clusterservices (
service_name VARCHAR(255) NOT NULL,
cluster_id BIGINT NOT NULL,
service_enabled INTEGER NOT NULL,
- PRIMARY KEY (service_name, cluster_id));
+ CONSTRAINT PK_clusterservices PRIMARY KEY (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 (cluster_id)
-);
+ CONSTRAINT PK_clusterstate PRIMARY KEY (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 MEDIUMTEXT NOT NULL,
+ repo_type VARCHAR(255) DEFAULT 'STANDARD' NOT NULL,
+ version_url VARCHAR(1024),
+ version_xml MEDIUMTEXT,
+ version_xsd VARCHAR(512),
+ parent_id BIGINT,
+ CONSTRAINT PK_repo_version PRIMARY KEY (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,
@@ -104,7 +162,23 @@ CREATE TABLE cluster_version (
start_time BIGINT NOT NULL,
end_time BIGINT,
user_name VARCHAR(32),
- PRIMARY KEY (id));
+ CONSTRAINT PK_cluster_version PRIMARY KEY (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(100) 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(100) 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,
@@ -117,8 +191,10 @@ CREATE TABLE hostcomponentdesiredstate (
maintenance_state VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
restart_required TINYINT(1) NOT NULL DEFAULT 0,
- PRIMARY KEY (cluster_id, component_name, host_id, service_name)
-);
+ CONSTRAINT PK_hostcomponentdesiredstate PRIMARY KEY (cluster_id, component_name, host_id, service_name),
+ CONSTRAINT FK_hcdesiredstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_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));
CREATE TABLE hostcomponentstate (
id BIGINT NOT NULL,
@@ -131,30 +207,13 @@ CREATE TABLE hostcomponentstate (
service_name VARCHAR(100) NOT NULL,
upgrade_state VARCHAR(32) NOT NULL DEFAULT 'NONE',
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
- CONSTRAINT pk_hostcomponentstate PRIMARY KEY (id)
-);
+ CONSTRAINT pk_hostcomponentstate PRIMARY KEY (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 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,
- cpu_info VARCHAR(255) NOT NULL,
- discovery_status VARCHAR(2000) NOT NULL,
- host_attributes LONGTEXT NOT NULL,
- ipv4 VARCHAR(255),
- ipv6 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,
- ph_cpu_count INTEGER,
- public_host_name VARCHAR(255),
- rack_info VARCHAR(255) NOT NULL,
- total_mem BIGINT NOT NULL,
- PRIMARY KEY (host_id));
-
CREATE TABLE hoststate (
agent_version VARCHAR(255) NOT NULL,
available_mem BIGINT NOT NULL,
@@ -163,27 +222,17 @@ CREATE TABLE hoststate (
host_id BIGINT NOT NULL,
time_in_state BIGINT NOT NULL,
maintenance_state VARCHAR(512),
- PRIMARY KEY (host_id));
+ CONSTRAINT PK_hoststate PRIMARY KEY (host_id),
+ CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (host_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 (id));
-
-CREATE TABLE servicecomponentdesiredstate (
- id BIGINT NOT NULL,
- component_name VARCHAR(100) 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(100) 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 PK_host_version PRIMARY KEY (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 servicedesiredstate (
cluster_id BIGINT NOT NULL,
@@ -193,8 +242,20 @@ CREATE TABLE servicedesiredstate (
service_name VARCHAR(255) NOT NULL,
maintenance_state VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
- PRIMARY KEY (cluster_id, service_name)
-);
+ CONSTRAINT PK_servicedesiredstate PRIMARY KEY (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 (principal_type_id));
+
+CREATE TABLE adminprincipal (
+ principal_id BIGINT NOT NULL,
+ principal_type_id INTEGER NOT NULL,
+ CONSTRAINT PK_adminprincipal PRIMARY KEY (principal_id),
+ CONSTRAINT FK_principal_principal_type_id FOREIGN KEY (principal_type_id) REFERENCES adminprincipaltype(principal_type_id));
CREATE TABLE users (
user_id INTEGER,
@@ -206,25 +267,79 @@ CREATE TABLE users (
user_password VARCHAR(255),
active INTEGER NOT NULL DEFAULT 1,
active_widget_layouts VARCHAR(1024) DEFAULT NULL,
- PRIMARY KEY (user_id));
+ CONSTRAINT PK_users PRIMARY KEY (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 (group_id));
+ CONSTRAINT PK_groups PRIMARY KEY (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 (member_id));
+ CONSTRAINT PK_members PRIMARY KEY (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 (
- task_id BIGINT NOT NULL,
- command LONGBLOB,
- PRIMARY KEY (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 (schedule_id));
+
+CREATE TABLE request (
+ request_id BIGINT NOT NULL,
+ cluster_id BIGINT,
+ request_schedule_id BIGINT,
+ command_name VARCHAR(255),
+ create_time BIGINT NOT NULL,
+ end_time BIGINT NOT NULL,
+ exclusive_execution TINYINT(1) NOT NULL DEFAULT 0,
+ inputs LONGBLOB,
+ request_context VARCHAR(255),
+ request_type VARCHAR(255),
+ start_time BIGINT NOT NULL,
+ status VARCHAR(255),
+ CONSTRAINT PK_request PRIMARY KEY (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,
+ 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 LONGBLOB,
+ command_params LONGBLOB,
+ host_params LONGBLOB,
+ CONSTRAINT PK_stage PRIMARY KEY (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,
@@ -250,42 +365,23 @@ CREATE TABLE host_role_command (
structured_out LONGBLOB,
command_detail VARCHAR(255),
custom_command_name VARCHAR(255),
- PRIMARY KEY (task_id));
+ CONSTRAINT PK_host_role_command PRIMARY KEY (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 (
+ task_id BIGINT NOT NULL,
+ command LONGBLOB,
+ CONSTRAINT PK_execution_command PRIMARY KEY (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 DOUBLE NOT NULL,
- PRIMARY KEY (role, request_id, stage_id));
-
-CREATE TABLE stage (
- stage_id BIGINT NOT NULL,
- request_id BIGINT NOT NULL,
- cluster_id BIGINT,
- 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 LONGBLOB,
- command_params LONGBLOB,
- host_params LONGBLOB,
- PRIMARY KEY (stage_id, request_id));
-
-CREATE TABLE request (
- request_id BIGINT NOT NULL,
- cluster_id BIGINT,
- request_schedule_id BIGINT,
- command_name VARCHAR(255),
- create_time BIGINT NOT NULL,
- end_time BIGINT NOT NULL,
- exclusive_execution TINYINT(1) NOT NULL DEFAULT 0,
- inputs LONGBLOB,
- request_context VARCHAR(255),
- request_type VARCHAR(255),
- start_time BIGINT NOT NULL,
- status VARCHAR(255),
- PRIMARY KEY (request_id));
+ CONSTRAINT PK_role_success_criteria PRIMARY KEY (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,
@@ -293,7 +389,8 @@ CREATE TABLE requestresourcefilter (
service_name VARCHAR(255),
component_name VARCHAR(255),
hosts LONGBLOB,
- PRIMARY KEY (filter_id));
+ CONSTRAINT PK_requestresourcefilter PRIMARY KEY (filter_id),
+ CONSTRAINT FK_reqresfilter_req_id FOREIGN KEY (request_id) REFERENCES request (request_id));
CREATE TABLE requestoperationlevel (
operation_level_id BIGINT NOT NULL,
@@ -303,11 +400,12 @@ 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 (operation_level_id));
+ CONSTRAINT PK_requestoperationlevel PRIMARY KEY (operation_level_id),
+ CONSTRAINT FK_req_op_level_req_id FOREIGN KEY (request_id) REFERENCES request (request_id));
CREATE TABLE key_value_store (`key` VARCHAR(255),
`value` LONGTEXT,
- PRIMARY KEY (`key`));
+ CONSTRAINT PK_key_value_store PRIMARY KEY (`key`));
CREATE TABLE clusterconfigmapping (
type_name VARCHAR(255) NOT NULL,
@@ -316,7 +414,8 @@ CREATE TABLE clusterconfigmapping (
selected INTEGER NOT NULL DEFAULT 0,
version_tag VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
- PRIMARY KEY (type_name, create_timestamp, cluster_id));
+ CONSTRAINT PK_clusterconfigmapping PRIMARY KEY (type_name, create_timestamp, cluster_id),
+ CONSTRAINT clusterconfigmappingcluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id));
CREATE TABLE hostconfigmapping (
create_timestamp BIGINT NOT NULL,
@@ -327,31 +426,26 @@ CREATE TABLE hostconfigmapping (
service_name VARCHAR(255),
version_tag VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
- PRIMARY KEY (create_timestamp, host_id, cluster_id, type_name));
+ CONSTRAINT PK_hostconfigmapping PRIMARY KEY (create_timestamp, host_id, cluster_id, type_name),
+ 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` LONGTEXT,
- PRIMARY KEY (`metainfo_key`));
+ CONSTRAINT PK_metainfo PRIMARY KEY (`metainfo_key`));
CREATE TABLE ClusterHostMapping (
cluster_id BIGINT NOT NULL,
host_id BIGINT NOT NULL,
- PRIMARY KEY (cluster_id, host_id));
+ CONSTRAINT PK_ClusterHostMapping PRIMARY KEY (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 ambari_sequences (
sequence_name VARCHAR(255),
sequence_value DECIMAL(38) NOT NULL,
- PRIMARY KEY (sequence_name));
-
-CREATE TABLE confgroupclusterconfigmapping (
- config_group_id BIGINT NOT NULL,
- cluster_id BIGINT NOT NULL,
- config_type VARCHAR(100) NOT NULL,
- version_tag VARCHAR(100) NOT NULL,
- user_name VARCHAR(100) DEFAULT '_db',
- create_timestamp BIGINT NOT NULL,
- PRIMARY KEY(config_group_id, cluster_id, config_type));
+ CONSTRAINT PK_ambari_sequences PRIMARY KEY (sequence_name));
CREATE TABLE configgroup (
group_id BIGINT,
@@ -361,34 +455,26 @@ CREATE TABLE configgroup (
description VARCHAR(1024),
create_timestamp BIGINT NOT NULL,
service_name VARCHAR(255),
- PRIMARY KEY(group_id));
+ CONSTRAINT PK_configgroup PRIMARY KEY (group_id),
+ CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id));
+
+CREATE TABLE confgroupclusterconfigmapping (
+ config_group_id BIGINT NOT NULL,
+ cluster_id BIGINT NOT NULL,
+ config_type VARCHAR(100) NOT NULL,
+ version_tag VARCHAR(100) NOT NULL,
+ user_name VARCHAR(100) DEFAULT '_db',
+ create_timestamp BIGINT NOT NULL,
+ CONSTRAINT PK_confgroupclustercfgmapping PRIMARY KEY (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(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(schedule_id));
+ CONSTRAINT PK_configgrouphostmapping PRIMARY KEY (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,
@@ -400,35 +486,39 @@ CREATE TABLE requestschedulebatchrequest (
request_status varchar(255),
return_code smallint,
return_message varchar(2000),
- PRIMARY KEY(schedule_id, batch_id));
+ CONSTRAINT PK_requestschedulebatchrequest PRIMARY KEY (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(blueprint_name)
-);
+ CONSTRAINT PK_blueprint PRIMARY KEY (blueprint_name),
+ CONSTRAINT FK_blueprint_stack_id FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
CREATE TABLE hostgroup (
blueprint_name VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
cardinality VARCHAR(255) NOT NULL,
- PRIMARY KEY(blueprint_name, name));
+ CONSTRAINT PK_hostgroup PRIMARY KEY (blueprint_name, name),
+ CONSTRAINT FK_hg_blueprint_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name));
CREATE TABLE hostgroup_component (
blueprint_name VARCHAR(100) NOT NULL,
hostgroup_name VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
provision_action VARCHAR(100),
- PRIMARY KEY(blueprint_name, hostgroup_name, name));
+ CONSTRAINT PK_hostgroup_component PRIMARY KEY (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(100) NOT NULL,
type_name VARCHAR(100) NOT NULL,
config_data LONGTEXT NOT NULL,
config_attributes LONGTEXT,
- PRIMARY KEY(blueprint_name, type_name));
+ CONSTRAINT PK_blueprint_configuration PRIMARY KEY (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,
@@ -445,7 +535,8 @@ CREATE TABLE hostgroup_configuration (
type_name VARCHAR(100) NOT NULL,
config_data LONGTEXT NOT NULL,
config_attributes LONGTEXT,
- PRIMARY KEY(blueprint_name, hostgroup_name, type_name));
+ CONSTRAINT PK_hostgroup_configuration PRIMARY KEY (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,
@@ -459,16 +550,8 @@ CREATE TABLE viewmain (
archive VARCHAR(255),
mask VARCHAR(255),
system_view TINYINT(1) NOT NULL DEFAULT 0,
- PRIMARY KEY(view_name));
-
-CREATE TABLE viewinstancedata (
- view_instance_id BIGINT,
- view_name VARCHAR(100) NOT NULL,
- view_instance_name VARCHAR(100) NOT NULL,
- name VARCHAR(100) NOT NULL,
- user_name VARCHAR(100) NOT NULL,
- value VARCHAR(2000),
- PRIMARY KEY(VIEW_INSTANCE_ID, NAME, USER_NAME));
+ CONSTRAINT PK_viewmain PRIMARY KEY (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,
@@ -484,14 +567,29 @@ CREATE TABLE viewinstance (
alter_names TINYINT(1) NOT NULL DEFAULT 1,
cluster_handle VARCHAR(255),
short_url VARCHAR (255),
- PRIMARY KEY(view_instance_id));
+ CONSTRAINT PK_viewinstance PRIMARY KEY (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(100) NOT NULL,
+ view_instance_name VARCHAR(100) NOT NULL,
+ name VARCHAR(100) NOT NULL,
+ user_name VARCHAR(100) NOT NULL,
+ value VARCHAR(2000),
+ CONSTRAINT PK_viewinstancedata PRIMARY KEY (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(100) NOT NULL,
view_instance_name VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
value VARCHAR(2000),
- PRIMARY KEY(view_name, view_instance_name, name));
+ CONSTRAINT PK_viewinstanceproperty PRIMARY KEY (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(100) NOT NULL,
@@ -503,7 +601,8 @@ CREATE TABLE viewparameter (
cluster_config VARCHAR(255),
required CHAR(1),
masked CHAR(1),
- PRIMARY KEY(view_name, name));
+ CONSTRAINT PK_viewparameter PRIMARY KEY (view_name, name),
+ CONSTRAINT FK_viewparam_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name));
CREATE TABLE viewresource (
view_name VARCHAR(100) NOT NULL,
@@ -514,7 +613,8 @@ CREATE TABLE viewresource (
provider VARCHAR(255),
service VARCHAR(255),
resource VARCHAR(255),
- PRIMARY KEY(view_name, name));
+ CONSTRAINT PK_viewresource PRIMARY KEY (view_name, name),
+ CONSTRAINT FK_viewres_view_name FOREIGN KEY (view_name) REFERENCES viewmain(view_name));
CREATE TABLE viewentity (
id BIGINT NOT NULL,
@@ -522,27 +622,8 @@ CREATE TABLE viewentity (
view_instance_name VARCHAR(100) NOT NULL,
class_name VARCHAR(255) NOT NULL,
id_property VARCHAR(255),
- PRIMARY KEY(id));
-
-CREATE TABLE adminresourcetype (
- resource_type_id INTEGER NOT NULL,
- resource_type_name VARCHAR(255) NOT NULL,
- PRIMARY KEY(resource_type_id));
-
-CREATE TABLE adminresource (
- resource_id BIGINT NOT NULL,
- resource_type_id INTEGER NOT NULL,
- PRIMARY KEY(resource_id));
-
-CREATE TABLE adminprincipaltype (
- principal_type_id INTEGER NOT NULL,
- principal_type_name VARCHAR(255) NOT NULL,
- PRIMARY KEY(principal_type_id));
-
-CREATE TABLE adminprincipal (
- principal_id BIGINT NOT NULL,
- principal_type_id INTEGER NOT NULL,
- PRIMARY KEY(principal_id));
+ CONSTRAINT PK_viewentity PRIMARY KEY (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,
@@ -550,38 +631,31 @@ CREATE TABLE adminpermission (
resource_type_id INTEGER NOT NULL,
permission_label VARCHAR(255),
sort_order SMALLINT NOT NULL DEFAULT 1,
- PRIMARY KEY(permission_id));
+ CONSTRAINT PK_adminpermission PRIMARY KEY (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(privilege_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 MEDIUMTEXT NOT NULL,
- repo_type VARCHAR(255) DEFAULT 'STANDARD' NOT NULL,
- version_url VARCHAR(1024),
- version_xml MEDIUMTEXT,
- version_xsd VARCHAR(512),
- parent_id BIGINT,
- PRIMARY KEY(repo_version_id)
-);
+ CONSTRAINT PK_adminprivilege PRIMARY KEY (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 widget (
id BIGINT NOT NULL,
@@ -596,7 +670,7 @@ CREATE TABLE widget (
widget_values LONGTEXT,
properties LONGTEXT,
cluster_id BIGINT NOT NULL,
- PRIMARY KEY(id)
+ CONSTRAINT PK_widget PRIMARY KEY (id)
);
CREATE TABLE widget_layout (
@@ -607,21 +681,22 @@ CREATE TABLE widget_layout (
user_name VARCHAR(255) NOT NULL,
display_name VARCHAR(255),
cluster_id BIGINT NOT NULL,
- PRIMARY KEY(id)
+ CONSTRAINT PK_widget_layout PRIMARY KEY (id)
);
CREATE TABLE widget_layout_user_widget (
widget_layout_id BIGINT NOT NULL,
widget_id BIGINT NOT NULL,
widget_order smallint,
- PRIMARY KEY(widget_layout_id, widget_id)
-);
+ CONSTRAINT PK_widget_layout_user_widget PRIMARY KEY (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 artifact (
artifact_name VARCHAR(100) NOT NULL,
foreign_keys VARCHAR(100) NOT NULL,
artifact_data LONGTEXT NOT NULL,
- PRIMARY KEY(artifact_name, foreign_keys));
+ CONSTRAINT PK_artifact PRIMARY KEY (artifact_name, foreign_keys));
CREATE TABLE topology_request (
id BIGINT NOT NULL,
@@ -631,8 +706,8 @@ CREATE TABLE topology_request (
cluster_properties LONGTEXT,
cluster_attributes LONGTEXT,
description VARCHAR(1024),
- PRIMARY KEY (id)
-);
+ CONSTRAINT PK_topology_request PRIMARY KEY (id),
+ CONSTRAINT FK_topology_request_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id));
CREATE TABLE topology_hostgroup (
id BIGINT NOT NULL,
@@ -640,8 +715,8 @@ CREATE TABLE topology_hostgroup (
group_properties LONGTEXT,
group_attributes LONGTEXT,
request_id BIGINT NOT NULL,
- PRIMARY KEY (id)
-);
+ CONSTRAINT PK_topology_hostgroup PRIMARY KEY (id),
+ CONSTRAINT FK_hostgroup_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id));
CREATE TABLE topology_host_info (
id BIGINT NOT NULL,
@@ -651,15 +726,16 @@ CREATE TABLE topology_host_info (
host_count INTEGER,
predicate VARCHAR(2048),
rack_info VARCHAR(255),
- PRIMARY KEY (id)
-);
+ CONSTRAINT PK_topology_host_info PRIMARY KEY (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 (id)
-);
+ CONSTRAINT PK_topology_logical_request PRIMARY KEY (id),
+ CONSTRAINT FK_logicalreq_req_id FOREIGN KEY (request_id) REFERENCES topology_request(id));
CREATE TABLE topology_host_request (
id BIGINT NOT NULL,
@@ -667,23 +743,25 @@ CREATE TABLE topology_host_request (
group_id BIGINT NOT NULL,
stage_id BIGINT NOT NULL,
host_name VARCHAR(255),
- PRIMARY KEY (id)
-);
+ CONSTRAINT PK_topology_host_request PRIMARY KEY (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 (id)
-);
+ CONSTRAINT PK_topology_host_task PRIMARY KEY (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 (id)
-);
+ CONSTRAINT PK_topology_logical_task PRIMARY KEY (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,
@@ -692,7 +770,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)
);
@@ -710,7 +788,7 @@ CREATE TABLE upgrade (
skip_sc_failures TINYINT(1) NOT NULL DEFAULT 0,
downgrade_allowed TINYINT(1) NOT NULL DEFAULT 1,
suspended TINYINT(1) DEFAULT 0 NOT NULL,
- PRIMARY KEY (upgrade_id),
+ CONSTRAINT PK_upgrade PRIMARY KEY (upgrade_id),
FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id),
FOREIGN KEY (request_id) REFERENCES request(request_id)
);
@@ -720,7 +798,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 (upgrade_group_id),
+ CONSTRAINT PK_upgrade_group PRIMARY KEY (upgrade_group_id),
FOREIGN KEY (upgrade_id) REFERENCES upgrade(upgrade_id)
);
@@ -732,7 +810,7 @@ CREATE TABLE upgrade_item (
hosts TEXT,
tasks TEXT,
item_text VARCHAR(1024),
- PRIMARY KEY (upgrade_item_id),
+ CONSTRAINT PK_upgrade_item PRIMARY KEY (upgrade_item_id),
FOREIGN KEY (upgrade_group_id) REFERENCES upgrade_group(upgrade_group_id)
);
@@ -755,132 +833,34 @@ CREATE INDEX idx_hrc_request_id ON host_role_command (request_id);
CREATE INDEX idx_hrc_status_role ON host_role_command (status, role);
CREATE INDEX idx_rsc_request_id ON role_success_criteria (request_id);
--- altering tables by creating unique 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);
+-------- altering tables by creating foreign keys ----------
+-- #1: This should always be an exceptional case. FK constraints should be inlined in table definitions when possible
+-- (reorder table definitions if necessary).
+-- #2: Oracle has a limitation of 30 chars in the constraint names name, and we should use the same constraint names in all DB types.
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 FK_hcdesiredstate_host_id 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 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 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 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(principal_name)
+ CONSTRAINT PK_kerberos_principal PRIMARY KEY (principal_name)
);
CREATE TABLE kerberos_principal_host (
principal_name VARCHAR(255) NOT NULL,
host_id BIGINT NOT NULL,
- PRIMARY KEY(principal_name, host_id)
-);
+ CONSTRAINT PK_kerberos_principal_host PRIMARY KEY (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 TEXT 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
@@ -902,7 +882,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 (definition_id),
+ CONSTRAINT PK_alert_definition PRIMARY KEY (definition_id),
FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id),
CONSTRAINT uni_alert_def_name UNIQUE(cluster_id,definition_name)
);
@@ -919,7 +899,7 @@ CREATE TABLE alert_history (
alert_label VARCHAR(1024),
alert_state VARCHAR(255) NOT NULL,
alert_text TEXT,
- PRIMARY KEY (alert_id),
+ CONSTRAINT PK_alert_history PRIMARY KEY (alert_id),
FOREIGN KEY (alert_definition_id) REFERENCES alert_definition(definition_id),
FOREIGN KEY (cluster_id) REFERENCES clusters(cluster_id)
);
@@ -934,7 +914,7 @@ CREATE TABLE alert_current (
latest_text TEXT,
occurrences BIGINT NOT NULL DEFAULT 1,
firmness VARCHAR(255) NOT NULL DEFAULT 'HARD',
- PRIMARY KEY (alert_id),
+ CONSTRAINT PK_alert_current PRIMARY KEY (alert_id),
FOREIGN KEY (definition_id) REFERENCES alert_definition(definition_id),
FOREIGN KEY (history_id) REFERENCES alert_history(alert_id)
);
@@ -945,7 +925,7 @@ CREATE TABLE alert_group (
group_name VARCHAR(255) NOT NULL,
is_default SMALLINT NOT NULL DEFAULT 0,
service_name VARCHAR(255),
- PRIMARY KEY (group_id),
+ CONSTRAINT PK_alert_group PRIMARY KEY (group_id),
CONSTRAINT uni_alert_group_name UNIQUE(cluster_id,group_name)
);
@@ -956,7 +936,7 @@ CREATE TABLE alert_target (
properties TEXT,
description VARCHAR(1024),
is_global SMALLINT NOT NULL DEFAULT 0,
- PRIMARY KEY (target_id)
+ CONSTRAINT PK_alert_target PRIMARY KEY (target_id)
);
CREATE TABLE alert_target_states (
@@ -968,7 +948,7 @@ CREATE TABLE alert_target_states (
CREATE TABLE alert_group_target (
group_id BIGINT NOT NULL,
target_id BIGINT NOT NULL,
- PRIMARY KEY (group_id, target_id),
+ CONSTRAINT PK_alert_group_target PRIMARY KEY (group_id, target_id),
FOREIGN KEY (group_id) REFERENCES alert_group(group_id),
FOREIGN KEY (target_id) REFERENCES alert_target(target_id)
);
@@ -976,7 +956,7 @@ CREATE TABLE alert_group_target (
CREATE TABLE alert_grouping (
definition_id BIGINT NOT NULL,
group_id BIGINT NOT NULL,
- PRIMARY KEY (group_id, definition_id),
+ CONSTRAINT PK_alert_grouping PRIMARY KEY (group_id, definition_id),
FOREIGN KEY (definition_id) REFERENCES alert_definition(definition_id),
FOREIGN KEY (group_id) REFERENCES alert_group(group_id)
);
@@ -987,7 +967,7 @@ CREATE TABLE alert_notice (
history_id BIGINT NOT NULL,
notify_state VARCHAR(255) NOT NULL,
uuid VARCHAR(64) NOT NULL UNIQUE,
- PRIMARY KEY (notification_id),
+ CONSTRAINT PK_alert_notice PRIMARY KEY (notification_id),
FOREIGN KEY (target_id) REFERENCES alert_target(target_id),
FOREIGN KEY (history_id) REFERENCES alert_history(alert_id)
);
@@ -1512,7 +1492,7 @@ CREATE TABLE workflow (
numJobsTotal INTEGER, numJobsCompleted INTEGER,
inputBytes BIGINT, outputBytes BIGINT,
duration BIGINT,
- PRIMARY KEY (workflowId),
+ CONSTRAINT PK_workflow PRIMARY KEY (workflowId),
FOREIGN KEY (parentWorkflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
@@ -1526,7 +1506,7 @@ CREATE TABLE job (
mapsRuntime BIGINT, reducesRuntime BIGINT,
mapCounters TEXT, reduceCounters TEXT, jobCounters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
- PRIMARY KEY(jobId),
+ CONSTRAINT PK_job PRIMARY KEY (jobId),
FOREIGN KEY(workflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
@@ -1534,7 +1514,7 @@ CREATE TABLE task (
taskId VARCHAR(255), jobId VARCHAR(255), taskType TEXT, splits TEXT,
startTime BIGINT, finishTime BIGINT, status TEXT, error TEXT, counters TEXT,
failedAttempt TEXT,
- PRIMARY KEY(taskId),
+ CONSTRAINT PK_task PRIMARY KEY (taskId),
FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE
);
@@ -1545,7 +1525,7 @@ CREATE TABLE taskAttempt (
locality TEXT, avataar TEXT,
status TEXT, error TEXT, counters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
- PRIMARY KEY(taskAttemptId),
+ CONSTRAINT PK_taskAttempt PRIMARY KEY (taskAttemptId),
FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE,
FOREIGN KEY(taskId) REFERENCES task(taskId) ON DELETE CASCADE
);