# SchemaComparator.java

    /*
    ** Module   : SchemaComparator.java
    ** Abstract : Compares database schema to a known last state using JDBC.
    **
    ** Copyright (c) 2022-2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 BS  20221103 Created initial version.
    ** 002 BS  20230331 Added support for extent fields and sequences.
    ** 003 BS  20230419 Excluded tables functionality.
    ** 004 OM  20240412 Added sort direction for index components.
    ** 005 OM  20240516 Skip tables marked with 'Skip: true'.
    **     OM  20240517 Throw StopConditionException() in the mutable database failed validation.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.orm.schema;

    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.orm.schema.element.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.util.*;
    import org.w3c.dom.*;
    import org.w3c.dom.Element;
    import org.xml.sax.*;
    import javax.xml.parsers.*;
    import java.io.*;
    import java.sql.*;
    import java.time.*;
    import java.util.*;
    import java.util.regex.*;
    import java.util.stream.*;

    /**
     * JDBC schema comparator. Compares current DB schema state with last known state. Last known state may be
     * provided.
     * <p>
     * Usage:
     * <pre>
     *    SchemaComparator comparator = SchemaComparator.getInstanceExistingState(database);
     *    ComparisonResult result = comparator.compareWithPrevious();
     *    if (!result.isSame())
     *    {
     *       // Use changes provided by the result
     *    }
     * </pre>
     * <p>
     * In case it is required to reset the state (server restart, etc.) - this overwrites the last known state
     * file:
     * <pre>
     *    SchemaComparator comparator = SchemaComparator.saveInitialState(database);
     * </pre>
     */
    public class SchemaComparator
    {
       /** Path to file into which last known database state is serialized. */
       private static final File LAST_KNOWN_STATE_FILE = new File("/tmp/last_known_state.xml");
       private static final boolean WRITE_KNOWN_STATE_FILE = false;

       private static final Pattern PATTERN_EXTENT_TABLE = Pattern.compile("(.+)__([0-9]+)");
       private static final Pattern PATTERN_EXTENT_COLUMN = Pattern.compile("(.+)_1");
       private static final Pattern COMMENT_EXTENT_COLUMN = Pattern.compile("\\s*EXTENT:?\\s*([0-9]+)",
                                                                            Pattern.CASE_INSENSITIVE);

       // column_1, column_2, column_3 is the minimum pattern to be handled as an extent automatically
       private static final int MINIMUM_EXTENT_COLUMNS = 9999;

       private static final String XML_TABLES = "tables";
       private static final String XML_TABLE = "table";
       private static final String XML_COLUMNS = "columns";
       private static final String XML_COLUMN = "column";
       private static final String XML_INDEX = "index";
       private static final String XML_ASCENDING = "ascending";
       private static final String XML_SEQUENCES = "sequences";
       private static final String XML_SEQUENCE = "sequence";
       private static final String XML_NAME = "name";
       private static final String XML_TYPE = "type";
       private static final String XML_PRECISION = "precision";
       private static final String XML_NULLABLE = "nullable";
       private static final String XML_PRIMARY = "primary";
       private static final String XML_UNIQUE = "unique";
       private static final String XML_DEFAULT = "default";
       private static final String XML_COMMENT = "comment";

       /** Database reference - used for all connections; can't be changed in one instance of the comparator. */
       private final Database database;

       /** In-memory storage of the last known state. This is to prevent extensive file operations. */
       private static DatabaseState lastKnownState;

       /**
        * List of lower-case names of tables that should not be used in schema comparison. They will be simply
        * ignored when loading tables from the database.
        *
        * NOTE: This configuration may be moved to a file if it becomes too large.
        */
       private static final Collection<String> EXCLUDED_TABLES = new HashSet<>();

       static
       {
          EXCLUDED_TABLES.add("meta_user");
       }

       /**
        * Get an instance of the Comparator, load the current database state for use in comparison.
        * This overwrites the last known state file. Also, it's a blocking operation.
        *
        * @param   database
        *          Database to which to connect.
        *
        * @return  Comparator instance.
        */
       public static SchemaComparator saveInitialState(Database database)
       throws PersistenceException
       {
          SchemaComparator comparator = new SchemaComparator(database);
          comparator.compare(new DatabaseState(Instant.now()));

          return comparator;
       }

       /**
        * Get an instance of the Comparator, assuming the current database state file exists (it will be loaded
        * on demand).
        *
        * @param   database
        *          Database to which to connect.
        *
        * @return  Comparator instance.
        */
       public static SchemaComparator getInstanceExistingState(Database database)
       {
          return new SchemaComparator(database);
       }

       /**
        * Constructor
        *
        * @param   database
        *          Database reference.
        */
       private SchemaComparator(Database database)
       {
          this.database = database;
       }

       /**
        * Compare current state with an empty database state, effectively reporting everything found in the scan
        * as new.
        *
        * @return  {@link ComparisonResult} instance.
        *
        * @throws  PersistenceException
        *          if there is an error performing the JDBC scan.
        */
       public ComparisonResult getFreshResult()
       throws PersistenceException
       {
          return compare(new DatabaseState(Instant.now()));
       }

       /**
        * Compare current state with last known state.
        *
        * @return  {@link ComparisonResult} instance.
        *
        * @throws  PersistenceException
        *          if there is an error performing the JDBC scan.
        */
       public ComparisonResult compareWithPrevious()
       throws PersistenceException
       {
          return compare(getLastKnownState());
       }

       /**
        * Compare current state with given state.
        *
        * @param   previousState
        *          Previous known state for comparison.
        *
        * @return  ComparisonResult instance
        *
        * @throws  PersistenceException
        *          if there is an error performing the JDBC scan.
        */
       public ComparisonResult compare(DatabaseState previousState)
       throws PersistenceException
       {
          try (Session session = new Session(database))
          {
             Connection conn = session.getConnection();
             ComparisonResult comparisonResult = new ComparisonResult();

             getAndCompareTables(conn, previousState, comparisonResult);
             getAndCompareColumns(conn, previousState, comparisonResult);
             getAndCompareIndexes(conn, previousState, comparisonResult);
             getAndCompareSequences(conn, previousState, comparisonResult);

             if (!comparisonResult.isSame())
             {
                if (WRITE_KNOWN_STATE_FILE)
                {
                   saveLastKnownState(comparisonResult.getState());
                }
                else
                {
                   lastKnownState = comparisonResult.getState();
                }
             }

             return comparisonResult;
          }
          catch (PersistenceException | SQLException e)
          {
             throw new PersistenceException("Connection failed during comparison", e);
          }
       }

       /**
        * Load last persisted state. This is to be used on application start.
        *
        * @return  Database state.
        *
        * @throws  PersistenceException
        *          if there was an error reading the last known state, other than a file not found error
        */
       synchronized DatabaseState getLastKnownState()
       throws PersistenceException
       {
          if (lastKnownState == null)
          {
             lastKnownState = new DatabaseState(Instant.now());
             try (FileInputStream fis = new FileInputStream(LAST_KNOWN_STATE_FILE))
             {
                Document dom = XmlHelper.parse(fis);
                NodeList tableNodes = dom.getElementsByTagName(XML_TABLE);
                for (int i = 0; i < tableNodes.getLength(); i++)
                {
                   Node tableNode = tableNodes.item(i);
                   Table table = new Table(
                      database,
                      XmlHelper.getAttribute(tableNode, XML_NAME),
                      XmlHelper.getAttribute(tableNode, XML_COMMENT)
                   );
                   lastKnownState.getTables().add(table);

                   NodeList childNodes = tableNode.getChildNodes();
                   for (int j = 0; j < childNodes.getLength(); j++)
                   {
                      Node node = childNodes.item(j);
                      if (node.getNodeName().equalsIgnoreCase(XML_COLUMN))
                      {
                         Attribute attribute = new Attribute(
                            XmlHelper.getAttribute(node, XML_NAME),
                            table,
                            XmlHelper.getAttribute(node, XML_TYPE),
                            XmlHelper.getAttribute(node, XML_PRECISION),
                            XmlHelper.getAttributeBoolean(node, XML_NULLABLE, false),
                            XmlHelper.getAttribute(node, XML_DEFAULT),
                            0,
                            XmlHelper.getAttribute(node, XML_COMMENT));
                         table.getAttributes().add(attribute);
                      }
                      else if (node.getNodeName().equalsIgnoreCase(XML_INDEX))
                      {
                         Collection<Tuple<String, Boolean>> attributeList = new ArrayList<>();
                         NodeList indexChildren = node.getChildNodes();
                         for (int k = 0; k < indexChildren.getLength(); k++)
                         {
                            Node indexChild = indexChildren.item(k);
                            if (indexChild.getNodeName().equalsIgnoreCase(XML_COLUMNS))
                            {
                               NodeList components = indexChild.getChildNodes();
                               for (int l = 0; l < components.getLength(); l++)
                               {
                                  Node component = components.item(l);
                                  if (component.getNodeName().equalsIgnoreCase(XML_COLUMN))
                                  {
                                     attributeList.add(new Tuple<>(
                                           XmlHelper.getAttribute(component, XML_NAME),
                                           XmlHelper.getAttributeBoolean(component, XML_ASCENDING, false)));
                                  }
                               }
                               break; // should be exactly one
                            }
                         }

                         Index index = new Index(
                            XmlHelper.getAttribute(node, XML_NAME),
                            table,
                            attributeList,
                            XmlHelper.getAttributeBoolean(node, XML_UNIQUE, false),
                            XmlHelper.getAttributeBoolean(node, XML_PRIMARY, false),
                            XmlHelper.getAttribute(node, XML_COMMENT));
                         table.getIndexes().add(index);
                      }
                   }
                }
                NodeList sequenceNodes = dom.getElementsByTagName(XML_SEQUENCES);
                for (int i = 0; i < sequenceNodes.getLength(); i++)
                {
                   Node sequenceNode = sequenceNodes.item(i);
                   Sequence sequence = new Sequence(
                      database,
                      XmlHelper.getAttribute(sequenceNode, XML_NAME),
                      XmlHelper.getAttribute(sequenceNode, XML_COMMENT)
                   );
                   lastKnownState.getSequences().add(sequence);
                }
             }
             catch (FileNotFoundException exc)
             {
                // assume this means no previous state exists and we must create a bootstrap database state;
                // empty database state will be returned
             }
             catch (IOException | ParserConfigurationException | SAXException exc)
             {
                throw new PersistenceException("Error reading last known schema state", exc);
             }
          }

          return lastKnownState;
       }

       /**
        * Persist the database state. This is to be used after every successful state comparison.
        *
        * @param  newState
        *         Database state to persist.
        */
       synchronized void saveLastKnownState(DatabaseState newState)
       throws PersistenceException
       {
          lastKnownState = newState;
          try (FileOutputStream fos = new FileOutputStream(LAST_KNOWN_STATE_FILE))
          {
             Document dom = XmlHelper.newDocument();
             Element tableRoot = dom.createElement(XML_TABLES);
             for (Table table : lastKnownState.getTables())
             {
                Element tableNode = dom.createElement(XML_TABLE);
                XmlHelper.setAttributeOptional(tableNode, XML_NAME, table.getName());
                XmlHelper.setAttributeOptional(tableNode, XML_COMMENT, table.getComment());
                tableRoot.appendChild(tableNode);

                for (Attribute attribute : table.getAttributes())
                {
                   Element attrNode = dom.createElement(XML_COLUMN);
                   XmlHelper.setAttributeOptional(attrNode, XML_NAME, attribute.getName());
                   XmlHelper.setAttributeOptional(attrNode, XML_TYPE, attribute.getType());
                   XmlHelper.setAttributeOptional(attrNode, XML_PRECISION, attribute.getPrecision());
                   XmlHelper.setAttribute(attrNode, XML_NULLABLE, attribute.getNullable());
                   XmlHelper.setAttributeOptional(attrNode, XML_DEFAULT, attribute.getDefaultValue());
                   XmlHelper.setAttributeOptional(attrNode, XML_COMMENT, attribute.getComment());
                   tableNode.appendChild(attrNode);
                }

                for (Index index : table.getIndexes())
                {
                   Element indexNode = dom.createElement(XML_INDEX);
                   XmlHelper.setAttributeOptional(indexNode, XML_NAME, index.getName());
                   XmlHelper.setAttribute(indexNode, XML_UNIQUE, index.getUnique());
                   XmlHelper.setAttribute(indexNode, XML_PRIMARY, index.getPrimary());
                   XmlHelper.setAttributeOptional(indexNode, XML_COMMENT, index.getComment());

                   Element columnsNode = dom.createElement(XML_COLUMNS);
                   for (Tuple<String, Boolean> column : index.getColumns())
                   {
                      Element indexComp = dom.createElement(XML_COLUMN);
                      XmlHelper.setAttribute(indexComp, XML_NAME, column.getKey());
                      XmlHelper.setAttribute(indexComp, XML_ASCENDING, column.getValue());
                      columnsNode.appendChild(indexComp);
                   }
                   indexNode.appendChild(columnsNode);

                   tableNode.appendChild(indexNode);
                }
             }
             dom.appendChild(tableRoot);

             Element sequenceRoot = dom.createElement(XML_SEQUENCES);
             for (Sequence sequence : lastKnownState.getSequences())
             {
                Element sequenceNode = dom.createElement(XML_SEQUENCE);
                XmlHelper.setAttributeOptional(sequenceNode, XML_NAME, sequence.getName());
                XmlHelper.setAttributeOptional(sequenceNode, XML_COMMENT, sequence.getComment());
                sequenceRoot.appendChild(sequenceNode);
             }
             dom.appendChild(sequenceRoot);

             XmlHelper.write(dom, fos);
          }
          catch (IOException | ParserConfigurationException exc)
          {
             throw new PersistenceException("Failed to write last known schema state", exc);
          }
       }

       /**
        * Load information on tables from the provided connection, put the state into result. If previous state is
        * provided we can compare current state with the previous one and report any differences into result.
        *
        * @param   conn
        *          Already initiated connection.
        * @param   previousState
        *          Last known state.
        * @param   comparisonResult
        *          Instance of result to be filled in.
        *
        * @throws  SQLException
        *          on unsuccessful calls to DB.
        */
       private void getAndCompareTables(Connection conn,
                                        DatabaseState previousState,
                                        ComparisonResult comparisonResult)
       throws SQLException
       {
          Dialect dialect = Dialect.getDialect(database);
          // Load tables from DB
          ResultSet tables = conn.getMetaData()
                                 .getTables(dialect.getCatalogPattern(database),
                                            dialect.getSchemaPattern(database),
                                            "%",
                                            new String[] {"TABLE"});

          // Put all data into the new State
          DatabaseState currentState = comparisonResult.getState();
          while (tables.next())
          {
             String tableName = tables.getString("TABLE_NAME");
             if (!EXCLUDED_TABLES.contains(tableName.toLowerCase()))
             {
                String remarks = tables.getString("REMARKS");
                Map<String, String> attrs = SchemaCheck.processComment(remarks);
                if (!Boolean.parseBoolean(SchemaCheck.getTokenValue(attrs, SchemaCheck.SKIP, "false")))
                {
                   currentState.getTables().add(new Table(database, tableName, remarks));
                }
             }
          }

          // Compare if old State is available
          if (previousState != null)
          {
             // Detect added
             for (Table table : currentState.getTables())
             {
                if (!previousState.getTables().contains(table))
                {
                   comparisonResult.getDifferences()
                                   .add(new Difference(table, null, table));
                }
             }

             // Detect removed
             for (Table table : previousState.getTables())
             {
                if (!currentState.getTables().contains(table))
                {
                   comparisonResult.getDifferences()
                                   .add(new Difference(table, table, null));
                }
             }
          }
       }

       /**
        * Load information on columns from the provided connection, put the state into result. If previous state
        * is provided, we can compare current state with the previous one and report any differences into result.
        *
        * @param   conn
        *          Already initiated connection.
        * @param   previousState
        *          Last known state.
        * @param   comparisonResult
        *          Instance of result to be filled in.
        *
        * @throws  SQLException
        *          on unsuccessful calls to DB.
        */
       private void getAndCompareColumns(Connection conn,
                                         DatabaseState previousState,
                                         ComparisonResult comparisonResult)
       throws SQLException
       {
          Dialect dialect = Dialect.getDialect(database);
          DatabaseState currentState = comparisonResult.getState();
          for (Table table : comparisonResult.getState().getTables())
          {
             // Load columns from DB
             ResultSet columns = conn.getMetaData()
                                     .getColumns(dialect.getCatalogPattern(database),
                                                 dialect.getSchemaPattern(database),
                                                 table.getName(),
                                                 null);

             // Put all data into the new State
             while (columns.next())
             {
                String columnName = columns.getString("COLUMN_NAME");
                String columnType = columns.getString("TYPE_NAME");
                String precision = columns.getString("DECIMAL_DIGITS");
                boolean nullable = columns.getBoolean("NULLABLE");
                String defaultValue = columns.getString("COLUMN_DEF");
                String comment = columns.getString("REMARKS");

                if ("current_timestamp(3)".equalsIgnoreCase(defaultValue)) {
                   defaultValue = "now";
                }

                table.getAttributes().add(new Attribute(columnName,
                                                        table,
                                                        columnType,
                                                        precision,
                                                        nullable,
                                                        defaultValue,
                                                        0,
                                                        comment));
             }
          }

          // Compare if old State is available
          if (previousState != null)
          {
             for (Table currentTable : currentState.getTables())
             {
                if (previousState.getTables().contains(currentTable))
                {
                   // Table already existed, let's check for column differences
                   previousState.getTables().stream()
                                .filter(t -> t.equals(currentTable))
                                .findFirst()
                                .ifPresent(previousTable ->
                                   {
                                      // Detect added or changed columns
                                      for (Attribute currentAttribute : currentTable.getAttributes())
                                      {
                                         if (!previousTable.getAttributes().contains(currentAttribute))
                                         {
                                            // Added column
                                            comparisonResult.getDifferences()
                                                            .add(new Difference(currentTable,
                                                              null,
                                                              currentAttribute));
                                         }
                                         else
                                         {
                                            // Column already existed, let's check for differences
                                            previousTable.getAttributes().stream()
                                               .filter(prevA ->
                                                       prevA.equals(currentAttribute))
                                               .filter(prevA ->
                                                       !prevA.getType()
                                                             .equals(currentAttribute.getType()))
                                               .filter(prevA ->
                                                       !prevA.getPrecision()
                                                             .equals(currentAttribute.getPrecision()))
                                               .filter(prevA ->
                                                       prevA.getNullable() != currentAttribute.getNullable())
                                               .filter(prevA ->
                                                       !prevA.getDefaultValue()
                                                             .equals(currentAttribute.getDefaultValue()))
                                               .filter(prevA ->
                                                       !prevA.getComment().equals(currentAttribute.getComment()))
                                               .findFirst()
                                               .ifPresent(previousAttribute ->
                                                  comparisonResult.getDifferences()
                                                                  .add(new Difference(currentTable,
                                                                                      previousAttribute,
                                                                                      currentAttribute)));
                                         }
                                      }

                                      // Detect removed columns
                                      for (Attribute previousAttribute : previousTable.getAttributes())
                                      {
                                         if (!currentTable.getAttributes().contains(previousAttribute))
                                         {
                                            comparisonResult.getDifferences()
                                                            .add(new Difference(currentTable,
                                                                                previousAttribute,
                                                                                null));
                                         }
                                      }
                                   });
                }
             }
          }
       }

       /**
        * Load information on indexes from the provided connection, put the state into result. If previous state
        * is provided, we can compare current state with the previous one and report any differences into result.
        *
        * @param   conn
        *          Already initiated connection.
        * @param   previousState
        *          Last known state.
        * @param   comparisonResult
        *          Instance of result to be filled in.
        *
        * @throws  SQLException
        *          On unsuccessful calls to DB.
        */
       private void getAndCompareIndexes(Connection conn,
                                         DatabaseState previousState,
                                         ComparisonResult comparisonResult)
       throws SQLException
       {
          Dialect dialect = Dialect.getDialect(database);
          DatabaseState currentState = comparisonResult.getState();
          for (Table table : comparisonResult.getState().getTables())
          {
             // Load indexes from DB
             ResultSet indexes = conn.getMetaData()
                                     .getIndexInfo(dialect.getCatalogPattern(database),
                                                   dialect.getSchemaPattern(database),
                                                   table.getName(),
                                                   false,
                                                   false);

             // Put all data into the new State
             while (indexes.next())
             {
                String indexName = indexes.getString("INDEX_NAME");
                String columnName = indexes.getString("COLUMN_NAME");
                boolean ascending = !"D".equalsIgnoreCase(indexes.getString("ASC_OR_DESC"));
                boolean primary = false;
                boolean unique = !indexes.getBoolean("NON_UNIQUE");
                String comment = "";
                List<Tuple<String, Boolean>> comp = Collections.singletonList(new Tuple<>(columnName, ascending));
                Index index = new Index(indexName, table, comp, unique, primary, comment);

                if (table.getIndexes().contains(index))
                {
                   // TODO: this could use heavy optimization
                   // Index already exists. Just merge the columns.
                   table.getIndexes().stream()
                        .filter(existingIndex -> existingIndex.equals(index))
                        .findFirst()
                        .ifPresent(existingIndex ->
                                   existingIndex.getColumns().addAll(index.getColumns()));
                }
                else
                {
                   // New index
                   table.getIndexes().add(index);
                }
             }

             for (Index index : table.getIndexes())
             {
                List<Tuple<String, Boolean>> columns = index.getColumns();
                if (columns.size() == 1 && columns.get(0).getKey().equalsIgnoreCase(Session.PK))
                {
                   index.setPrimary(true);
                }
             }
          }

          // Compare if old State is available
          if (previousState != null)
          {
             for (Table currentTable : currentState.getTables())
             {
                if (previousState.getTables().contains(currentTable))
                {
                   // Table already existed, let's check for index differences
                   previousState.getTables().stream()
                                .filter(t -> t.equals(currentTable))
                                .findFirst()
                                .ifPresent(previousTable ->
                                   {
                                      // Detect added or changed indexes
                                      for (Index currentIndex : currentTable.getIndexes())
                                      {
                                         if (!previousTable.getIndexes().contains(currentIndex))
                                         {
                                            // Added index
                                            comparisonResult.getDifferences()
                                                            .add(new Difference(currentTable, null, currentIndex));
                                         }
                                         else
                                         {
                                            // Index already existed, let's check for differences
                                            previousTable.getIndexes().stream()
                                               .filter(prevI -> prevI.equals(currentIndex))
                                               .filter(prevI ->
                                                       !prevI.getColumns().equals(currentIndex.getColumns()))
                                               .filter(prevI ->
                                                       prevI.getUnique() != currentIndex.getUnique())
                                               .filter(prevI ->
                                                       prevI.getPrimary() != currentIndex.getPrimary())
                                               .filter(prevI ->
                                                       !prevI.getComment().equals(currentIndex.getComment()))
                                               .findFirst()
                                               .ifPresent(previousIndex ->
                                                          comparisonResult.getDifferences()
                                                                          .add(new Difference(currentTable,
                                                                                              previousIndex,
                                                                                              currentIndex)));
                                         }
                                      }

                                      // Detect removed indexes
                                      for (Index previousIndex : previousTable.getIndexes())
                                      {
                                         if (!currentTable.getIndexes().contains(previousIndex))
                                         {
                                            comparisonResult.getDifferences()
                                                            .add(new Difference(currentTable,
                                                                                previousIndex,
                                                                                null));
                                         }
                                      }
                                   });
                }
             }
          }
       }

       /**
        * Load information on sequences from the provided connection, put the state into result. If previous state
        * is provided we can compare current state with the previous one and report any differences into result.
        *
        * @param   conn
        *          Already initiated connection.
        * @param   previousState
        *          Last known state.
        * @param   comparisonResult
        *          Instance of result to be filled in.
        *
        * @throws  SQLException
        *          on unsuccessful calls to DB.
        */
       private void getAndCompareSequences(Connection conn,
                                           DatabaseState previousState,
                                           ComparisonResult comparisonResult)
       throws SQLException
       {
          Dialect dialect = Dialect.getDialect(database);
          // Load sequences from DB
          ResultSet sequences = conn.prepareStatement(dialect.getSequenceSQL(database)).executeQuery();

          // Put all data into the new State
          DatabaseState currentState = comparisonResult.getState();
          while (sequences.next())
          {
             currentState.getSequences()
                     .add(new Sequence(database, sequences.getString("sequence_name"), ""));
          }

          // Compare if old State is available
          if (previousState != null)
          {
             // Detect added
             for (Sequence sequence : currentState.getSequences())
             {
                if (!previousState.getSequences().contains(sequence))
                {
                   comparisonResult.getDifferences()
                           .add(new Difference(null, null, sequence));
                }
             }

             // Detect removed
             for (Sequence sequence : currentState.getSequences())
             {
                if (!currentState.getSequences().contains(sequence))
                {
                   comparisonResult.getDifferences()
                           .add(new Difference(null, sequence, null));
                }
             }
          }
       }

       /**
        * If table validation or DMO generation failed, this method can remove it from result so that it can be
        * loaded next time.
        *
        * @param   table
        *          failing table
        * @throws  PersistenceException
        *          only thrown if file storage is enabled and writing it fails
        */
       public void removeTableFromLastKnownState(Table table)
       throws PersistenceException
       {
          lastKnownState.getTables().remove(table);
          if (WRITE_KNOWN_STATE_FILE)
          {
             saveLastKnownState(lastKnownState);
          }
       }

       /**
        * EXTENT property of columns are stored in separate tables/columns (normalized/denormalized mode). This
        * method looks for extent tables and assigns found attributes to the result. Also, extent tables are
        * removed from the result and parent tables are inserted instead.
        *
        * This alters the result (it doesn't fully represent what is available in the database any more), but
        * is required for EXTENT fields to work.
        *
        * @param   result
        *          Original comparison result
        */
       public void enrichExtentFields(ComparisonResult result)
       {
          // Map tables for easier lookup
          Set<Table> tables = result.getState().tables;
          Map<String, Table> tablesByName = tables.stream()
                  .collect(Collectors.toMap(Table::getName, table -> table));

          // Iterate
          for (Table table : new ArrayList<>(tables))
          {
             // Find normalized EXTENT tables by name pattern
             Matcher tabMatcher = PATTERN_EXTENT_TABLE.matcher(table.getName());
             if (tabMatcher.find())
             {
                String parentTableName = tabMatcher.group(1);
                int extent = Integer.parseInt(tabMatcher.group(2));

                // Find parent table
                Table parentTable = tablesByName.get(parentTableName);
                if (parentTable != null)
                {
                   throw new StopConditionException(
                        "Table '" + table.getName() + "' appears to be a normalized extent table. " +
                        "They are not supported in mutable databases. Please, convert to denormalized extent."
                   );
                }
             }

             // Find denormalized columns
             for (Attribute attribute : new ArrayList<>(table.getAttributes()))
             {
                Matcher colMatcher = PATTERN_EXTENT_COLUMN.matcher(attribute.getName());
                if (colMatcher.find())
                {
                   String baseName = colMatcher.group(1);

                   // Find further columns matching the pattern
                   int highestExtent;
                   for (highestExtent = 1; true; highestExtent++)
                   {
                      String extentName = baseName + "_" + (highestExtent + 1);
                      if (table.getAttributes().stream()
                         .noneMatch(attr -> attr.getName().equalsIgnoreCase(extentName)))
                      {
                         break;
                      }
                   }

                   // Find extent meta info in comments
                   Matcher metaMatcher = COMMENT_EXTENT_COLUMN.matcher(attribute.getComment());
                   boolean metaAvailable = metaMatcher.find();
                   if (metaAvailable)
                   {
                      highestExtent = Integer.parseInt(metaMatcher.group(1));
                   }

                   // Found at least 3 columns matching the pattern, consider it an extent column
                   if (highestExtent >= MINIMUM_EXTENT_COLUMNS || metaAvailable)
                   {
                      updateResultByExtentColumn(result, table, highestExtent, baseName);
                   }
                }
             }
          }
       }

       /**
        * Update result to reflect detected EXTENT based on columns (denormalized mode).
        *
        * @param  result
        *         Original comparison result
        * @param  table
        *         Table containing extent columns
        * @param  extent
        *         Calculated extent value
        * @param  baseName
        *         Calculated name of the base column
        */
       private void updateResultByExtentColumn(ComparisonResult result, Table table, int extent, String baseName)
       {
          boolean hadDifference = false;
          Attribute lastAttribute = null;
          for (int i = 1; i <= extent; i++)
          {
             String extentName = baseName + "_" + i;
             for (Attribute attr : table.attributes)
             {
                if (attr.getName().equalsIgnoreCase(extentName))
                {
                   table.attributes.remove(attr);
                   if (result.differences.removeIf(difference -> difference.getNewElement() == attr))
                   {
                      hadDifference = true;
                   }
                   lastAttribute = attr;
                   break;
                }
             }
          }
          if (lastAttribute != null)
          {
             Attribute attr = new Attribute(
                     baseName,
                     lastAttribute.getTable(),
                     lastAttribute.getType(),
                     lastAttribute.getPrecision(),
                     lastAttribute.getNullable(),
                     lastAttribute.getDefaultValue(),
                     extent,
                     lastAttribute.getComment()
             );
             table.getAttributes().add(attr);
             if (hadDifference) {
                result.differences.add(new Difference(table, null, attr));
             }
          }
       }
    }