package dataProfileWebSite; import java.io.File; import java.math.BigDecimal; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Calendar; import dataWarehousingTools.LogFile; import dataWarehousingTools.Configuration; import dataWarehousingTools.DashboardFile; public class ColumnProfile { public ColumnProfile ( Configuration configuration, Connection connection, LogFile logFile, int databaseLinkId, int tableProfileId, String databaseName, String schemaName, String tableName, int rowCount, Timestamp dateTimeStatisticsCollected ) { Statement statement = null; ResultSet resultSet = null; int columnProfileId; String columnName; String dataType; int dataLength; int nullCount; double percentPopulated; int distinctValueCount; long minInteger; BigDecimal minDecimal; Timestamp minDate; // String minString; long maxInteger; BigDecimal maxDecimal; Timestamp maxDate; // String maxString; boolean nullable; int maxLength; String tableProfileFileName; File checkExists; DashboardFile tableProfileFile; XmlElement xmlElement; Calendar today; DateFormat longDateFormat; DateFormat shortDateFormat; DecimalFormat integerFormat; DecimalFormat decimalFormat; DecimalFormat percentageFormat; String minDisplayValue; String minDisplayLong; String maxDisplayValue; String maxDisplayLong; today = Calendar.getInstance(); longDateFormat = new SimpleDateFormat("EEE dd-MMM-yyyy HH:mm:ss"); longDateFormat.format(today.getTime()); shortDateFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); shortDateFormat.format(today.getTime()); integerFormat = new DecimalFormat("###,###,###,###,###"); decimalFormat = new DecimalFormat("###,###,###,###.####"); percentageFormat = new DecimalFormat("##0.00"); minDisplayValue = new String(""); maxDisplayValue = new String(""); tableProfileFileName = new String(configuration.getHtmlFileLocation() + "TableProfile" + tableProfileId + ".htm"); checkExists = new File(tableProfileFileName); if (checkExists.exists()) return; tableProfileFile = new DashboardFile ( configuration.getHtmlFileLocation() + "TableProfile" + tableProfileId + ".htm" ); tableProfileFile.PutLine(""); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine ( " " ); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine("
DatabaseSchemaTableRow CountDate Statistics Collected
" + databaseName + "" + schemaName + "" + tableName + "" + integerFormat.format(rowCount) + "" + longDateFormat.format(dateTimeStatisticsCollected.getTime()) + "
"); xmlElement = new XmlElement ( 6, "a", "href=\"" + "index.html\"", "Back to List of Databases" ); tableProfileFile.PutLine("

" + xmlElement.getXmlElement() + "

"); xmlElement = new XmlElement ( 6, "a", "href=\"" + "Schema" + databaseLinkId + ".htm\"", "Back to List of Tables" ); tableProfileFile.PutLine("

" + xmlElement.getXmlElement() + "

"); xmlElement = new XmlElement(2, "title", "", "Column Profiles for Table: " + tableName); tableProfileFile.PutLine(xmlElement.getXmlElement()); xmlElement = new XmlElement(4, "h4", "", "Published: " + longDateFormat.format(today.getTime()) + " GMT"); tableProfileFile.PutLine(xmlElement.getXmlElement()); tableProfileFile.PutLine("
"); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); try { statement = connection.createStatement(); } catch(SQLException sqle) { logFile.logMessage(sqle.toString()); } try { resultSet = statement.executeQuery ( "select " + "id as column_profile_id, " + "table_profile_id, " + "date_time_statistics_collected, " + "column_name, " + "data_type, " + "data_length, " + "null_count, " + "unique_value_count as distinct_value_count, " + "min_number, " + "min_date, " + "min_string, " + "max_number, " + "max_date, " + "max_string, " + "nullable, " + "max_length " + "from " + "column_profile " + "where " + "table_profile_id = " + tableProfileId + "order by " + "column_name" ); } catch(SQLException sqle) { logFile.logMessage(sqle.toString()); } try { while (resultSet.next()) { columnProfileId = resultSet.getInt("column_profile_id"); dateTimeStatisticsCollected = resultSet.getTimestamp("date_time_statistics_collected"); columnName = new String(resultSet.getString("column_name")); dataType = new String(resultSet.getString("data_type")); dataLength = resultSet.getInt("data_length"); nullCount = resultSet.getInt("null_count"); distinctValueCount = resultSet.getInt("distinct_value_count"); minDisplayValue = new String(""); minDisplayLong = new String(""); maxDisplayValue = new String(""); maxDisplayLong = new String(""); if ( dataType.equalsIgnoreCase("varchar") || dataType.equalsIgnoreCase("nvarchar") || dataType.equalsIgnoreCase("text") || dataType.equalsIgnoreCase("char") || dataType.equalsIgnoreCase("nchar") ) { try { minDisplayValue = new String(resultSet.getString("min_string")); } catch (NullPointerException npe) { minDisplayValue = new String("Missing"); } if (resultSet.wasNull()) { minDisplayValue = new String("Null"); } if (minDisplayValue.length() > 24) { minDisplayLong = new String(minDisplayValue); minDisplayValue = new String(minDisplayLong.substring(0, 24) + "…"); } } else if ( dataType.equalsIgnoreCase("int4") || dataType.equalsIgnoreCase("int2") || dataType.equalsIgnoreCase("integer") || dataType.equalsIgnoreCase("bigint") || dataType.equalsIgnoreCase("smallint") || dataType.equalsIgnoreCase("tinyint") ) { minInteger = resultSet.getLong("min_number"); if (resultSet.wasNull()) { minDisplayValue = new String("Null"); } else { minDisplayValue = new String(integerFormat.format(minInteger)); } } else if ( dataType.equalsIgnoreCase("decimal") || dataType.equalsIgnoreCase("numeric") || dataType.equalsIgnoreCase("float") || dataType.equalsIgnoreCase("double") ) { minDecimal = resultSet.getBigDecimal("min_number"); if (resultSet.wasNull()) { minDisplayValue = new String("Null"); } else { minDisplayValue = new String(decimalFormat.format(minDecimal)); } } else if (dataType.equalsIgnoreCase("date") || dataType.equalsIgnoreCase("timestamp")) { minDate = resultSet.getTimestamp("min_date"); if (resultSet.wasNull()) { minDisplayValue = new String("Null"); } else { minDisplayValue = new String(shortDateFormat.format(minDate)); } } else if (dataType.equals("bool")) { minDisplayValue = new String("N/A"); } if ( dataType.equalsIgnoreCase("varchar") || dataType.equalsIgnoreCase("nvarchar") || dataType.equalsIgnoreCase("text") || dataType.equalsIgnoreCase("char") || dataType.equalsIgnoreCase("nchar") ) { try { maxDisplayValue = new String(resultSet.getString("max_string")); } catch (NullPointerException npe) { maxDisplayValue = new String("Missing"); } if (resultSet.wasNull()) { maxDisplayValue = new String("Null"); } if (maxDisplayValue.length() > 24) { maxDisplayLong = new String(maxDisplayValue); maxDisplayValue = new String(maxDisplayLong.substring(0, 24) + "…"); } } else if ( dataType.equalsIgnoreCase("int4") || dataType.equalsIgnoreCase("int2") || dataType.equalsIgnoreCase("integer") || dataType.equalsIgnoreCase("bigint") || dataType.equalsIgnoreCase("smallint") || dataType.equalsIgnoreCase("tinyint") ) { maxInteger = resultSet.getLong("max_number"); if (resultSet.wasNull()) { maxDisplayValue = new String("Null"); } else { maxDisplayValue = new String(integerFormat.format(maxInteger)); } } else if ( dataType.equalsIgnoreCase("decimal") || dataType.equalsIgnoreCase("numeric") || dataType.equalsIgnoreCase("float") || dataType.equalsIgnoreCase("double") ) { maxDecimal = resultSet.getBigDecimal("max_number"); if (resultSet.wasNull()) { maxDisplayValue = new String("Null"); } else { maxDisplayValue = new String(decimalFormat.format(maxDecimal)); } } else if (dataType.equalsIgnoreCase("date") || dataType.equalsIgnoreCase("timestamp")) { maxDate = resultSet.getTimestamp("max_date"); if (resultSet.wasNull()) { maxDisplayValue = new String("Null"); } else { maxDisplayValue = new String(shortDateFormat.format(maxDate)); } } else if (dataType.equals("bool")) { maxDisplayValue = new String("N/A"); } nullable = resultSet.getBoolean("nullable"); maxLength = resultSet.getInt("max_length"); if (rowCount > 0) percentPopulated = (double)(rowCount - nullCount) * 100 / rowCount; else percentPopulated = 0.00; tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); if (nullable) { tableProfileFile.PutLine(" "); } else { tableProfileFile.PutLine(" "); } tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); // check for PANs and mask // tableProfileFile.PutLine(" "); // tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); tableProfileFile.PutLine(" "); if ( dataType.equalsIgnoreCase("varchar") || dataType.equalsIgnoreCase("nvarchar") || dataType.equalsIgnoreCase("text") || dataType.equalsIgnoreCase("char") || dataType.equalsIgnoreCase("nchar") ) tableProfileFile.PutLine(" "); else tableProfileFile.PutLine(" "); // if (distinctValueCount == 1) // { // tableProfileFile.PutLine(" "); // } // else if (distinctValueCount == rowCount) // { // tableProfileFile.PutLine(" "); // } // else // { xmlElement = new XmlElement ( 6, "a", "href=\"" + "Frequency" + columnProfileId + ".htm\"", "Frequency" ); tableProfileFile.PutLine ( " " ); new Frequencies ( configuration, connection, logFile, databaseLinkId, databaseName, schemaName, tableName, rowCount, distinctValueCount, tableProfileId, columnProfileId, columnName, dateTimeStatisticsCollected ); // } if ( dataType.equalsIgnoreCase("varchar") || dataType.equalsIgnoreCase("nvarchar") || dataType.equalsIgnoreCase("text") || dataType.equalsIgnoreCase("char") || dataType.equalsIgnoreCase("nchar") ) { xmlElement = new XmlElement ( 6, "a", "href=\"" + "Pattern" + columnProfileId + ".htm\"", "Pattern" ); tableProfileFile.PutLine ( " " ); tableProfileFile.PutLine(" "); new Patterns ( configuration, connection, logFile, databaseLinkId, databaseName, schemaName, tableName, rowCount, tableProfileId, columnProfileId, columnName, dateTimeStatisticsCollected ); } else { tableProfileFile.PutLine(" "); } } } catch (SQLException sqle) { logFile.logMessage(sqle.toString()); } tableProfileFile.PutLine(" "); tableProfileFile.PutLine("
Column NameData TypeDefined LengthNulls AllowedNull CountPercentage PopulatedDistinct Value CountMinimum ValueMaximum ValueMax Actual LengthLink to FrequenciesLink to Patterns
" + columnName + "" + dataType.toLowerCase() + "" + integerFormat.format(dataLength) + "YesNo" + integerFormat.format(nullCount) + "" + percentageFormat.format(percentPopulated) + "%" + integerFormat.format(distinctValueCount) + "" + maskIfPan.mask(minDisplayValue) + "" + maskIfPan.mask(maxDisplayValue) + "" + minDisplayValue + "" + maxDisplayValue + "" + integerFormat.format(maxLength) + "Not a stringAll values the sameEvery value distinct" + xmlElement.getXmlElement() + "" + xmlElement.getXmlElement() + "
Not a string
"); tableProfileFile.PutLine("
"); xmlElement = new XmlElement(4, "h4", "", "Published: " + longDateFormat.format(today.getTime()) + " GMT"); tableProfileFile.PutLine(xmlElement.getXmlElement()); tableProfileFile.PutLine(" "); tableProfileFile.Close(); } }