You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ambari.apache.org by ds...@apache.org on 2015/06/07 16:29:47 UTC
ambari git commit: AMBARI-11770 Check PostgreSQL database schema
consistency after upgrade (dsen)
Repository: ambari
Updated Branches:
refs/heads/branch-2.1 b398f7060 -> a6241536b
AMBARI-11770 Check PostgreSQL database schema consistency after upgrade (dsen)
Project: http://git-wip-us.apache.org/repos/asf/ambari/repo
Commit: http://git-wip-us.apache.org/repos/asf/ambari/commit/a6241536
Tree: http://git-wip-us.apache.org/repos/asf/ambari/tree/a6241536
Diff: http://git-wip-us.apache.org/repos/asf/ambari/diff/a6241536
Branch: refs/heads/branch-2.1
Commit: a6241536b3c4e8be57c8a8259a3f852a138013bd
Parents: b398f70
Author: Dmytro Sen <ds...@apache.org>
Authored: Sun Jun 7 17:29:24 2015 +0300
Committer: Dmytro Sen <ds...@apache.org>
Committed: Sun Jun 7 17:29:24 2015 +0300
----------------------------------------------------------------------
.../server/upgrade/UpgradeCatalog210.java | 95 +++++++++-----------
.../main/resources/Ambari-DDL-MySQL-CREATE.sql | 54 ++++++-----
.../main/resources/Ambari-DDL-Oracle-CREATE.sql | 53 ++++++-----
.../resources/Ambari-DDL-Postgres-CREATE.sql | 55 +++++++-----
.../Ambari-DDL-Postgres-EMBEDDED-CREATE.sql | 53 ++++++-----
.../resources/Ambari-DDL-SQLServer-CREATE.sql | 46 +++++-----
6 files changed, 193 insertions(+), 163 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java b/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java
index 9e1404c..cd842fe 100644
--- a/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java
+++ b/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java
@@ -185,65 +185,65 @@ public class UpgradeCatalog210 extends AbstractUpgradeCatalog {
columns.add(new DBColumnInfo("action", String.class, 255, null, false));
columns.add(new DBColumnInfo("cluster_name", String.class, 100, null, false));
columns.add(new DBColumnInfo("bp_name", String.class, 100, null, false));
- columns.add(new DBColumnInfo("cluster_properties", char[].class, null, null, false));
- columns.add(new DBColumnInfo("cluster_attributes", char[].class, null, null, false));
- columns.add(new DBColumnInfo("description", String.class, 1024, null, false));
+ columns.add(new DBColumnInfo("cluster_properties", char[].class, null, null, true));
+ columns.add(new DBColumnInfo("cluster_attributes", char[].class, null, null, true));
+ columns.add(new DBColumnInfo("description", String.class, 1024, null, true));
dbAccessor.createTable(TOPOLOGY_REQUEST_TABLE, columns, "id");
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
columns.add(new DBColumnInfo("name", String.class, 255, null, false));
- columns.add(new DBColumnInfo("group_properties", char[].class, null, null, false));
- columns.add(new DBColumnInfo("group_attributes", char[].class, null, null, false));
+ columns.add(new DBColumnInfo("group_properties", char[].class, null, null, true));
+ columns.add(new DBColumnInfo("group_attributes", char[].class, null, null, true));
columns.add(new DBColumnInfo("request_id", Long.class, null, null, false));
dbAccessor.createTable(TOPOLOGY_HOST_GROUP_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_GROUP_TABLE, "FK_hostgroup_req_id", "request_id", TOPOLOGY_REQUEST_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_HOST_GROUP_TABLE, "FK_hostgroup_req_id", "request_id", TOPOLOGY_REQUEST_TABLE, "id", false, false);
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
- columns.add(new DBColumnInfo("request_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("group_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("fqdn", String.class, 255, null, true));
columns.add(new DBColumnInfo("host_count", Integer.class, null, null, true));
columns.add(new DBColumnInfo("predicate", String.class, 2048, null, true));
dbAccessor.createTable(TOPOLOGY_HOST_INFO_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_INFO_TABLE, "FK_hostinfo_group_id", "group_id", TOPOLOGY_HOST_GROUP_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_HOST_INFO_TABLE, "FK_hostinfo_group_id", "group_id", TOPOLOGY_HOST_GROUP_TABLE, "id", false, false);
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
- columns.add(new DBColumnInfo("description", String.class, 1024, null, false));
+ columns.add(new DBColumnInfo("request_id", Long.class, null, null, false));
+ columns.add(new DBColumnInfo("description", String.class, 1024, null, true));
dbAccessor.createTable(TOPOLOGY_LOGICAL_REQUEST_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_GROUP_TABLE, "FK_logicalreq_req_id", "request_id", TOPOLOGY_REQUEST_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_LOGICAL_REQUEST_TABLE, "FK_logicalreq_req_id", "request_id", TOPOLOGY_REQUEST_TABLE, "id", false, false);
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
columns.add(new DBColumnInfo("logical_request_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("group_id", Long.class, null, null, false));
- columns.add(new DBColumnInfo("stage_id", Integer.class, null, null, false));
+ columns.add(new DBColumnInfo("stage_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("host_name", String.class, 255, null, true));
dbAccessor.createTable(TOPOLOGY_HOST_REQUEST_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_REQUEST_TABLE, "FK_hostreq_logicalreq_id", "logical_request_id", TOPOLOGY_LOGICAL_REQUEST_TABLE, "id", true, false);
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_REQUEST_TABLE, "FK_hostreq_group_id", "group_id", TOPOLOGY_HOST_GROUP_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_HOST_REQUEST_TABLE, "FK_hostreq_logicalreq_id", "logical_request_id", TOPOLOGY_LOGICAL_REQUEST_TABLE, "id", false, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_HOST_REQUEST_TABLE, "FK_hostreq_group_id", "group_id", TOPOLOGY_HOST_GROUP_TABLE, "id", false, false);
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
columns.add(new DBColumnInfo("host_request_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("type", String.class, 255, null, false));
dbAccessor.createTable(TOPOLOGY_HOST_TASK_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_HOST_TASK_TABLE, "FK_hosttask_req_id", "host_request_id", TOPOLOGY_HOST_REQUEST_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_HOST_TASK_TABLE, "FK_hosttask_req_id", "host_request_id", TOPOLOGY_HOST_REQUEST_TABLE, "id", false, false);
columns.clear();
columns.add(new DBColumnInfo("id", Long.class, null, null, false));
columns.add(new DBColumnInfo("host_task_id", Long.class, null, null, false));
- columns.add(new DBColumnInfo("physical_task_id", Long.class, null, null, false));
+ columns.add(new DBColumnInfo("physical_task_id", Long.class, null, null, true));
columns.add(new DBColumnInfo("component", String.class, 255, null, false));
dbAccessor.createTable(TOPOLOGY_LOGICAL_TASK_TABLE, columns, "id");
- dbAccessor.addFKConstraint(TOPOLOGY_LOGICAL_TASK_TABLE, "FK_ltask_hosttask_id", "host_task_id", TOPOLOGY_HOST_TASK_TABLE, "id", true, false);
+ dbAccessor.addFKConstraint(TOPOLOGY_LOGICAL_TASK_TABLE, "FK_ltask_hosttask_id", "host_task_id", TOPOLOGY_HOST_TASK_TABLE, "id", false, false);
dbAccessor.addFKConstraint(TOPOLOGY_LOGICAL_TASK_TABLE, "FK_ltask_hrc_id", "physical_task_id", "host_role_command", "task_id", false, false);
// Sequence updates
@@ -436,26 +436,17 @@ public class UpgradeCatalog210 extends AbstractUpgradeCatalog {
}
// These are the FKs that have already been corrected.
- dbAccessor.addFKConstraint(CONFIG_GROUP_HOST_MAPPING_TABLE, "FK_cghm_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(CLUSTER_HOST_MAPPING_TABLE, "FK_clusterhostmapping_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(HOST_CONFIG_MAPPING_TABLE, "FK_hostconfmapping_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(HOST_COMPONENT_STATE_TABLE, "FK_hostcomponentstate_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(HOST_COMPONENT_DESIRED_STATE_TABLE, "FK_hcdesiredstate_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(HOST_ROLE_COMMAND_TABLE, "FK_host_role_command_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(HOST_STATE_TABLE, "FK_hoststate_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
- dbAccessor.addFKConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_principalname",
- "principal_name", KERBEROS_PRINCIPAL_TABLE, "principal_name", false);
- dbAccessor.addFKConstraint(SERVICE_CONFIG_HOSTS_TABLE, "FK_scvhosts_host_id",
- "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(CONFIG_GROUP_HOST_MAPPING_TABLE, "FK_cghm_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(CLUSTER_HOST_MAPPING_TABLE, "FK_clusterhostmapping_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_CONFIG_MAPPING_TABLE, "FK_hostconfmapping_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_COMPONENT_STATE_TABLE, "FK_hostcomponentstate_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_COMPONENT_DESIRED_STATE_TABLE, "FK_hcdesiredstate_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_ROLE_COMMAND_TABLE, "FK_host_role_command_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_STATE_TABLE, "FK_hoststate_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(HOST_VERSION_TABLE, "FK_host_version_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_id", "host_id", HOSTS_TABLE, "host_id", false);
+ dbAccessor.addFKConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_principalname", "principal_name", KERBEROS_PRINCIPAL_TABLE, "principal_name", false);
+ dbAccessor.addFKConstraint(SERVICE_CONFIG_HOSTS_TABLE, "FK_scvhosts_host_id", "host_id", HOSTS_TABLE, "host_id", false);
// For any tables where the host_name was part of the PK, need to drop the PK, and recreate it with the host_id
@@ -562,16 +553,16 @@ public class UpgradeCatalog210 extends AbstractUpgradeCatalog {
columns = new ArrayList<DBColumnInfo>();
columns.add(new DBColumnInfo("widget_layout_id", Long.class, null, null, false));
columns.add(new DBColumnInfo("widget_id", Long.class, null, null, false));
- columns.add(new DBColumnInfo("widget_order", Integer.class, null, null, false));
+ columns.add(new DBColumnInfo("widget_order", Short.class, null, null, true));
dbAccessor.createTable(WIDGET_LAYOUT_USER_WIDGET_TABLE, columns, "widget_layout_id", "widget_id");
- dbAccessor.addFKConstraint(WIDGET_LAYOUT_USER_WIDGET_TABLE, "FK_widget_layout_id", "widget_layout_id", "widget_layout", "id", true, false);
- dbAccessor.addFKConstraint(WIDGET_LAYOUT_USER_WIDGET_TABLE, "FK_widget_id", "widget_id", "widget", "id", true, false);
+ dbAccessor.addFKConstraint(WIDGET_LAYOUT_USER_WIDGET_TABLE, "FK_widget_layout_id", "widget_layout_id", "widget_layout", "id", false, false);
+ dbAccessor.addFKConstraint(WIDGET_LAYOUT_USER_WIDGET_TABLE, "FK_widget_id", "widget_id", "widget", "id", false, false);
//Alter users to store active widget layouts
dbAccessor.addColumn("users", new DBColumnInfo("active_widget_layouts", String.class, 1024, null, true));
// Sequence updates
- addSequences(Arrays.asList("widget_id_seq", "widget_layout_id_seq"), 0L, false);
+ addSequences(Arrays.asList("widget_id_seq", "widget_layout_id_seq"), 0L, false);
}
/**
@@ -1196,18 +1187,18 @@ public class UpgradeCatalog210 extends AbstractUpgradeCatalog {
STACK_ID_COLUMN.setNullable(false);
// make all stack columns NOT NULL now that they are filled in
- dbAccessor.alterColumn(CLUSTERS_TABLE, DESIRED_STACK_ID_COLUMN);
- dbAccessor.alterColumn("hostcomponentdesiredstate", DESIRED_STACK_ID_COLUMN);
- dbAccessor.alterColumn("servicecomponentdesiredstate", DESIRED_STACK_ID_COLUMN);
- dbAccessor.alterColumn("servicedesiredstate", DESIRED_STACK_ID_COLUMN);
-
- dbAccessor.alterColumn("clusterstate", CURRENT_STACK_ID_COLUMN);
- dbAccessor.alterColumn("hostcomponentstate", CURRENT_STACK_ID_COLUMN);
-
- dbAccessor.alterColumn("clusterconfig", STACK_ID_COLUMN);
- dbAccessor.alterColumn("serviceconfig", STACK_ID_COLUMN);
- dbAccessor.alterColumn("blueprint", STACK_ID_COLUMN);
- dbAccessor.alterColumn(REPO_VERSION_TABLE, STACK_ID_COLUMN);
+ dbAccessor.setColumnNullable(CLUSTERS_TABLE, DESIRED_STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("hostcomponentdesiredstate", DESIRED_STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("servicecomponentdesiredstate", DESIRED_STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("servicedesiredstate", DESIRED_STACK_ID_COLUMN_NAME, false);
+
+ dbAccessor.setColumnNullable("clusterstate", CURRENT_STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("hostcomponentstate", CURRENT_STACK_ID_COLUMN_NAME, false);
+
+ dbAccessor.setColumnNullable("clusterconfig", STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("serviceconfig", STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable("blueprint", STACK_ID_COLUMN_NAME, false);
+ dbAccessor.setColumnNullable(REPO_VERSION_TABLE, STACK_ID_COLUMN_NAME, false);
// drop unused JSON columns
dbAccessor.dropColumn(CLUSTERS_TABLE, DESIRED_STACK_VERSION_COLUMN_NAME);
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/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 7827df4..fb69bb3 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,7 @@ CREATE TABLE stack(
stack_id BIGINT NOT NULL,
stack_name VARCHAR(255) NOT NULL,
stack_version VARCHAR(255) NOT NULL,
- PRIMARY KEY (stack_id),
- CONSTRAINT unq_stack UNIQUE(stack_name,stack_version)
+ PRIMARY KEY (stack_id)
);
CREATE TABLE clusters (
@@ -44,8 +43,8 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE clusterconfig (
config_id BIGINT NOT NULL,
@@ -57,8 +56,8 @@ CREATE TABLE clusterconfig (
config_data LONGTEXT NOT NULL,
config_attributes LONGTEXT,
create_timestamp BIGINT NOT NULL,
- PRIMARY KEY (config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (config_id)
+);
CREATE TABLE serviceconfig (
service_config_id BIGINT NOT NULL,
@@ -70,8 +69,8 @@ CREATE TABLE serviceconfig (
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
group_id BIGINT,
note LONGTEXT,
- PRIMARY KEY (service_config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (service_config_id)
+);
CREATE TABLE serviceconfighosts (
service_config_id BIGINT NOT NULL,
@@ -93,8 +92,8 @@ 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),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE cluster_version (
id BIGINT NOT NULL,
@@ -117,8 +116,8 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hostcomponentstate (
cluster_id BIGINT NOT NULL,
@@ -130,8 +129,8 @@ 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 (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hosts (
host_id BIGINT NOT NULL,
@@ -175,8 +174,8 @@ CREATE TABLE servicecomponentdesiredstate (
desired_stack_id BIGINT NOT NULL,
desired_state VARCHAR(255) NOT NULL,
service_name VARCHAR(255) NOT NULL,
- PRIMARY KEY (component_name, cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (component_name, cluster_id, service_name)
+);
CREATE TABLE servicedesiredstate (
cluster_id BIGINT NOT NULL,
@@ -186,8 +185,8 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, service_name)
+);
CREATE TABLE users (
user_id INTEGER,
@@ -394,8 +393,8 @@ CREATE TABLE requestschedulebatchrequest (
CREATE TABLE blueprint (
blueprint_name VARCHAR(255) NOT NULL,
stack_id BIGINT NOT NULL,
- PRIMARY KEY(blueprint_name),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY(blueprint_name)
+);
CREATE TABLE hostgroup (
blueprint_name VARCHAR(255) NOT NULL,
@@ -538,8 +537,8 @@ CREATE TABLE repo_version (
display_name VARCHAR(128) NOT NULL,
upgrade_package VARCHAR(255) NOT NULL,
repositories LONGTEXT NOT NULL,
- PRIMARY KEY(repo_version_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY(repo_version_id)
+);
CREATE TABLE widget (
id BIGINT NOT NULL,
@@ -654,6 +653,7 @@ ALTER TABLE serviceconfig ADD CONSTRAINT UQ_scv_service_version UNIQUE (cluster_
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_version 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.
@@ -727,6 +727,16 @@ ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_group_id FOREIGN KEY
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 (
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
index 93d6f6e..683506e 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
@@ -21,8 +21,7 @@ CREATE TABLE stack(
stack_id NUMBER(19) NOT NULL,
stack_name VARCHAR2(255) NOT NULL,
stack_version VARCHAR2(255) NOT NULL,
- PRIMARY KEY (stack_id),
- CONSTRAINT unq_stack UNIQUE(stack_name,stack_version)
+ PRIMARY KEY (stack_id)
);
CREATE TABLE clusters (
@@ -34,8 +33,8 @@ CREATE TABLE clusters (
security_type VARCHAR2(32) DEFAULT 'NONE' NOT NULL,
desired_cluster_state VARCHAR2(255) NULL,
desired_stack_id NUMBER(19) NOT NULL,
- PRIMARY KEY (cluster_id),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE clusterconfig (
config_id NUMBER(19) NOT NULL,
@@ -47,8 +46,8 @@ CREATE TABLE clusterconfig (
config_data CLOB NOT NULL,
config_attributes CLOB,
create_timestamp NUMBER(19) NOT NULL,
- PRIMARY KEY (config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (config_id)
+);
CREATE TABLE serviceconfig (
service_config_id NUMBER(19) NOT NULL,
@@ -60,8 +59,8 @@ CREATE TABLE serviceconfig (
user_name VARCHAR(255) DEFAULT '_db' NOT NULL,
group_id NUMBER(19),
note CLOB,
- PRIMARY KEY (service_config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (service_config_id)
+);
CREATE TABLE serviceconfighosts (
service_config_id NUMBER(19) NOT NULL,
@@ -83,8 +82,8 @@ CREATE TABLE clusterstate (
cluster_id NUMBER(19) NOT NULL,
current_cluster_state VARCHAR2(255) NULL,
current_stack_id NUMBER(19) NULL,
- PRIMARY KEY (cluster_id),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE cluster_version (
id NUMBER(19) NULL,
@@ -107,8 +106,8 @@ CREATE TABLE hostcomponentdesiredstate (
maintenance_state VARCHAR2(32) NOT NULL,
security_state VARCHAR2(32) DEFAULT 'UNSECURED' NOT NULL,
restart_required NUMBER(1) DEFAULT 0 NOT NULL,
- PRIMARY KEY (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hostcomponentstate (
cluster_id NUMBER(19) NOT NULL,
@@ -120,8 +119,8 @@ CREATE TABLE hostcomponentstate (
service_name VARCHAR2(255) NOT NULL,
upgrade_state VARCHAR2(32) DEFAULT 'NONE' NOT NULL,
security_state VARCHAR2(32) DEFAULT 'UNSECURED' NOT NULL,
- PRIMARY KEY (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hosts (
host_id NUMBER(19) NOT NULL,
@@ -165,8 +164,8 @@ CREATE TABLE servicecomponentdesiredstate (
desired_stack_id NUMBER(19) NOT NULL,
desired_state VARCHAR2(255) NOT NULL,
service_name VARCHAR2(255) NOT NULL,
- PRIMARY KEY (component_name, cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (component_name, cluster_id, service_name)
+);
CREATE TABLE servicedesiredstate (
cluster_id NUMBER(19) NOT NULL,
@@ -176,8 +175,8 @@ CREATE TABLE servicedesiredstate (
service_name VARCHAR2(255) NOT NULL,
maintenance_state VARCHAR2(32) NOT NULL,
security_state VARCHAR2(32) DEFAULT 'UNSECURED' NOT NULL,
- PRIMARY KEY (cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, service_name)
+);
CREATE TABLE users (
user_id NUMBER(10) NOT NULL,
@@ -385,8 +384,8 @@ CREATE TABLE requestschedulebatchrequest (
CREATE TABLE blueprint (
blueprint_name VARCHAR2(255) NOT NULL,
stack_id NUMBER(19) NOT NULL,
- PRIMARY KEY(blueprint_name),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY(blueprint_name)
+);
CREATE TABLE hostgroup (
blueprint_name VARCHAR2(255) NOT NULL,
@@ -527,8 +526,7 @@ CREATE TABLE repo_version (
display_name VARCHAR2(128) NOT NULL,
upgrade_package VARCHAR2(255) NOT NULL,
repositories CLOB NOT NULL,
- PRIMARY KEY(repo_version_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY(repo_version_id)
);
CREATE TABLE widget (
@@ -644,6 +642,7 @@ ALTER TABLE serviceconfig ADD CONSTRAINT UQ_scv_service_version UNIQUE (cluster_
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_version 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.
@@ -717,6 +716,16 @@ ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_group_id FOREIGN KEY
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 (
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
index 9ab65c3..6febff2 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
@@ -25,8 +25,7 @@ CREATE TABLE stack(
stack_id BIGINT NOT NULL,
stack_name VARCHAR(255) NOT NULL,
stack_version VARCHAR(255) NOT NULL,
- PRIMARY KEY (stack_id),
- CONSTRAINT unq_stack UNIQUE(stack_name,stack_version)
+ PRIMARY KEY (stack_id)
);
CREATE TABLE clusters (
@@ -38,8 +37,8 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE clusterconfig (
config_id BIGINT NOT NULL,
@@ -51,8 +50,8 @@ CREATE TABLE clusterconfig (
config_data TEXT NOT NULL,
config_attributes TEXT,
create_timestamp BIGINT NOT NULL,
- PRIMARY KEY (config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (config_id)
+);
CREATE TABLE clusterconfigmapping (
cluster_id BIGINT NOT NULL,
@@ -73,8 +72,8 @@ CREATE TABLE serviceconfig (
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
group_id BIGINT,
note TEXT,
- PRIMARY KEY (service_config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (service_config_id)
+);
CREATE TABLE serviceconfighosts (
service_config_id BIGINT NOT NULL,
@@ -96,8 +95,8 @@ 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),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
CREATE TABLE cluster_version (
id BIGINT NOT NULL,
@@ -120,8 +119,8 @@ CREATE TABLE hostcomponentdesiredstate (
maintenance_state VARCHAR(32) NOT NULL,
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
restart_required SMALLINT NOT NULL DEFAULT 0,
- PRIMARY KEY (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hostcomponentstate (
cluster_id BIGINT NOT NULL,
@@ -133,8 +132,8 @@ 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 (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hosts (
host_id BIGINT NOT NULL,
@@ -178,8 +177,8 @@ CREATE TABLE servicecomponentdesiredstate (
desired_stack_id BIGINT NOT NULL,
desired_state VARCHAR(255) NOT NULL,
service_name VARCHAR(255) NOT NULL,
- PRIMARY KEY (component_name, cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (component_name, cluster_id, service_name)
+);
CREATE TABLE servicedesiredstate (
cluster_id BIGINT NOT NULL,
@@ -189,8 +188,8 @@ 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 (cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY (cluster_id, service_name)
+);
CREATE TABLE users (
user_id INTEGER,
@@ -391,8 +390,8 @@ CREATE TABLE requestschedulebatchrequest (
CREATE TABLE blueprint (
blueprint_name VARCHAR(255) NOT NULL,
stack_id BIGINT NOT NULL,
- PRIMARY KEY(blueprint_name),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY(blueprint_name)
+);
CREATE TABLE hostgroup (
blueprint_name VARCHAR(255) NOT NULL,
@@ -536,8 +535,7 @@ CREATE TABLE repo_version (
display_name VARCHAR(128) NOT NULL,
upgrade_package VARCHAR(255) NOT NULL,
repositories TEXT NOT NULL,
- PRIMARY KEY(repo_version_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY(repo_version_id)
);
CREATE TABLE widget (
@@ -649,7 +647,8 @@ ALTER TABLE viewinstance ADD CONSTRAINT UQ_viewinstance_name_id UNIQUE (view_ins
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_version UNIQUE (stack_id, version);
+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.
@@ -723,6 +722,16 @@ ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_group_id FOREIGN KEY
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 (
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
index cbe47f7..8682e0e 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
@@ -36,8 +36,7 @@ CREATE TABLE ambari.stack(
stack_id BIGINT NOT NULL,
stack_name VARCHAR(255) NOT NULL,
stack_version VARCHAR(255) NOT NULL,
- PRIMARY KEY (stack_id),
- CONSTRAINT unq_stack UNIQUE(stack_name,stack_version)
+ PRIMARY KEY (stack_id)
);
GRANT ALL PRIVILEGES ON TABLE ambari.stack TO :username;
@@ -50,8 +49,8 @@ CREATE TABLE ambari.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),
- FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.clusters TO :username;
CREATE TABLE ambari.clusterconfig (
@@ -64,8 +63,8 @@ CREATE TABLE ambari.clusterconfig (
config_data TEXT NOT NULL,
config_attributes TEXT,
create_timestamp BIGINT NOT NULL,
- PRIMARY KEY (config_id),
- FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (config_id)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.clusterconfig TO :username;
CREATE TABLE ambari.clusterconfigmapping (
@@ -88,8 +87,8 @@ CREATE TABLE ambari.serviceconfig (
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
group_id BIGINT,
note TEXT,
- PRIMARY KEY (service_config_id),
- FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (service_config_id)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.serviceconfig TO :username;
CREATE TABLE ambari.serviceconfighosts (
@@ -115,8 +114,8 @@ CREATE TABLE ambari.clusterstate (
cluster_id BIGINT NOT NULL,
current_cluster_state VARCHAR(255) NOT NULL,
current_stack_id BIGINT NOT NULL,
- PRIMARY KEY (cluster_id),
- FOREIGN KEY (current_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (cluster_id)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.clusterstate TO :username;
CREATE TABLE ambari.cluster_version (
@@ -141,8 +140,8 @@ CREATE TABLE ambari.hostcomponentdesiredstate (
maintenance_state VARCHAR(32) NOT NULL,
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
restart_required SMALLINT NOT NULL DEFAULT 0,
- PRIMARY KEY (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.hostcomponentdesiredstate TO :username;
CREATE TABLE ambari.hostcomponentstate (
@@ -155,8 +154,8 @@ CREATE TABLE ambari.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 (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (current_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (cluster_id, component_name, host_id, service_name)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.hostcomponentstate TO :username;
CREATE TABLE ambari.hosts (
@@ -204,8 +203,8 @@ CREATE TABLE ambari.servicecomponentdesiredstate (
desired_stack_id BIGINT NOT NULL,
desired_state VARCHAR(255) NOT NULL,
service_name VARCHAR(255) NOT NULL,
- PRIMARY KEY (component_name, cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (component_name, cluster_id, service_name)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.servicecomponentdesiredstate TO :username;
CREATE TABLE ambari.servicedesiredstate (
@@ -216,8 +215,8 @@ CREATE TABLE ambari.servicedesiredstate (
service_name VARCHAR(255) NOT NULL,
maintenance_state VARCHAR(32) NOT NULL,
security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED',
- PRIMARY KEY (cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY (cluster_id, service_name)
+);
GRANT ALL PRIVILEGES ON TABLE ambari.servicedesiredstate TO :username;
CREATE TABLE ambari.users (
@@ -439,8 +438,8 @@ GRANT ALL PRIVILEGES ON TABLE ambari.requestschedulebatchrequest TO :username;
CREATE TABLE ambari.blueprint (
blueprint_name VARCHAR(255) NOT NULL,
stack_id BIGINT NOT NULL,
- PRIMARY KEY(blueprint_name),
- FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id));
+ PRIMARY KEY(blueprint_name)
+);
CREATE TABLE ambari.hostgroup (
blueprint_name VARCHAR(255) NOT NULL,
@@ -604,8 +603,7 @@ CREATE TABLE ambari.repo_version (
display_name VARCHAR(128) NOT NULL,
upgrade_package VARCHAR(255) NOT NULL,
repositories TEXT NOT NULL,
- PRIMARY KEY(repo_version_id),
- FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id)
+ PRIMARY KEY(repo_version_id)
);
GRANT ALL PRIVILEGES ON TABLE ambari.repo_version TO :username;
@@ -730,6 +728,7 @@ ALTER TABLE ambari.serviceconfig ADD CONSTRAINT UQ_scv_service_version UNIQUE (c
ALTER TABLE ambari.adminpermission ADD CONSTRAINT UQ_perm_name_resource_type_id UNIQUE (permission_name, resource_type_id);
ALTER TABLE ambari.repo_version ADD CONSTRAINT UQ_repo_version_display_name UNIQUE (display_name);
ALTER TABLE ambari.repo_version ADD CONSTRAINT UQ_repo_version_stack_version UNIQUE (stack_id, version);
+ALTER TABLE ambari.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.
@@ -803,6 +802,16 @@ ALTER TABLE ambari.topology_host_request ADD CONSTRAINT FK_hostreq_group_id FORE
ALTER TABLE ambari.topology_host_task ADD CONSTRAINT FK_hosttask_req_id FOREIGN KEY (host_request_id) REFERENCES ambari.topology_host_request (id);
ALTER TABLE ambari.topology_logical_task ADD CONSTRAINT FK_ltask_hosttask_id FOREIGN KEY (host_task_id) REFERENCES ambari.topology_host_task (id);
ALTER TABLE ambari.topology_logical_task ADD CONSTRAINT FK_ltask_hrc_id FOREIGN KEY (physical_task_id) REFERENCES ambari.host_role_command (task_id);
+ALTER TABLE ambari.clusters ADD CONSTRAINT FK_clusters_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.clusterconfig ADD CONSTRAINT FK_clusterconfig_stack_id FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.serviceconfig ADD CONSTRAINT FK_serviceconfig_stack_id FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.clusterstate ADD CONSTRAINT FK_cs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.hostcomponentdesiredstate ADD CONSTRAINT FK_hcds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.hostcomponentstate ADD CONSTRAINT FK_hcs_current_stack_id FOREIGN KEY (current_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.servicecomponentdesiredstate ADD CONSTRAINT FK_scds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.servicedesiredstate ADD CONSTRAINT FK_sds_desired_stack_id FOREIGN KEY (desired_stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.blueprint ADD CONSTRAINT FK_blueprint_stack_id FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id);
+ALTER TABLE ambari.repo_version ADD CONSTRAINT FK_repoversion_stack_id FOREIGN KEY (stack_id) REFERENCES ambari.stack(stack_id);
-- Kerberos
CREATE TABLE ambari.kerberos_principal (
http://git-wip-us.apache.org/repos/asf/ambari/blob/a6241536/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 20c75b3..cb18fc4 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql
@@ -35,8 +35,7 @@ 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 unq_stack UNIQUE(stack_name,stack_version)
+ PRIMARY KEY CLUSTERED (stack_id)
);
CREATE TABLE clusters (
@@ -48,8 +47,7 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (cluster_id)
);
CREATE TABLE clusterconfig (
@@ -62,8 +60,7 @@ CREATE TABLE clusterconfig (
config_data VARCHAR(MAX) NOT NULL,
config_attributes VARCHAR(MAX),
create_timestamp BIGINT NOT NULL,
- PRIMARY KEY CLUSTERED (config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (config_id)
);
CREATE TABLE serviceconfig (
@@ -76,8 +73,7 @@ CREATE TABLE serviceconfig (
user_name VARCHAR(255) NOT NULL DEFAULT '_db',
group_id BIGINT,
note VARCHAR(MAX),
- PRIMARY KEY CLUSTERED (service_config_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (service_config_id)
);
CREATE TABLE serviceconfighosts (
@@ -113,8 +109,7 @@ 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),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (cluster_id)
);
CREATE TABLE cluster_version (
@@ -139,8 +134,8 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY CLUSTERED (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hostcomponentstate (
cluster_id BIGINT NOT NULL,
@@ -152,8 +147,8 @@ 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 (cluster_id, component_name, host_id, service_name),
- FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id));
+ PRIMARY KEY CLUSTERED (cluster_id, component_name, host_id, service_name)
+);
CREATE TABLE hosts (
host_id BIGINT NOT NULL,
@@ -190,8 +185,7 @@ CREATE TABLE servicecomponentdesiredstate (
desired_stack_id BIGINT NOT NULL,
desired_state VARCHAR(255) NOT NULL,
service_name VARCHAR(255) NOT NULL,
- PRIMARY KEY CLUSTERED (component_name, cluster_id, service_name),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (component_name, cluster_id, service_name)
);
CREATE TABLE servicedesiredstate (
@@ -202,8 +196,7 @@ 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),
- FOREIGN KEY (desired_stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (cluster_id,service_name)
);
CREATE TABLE users (
@@ -447,8 +440,7 @@ CREATE TABLE requestschedulebatchrequest (
CREATE TABLE blueprint (
blueprint_name VARCHAR(255) NOT NULL,
stack_id BIGINT NOT NULL,
- PRIMARY KEY CLUSTERED (blueprint_name),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (blueprint_name)
);
CREATE TABLE hostgroup (
@@ -645,8 +637,7 @@ CREATE TABLE repo_version (
display_name VARCHAR(128) NOT NULL,
upgrade_package VARCHAR(255) NOT NULL,
repositories VARCHAR(MAX) NOT NULL,
- PRIMARY KEY CLUSTERED (repo_version_id),
- FOREIGN KEY (stack_id) REFERENCES stack(stack_id)
+ PRIMARY KEY CLUSTERED (repo_version_id)
);
CREATE TABLE artifact (
@@ -768,6 +759,7 @@ ALTER TABLE serviceconfig ADD CONSTRAINT UQ_scv_service_version UNIQUE (cluster_
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_version 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.
@@ -841,6 +833,16 @@ ALTER TABLE topology_host_request ADD CONSTRAINT FK_hostreq_group_id FOREIGN KEY
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 (