PageRenderTime 55ms CodeModel.GetById 3ms app.highlight 46ms RepoModel.GetById 1ms app.codeStats 1ms

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

http://vita.codeplex.com
C# | 458 lines | 379 code | 41 blank | 38 comment | 178 complexity | e24c3c09a6c5a228145d59559bffb299 MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5
  6using Vita.Common;
  7using Vita.Entities;
  8using Vita.Entities.Model;
  9using Vita.Logging;
 10using Vita.Data.Driver;
 11using Vita.Data.Model;
 12using Vita.Data.Runtime;
 13
 14namespace Vita.Data.Management {
 15
 16  public class DbModelComparer {
 17    DbSchemaUpdateInfo _changes;
 18    DbModel _newModel, _oldModel;
 19    DbSchemaUpdateOptions _optons;
 20    bool _useRefIntegrity, _useStoredProcs, _deleteTables, _supportsSchemas, _supportsClusteredIndexes;
 21    Log _log;
 22
 23    public DbSchemaUpdateInfo GetSchemaChanges(DbModel newModel, DbModel oldModel, Log log, DbSchemaUpdateOptions updateOptions) {
 24      _newModel = newModel;
 25      _oldModel = oldModel;
 26      _log = log;
 27      _optons = updateOptions;
 28      _changes = new DbSchemaUpdateInfo(_newModel, _oldModel);
 29      _changes.Actions.Clear(); 
 30      var driver = _newModel.Driver;
 31      _useRefIntegrity = driver.Supports(DbCapabilities.ReferentialConstraints) && _newModel.Settings.Options.IsSet(DbOptions.UseRefIntegrity) ;
 32      _useStoredProcs = driver.Supports(DbCapabilities.StoredProcedures) && _newModel.Settings.Options.IsSet(DbOptions.UseStoredProcs);
 33      _deleteTables = updateOptions.IsSet(DbSchemaUpdateOptions.DropUnknownTables); 
 34      _supportsSchemas = driver.Supports(DbCapabilities.Schemas);
 35      _supportsClusteredIndexes = driver.Supports(DbCapabilities.ClusteredIndexes); 
 36      
 37      ResetChangeInfo(_changes.OldDbModel.Tables);
 38      ResetChangeInfo(_newModel.Tables);
 39      MatchObjectsWithPeers();
 40      MarkObjectsForDrop();
 41      MarkObjectsForAdd();
 42      AnalyzeObjectsChanges();
 43      BuildChangeActions();
 44      _changes.Actions.Sort(DbSchemaUpdateAction.ByChangeType);
 45      ResetPeers(_newModel.Tables); //make sure we drop references to old model
 46      return _changes; 
 47    }//class
 48
 49    // Returns true if schema is present in New (current model); by default we do not do anything with 
 50    //   objects in inactive schemas 
 51    private bool IsActive(string schema) {
 52      if (!_supportsSchemas && string.IsNullOrEmpty(schema)) 
 53        return true; 
 54      return _changes.NewDbModel.ContainsSchema(schema);
 55    }
 56
 57    private void ResetPeers(IEnumerable<DbTableInfo> tables) {
 58      foreach (var t in tables) {
 59        t.Peer = null;
 60        foreach (var c in t.Columns) 
 61          c.Peer = null; 
 62        foreach(var k in t.Keys) 
 63          k.Peer = null;
 64        foreach(var rc in t.RefConstraints) 
 65          rc.Peer = null; 
 66      }
 67    }
 68
 69    private static void ResetChangeInfo(IEnumerable<DbTableInfo> tables) {
 70      foreach (var t in tables) {
 71        t.ChangeType = DbObjectChange.None;
 72        t.Peer = null;
 73        foreach (var c in t.Columns) {
 74          c.ChangeType = DbObjectChange.None;
 75          c.Peer = null;
 76        }
 77        foreach (var key in t.Keys) {
 78          key.ChangeType = DbObjectChange.None;
 79          key.Peer = null; 
 80        }
 81        foreach (var rc in t.RefConstraints) {
 82          rc.ChangeType = DbObjectChange.None; 
 83          rc.Peer = null; 
 84        }
 85      }
 86    }
 87
 88    #region Matching objects - set Peer property on each object in new and old models
 89    private void MatchObjectsWithPeers() {
 90      //Go through tables in new model and try to find match in old model
 91      foreach(var newT in _newModel.Tables) {
 92        var oldT = FindOldTable(newT);
 93        if (oldT == null) {
 94          newT.ChangeType = DbObjectChange.New;
 95          continue; //next table
 96        }
 97        //We found matching table - link them; then match columns and keys in both tables
 98        oldT.Peer = newT;
 99        newT.Peer = oldT; 
100        
101        // Match columns
102        foreach (var newCol in newT.Columns) {
103          var oldCol = FindOldColumn(newCol, oldT);
104          if (oldCol == null) continue; 
105          oldCol.Peer = newCol;
106          newCol.Peer = oldCol; 
107        }//foreach newCol
108
109        //Match keys
110        foreach (var newK in newT.Keys) {
111          //first by name      
112          var oldK = FindOldKey(oldT, newK);
113          if (oldK == null) 
114            continue; 
115          oldK.Peer = newK;
116          newK.Peer = oldK;
117        }//foreach newKey
118      } //foreach newT
119
120      // Match ref constraints - in a separate loop, after all tables and keys are matched
121      foreach (var oldT in _oldModel.Tables) {
122        //Detect changes - only if there's object in new model
123        if (oldT.Peer == null) continue;
124        foreach (var newRc in oldT.Peer.RefConstraints) {
125          var oldRc = FindOldRefConstraint(oldT, newRc);
126          if (oldRc == null) continue; 
127          oldRc.Peer = newRc;
128          newRc.Peer = oldRc; 
129        } //foreach newRc
130      }//foreach oldT
131
132      //Match stored procs
133      if (_useStoredProcs && _optons.IsSet(DbSchemaUpdateOptions.VerifyRoutines)) {
134        foreach (var oldCmd in _oldModel.Commands) {
135          oldCmd.Peer = _newModel.GetCommand(oldCmd.FullCommandName);
136          if (oldCmd.Peer != null)
137            oldCmd.Peer.Peer = oldCmd;
138        }
139      }//if useStoredProcs
140    }//method
141
142    // Utility methods used in matching ----------------------------------------------------------
143    private DbTableInfo FindOldTable(DbTableInfo newTable) {
144      DbTableInfo oldT = _oldModel.GetTable(newTable.FullName);
145      if (oldT != null) return oldT;
146      var entity = newTable.Entity;
147      if(entity == null) return null; //just in case, if we ever have tables without entities
148      if (entity.OldNames == null) return null; 
149      foreach (var oldName in entity.OldNames) {
150        oldT = _oldModel.GetTable(newTable.SchemaName, oldName);
151        if (oldT != null)  return oldT;
152        //if old name starts with "I" (like all interfaces), then try without I
153        if (oldName.StartsWith("I"))
154          oldT = _oldModel.GetTable(newTable.SchemaName, oldName.Substring(1));
155        if (oldT != null)  return oldT;
156      }//foreach oldName
157      return null; 
158    }
159    private DbColumnInfo FindOldColumn(DbColumnInfo newCol, DbTableInfo oldTable) {
160      var oldCol = oldTable.Columns.FirstOrDefault(c => c.ColumnName == newCol.ColumnName);
161      if (oldCol != null) return oldCol;
162      // Try finding by old name
163      var oldNames = newCol.Member.OldNames;
164      if (oldNames == null)  return null; 
165      foreach (var oldName in oldNames) {
166        oldCol = oldTable.Columns.FirstOrDefault(c => c.ColumnName == oldName);
167        if (oldCol != null)
168          return oldCol; 
169      }
170      return null; 
171    }
172
173    private DbKeyInfo FindOldKey(DbTableInfo oldTable, DbKeyInfo newKey) {
174      // just for easier debugging, preselect keys matching by type and column count
175      var similarOldKeys = oldTable.Keys.Where(k => k.KeyType == newKey.KeyType && k.Columns.Count == newKey.Columns.Count).ToList();
176      foreach (var oldKey in similarOldKeys) {
177        // If we have duplicating keys (ex: Northwind database, OrdersTable, key CustomerID, CustomerOrders), then lets try match them in pairs, to accurately report differences
178        if (oldKey.Peer != null)
179          continue;
180        if (DbKeysMatch(oldKey, newKey))
181          return oldKey;
182      }
183      return null;
184    }
185
186    private bool DbKeysMatch(DbKeyInfo oldKey, DbKeyInfo newKey) {
187      if(oldKey.KeyType != newKey.KeyType ||
188          oldKey.Columns.Count != newKey.Columns.Count) return false;
189      //check column-by-column match
190      for(int i = 0; i < oldKey.Columns.Count; i++) {
191        var oldKeyCol = oldKey.Columns[i];
192        var newKeyCol = newKey.Columns[i];
193        if (oldKeyCol.Peer != newKeyCol)
194          return false;
195        var oldIsDesc = oldKey.DescendingColumns.Contains(oldKeyCol);
196        var newIsDesc = newKey.DescendingColumns.Contains(newKeyCol);
197        if (oldIsDesc != newIsDesc)
198          return false; 
199      }
200      return true; 
201    }
202
203    private DbRefConstraintInfo FindOldRefConstraint(DbTableInfo oldTable, DbRefConstraintInfo newRefConstraint) {
204      var newFrom = newRefConstraint.FromKey;
205      var newTo = newRefConstraint.ToKey;
206      foreach (var oldRc in oldTable.RefConstraints)
207        if (oldRc.FromKey.Peer == newFrom && oldRc.ToKey.Peer == newTo)
208          return oldRc;
209      return null;
210    } 
211    #endregion
212
213    #region Analyzing changes
214    // Sets objects with no peers in old model to "DROP" change type; objects in new model with no peers are set to "New"
215    private void MarkObjectsForDrop() {
216      foreach (var T in _oldModel.Tables) {
217        if (!IsActive(T.SchemaName)) continue; 
218        if (T.Peer == null && _deleteTables) {
219          T.ChangeType = DbObjectChange.Drop;
220        }
221        foreach (var c in T.Columns)
222          if (c.Peer == null) c.ChangeType = DbObjectChange.Drop;
223        //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
224        foreach (var k in T.Keys) {
225          if (T.ChangeType == DbObjectChange.Drop) 
226            continue; // if table is dropped, all keys will be dropped with it
227          if (k.Peer == null && k.Columns.Count > 0 && !k.NotSupported)
228            k.ChangeType = DbObjectChange.Drop;
229        }
230        foreach (var rc in T.RefConstraints)
231          if (rc.Peer == null) 
232            rc.ChangeType = DbObjectChange.Drop;
233      }//tables
234    }
235
236    private void MarkObjectsForAdd() {
237      foreach (var T in _newModel.Tables) {
238        if (T.Peer == null) T.ChangeType = DbObjectChange.New;
239        foreach (var c in T.Columns)
240          if (c.Peer == null) c.ChangeType = DbObjectChange.New;
241        foreach (var k in T.Keys)
242          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
243            k.ChangeType = DbObjectChange.New;
244        foreach (var rc in T.RefConstraints)
245          if (rc.Peer == null) rc.ChangeType = DbObjectChange.New;
246      }
247    }
248
249    //Go only through those objects that have peers
250    private void AnalyzeObjectsChanges() {
251      //Go through old tables and analyze changes
252      foreach (var oldT in _oldModel.Tables) {
253        if (oldT.Peer == null) continue; 
254        if (!IsActive(oldT.SchemaName)) continue; 
255        //Check columns changes
256        bool anyChanged = false; 
257        foreach (var oldCol in oldT.Columns)
258          anyChanged |= CheckColumnChanged(oldCol);
259        //Check if table columns changed; if yes, mark old table as modified
260        if (oldT.ChangeType == DbObjectChange.None && (anyChanged || oldT.Peer.Columns.Count > oldT.Columns.Count)) 
261            oldT.ChangeType = DbObjectChange.Modify;
262        //check all keys - if any column in the key changed, mark key as changed
263        foreach (var key in oldT.Keys) {
264          anyChanged = key.Columns.Any(c => c.ChangeType != DbObjectChange.None);
265          if (key.ChangeType == DbObjectChange.None && anyChanged)
266            key.ChangeType = DbObjectChange.Modify;
267        }
268      }//foreach oldT
269
270      // Now check ref constraints - if From or To key changed, mark constraint as modified.
271      // We must do it in a separate loop, after setting change status for all keys in all tables
272      foreach (var oldT in _oldModel.Tables) {
273        if (oldT.Peer == null) continue;
274        foreach (var refc in oldT.RefConstraints) {
275          if (refc.ChangeType != DbObjectChange.None) continue;
276          if (refc.ToKey.Table.ChangeType == DbObjectChange.Drop) {
277            refc.ChangeType = DbObjectChange.Drop;
278            continue;           
279          }
280          var modified = refc.Peer == null || refc.CascadeDelete != refc.Peer.CascadeDelete ||
281            refc.FromKey.ChangeType != DbObjectChange.None || refc.ToKey.ChangeType != DbObjectChange.None;
282          if (modified)
283            refc.ChangeType = DbObjectChange.Modify;
284        }//foreach refc
285      }//foreach oldT
286
287    }//mehtod
288
289    private bool CheckColumnChanged(DbColumnInfo oldColumn) {
290      //Note: we check renaming separately when we build change list
291      if (oldColumn.Peer == null) 
292        oldColumn.ChangeType = DbObjectChange.Drop;
293      else if (!ColumnsMatch(oldColumn, oldColumn.Peer)) 
294        oldColumn.ChangeType = DbObjectChange.Modify;
295      return oldColumn.ChangeType != DbObjectChange.None;
296    }//method
297
298    private static bool ColumnsMatch(DbColumnInfo x, DbColumnInfo y) {
299      var nullableMatch = x.Flags.IsSet(DbColumnFlags.Nullable) == y.Flags.IsSet(DbColumnFlags.Nullable);
300      var identityMatch = x.Flags.IsSet(DbColumnFlags.Identity) == y.Flags.IsSet(DbColumnFlags.Identity);
301      var dbTypesMatch = x.TypeInfo.DbType == y.TypeInfo.DbType;
302      var scalePrecMatch = x.TypeInfo.ClrType == typeof(decimal) ? x.TypeInfo.Scale == y.TypeInfo.Scale && x.TypeInfo.Precision == y.TypeInfo.Precision : true; 
303      var clrType = x.TypeInfo.ClrType;
304      var sizeMatters = clrType == typeof(string) || clrType == typeof(byte[]); 
305      var sizeMatch = sizeMatters ? x.TypeInfo.Size == y.TypeInfo.Size : true;
306      return (x.TypeInfo.DbType == y.TypeInfo.DbType && nullableMatch && identityMatch && sizeMatch && dbTypesMatch && scalePrecMatch);
307         // && x.TypeInfo.DefaultExpression == y.TypeInfo.DefaultExpression);
308        //NOTE: default expresssion cannot be changed after column is created (MSSQL).
309    }
310
311    #endregion
312
313    #region Building change actions
314    private void BuildChangeActions() {
315      BuildRenameActions();
316      BuildModifyDropActions();
317      BuildAddActions();
318      if (_optons.IsSet(DbSchemaUpdateOptions.VerifyRoutines))
319        BuildStoredProcActions(); 
320    }
321
322    private void BuildModifyDropActions() {
323      // Objects in OLD model: drop or modify
324      foreach (var oldT in _oldModel.Tables) {
325        switch(oldT.ChangeType) {
326          case DbObjectChange.Drop: AddChangeAction(DbSchemaChangeActionType.TableDrop, oldT); break; 
327          case DbObjectChange.Modify: 
328            //Check columns
329            foreach (var oldCol in oldT.Columns) {
330              if (oldCol.Flags.IsSet(DbColumnFlags.Error)) continue; //do not do anything about column in error
331              switch (oldCol.ChangeType) {
332                case DbObjectChange.Drop: AddChangeAction(DbSchemaChangeActionType.ColumnDrop, oldCol); break;
333                case DbObjectChange.Modify:
334                  var newCol = oldCol.Peer; 
335                  bool needsInit = oldCol.Flags.IsSet(DbColumnFlags.Nullable) && 
336                     !newCol.Flags.IsSet(DbColumnFlags.Nullable) && newCol.DefaultExpression == null;
337                  if (needsInit)
338                    AddChangeAction(DbSchemaChangeActionType.ColumnInit, newCol); 
339                  AddChangeAction(DbSchemaChangeActionType.ColumnModify, newCol); 
340                  break;
341              }//switch
342            }//foreach oldCol
343            break; 
344          }//switch
345
346        // Keys 
347        foreach (var key in oldT.Keys) {
348          if (key.KeyType.IsSet(KeyType.Auto) || key.ChangeType == DbObjectChange.None) continue; 
349          AddChangeAction(DbSchemaChangeActionType.KeyDrop, key);
350          if (key.Peer != null)
351            AddKeyAddAction(key.Peer);
352        }
353
354        //Ref constraints
355        foreach (var refc in oldT.RefConstraints)
356          if (refc.ChangeType != DbObjectChange.None) {
357            AddChangeAction(DbSchemaChangeActionType.RefConstraintDrop, refc);
358            if (refc.Peer != null && _useRefIntegrity)
359              AddChangeAction(DbSchemaChangeActionType.RefConstraintAdd, refc.Peer);
360          }
361        
362      }//foreach oldT 
363
364    }
365
366    private void BuildAddActions() {
367      // Schemas
368      if (_supportsSchemas) {
369        foreach (var schema in _newModel.Schemas)
370          if (!_oldModel.ContainsSchema(schema))
371            AddChangeAction(DbSchemaChangeActionType.SchemaAdd, schema);
372      }
373      //Tables and columns
374      foreach (var newT in _newModel.Tables) {
375        switch (newT.ChangeType) {
376          case DbObjectChange.New:
377            AddChangeAction(DbSchemaChangeActionType.TableAdd, newT);
378            break;
379          default:
380            var changing = (newT.Peer != null && newT.Peer.ChangeType != DbObjectChange.None);
381            if (!changing)  break;
382            foreach (var col in newT.Columns) 
383              if(col.ChangeType == DbObjectChange.New) {
384                AddChangeAction(DbSchemaChangeActionType.ColumnAdd, col);
385                bool needsInit = !col.Flags.IsSet(DbColumnFlags.Nullable) && col.DefaultExpression == null;
386                if (needsInit) {
387                  AddChangeAction(DbSchemaChangeActionType.ColumnAddedInit, col);
388                  AddChangeAction(DbSchemaChangeActionType.ColumnAddComplete, col);
389                }
390            }//foreach col
391            break;
392        }//switch
393
394        //Keys and constraints
395        foreach (var key in newT.Keys) {
396          if (key.KeyType.IsSet(KeyType.Auto))
397            continue; 
398          if (key.ChangeType == DbObjectChange.New)
399            AddKeyAddAction(key);
400        }
401        if (_useRefIntegrity)
402          foreach (var refc in newT.RefConstraints)
403            if (refc.ChangeType == DbObjectChange.New)
404              AddChangeAction(DbSchemaChangeActionType.RefConstraintAdd, refc);
405      }//foreach newT
406
407    }//method
408
409    private void BuildStoredProcActions() {
410      if (!_useStoredProcs)
411        return;
412      var driver = _newModel.Driver;
413      //Delete old routines in active schema with no match
414      foreach (var oldCmd in _oldModel.Commands) {
415        if (oldCmd.Flags.IsSet(DbCommandFlags.NotGenerated)) // do not drop SPs that were not generated by VITA
416          continue; 
417        if (IsActive(oldCmd.Schema) && oldCmd.Peer == null) 
418          AddChangeAction(DbSchemaChangeActionType.RoutineDrop, oldCmd.FullCommandName);
419      }
420      foreach(var newCmd in _newModel.Commands) {
421        if (newCmd.Peer != null && driver.StoredProceduresMatch(newCmd, newCmd.Peer)) 
422          continue; 
423        //no match
424        if (newCmd.Peer != null)
425          AddChangeAction(DbSchemaChangeActionType.RoutineDrop, newCmd.FullCommandName);
426        AddChangeAction(DbSchemaChangeActionType.RoutineAdd, newCmd);
427      }
428    }
429
430    //Note that name change is not reflected in ChangeStatus - it would be still None if only Name changed, but nothing else did not. 
431    private void BuildRenameActions() {
432      bool tableNamesIgnoreCase = _newModel.Settings.Options.IsSet(DbOptions.IgnoreTableNamesCase);
433      foreach (var oldT in _oldModel.Tables) {
434        if (oldT.Peer == null) continue;
435        if (string.Compare(oldT.TableName, oldT.Peer.TableName, tableNamesIgnoreCase) != 0)
436          AddChangeAction(DbSchemaChangeActionType.TableRename, oldT);
437        foreach (var c in oldT.Columns)
438          if (c.Peer != null && c.ColumnName != c.Peer.ColumnName)
439            AddChangeAction(DbSchemaChangeActionType.ColumnRename, c);
440      }
441    }
442
443    private DbSchemaUpdateAction AddKeyAddAction(DbKeyInfo key) {
444      var actionType = key.KeyType == KeyType.PrimaryKey ? DbSchemaChangeActionType.PrimaryKeyAdd : DbSchemaChangeActionType.KeyAdd;
445      return AddChangeAction(actionType, key); 
446    }
447    private DbSchemaUpdateAction AddChangeAction(DbSchemaChangeActionType actionType, object objectInfo) {
448      var action = new DbSchemaUpdateAction(actionType, objectInfo);
449      _changes.Actions.Add(action);
450      return action;
451    }
452    #endregion
453
454
455
456  } //class
457
458}//namespace