/1.Framework/Vita.Data/Management/DbModelComparer.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;



using Vita.Common;

using Vita.Entities;

using Vita.Entities.Model;

using Vita.Logging;

using Vita.Data.Driver;

using Vita.Data.Model;

using Vita.Data.Runtime;



namespace Vita.Data.Management {



  public class DbModelComparer {

    DbSchemaUpdateInfo _changes;

    DbModel _newModel, _oldModel;

    DbSchemaUpdateOptions _optons;

    bool _useRefIntegrity, _useStoredProcs, _deleteTables, _supportsSchemas, _supportsClusteredIndexes;

    Log _log;



    public DbSchemaUpdateInfo GetSchemaChanges(DbModel newModel, DbModel oldModel, Log log, DbSchemaUpdateOptions updateOptions) {

      _newModel = newModel;

      _oldModel = oldModel;

      _log = log;

      _optons = updateOptions;

      _changes = new DbSchemaUpdateInfo(_newModel, _oldModel);

      _changes.Actions.Clear(); 

      var driver = _newModel.Driver;

      _useRefIntegrity = driver.Supports(DbCapabilities.ReferentialConstraints) && _newModel.Settings.Options.IsSet(DbOptions.UseRefIntegrity) ;

      _useStoredProcs = driver.Supports(DbCapabilities.StoredProcedures) && _newModel.Settings.Options.IsSet(DbOptions.UseStoredProcs);

      _deleteTables = updateOptions.IsSet(DbSchemaUpdateOptions.DropUnknownTables); 

      _supportsSchemas = driver.Supports(DbCapabilities.Schemas);

      _supportsClusteredIndexes = driver.Supports(DbCapabilities.ClusteredIndexes); 

      

      ResetChangeInfo(_changes.OldDbModel.Tables);

      ResetChangeInfo(_newModel.Tables);

      MatchObjectsWithPeers();

      MarkObjectsForDrop();

      MarkObjectsForAdd();

      AnalyzeObjectsChanges();

      BuildChangeActions();

      _changes.Actions.Sort(DbSchemaUpdateAction.ByChangeType);

      ResetPeers(_newModel.Tables); //make sure we drop references to old model

      return _changes; 

    }//class



    // Returns true if schema is present in New (current model); by default we do not do anything with 

    //   objects in inactive schemas 

    private bool IsActive(string schema) {

      if (!_supportsSchemas && string.IsNullOrEmpty(schema)) 

        return true; 

      return _changes.NewDbModel.ContainsSchema(schema);

    }



    private void ResetPeers(IEnumerable<DbTableInfo> tables) {

      foreach (var t in tables) {

        t.Peer = null;

        foreach (var c in t.Columns) 

          c.Peer = null; 

        foreach(var k in t.Keys) 

          k.Peer = null;

        foreach(var rc in t.RefConstraints) 

          rc.Peer = null; 

      }

    }



    private static void ResetChangeInfo(IEnumerable<DbTableInfo> tables) {

      foreach (var t in tables) {

        t.ChangeType = DbObjectChange.None;

        t.Peer = null;

        foreach (var c in t.Columns) {

          c.ChangeType = DbObjectChange.None;

          c.Peer = null;

        }

        foreach (var key in t.Keys) {

          key.ChangeType = DbObjectChange.None;

          key.Peer = null; 

        }

        foreach (var rc in t.RefConstraints) {

          rc.ChangeType = DbObjectChange.None; 

          rc.Peer = null; 

        }

      }

    }



    #region Matching objects - set Peer property on each object in new and old models

    private void MatchObjectsWithPeers() {

      //Go through tables in new model and try to find match in old model

      foreach(var newT in _newModel.Tables) {

        var oldT = FindOldTable(newT);

        if (oldT == null) {

          newT.ChangeType = DbObjectChange.New;

          continue; //next table

        }

        //We found matching table - link them; then match columns and keys in both tables

        oldT.Peer = newT;

        newT.Peer = oldT; 

        

        // Match columns

        foreach (var newCol in newT.Columns) {

          var oldCol = FindOldColumn(newCol, oldT);

          if (oldCol == null) continue; 

          oldCol.Peer = newCol;

          newCol.Peer = oldCol; 

        }//foreach newCol



        //Match keys

        foreach (var newK in newT.Keys) {

          //first by name      

          var oldK = FindOldKey(oldT, newK);

          if (oldK == null) 

            continue; 

          oldK.Peer = newK;

          newK.Peer = oldK;

        }//foreach newKey

      } //foreach newT



      // Match ref constraints - in a separate loop, after all tables and keys are matched

      foreach (var oldT in _oldModel.Tables) {

        //Detect changes - only if there's object in new model

        if (oldT.Peer == null) continue;

        foreach (var newRc in oldT.Peer.RefConstraints) {

          var oldRc = FindOldRefConstraint(oldT, newRc);

          if (oldRc == null) continue; 

          oldRc.Peer = newRc;

          newRc.Peer = oldRc; 

        } //foreach newRc

      }//foreach oldT



      //Match stored procs

      if (_useStoredProcs && _optons.IsSet(DbSchemaUpdateOptions.VerifyRoutines)) {

        foreach (var oldCmd in _oldModel.Commands) {

          oldCmd.Peer = _newModel.GetCommand(oldCmd.FullCommandName);

          if (oldCmd.Peer != null)

            oldCmd.Peer.Peer = oldCmd;

        }

      }//if useStoredProcs

    }//method



    // Utility methods used in matching ----------------------------------------------------------

    private DbTableInfo FindOldTable(DbTableInfo newTable) {

      DbTableInfo oldT = _oldModel.GetTable(newTable.FullName);

      if (oldT != null) return oldT;

      var entity = newTable.Entity;

      if(entity == null) return null; //just in case, if we ever have tables without entities

      if (entity.OldNames == null) return null; 

      foreach (var oldName in entity.OldNames) {

        oldT = _oldModel.GetTable(newTable.SchemaName, oldName);

        if (oldT != null)  return oldT;

        //if old name starts with "I" (like all interfaces), then try without I

        if (oldName.StartsWith("I"))

          oldT = _oldModel.GetTable(newTable.SchemaName, oldName.Substring(1));

        if (oldT != null)  return oldT;

      }//foreach oldName

      return null; 

    }

    private DbColumnInfo FindOldColumn(DbColumnInfo newCol, DbTableInfo oldTable) {

      var oldCol = oldTable.Columns.FirstOrDefault(c => c.ColumnName == newCol.ColumnName);

      if (oldCol != null) return oldCol;

      // Try finding by old name

      var oldNames = newCol.Member.OldNames;

      if (oldNames == null)  return null; 

      foreach (var oldName in oldNames) {

        oldCol = oldTable.Columns.FirstOrDefault(c => c.ColumnName == oldName);

        if (oldCol != null)

          return oldCol; 

      }

      return null; 

    }



    private DbKeyInfo FindOldKey(DbTableInfo oldTable, DbKeyInfo newKey) {

      // just for easier debugging, preselect keys matching by type and column count

      var similarOldKeys = oldTable.Keys.Where(k => k.KeyType == newKey.KeyType && k.Columns.Count == newKey.Columns.Count).ToList();

      foreach (var oldKey in similarOldKeys) {

        // If we have duplicating keys (ex: Northwind database, OrdersTable, key CustomerID, CustomerOrders), then lets try match them in pairs, to accurately report differences

        if (oldKey.Peer != null)

          continue;

        if (DbKeysMatch(oldKey, newKey))

          return oldKey;

      }

      return null;

    }



    private bool DbKeysMatch(DbKeyInfo oldKey, DbKeyInfo newKey) {

      if(oldKey.KeyType != newKey.KeyType ||

          oldKey.Columns.Count != newKey.Columns.Count) return false;

      //check column-by-column match

      for(int i = 0; i < oldKey.Columns.Count; i++) {

        var oldKeyCol = oldKey.Columns[i];

        var newKeyCol = newKey.Columns[i];

        if (oldKeyCol.Peer != newKeyCol)

          return false;

        var oldIsDesc = oldKey.DescendingColumns.Contains(oldKeyCol);

        var newIsDesc = newKey.DescendingColumns.Contains(newKeyCol);

        if (oldIsDesc != newIsDesc)

          return false; 

      }

      return true; 

    }



