PageRenderTime 41ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

/source/library/DatabaseCop/Dependencies.cs

https://bitbucket.org/VahidN/interlace
C# | 363 lines | 255 code | 80 blank | 28 comment | 39 complexity | 4c53d82123c96520d13bcb04e53f5f00 MD5 | raw file
  1. #region Using Directives and Copyright Notice
  2. // Copyright (c) 2007-2010, Computer Consultancy Pty Ltd
  3. // All rights reserved.
  4. //
  5. // Redistribution and use in source and binary forms, with or without
  6. // modification, are permitted provided that the following conditions are met:
  7. // * Redistributions of source code must retain the above copyright
  8. // notice, this list of conditions and the following disclaimer.
  9. // * Redistributions in binary form must reproduce the above copyright
  10. // notice, this list of conditions and the following disclaimer in the
  11. // documentation and/or other materials provided with the distribution.
  12. // * Neither the name of the Computer Consultancy Pty Ltd nor the
  13. // names of its contributors may be used to endorse or promote products
  14. // derived from this software without specific prior written permission.
  15. //
  16. // THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
  17. // AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  18. // IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  19. // ARE DISCLAIMED. IN NO EVENT SHALL COMPUTER CONSULTANCY PTY LTD BE LIABLE
  20. // FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
  21. // DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
  22. // SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
  23. // CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
  24. // LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
  25. // OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
  26. // DAMAGE.
  27. using System;
  28. using System.Collections.Generic;
  29. using System.Data.SqlClient;
  30. using System.IO;
  31. using System.Runtime.Serialization.Formatters.Binary;
  32. using System.Text;
  33. using System.Text.RegularExpressions;
  34. using Interlace.Collections;
  35. using Interlace.Utilities;
  36. using DatabaseCop.RuleHelpers;
  37. #endregion
  38. namespace DatabaseCop
  39. {
  40. public static class Dependencies
  41. {
  42. static IEnumerable<Table> EdgeGetter(Table table)
  43. {
  44. foreach (ForeignKeyConstraint constraint in table.ForeignKeyConstraints)
  45. {
  46. if (constraint.IsDisabled ?? false) continue;
  47. yield return constraint.UniqueConstraint.Table;
  48. }
  49. }
  50. public static void CheckDependencies(Database database)
  51. {
  52. ICollection<Table> ordered = TopologicalSort.Sort<Table>(database.TablesByName.Values, EdgeGetter);
  53. }
  54. public static void CreateFilteredDependenciesGraph(Database database, Table startTable, Plan plan)
  55. {
  56. Set<Table> visited = new Set<Table>();
  57. Stack<Table> seen = new Stack<Table>();
  58. seen.Push(startTable);
  59. // The relations that, as a precondition of the delete, must not exist:
  60. while (seen.Count > 0)
  61. {
  62. Table table = seen.Pop();
  63. visited.UnionUpdate(table);
  64. foreach (ForeignKeyConstraint constraint in table.ForeignKeyDependencies)
  65. {
  66. if (plan.IsConstraintExcludedFromDependencyDiagram(constraint)) continue;
  67. if (!visited.Contains(constraint.Table))
  68. {
  69. seen.Push(constraint.Table);
  70. }
  71. }
  72. }
  73. CreateDependenciesGraph(database, visited);
  74. }
  75. public static void CreateDependenciesGraph(Database database)
  76. {
  77. CreateDependenciesGraph(database, null);
  78. }
  79. public static void CreateDependenciesGraph(Database database, Set<Table> tableSubset)
  80. {
  81. using (StreamWriter writer = new StreamWriter("C:\\test.viz"))
  82. {
  83. writer.WriteLine("digraph database {");
  84. writer.WriteLine(" node [fontname=Helvetica];");
  85. writer.WriteLine(" node [shape=none, fontcolor=white, style=filled];");
  86. foreach (Table table in database.TablesByName.Values)
  87. {
  88. if (tableSubset != null && !tableSubset.Contains(table)) continue;
  89. ParsedTableIdentifier tableIdentifier = new ParsedTableIdentifier(table.Name.Name);
  90. string nodeStyle = "black";
  91. if (tableIdentifier.Prefix == "common") nodeStyle = "fillcolor=yellow4";
  92. if (tableIdentifier.Prefix == "courier") nodeStyle = "fillcolor=darkolivegreen";
  93. if (tableIdentifier.Prefix == "haulage") nodeStyle = "fillcolor=darkgoldenrod4";
  94. string nodeSizing = "";
  95. int connectionCount = table.ForeignKeyConstraintsByName.Count;
  96. foreach (TableConstraint constraint in table.TableConstraints)
  97. {
  98. connectionCount += constraint.RelatedForeignKeysByName.Count;
  99. }
  100. double width = 1.5 + 1.5 * connectionCount / 3.0;
  101. double height = 0.5 + 0.5 * connectionCount / 3.0;
  102. if (connectionCount > 3) nodeSizing = string.Format(", width={0:0.0}, height={1:0.0}", width, height);
  103. writer.WriteLine(" \"{0}\" [{1}{2}]", tableIdentifier.Value, nodeStyle, nodeSizing);
  104. foreach (ForeignKeyConstraint foreignKey in table.ForeignKeyConstraints)
  105. {
  106. if (tableSubset != null && !tableSubset.Contains(foreignKey.Table)) continue;
  107. ParsedTableIdentifier foreignKeyTableName = new ParsedTableIdentifier(foreignKey.Table.Name.Name);
  108. ParsedTableIdentifier primaryKeyTableName = new ParsedTableIdentifier(foreignKey.UniqueConstraint.Table.Name.Name);
  109. string edgeStyle = "color=gray15";
  110. string arrowStyle = "diamondnormal";
  111. if (foreignKey.IsOneToOne) arrowStyle = "normal";
  112. if (foreignKey.IsDisabled ?? false) edgeStyle = "style=dotted, color=gray15";
  113. if (foreignKey.UpdateRule == ForeignKeyRule.Cascade) edgeStyle = "color=red4";
  114. writer.WriteLine(" \"{0}\" -> \"{1}\" [{2}, arrowhead={3}]",
  115. foreignKeyTableName.Value, primaryKeyTableName.Value, edgeStyle, arrowStyle);
  116. }
  117. }
  118. writer.WriteLine("}");
  119. writer.Close();
  120. }
  121. }
  122. public static string IdentifyCompany(Database database, SqlConnection connection)
  123. {
  124. if (database.TablesByName.ContainsKey(new ObjectName("dbo", "Company")))
  125. {
  126. Table companyTable = database.TablesByName[new ObjectName("dbo", "Company")];
  127. if (companyTable.Columns.ContainsKey("Comp_ID") && companyTable.Columns.ContainsKey("Comp_Name"))
  128. {
  129. using (SqlCommand command = new SqlCommand())
  130. {
  131. command.Connection = connection;
  132. command.CommandText =
  133. "SELECT TOP 1 Comp_Name FROM dbo.Company ORDER BY Comp_ID ASC";
  134. using (SqlDataReader reader = command.ExecuteReader())
  135. {
  136. if (reader.Read() && !reader.IsDBNull(0))
  137. {
  138. return Capitalisation.ToNameCase(reader.GetString(0)).Trim();
  139. }
  140. }
  141. }
  142. }
  143. }
  144. return "Unknown Company";
  145. }
  146. public static string IdentifyLastUseDate(Database database, SqlConnection connection)
  147. {
  148. if (database.TablesByName.ContainsKey(new ObjectName("dbo", "Booking_1")))
  149. {
  150. Table bookingTable = database.TablesByName[new ObjectName("dbo", "Booking_1")];
  151. if (bookingTable.Columns.ContainsKey("Job_BookingTime"))
  152. {
  153. using (SqlCommand command = new SqlCommand())
  154. {
  155. command.Connection = connection;
  156. command.CommandText =
  157. "SELECT MAX(Job_BookingTime) FROM dbo.Booking_1";
  158. using (SqlDataReader reader = command.ExecuteReader())
  159. {
  160. if (reader.Read() && !reader.IsDBNull(0))
  161. {
  162. return reader.GetDateTime(0).ToString("d MMMM yyyy");
  163. }
  164. }
  165. }
  166. }
  167. }
  168. return "Unknown Date";
  169. }
  170. static Regex _invalidFileNameComponent = new Regex("[^-_(), A-Za-z0-9]+");
  171. static string GetFilename(Database database, SqlConnection connection, string extension)
  172. {
  173. string identity = IdentifyCompany(database, connection);
  174. string date = IdentifyLastUseDate(database, connection);
  175. string fileNameBase = string.Format("Database Dump, {0} ({1}, {2})",
  176. database.Name, identity, date);
  177. string filteredBase = _invalidFileNameComponent.Replace(fileNameBase, "");
  178. return string.Format("{0}.{1}", filteredBase, extension.TrimStart('.'));
  179. }
  180. public static void DumpSerialized(Database database, SqlConnection connection)
  181. {
  182. string fileName = GetFilename(database, connection, "databasecop");
  183. using (Stream stream = new FileStream(fileName, FileMode.Create))
  184. {
  185. BinaryFormatter formatter = new BinaryFormatter();
  186. formatter.Serialize(stream, database);
  187. }
  188. }
  189. public static void Dump(Database database, SqlConnection connection)
  190. {
  191. string fileName = GetFilename(database, connection, "txt");
  192. using (StreamWriter writer = new StreamWriter(fileName))
  193. {
  194. List<ObjectName> tableNames = new List<ObjectName>();
  195. tableNames.AddRange(database.TablesByName.Keys);
  196. tableNames.Sort();
  197. foreach (ObjectName tableName in tableNames)
  198. {
  199. Table table = database.TablesByName[tableName];
  200. writer.WriteLine("table \"{0}\"", table.Name);
  201. writer.WriteLine("{");
  202. foreach (Column column in table.Columns.Values)
  203. {
  204. writer.Write(" column \"{0}\" ", column.Name);
  205. writer.Write("{ ");
  206. writer.Write("type \"{0}\";", column.DataType);
  207. if (column.IsNullable) writer.Write(" nullable;");
  208. if (column.ColumnDefault != null)
  209. {
  210. writer.Write(" default \"{0}\";", column.ColumnDefault);
  211. }
  212. if (column.CharacterMaximumLength.HasValue)
  213. {
  214. writer.Write(" length {0};", column.CharacterMaximumLength.Value);
  215. }
  216. writer.WriteLine(" }");
  217. }
  218. writer.WriteLine("}");
  219. writer.WriteLine();
  220. }
  221. }
  222. }
  223. static List<Database> LoadDatabasesInCurrentDirectory()
  224. {
  225. List<Database> databases = new List<Database>();
  226. DirectoryInfo directory = new DirectoryInfo(Environment.CurrentDirectory);
  227. foreach (FileInfo file in directory.GetFiles("*.databasecop"))
  228. {
  229. using (FileStream stream = new FileStream(file.FullName, FileMode.Open, FileAccess.Read, FileShare.Read))
  230. {
  231. BinaryFormatter formatter = new BinaryFormatter();
  232. object databaseObject = formatter.Deserialize(stream);
  233. Database database = databaseObject as Database;
  234. if (database != null) databases.Add(database);
  235. }
  236. }
  237. return databases;
  238. }
  239. static string NormaliseColumnName(string columnName)
  240. {
  241. return columnName.ToLower();
  242. }
  243. internal static void CompareInCurrentDirectory()
  244. {
  245. List<Database> databases = LoadDatabasesInCurrentDirectory();
  246. // Find which tables are common to all databases:
  247. Set<ObjectName> allTables = new Set<ObjectName>();
  248. Set<ObjectName> commonTables = new Set<ObjectName>();
  249. if (databases.Count > 0) commonTables.UnionUpdate(databases[0].TablesByName.Keys);
  250. foreach (Database database in databases)
  251. {
  252. Set<ObjectName> tablesInThisDatabase = new Set<ObjectName>(database.TablesByName.Keys);
  253. allTables.UnionUpdate(tablesInThisDatabase);
  254. commonTables.IntersectionUpdate(tablesInThisDatabase);
  255. }
  256. Set<ObjectName> nonCommonTables = Set<ObjectName>.Difference(allTables, commonTables);
  257. Console.WriteLine(nonCommonTables);
  258. // Find which columns are common to all databases:
  259. foreach (ObjectName commonTableName in commonTables)
  260. {
  261. Set<string> allColumns = new Set<string>();
  262. Set<string> commonColumns = new Set<string>();
  263. if (databases.Count > 0)
  264. {
  265. Set<string> columns = new Set<string>(databases[0].TablesByName[commonTableName].Columns.Keys);
  266. commonColumns.UnionUpdate(columns.Map<string>(NormaliseColumnName));
  267. }
  268. foreach (Database database in databases)
  269. {
  270. Set<string> columnsInThisTable = new Set<string>(database.TablesByName[commonTableName].Columns.Keys).Map<string>(NormaliseColumnName);
  271. commonColumns.IntersectionUpdate(columnsInThisTable);
  272. allColumns.UnionUpdate(columnsInThisTable);
  273. }
  274. Set<string> nonCommonColumns = Set<string>.Difference(allColumns, commonColumns);
  275. if (nonCommonColumns.Count > 0)
  276. {
  277. Console.WriteLine(commonTableName.Name);
  278. Console.WriteLine(nonCommonColumns);
  279. }
  280. }
  281. }
  282. }
  283. }