public void Uses_comparer()
{
var namer = new CSharpUniqueNamer<TableModel>(t => t.Name);
var table1 = new TableModel { Name = "A B C" };
var table2 = new TableModel { Name = "A_B_C" };
Assert.Equal("A_B_C", namer.GetName(table1));
Assert.Equal("A_B_C1", namer.GetName(table2));
}
private static string ColumnKey(TableModel table, string columnName)
=> "[" + table.Name + "].[" + columnName + "]";
private void GetTables()
{
using (var command = _connection.CreateCommand())
{
command.CommandText =
"SELECT name FROM sqlite_master" +
" WHERE type = 'table'" +
" AND name <> 'sqlite_sequence'" +
$" AND name <> '{HistoryRepository.DefaultTableName}'";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetValueOrDefault<string>("name");
Logger.LogTrace(SqliteDesignStrings.FoundTable(name));
if (_tableSelectionSet.Allows(name))
{
var table = new TableModel
{
Database = _databaseModel,
Name = name
};
_databaseModel.Tables.Add(table);
_tables.Add(name, table);
}
else
{
Logger.LogTrace(SqliteDesignStrings.TableNotInSelectionSet(name));
}
}
}
}
}
public void Unique_index_foreign_key()
{
var table = new TableModel
{
Name = "Friends",
Columns =
{
IdColumn,
new ColumnModel { Name = "BuddyId", DataType = "long", IsNullable = false }
}
};
table.Indexes.Add(new IndexModel
{
IndexColumns = { new IndexColumnModel { Column = table.Columns.ElementAt(1) } },
IsUnique = true
});
table.ForeignKeys.Add(new ForeignKeyModel
{
Table = table,
PrincipalTable = table,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = table.Columns.ElementAt(1),
PrincipalColumn = table.Columns.ElementAt(0)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { table } }).FindEntityType("Friends");
var fk = Assert.Single(model.GetForeignKeys());
Assert.True(fk.IsUnique);
Assert.Equal(model.FindPrimaryKey(), fk.PrincipalKey);
}
public void Unique_index_composite_foreign_key()
{
var parentTable = new TableModel
{
Name = "Parent",
Columns =
{
new ColumnModel { Name = "Id_A", DataType = "long", PrimaryKeyOrdinal = 1 },
new ColumnModel { Name = "Id_B", DataType = "long", PrimaryKeyOrdinal = 2 }
}
};
var childrenTable = new TableModel
{
Name = "Children",
Columns =
{
IdColumn,
new ColumnModel { Name = "ParentId_A", DataType = "long" },
new ColumnModel { Name = "ParentId_B", DataType = "long" }
}
};
childrenTable.Indexes.Add(new IndexModel
{
IsUnique = true,
IndexColumns =
{
new IndexColumnModel { Column = childrenTable.Columns.ElementAt(1) },
new IndexColumnModel { Column = childrenTable.Columns.ElementAt(2) }
}
});
childrenTable.ForeignKeys.Add(new ForeignKeyModel
{
Table = childrenTable,
PrincipalTable = parentTable,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(1),
PrincipalColumn = parentTable.Columns.ElementAt(0)
},
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(2),
PrincipalColumn = parentTable.Columns.ElementAt(1)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
var parent = model.FindEntityType("Parent");
var children = model.FindEntityType("Children");
var fk = Assert.Single(children.GetForeignKeys());
Assert.True(fk.IsUnique);
Assert.Equal(parent.FindPrimaryKey(), fk.PrincipalKey);
}
public void It_loads_self_referencing_foreign_key()
{
var table = new TableModel
{
Name = "ItemsList",
Columns =
{
IdColumn,
new ColumnModel { Name = "ParentId", DataType = "long", IsNullable = false }
}
};
table.ForeignKeys.Add(new ForeignKeyModel
{
Table = table,
PrincipalTable = table,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = table.Columns.ElementAt(1),
PrincipalColumn = table.Columns.ElementAt(0)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { table } });
var list = model.FindEntityType("ItemsList");
Assert.NotEmpty(list.GetReferencingForeignKeys());
Assert.NotEmpty(list.GetForeignKeys());
var principalKey = list.FindForeignKeys(list.FindProperty("ParentId")).SingleOrDefault().PrincipalKey;
Assert.Equal("ItemsList", principalKey.DeclaringEntityType.Name);
Assert.Equal("Id", principalKey.Properties[0].Name);
}
public void It_logs_warning_for_bad_foreign_key()
{
var parentTable = new TableModel
{
Name = "Parent",
Columns =
{
IdColumn,
new ColumnModel { Name = "NotPkId", DataType = "long", PrimaryKeyOrdinal = null }
}
};
var childrenTable = new TableModel
{
Name = "Children",
Columns =
{
IdColumn,
new ColumnModel { Name = "ParentId", DataType = "long" }
}
};
childrenTable.ForeignKeys.Add(new ForeignKeyModel
{
Table = childrenTable,
PrincipalTable = parentTable,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(1),
PrincipalColumn = parentTable.Columns.ElementAt(1)
}
}
});
_factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
Assert.Contains("Warning: " +
RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
childrenTable.ForeignKeys.ElementAt(0).DisplayName, "NotPkId", "Parent"),
_logger.FullLog);
}
public void Unique_foreign_key()
{
var parentTable = new TableModel { Name = "Parent", Columns = { IdColumn } };
var childrenTable = new TableModel { Name = "Children", Columns = { IdColumn } };
childrenTable.ForeignKeys.Add(new ForeignKeyModel
{
Table = childrenTable,
PrincipalTable = parentTable,
OnDelete = ReferentialAction.NoAction,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(0),
PrincipalColumn = parentTable.Columns.ElementAt(0)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
var children = (EntityType)model.FindEntityType("Children");
var fk = Assert.Single(children.GetForeignKeys());
Assert.True(fk.IsUnique);
Assert.Equal(DeleteBehavior.Restrict, fk.DeleteBehavior);
}
public void Composite_foreign_key()
{
var parentTable = new TableModel
{
Name = "Parent",
Columns =
{
new ColumnModel { Name = "Id_A", DataType = "long", PrimaryKeyOrdinal = 1 },
new ColumnModel { Name = "Id_B", DataType = "long", PrimaryKeyOrdinal = 2 }
}
};
var childrenTable = new TableModel
{
Name = "Children",
Columns =
{
IdColumn,
new ColumnModel { Name = "ParentId_A", DataType = "long" },
new ColumnModel { Name = "ParentId_B", DataType = "long" }
}
};
childrenTable.ForeignKeys.Add(new ForeignKeyModel
{
Table = childrenTable,
PrincipalTable = parentTable,
OnDelete = ReferentialAction.SetNull,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(1),
PrincipalColumn = parentTable.Columns.ElementAt(0)
},
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(2),
PrincipalColumn = parentTable.Columns.ElementAt(1)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
var parent = (EntityType)model.FindEntityType("Parent");
var children = (EntityType)model.FindEntityType("Children");
Assert.NotEmpty(parent.GetReferencingForeignKeys());
var fk = Assert.Single(children.GetForeignKeys());
Assert.False(fk.IsUnique);
Assert.Equal(DeleteBehavior.SetNull, fk.DeleteBehavior);
var principalKey = fk.PrincipalKey;
Assert.Equal("Parent", principalKey.DeclaringEntityType.Name);
Assert.Equal("Id_A", principalKey.Properties[0].Name);
Assert.Equal("Id_B", principalKey.Properties[1].Name);
}
public void Indexes_and_alternate_keys()
{
var table = new TableModel
{
Name = "T",
Columns =
{
new ColumnModel { Name = "C1", DataType = "long", PrimaryKeyOrdinal = 1 },
new ColumnModel { Name = "C2", DataType = "long" },
new ColumnModel { Name = "C3", DataType = "long" }
}
};
table.Indexes.Add(new IndexModel
{
Name = "IDX_C1",
IndexColumns = { new IndexColumnModel { Column = table.Columns.ElementAt(0) } },
IsUnique = false
});
table.Indexes.Add(new IndexModel
{
Name = "UNQ_C2",
IndexColumns = { new IndexColumnModel { Column = table.Columns.ElementAt(1) } },
IsUnique = true
});
table.Indexes.Add(new IndexModel
{
Name = "IDX_C2_C1",
IndexColumns =
{
new IndexColumnModel { Column = table.Columns.ElementAt(1) },
new IndexColumnModel { Column = table.Columns.ElementAt(0) }
},
IsUnique = false
});
table.Indexes.Add(new IndexModel
{
/*Name ="UNQ_C3_C1",*/
IndexColumns =
{
new IndexColumnModel { Column = table.Columns.ElementAt(2) },
new IndexColumnModel { Column = table.Columns.ElementAt(0) }
},
IsUnique = true
});
var info = new DatabaseModel { Tables = { table } };
var entityType = (EntityType)_factory.Create(info).GetEntityTypes().Single();
Assert.Collection(entityType.GetIndexes(),
indexColumn1 =>
{
Assert.False(indexColumn1.IsUnique);
Assert.Equal("IDX_C1", indexColumn1.Relational().Name);
Assert.Same(entityType.FindProperty("C1"), indexColumn1.Properties.Single());
},
uniqueColumn2 =>
{
Assert.True(uniqueColumn2.IsUnique);
Assert.Same(entityType.FindProperty("C2"), uniqueColumn2.Properties.Single());
},
indexColumn2Column1 =>
{
Assert.False(indexColumn2Column1.IsUnique);
Assert.Equal(new[] { "C2", "C1" }, indexColumn2Column1.Properties.Select(c => c.Name).ToArray());
},
uniqueColumn3Column1 =>
{
Assert.True(uniqueColumn3Column1.IsUnique);
Assert.Equal(new[] { "C3", "C1" }, uniqueColumn3Column1.Properties.Select(c => c.Name).ToArray());
}
);
Assert.Collection(entityType.GetKeys().Where(k => !k.IsPrimaryKey()),
single =>
{
Assert.Equal("UNQ_C2", single.Relational().Name);
Assert.Same(entityType.FindProperty("C2"), single.Properties.Single());
},
composite => { Assert.Equal(new[] { "C3", "C1" }, composite.Properties.Select(c => c.Name).ToArray()); });
}
public void Foreign_key()
{
var parentTable = new TableModel { Name = "Parent", Columns = { IdColumn } };
var childrenTable = new TableModel
{
Name = "Children",
Columns =
{
IdColumn,
new ColumnModel { Name = "ParentId", DataType = "long", IsNullable = true }
}
};
childrenTable.ForeignKeys.Add(new ForeignKeyModel
{
Table = childrenTable,
PrincipalTable = parentTable,
OnDelete = ReferentialAction.Cascade,
Columns =
{
new ForeignKeyColumnModel
{
Ordinal = 1,
Column = childrenTable.Columns.ElementAt(1),
PrincipalColumn = parentTable.Columns.ElementAt(0)
}
}
});
var model = _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } });
var parent = (EntityType)model.FindEntityType("Parent");
var children = (EntityType)model.FindEntityType("Children");
Assert.NotEmpty(parent.GetReferencingForeignKeys());
var fk = Assert.Single(children.GetForeignKeys());
Assert.False(fk.IsUnique);
Assert.Equal(DeleteBehavior.Cascade, fk.DeleteBehavior);
var principalKey = fk.PrincipalKey;
Assert.Same(parent, principalKey.DeclaringEntityType);
Assert.Same(parent.GetProperties().First(), principalKey.Properties[0]);
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
protected override KeyBuilder VisitPrimaryKey(EntityTypeBuilder builder, TableModel table)
{
var keyBuilder = base.VisitPrimaryKey(builder, table);
if (keyBuilder == null)
{
return null;
}
// If this property is the single integer primary key on the EntityType then
// KeyConvention assumes ValueGeneratedOnAdd(). If the underlying column does
// not have Identity set then we need to set to ValueGeneratedNever() to
// override this behavior.
// TODO use KeyConvention directly to detect when it will be applied
var pkColumns = table.Columns.Where(c => c.PrimaryKeyOrdinal.HasValue).ToList();
if (pkColumns.Count != 1
|| pkColumns[0].ValueGenerated != null
|| pkColumns[0].DefaultValue != null)
{
return keyBuilder;
}
// TODO
var property = builder.Metadata.FindProperty(GetPropertyName(pkColumns[0]));
var propertyType = property?.ClrType?.UnwrapNullableType();
if (propertyType?.IsInteger() == true
|| propertyType == typeof(Guid))
{
property.ValueGenerated = ValueGenerated.Never;
}
return keyBuilder;
}
private ColumnModel FindColumnForForeignKey(
string columnName, TableModel table, string fkName)
{
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignStrings.ColumnNameEmptyOnForeignKey(
table.SchemaName, table.Name, fkName));
return null;
}
if (!_tableColumns.TryGetValue(
ColumnKey(table, columnName), out column))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindColumnForForeignKey(
fkName, columnName, table.SchemaName, table.Name));
return null;
}
return column;
}
private static string ColumnKey(TableModel table, string columnName) => TableKey(table) + ".[" + columnName + "]";
private static string TableKey(TableModel table) => TableKey(table.Name, table.SchemaName);
private void GetTables()
{
var command = _connection.CreateCommand();
// for origin of the sys.extended_properties SELECT statement
// below see https://github.com/aspnet/EntityFramework/issues/5126
command.CommandText =
@"SELECT
schema_name(t.schema_id) AS [schema],
t.name
FROM sys.tables AS t
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (SELECT *
FROM sys.extended_properties
WHERE major_id = t.object_id
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support') " +
$"AND t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var table = new TableModel
{
Database = _databaseModel,
SchemaName = reader.GetValueOrDefault<string>("schema"),
Name = reader.GetValueOrDefault<string>("name")
};
Logger.LogTrace(SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name));
if (_tableSelectionSet.Allows(table.SchemaName, table.Name))
{
_databaseModel.Tables.Add(table);
_tables[TableKey(table)] = table;
}
else
{
Logger.LogTrace(
SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name));
}
}
}
}
