package com.vblinov.tester;
import org.dbunit.Assertion;
import org.dbunit.IDatabaseTester;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.assertion.DiffCollectingFailureHandler;
import org.dbunit.assertion.Difference;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.ext.mysql.MySqlDataTypeFactory;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
/**
* Test class for DDL and DML operations
*/
public class DDLTest {
/**
* Test logger
*/
private static final Logger log = LoggerFactory.getLogger(DDLTest.class);
/**
* SQL query to fetch all data from specified table
*/
private static final String SQL_SELECT_ALL = "SELECT * FROM %s";
/**
* SQL query to fetch all table metadata
*/
private static final String SQL_SELECT_METADATA = "SELECT column_name, COALESCE(column_default, 'null') AS DEF, is_nullable, data_type, " +
"COALESCE(character_maximum_length, 'null') AS CML FROM information_schema.columns " +
"WHERE table_schema = '%s' AND table_name = '%s'";
/**
* SQL query to fetch all table constraints
*/
private static final String SQL_SELECT_CONSTRAINTS = "SELECT constraint_name, constraint_type FROM information_schema.table_constraints " +
"WHERE table_schema = '%s' AND table_name = '%s'";
private static IDataSet expectedData;
private static IDatabaseConnection connection;
private static String schemaName;
/**
* Load expected result data sets from dataSet.xml
*
* @throws Exception in case of i/o of file format errors
*/
@BeforeClass
public static void initDataSet() throws Exception {
if (null == expectedData) {
expectedData = new FlatXmlDataSet(DDLTest.class.getResourceAsStream(
"/dataSet.xml"));
}
}
/**
* Init connection to MySQL server
*
* @throws Exception in case of SQL exceptions
*/
@BeforeClass
public static void initConnection() throws Exception {
if (null == connection) {
IDatabaseTester databaseTester = new PropertiesBasedJdbcDatabaseTester();
connection = databaseTester.getConnection();
DatabaseConfig dbConfig = connection.getConfig();
dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
schemaName = System.getProperty("schemaName");
}
}
/**
* Release connection
*
* @throws Exception in case of SQL Exception
*/
@AfterClass
public static void tearDown() throws Exception {
connection.close();
}
@Test
public void testMakersTable() throws Exception {
checkTableData("makers");
}
@Test
public void testProductTable() throws Exception {
checkTableData("product");
}
@Test
public void testPrinterTable() throws Exception {
checkTableData("printer");
}
@Test
public void testLaptopTable() throws Exception {
checkTableData("laptop");
}
@Test
public void testPCTable() throws Exception {
checkTableData("pc");
}
@Test
public void testPrinterTypeTable() throws Exception {
checkTableData("printer_type");
}
@Test
public void testMakersMetadata() throws Exception {
checkTableMetadata("makers");
}
@Test
public void testMakersConstraints() throws Exception {
checkTableConstraints("makers");
}
@Test
public void testProductMetadata() throws Exception {
checkTableMetadata("product");
}
@Test
public void testProductConstraints() throws Exception {
checkTableConstraints("product");
}
@Test
public void testPCMetadata() throws Exception {
checkTableMetadata("pc");
}
@Test
public void testPCConstraints() throws Exception {
checkTableConstraints("pc");
}
@Test
public void testLaptopMetadata() throws Exception {
checkTableMetadata("laptop");
}
@Test
public void testLaptopConstraints() throws Exception {
checkTableConstraints("laptop");
}
@Test
public void testPrinterTypeMetadata() throws Exception {
checkTableMetadata("printer_type");
}
@Test
public void testPrinterTypeConstraints() throws Exception {
checkTableConstraints("printer_type");
}
@Test
public void testPrinterMetadata() throws Exception {
checkTableMetadata("printer");
}
@Test
public void testPrinterConstraints() throws Exception {
checkTableConstraints("printer");
}
/**
* Checks table data with expected values
*
* @param tableName name of table
* @throws Exception in case of JDBC or DbUnit errors
*/
private void checkTableData(String tableName) throws Exception {
ITable actualTable =
new SortedTable(connection.createQueryTable(tableName, String.format(SQL_SELECT_ALL, tableName)));
ITable expectedTable = new SortedTable(expectedData.getTable(tableName));
logDifferences(expectedTable, actualTable);
}
/**
* Convenience method for checking table metadata
*
* @param tableName name of table
* @throws Exception in case of JDBC or DbUnit errors
*/
private void checkTableMetadata(String tableName) throws Exception {
checkTableMetadata(SQL_SELECT_METADATA, tableName, "_metadata");
}
/**
* Convenience method for checking table constraints
*
* @param tableName name of table
* @throws Exception in case of JDBC or DbUnit errors
*/
private void checkTableConstraints(String tableName) throws Exception {
checkTableMetadata(SQL_SELECT_CONSTRAINTS, tableName, "_constraints");
}
/**
* Checks table mata data
*
* @param query metadata query
* @param tableName name of table
* @throws Exception in case DBUnit or SQL exceptions
*/
private void checkTableMetadata(String query, String tableName, String suffix) throws Exception {
ITable actualTable = connection.createQueryTable(tableName, String.format(query, schemaName, tableName));
ITable expectedTable = expectedData.getTable(tableName + suffix);
logDifferences(expectedTable, actualTable);
}
/**
* Asserting and logging differences
*
* @param expectedTable expected dataset
* @param actualTable actual dataset (returned from database)
* @throws Exception in case of JDBC or DbUnit errors
*/
private void logDifferences(ITable expectedTable, ITable actualTable) throws Exception {
DiffCollectingFailureHandler failureHandler = new DiffCollectingFailureHandler();
Assertion.assertEquals(expectedTable, actualTable, failureHandler);
List differenceList = failureHandler.getDiffList();
if (!differenceList.isEmpty()) {
for (Object obj : differenceList) {
Difference difference = (Difference) obj;
log.info("Column={}, expected={}, actual={},", difference.getColumnName(),
difference.getExpectedValue(), difference.getActualValue());
}
Assert.fail("Data for table " + actualTable.getTableMetaData().getTableName() + " doesn't match expected values");
}
}
}