Return-Path: X-Original-To: apmail-ambari-commits-archive@www.apache.org Delivered-To: apmail-ambari-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 1DCB31774E for ; Wed, 25 Mar 2015 07:28:28 +0000 (UTC) Received: (qmail 51359 invoked by uid 500); 25 Mar 2015 07:28:21 -0000 Delivered-To: apmail-ambari-commits-archive@ambari.apache.org Received: (qmail 51269 invoked by uid 500); 25 Mar 2015 07:28:21 -0000 Mailing-List: contact commits-help@ambari.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ambari-dev@ambari.apache.org Delivered-To: mailing list commits@ambari.apache.org Received: (qmail 51259 invoked by uid 99); 25 Mar 2015 07:28:21 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 25 Mar 2015 07:28:21 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 8188DDFBC5; Wed, 25 Mar 2015 07:28:21 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: alejandro@apache.org To: commits@ambari.apache.org Date: Wed, 25 Mar 2015 07:28:22 -0000 Message-Id: In-Reply-To: <7c6d200a066e4628b1b7caacb94e4e8c@git.apache.org> References: <7c6d200a066e4628b1b7caacb94e4e8c@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [2/2] ambari git commit: AMBARI-10190. Full Delete of Host : Add host_id column to clusterhostmapping, and create initial UpgradeCatalog210 (alejandro) AMBARI-10190. Full Delete of Host : Add host_id column to clusterhostmapping, and create initial UpgradeCatalog210 (alejandro) Project: http://git-wip-us.apache.org/repos/asf/ambari/repo Commit: http://git-wip-us.apache.org/repos/asf/ambari/commit/f73936a2 Tree: http://git-wip-us.apache.org/repos/asf/ambari/tree/f73936a2 Diff: http://git-wip-us.apache.org/repos/asf/ambari/diff/f73936a2 Branch: refs/heads/trunk Commit: f73936a281896b82e89a64399f712d0e17142637 Parents: d83c14c Author: Alejandro Fernandez Authored: Thu Mar 19 10:36:36 2015 -0700 Committer: Alejandro Fernandez Committed: Wed Mar 25 00:26:07 2015 -0700 ---------------------------------------------------------------------- .../ambari/server/agent/HeartBeatHandler.java | 4 +- .../server/api/query/JpaPredicateVisitor.java | 20 +- .../listeners/alerts/AlertReceivedListener.java | 13 +- .../apache/ambari/server/orm/dao/AlertsDAO.java | 1 - .../apache/ambari/server/orm/dao/HostDAO.java | 19 +- .../ambari/server/orm/entities/HostEntity.java | 34 ++- .../org/apache/ambari/server/state/Alert.java | 16 +- .../server/upgrade/SchemaUpgradeHelper.java | 1 + .../server/upgrade/UpgradeCatalog210.java | 293 ++++++++++++++++++ .../main/resources/Ambari-DDL-MySQL-CREATE.sql | 44 ++- .../main/resources/Ambari-DDL-Oracle-CREATE.sql | 38 ++- .../resources/Ambari-DDL-Postgres-CREATE.sql | 294 +++++++++++-------- .../Ambari-DDL-Postgres-EMBEDDED-CREATE.sql | 55 +++- .../resources/Ambari-DDL-SQLServer-CREATE.sql | 52 +++- .../actionmanager/TestActionDBAccessorImpl.java | 2 +- .../state/alerts/AlertReceivedListenerTest.java | 2 +- .../server/upgrade/UpgradeCatalog210Test.java | 148 ++++++++++ 17 files changed, 848 insertions(+), 188 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/agent/HeartBeatHandler.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/agent/HeartBeatHandler.java b/ambari-server/src/main/java/org/apache/ambari/server/agent/HeartBeatHandler.java index 8833148..9f39049 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/agent/HeartBeatHandler.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/agent/HeartBeatHandler.java @@ -293,8 +293,8 @@ public class HeartBeatHandler { if (null != heartbeat.getAlerts()) { for (Alert alert : heartbeat.getAlerts()) { - if (null == alert.getHost()) { - alert.setHost(hostname); + if (null == alert.getHostName()) { + alert.setHostName(hostname); } try { http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/api/query/JpaPredicateVisitor.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/api/query/JpaPredicateVisitor.java b/ambari-server/src/main/java/org/apache/ambari/server/api/query/JpaPredicateVisitor.java index afbb3e2..75ca2d2 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/api/query/JpaPredicateVisitor.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/api/query/JpaPredicateVisitor.java @@ -158,10 +158,12 @@ public abstract class JpaPredicateVisitor implements PredicateVisitor { for (SingularAttribute singularAttribute : singularAttributes) { lastSingularAttribute = singularAttribute; - if (null == path) { - path = m_root.get(singularAttribute.getName()); - } else { - path = path.get(singularAttribute.getName()); + if (singularAttribute != null) { + if (null == path) { + path = m_root.get(singularAttribute.getName()); + } else { + path = path.get(singularAttribute.getName()); + } } } @@ -173,10 +175,12 @@ public abstract class JpaPredicateVisitor implements PredicateVisitor { Comparable value = predicate.getValue(); // convert string to enum for proper JPA comparisons - Class clazz = lastSingularAttribute.getJavaType(); - if (clazz.isEnum()) { - Class enumClass = (Class) clazz; - value = Enum.valueOf(enumClass, value.toString()); + if (lastSingularAttribute != null) { + Class clazz = lastSingularAttribute.getJavaType(); + if (clazz.isEnum()) { + Class enumClass = (Class) clazz; + value = Enum.valueOf(enumClass, value.toString()); + } } javax.persistence.criteria.Predicate jpaPredicate = null; http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/events/listeners/alerts/AlertReceivedListener.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/events/listeners/alerts/AlertReceivedListener.java b/ambari-server/src/main/java/org/apache/ambari/server/events/listeners/alerts/AlertReceivedListener.java index 7248459..849c19a 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/events/listeners/alerts/AlertReceivedListener.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/events/listeners/alerts/AlertReceivedListener.java @@ -41,6 +41,7 @@ import org.apache.ambari.server.state.Host; import org.apache.ambari.server.state.MaintenanceState; import org.apache.ambari.server.state.Service; import org.apache.ambari.server.state.ServiceComponentHost; +import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -136,10 +137,10 @@ public class AlertReceivedListener { AlertCurrentEntity current = null; - if (null == alert.getHost() || definition.isHostIgnored()) { + if (StringUtils.isBlank(alert.getHostName()) || definition.isHostIgnored()) { current = m_alertsDao.findCurrentByNameNoHost(clusterId, alert.getName()); } else { - current = m_alertsDao.findCurrentByHostAndName(clusterId, alert.getHost(), + current = m_alertsDao.findCurrentByHostAndName(clusterId, alert.getHostName(), alert.getName()); } @@ -221,7 +222,7 @@ public class AlertReceivedListener { String clusterName = alert.getCluster(); String serviceName = alert.getService(); String componentName = alert.getComponent(); - String hostName = alert.getHost(); + String hostName = alert.getHostName(); // if the alert is not bound to a cluster, then it's most likely a // host alert and is always valid @@ -260,7 +261,7 @@ public class AlertReceivedListener { return false; } - if (null != hostName) { + if (StringUtils.isNotBlank(hostName)) { List hosts = m_clusters.get().getHosts(); if (null == hosts) { LOG.error("Unable to process alert {} for an invalid host named {}", @@ -287,7 +288,7 @@ public class AlertReceivedListener { // if the alert is for a host/component then verify that the component // is actually installed on that host - if (null != hostName && null != componentName) { + if (StringUtils.isNotBlank(hostName) && null != componentName) { boolean validServiceComponentHost = false; List serviceComponentHosts = cluster.getServiceComponentHosts(hostName); @@ -338,7 +339,7 @@ public class AlertReceivedListener { if (definition.isHostIgnored()) { history.setHostName(null); } else { - history.setHostName(alert.getHost()); + history.setHostName(alert.getHostName()); } return history; http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/AlertsDAO.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/AlertsDAO.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/AlertsDAO.java index 5435982..fd63166 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/AlertsDAO.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/AlertsDAO.java @@ -755,7 +755,6 @@ public class AlertsDAO { * Locate the current alert for the provided service and alert name, but when * host is not set ({@code IS NULL}). * @param clusterId the cluster id - * @param serviceName the service name * @param alertName the name of the alert * @return the current record, or {@code null} if not found */ http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostDAO.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostDAO.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostDAO.java index 35c795b..0fb9c59 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostDAO.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostDAO.java @@ -38,9 +38,26 @@ public class HostDAO { @Inject Provider entityManagerProvider; + /** + * Looks for Host by ID + * @param id ID of Host + * @return Found entity or NULL + */ + @RequiresSession + public HostEntity findById(long id) { + return entityManagerProvider.get().find(HostEntity.class, id); + } + @RequiresSession public HostEntity findByName(String hostName) { - return entityManagerProvider.get().find(HostEntity.class, hostName); + TypedQuery query = entityManagerProvider.get().createNamedQuery( + "HostEntity.findByHostName", HostEntity.class); + query.setParameter("hostName", hostName); + try { + return query.getSingleResult(); + } catch (NoResultException ignored) { + return null; + } } @RequiresSession http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostEntity.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostEntity.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostEntity.java index 3255e58..4df5f39 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostEntity.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostEntity.java @@ -23,13 +23,18 @@ import javax.persistence.CascadeType; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.FetchType; +import javax.persistence.GeneratedValue; +import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.JoinTable; import javax.persistence.Lob; import javax.persistence.ManyToMany; +import javax.persistence.NamedQueries; +import javax.persistence.NamedQuery; import javax.persistence.OneToMany; import javax.persistence.OneToOne; +import javax.persistence.TableGenerator; import java.util.Collection; import java.util.Collections; @@ -37,10 +42,24 @@ import static org.apache.commons.lang.StringUtils.defaultString; @javax.persistence.Table(name = "hosts") @Entity +@TableGenerator(name = "host_id_generator", + table = "ambari_sequences", pkColumnName = "sequence_name", valueColumnName = "sequence_value" + , pkColumnValue = "host_id_seq" + , initialValue = 0 + , allocationSize = 1 +) +@NamedQueries({ + @NamedQuery(name = "HostEntity.findByHostName", query = "SELECT host FROM HostEntity host WHERE host.hostName = :hostName"), +}) public class HostEntity implements Comparable { @Id - @Column(name = "host_name", nullable = false, insertable = true, updatable = true) + @Column(name = "id", nullable = false, insertable = true, updatable = false) + @GeneratedValue(strategy = GenerationType.TABLE, generator = "host_id_generator") + private Long id; + + @Column(name = "host_name", nullable = false, insertable = true, updatable = true, unique = true) + @Basic private String hostName; @Column(name = "ipv4", nullable = true, insertable = true, updatable = true) @@ -114,7 +133,7 @@ public class HostEntity implements Comparable { @ManyToMany @JoinTable(name = "ClusterHostMapping", - joinColumns = {@JoinColumn(name = "host_name", referencedColumnName = "host_name")}, + joinColumns = {@JoinColumn(name = "host_id", referencedColumnName = "id")}, inverseJoinColumns = {@JoinColumn(name = "cluster_id", referencedColumnName = "cluster_id")} ) private Collection clusterEntities; @@ -124,7 +143,15 @@ public class HostEntity implements Comparable { @OneToMany(mappedBy = "host", cascade = CascadeType.REMOVE) private Collection hostRoleCommandEntities; - + + public Long getId() { + return id; + } + + public void setId(Long id) { + this.id = id; + } + public String getHostName() { return hostName; } @@ -360,5 +387,4 @@ public class HostEntity implements Comparable { public void setHostVersionEntities(Collection hostVersionEntities) { this.hostVersionEntities = hostVersionEntities; } - } http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/state/Alert.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/state/Alert.java b/ambari-server/src/main/java/org/apache/ambari/server/state/Alert.java index 3211cfc..be99d96 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/state/Alert.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/state/Alert.java @@ -27,7 +27,7 @@ public class Alert { private String instance = null; private String service = null; private String component = null; - private String host = null; + private String hostName = null; private AlertState state = AlertState.UNKNOWN; private String label = null; private String text = null; @@ -50,7 +50,7 @@ public class Alert { instance = alertInstance; service = serviceName; component = componentName; - host = hostName; + this.hostName = hostName; state = alertState; } @@ -86,8 +86,8 @@ public class Alert { * @return the host */ @JsonProperty("host") - public String getHost() { - return host; + public String getHostName() { + return hostName; } /** @@ -156,8 +156,8 @@ public class Alert { } @JsonProperty("host") - public void setHost(String host) { - this.host = host; + public void setHostName(String hostName) { + this.hostName = hostName; } @JsonProperty("state") @@ -217,7 +217,7 @@ public class Alert { int result = (null != name) ? name.hashCode() : 0; result += 31 * result + (null != service ? service.hashCode() : 0); result += 31 * result + (null != component ? component.hashCode() : 0); - result += 31 * result + (null != host ? host.hashCode() : 0); + result += 31 * result + (null != hostName ? hostName.hashCode() : 0); return result; } @@ -242,7 +242,7 @@ public class Alert { sb.append("name=").append(name).append(", "); sb.append("service=").append(service).append(", "); sb.append("component=").append(component).append(", "); - sb.append("host=").append(host).append(", "); + sb.append("host=").append(hostName).append(", "); sb.append("instance=").append(instance).append(", "); sb.append("text='").append(text).append("'"); sb.append('}'); http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/ambari-server/src/main/java/org/apache/ambari/server/upgrade/SchemaUpgradeHelper.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/upgrade/SchemaUpgradeHelper.java b/ambari-server/src/main/java/org/apache/ambari/server/upgrade/SchemaUpgradeHelper.java index 5968b2f..3691af2 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/upgrade/SchemaUpgradeHelper.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/upgrade/SchemaUpgradeHelper.java @@ -173,6 +173,7 @@ public class SchemaUpgradeHelper { catalogBinder.addBinding().to(UpgradeCatalog161.class); catalogBinder.addBinding().to(UpgradeCatalog170.class); catalogBinder.addBinding().to(UpgradeCatalog200.class); + catalogBinder.addBinding().to(UpgradeCatalog210.class); } } http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/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 new file mode 100644 index 0000000..92f1dac --- /dev/null +++ b/ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog210.java @@ -0,0 +1,293 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.ambari.server.upgrade; + +import com.google.inject.Inject; +import com.google.inject.Injector; +import com.google.inject.persist.Transactional; +import org.apache.ambari.server.AmbariException; +import org.apache.ambari.server.configuration.Configuration; +import org.apache.ambari.server.orm.DBAccessor.DBColumnInfo; +import org.apache.ambari.server.orm.dao.HostDAO; +import org.apache.ambari.server.orm.entities.HostEntity; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +import java.sql.ResultSet; +import java.sql.SQLException; + + +/** + * Upgrade catalog for version 2.1.0. + */ +public class UpgradeCatalog210 extends AbstractUpgradeCatalog { + + @Inject + HostDAO hostDAO; + + private static final String CLUSTERS_TABLE = "clusters"; + private static final String HOSTS_TABLE = "hosts"; + private static final String HOST_COMPONENT_DESIRED_STATE_TABLE = "hostcomponentdesiredstate"; + private static final String HOST_COMPONENT_STATE_TABLE = "hostcomponentstate"; + private static final String HOST_STATE_TABLE = "hoststate"; + private static final String HOST_VERSION_TABLE = "host_version"; + private static final String HOST_ROLE_COMMAND_TABLE = "host_role_command"; + private static final String HOST_CONFIG_MAPPING_TABLE = "hostconfigmapping"; + private static final String CONFIG_GROUP_HOST_MAPPING_TABLE = "configgrouphostmapping"; + private static final String KERBEROS_PRINCIPAL_HOST_TABLE = "kerberos_principal_host"; + private static final String CLUSTER_HOST_MAPPING_TABLE = "ClusterHostMapping"; + + /** + * {@inheritDoc} + */ + @Override + public String getSourceVersion() { + return "2.0.0"; + } + + /** + * {@inheritDoc} + */ + @Override + public String getTargetVersion() { + return "2.1.0"; + } + + /** + * Logger. + */ + private static final Logger LOG = LoggerFactory.getLogger + (UpgradeCatalog210.class); + + // ----- Constructors ------------------------------------------------------ + + /** + * Don't forget to register new UpgradeCatalogs in {@link org.apache.ambari.server.upgrade.SchemaUpgradeHelper.UpgradeHelperModule#configure()} + * @param injector Guice injector to track dependencies and uses bindings to inject them. + */ + @Inject + public UpgradeCatalog210(Injector injector) { + super(injector); + this.injector = injector; + } + + // ----- AbstractUpgradeCatalog -------------------------------------------- + + /** + * {@inheritDoc} + */ + @Override + protected void executeDDLUpdates() throws AmbariException, SQLException { + executeHostsDDLUpdates(); + } + + /** + * Execute all of the hosts DDL updates. + * + * @throws org.apache.ambari.server.AmbariException + * @throws java.sql.SQLException + */ + private void executeHostsDDLUpdates() throws AmbariException, SQLException { + Configuration.DatabaseType databaseType = configuration.getDatabaseType(); + + dbAccessor.addColumn(HOSTS_TABLE, new DBColumnInfo("id", Long.class, null, null, true)); + + Long hostId = 0L; + ResultSet resultSet = null; + try { + resultSet = dbAccessor.executeSelect("SELECT host_name FROM hosts"); + hostId = populateHostsId(resultSet); + } finally { + if (resultSet != null) { + resultSet.close(); + } + } + + // Insert host id number into ambari_sequences + dbAccessor.executeQuery("INSERT INTO ambari_sequences (sequence_name, sequence_value) VALUES ('host_id_seq', " + hostId + ")"); + //dbAccessor.insertRow("ambari_sequences", new String[]{"sequence_name", "sequence_value"}, new String[]{"host_id_seq", hostId.toString()}, false); + + // Make the hosts id non-null after all the values are populated + if (databaseType == Configuration.DatabaseType.DERBY) { + // This is a workaround for UpgradeTest.java unit test + dbAccessor.executeQuery("ALTER TABLE hosts ALTER column id NOT NULL"); + } else { + dbAccessor.alterColumn("hosts", new DBColumnInfo("id", Long.class, null, null, false)); + //dbAccessor.executeQuery("ALTER TABLE hosts ALTER column id SET NOT NULL"); + } + + + // Drop the 8 FK constraints in the host-related tables. They will be recreated later after the PK is changed. + // The only host-related table not being included is alert_history. + if (databaseType == Configuration.DatabaseType.DERBY) { + dbAccessor.executeQuery("ALTER TABLE hostcomponentdesiredstate DROP CONSTRAINT hstcmponentdesiredstatehstname"); + dbAccessor.executeQuery("ALTER TABLE hostcomponentstate DROP CONSTRAINT hostcomponentstate_host_name"); + dbAccessor.executeQuery("ALTER TABLE hoststate DROP CONSTRAINT FK_hoststate_host_name"); + dbAccessor.executeQuery("ALTER TABLE host_version DROP CONSTRAINT FK_host_version_host_name"); + dbAccessor.executeQuery("ALTER TABLE host_role_command DROP CONSTRAINT FK_host_role_command_host_name"); + // This FK name is actually different on Derby. + dbAccessor.executeQuery("ALTER TABLE hostconfigmapping DROP CONSTRAINT FK_hostconfigmapping_host_name"); + dbAccessor.executeQuery("ALTER TABLE configgrouphostmapping DROP CONSTRAINT FK_cghm_hname"); + dbAccessor.executeQuery("ALTER TABLE kerberos_principal_host DROP CONSTRAINT FK_krb_pr_host_hostname"); + } else { + dbAccessor.dropConstraint(HOST_COMPONENT_DESIRED_STATE_TABLE, "hstcmponentdesiredstatehstname"); + dbAccessor.dropConstraint(HOST_COMPONENT_STATE_TABLE, "hostcomponentstate_host_name"); + dbAccessor.dropConstraint(HOST_STATE_TABLE, "FK_hoststate_host_name"); + dbAccessor.dropConstraint(HOST_VERSION_TABLE, "FK_host_version_host_name"); + dbAccessor.dropConstraint(HOST_ROLE_COMMAND_TABLE, "FK_host_role_command_host_name"); + dbAccessor.dropConstraint(HOST_CONFIG_MAPPING_TABLE, "FK_hostconfmapping_host_name"); + dbAccessor.dropConstraint(CONFIG_GROUP_HOST_MAPPING_TABLE, "FK_cghm_hname"); + dbAccessor.dropConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_hostname"); + } + + // In Ambari 2.0.0, there were discrepancies with the FK in the ClusterHostMapping table in the Postgres databases. + // They were either swapped, or pointing to the wrong table. Ignore failures for both of these. + try { + dbAccessor.dropConstraint(CLUSTER_HOST_MAPPING_TABLE, "ClusterHostMapping_host_name", true); + } catch (Exception e) { + LOG.warn("Performed best attempt at deleting FK ClusterHostMapping_host_name. " + + "It is possible it did not exist or the deletion failed. " + e.getMessage()); + } + try { + dbAccessor.dropConstraint(CLUSTER_HOST_MAPPING_TABLE, "ClusterHostMapping_cluster_id", true); + } catch (Exception e) { + LOG.warn("Performed best attempt at deleting FK ClusterHostMapping_cluster_id. " + + "It is possible it did not exist or the deletion failed. " + e.getMessage()); + } + + // Readd the FK to the cluster_id; will add the host_id at the end. + dbAccessor.addFKConstraint(CLUSTER_HOST_MAPPING_TABLE, "FK_clusterhostmapping_cluster_id", + "cluster_id", CLUSTERS_TABLE, "cluster_id", false); + + // Drop the PK, and recreate it on the id instead + if (databaseType == Configuration.DatabaseType.DERBY) { + String constraintName = getDerbyTableConstraintName("p", HOSTS_TABLE); + if (null != constraintName) { + dbAccessor.executeQuery("ALTER TABLE hosts DROP CONSTRAINT " + constraintName); + } + } else { + dbAccessor.dropConstraint(HOSTS_TABLE, "hosts_pkey"); + } + dbAccessor.executeQuery("ALTER TABLE hosts ADD CONSTRAINT PK_hosts_id PRIMARY KEY (id)"); + + dbAccessor.executeQuery("ALTER TABLE hosts ADD CONSTRAINT UQ_hosts_host_name UNIQUE (host_name)"); + + // TODO, for now, these still point to the host_name and will be fixed one table at a time to point to the host id. + // Re-add the FKs + dbAccessor.addFKConstraint(HOST_COMPONENT_DESIRED_STATE_TABLE, "hstcmponentdesiredstatehstname", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(HOST_COMPONENT_STATE_TABLE, "hostcomponentstate_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(HOST_STATE_TABLE, "FK_hoststate_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(HOST_VERSION_TABLE, "FK_host_version_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(HOST_ROLE_COMMAND_TABLE, "FK_host_role_command_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(HOST_CONFIG_MAPPING_TABLE, "FK_hostconfmapping_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(CONFIG_GROUP_HOST_MAPPING_TABLE, "FK_cghm_hname", + "host_name", HOSTS_TABLE, "host_name", false); + dbAccessor.addFKConstraint(KERBEROS_PRINCIPAL_HOST_TABLE, "FK_krb_pr_host_host_name", + "host_name", HOSTS_TABLE, "host_name", false); + + + // Add host_id to the host-related tables, and populate the host_id, one table at a time. + dbAccessor.addColumn(CLUSTER_HOST_MAPPING_TABLE, new DBColumnInfo("host_id", Long.class, null, null, true)); + dbAccessor.executeQuery("UPDATE clusterhostmapping chm SET host_id = (SELECT id FROM hosts h WHERE h.host_name = chm.host_name) WHERE chm.host_id IS NULL AND chm.host_name IS NOT NULL"); + + if (databaseType == Configuration.DatabaseType.DERBY) { + // This is a workaround for UpgradeTest.java unit test + dbAccessor.executeQuery("ALTER TABLE clusterhostmapping ALTER column host_id NOT NULL"); + } else { + dbAccessor.executeQuery("ALTER TABLE clusterhostmapping ALTER column host_id SET NOT NULL"); + } + + // These are the FKs that have already been corrected. + dbAccessor.addFKConstraint(CLUSTER_HOST_MAPPING_TABLE, "FK_clusterhostmapping_host_id", + "host_id", HOSTS_TABLE, "id", false); + + dbAccessor.dropColumn(CLUSTER_HOST_MAPPING_TABLE, "host_name"); + } + + // ----- UpgradeCatalog ---------------------------------------------------- + + /** + * Populate the id of the hosts table with an auto-increment int. + * @param resultSet Rows from the hosts table + * @return Returns an integer with the id for the next host record to be inserted. + * @throws SQLException + */ + @Transactional + private Long populateHostsId(ResultSet resultSet) throws SQLException { + Long hostId = 0L; + if (resultSet != null) { + try { + while (resultSet.next()) { + final String hostName = resultSet.getString(1); + HostEntity host = hostDAO.findByName(hostName); + host.setId(++hostId); + hostDAO.merge(host); + } + } catch (Exception e) { + LOG.error("Unable to populate the id of the hosts. " + e.getMessage()); + } + } + return hostId; + } + + /** + * Get the constraint name created by Derby if one was not specified for the table. + * @param type Constraint-type, either, "p" (Primary), "c" (Check), "f" (Foreign), "u" (Unique) + * @param tableName Table Name + * @return Return the constraint name, or null if not found. + * @throws SQLException + */ + private String getDerbyTableConstraintName(String type, String tableName) throws SQLException { + ResultSet resultSet = null; + boolean found = false; + String constraint = null; + + try { + resultSet = dbAccessor.executeSelect("SELECT c.constraintname, c.type, t.tablename FROM sys.sysconstraints c, sys.systables t WHERE c.tableid = t.tableid"); + while(resultSet.next()) { + constraint = resultSet.getString(1); + String recordType = resultSet.getString(2); + String recordTableName = resultSet.getString(3); + + if (recordType.equalsIgnoreCase(type) && recordTableName.equalsIgnoreCase(tableName)) { + found = true; + break; + } + } + } finally { + if (resultSet != null) { + resultSet.close(); + } + } + return found ? constraint : null; + } + + /** + * {@inheritDoc} + */ + @Override + protected void executeDMLUpdates() throws AmbariException, SQLException { + } +} http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/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 9ff62df..be90dce 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql @@ -26,6 +26,10 @@ delimiter ; # USE @schema; +-- DEVELOPER COMMENT +-- Ambari is transitioning to make the host_id the FK instead of the host_name. +-- Please do not remove lines that are related to this change and are being staged. + CREATE TABLE clusters ( cluster_id BIGINT NOT NULL, resource_id BIGINT NOT NULL, @@ -62,7 +66,9 @@ CREATE TABLE serviceconfig ( CREATE TABLE serviceconfighosts ( service_config_id BIGINT NOT NULL, hostname VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY(service_config_id, hostname)); + --PRIMARY KEY(service_config_id, host_id)); CREATE TABLE serviceconfigmapping ( service_config_id BIGINT NOT NULL, @@ -97,12 +103,14 @@ CREATE TABLE hostcomponentdesiredstate ( desired_stack_version VARCHAR(255) NOT NULL, desired_state VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, service_name VARCHAR(255) NOT NULL, admin_state VARCHAR(32), maintenance_state VARCHAR(32) NOT NULL 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_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hostcomponentstate ( cluster_id BIGINT NOT NULL, @@ -111,12 +119,15 @@ CREATE TABLE hostcomponentstate ( current_stack_version VARCHAR(255) NOT NULL, current_state VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, service_name VARCHAR(255) NOT NULL, upgrade_state VARCHAR(32) NOT NULL DEFAULT 'NONE', security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', PRIMARY KEY (cluster_id, component_name, host_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hosts ( + id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, cpu_count INTEGER NOT NULL, cpu_info VARCHAR(255) NOT NULL, @@ -132,7 +143,7 @@ CREATE TABLE hosts ( public_host_name VARCHAR(255), rack_info VARCHAR(255) NOT NULL, total_mem BIGINT NOT NULL, - PRIMARY KEY (host_name)); + PRIMARY KEY (id)); CREATE TABLE hoststate ( agent_version VARCHAR(255) NOT NULL, @@ -140,14 +151,17 @@ CREATE TABLE hoststate ( current_state VARCHAR(255) NOT NULL, health_status VARCHAR(255), host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, time_in_state BIGINT NOT NULL, maintenance_state VARCHAR(512), PRIMARY KEY (host_name)); + --PRIMARY KEY (host_id)); CREATE TABLE host_version ( id BIGINT NOT NULL, repo_version_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, state VARCHAR(32) NOT NULL, PRIMARY KEY (id)); @@ -204,6 +218,7 @@ CREATE TABLE host_role_command ( event LONGTEXT NOT NULL, exitcode INTEGER NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, last_attempt_time BIGINT NOT NULL, request_id BIGINT NOT NULL, role VARCHAR(255), @@ -271,6 +286,7 @@ CREATE TABLE requestoperationlevel ( service_name VARCHAR(255), host_component_name VARCHAR(255), host_name VARCHAR(255), + --host_id BIGINT NOT NULL, PRIMARY KEY (operation_level_id)); CREATE TABLE key_value_store (`key` VARCHAR(255), @@ -289,6 +305,7 @@ CREATE TABLE clusterconfigmapping ( CREATE TABLE hostconfigmapping ( create_timestamp BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, cluster_id BIGINT NOT NULL, type_name VARCHAR(255) NOT NULL, selected INTEGER NOT NULL DEFAULT 0, @@ -296,6 +313,7 @@ CREATE TABLE hostconfigmapping ( version_tag VARCHAR(255) NOT NULL, user_name VARCHAR(255) NOT NULL DEFAULT '_db', PRIMARY KEY (create_timestamp, host_name, cluster_id, type_name)); + --PRIMARY KEY (create_timestamp, host_id, cluster_id, type_name)); CREATE TABLE metainfo ( `metainfo_key` VARCHAR(255), @@ -305,7 +323,9 @@ CREATE TABLE metainfo ( CREATE TABLE ClusterHostMapping ( cluster_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY (cluster_id, host_name)); + --PRIMARY KEY (cluster_id, host_id)); CREATE TABLE ambari_sequences ( sequence_name VARCHAR(255), @@ -334,7 +354,9 @@ CREATE TABLE configgroup ( CREATE TABLE configgrouphostmapping ( config_group_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY(config_group_id, host_name)); + --PRIMARY KEY(config_group_id, host_id)); CREATE TABLE requestschedule ( schedule_id bigint, @@ -530,6 +552,7 @@ 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); @@ -547,24 +570,30 @@ ALTER TABLE clusterstate ADD CONSTRAINT FK_clusterstate_cluster_id FOREIGN KEY ( 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 hstcmponentdesiredstatehstname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmponentdesiredstatehstid FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hstcomponentstatecomponentname FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_id FOREIGN KEY (host_id) REFERENCES hosts (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_role_command ADD CONSTRAINT FK_host_role_command_host_id FOREIGN KEY (host_id) REFERENCES hosts (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 ClusterHostMapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE ClusterHostMapping ADD CONSTRAINT ClusterHostMapping_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +ALTER TABLE ClusterHostMapping ADD CONSTRAINT FK_clusterhostmapping_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 (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostconfigmapping ADD CONSTRAINT FK_hostconfmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (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); @@ -573,6 +602,7 @@ ALTER TABLE confgroupclusterconfigmapping ADD CONSTRAINT FK_confg FOREIGN KEY (c 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_hname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE configgrouphostmapping ADD CONSTRAINT FK_cghm_host_id FOREIGN KEY (host_id) REFERENCES hosts (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); @@ -609,12 +639,13 @@ CREATE TABLE kerberos_principal ( CREATE TABLE kerberos_principal_host ( principal_name VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY(principal_name, host_name) + --PRIMARY KEY(principal_name, host_id) ); -ALTER TABLE kerberos_principal_host -ADD CONSTRAINT FK_krb_pr_host_hostname -FOREIGN KEY (host_name) REFERENCES hosts (host_name) ON DELETE CASCADE; +ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_hostname FOREIGN KEY (host_name) REFERENCES hosts (host_name) ON DELETE CASCADE; +--ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_id FOREIGN KEY (host_id) REFERENCES hosts (id) ON DELETE CASCADE; ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_principalname @@ -769,6 +800,7 @@ CREATE TABLE upgrade_item ( -- In order for the first ID to be 1, must initialize the ambari_sequences table with a sequence_value of 0. INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('cluster_id_seq', 1); +INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('host_id_seq', 0); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('host_role_command_id_seq', 1); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('user_id_seq', 2); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('group_id_seq', 1); http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/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 7d62aee..76f0e6b 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql @@ -16,6 +16,10 @@ -- limitations under the License. -- +-- DEVELOPER COMMENT +-- Ambari is transitioning to make the host_id the FK instead of the host_name. +-- Please do not remove lines that are related to this change and are being staged. + ------create tables--------- CREATE TABLE clusters ( cluster_id NUMBER(19) NOT NULL, @@ -88,12 +92,14 @@ CREATE TABLE hostcomponentdesiredstate ( desired_stack_version VARCHAR2(255) NULL, desired_state VARCHAR2(255) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, service_name VARCHAR2(255) NOT NULL, admin_state VARCHAR2(32) NULL, 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_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hostcomponentstate ( cluster_id NUMBER(19) NOT NULL, @@ -102,12 +108,15 @@ CREATE TABLE hostcomponentstate ( current_stack_version VARCHAR2(255) NOT NULL, current_state VARCHAR2(255) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, 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_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hosts ( + id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, cpu_count INTEGER NOT NULL, cpu_info VARCHAR2(255) NULL, @@ -123,7 +132,7 @@ CREATE TABLE hosts ( public_host_name VARCHAR2(255) NULL, rack_info VARCHAR2(255) NOT NULL, total_mem INTEGER NOT NULL, - PRIMARY KEY (host_name)); + PRIMARY KEY (id)); CREATE TABLE hoststate ( agent_version VARCHAR2(255) NULL, @@ -131,14 +140,17 @@ CREATE TABLE hoststate ( current_state VARCHAR2(255) NOT NULL, health_status VARCHAR2(255) NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, time_in_state NUMBER(19) NOT NULL, maintenance_state VARCHAR2(512), PRIMARY KEY (host_name)); + --PRIMARY KEY (host_id)); CREATE TABLE host_version ( id NUMBER(19) NOT NULL, repo_version_id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, state VARCHAR2(32) NOT NULL, PRIMARY KEY (id)); @@ -195,6 +207,7 @@ CREATE TABLE host_role_command ( event CLOB NULL, exitcode NUMBER(10) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, last_attempt_time NUMBER(19) NOT NULL, request_id NUMBER(19) NOT NULL, role VARCHAR2(255) NULL, @@ -262,6 +275,7 @@ CREATE TABLE requestoperationlevel ( service_name VARCHAR2(255), host_component_name VARCHAR2(255), host_name VARCHAR2(255), + --host_id NUMBER(19) NOT NULL, PRIMARY KEY (operation_level_id)); CREATE TABLE key_value_store ( @@ -281,6 +295,7 @@ CREATE TABLE clusterconfigmapping ( CREATE TABLE hostconfigmapping ( create_timestamp NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, cluster_id NUMBER(19) NOT NULL, type_name VARCHAR2(255) NOT NULL, selected NUMBER(10) NOT NULL, @@ -288,6 +303,7 @@ CREATE TABLE hostconfigmapping ( version_tag VARCHAR2(255) NOT NULL, user_name VARCHAR(255) DEFAULT '_db', PRIMARY KEY (create_timestamp, host_name, cluster_id, type_name)); + --PRIMARY KEY (create_timestamp, host_id, cluster_id, type_name)); CREATE TABLE metainfo ( "metainfo_key" VARCHAR2(255) NOT NULL, @@ -297,7 +313,9 @@ CREATE TABLE metainfo ( CREATE TABLE ClusterHostMapping ( cluster_id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, PRIMARY KEY (cluster_id, host_name)); + --PRIMARY KEY (cluster_id, host_id)); CREATE TABLE ambari_sequences ( sequence_name VARCHAR2(50) NOT NULL, @@ -326,7 +344,9 @@ CREATE TABLE confgroupclusterconfigmapping ( CREATE TABLE configgrouphostmapping ( config_group_id NUMBER(19) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, PRIMARY KEY(config_group_id, host_name)); + --PRIMARY KEY(config_group_id, host_id)); CREATE TABLE requestschedule ( schedule_id NUMBER(19), @@ -520,6 +540,7 @@ 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); @@ -538,24 +559,30 @@ ALTER TABLE clusterstate ADD CONSTRAINT FK_clusterstate_cluster_id FOREIGN KEY ( 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 hstcmponentdesiredstatehstname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmponentdesiredstatehstid FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hstcomponentstatecomponentname FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_id FOREIGN KEY (host_id) REFERENCES hosts (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_role_command ADD CONSTRAINT FK_host_role_command_host_id FOREIGN KEY (host_id) REFERENCES hosts (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 ClusterHostMapping_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); -ALTER TABLE ClusterHostMapping ADD CONSTRAINT ClusterHostMapping_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +ALTER TABLE ClusterHostMapping ADD CONSTRAINT FK_clusterhostmapping_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 (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostconfigmapping ADD CONSTRAINT FK_hostconfmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (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 configgroup ADD CONSTRAINT FK_configgroup_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); @@ -563,6 +590,7 @@ ALTER TABLE confgroupclusterconfigmapping ADD CONSTRAINT FK_confg FOREIGN KEY (v 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_hname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE configgrouphostmapping ADD CONSTRAINT FK_cghm_host_id FOREIGN KEY (host_id) REFERENCES hosts (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); @@ -599,12 +627,15 @@ CREATE TABLE kerberos_principal ( CREATE TABLE kerberos_principal_host ( principal_name VARCHAR2(255) NOT NULL, host_name VARCHAR2(255) NOT NULL, + --host_id NUMBER(19) NOT NULL, PRIMARY KEY(principal_name, host_name) + --PRIMARY KEY(principal_name, host_id) ); ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_hostname FOREIGN KEY (host_name) REFERENCES hosts (host_name) ON DELETE CASCADE; +--ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_id FOREIGN KEY (host_id) REFERENCES hosts (id) ON DELETE CASCADE; ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_principalname @@ -765,6 +796,7 @@ INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('user_id_seq INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('group_id_seq', 0); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('member_id_seq', 0); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('cluster_id_seq', 0); +INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('host_id_seq', 0); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('configgroup_id_seq', 1); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('requestschedule_id_seq', 1); INSERT INTO ambari_sequences(sequence_name, sequence_value) values ('resourcefilter_id_seq', 1); http://git-wip-us.apache.org/repos/asf/ambari/blob/f73936a2/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 78a263f..0906587 100644 --- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql +++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql @@ -16,6 +16,10 @@ -- limitations under the License. -- +-- DEVELOPER COMMENT +-- Ambari is transitioning to make the host_id the FK instead of the host_name. +-- Please do not remove lines that are related to this change and are being staged. + ------create tables and grant privileges to db user--------- CREATE TABLE clusters ( cluster_id BIGINT NOT NULL, @@ -55,7 +59,8 @@ CREATE TABLE serviceconfig ( version BIGINT NOT NULL, create_timestamp BIGINT NOT NULL, user_name VARCHAR(255) NOT NULL DEFAULT '_db', - group_id BIGINT, note TEXT, + group_id BIGINT, + note TEXT, PRIMARY KEY (service_config_id)); CREATE TABLE serviceconfighosts ( @@ -96,12 +101,14 @@ CREATE TABLE hostcomponentdesiredstate ( desired_stack_version VARCHAR(255) NOT NULL, desired_state VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, service_name VARCHAR(255) NOT NULL, admin_state VARCHAR(32), maintenance_state VARCHAR(32) NOT NULL, security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', restart_required SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY (cluster_id, component_name, host_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hostcomponentstate ( cluster_id BIGINT NOT NULL, @@ -110,19 +117,23 @@ CREATE TABLE hostcomponentstate ( current_stack_version VARCHAR(255) NOT NULL, current_state VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, service_name VARCHAR(255) NOT NULL, upgrade_state VARCHAR(32) NOT NULL DEFAULT 'NONE', security_state VARCHAR(32) NOT NULL DEFAULT 'UNSECURED', PRIMARY KEY (cluster_id, component_name, host_name, service_name)); + --PRIMARY KEY (cluster_id, component_name, host_id, service_name)); CREATE TABLE hosts ( + id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, cpu_count INTEGER NOT NULL, ph_cpu_count INTEGER, cpu_info VARCHAR(255) NOT NULL, discovery_status VARCHAR(2000) NOT NULL, host_attributes VARCHAR(20000) NOT NULL, - ipv4 VARCHAR(255), ipv6 VARCHAR(255), + ipv4 VARCHAR(255), + ipv6 VARCHAR(255), public_host_name VARCHAR(255), last_registration_time BIGINT NOT NULL, os_arch VARCHAR(255) NOT NULL, @@ -130,7 +141,7 @@ CREATE TABLE hosts ( os_type VARCHAR(255) NOT NULL, rack_info VARCHAR(255) NOT NULL, total_mem BIGINT NOT NULL, - PRIMARY KEY (host_name)); + PRIMARY KEY (id)); CREATE TABLE hoststate ( agent_version VARCHAR(255) NOT NULL, @@ -138,14 +149,17 @@ CREATE TABLE hoststate ( current_state VARCHAR(255) NOT NULL, health_status VARCHAR(255), host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, time_in_state BIGINT NOT NULL, maintenance_state VARCHAR(512), PRIMARY KEY (host_name)); + --PRIMARY KEY (host_id)); CREATE TABLE host_version ( id BIGINT NOT NULL, repo_version_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, state VARCHAR(32) NOT NULL, PRIMARY KEY (id)); @@ -205,6 +219,7 @@ CREATE TABLE host_role_command ( event VARCHAR(32000) NOT NULL, exitcode INTEGER NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, last_attempt_time BIGINT NOT NULL, request_id BIGINT NOT NULL, role VARCHAR(255), @@ -272,12 +287,15 @@ CREATE TABLE requestoperationlevel ( service_name VARCHAR(255), host_component_name VARCHAR(255), host_name VARCHAR(255), + --host_id BIGINT NOT NULL, PRIMARY KEY (operation_level_id)); CREATE TABLE ClusterHostMapping ( cluster_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY (cluster_id, host_name)); + --PRIMARY KEY (cluster_id, host_id)); CREATE TABLE key_value_store ( "key" VARCHAR(255), @@ -287,6 +305,7 @@ CREATE TABLE key_value_store ( CREATE TABLE hostconfigmapping ( cluster_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, type_name VARCHAR(255) NOT NULL, version_tag VARCHAR(255) NOT NULL, service_name VARCHAR(255), @@ -294,6 +313,7 @@ CREATE TABLE hostconfigmapping ( selected INTEGER NOT NULL DEFAULT 0, user_name VARCHAR(255) NOT NULL DEFAULT '_db', PRIMARY KEY (cluster_id, host_name, type_name, create_timestamp)); + --PRIMARY KEY (cluster_id, host_id, type_name, create_timestamp)); CREATE TABLE metainfo ( "metainfo_key" VARCHAR(255), @@ -326,7 +346,9 @@ CREATE TABLE confgroupclusterconfigmapping ( CREATE TABLE configgrouphostmapping ( config_group_id BIGINT NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY(config_group_id, host_name)); + --PRIMARY KEY(config_group_id, host_id)); CREATE TABLE requestschedule ( schedule_id bigint, @@ -351,7 +373,8 @@ CREATE TABLE requestschedule ( PRIMARY KEY(schedule_id)); CREATE TABLE requestschedulebatchrequest ( - schedule_id bigint, batch_id bigint, + schedule_id bigint, + batch_id bigint, request_id bigint, request_type varchar(255), request_uri varchar(1024), @@ -382,7 +405,7 @@ CREATE TABLE hostgroup_component ( CREATE TABLE blueprint_configuration ( blueprint_name varchar(255) NOT NULL, type_name varchar(255) NOT NULL, - config_data TEXT NOT NULL , + config_data TEXT NOT NULL, config_attributes varchar(32000), PRIMARY KEY(blueprint_name, type_name)); @@ -463,7 +486,8 @@ CREATE TABLE viewentity ( view_name VARCHAR(255) NOT NULL, view_instance_name VARCHAR(255) NOT NULL, class_name VARCHAR(255) NOT NULL, - id_property VARCHAR(255), PRIMARY KEY(id)); + id_property VARCHAR(255), + PRIMARY KEY(id)); CREATE TABLE adminresourcetype ( resource_type_id INTEGER NOT NULL, @@ -518,6 +542,7 @@ CREATE TABLE artifact ( --------altering tables by creating unique constraints---------- 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); @@ -535,28 +560,36 @@ ALTER TABLE clusterstate ADD CONSTRAINT FK_clusterstate_cluster_id FOREIGN KEY ( 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 hstcmponentdesiredstatehstname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmponentdesiredstatehstid FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hstcomponentstatecomponentname FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate (component_name, cluster_id, service_name); ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostcomponentstate ADD CONSTRAINT hostcomponentstate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hoststate ADD CONSTRAINT FK_hoststate_host_id FOREIGN KEY (host_id) REFERENCES hosts (id); ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_version ADD CONSTRAINT FK_host_version_host_id FOREIGN KEY (host_id) REFERENCES hosts (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE host_role_command ADD CONSTRAINT FK_host_role_command_host_id FOREIGN KEY (host_id) REFERENCES hosts (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 ClusterHostMapping_host_name FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id); +ALTER TABLE ClusterHostMapping ADD CONSTRAINT FK_clusterhostmapping_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 (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_name FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE hostconfigmapping ADD CONSTRAINT FK_hostconfmapping_host_id FOREIGN KEY (host_id) REFERENCES hosts (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 (version_tag, config_type, cluster_id) REFERENCES clusterconfig (version_tag, type_name, cluster_id); 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_hname FOREIGN KEY (host_name) REFERENCES hosts (host_name); +--ALTER TABLE configgrouphostmapping ADD CONSTRAINT FK_cghm_host_id FOREIGN KEY (host_id) REFERENCES hosts (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); @@ -596,12 +629,15 @@ CREATE TABLE kerberos_principal ( CREATE TABLE kerberos_principal_host ( principal_name VARCHAR(255) NOT NULL, host_name VARCHAR(255) NOT NULL, + --host_id BIGINT NOT NULL, PRIMARY KEY(principal_name, host_name) + --PRIMARY KEY(principal_name, host_id) ); ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_hostname FOREIGN KEY (host_name) REFERENCES hosts (host_name) ON DELETE CASCADE; +--ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_id FOREIGN KEY (host_id) REFERENCES hosts (id) ON DELETE CASCADE; ALTER TABLE kerberos_principal_host ADD CONSTRAINT FK_krb_pr_host_principalname @@ -757,9 +793,11 @@ CREATE TABLE upgrade_item ( ---------inserting some data----------- -- In order for the first ID to be 1, must initialize the ambari_sequences table with a sequence_value of 0. BEGIN; - INSERT INTO ambari_sequences (sequence_name, sequence_value) +INSERT INTO ambari_sequences (sequence_name, sequence_value) SELECT 'cluster_id_seq', 1 UNION ALL + SELECT 'host_id_seq', 0 + UNION ALL SELECT 'user_id_seq', 2 UNION ALL SELECT 'group_id_seq', 1 @@ -818,30 +856,32 @@ BEGIN; union all select 'upgrade_group_id_seq', 0 union all + select 'upgrade_group_id_seq', 0 + union all select 'upgrade_item_id_seq', 0; - INSERT INTO adminresourcetype (resource_type_id, resource_type_name) +INSERT INTO adminresourcetype (resource_type_id, resource_type_name) SELECT 1, 'AMBARI' UNION ALL SELECT 2, 'CLUSTER' UNION ALL SELECT 3, 'VIEW'; - INSERT INTO adminresource (resource_id, resource_type_id) +INSERT INTO adminresource (resource_id, resource_type_id) SELECT 1, 1; - INSERT INTO adminprincipaltype (principal_type_id, principal_type_name) +INSERT INTO adminprincipaltype (principal_type_id, principal_type_name) SELECT 1, 'USER' UNION ALL SELECT 2, 'GROUP'; - INSERT INTO adminprincipal (principal_id, principal_type_id) +INSERT INTO adminprincipal (principal_id, principal_type_id) SELECT 1, 1; - INSERT INTO Users (user_id, principal_id, user_name, user_password) +INSERT INTO Users (user_id, principal_id, user_name, user_password) SELECT 1, 1, 'admin', '538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00'; - INSERT INTO adminpermission(permission_id, permission_name, resource_type_id) +INSERT INTO adminpermission(permission_id, permission_name, resource_type_id) SELECT 1, 'AMBARI.ADMIN', 1 UNION ALL SELECT 2, 'CLUSTER.READ', 2 @@ -850,158 +890,158 @@ BEGIN; UNION ALL SELECT 4, 'VIEW.USE', 3; - INSERT INTO adminprivilege (privilege_id, permission_id, resource_id, principal_id) +INSERT INTO adminprivilege (privilege_id, permission_id, resource_id, principal_id) SELECT 1, 1, 1, 1; - INSERT INTO metainfo (metainfo_key, metainfo_value) +INSERT INTO metainfo (metainfo_key, metainfo_value) SELECT 'version', '${ambariVersion}'; COMMIT; -- Quartz tables CREATE TABLE qrtz_job_details - ( - SCHED_NAME VARCHAR(120) NOT NULL, - JOB_NAME VARCHAR(200) NOT NULL, - JOB_GROUP VARCHAR(200) NOT NULL, - DESCRIPTION VARCHAR(250) NULL, - JOB_CLASS_NAME VARCHAR(250) NOT NULL, - IS_DURABLE BOOL NOT NULL, - IS_NONCONCURRENT BOOL NOT NULL, - IS_UPDATE_DATA BOOL NOT NULL, - REQUESTS_RECOVERY BOOL NOT NULL, - JOB_DATA BYTEA NULL, - PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + JOB_NAME VARCHAR(200) NOT NULL, + JOB_GROUP VARCHAR(200) NOT NULL, + DESCRIPTION VARCHAR(250) NULL, + JOB_CLASS_NAME VARCHAR(250) NOT NULL, + IS_DURABLE BOOL NOT NULL, + IS_NONCONCURRENT BOOL NOT NULL, + IS_UPDATE_DATA BOOL NOT NULL, + REQUESTS_RECOVERY BOOL NOT NULL, + JOB_DATA BYTEA NULL, + PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) ); CREATE TABLE qrtz_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - JOB_NAME VARCHAR(200) NOT NULL, - JOB_GROUP VARCHAR(200) NOT NULL, - DESCRIPTION VARCHAR(250) NULL, - NEXT_FIRE_TIME BIGINT NULL, - PREV_FIRE_TIME BIGINT NULL, - PRIORITY INTEGER NULL, - TRIGGER_STATE VARCHAR(16) NOT NULL, - TRIGGER_TYPE VARCHAR(8) NOT NULL, - START_TIME BIGINT NOT NULL, - END_TIME BIGINT NULL, - CALENDAR_NAME VARCHAR(200) NULL, - MISFIRE_INSTR SMALLINT NULL, - JOB_DATA BYTEA NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) - REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + JOB_NAME VARCHAR(200) NOT NULL, + JOB_GROUP VARCHAR(200) NOT NULL, + DESCRIPTION VARCHAR(250) NULL, + NEXT_FIRE_TIME BIGINT NULL, + PREV_FIRE_TIME BIGINT NULL, + PRIORITY INTEGER NULL, + TRIGGER_STATE VARCHAR(16) NOT NULL, + TRIGGER_TYPE VARCHAR(8) NOT NULL, + START_TIME BIGINT NOT NULL, + END_TIME BIGINT NULL, + CALENDAR_NAME VARCHAR(200) NULL, + MISFIRE_INSTR SMALLINT NULL, + JOB_DATA BYTEA NULL, + PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), + FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) + REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP) ); CREATE TABLE qrtz_simple_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - REPEAT_COUNT BIGINT NOT NULL, - REPEAT_INTERVAL BIGINT NOT NULL, - TIMES_TRIGGERED BIGINT NOT NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + REPEAT_COUNT BIGINT NOT NULL, + REPEAT_INTERVAL BIGINT NOT NULL, + TIMES_TRIGGERED BIGINT NOT NULL, + PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), + FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) + REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_cron_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - CRON_EXPRESSION VARCHAR(120) NOT NULL, - TIME_ZONE_ID VARCHAR(80), - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + CRON_EXPRESSION VARCHAR(120) NOT NULL, + TIME_ZONE_ID VARCHAR(80), + PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), + FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) + REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_simprop_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - STR_PROP_1 VARCHAR(512) NULL, - STR_PROP_2 VARCHAR(512) NULL, - STR_PROP_3 VARCHAR(512) NULL, - INT_PROP_1 INT NULL, - INT_PROP_2 INT NULL, - LONG_PROP_1 BIGINT NULL, - LONG_PROP_2 BIGINT NULL, - DEC_PROP_1 NUMERIC(13,4) NULL, - DEC_PROP_2 NUMERIC(13,4) NULL, - BOOL_PROP_1 BOOL NULL, - BOOL_PROP_2 BOOL NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + STR_PROP_1 VARCHAR(512) NULL, + STR_PROP_2 VARCHAR(512) NULL, + STR_PROP_3 VARCHAR(512) NULL, + INT_PROP_1 INT NULL, + INT_PROP_2 INT NULL, + LONG_PROP_1 BIGINT NULL, + LONG_PROP_2 BIGINT NULL, + DEC_PROP_1 NUMERIC(13,4) NULL, + DEC_PROP_2 NUMERIC(13,4) NULL, + BOOL_PROP_1 BOOL NULL, + BOOL_PROP_2 BOOL NULL, + PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), + FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) + REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_blob_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - BLOB_DATA BYTEA NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + BLOB_DATA BYTEA NULL, + PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), + FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) + REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_calendars - ( - SCHED_NAME VARCHAR(120) NOT NULL, - CALENDAR_NAME VARCHAR(200) NOT NULL, - CALENDAR BYTEA NOT NULL, - PRIMARY KEY (SCHED_NAME,CALENDAR_NAME) +( + SCHED_NAME VARCHAR(120) NOT NULL, + CALENDAR_NAME VARCHAR(200) NOT NULL, + CALENDAR BYTEA NOT NULL, + PRIMARY KEY (SCHED_NAME,CALENDAR_NAME) ); CREATE TABLE qrtz_paused_trigger_grps - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) +( + SCHED_NAME VARCHAR(120) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) ); CREATE TABLE qrtz_fired_triggers - ( - SCHED_NAME VARCHAR(120) NOT NULL, - ENTRY_ID VARCHAR(95) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - INSTANCE_NAME VARCHAR(200) NOT NULL, - FIRED_TIME BIGINT NOT NULL, - SCHED_TIME BIGINT NOT NULL, - PRIORITY INTEGER NOT NULL, - STATE VARCHAR(16) NOT NULL, - JOB_NAME VARCHAR(200) NULL, - JOB_GROUP VARCHAR(200) NULL, - IS_NONCONCURRENT BOOL NULL, - REQUESTS_RECOVERY BOOL NULL, - PRIMARY KEY (SCHED_NAME,ENTRY_ID) +( + SCHED_NAME VARCHAR(120) NOT NULL, + ENTRY_ID VARCHAR(95) NOT NULL, + TRIGGER_NAME VARCHAR(200) NOT NULL, + TRIGGER_GROUP VARCHAR(200) NOT NULL, + INSTANCE_NAME VARCHAR(200) NOT NULL, + FIRED_TIME BIGINT NOT NULL, + SCHED_TIME BIGINT NOT NULL, + PRIORITY INTEGER NOT NULL, + STATE VARCHAR(16) NOT NULL, + JOB_NAME VARCHAR(200) NULL, + JOB_GROUP VARCHAR(200) NULL, + IS_NONCONCURRENT BOOL NULL, + REQUESTS_RECOVERY BOOL NULL, + PRIMARY KEY (SCHED_NAME,ENTRY_ID) ); CREATE TABLE qrtz_scheduler_state - ( - SCHED_NAME VARCHAR(120) NOT NULL, - INSTANCE_NAME VARCHAR(200) NOT NULL, - LAST_CHECKIN_TIME BIGINT NOT NULL, - CHECKIN_INTERVAL BIGINT NOT NULL, - PRIMARY KEY (SCHED_NAME,INSTANCE_NAME) +( + SCHED_NAME VARCHAR(120) NOT NULL, + INSTANCE_NAME VARCHAR(200) NOT NULL, + LAST_CHECKIN_TIME BIGINT NOT NULL, + CHECKIN_INTERVAL BIGINT NOT NULL, + PRIMARY KEY (SCHED_NAME,INSTANCE_NAME) ); CREATE TABLE qrtz_locks - ( - SCHED_NAME VARCHAR(120) NOT NULL, - LOCK_NAME VARCHAR(40) NOT NULL, - PRIMARY KEY (SCHED_NAME,LOCK_NAME) +( + SCHED_NAME VARCHAR(120) NOT NULL, + LOCK_NAME VARCHAR(40) NOT NULL, + PRIMARY KEY (SCHED_NAME,LOCK_NAME) ); create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY);