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 + "]";