/// <summary>The process foreign key references.</summary> /// <param name="level">The level.</param> /// <param name="tablesList">The tables list.</param> /// <param name="table">The table.</param> /// <exception cref="InvalidOperationException"></exception> private void ProcessForeignKeyReferences(int level, List<DbModelTable> tablesList, DbModelTable table) { if (tablesList.Contains(table)) { return; } // recursive insurance ;-) level++; if (level > 1000) { throw new InvalidOperationException(string.Format("FK processor exceeded recursive level of 1000 at '{0}'.", table.Name)); } // if there are FK refs, add the refered tables first if (table.ForeignKeyColumns.Count > 0) { // if the table is not already in the list.... foreach (DbModelColumn fkColumn in table.ForeignKeyColumns) { // ensure its not a self referencing table if (fkColumn.ForeignKeyReference.ReferenceTable != table) { ProcessForeignKeyReferences(++level, tablesList, fkColumn.ForeignKeyReference.ReferenceTable); } } // now add the table if not in the list yet if (!tablesList.Contains(table)) { tablesList.Add(table); } } }
public void nvelocity_with_dbmodel2() { DbModelInstance model = new DbModelInstance(); model.ConnectionString = "conn str"; model.ProviderName = "sql.foo"; DbModelTable table = new DbModelTable {Name = "MyTable"}; model.Add(table); table.Add(new DbModelColumn {Name = "ID"}); table.Add(new DbModelColumn {Name = "FirstName"}); Dictionary<string, object> items = new Dictionary<string, object>(); items.Add("model", model); string template = @"Template Test ($num): ConnectionString: ""$model.ConnectionString"" ProviderName: ""$model.ProviderName"" #foreach ($table in $model.Tables) $table.Name #foreach ($c in $table.Columns) * $c.Name #end #end "; string s = _formatter.Format(template, items); Console.WriteLine(s); Assert.That(s.Length, Is.GreaterThan(0)); }
/// <summary>The get foreign key references for table.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="dbTable">The db table.</param> protected override void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { ForeignKeyInformationAvailable = true; try { using (var cmd = dbConn.CreateCommand()) { cmd.CommandText = string.Format( @"SELECT OBJECT_SCHEMA_NAME(f.parent_object_id) AS TableSchemaName, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, f.name AS ForeignKeyName, OBJECT_SCHEMA_NAME(f.referenced_object_id) AS ReferenceTableSchemaName, OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName, f.update_referential_action_desc, f.delete_referential_action_desc FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id WHERE OBJECT_SCHEMA_NAME(f.parent_object_id) = '{0}' AND OBJECT_NAME(f.parent_object_id) = '{1}' ", dbTable.Schema, dbTable.Name); cmd.CommandType = CommandType.Text; using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { dbTable.Constraints.Add(new DbModelConstraint { ConstraintTableSchema = (string)dr["TableSchemaName"], ConstraintTableName = (string)dr["TableName"], ColumnName = (string)dr["ColumnName"], ConstraintName = (string)dr["ForeignKeyName"], UniqueConstraintTableSchema = (string)dr["ReferenceTableSchemaName"], UniqueConstraintTableName = (string)dr["ReferenceTableName"], UniqueColumnName = (string)dr["ReferenceColumnName"], UpdateRule = (string)dr["update_referential_action_desc"], DeleteRule = (string)dr["delete_referential_action_desc"], }); } } } } catch (SqlException) { ForeignKeyInformationAvailable = false; } }
/// <summary>The write delete.</summary> /// <param name="writer">The writer.</param> /// <param name="tableOrView">The table or view.</param> public virtual void WriteDelete(TextWriter writer, DbModelTable tableOrView) { writer.WriteLine("DELETE FROM"); writer.Write("\t"); writer.WriteLine(MakeSqlFriendly(tableOrView.FullName)); writer.WriteLine("WHERE"); for (int i = 0; i < tableOrView.PrimaryKeyColumns.Count; i++) { var column = tableOrView.PrimaryKeyColumns[i]; writer.Write("\t{0} = ", MakeSqlFriendly(column.Name)); if (i < tableOrView.PrimaryKeyColumns.Count - 1) { writer.Write(" /*value:{0}*/ AND", column.Name); writer.WriteLine(); } else { writer.Write("/*value:{0}*/", column.Name); } } writer.WriteLine(); }
/// <summary>The build tool tip.</summary> /// <param name="table">The table.</param> /// <param name="column">The column.</param> /// <returns>The build tool tip.</returns> private string BuildToolTip(DbModelTable table, DbModelColumn column) { string friendlyColumnName = Utility.MakeSqlFriendly(column.Name); string toolTip = table.FullName + "." + friendlyColumnName; if (column.IsKey) { toolTip += "; Primary Key"; } if (column.IsAutoIncrement) { toolTip += "; Auto*"; } if (column.ForeignKeyReference != null) { toolTip += string.Format("; FK -> {0}.{1}", column.ForeignKeyReference.ReferenceTable.FullName, column.ForeignKeyReference.ReferenceColumn.Name); } if (column.IsReadOnly) { toolTip += "; Read Only"; } return toolTip; }
/// <summary>The write update.</summary> /// <param name="writer">The writer.</param> /// <param name="tableOrView">The table or view.</param> public virtual void WriteUpdate(TextWriter writer, DbModelTable tableOrView) { writer.Write("UPDATE "); writer.WriteLine(MakeSqlFriendly(tableOrView.FullName)); writer.WriteLine("SET"); // get all columns that are "writable" excluding keys that are not auto generated var writableColumns = tableOrView.Columns.FindAll(c => c.IsWritable && !c.IsKey); for (int i = 0; i < writableColumns.Count; i++) { var column = writableColumns[i]; writer.Write("\t{0} = {1}", MakeSqlFriendly(column.Name), column.DbType.ToDDLValue(column.Nullable)); if (i < writableColumns.Count - 1) { writer.Write(","); writer.WriteLine(); } } writer.WriteLine(); writer.WriteLine("WHERE"); for (int i = 0; i < tableOrView.PrimaryKeyColumns.Count; i++) { var column = tableOrView.PrimaryKeyColumns[i]; writer.Write("\t{0} = ", MakeSqlFriendly(column.Name)); if (i < tableOrView.PrimaryKeyColumns.Count - 1) { writer.Write(" /*value:{0},{1}*/ AND", column.Name, column.DbType.Summary); writer.WriteLine(); } else { writer.Write("/*value:{0},{1}*/", column.Name, column.DbType.Summary); } } writer.WriteLine(); }
/// <summary>The write select count.</summary> /// <param name="writer">The writer.</param> /// <param name="tableOrView">The table or view.</param> public virtual void WriteSelectCount(TextWriter writer, DbModelTable tableOrView) { writer.Write("SELECT COUNT(*) FROM {0}", MakeSqlFriendly(tableOrView.FullName)); writer.WriteLine(); }
/// <summary>The process foreign key references for table.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="dbTable">The db table.</param> protected override void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { // todo - check GetGroupForeingKeys foreach (DbModelConstraint constraint in dbTable.Constraints) { var column = dbTable.Columns.Find(c => c.Name == constraint.ColumnName); var refTable = dbTable.ParentDb.FindTable( Utility.RenderSafeSchemaObjectName(constraint.UniqueConstraintTableSchema, constraint.UniqueConstraintTableName)); var refColumn = refTable.Columns.Find(c => c.Name == constraint.UniqueColumnName); DbModelForeignKeyReference fk = new DbModelForeignKeyReference(column, refTable, refColumn); fk.UpdateRule = constraint.UpdateRule; fk.DeleteRule = constraint.DeleteRule; column.ForeignKeyReference = fk; } }
/// <summary>The get data type name for column.</summary> /// <param name="dbTable">The db table.</param> /// <param name="schemaTableKeyInfo">The schema table key info.</param> /// <param name="columnRow">The column row.</param> /// <returns>The get data type name for column.</returns> protected override string GetDataTypeNameForColumn(DbModelTable dbTable, DataTable schemaTableKeyInfo, DataRow columnRow) { return SafeGetString(columnRow, "ProviderType"); }
/// <summary>Gets a database object model that represents the items defined by the <paramref name="connection"/>.</summary> /// <param name="connection">The connection string.</param> /// <returns>An instance of <see cref="DbModelInstance"/> describing the database.</returns> public virtual DbModelInstance GetDbObjectModel(string connection) { _connection = connection; DbModelInstance model = new DbModelInstance(); DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName); using (DbConnection dbConn = factory.CreateConnection()) { dbConn.ConnectionString = connection; dbConn.Open(); DataTable tables = dbConn.GetSchema("Tables"); Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn); model.Types = dbTypes; model.ProviderName = ProviderName; model.ConnectionString = _connection; DataView tablesDV = new DataView(tables, "TABLE_TYPE='TABLE' OR TABLE_TYPE='BASE TABLE'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows); foreach (DataRowView row in tablesDV) { string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA"); string tableName = SafeGetString(row.Row, "TABLE_NAME"); DbModelTable dbTable = new DbModelTable {Schema = schemaName, Name = tableName}; model.Add(dbTable); DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName); GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes); } DataView viewsDV = new DataView(tables, "TABLE_TYPE='VIEW'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows); foreach (DataRowView row in viewsDV) { string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA"); string tableName = SafeGetString(row.Row, "TABLE_NAME"); DbModelView dbTable = new DbModelView {Schema = schemaName, Name = tableName}; model.Add(dbTable); DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName); GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes); } // build FK relationships foreach (DbModelTable table in model.Tables) { GetForeignKeyReferencesForTable(dbConn, table); ProcessForeignKeyReferencesForTable(dbConn, table); } // build FK relationships foreach (DbModelView view in model.Views) { GetForeignKeyReferencesForTable(dbConn, view); ProcessForeignKeyReferencesForTable(dbConn, view); } } return model; }
/// <summary>The process foreign key references for table.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="dbTable">The db table.</param> protected virtual void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { }
/// <summary>The get foreign key references for table.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="dbTable">The db table.</param> protected virtual void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { // foreach (DbModelColumn column in dbTable.Columns) // { // // KF info for DB's varies widley, needs to be implemented by derived class // column.ForeignKeyReference = DbModelForeignKeyReference.NullForeignKeyReference; // } }
/// <summary>The get data type name for column.</summary> /// <param name="dbTable">The db table.</param> /// <param name="schemaTableKeyInfo">The schema table key info.</param> /// <param name="columnRow">The column row.</param> /// <returns>The get data type name for column.</returns> protected virtual string GetDataTypeNameForColumn(DbModelTable dbTable, DataTable schemaTableKeyInfo, DataRow columnRow) { return SafeGetString(columnRow, "DataTypeName"); }
/// <summary>The get columns for table.</summary> /// <param name="dbTable">The db table.</param> /// <param name="schemaTableKeyInfo">The schema table key info.</param> /// <param name="dbTypes">The db types.</param> protected virtual void GetColumnsForTable(DbModelTable dbTable, DataTable schemaTableKeyInfo, Dictionary<string, DbModelType> dbTypes) { if (schemaTableKeyInfo == null) { return; } foreach (DataRow columnRow in schemaTableKeyInfo.Rows) { if (SafeGetBool(columnRow, "IsHidden")) { continue; } string columnName = SafeGetString(columnRow, "ColumnName"); string dataType = GetDataTypeNameForColumn(dbTable, schemaTableKeyInfo, columnRow); // note - need a better work around for columns missing the data type info (e.g. access) if (string.IsNullOrEmpty(dataType)) { // try using the "ProviderDbType" to match string providerDbType = SafeGetString(columnRow, "ProviderType"); foreach (var type in dbTypes.Values) { if (type.ProviderDbType == providerDbType) { dataType = type.Name; break; } } } DbModelType dbType = DbModelType.Create( dbTypes, dataType, SafeGetInt(columnRow, "ColumnSize"), SafeGetInt(columnRow, "Precision"), SafeGetInt(columnRow, "Scale"), SafeGetString(columnRow, "DataType")); // todo - FK info DbModelColumn dbColumn = new DbModelColumn { Name = columnName, // Name = MakeSqlFriendly(columnName), Nullable = SafeGetBool(columnRow, "AllowDBNull"), IsKey = SafeGetBool(columnRow, "IsKey"), IsUnique = SafeGetBool(columnRow, "IsUnique"), IsRowVersion = SafeGetBool(columnRow, "IsRowVersion"), IsIdentity = SafeGetBool(columnRow, "IsIdentity"), IsAutoIncrement = SafeGetBool(columnRow, "IsAutoIncrement"), IsReadOnly = SafeGetBool(columnRow, "IsReadOnly"), DbType = dbType, }; dbTable.Add(dbColumn); } }
/// <summary>The add.</summary> /// <param name="table">The table.</param> public virtual void Add(DbModelTable table) { table.ParentDb = this; _tables.Add(table); }
/// <summary>The create tree nodes.</summary> /// <param name="table">The table.</param> private void CreateTreeNodes(DbModelTable table) { TreeNode tableNode = new TreeNode(table.FullName); tableNode.Name = table.FullName; tableNode.ImageKey = table.ObjectType; tableNode.SelectedImageKey = table.ObjectType; tableNode.ContextMenuStrip = TableNodeContextMenuStrip; tableNode.Tag = table; foreach (DbModelColumn column in table.Columns) { string friendlyColumnName = Utility.MakeSqlFriendly(column.Name); TreeNode columnNode = new TreeNode(friendlyColumnName); columnNode.Name = column.Name; string imageKey = BuildImageKey(column); columnNode.ImageKey = imageKey; columnNode.SelectedImageKey = imageKey; columnNode.ContextMenuStrip = ColumnNameContextMenuStrip; columnNode.Tag = column; columnNode.Text = GetSummary(column); string toolTip = BuildToolTip(table, column); columnNode.ToolTipText = toolTip; tableNode.Nodes.Add(columnNode); } switch (table.ObjectType) { case ObjectTypes.Table: _tablesNode.Nodes.Add(tableNode); break; case ObjectTypes.View: _viewsNode.Nodes.Add(tableNode); break; } }
/// <summary>Initializes a new instance of the <see cref="DbModelForeignKeyReference"/> class.</summary> /// <param name="owningColumn">The owning column.</param> /// <param name="fkTable">The fk table.</param> /// <param name="fkColumn">The fk column.</param> public DbModelForeignKeyReference(DbModelColumn owningColumn, DbModelTable fkTable, DbModelColumn fkColumn) { OwningColumn = owningColumn; ReferenceTable = fkTable; ReferenceColumn = fkColumn; }
private void DisplayTableDetails(DbModelTable[] tablesList) { foreach (DbModelTable table in tablesList) { Console.WriteLine(string.Format("{0} (fks:{1})", table.Name, table.ForeignKeyColumns.Count)); if (table.ForeignKeyColumns.Count > 0) { foreach (DbModelColumn fk in table.ForeignKeyColumns) { Console.WriteLine(" (FK --> {0}.{1})", fk.ForeignKeyReference.ReferenceTable.Name, fk.ForeignKeyReference.ReferenceColumn.Name); } } } }
/// <summary>The get foreign key references for table.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="dbTable">The db table.</param> protected override void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { ForeignKeyInformationAvailable = true; try { using (var cmd = dbConn.CreateCommand()) { cmd.CommandText = string.Format( @"SELECT KCU1.TABLE_NAME AS FK_TABLE_NAME, KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME, KCU1.COLUMN_NAME AS FK_COLUMN_NAME, KCU2.TABLE_NAME AS UQ_TABLE_NAME, KCU2.CONSTRAINT_NAME AS UQ_CONSTRAINT_NAME, KCU2.COLUMN_NAME AS UQ_COLUMN_NAME, RC.UPDATE_RULE, RC.DELETE_RULE, KCU2.ORDINAL_POSITION AS UQ_ORDINAL_POSITION, KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME WHERE KCU1.TABLE_NAME = '{0}' ORDER BY FK_TABLE_NAME, FK_CONSTRAINT_NAME, FK_ORDINAL_POSITION ", dbTable.Name); cmd.CommandType = CommandType.Text; using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { dbTable.Constraints.Add(new DbModelConstraint { ConstraintTableName = dr.GetString(0), ConstraintName = dr.GetString(1), ColumnName = dr.GetString(2), UniqueConstraintTableName = dr.GetString(3), UniqueConstraintName = dr.GetString(4), UniqueColumnName = dr.GetString(5), UpdateRule = dr.GetString(6), DeleteRule = dr.GetString(7) }); } } } } catch (DbException) { ForeignKeyInformationAvailable = false; } }
/// <summary>The write insert.</summary> /// <param name="writer">The writer.</param> /// <param name="tableOrView">The table or view.</param> public virtual void WriteInsert(TextWriter writer, DbModelTable tableOrView) { writer.Write("INSERT INTO "); writer.Write(MakeSqlFriendly(tableOrView.FullName)); if (InsertLineBreaksBetweenColumns) { writer.WriteLine(); writer.Write("\t"); } writer.Write("("); // get all columns that are "writable" including PKs that are not auto generated (unless specified) List<DbModelColumn> writableColumns = null; if (IncludeReadOnlyColumnsInExport) { writableColumns = tableOrView.Columns; } else { writableColumns = tableOrView.Columns.FindAll(c => c.IsWritable); } for (int i = 0; i < writableColumns.Count; i++) { var column = writableColumns[i]; writer.Write(MakeSqlFriendly(column.Name)); if (i < writableColumns.Count - 1) { if (InsertLineBreaksBetweenColumns) { writer.WriteLine(","); writer.Write("\t"); } else { writer.Write(", "); } } } writer.WriteLine(")"); writer.Write("VALUES"); if (InsertLineBreaksBetweenColumns) { writer.WriteLine(); writer.Write("\t"); } writer.Write("("); for (int i = 0; i < writableColumns.Count; i++) { var column = writableColumns[i]; writer.Write(column.DbType.ToDDLValue(column.Nullable)); if (IncludeComments) { writer.Write(" /*{0},{1}*/", column.Name, column.DbType.Summary); } if (i < writableColumns.Count - 1) { if (InsertLineBreaksBetweenColumns) { writer.WriteLine(","); writer.Write("\t"); } else { writer.Write(", "); } } } writer.WriteLine(")"); }
/// <summary>The query table names.</summary> /// <param name="dbConn">The db conn.</param> /// <param name="model">The model.</param> private void QueryTableNames(DbConnection dbConn, DbModelInstance model) { using (var cmd = dbConn.CreateCommand()) { cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE = N'TABLE'"; cmd.CommandType = CommandType.Text; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { DbModelTable table = new DbModelTable(); table.Name = (string)reader["table_name"]; model.Add(table); } } } }
/// <summary>The write select.</summary> /// <param name="writer">The writer.</param> /// <param name="tableOrView">The table or view.</param> public virtual void WriteSelect(TextWriter writer, DbModelTable tableOrView) { writer.Write("SELECT"); writer.WriteLine(); for (int i = 0; i < tableOrView.Columns.Count; i++) { writer.Write("\t"); writer.Write(MakeSqlFriendly(tableOrView.Columns[i].Name)); if (i < tableOrView.Columns.Count - 1) { writer.Write(","); writer.WriteLine(); } } writer.WriteLine(); writer.Write("FROM {0}", MakeSqlFriendly(tableOrView.FullName)); writer.WriteLine(); }