    private DbRefConstraintInfo FindOldRefConstraint(DbTableInfo oldTable, DbRefConstraintInfo newRefConstraint) {

      var newFrom = newRefConstraint.FromKey;

      var newTo = newRefConstraint.ToKey;

      foreach (var oldRc in oldTable.RefConstraints)

        if (oldRc.FromKey.Peer == newFrom && oldRc.ToKey.Peer == newTo)

          return oldRc;

      return null;

    } 

    #endregion



    #region Analyzing changes

    // Sets objects with no peers in old model to "DROP" change type; objects in new model with no peers are set to "New"

    private void MarkObjectsForDrop() {

      foreach (var T in _oldModel.Tables) {

        if (!IsActive(T.SchemaName)) continue; 

        if (T.Peer == null && _deleteTables) {

          T.ChangeType = DbObjectChange.Drop;

        }

        foreach (var c in T.Columns)

          if (c.Peer == null) c.ChangeType = DbObjectChange.Drop;

        //do not drop not supported 'strange' keys, let them stay; also, key with no columns is fake PK that VITA creates if there is no PK on the table

        foreach (var k in T.Keys) {

          if (T.ChangeType == DbObjectChange.Drop) 

            continue; // if table is dropped, all keys will be dropped with it

          if (k.Peer == null && k.Columns.Count > 0 && !k.NotSupported)

            k.ChangeType = DbObjectChange.Drop;

        }

        foreach (var rc in T.RefConstraints)

          if (rc.Peer == null) 

            rc.ChangeType = DbObjectChange.Drop;

      }//tables

    }



    private void MarkObjectsForAdd() {

      foreach (var T in _newModel.Tables) {

        if (T.Peer == null) T.ChangeType = DbObjectChange.New;

        foreach (var c in T.Columns)

          if (c.Peer == null) c.ChangeType = DbObjectChange.New;

        foreach (var k in T.Keys)

          if (k.Peer == null && k.Columns.Count > 0)  // when there is no PK on the table, VITA creates fake PK with no columns - do not create this in database

            k.ChangeType = DbObjectChange.New;

        foreach (var rc in T.RefConstraints)

          if (rc.Peer == null) rc.ChangeType = DbObjectChange.New;

      }

    }



    //Go only through those objects that have peers

    private void AnalyzeObjectsChanges() {

      //Go through old tables and analyze changes

      foreach (var oldT in _oldModel.Tables) {

        if (oldT.Peer == null) continue; 

        if (!IsActive(oldT.SchemaName)) continue; 

        //Check columns changes

        bool anyChanged = false; 

        foreach (var oldCol in oldT.Columns)

          anyChanged |= CheckColumnChanged(oldCol);

        //Check if table columns changed; if yes, mark old table as modified

        if (oldT.ChangeType == DbObjectChange.None && (anyChanged || oldT.Peer.Columns.Count > oldT.Columns.Count)) 

            oldT.ChangeType = DbObjectChange.Modify;

        //check all keys - if any column in the key changed, mark key as changed

        foreach (var key in oldT.Keys) {

          anyChanged = key.Columns.Any(c => c.ChangeType != DbObjectChange.None);

          if (key.ChangeType == DbObjectChange.None && anyChanged)

            key.ChangeType = DbObjectChange.Modify;

        }

      }//foreach oldT



      // Now check ref constraints - if From or To key changed, mark constraint as modified.

      // We must do it in a separate loop, after setting change status for all keys in all tables

      foreach (var oldT in _oldModel.Tables) {

        if (oldT.Peer == null) continue;

        foreach (var refc in oldT.RefConstraints) {

          if (refc.ChangeType != DbObjectChange.None) continue;

          if (refc.ToKey.Table.ChangeType == DbObjectChange.Drop) {

            refc.ChangeType = DbObjectChange.Drop;

            continue;           

          }

          var modified = refc.Peer == null || refc.CascadeDelete != refc.Peer.CascadeDelete ||

            refc.FromKey.ChangeType != DbObjectChange.None || refc.ToKey.ChangeType != DbObjectChange.None;

          if (modified)

            refc.ChangeType = DbObjectChange.Modify;

        }//foreach refc

      }//foreach oldT



    }//mehtod



