public void CanAddSingleKey()
{
var collection = new ForeignKeyCollection();
var key = new ForeignKey(new ObjectName("", "Table1"), new[] {"Field1"}, new ObjectName("", "Table2"),
new[] {"Field2"});
collection.Add(key);
Assert.That(collection, Contains.Item(key));
}
public void CanNotAddMultipleForeignKeysToTheSameTable()
{
var collection = new ForeignKeyCollection();
var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"),new[] { "Field1" });
var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable1"),new[] { "Field2" });
collection.Add(key1);
Assert.Throws<ArgumentException>(() => collection.Add(key2));
}
private string MakeJoinText(ForeignKey foreignKey)
{
var builder = new StringBuilder(JoinKeyword);
builder.AppendFormat(" JOIN {0} ON (", _schema.QuoteObjectName(foreignKey.DetailTable));
builder.Append(FormatJoinExpression(foreignKey, 0));
for (int i = 1; i < foreignKey.Columns.Length; i++)
{
builder.Append(" AND ");
builder.Append(FormatJoinExpression(foreignKey, i));
}
builder.Append(")");
return builder.ToString();
}
public void CanAddMultipleForeignKeys()
{
var collection = new ForeignKeyCollection();
var key1 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field1" }, new ObjectName("", "MasterTable1"),new[] { "Field1" });
var key2 = new ForeignKey(new ObjectName("", "DetailTable1"), new[] { "Field2" }, new ObjectName("", "MasterTable2"),new[] { "Field2" });
collection.Add(key1);
collection.Add(key2);
Assert.That(collection,Contains.Item(key1));
Assert.That(collection,Contains.Item(key2));
}
private string MakeJoinText(Table rightTable, ForeignKey foreignKey)
{
var builder = new StringBuilder(JoinKeyword);
builder.AppendFormat(" JOIN {0} ON (", rightTable.QualifiedName);
builder.Append(FormatJoinExpression(foreignKey, 0));
for (int i = 1; i < foreignKey.Columns.Length; i++)
{
builder.Append(" AND ");
builder.Append(FormatJoinExpression(foreignKey, i));
}
builder.Append(")");
return builder.ToString();
}
public IEnumerable<ForeignKey> GetForeignKeys(Table table)
{
var groups = SelectToDataTable("pragma foreign_key_list(" + table.ActualName + ");")
.AsEnumerable()
.GroupBy(row => row.Field<string>("table"));
foreach(var group in groups)
{
var masterName = new ObjectName(null, group.First().Field<string>("table"));
var detailName = new ObjectName(null, table.ActualName);
var key = new ForeignKey(detailName, group.Select(row => row.Field<string>("to")), masterName,
group.Select(row => row.Field<string>("from")));
yield return key;
}
}
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex)
{
return string.Format("{0}.{1} = {2}.{3}", _schema.QuoteObjectName(foreignKey.MasterTable), _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
}
public IEnumerable<ForeignKey> GetForeignKeys(Table table)
{
var groups = GetSchema( "FOREIGNKEYS", new[] { null, null, table.ActualName } )
.AsEnumerable()
.GroupBy( row => new
{
CatalogName = row.Field<string>( "FKEY_TO_CATALOG" ),
SchemaName = row.Field<string>( "FKEY_TO_SCHEMA" ),
TableName = row.Field<string>( "FKEY_TO_TABLE" )
} );
foreach (var group in groups)
{
var masterName = new ObjectName( null, group.Key.TableName );
var detailName = new ObjectName(null, table.ActualName);
var key = new ForeignKey(
detailName,
group.Select(row => row.Field<string>("FKEY_TO_COLUMN")),
masterName,
group.Select(row => row.Field<string>("FKEY_FROM_COLUMN")));
yield return key;
}
}
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex, Table rightTable, string alias)
{
if (rightTable.ActualName == foreignKey.MasterTable.Name &&
rightTable.Schema == foreignKey.MasterTable.Schema)
{
var rightTableName = string.IsNullOrWhiteSpace(alias)
? _schema.QuoteObjectName(foreignKey.MasterTable)
: _schema.QuoteObjectName(alias);
return string.Format("{0}.{1} = {2}.{3}",
rightTableName, _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex])
);
}
var leftTableName = string.IsNullOrWhiteSpace(alias)
? _schema.QuoteObjectName(foreignKey.DetailTable)
: _schema.QuoteObjectName(alias);
return string.Format("{0}.{1} = {2}.{3}", _schema.QuoteObjectName(foreignKey.MasterTable), _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
leftTableName, _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
}
private SimpleExpression CreateJoinExpression(ObjectReference table, ForeignKey foreignKey, int columnIndex)
{
var masterObjectReference = ObjectReference.FromStrings(foreignKey.MasterTable.Name,
foreignKey.UniqueColumns[columnIndex]);
var detailObjectReference = ObjectReference.FromStrings(foreignKey.DetailTable.Name,
foreignKey.Columns[columnIndex]);
if (!string.IsNullOrWhiteSpace(table.GetAlias()))
{
if (detailObjectReference.GetOwner().GetName() == table.GetName())
detailObjectReference = new ObjectReference(detailObjectReference.GetName(), table);
else if (masterObjectReference.GetOwner().GetName() == table.GetName())
masterObjectReference = new ObjectReference(masterObjectReference.GetName(), table);
}
return masterObjectReference == detailObjectReference;
}
private SimpleExpression MakeJoinExpression(ObjectReference table, ForeignKey foreignKey)
{
var expression = CreateJoinExpression(table, foreignKey, 0);
for (int i = 1; i < foreignKey.Columns.Length; i++)
{
expression = expression && CreateJoinExpression(table, foreignKey, i);
}
return expression;
}
private string MakeJoinText(Table rightTable, string alias, ForeignKey foreignKey, JoinType joinType)
{
var builder = new StringBuilder(JoinKeywordFor(joinType));
builder.AppendFormat(" JOIN {0}", rightTable.QualifiedName);
if (!string.IsNullOrWhiteSpace(alias)) builder.Append(" " + _schema.QuoteObjectName(alias));
builder.Append(" ON (");
builder.Append(FormatJoinExpression(foreignKey, 0, rightTable, alias));
for (int i = 1; i < foreignKey.Columns.Length; i++)
{
builder.Append(" AND ");
builder.Append(FormatJoinExpression(foreignKey, i, rightTable, alias));
}
builder.Append(")");
return builder.ToString();
}
public TableForeignKeyPair(Table table, ForeignKey foreignKey)
{
Table = table;
ForeignKey = foreignKey;
}
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex, string alias)
{
var leftTable = string.IsNullOrWhiteSpace(alias)
? _schema.QuoteObjectName(foreignKey.MasterTable)
: _schema.QuoteObjectName(alias);
return string.Format("{0}.{1} = {2}.{3}", leftTable, _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
}
