/// <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();
}