    private bool CheckColumnChanged(DbColumnInfo oldColumn) {

      //Note: we check renaming separately when we build change list

      if (oldColumn.Peer == null) 

        oldColumn.ChangeType = DbObjectChange.Drop;

      else if (!ColumnsMatch(oldColumn, oldColumn.Peer)) 

        oldColumn.ChangeType = DbObjectChange.Modify;

      return oldColumn.ChangeType != DbObjectChange.None;

    }//method



    private static bool ColumnsMatch(DbColumnInfo x, DbColumnInfo y) {

      var nullableMatch = x.Flags.IsSet(DbColumnFlags.Nullable) == y.Flags.IsSet(DbColumnFlags.Nullable);

      var identityMatch = x.Flags.IsSet(DbColumnFlags.Identity) == y.Flags.IsSet(DbColumnFlags.Identity);

      var dbTypesMatch = x.TypeInfo.DbType == y.TypeInfo.DbType;

      var scalePrecMatch = x.TypeInfo.ClrType == typeof(decimal) ? x.TypeInfo.Scale == y.TypeInfo.Scale && x.TypeInfo.Precision == y.TypeInfo.Precision : true; 

      var clrType = x.TypeInfo.ClrType;

      var sizeMatters = clrType == typeof(string) || clrType == typeof(byte[]); 

      var sizeMatch = sizeMatters ? x.TypeInfo.Size == y.TypeInfo.Size : true;

      return (x.TypeInfo.DbType == y.TypeInfo.DbType && nullableMatch && identityMatch && sizeMatch && dbTypesMatch && scalePrecMatch);

         // && x.TypeInfo.DefaultExpression == y.TypeInfo.DefaultExpression);

        //NOTE: default expresssion cannot be changed after column is created (MSSQL).

    }



    #endregion



    #region Building change actions

    private void BuildChangeActions() {

      BuildRenameActions();

      BuildModifyDropActions();

      BuildAddActions();

      if (_optons.IsSet(DbSchemaUpdateOptions.VerifyRoutines))

        BuildStoredProcActions(); 

    }



    private void BuildModifyDropActions() {

      // Objects in OLD model: drop or modify

      foreach (var oldT in _oldModel.Tables) {

        switch(oldT.ChangeType) {

          case DbObjectChange.Drop: AddChangeAction(DbSchemaChangeActionType.TableDrop, oldT); break; 

          case DbObjectChange.Modify: 

            //Check columns

            foreach (var oldCol in oldT.Columns) {

              if (oldCol.Flags.IsSet(DbColumnFlags.Error)) continue; //do not do anything about column in error

              switch (oldCol.ChangeType) {

                case DbObjectChange.Drop: AddChangeAction(DbSchemaChangeActionType.ColumnDrop, oldCol); break;

                case DbObjectChange.Modify:

                  var newCol = oldCol.Peer; 

                  bool needsInit = oldCol.Flags.IsSet(DbColumnFlags.Nullable) && 

                     !newCol.Flags.IsSet(DbColumnFlags.Nullable) && newCol.DefaultExpression == null;

                  if (needsInit)

                    AddChangeAction(DbSchemaChangeActionType.ColumnInit, newCol); 

                  AddChangeAction(DbSchemaChangeActionType.ColumnModify, newCol); 

                  break;

              }//switch

            }//foreach oldCol

            break; 

          }//switch



        // Keys 

        foreach (var key in oldT.Keys) {

          if (key.KeyType.IsSet(KeyType.Auto) || key.ChangeType == DbObjectChange.None) continue; 

          AddChangeAction(DbSchemaChangeActionType.KeyDrop, key);

          if (key.Peer != null)

            AddKeyAddAction(key.Peer);

        }



        //Ref constraints

        foreach (var refc in oldT.RefConstraints)

          if (refc.ChangeType != DbObjectChange.None) {

            AddChangeAction(DbSchemaChangeActionType.RefConstraintDrop, refc);

            if (refc.Peer != null && _useRefIntegrity)

              AddChangeAction(DbSchemaChangeActionType.RefConstraintAdd, refc.Peer);

          }

        

      }//foreach oldT 



    }



