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])); }