ambari-server/src/test/java/org/apache/ambari/server/orm/db/DDLTests.java - ambari - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  *     http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing, software
  * distributed under the License is distributed on an "AS IS" BASIS,
  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  * See the License for the specific language governing permissions and
  * limitations under the License.
  */
 package org.apache.ambari.server.orm.db;

 import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;

 import org.apache.commons.lang.StringUtils;
 import org.junit.Assert;
 import org.junit.Test;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;

 import com.google.common.base.Joiner;
 import com.google.common.base.Optional;
 import com.google.common.collect.ImmutableSet;
 import com.google.common.collect.Maps;
 import com.google.common.collect.Sets;


 /**
  * Test to check the sanity and consistence of DDL scripts for different SQL dialects.
  * (e.g. no unnamed constraints, the same tables with the same columns and constraints must exist)
  */
 public class DDLTests {
   private static final Logger LOG = LoggerFactory.getLogger(DDLTestUtils.class);

   private static final int EXPECTED_ALTER_TABLE_COUNT = 1;

   @Test
   public void testVerifyDerby() throws Exception {
     verifyDDL("Derby");
   }

   @Test
   public void testVerifyPostgres() throws Exception {
     verifyDDL("Postgres");
   }

   @Test
   public void testVerifyMySQL() throws Exception {
     verifyDDL("MySQL");
   }

   @Test
   public void testVerifyOracle() throws Exception {
     verifyDDL("Oracle");
   }

   @Test
   public void testVerifySqlAnywhere() throws Exception {
     verifyDDL("SQLAnywhere");
   }

   @Test
   public void testVerifyMsSqlServer() throws Exception {
     verifyDDL("SQLServer");
   }

   /**
    * To verify if DDL have certain characteristics:
    * <ul>
    *   <li>There are no unnamed constraints</li>
    *   <li>Alter tables are only used in exceptional cases</li>
    *   <li>Table and constraint name lenghts doesn't exceed 30 (a restriction imposed by Oracle)</li>
    * </ul>
    *
    */
   private void verifyDDL(String dbType) throws Exception {
     LOG.info("Checking DDL for {}", dbType);
     DDL ddl = DDLTestUtils.getDdl(dbType);
     printDDLMetrics(ddl);

     // check for unwanted alter tables
     Assert.assertEquals("Expected count of alter tables mismatch. Please include all constraint definitions in " +
             "the create table statement, only use alter table in exceptional cases, such as to work around a circular " +
             "FK dependency. Would another such case occur, please document it in the DDL's and adjust the " +
             "EXPECTED_ALTER_TABLE_COUNT in this test.",
         EXPECTED_ALTER_TABLE_COUNT,
         ddl.alterTables.size());

     // check for too long table/constraint names
     for (String tableName: ddl.tableNames()) {
       Assert.assertTrue("Table name exceeds the 30 character limit: " + tableName, tableName.length() <= 30);
     }
     for (Table table: ddl.tables.values()) {
       Assert.assertTrue("PK name exceeds the 30 character limit: " + table.primaryKey,
           !table.primaryKey.isPresent() || table.primaryKey.get().name().length() <= 30);
       for (Constraint constr: Sets.union(table.foreignKeys, table.uniqueConstraints)) {
         Assert.assertTrue("Constraint name exceeds the 30 character limit: " + constr, constr.name().length() <= 30);
       }
     }
     // check for unnamed PK's (skip quartz tables)
     for (Table table: ddl.tables.values()) {
       Assert.assertFalse("Unnamed PK exists for table: " + table.name,
           !table.name.startsWith("qrtz") && table.primaryKey.isPresent() && table.primaryKey.get().name().equals("<default>"));
       for (Constraint constr: Sets.union(table.foreignKeys, table.uniqueConstraints)) {
         Assert.assertTrue("Constraint name exceeds the 30 character limit: " + constr, constr.name().length() <= 30);
       }
     }

   }

   @Test
   public void testCompareDerby() throws Exception {
     compareAgainstPostgres("Derby");
   }

   @Test
   public void testCompareOracle() throws Exception {
     compareAgainstPostgres("Oracle");
   }

   @Test
   public void testCompareMySQL() throws Exception {
     compareAgainstPostgres("MySQL");
   }

   @Test
   public void testCompareSQLAnywhere() throws Exception {
     compareAgainstPostgres("SQLAnywhere");
   }

   @Test
   public void testCompareSQLServer() throws Exception  {
     compareAgainstPostgres("SQLServer");
   }

   static void compareAgainstPostgres(String dbType) throws Exception {
     LOG.info("Comparing {} against Postgres", dbType);
     DDL postgres = DDLTestUtils.getDdl("Postgres");
     DDL other = DDLTestUtils.getDdl(dbType);
     List<String> diffs = compareDdls(postgres, other);
     if (diffs.isEmpty()) {
       LOG.info("Compare OK.");
     }
     else {
       LOG.info("{} differences found:", diffs.size());
       for (String diff: diffs) { LOG.info(diff); }
       StringBuilder buffer = new StringBuilder();
       buffer.append("Found ").append(diffs.size()).append(" differences when comparing ").append(
           other).append(" against Postgres:").append(System.lineSeparator()).append(
               StringUtils.join(diffs, System.lineSeparator()));

       Assert.fail(buffer.toString());
     }
   }

   static void printDDLMetrics(DDL ddl) {
     LOG.info("DDL metrics for {}", ddl.dbType);
     int colCount = 0;
     int pkCount = 0;
     int fkCount = 0;
     int uqCount = 0;
     for (Table t: ddl.tables.values()) {
       colCount += t.columns.size();
       if (t.primaryKey.isPresent()) {
         pkCount ++;
       }
       fkCount += t.foreignKeys.size();
       uqCount += t.uniqueConstraints.size();
     }
     LOG.info("Found {} tables", ddl.tables.size());
     List<String> tableNames = new ArrayList<>();
     tableNames.addAll(ddl.tableNames());
     Collections.sort(tableNames);
     LOG.info("Table names: {}", Joiner.on(',').join(tableNames));
     LOG.info("Total number of Columns: {}", colCount);
     LOG.info("Total number of PK's: {}", pkCount);
     LOG.info("Total number of FK's: {}", fkCount);
     LOG.info("Total number of UQ's: {}", uqCount);
     LOG.info("Number of Alter table statements: {}", ddl.alterTables.size());

   }

   static List<String> compareDdls(DDL base, DDL other) {
     List<String> diffs = new ArrayList<>();

     if (!base.tableNames().equals(other.tableNames())) {
       Set<String> missingTables = Sets.difference(base.tableNames(), other.tableNames());
       if (!missingTables.isEmpty()) {
         diffs.add("Missing tables: " + Joiner.on(", ").join(missingTables));
       }

       Set<String> extraTables = Sets.difference(other.tableNames(), base.tableNames());
       if (!extraTables.isEmpty()) {
         diffs.add("Extra tables: " + Joiner.on(", ").join(extraTables));
       }
     }

     Set<String> commonTables = Sets.intersection(base.tableNames(), other.tableNames());
     for (String tableName: commonTables) {
       Table baseTable = base.tables.get(tableName);
       Table otherTable = other.tables.get(tableName);
       diffs.addAll(
           compareSets(String.format("Comparing columns of table %s.", tableName), baseTable.columns, otherTable.columns));
       diffs.addAll(
           DDLTests.compareConstraints(tableName, "FK", baseTable.foreignKeys, otherTable.foreignKeys, false));
       diffs.addAll(
           DDLTests.compareConstraints(tableName, "UQ", baseTable.uniqueConstraints, otherTable.uniqueConstraints, false));
       boolean comparePKName = !tableName.contains("qrtz"); // we are more lenient with quartz tables
       diffs.addAll(
           DDLTests.compareConstraints(tableName, "PK", toSet(baseTable.primaryKey), toSet(otherTable.primaryKey), comparePKName));
     }

     return diffs;
   }

   static <T> Set<T> toSet(Optional<T> arg) {
     return arg.isPresent() ? ImmutableSet.of(arg.get()) : ImmutableSet.of();
   }

   static <ContentType> List<String> compareSets(String message, Set<ContentType> base, Set<ContentType> other) {
     List<String> diffs = new ArrayList<>(2);
     Set<ContentType> missingItems = Sets.difference(base, other);
     if (!missingItems.isEmpty()) {
       diffs.add(message + " Missing items: " + Joiner.on(", ").join(missingItems));
     }
     Set<ContentType> extraItems = Sets.difference(other, base);
     if (!extraItems.isEmpty()) {
       diffs.add(message + " Extra items: " + Joiner.on(", ").join(extraItems));
     }
     return diffs;
   }


   static <ContentType> List<String> compareConstraints(String tableName,
                                              String constraintType,
                                              Set<? extends Constraint<ContentType>> base,
                                              Set<? extends Constraint<ContentType>> other,
                                              boolean compareConstraintNames) {
     List<String> diffs = new ArrayList<>();
     Map<ContentType, Constraint<ContentType>> baseByContent = Maps.newHashMap();
     Map<ContentType, Constraint<ContentType>> otherByContent = Maps.newHashMap();
     for (Constraint<ContentType> c: base) {
       baseByContent.put(c.content(), c);
     }
     for (Constraint<ContentType> c: other) {
       otherByContent.put(c.content(), c);
     }
     diffs.addAll(compareSets(String.format("Comparing %ss of table %s.", constraintType, tableName),
         baseByContent.keySet(),
         otherByContent.keySet()));

     Set<ContentType> common = Sets.intersection(baseByContent.keySet(), otherByContent.keySet());
     for (ContentType constrContent : common) {
       Constraint b = baseByContent.get(constrContent);
       Constraint o = otherByContent.get(constrContent);
       if (!b.name().equals(o.name())) {
         if (compareConstraintNames) {
           diffs.add(String.format("Constraint name mismatch for table %s: %s vs. %s", tableName, b, o));
         }
         else {
           LOG.info("Ignoring constraint name mismatch for table {}: {} vs. {}", tableName, b, o);
         }
       }
     }

     return diffs;
   }

 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing, software
	* distributed under the License is distributed on an "AS IS" BASIS,
	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	* See the License for the specific language governing permissions and
	* limitations under the License.
	*/
	package org.apache.ambari.server.orm.db;

	import java.util.ArrayList;
	import java.util.Collections;
	import java.util.List;
	import java.util.Map;
	import java.util.Set;

	import org.apache.commons.lang.StringUtils;
	import org.junit.Assert;
	import org.junit.Test;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;

	import com.google.common.base.Joiner;
	import com.google.common.base.Optional;
	import com.google.common.collect.ImmutableSet;
	import com.google.common.collect.Maps;
	import com.google.common.collect.Sets;


	/**
	* Test to check the sanity and consistence of DDL scripts for different SQL dialects.
	* (e.g. no unnamed constraints, the same tables with the same columns and constraints must exist)
	*/
	public class DDLTests {
	private static final Logger LOG = LoggerFactory.getLogger(DDLTestUtils.class);

	private static final int EXPECTED_ALTER_TABLE_COUNT = 1;

	@Test
	public void testVerifyDerby() throws Exception {
	verifyDDL("Derby");
	}

	@Test
	public void testVerifyPostgres() throws Exception {
	verifyDDL("Postgres");
	}

	@Test
	public void testVerifyMySQL() throws Exception {
	verifyDDL("MySQL");
	}

	@Test
	public void testVerifyOracle() throws Exception {
	verifyDDL("Oracle");
	}

	@Test
	public void testVerifySqlAnywhere() throws Exception {
	verifyDDL("SQLAnywhere");
	}

	@Test
	public void testVerifyMsSqlServer() throws Exception {
	verifyDDL("SQLServer");
	}

	/**
	* To verify if DDL have certain characteristics:
	* <ul>
	* <li>There are no unnamed constraints</li>
	* <li>Alter tables are only used in exceptional cases</li>
	* <li>Table and constraint name lenghts doesn't exceed 30 (a restriction imposed by Oracle)</li>
	* </ul>
	*
	*/
	private void verifyDDL(String dbType) throws Exception {
	LOG.info("Checking DDL for {}", dbType);
	DDL ddl = DDLTestUtils.getDdl(dbType);
	printDDLMetrics(ddl);

	// check for unwanted alter tables
	Assert.assertEquals("Expected count of alter tables mismatch. Please include all constraint definitions in " +
	"the create table statement, only use alter table in exceptional cases, such as to work around a circular " +
	"FK dependency. Would another such case occur, please document it in the DDL's and adjust the " +
	"EXPECTED_ALTER_TABLE_COUNT in this test.",
	EXPECTED_ALTER_TABLE_COUNT,
	ddl.alterTables.size());

	// check for too long table/constraint names
	for (String tableName: ddl.tableNames()) {
	Assert.assertTrue("Table name exceeds the 30 character limit: " + tableName, tableName.length() <= 30);
	}
	for (Table table: ddl.tables.values()) {
	Assert.assertTrue("PK name exceeds the 30 character limit: " + table.primaryKey,
	!table.primaryKey.isPresent() \|\| table.primaryKey.get().name().length() <= 30);
	for (Constraint constr: Sets.union(table.foreignKeys, table.uniqueConstraints)) {
	Assert.assertTrue("Constraint name exceeds the 30 character limit: " + constr, constr.name().length() <= 30);
	}
	}
	// check for unnamed PK's (skip quartz tables)
	for (Table table: ddl.tables.values()) {
	Assert.assertFalse("Unnamed PK exists for table: " + table.name,
	!table.name.startsWith("qrtz") && table.primaryKey.isPresent() && table.primaryKey.get().name().equals("<default>"));
	for (Constraint constr: Sets.union(table.foreignKeys, table.uniqueConstraints)) {
	Assert.assertTrue("Constraint name exceeds the 30 character limit: " + constr, constr.name().length() <= 30);
	}
	}

	}

	@Test
	public void testCompareDerby() throws Exception {
	compareAgainstPostgres("Derby");
	}

	@Test
	public void testCompareOracle() throws Exception {
	compareAgainstPostgres("Oracle");
	}

	@Test
	public void testCompareMySQL() throws Exception {
	compareAgainstPostgres("MySQL");
	}

	@Test
	public void testCompareSQLAnywhere() throws Exception {
	compareAgainstPostgres("SQLAnywhere");
	}

	@Test
	public void testCompareSQLServer() throws Exception {
	compareAgainstPostgres("SQLServer");
	}

	static void compareAgainstPostgres(String dbType) throws Exception {
	LOG.info("Comparing {} against Postgres", dbType);
	DDL postgres = DDLTestUtils.getDdl("Postgres");
	DDL other = DDLTestUtils.getDdl(dbType);
	List<String> diffs = compareDdls(postgres, other);
	if (diffs.isEmpty()) {
	LOG.info("Compare OK.");
	}
	else {
	LOG.info("{} differences found:", diffs.size());
	for (String diff: diffs) { LOG.info(diff); }
	StringBuilder buffer = new StringBuilder();
	buffer.append("Found ").append(diffs.size()).append(" differences when comparing ").append(
	other).append(" against Postgres:").append(System.lineSeparator()).append(
	StringUtils.join(diffs, System.lineSeparator()));

	Assert.fail(buffer.toString());
	}
	}

	static void printDDLMetrics(DDL ddl) {
	LOG.info("DDL metrics for {}", ddl.dbType);
	int colCount = 0;
	int pkCount = 0;
	int fkCount = 0;
	int uqCount = 0;
	for (Table t: ddl.tables.values()) {
	colCount += t.columns.size();
	if (t.primaryKey.isPresent()) {
	pkCount ++;
	}
	fkCount += t.foreignKeys.size();
	uqCount += t.uniqueConstraints.size();
	}
	LOG.info("Found {} tables", ddl.tables.size());
	List<String> tableNames = new ArrayList<>();
	tableNames.addAll(ddl.tableNames());
	Collections.sort(tableNames);
	LOG.info("Table names: {}", Joiner.on(',').join(tableNames));
	LOG.info("Total number of Columns: {}", colCount);
	LOG.info("Total number of PK's: {}", pkCount);
	LOG.info("Total number of FK's: {}", fkCount);
	LOG.info("Total number of UQ's: {}", uqCount);
	LOG.info("Number of Alter table statements: {}", ddl.alterTables.size());

	}

	static List<String> compareDdls(DDL base, DDL other) {
	List<String> diffs = new ArrayList<>();

	if (!base.tableNames().equals(other.tableNames())) {
	Set<String> missingTables = Sets.difference(base.tableNames(), other.tableNames());
	if (!missingTables.isEmpty()) {
	diffs.add("Missing tables: " + Joiner.on(", ").join(missingTables));
	}

	Set<String> extraTables = Sets.difference(other.tableNames(), base.tableNames());
	if (!extraTables.isEmpty()) {
	diffs.add("Extra tables: " + Joiner.on(", ").join(extraTables));
	}
	}

	Set<String> commonTables = Sets.intersection(base.tableNames(), other.tableNames());
	for (String tableName: commonTables) {
	Table baseTable = base.tables.get(tableName);
	Table otherTable = other.tables.get(tableName);
	diffs.addAll(
	compareSets(String.format("Comparing columns of table %s.", tableName), baseTable.columns, otherTable.columns));
	diffs.addAll(
	DDLTests.compareConstraints(tableName, "FK", baseTable.foreignKeys, otherTable.foreignKeys, false));
	diffs.addAll(
	DDLTests.compareConstraints(tableName, "UQ", baseTable.uniqueConstraints, otherTable.uniqueConstraints, false));
	boolean comparePKName = !tableName.contains("qrtz"); // we are more lenient with quartz tables
	diffs.addAll(
	DDLTests.compareConstraints(tableName, "PK", toSet(baseTable.primaryKey), toSet(otherTable.primaryKey), comparePKName));
	}

	return diffs;
	}

	static <T> Set<T> toSet(Optional<T> arg) {
	return arg.isPresent() ? ImmutableSet.of(arg.get()) : ImmutableSet.of();
	}

	static <ContentType> List<String> compareSets(String message, Set<ContentType> base, Set<ContentType> other) {
	List<String> diffs = new ArrayList<>(2);
	Set<ContentType> missingItems = Sets.difference(base, other);
	if (!missingItems.isEmpty()) {
	diffs.add(message + " Missing items: " + Joiner.on(", ").join(missingItems));
	}
	Set<ContentType> extraItems = Sets.difference(other, base);
	if (!extraItems.isEmpty()) {
	diffs.add(message + " Extra items: " + Joiner.on(", ").join(extraItems));
	}
	return diffs;
	}


	static <ContentType> List<String> compareConstraints(String tableName,
	String constraintType,
	Set<? extends Constraint<ContentType>> base,
	Set<? extends Constraint<ContentType>> other,
	boolean compareConstraintNames) {
	List<String> diffs = new ArrayList<>();
	Map<ContentType, Constraint<ContentType>> baseByContent = Maps.newHashMap();
	Map<ContentType, Constraint<ContentType>> otherByContent = Maps.newHashMap();
	for (Constraint<ContentType> c: base) {
	baseByContent.put(c.content(), c);
	}
	for (Constraint<ContentType> c: other) {
	otherByContent.put(c.content(), c);
	}
	diffs.addAll(compareSets(String.format("Comparing %ss of table %s.", constraintType, tableName),
	baseByContent.keySet(),
	otherByContent.keySet()));

	Set<ContentType> common = Sets.intersection(baseByContent.keySet(), otherByContent.keySet());
	for (ContentType constrContent : common) {
	Constraint b = baseByContent.get(constrContent);
	Constraint o = otherByContent.get(constrContent);
	if (!b.name().equals(o.name())) {
	if (compareConstraintNames) {
	diffs.add(String.format("Constraint name mismatch for table %s: %s vs. %s", tableName, b, o));
	}
	else {
	LOG.info("Ignoring constraint name mismatch for table {}: {} vs. {}", tableName, b, o);
	}
	}
	}

	return diffs;
	}

	}