    private void BuildAddActions() {

      // Schemas

      if (_supportsSchemas) {

        foreach (var schema in _newModel.Schemas)

          if (!_oldModel.ContainsSchema(schema))

            AddChangeAction(DbSchemaChangeActionType.SchemaAdd, schema);

      }

      //Tables and columns

      foreach (var newT in _newModel.Tables) {

        switch (newT.ChangeType) {

          case DbObjectChange.New:

            AddChangeAction(DbSchemaChangeActionType.TableAdd, newT);

            break;

          default:

            var changing = (newT.Peer != null && newT.Peer.ChangeType != DbObjectChange.None);

            if (!changing)  break;

            foreach (var col in newT.Columns) 

              if(col.ChangeType == DbObjectChange.New) {

                AddChangeAction(DbSchemaChangeActionType.ColumnAdd, col);

                bool needsInit = !col.Flags.IsSet(DbColumnFlags.Nullable) && col.DefaultExpression == null;

                if (needsInit) {

                  AddChangeAction(DbSchemaChangeActionType.ColumnAddedInit, col);

                  AddChangeAction(DbSchemaChangeActionType.ColumnAddComplete, col);

                }

            }//foreach col

            break;

        }//switch



        //Keys and constraints

        foreach (var key in newT.Keys) {

          if (key.KeyType.IsSet(KeyType.Auto))

            continue; 

          if (key.ChangeType == DbObjectChange.New)

            AddKeyAddAction(key);

        }

        if (_useRefIntegrity)

          foreach (var refc in newT.RefConstraints)

            if (refc.ChangeType == DbObjectChange.New)

              AddChangeAction(DbSchemaChangeActionType.RefConstraintAdd, refc);

      }//foreach newT



    }//method



    private void BuildStoredProcActions() {

      if (!_useStoredProcs)

        return;

      var driver = _newModel.Driver;

      //Delete old routines in active schema with no match

      foreach (var oldCmd in _oldModel.Commands) {

        if (oldCmd.Flags.IsSet(DbCommandFlags.NotGenerated)) // do not drop SPs that were not generated by VITA

          continue; 

        if (IsActive(oldCmd.Schema) && oldCmd.Peer == null) 

          AddChangeAction(DbSchemaChangeActionType.RoutineDrop, oldCmd.FullCommandName);

      }

      foreach(var newCmd in _newModel.Commands) {

        if (newCmd.Peer != null && driver.StoredProceduresMatch(newCmd, newCmd.Peer)) 

          continue; 

        //no match

        if (newCmd.Peer != null)

          AddChangeAction(DbSchemaChangeActionType.RoutineDrop, newCmd.FullCommandName);

        AddChangeAction(DbSchemaChangeActionType.RoutineAdd, newCmd);

      }

    }



    //Note that name change is not reflected in ChangeStatus - it would be still None if only Name changed, but nothing else did not. 

    private void BuildRenameActions() {

      bool tableNamesIgnoreCase = _newModel.Settings.Options.IsSet(DbOptions.IgnoreTableNamesCase);

      foreach (var oldT in _oldModel.Tables) {

        if (oldT.Peer == null) continue;

        if (string.Compare(oldT.TableName, oldT.Peer.TableName, tableNamesIgnoreCase) != 0)

          AddChangeAction(DbSchemaChangeActionType.TableRename, oldT);

        foreach (var c in oldT.Columns)

          if (c.Peer != null && c.ColumnName != c.Peer.ColumnName)

            AddChangeAction(DbSchemaChangeActionType.ColumnRename, c);

      }

    }



    private DbSchemaUpdateAction AddKeyAddAction(DbKeyInfo key) {

      var actionType = key.KeyType == KeyType.PrimaryKey ? DbSchemaChangeActionType.PrimaryKeyAdd : DbSchemaChangeActionType.KeyAdd;

      return AddChangeAction(actionType, key); 

    }

    private DbSchemaUpdateAction AddChangeAction(DbSchemaChangeActionType actionType, object objectInfo) {

      var action = new DbSchemaUpdateAction(actionType, objectInfo);

      _changes.Actions.Add(action);

      return action;

    }

    #endregion







  } //class



}//namespace