static void Main(string[] args)
{
int result = 2;
while (result == 2)
{
IGarageManager garageManager = new GarageManagerFactory().Create();
IConsoleInputOutput consoleInputOutput = new ConsoleInputOutput();
IVehicleManager vehicleManager = VehicleManagerFactory.Create(garageManager);
IVehicleParser vehicleParser = new VehicleParser();
IGarageParser garageParser = new GarageParser();
IFileInputOutput file = new FileInputOutput();
ICsvImporter csvImporter = new CsvImporter(file, vehicleParser);
ICommandExecuterFactory commandExecuterFactory = new CommandExecuterFactory(vehicleManager, consoleInputOutput, vehicleParser, garageManager, garageParser, csvImporter, file);
var commandLineParser = new CommandLineParser(consoleInputOutput, CommandDictionaryFactory.Create(), vehicleManager, commandExecuterFactory);
while ((result = commandLineParser.ReadCommand()) == 1)
{
;
}
if (result == 2)
{
consoleInputOutput.WriteInfo("Daten werden wiederhergestellt.");
}
}
}
public void ReadCommandTest_FindCarsByLicensePlateWasCalled()
{
bool isCalled = false;
var consoleMock = new ConsoleMock("select 1;", null, null);
var vehicleManagerMock = new vehicleManagerMock((s) =>
{
if (s == "1")
{
isCalled = true;
}
});
vehicleManagerMock.FindCarsByLicensePlateReturn = new Dictionary <Vehicle, ParkingPlaceOutput>();
var factory = new CommandExecuterFactory(vehicleManagerMock, consoleMock, null, null, null, null, null);
var sut = new CommandLineParser(consoleMock, CommandDictionaryFactory.Create(), vehicleManagerMock, factory);
sut.ReadCommand();
Assert.IsTrue(isCalled);
}
public async Task <IActionResult> GenerateData(SaveConnectionRequest request)
{
#region CHECKING IF USER IS AUTHORIZED TO ACCES REQUESTED DATABASE
var loginId = ((Login)HttpContext.Items["Login"]).LoginId;
//check if user is allowed to connect to database
var connectionProps = await ctx.ConnectionTables.Where(c => c.ConnectionId == request.ConnectionId && c.LoginId == loginId).FirstOrDefaultAsync();
if (connectionProps == null)
{
return(BadRequest(new
{
error = true,
message = "User not authorized to connect to specified database."
}));
}
#endregion
// Only process the tables from the request that need to have values generated.
var tablesForWhichToGenerateSql = request.Tables.Where(t => t.NumberOfColumnsToGenerate > 0).ToList();
if (tablesForWhichToGenerateSql.Count == 0)
{
return(Ok(new
{
error = false,
message = "No data to generate."
}));
}
#region CHECK IF ANY TABLES THAT NEED TO BE GENERATED REFERENCE TABLES/COLUMNS THAT DO NOT EXIST IN THE TABLES THAT NEED TO BE GENERATED
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
// If column is nullable. Then it can reference nonexisting column
if (columnForWhichToGenerateSql.IsNullable == 1)
{
continue;
}
if (columnForWhichToGenerateSql.ForeignTableName != null)
{
string foreignTableName = columnForWhichToGenerateSql.ForeignTableName;
string foreignColumnName = columnForWhichToGenerateSql.ForeignColumnName;
DatabaseTable foreignTable = tablesForWhichToGenerateSql.Where(t => t.Name == foreignTableName).FirstOrDefault();
DatabaseColumn foreignColumn = null;
if (foreignTable != null)
{
foreignColumn = foreignTable.DatabaseColumns.Where(c => c.Name == foreignColumnName).FirstOrDefault();
}
// If foreignTable is null, it means that there will be nothing for the foreign key to reference
if (foreignTable == null || foreignColumn == null)
{
return(BadRequest(new
{
error = true,
message = $"Table {tableForWhichToGenerateSql.Name} column {columnForWhichToGenerateSql.Name} references table {foreignTableName} column {foreignColumnName} which does not exist, in the data provided."
}));
}
}
}
}
#endregion
#region ORDER TABLES SO THAT IF TABLE A CONTAINS FOREIGN KEY REFERENCING TABLE B. TABLE A MUST BE FIRST IN LIST.
// Add those tables for which all referenced table are alredy in sorted list
List <DatabaseTable> sortedListOfTablesForWhichToGenerateSqlByForeignKey = new List <DatabaseTable>();
int renemberedCount;
// When this is true next loop will allow null values
bool tryToPutOneNull = false;
while (tablesForWhichToGenerateSql.Count() != 0)
{
renemberedCount = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Count();
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
List <ReferencedTableNullablePair> listOfTablesWhichThisTableReferences = new List <ReferencedTableNullablePair>();
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
// If column references some table
if (columnForWhichToGenerateSql.ForeignTableName != null)
{
// Get table of this name
DatabaseTable referencedTable = tablesForWhichToGenerateSql.Where(t => t.Name == columnForWhichToGenerateSql.ForeignTableName).FirstOrDefault();
// If this column refences another table add it to referenced tables
if (referencedTable != null)
{
listOfTablesWhichThisTableReferences.Add(new ReferencedTableNullablePair(referencedTable, columnForWhichToGenerateSql.IsNullable == 1));
}
}
}
// If this table references no one, the data for it can be generated
if (listOfTablesWhichThisTableReferences.Count() == 0)
{
// Add to list of sorted tables
if (!sortedListOfTablesForWhichToGenerateSqlByForeignKey.Contains(tableForWhichToGenerateSql))
{
sortedListOfTablesForWhichToGenerateSqlByForeignKey.Add(tableForWhichToGenerateSql);
}
continue;
}
// If table does reference other tables check if those tables are already in sorted list
bool allAlreadySorted = true;
for (int i = 0; i < listOfTablesWhichThisTableReferences.Count(); i++)
{
Boolean isNullable = listOfTablesWhichThisTableReferences[i].IsNullableReference;
DatabaseTable referencedTable = listOfTablesWhichThisTableReferences[i].ReferencedTable;
DatabaseTable sortedTable = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Where(t => t == referencedTable).FirstOrDefault();
if (sortedTable == null)
{
// Referenced table does not exist in sorted list, so this table is not yet ready to be generated
allAlreadySorted = false;
// Unless we are allowed to pass null and this is nullable
if (tryToPutOneNull && isNullable)
{
allAlreadySorted = true;
tryToPutOneNull = false;
}
}
if (!allAlreadySorted)
{
break;
}
}
//foreach (DatabaseTable referencedTable in listOfTablesWhichThisTableReferences)
//{
// DatabaseTable sortedTable = sortedListOfTablesForWhichToGenerateSqlByForeignKey.Where(t => t == referencedTable).FirstOrDefault();
// if (sortedTable == null)
// {
// // Referenced table does not exist in sorted list, so this table is not yet ready to be generated
// allAlreadySorted = false;
// }
// if (!allAlreadySorted)
// {
// break;
// }
//}
if (allAlreadySorted)
{
if (!sortedListOfTablesForWhichToGenerateSqlByForeignKey.Contains(tableForWhichToGenerateSql))
{
sortedListOfTablesForWhichToGenerateSqlByForeignKey.Add(tableForWhichToGenerateSql);
}
}
}
/// If no new generatable tables were found we are in a loop and should return BadRequest
// If no new generatable tables were found do it again but this time allow for null values on nullables. Add only one on there then try again.
if (renemberedCount >= sortedListOfTablesForWhichToGenerateSqlByForeignKey.Count())
{
// Try to pass with nulls
if (tryToPutOneNull == false)
{
tryToPutOneNull = true;
continue;
}
// Return BadRequest
return(BadRequest(new
{
error = true,
message = $"Please remove circular refenreces and try again"
}));
}
// Remove from tablesForWhichToGenerateSql everything that is in sortedList
foreach (DatabaseTable table in sortedListOfTablesForWhichToGenerateSqlByForeignKey)
{
tablesForWhichToGenerateSql.Remove(table);
}
}
// Return sorted list
tablesForWhichToGenerateSql = sortedListOfTablesForWhichToGenerateSqlByForeignKey;
#endregion
#region ORDER COLUMN INSIDE TABLE SO IF ONE NEEDS TO BE BIGGER THEN ANOTHER THE OTHER IS GENERATED BEFORE THE ONE
foreach (DatabaseTable table in tablesForWhichToGenerateSql)
{
List <DatabaseColumn> columns = table.DatabaseColumns.ToList();
List <string> listOfColumnNamesInTable = columns.Select(c => c.Name).ToList();
foreach (DatabaseColumn column in table.DatabaseColumns)
{
if (column.RelatedColumn != null &&
listOfColumnNamesInTable.Contains(column.RelatedColumn) &&
listOfColumnNamesInTable.IndexOf(column.Name) < listOfColumnNamesInTable.IndexOf(column.RelatedColumn))
{
DatabaseColumn tmp = table.DatabaseColumns.Where(c => c.Name == column.RelatedColumn).First();
columns.Remove(tmp);
columns.Insert(0, tmp);
}
}
table.DatabaseColumns = columns;
}
#endregion
// If program reaches this point, assume tables referenced by foreign keys exist and have data. String builder which will build the SQL commands.
StringBuilder builder = new StringBuilder();
// Dictionary in which string key of format tableName:columnName references list of values to put into mentioned column in mentioned table
Dictionary <string, List <string> > valuesGeneratedForDatabase = new Dictionary <string, List <string> >();
#region GENERATE SQL COMMAND
foreach (DatabaseTable tableForWhichToGenerateSql in tablesForWhichToGenerateSql)
{
// We know that NumberOfColumnsToGenerate is greater then 0 and is not null
int numberOfRowsToGenerate = (int)tableForWhichToGenerateSql.NumberOfColumnsToGenerate;
// Part of sql command that will be used to generate each row of this table.
string baseForSqlCommand = $"INSERT INTO {tableForWhichToGenerateSql.Name} (";
// First column inside brackets does not have ',', while the others have.
bool isFirstColumn = true;
// List of values to insert into table. Each list inside this list contains string to insert into corresponding row. listOfValueLists[0] means the list of values for first column.
// listOfValueLists[0][0] means value to put on first row for first column
List <List <string> > listOfValueLists = new List <List <string> >();
foreach (DatabaseColumn columnForWhichToGenerateSql in tableForWhichToGenerateSql.DatabaseColumns)
{
if (isFirstColumn)
{
baseForSqlCommand += columnForWhichToGenerateSql.Name;
isFirstColumn = false;
}
else
{
baseForSqlCommand += $", {columnForWhichToGenerateSql.Name}";
}
// List of values to use for generating column data.
List <string> listOfValues = new List <string>();
try
{
// Append to column his table
columnForWhichToGenerateSql.Table = tableForWhichToGenerateSql;
// Generate values for this column
listOfValues = GenerationModes.Generate(ctx, numberOfRowsToGenerate, columnForWhichToGenerateSql, valuesGeneratedForDatabase, connectionProps.SqlPlatformId, tableForWhichToGenerateSql);
}
catch (GenerationException e)
{
return(BadRequest(new
{
error = true,
message = $"Error while generating data for COLUMN '{columnForWhichToGenerateSql.Name}' in TABLE '{tableForWhichToGenerateSql.Name}': " + e.Message
}));
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(BadRequest(new
{
error = true,
message = "Unexpected error ocurred"
}));
}
// Add to list of values for generating
listOfValueLists.Add(listOfValues);
// Insert valueList for given table and column int Dictionary for potential foreign key use
valuesGeneratedForDatabase[$"{tableForWhichToGenerateSql.Name}:{columnForWhichToGenerateSql.Name}"] = listOfValues;
}
// Continue base of command
baseForSqlCommand += ") VALUES(";
// Add created values to StringBuilder
for (int i = 0; i < numberOfRowsToGenerate; i++)
{
// The first values for columns does not have ',', while the others have.
isFirstColumn = true;
string rowInsertCommand = baseForSqlCommand;
// For this row for every list in listOfValueLists get the i-th element and insert it into the command.
foreach (List <string> valueList in listOfValueLists)
{
if (isFirstColumn)
{
rowInsertCommand += $"{valueList[i]} ";
isFirstColumn = false;
}
else
{
rowInsertCommand += $",{valueList[i]} ";
}
}
// Finish the command
rowInsertCommand += ");\n";
// Add line insert command to builder.
builder.Append(rowInsertCommand);
}
}
#endregion
string connectionString = $"Host={connectionProps.Host}; Username={connectionProps.Username}; Password={request.DatabasePassword}; Database={connectionProps.Database}";
SqlPlatform databasePlatform = ctx.SqlPlatforms.Where(p => p.SqlPlatformId == connectionProps.SqlPlatformId).FirstOrDefault();
if (databasePlatform == null)
{
return(Ok(new
{
error = true,
message = $"Invalid sql platform id {connectionProps.SqlPlatformId}."
}));
}
CommandExecuterFactory commandExecuterFactory = new CommandExecuterFactory(connectionProps, request.DatabasePassword);
IDatabaseCommandExecuter commandExecuter = commandExecuterFactory.GetExecuter(databasePlatform.Name);
string sql = builder.ToString();
try
{
await commandExecuter.ExecuteCommand(sql);
}
catch (Exception e)
{
Console.WriteLine(e);
return(BadRequest(new
{
error = true,
message = "Unexpected error ocurred"
}));
}
return(Ok(new
{
error = false,
message = "Data successfully generate and inserted into database"
}));
}
