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)); }
public static void ParseTableDefinition(TableModel table, string sql) { var statements = ParseStatements(sql).ToList(); var i = 0; for (; i < statements.Count; i++) { var firstWord = statements[i].Split(' ', '(')[0]; if (_constraintKeyWords.Contains(firstWord)) { break; // once we see the first constraint, stop looking for params } ParseColumnDefinition(table, statements[i]); } for (; i < statements.Count; i++) { ParseConstraints(table, statements[i]); } }
public static void ParseColumnDefinition(TableModel table, string statement) { var paramName = UnescapeString(SafeSplit(statement, ' ').First()); var column = table.Columns.FirstOrDefault(c => c.Name.Equals(paramName, StringComparison.OrdinalIgnoreCase)); if (column == null) { return; } if (statement.IndexOf(" UNIQUE", StringComparison.OrdinalIgnoreCase) > 0) { var indexInfo = table.Indexes.FirstOrDefault(i => i.Columns.SingleOrDefault()?.Name.Equals(column.Name, StringComparison.OrdinalIgnoreCase) == true); if (indexInfo != null) { indexInfo.IsUnique = true; } } }
private void GetTables() { var command = _connection.CreateCommand(); command.CommandText = "SELECT schema_name(t.schema_id) AS [schema], t.name FROM sys.tables AS t " + $"WHERE t.name <> '{HistoryRepository.DefaultTableName}'"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var table = new TableModel { SchemaName = reader.GetString(0), Name = reader.GetString(1) }; if (_tableSelectionSet.Allows(table.SchemaName, table.Name)) { _databaseModel.Tables.Add(table); _tables[TableKey(table)] = table; } } } }
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, Columns = { childrenTable.Columns[0] }, PrincipalTable = parentTable, PrincipalColumns = { parentTable.Columns[0] }, OnDelete = Migrations.ReferentialAction.NoAction }); 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); }
private static string TableKey(TableModel table) => TableKey(table.Name, table.SchemaName);
private static string ColumnKey(TableModel table, string columnName) => TableKey(table) + ".[" + columnName + "]";
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 { Columns = { table.Columns[1] }, IsUnique = true }); table.ForeignKeys.Add(new ForeignKeyModel { Table = table, Columns = { table.Columns[1] }, PrincipalTable = table, PrincipalColumns = { table.Columns[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, Columns = { childrenTable.Columns[1], childrenTable.Columns[2] } }); childrenTable.ForeignKeys.Add(new ForeignKeyModel { Table = childrenTable, Columns = { childrenTable.Columns[1], childrenTable.Columns[2] }, PrincipalTable = parentTable, PrincipalColumns = { parentTable.Columns[0], parentTable.Columns[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, Columns = { table.Columns[1] }, PrincipalTable = table, PrincipalColumns = { table.Columns[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, Columns = { childrenTable.Columns[1] }, PrincipalTable = parentTable, PrincipalColumns = { parentTable.Columns[1] } }); _factory.Create(new DatabaseModel { Tables = { parentTable, childrenTable } }); Assert.Contains("Warning: " + RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound( childrenTable.ForeignKeys[0].DisplayName, "NotPkId", "Parent"), _logger.FullLog); }
public static void ParseConstraints(TableModel table, string statement) { var constraint = statement.Split(' ', '(')[0]; if (constraint.Equals("UNIQUE", StringComparison.OrdinalIgnoreCase)) { ParseInlineUniqueConstraint(table, statement); } }
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, Columns = { childrenTable.Columns[1], childrenTable.Columns[2] }, PrincipalTable = parentTable, PrincipalColumns = { parentTable.Columns[0], parentTable.Columns[1] }, OnDelete = Migrations.ReferentialAction.SetNull }); 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 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, Columns = { childrenTable.Columns[1] }, PrincipalTable = parentTable, PrincipalColumns = { parentTable.Columns[0] }, OnDelete = Migrations.ReferentialAction.Cascade }); 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]); }
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", Columns = { table.Columns[0] }, IsUnique = false }); table.Indexes.Add(new IndexModel { Name = "UNQ_C2", Columns = { table.Columns[1] }, IsUnique = true }); table.Indexes.Add(new IndexModel { Name = "IDX_C2_C1", Columns = { table.Columns[1], table.Columns[0] }, IsUnique = false }); table.Indexes.Add(new IndexModel { /*Name ="UNQ_C3_C1",*/ Columns = { table.Columns[2], table.Columns[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 static void ParseInlineUniqueConstraint(TableModel table, string statement) { var start = statement.IndexOf('(') + 1; var paramChunk = statement.Substring(start, statement.LastIndexOf(')') - start); var columns = SafeSplit(paramChunk, ',') .Select(UnescapeString) .ToList(); var index = table.Indexes.FirstOrDefault(i => { if (!i.Name.StartsWith("sqlite_autoindex") || !i.Table.Name.Equals(table.Name, StringComparison.OrdinalIgnoreCase)) { return false; } return columns.All(prop => i.Columns.Any(p => p.Name.Equals(prop, StringComparison.OrdinalIgnoreCase))); }); if (index != null) { index.IsUnique = true; } }
private void GetSqliteMaster() { var command = _connection.CreateCommand(); command.CommandText = "SELECT type, name, sql, tbl_name FROM sqlite_master ORDER BY type DESC"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { var type = reader.GetString(0); var name = reader.GetString(1); var sql = reader.GetValue(2) as string; // can be null var tableName = reader.GetString(3); if (type == "table" && name != "sqlite_sequence" && _tableSelectionSet.Allows(name)) { var table = new TableModel { Name = name }; _databaseModel.Tables.Add(table); _tables.Add(name, table); _tableDefinitions[name] = sql; } else if (type == "index" && _tables.ContainsKey(tableName)) { var table = _tables[tableName]; table.Indexes.Add(new IndexModel { Name = name, Table = table }); _indexDefinitions[name] = sql; } } } }
private static string ColumnKey(TableModel table, string columnName) => "[" + table.Name + "].[" + columnName + "]";