public async void Missing_primary_key()
{
using (var testStore = SqliteTestStore.CreateScratch())
{
testStore.ExecuteNonQuery("CREATE TABLE Alicia ( Keys TEXT );");
var results = await Generator.GenerateAsync(new ReverseEngineeringConfiguration
{
ConnectionString = testStore.Connection.ConnectionString,
ProjectPath = "testout",
ProjectRootNamespace = "E2E.Sqlite",
UseFluentApiOnly = UseFluentApiOnly
});
var errorMessage = SqliteDesignStrings.MissingPrimaryKey("Alicia");
var expectedLog = new LoggerMessages
{
Warn =
{
errorMessage
}
};
AssertLog(expectedLog);
Assert.Contains(errorMessage, InMemoryFiles.RetrieveFileContents("testout", "Alicia.cs"));
}
}
public void It_logs_warning_for_bad_foreign_key()
{
// will fail because Id is not found
var sql = @"CREATE TABLE Parent ( Name );
CREATE TABLE Children (
Id INT PRIMARY KEY,
ParentId INT,
FOREIGN KEY (ParentId) REFERENCES Parent (Id)
);";
GetModel(sql);
Assert.Contains("Warning: " + SqliteDesignStrings.ForeignKeyScaffoldError("Children", "ParentId"), _logger.FullLog);
}
public void It_logs_warning_for_bad_foreign_key_principal_table()
{
// will fail because the referenced table, Parent, is not found
var sql = @"CREATE TABLE Children (
Id INT PRIMARY KEY,
ParentId INT,
FOREIGN KEY (ParentId) REFERENCES Parent (Id)
);";
GetModel(sql);
Assert.Contains("Debug: " +
SqliteDesignStrings.PrincipalTableNotFound(0, "Children", "Parent"),
_logger.FullLog);
}
public void It_logs_warning_for_bad_foreign_key_column()
{
// will fail because the referenced column, Id, is not found
var sql = @"CREATE TABLE Parent ( Name PRIMARY KEY);
CREATE TABLE Children (
Id INT PRIMARY KEY,
ParentId INT,
FOREIGN KEY (ParentId) REFERENCES Parent (Id)
);";
GetModel(sql);
Assert.Contains("Trace: " +
SqliteDesignStrings.PrincipalColumnNotFound(0, "Children", "Id", "Parent"),
_logger.FullLog);
}
public async void Principal_missing_primary_key()
{
using (var testStore = SqliteTestStore.GetOrCreateShared("NoPrincipalPk" + DbSuffix).AsTransient())
{
testStore.ExecuteNonQuery(@"CREATE TABLE Dependent (
Id PRIMARY KEY,
PrincipalId INT,
FOREIGN KEY (PrincipalId) REFERENCES Principal(Id)
);
CREATE TABLE Principal ( Id INT);");
testStore.Transaction.Commit();
var results = await Generator.GenerateAsync(new ReverseEngineeringConfiguration
{
ConnectionString = testStore.Connection.ConnectionString,
ProjectPath = "testout",
ProjectRootNamespace = "E2E.Sqlite",
UseFluentApiOnly = UseFluentApiOnly
});
var expectedLog = new LoggerMessages
{
Warn =
{
SqliteDesignStrings.MissingPrimaryKey("Principal"),
SqliteDesignStrings.ForeignKeyScaffoldError("Dependent", "PrincipalId")
}
};
AssertLog(expectedLog);
var expectedFileSet = new FileSet(new FileSystemFileService(), Path.Combine(ExpectedResultsParentDir, "NoPrincipalPk"))
{
Files =
{
"NoPrincipalPk" + DbSuffix + "Context.expected",
"Dependent.expected",
"Principal.expected"
}
};
var actualFileSet = new FileSet(InMemoryFiles, "testout")
{
Files = Enumerable.Repeat(results.ContextFile, 1).Concat(results.EntityTypeFiles).Select(Path.GetFileName).ToList()
};
AssertEqualFileContents(expectedFileSet, actualFileSet);
AssertCompile(actualFileSet);
}
}
private void GetColumns()
{
foreach (var table in _databaseModel.Tables)
{
using (var command = _connection.CreateCommand())
{
command.CommandText = $"PRAGMA table_info(\"{table.Name.Replace("\"", "\"\"")}\");"; // Interpolation okay; strings
using (var reader = command.ExecuteReader())
{
var ordinal = 0;
while (reader.Read())
{
var columnName = reader.GetValueOrDefault <string>("name");
var dataType = reader.GetValueOrDefault <string>("type");
var primaryKeyOrdinal = reader.GetValueOrDefault <int>("pk");
var notNull = reader.GetValueOrDefault <bool>("notnull");
var defaultValue = reader.GetValueOrDefault <string>("dflt_value");
Logger.LogDebug(
RelationalDesignEventId.FoundColumn,
() => SqliteDesignStrings.FoundColumn(
table.Name, columnName, dataType, ordinal,
notNull, primaryKeyOrdinal, defaultValue));
var isPk = primaryKeyOrdinal != 0;
var column = new ColumnModel
{
Table = table,
Name = columnName,
DataType = dataType,
Ordinal = ordinal++,
IsNullable = !notNull && !isPk,
PrimaryKeyOrdinal = isPk ? primaryKeyOrdinal : default(int?),
DefaultValue = defaultValue
};
table.Columns.Add(column);
_tableColumns[ColumnKey(table, column.Name)] = column;
}
}
}
}
}
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}'"; // Interpolation okay; strings
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetValueOrDefault <string>("name");
Logger.LogDebug(
RelationalDesignEventId.FoundTable,
() => 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.LogDebug(
RelationalDesignEventId.TableSkipped,
() => SqliteDesignStrings.TableNotInSelectionSet(name));
}
}
}
}
}
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
using (var fkList = _connection.CreateCommand())
{
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";
var tableForeignKeys = new Dictionary <int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetValueOrDefault <int>("id");
var principalTableName = reader.GetValueOrDefault <string>("table");
var fromColumnName = reader.GetValueOrDefault <string>("from");
var toColumnName = reader.GetValueOrDefault <string>("to");
var deleteAction = reader.GetValueOrDefault <string>("on_delete");
var fkOrdinal = reader.GetValueOrDefault <int>("seq");
Logger.LogTrace(SqliteDesignStrings.FoundForeignKeyColumn(
dependentTable.Name, id, principalTableName, fromColumnName,
toColumnName, deleteAction, fkOrdinal));
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
if (!_tables.TryGetValue(principalTableName, out principalTable))
{
Logger.LogTrace(SqliteDesignStrings.PrincipalTableNotFound(
id, dependentTable.Name, principalTableName));
continue;
}
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteAction)
};
}
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = fkOrdinal,
Column = _tableColumns[ColumnKey(dependentTable, fromColumnName)]
};
ColumnModel toColumn;
if (!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
Logger.LogTrace(SqliteDesignStrings.PrincipalColumnNotFound(
id, dependentTable.Name, toColumnName, principalTableName));
continue;
}
fkColumn.PrincipalColumn = toColumn;
foreignKey.Columns.Add(fkColumn);
if (!tableForeignKeys.ContainsKey(id))
{
tableForeignKeys.Add(id, foreignKey);
}
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
}
private void GetIndexes()
{
foreach (var table in _databaseModel.Tables)
{
using (var indexInfo = _connection.CreateCommand())
{
indexInfo.CommandText = $"PRAGMA index_list(\"{table.Name.Replace("\"", "\"\"")}\");";
using (var reader = indexInfo.ExecuteReader())
{
while (reader.Read())
{
var index = new IndexModel
{
Name = reader.GetValueOrDefault <string>("name"),
Table = table,
IsUnique = reader.GetValueOrDefault <bool>("unique")
};
Logger.LogTrace(SqliteDesignStrings
.FoundIndex(index.Name, table.Name, index.IsUnique));
table.Indexes.Add(index);
}
}
foreach (var index in table.Indexes)
{
var indexColumns = _connection.CreateCommand();
indexColumns.CommandText = $"PRAGMA index_info(\"{index.Name.Replace("\"", "\"\"")}\");";
index.IndexColumns = new List <IndexColumnModel>();
using (var reader = indexColumns.ExecuteReader())
{
while (reader.Read())
{
var columnName = reader.GetValueOrDefault <string>("name");
var indexOrdinal = reader.GetValueOrDefault <int>("seqno");
Logger.LogTrace(SqliteDesignStrings.FoundIndexColumn(
index.Name, table.Name, columnName, indexOrdinal));
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(SqliteDesignStrings
.ColumnNameEmptyOnIndex(index.Name, table.Name));
continue;
}
var column = _tableColumns[ColumnKey(index.Table, columnName)];
var indexColumn = new IndexColumnModel
{
Ordinal = indexOrdinal,
Column = column
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
}
}
private void LoadTablesAndColumns(SqliteConnection connection, ModelBuilder modelBuilder, ICollection <string> tables)
{
foreach (var tableName in tables)
{
modelBuilder.Entity(tableName, builder =>
{
builder.ToTable(tableName);
var tableInfo = connection.CreateCommand();
tableInfo.CommandText = $"PRAGMA table_info(\"{tableName.Replace("\"", "\"\"")}\");";
var keyProps = new List <string>();
using (var reader = tableInfo.ExecuteReader())
{
while (reader.Read())
{
var colName = reader.GetString((int)TableInfo.Name);
var typeName = reader.GetString((int)TableInfo.Type);
var isPk = reader.GetBoolean((int)TableInfo.Pk);
var notNull = isPk || reader.GetBoolean((int)TableInfo.NotNull);
var clrType = _typeMapper.GetClrType(typeName, nullable: !notNull);
var property = builder.Property(clrType, colName)
.HasColumnName(colName);
if (!string.IsNullOrEmpty(typeName))
{
property.HasColumnType(typeName);
}
var defaultVal = reader.GetValue((int)TableInfo.DefaultValue) as string;
if (!string.IsNullOrEmpty(defaultVal))
{
property.HasDefaultValueSql(defaultVal);
}
if (isPk)
{
keyProps.Add(colName);
}
else
{
property.IsRequired(notNull);
}
}
}
if (keyProps.Count > 0)
{
builder.HasKey(keyProps.ToArray());
}
else
{
var errorMessage = SqliteDesignStrings.MissingPrimaryKey(tableName);
builder.Metadata.AddAnnotation(AnnotationNameEntityTypeError, errorMessage);
Logger.LogWarning(errorMessage);
}
});
}
}
private void LogFailedForeignKey(ForeignKeyInfo foreignKey)
=> Logger.LogWarning(SqliteDesignStrings.ForeignKeyScaffoldError(foreignKey.Table, string.Join(",", foreignKey.From)));